-- spec.sr.ht full initial schema.
--
-- This is the authoritative DDL for a fresh install. The brant migration in
-- migrations/0001_initial.sql applies the same objects incrementally; keep the
-- two in sync.
--
-- Git is authoritative for document bodies; Postgres never stores a body. Every
-- table here holds only what git cannot answer cheaply, and every table here is
-- reconstructable from refs by the reconciler — which is what makes the
-- non-transactional merge path (git refs, then Postgres, then the index)
-- tolerable.
--
-- `comment` was deliberately absent until the review UI existed: the anchoring
-- model had to be settled against a built UI before being committed to a
-- schema. It was, in Phase 5b, and the table is below.

-- Spaces exist as repos; this table is for listing and index bookkeeping.
CREATE TABLE space (
	id            SERIAL PRIMARY KEY,
	owner         TEXT NOT NULL,          -- "bigbes", no ~ prefix
	name          TEXT NOT NULL,
	created       TIMESTAMPTZ NOT NULL DEFAULT now(),
	CONSTRAINT uq_space_owner_name UNIQUE (owner, name)
);

-- Global, not per-project: a later import cannot collide. doc_id is the PRIMARY
-- KEY rather than a (space_id, doc_id) pair on purpose — global uniqueness is
-- the invariant the whole link/comment/staleness model rests on, so a colliding
-- registration must be impossible to insert, not merely detected in Go.
CREATE TABLE document_id (
	doc_id        TEXT PRIMARY KEY,       -- "SPEC-0007"
	space_id      INTEGER NOT NULL REFERENCES space(id) ON DELETE CASCADE,
	path          TEXT NOT NULL,          -- current path on the approved branch
	updated_rev   TEXT NOT NULL
);

CREATE TABLE proposal (
	id            SERIAL PRIMARY KEY,
	space_id      INTEGER NOT NULL REFERENCES space(id) ON DELETE CASCADE,
	title         TEXT NOT NULL,
	rationale     TEXT,
	base_rev      TEXT NOT NULL,          -- the If-Match value; does not move
	branch        TEXT NOT NULL,          -- "proposals/42"
	state         TEXT NOT NULL,          -- open | merged | rejected
	approval      TEXT,                   -- human | policy, set on merge
	merged_rev    TEXT,
	agent         TEXT NOT NULL,          -- "claude-code/spec-writer"
	agent_session TEXT NOT NULL,
	created       TIMESTAMPTZ NOT NULL DEFAULT now(),
	resolved      TIMESTAMPTZ,

	-- The state machine is `open -> merged` and `open -> rejected`, and nothing
	-- else. These constraints make every row that would contradict it
	-- unwritable; the UPDATE ... WHERE state = 'open' guard in db/proposal.go
	-- is what makes an illegal *transition* unwritable.
	CONSTRAINT ck_proposal_state CHECK (state IN ('open', 'merged', 'rejected')),
	CONSTRAINT ck_proposal_approval CHECK (approval IS NULL OR approval IN ('human', 'policy')),
	-- Auto-merged is not human-approved and readers must be able to tell, so a
	-- merged row without an approval kind (or an unmerged row carrying one)
	-- would launder unreviewed agent output as blessed.
	CONSTRAINT ck_proposal_merged CHECK ((state = 'merged') = (approval IS NOT NULL)),
	CONSTRAINT ck_proposal_merged_rev CHECK ((state = 'merged') = (merged_rev IS NOT NULL)),
	CONSTRAINT ck_proposal_resolved CHECK ((state = 'open') = (resolved IS NULL)),
	-- Provenance is the one thing that is not optional: one shared token still
	-- yields a full audit trail because the identity strings, not the
	-- credential, are what identify who did what. NOT NULL alone would accept
	-- the empty string and lose that.
	CONSTRAINT ck_proposal_provenance CHECK (length(agent) > 0 AND length(agent_session) > 0)
);
-- The inbox ("N proposals waiting on you") and the digest are both
-- state-filtered, newest-first scans.
CREATE INDEX ix_proposal_state_created ON proposal (state, created DESC);

-- There is no credential table. agent_token stood here — one instance-wide
-- shared secret, stored as a sha256 — until agent issuance moved to
-- tokens.sr.ht (migration 0005). A working token is signed by the instance and
-- carries its own owner, expiry and grants, so authenticating one is a
-- signature check in authn/ and there is nothing here to look up.

-- Index staleness: compared against the space's approved head.
CREATE TABLE index_stamp (
	space_id      INTEGER PRIMARY KEY REFERENCES space(id) ON DELETE CASCADE,
	rev           TEXT NOT NULL,
	indexed_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- "What landed since you last looked", for the policy-merged digest.
CREATE TABLE digest_mark (
	owner         TEXT PRIMARY KEY,
	seen_at       TIMESTAMPTZ NOT NULL
);

-- A project is pure metadata: a saved filter, not a container. It owns no index
-- and no storage, so this table is a name and the next one is the filter.
--
-- There is exactly one bleve index; querying a project means restricting that
-- index to the project's spaces. Per-project indexes were specified in an
-- earlier draft and retracted: every merge would fan out to N rebuilds and
-- adding a space to a project would force one. Nothing here scopes document
-- IDs either — those are global (see document_id above), because projects are
-- edited *after* merges, so a per-project registry could juxtapose two
-- already-merged documents sharing an ID with no merge left to reject.
CREATE TABLE project (
	id            SERIAL PRIMARY KEY,
	owner         TEXT NOT NULL,          -- "bigbes", no ~ prefix
	name          TEXT NOT NULL,          -- "tarantool", no + prefix
	created       TIMESTAMPTZ NOT NULL DEFAULT now(),
	CONSTRAINT uq_project_owner_name UNIQUE (owner, name)
);

-- The filter itself: which spaces a project selects. The composite primary key
-- is what makes membership a set — a space cannot be added to a project twice,
-- so no query has to deduplicate — and it is also the index for resolving a
-- project to its spaces. Both sides cascade: deleting a project drops its
-- membership rows and nothing else, and deleting a space removes it from every
-- project that named it rather than leaving a dangling filter term.
CREATE TABLE project_space (
	project_id    INTEGER NOT NULL REFERENCES project(id) ON DELETE CASCADE,
	space_id      INTEGER NOT NULL REFERENCES space(id) ON DELETE CASCADE,
	PRIMARY KEY (project_id, space_id)
);
-- The other direction: which projects contain this space. Not covered by the
-- primary key, whose leading column is project_id.
CREATE INDEX ix_project_space_space ON project_space (space_id);

-- GraphQL-native webhooks (added in migrations/0003_webhooks.sql).
--
-- These tables are written and read by core-go's webhook engine, which inserts
-- `NOW() at time zone 'utc'` — a `timestamp` WITHOUT time zone. So, unlike
-- spec's own tables, the three tables in THIS SECTION use bare `timestamp` to
-- match pages.sr.ht and avoid timezone coercion. This is deliberate; do not
-- "fix" it to TIMESTAMPTZ. The rule is scoped to core-go's own tables: `comment`
-- below is spec's, added later, and correctly uses TIMESTAMPTZ.

-- Users. spec.sr.ht is single-owner, but the webhook subscription is
-- user-scoped in the core-go convention, so a user row is the owner's identity
-- and the FK target. Columns mirror what core-go's auth.LookupUser selects, so
-- the table is ready if the service ever adopts core-go auth wholesale; today
-- only id/username are used (the owner row is seeded at startup).
CREATE TYPE user_type AS ENUM ('PENDING','USER','ADMIN','SUSPENDED');
CREATE TABLE "user" (
	id                serial PRIMARY KEY,
	created           timestamp NOT NULL,
	updated           timestamp NOT NULL,
	username          varchar(256) NOT NULL UNIQUE,
	email             varchar(256) NOT NULL DEFAULT '',
	user_type         user_type NOT NULL DEFAULT 'USER',
	url               varchar,
	location          varchar,
	bio               varchar,
	suspension_notice varchar
);

-- Proposal lifecycle events a webhook may subscribe to.
CREATE TYPE webhook_event AS ENUM ('PROPOSAL_OPENED','PROPOSAL_MERGED','PROPOSAL_REJECTED');

-- The auth captured on a subscription at creation time (core-go webhooks/config.go
-- AuthConfig). Only OAUTH2 and INTERNAL are storable; the check enforces it.
CREATE TYPE auth_method AS ENUM ('OAUTH_LEGACY','OAUTH2','COOKIE','INTERNAL','WEBHOOK');

-- GraphQL-native user webhook subscription. The auth columns
-- (auth_method/token_hash/grants/client_id/expires/node_id) mirror core-go's
-- webhooks.WebhookSubscription exactly; the engine selects them by these names.
CREATE TABLE gql_user_wh_sub (
	id          serial PRIMARY KEY,
	created     timestamp NOT NULL,
	events      webhook_event[] NOT NULL CHECK (cardinality(events) > 0),
	url         varchar NOT NULL,
	query       varchar NOT NULL,
	auth_method auth_method NOT NULL CHECK (auth_method IN ('OAUTH2','INTERNAL')),
	token_hash  varchar(128) CHECK ((auth_method = 'OAUTH2') = (token_hash IS NOT NULL)),
	grants      varchar,
	client_id   uuid,
	expires     timestamp CHECK ((auth_method = 'OAUTH2') = (expires IS NOT NULL)),
	node_id     varchar CHECK ((auth_method = 'INTERNAL') = (node_id IS NOT NULL)),
	user_id     integer NOT NULL REFERENCES "user"(id) ON DELETE CASCADE
);
CREATE INDEX gql_user_wh_sub_token_hash_idx ON gql_user_wh_sub (token_hash);

-- Delivery records. Columns match core-go's insert/update in webhooks/queue.go.
CREATE TABLE gql_user_wh_delivery (
	id               serial PRIMARY KEY,
	uuid             uuid NOT NULL,
	date             timestamp NOT NULL,
	event            webhook_event NOT NULL,
	subscription_id  integer NOT NULL REFERENCES gql_user_wh_sub(id) ON DELETE CASCADE,
	request_body     varchar NOT NULL,
	response_body    varchar,
	response_headers varchar,
	response_status  integer
);

-- Inline comments on a proposal, anchored to a block of a document (added in
-- migrations/0004_comment.sql).
--
-- The anchor is (doc_id, heading_path, block_index, block_hash) and is
-- content-first: a block is found by its hash wherever it moved to, and only
-- when that fails is position consulted. Line numbers are deliberately absent —
-- prose reflows, so a one-word edit moves every line below it.
--
-- WHAT IS NOT STORED: whether a comment still fits. A comment is not outdated
-- in general, it is outdated *at a revision*, and a proposal branch moves under
-- it as the agent revises. Anchor state is derived at read time by
-- core.ResolveAnchor. A column here would cache a function of a moving input
-- and be wrong every time the agent pushed.
CREATE TABLE comment (
	id            SERIAL PRIMARY KEY,
	proposal_id   INTEGER NOT NULL REFERENCES proposal(id) ON DELETE CASCADE,
	-- A reply. One level only: a thread is a root plus its replies, which is
	-- what a review conversation with one human and one agent actually is.
	-- Enforced in db/comment.go, since a CHECK cannot look at another row.
	parent_id     INTEGER REFERENCES comment(id) ON DELETE CASCADE,

	-- The anchor. NULL on a reply, which inherits its root's rather than
	-- carrying a copy that could drift from it.
	doc_id        TEXT,                   -- archive addressing key: "SPEC-0007", or the path
	doc_path      TEXT,                   -- path as at comment time; display, and which diff it belongs to
	heading_path  TEXT[],                 -- enclosing headings, outermost first
	block_index   INTEGER,                -- position within heading_path, NOT within the document
	block_hash    TEXT,                   -- prosediff block hash when the comment was written
	side          TEXT,                   -- new | old ("old" only for a block the proposal deletes)

	body          TEXT NOT NULL,
	author        TEXT NOT NULL,          -- owner username, or "claude-code/spec-writer"
	author_kind   TEXT NOT NULL,          -- human | agent
	agent_session TEXT,
	created       TIMESTAMPTZ NOT NULL DEFAULT now(),
	-- Set when the owner resolves the thread. Agents may not resolve — an agent
	-- marking its own critique resolved would defeat the auto-merge gate — which
	-- is enforced in the service layer, where the caller's identity is known;
	-- this column only records that it happened.
	resolved      TIMESTAMPTZ,

	CONSTRAINT ck_comment_body CHECK (length(btrim(body)) > 0),
	CONSTRAINT ck_comment_author CHECK (length(author) > 0),
	CONSTRAINT ck_comment_author_kind CHECK (author_kind IN ('human', 'agent')),
	-- Provenance, on the same rule as proposal: the identity strings, not the
	-- credential, are what identify who said what, so an agent comment without a
	-- session is not a comment with a missing field — it is an unattributable
	-- one. NOT NULL alone would accept the empty string and lose that.
	CONSTRAINT ck_comment_provenance CHECK (
		(author_kind = 'agent') = (agent_session IS NOT NULL AND length(agent_session) > 0)
	),
	-- A root carries the whole anchor and a reply carries none of it. Written as
	-- one predicate over every anchor column so a half-populated anchor — the
	-- shape a partial write would leave — cannot be stored at all.
	CONSTRAINT ck_comment_anchor CHECK (
		(parent_id IS NULL) = (doc_id IS NOT NULL)
		AND (doc_id IS NULL) = (doc_path IS NULL)
		AND (doc_id IS NULL) = (heading_path IS NULL)
		AND (doc_id IS NULL) = (block_index IS NULL)
		AND (doc_id IS NULL) = (block_hash IS NULL)
		AND (doc_id IS NULL) = (side IS NULL)
	),
	CONSTRAINT ck_comment_side CHECK (side IS NULL OR side IN ('new', 'old')),
	CONSTRAINT ck_comment_block_index CHECK (block_index IS NULL OR block_index >= 0),
	-- Resolution is a property of the thread, not of one message in it.
	CONSTRAINT ck_comment_resolved CHECK (resolved IS NULL OR parent_id IS NULL)
);

-- The review page reads a proposal's whole thread set in one go.
CREATE INDEX ix_comment_proposal ON comment (proposal_id, created);
-- Replies, by thread.
CREATE INDEX ix_comment_parent ON comment (parent_id) WHERE parent_id IS NOT NULL;
-- The auto-merge gate asks one question — "does this proposal have an
-- unresolved thread?" — on every policy merge, so it gets its own partial index
-- rather than scanning a proposal's comments to answer it.
CREATE INDEX ix_comment_unresolved ON comment (proposal_id)
	WHERE parent_id IS NULL AND resolved IS NULL;