[EPIC] Move persistence to Postgres (pgx, sqlc, goose) #1128

Closed
opened 2026-10-27 13:39:08 +00:00 by sam · 0 comments
Owner

Decided 2026-09-24 (Sam): move all persistence to Postgres before orgs land, using sqlc for queries and goose for migrations. Postgres only, no second SQLite backend. Replaces the SQLite State Store (internal/store), the PocketBase event store (internal/infrastructure/edastore), the PocketBase admin views (internal/infrastructure/stateadmin) and the Gateway conversation file. The State Store gRPC contract stays the boundary, so consumers do not change. Orgs (archie-core-e5fn) are built on the result, with row-level security for tenant isolation.

Notes

2026-09-24 decisions (Sam): plain Postgres, not Supabase (any Postgres host works through a direct session connection; archie never uses a provider's auth/REST/realtime). PostgreSQL 18 is the minimum and target (18.6 latest stable per postgresql.org/versions.json on 2026-09-24); use its features where they fit (built-in uuidv7(), virtual generated columns, RETURNING OLD/NEW, temporal constraints). Session-mode connections only: advisory locks, LISTEN/NOTIFY and per-transaction settings break behind a transaction-mode pooler.

Close reason

Closed


Imported from beads archie-core-7r0x · GitHub #1022 · type epic · priority P1 · created 2026-09-23 · closed 2026-09-28

Decided 2026-09-24 (Sam): move all persistence to Postgres before orgs land, using sqlc for queries and goose for migrations. Postgres only, no second SQLite backend. Replaces the SQLite State Store (internal/store), the PocketBase event store (internal/infrastructure/edastore), the PocketBase admin views (internal/infrastructure/stateadmin) and the Gateway conversation file. The State Store gRPC contract stays the boundary, so consumers do not change. Orgs (archie-core-e5fn) are built on the result, with row-level security for tenant isolation. ### Notes 2026-09-24 decisions (Sam): plain Postgres, not Supabase (any Postgres host works through a direct session connection; archie never uses a provider's auth/REST/realtime). PostgreSQL 18 is the minimum and target (18.6 latest stable per postgresql.org/versions.json on 2026-09-24); use its features where they fit (built-in uuidv7(), virtual generated columns, RETURNING OLD/NEW, temporal constraints). Session-mode connections only: advisory locks, LISTEN/NOTIFY and per-transaction settings break behind a transaction-mode pooler. ### Close reason Closed --- _Imported from beads `archie-core-7r0x` · GitHub #1022 · type epic · priority P1 · created 2026-09-23 · closed 2026-09-28_
sam added the highepic labels 2026-10-27 13:39:08 +00:00
sam self-assigned this 2026-10-27 13:39:08 +00:00
sam closed this issue 2026-10-27 13:39:08 +00:00
Sign in to join this conversation.
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: sam/archie#1128