-- 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.
--
-- Deliberately absent: `project` (a project is a saved filter — a name plus a
-- space-id list — and committing it before there are several spaces to filter
-- would be speculative; it arrives with the meta-project in Phase 2) and
-- `comment` (inline comments are post-v1, and the anchoring model should be
-- settled by building the review UI before it is committed to a schema).

-- 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);

-- Only a hash is ever stored; the token itself exists exactly once, at mint
-- time, in the response to the operator.
CREATE TABLE agent_token (
	id            SERIAL PRIMARY KEY,
	name          TEXT NOT NULL,
	token_hash    BYTEA NOT NULL UNIQUE,
	created       TIMESTAMPTZ NOT NULL DEFAULT now(),
	revoked       TIMESTAMPTZ
);

-- 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
);