docs/adr/0014-postgres-event-store.md

# ADR-0014: Durable Postgres Event Store Behind the Ledger Interface

## Status

Accepted

## Context

The append-only event ledger is the single source of truth: all projections,
memory, and crash recovery derive from it (ADR-0001). `SQLiteLedger` (ADR-0013,
`docs/architecture/persistence.md`) made that log durable on a local file, which
is enough for one process on one disk. A hosted, multi-instance deployment wants
a managed database: durable across restarts, reachable by several workers, and
operated by someone else. Managed Postgres — e.g. Neon — is the natural target,
but the engine must not be coupled to a single vendor or to a database being
present at all: the suite has to stay green with no connection.

Two product requests shaped the implementation: use **SQLAlchemy** for the store,
and use the **`eventsourcing`** library. We evaluated `eventsourcing`'s
persistence primitives against our model and they do not compose cleanly (see
below).

## Decision

Add a durable backend *for* the ledger; do not replace the ledger. The `Event`
envelope and the `Ledger` interface are unchanged.

**Layered.** `SqlAlchemyLedger(Ledger)` (`src/core/sqlalchemy_ledger.py`) is a
drop-in backend that mirrors `SQLiteLedger`'s surface — `append`, `events`,
`reset`, `extend`, plus `snapshot_to` / `from_file` / `tail` / `latest_offset` /
`close`. Idempotency is a `UNIQUE` constraint on the event `id`; insertion order
is a serial `offset` column (not `created_at`, which is subject to clock skew, nor
`turn`, which repeats on retry) — the same guarantees as the SQLite backend. The
*same* SQLAlchemy code drives both Postgres and SQLite, so the backend is
exercised in CI against SQLite without a server, and the Neon path is
code-identical.

**The durable store is required.** A small factory (`src/core/ledger_factory.py`,
`make_ledger()`) constructs the backend from `DATABASE_URL` (`SqlAlchemyLedger`).
There is **no in-memory fallback**: with no URL resolved, `make_ledger()` raises —
the app persists to a real event store and refuses to run without one (this is
part of dropping the offline product mode; see ADR-0010). The store deps
(`sqlalchemy>=2.0`, `psycopg[binary]>=3` — the Neon driver) are therefore **core
dependencies** in `pyproject.toml`, not an optional extra. SQLAlchemy is still
imported lazily inside the backend (so `src.core.*` stays importable in minimal
contexts), but it always ships. Tests pass an explicit ephemeral `sqlite://` URL
as the mock store — a real `SqlAlchemyLedger` with no server.

**SQLAlchemy-direct, not the `eventsourcing` library.** `eventsourcing` is built
around DDD aggregates: its `StoredEvent` is keyed on `originator_id` +
`originator_version` (a per-aggregate sequence), reads are
`select_events(originator_id, gt=version)`, and event state is opaque serialised
`bytes`. Our ledger is a *flat envelope* with a single global insertion-ordered
log and idempotency by UUID `id` — not aggregate streams. Mapping onto its
recorder would force either one synthetic aggregate per run (conflating
idempotency-by-`id` with version-by-sequence and losing a clean global order) or
opaque blobs (losing the queryable, indexed `run_id` / `kind` / `actor` columns
the ledger relies on). That is the awkward aggregate model to avoid, so the lib is
**not** a dependency. SQLAlchemy is the right level for a flat event table.

*How `eventsourcing` could layer in later:* if a scenario ever needs true DDD
aggregates (per-entity invariants, optimistic-concurrency version checks), it can
adopt `eventsourcing` *above* this store — an `ApplicationRecorder` backed by the
same Postgres — while the flat ledger remains the cross-cutting source of truth.
The two are complementary, not competing.

## Consequences

- A hosted deployment points `DATABASE_URL` at Neon
  (`postgresql+psycopg://USER:PASSWORD@HOST/DB?sslmode=require`) and the durable
  log lives in managed Postgres; everything else is unchanged.
- A `DATABASE_URL` is required to run; `make_ledger()` raises without one. Tests
  pass an ephemeral `sqlite://` URL (a real `SqlAlchemyLedger`, no server) as the
  mock store, so the suite stays green with no database server and no network.
- `snapshot_to` is backend-agnostic (it replays the log into a destination
  ledger, default a SQLite file) since Postgres has no portable in-process backup
  API like SQLite's `.backup()`; a Postgres run can be checkpointed to a portable
  file that `from_file` reopens.
- `scripts/resume_run.py` and `modal_app.py` use `DATABASE_URL` when set and fall
  back to their local SQLite file otherwise, so their offline behaviour is intact.
- Caveat: the live multi-scenario UI shares one `DATABASE_URL` across scenarios
  (one `events` table). For isolated durable runs, use `resume_run.py`, which
  keeps one database per scenario. Multi-run/multi-scenario partitioning within a
  single store (e.g. filtering by `run_id`) is a follow-up.
- `pgvector`-based episodic retrieval and Postgres `LISTEN/NOTIFY` tailing (noted
  in `docs/architecture/persistence.md`) become possible now that the durable
  backend is Postgres; neither is built here.