Epic: PostgreSQL backend support for the cq server

[nissessenap]

Summary

Add opt-in PostgreSQL support to the cq server. SQLite remains the
zero-config default for single-instance use; PostgreSQL is opt-in for
multi-process / Kubernetes deployments. Selection via CQ_DATABASE_URL
with fallback to CQ_DB_PATH.

Server only. Go SDK, Python SDK LocalStore, and CLI local store
remain SQLite — those are offline (local) stores, not shared databases.

This epic tracks the implementation agreed in RFC #275.

Original request

Preserved from the issue history above (opened by @nissessenap,
2026-04-09): hosting cq in Kubernetes with a single SQLite instance is
not viable — backup pain, single-writer ceiling, no path to horizontal
scale.

Architecture (from RFC #275)

• Store protocol (async) with one concrete implementation per backend.
  • SqliteStore — owns sqlite-vec, FTS5, PRAGMAs, anything SQLite-native.
  • PostgresStore — owns pgvector, tsvector + GIN, advisory locks,
    anything Postgres-native.
• Portable queries live in a shared SQLAlchemy Core helper consumed by
  both concrete stores. Strategy pattern at the protocol boundary; no
  runtime if dialect == ... branches inside methods.
• Migrations: Alembic with render_as_batch=True. Single migration
  files run on both backends.
• pg_advisory_lock wraps migration on Postgres to prevent multi-pod
  startup race.
• Baseline migration reverse-engineered from current production schema,
  stamped on first startup of existing databases.
• Timestamps cleaned up to proper datetime at store boundary
  (TIMESTAMP WITH TIME ZONE on Postgres, ISO strings on SQLite).

Phased plan

Phase 1 — make the change easy (SQLite-only, no behavior change):
extension point + migration framework. Mergeable independently.

Phase 2 — make the easy change: drop in PostgresStore, CI matrix.

Phase 3 — timestamp cleanup: proper datetime at store boundary.

Phase 4 — documentation.

Phase 1 dependency graph

Level 0:  (1) deps + Alembic skeleton
              │
              ├──────────────┬───────────────┐
              ▼              ▼               ▼
Level 1:  (2) baseline    (3) async      (4) Core query
          + stamp           Store           helpers
                            protocol
              │              │               │
              │              └───────┬───────┘
              │                      ▼
Level 2:      │             (5) SqliteStore
              │                  via protocol
              │                      │
              │                      ▼
Level 3:      │             (6) CQ_DATABASE_URL
              │                  + factory
              │
              ▼
Level 4:  (7) remove ad-hoc _ensure_* DDL
              (gate on (2) deployed)

After (1) lands, (2), (3), and (4) can proceed in parallel. (5) waits
on (3) + (4). (6) waits on (5). (7) waits on (2) being deployed
everywhere.

Child issues — Phase 1: protocol + Alembic, SQLite-only

• Add SQLAlchemy + Alembic dependencies and Alembic skeleton #304 SQLAlchemy + Alembic dependencies + Alembic skeleton
• Baseline Alembic migration + stamp-on-startup logic #305 Baseline migration + stamp-on-startup logic
• Define async Store protocol #306 Define async Store protocol
• Shared SQLAlchemy Core query helpers for portable queries #307 Shared SQLAlchemy Core query helpers
• Implement SqliteStore against Store protocol via Core helpers #308 Implement SqliteStore against protocol via Core helpers
• Wire CQ_DATABASE_URL and store factory #309 Wire CQ_DATABASE_URL + store factory
• Remove ad-hoc _ensure_* DDL now that Alembic owns the schema #310 Remove ad-hoc _ensure_* DDL

Child issues — Phase 2: PostgresStore

• Add psycopg v3 dependency and postgresql+psycopg URL handling #311 Add psycopg v3 dependency + postgresql+psycopg URL handling
• Implement PostgresStore against Store protocol #312 Implement PostgresStore
• Wrap migration startup in pg_advisory_lock (multi-pod safety) #313 pg_advisory_lock wrap for migration startup
• Add testcontainers-python fixtures for PostgreSQL tests #314 testcontainers-python fixtures
• CI matrix: run server tests against SQLite and PostgreSQL #315 CI matrix: SQLite + PostgreSQL

Child issues — Phase 3: timestamp cleanup

• Convert timestamps to datetime at the store boundary #316 Datetime conversion at store boundary
• Migrate Postgres timestamps to TIMESTAMP WITH TIME ZONE #317 TIMESTAMP WITH TIME ZONE on Postgres + caller updates

Child issues — Phase 4: documentation

• Update docs/architecture.md for multi-backend support #318 Update docs/architecture.md for multi-backend
• Document CQ_DATABASE_URL in deployment / configuration docs #319 Document CQ_DATABASE_URL in deployment docs
• Add optional PostgreSQL service to docker-compose.yml #320 Optional Postgres service in docker-compose.yml

[nissessenap]

Should be rather easy having an AI agent write a new ABC class and provide a second implementation.
It looks rather straight forward to fix, I'm happy to contribute if it's interesting.

I normally program in go, but it looks like psycopg would be the natural fit and use psycopg_pool.ConnectionPool for starters.

[peteski22]

Hey @nissessenap! 😄

Just to make sure we're on the same page... This would apply to the server; the CLI's local store is intentionally SQLite (it's a local cache, not a shared database)?

If so, PostgreSQL support makes sense for production deployments; SQLite is great for local/single-instance use but doesn't scale to k8s as you describe.

We'd welcome a contribution here. A couple of things to consider before diving in:

• SQLite should remain the default: zero-config local setup is important for the getting-started experience. PostgreSQL should be opt-in via config.
• Clean abstraction boundary: rather than scattering database-specific code, a storage interface that backends implement behind would make this maintainable and keep the door open for other backends in future. Worth opening a design PR or draft first so we can align on the abstraction before the full implementation.
• Migration parity: if the server supports both SQLite and PostgreSQL, schema migrations need to work across both dialects. Worth thinking through early.

If you're up for it, a good starting point would be a short design proposal (even just a GitHub comment) showing the proposed interface and how configuration would work.

Happy to give feedback on that before you write code.

[nissessenap]

Yes the server ;)
Cool then I will try to take a look tomorrow or the comming week on a design.

[peteski22]

#275