pg: State Store tables on Postgres via sqlc #102
Notifications
Due Date
No due date set.
Blocks
Depends on
Reference: sam/archie#102
Reference in new issue
Block a user
Port internal/store (tasks, transitions, events, resources, resource_history, config_snapshot, channel_status, apply_status, identities, identity_aliases, identity_events, identity_subjects) to goose migrations and sqlc queries, preserving every storecontract/staterpc behaviour and sentinel. ClaimNext uses SELECT ... FOR UPDATE SKIP LOCKED in id order. The EventsSince cursor must never skip an event that commits after one with a higher id (bigserial is not commit-ordered); pin it with a test. Existing store tests run against Postgres.
Notes
2026-09-24 progress (
a463f5ed): the schema and the task queries are done and tested against real PG 18 via testcontainers (goose migration 0001, sqlc queries/tasks.sql, committed postgresdb package, task db:generate + db:check in the gate). Two SQLite-vs-Postgres behaviours are now pinned: FOR UPDATE SKIP LOCKED claiming, and every Go-int column being bigint (the chat issue-number seed is 10^15 and does not fit int4 -- a 32-bit column would have narrowed silently). STILL TO DO: the remaining store methods (events, resources, resources_history, identities, config_snapshot, channel_status, apply_status, transitions beyond the insert) still run on SQLite, as do their tests; Open still takes a file path; store.AcquireOwnership has not been replaced by the advisory lock; the database URL config field and the docker-compose postgres service are not wired. OPEN DESIGN QUESTION for the event feed: the EventsSince cursor is (at, id) and bigserial is not commit-ordered, so a reader can skip a row whose transaction commits after one with a higher id. The fix is to freeze a horizon (xmin against pg_snapshot_xmin) and carry it in the cursor, which changes the cursor string format -- a wire change that needs Sam's decision.2026-09-24 scope split for the fleet run: the foundation wiring tail (DB URL config field, docker-compose service, task dev, per-domain sqlc/goose layout, db:check coverage, AcquireOwnership -> advisory lock, event-insert serialization) moved to archie-core-7r0x.9 so it can land alone before the three ports. What stays on .2: events, identities, transitions (read + write), config_snapshot, channel_status, apply_status, the maintenance surface, and the write-side normalisation recon found unowned -- clip(detail,4000), park_class/park_reason defaulting, and the transitions read side. Evidence: .pi/pg-migrate-run/recon/01-store-remainder.md section 5.
2ic ruling (approved; implementation scope includes this bead): Automatic schema rollback is withdrawn. Keep a whole-database pre-migration snapshot; recovery is explicit offline pg_restore --clean after all clients stop and ownership lock confirms no State Store is serving. Do not run goose Down as rollback; it is destructive. Ensure installer/watchdog behavior and operator messaging do not promise automatic schema restoration.
Close reason
Closed
Imported from beads
archie-core-7r0x.2· GitHub #1020 · type task · priority P1 · created 2026-09-23 · closed 2026-09-28sam wrote (originally 2026-09-23):
2026-09-24 progress (
cebbcb56): ported the control-plane resources slice to PostgreSQL. The new Resources adapter uses a transaction-scoped advisory lock before the request-id lookup and expected-version read, preserving SQLite's retry-idempotency and optimistic-version semantics under concurrent PG writers. Added sqlc resource/history queries and PG 18 coverage for stale writes, immediate and late replays, read/not-found, bounded/unbounded history, and two concurrent writers (exactly one write/audit revision wins). Validation: TESTCONTAINERS_RYUK_DISABLED=true task check passed.