-- 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: `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
);
-- 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 above (which use TIMESTAMPTZ), everything below uses bare
-- `timestamp` to match pages.sr.ht and avoid timezone coercion. This is
-- deliberate; do not "fix" it to 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
);