feat(db): webhook + user tables (Phase 5a foundation)
The DB foundation for GraphQL-native webhooks, matching the core-go /
pages.sr.ht convention:
- "user" table — spec is single-owner, but the webhook subscription is
user-scoped in core-go's model, so a user row is the owner's identity
and the FK target. Columns mirror core-go auth.LookupUser so the table
is ready if the service ever adopts core-go auth; today only id/username
are used (owner seeded at startup).
- webhook_event enum (PROPOSAL_OPENED/MERGED/REJECTED), auth_method enum.
- gql_user_wh_sub / gql_user_wh_delivery — column names match core-go's
webhooks engine exactly (it selects/inserts by these names). The
events check uses cardinality() not array_length() so an empty event
list is rejected at the DB (array_length returns NULL there, which a
CHECK does not reject — a latent gap in the upstream convention).
Webhook tables use bare `timestamp` (not spec's TIMESTAMPTZ) to match
core-go's `NOW() at time zone 'utc'` inserts. Verified: schema.sql and
the 0003 migration both apply and reverse cleanly; FK/check/cascade all
behave.
feat: projects — a saved filter over one global index, not a container
A project is a named space set; querying one filters the single global
index. The meta-project is an implicit filter at a reserved address rather
than a row: a stored +everything would need a sync job on every space
creation, and its one failure mode is silently omitting a space.
SpaceFilter distinguishes All from an empty member list, because a freshly
created project has no members and must mean selects-nothing. Collapsing
the two would make every new project silently match the whole corpus.
feat: Postgres schema, initial migration and the db/ query layer
The six tables of the design's Postgres schema — space, document_id,
proposal, agent_token, index_stamp, digest_mark — plus a typed query layer
over them. project and comment stay deliberately absent.
Two invariants are enforced in SQL rather than in Go, because a
check-then-write in Go has a window and this does not:
- Global document ID uniqueness is the doc_id PRIMARY KEY, and the merge
path's multi-row upsert guards its ON CONFLICT branch on the owning
space, so a batch claiming another space's ID leaves that row untouched
and surfaces as a CollisionError naming where the ID really lives.
- Proposal transitions run as UPDATE ... WHERE state = 'open'; the current
state is read back only to name it in the error. CHECK constraints make
an unknown state, a merged row without an approval kind or merged_rev,
and empty provenance unwritable by anything, including SQL that does not
go through this package.
MergeProposal wraps the transition and the registry re-pointing in one
transaction: a merged proposal whose documents are still registered at their
pre-merge paths would break link resolution and the next staleness check.
Agent tokens store only a SHA-256 hash; the lookup boundary re-verifies with
subtle.ConstantTimeCompare and rejects revoked tokens distinctly.
Tests skip when SPECSRHT_TEST_PG is unset, so the pure-logic half — batch
duplicate detection, token comparison, argument guards, and the agreement
between schema.sql and migrations/0001_initial.sql — still runs with no
database available.