Support pluggable persistent session stores for horizontally scaled server deployments

[kitadesign]

Overview

Persistent sessions are backed by the SQLite session store. That works well locally and
for a single instance, but it is hard to run docker agent serve api as a long-lived
server where more than one replica may serve requests — Kubernetes, Cloud Run, ECS.
Every replica would need the same SQLite file to resume and mutate the same session,
which requires a shared POSIX filesystem or session affinity; and container filesystems
are ephemeral, so a restart loses the history entirely.

pkg/session already exposes a session.Store interface, and #3771 moved the
file-backed open/recovery path into pkg/session/sqlitestore so the driver stays out of
the code-built embedder surface. That leaves a natural place for a second backend, and I
would like to add PostgreSQL there:

session.Store
 ├── InMemory              pkg/session
 ├── SQLite                pkg/session (+ pkg/session/sqlitestore)
 └── PostgreSQL            pkg/session/postgresstore   (new)

The runtime and API server keep depending only on session.Store. --session-db
behaviour is unchanged; a backend-neutral --session-store <URI> selects an
implementation, and the two are mutually exclusive.

Scope limitation, stated up front: a shared store alone does not make serve api
stateless. SessionManager keeps the live runtime, the SSE event log, the per-session
streaming mutex and the follow-up injectors in process, so /steer, /followup,
/resume, /elicitation, /events, /status and /queue still need the replica that
owns the turn. What a shared store buys is durable history, replica-independent listing
and new turns, and survival across restarts — with session affinity still assumed at the
deployment layer. I would rather be explicit about that boundary than imply this makes
the server stateless.

Motivation

The concrete case is serve api on Cloud Run with more than one instance. Instances are
disposable with ephemeral filesystems, so session history dies with the instance — not
just on scale-in, but on every deploy. The workarounds are pinning to one instance
(giving up availability) or putting SQLite on network storage, which SQLite documents as
unsupported.

Use cases

1. serve api on Cloud Run / Kubernetes with N > 1 replicas, where a redeploy or
   scale-in must not destroy session history.
2. A single long-lived serve api whose sessions survive a container restart without a
   persistent volume.
3. Several surfaces (serve api, serve a2a, acp) sharing one session database.

Proposed solution

Layout. pkg/session/postgresstore as a leaf package next to sqlitestore, so
pkg/session stays driver-free, plus a small URI→Store factory for the CLI. I would
extend e2e/dependencies_test.go to forbid github.com/jackc/pgx in the embedder
surface so the new driver cannot leak back in.

Concurrency. This needs the most care. Today the append paths build the next position
with an inline (SELECT COALESCE(MAX(position), -1) + 1 FROM session_items WHERE session_id = ?), and AddMessage, AddSummary and AddError run it outside a
transaction. There is no UNIQUE(session_id, position) constraint, only a plain index.
That is safe today because pkg/server's per-session streaming mutex serialises turns
within a process — not because the schema enforces it. With a network store that
protection is gone, so the PostgreSQL backend would:

• add UNIQUE (session_id, position) on session_items;
• run every append in a transaction that first takes SELECT 1 FROM sessions WHERE id = $1 FOR UPDATE, serialising appends per session while different sessions stay
  parallel, with a bounded retry on 23505 as a backstop;
• keep PersistCompaction atomic, including its ErrOriginMismatch behaviour;
• keep UpdateMessage last-writer-wins, matching SQLite.

Related: PersistenceObserver calls UpdateMessage once per streaming delta and
rewrites the full body each time, with no throttling. I originally wrote that this is
free against a local file; measuring it, that is not quite right — a local WAL fsync
costs about the same per call as a localhost PostgreSQL round-trip. The costs a network
store actually adds are the accumulated round-trips (one per chunk, so 1–3 ms each on a
same-region managed instance) and the O(K²) payload: persisting a 400-chunk, 8 KB
streamed message means roughly 1.5 MB on the wire, since every delta resends the whole
body. Some debouncing would be needed; I have no strong opinion on whether it belongs in
the store or the observer.

Migrations. The 27 sequential SQLite migrations net out to the current two-table
schema (several add a column a later one drops), so replaying that history has no value.
I would give the backend its own ledger starting at 001_initial_schema = the current
schema, keep the ErrNewerDatabase guard, and run it under pg_advisory_xact_lock so N
replicas starting at once do not race. One deliberate difference from sqlitestore.New:
on migration failure it must fail closed, never move-aside-and-recreate, which on a
shared database would discard other replicas' data.

PR sequence. Three reviewable changes:

1. A backend-neutral session.Store contract test suite, run against InMemory and
   SQLite. Worth having on its own even if the rest is rejected.
2. pkg/session/postgresstore satisfying the same suite, plus concurrent-append tests
   and the dependency-budget guard.
3. CLI wiring: --session-store and the existing construction sites, --session-db
   untouched.

No SQLite→PostgreSQL migration tool in this series; better as a follow-up.

Alternatives

• Session affinity, one replica per session. Helps availability; the history still
  dies with the instance's filesystem.
• SQLite on shared network storage (EFS, Filestore, NFS). Locking over NFS is
  unreliable and unsupported by SQLite.
• An external-store extension point only, PostgreSQL out of tree. Legitimate, and I
  would be happy with it — but the session.Store contract is currently defined only by
  two in-tree implementations that do not fully agree, so an external implementer has
  nothing to code against. The contract suite is the prerequisite either way.

Related issues

• refactor: cut heavy transitive deps (goja, expr, openai-go, go-git, sqlite) from the code-built embedder surface #3771 — moved the SQLite open/recovery path into pkg/session/sqlitestore; the
  extension point this builds on.
• Adopt a conflict-resistant strategy for session DB migrations from parallel branches #3968 — conflict-resistant session DB migrations. A backend on a fresh, independent
  ledger does not inherit the parallel-branch ID collision problem and can adopt whatever
  that issue settles on; its "fail closed, never an automatic reset" invariant is also
  what a shared database requires.

Additional context

The two in-tree stores do not currently satisfy a single contract: GetSession returns
the live stored object from InMemorySessionStore but a fresh copy from SQLite, and
AddSubSession embeds the child in memory but stores it as a separate row in SQLite.
Hence the split into a core suite and a persistent-backend suite rather than forcing
agreement in the same change. (Smaller thing noticed while mapping the schema:
AddSession's INSERT omits the starred column that UpdateSession, addSessionTx and
PersistCompaction all set, so adding an already-starred session loses the flag — happy
to fix separately.)

Cloud SQL needs no provider-specific code: pgx DSNs already express Unix-socket hosts
and private IPs, so the Auth Proxy stays a deployment concern.

Questions

1. Is supporting network-accessible persistent session stores desirable at all?
2. Should PostgreSQL live in-tree, or should core only expose a stronger external-store
   extension point?
3. Is --session-store <URI> the right shape, or would you prefer --session-postgres-dsn?
4. Is an independent PostgreSQL migration ledger starting from the current schema
   acceptable, and how should it relate to whatever Adopt a conflict-resistant strategy for session DB migrations from parallel branches #3968 settles on?
5. Is github.com/jackc/pgx/v5 acceptable confined to a leaf package, and how would you
   want PostgreSQL integration tests run in CI? I did not find a precedent for a
   database-backed test in the repo.
6. Is the scope boundary acceptable (store only, affinity assumed), or would you want the
   runtime-state side addressed in the same effort?

Happy to start with the contract test suite so the abstraction work can be reviewed
before any PostgreSQL code lands.