# Workflow storage Runs have to survive a restart. A proposal submitted at 17:00 and approved at 09:00 the next morning spans at least one deploy, and the in-process store loses everything in between — which is precisely the situation resumability exists for. ## Schema Two tables. Steps are rows rather than JSON inside the run, because monitoring asks for step duration, retry counts and where runs stall; as rows those are indexed queries, and as JSON they are a scan of every run ever recorded. ``` workflow_runs workflow_steps id TEXT PK run_id TEXT FK → runs, ON DELETE CASCADE workflow TEXT position SMALLINT ─┐ composite PK state TEXT name TEXT │ error TEXT tool TEXT │ context JSONB state TEXT │ version INTEGER attempts SMALLINT │ created_at TIMESTAMPTZ output JSONB │ updated_at TIMESTAMPTZ proposed JSONB │ archived_at TIMESTAMPTZ amendments JSONB │ approved BOOLEAN │ approved_by TEXT │ …timings ┘ ``` `id` is `TEXT`, not `UUID`. It is whatever the caller set — today a uuid4 string, but a column that rejects anything else turns a caller's choice into a runtime error at the worst moment. ## Two rules the store enforces **A save is one transaction.** Run and steps move together or not at all. A run whose state says `completed` above steps that say `pending` is a corruption no later read can detect. **Every save asserts the version it read.** Two processes that both loaded a run cannot both write it; the second gets `ConcurrentUpdateError`. Losing that race is recoverable — read again and retry — and the alternative is one process silently discarding a completed step another had recorded. `InMemoryRunStore` enforces the same rule, and returns copies rather than the objects it holds. That is not ceremony: a store with no concurrency semantics makes every test pass that would fail against Postgres, and the difference surfaces first in production, after a restart. Adding it immediately exposed three tests that had been relying on shared-object aliasing. ## Archival `archive(older_than)` sets `archived_at` on finished runs. An `UPDATE`, never a `DELETE`: the run is the account of what happened to a client's document, and the reason to stop loading it is volume, not irrelevance. Unfinished runs are never archived, however old. A proposal that has waited three months is exactly what the overdue check exists to surface; archiving it would hide work nobody has done. Indexes are partial — `WHERE archived_at IS NULL AND state IN (…)` — so they hold the working set rather than every run the installation has ever executed, which is the set that grows without bound. ## Migrations Plain SQL files in `app/database/migrations`, named `0001_description.sql`, plus a small runner. The trade against Alembic is real and worth naming: no autogeneration, no downgrade path, and the runner is ours to maintain. Alembic without the SQLAlchemy ORM is awkward, and pulling in an ORM to issue DDL for two tables is a large dependency for a small job. Migrations here are hand-written and forward-only, which for software shipped to installations nobody babysits is arguably the safer shape — a `down` nobody tested is not a rollback plan. Three rules the runner enforces: - **A misnamed file is an error, not a skip.** A migration silently ignored because of a typo is a schema change that ran everywhere except one installation, and was noticed only when something else broke. - **Two files at the same version are refused.** They have no defined order between them, so which ran first would depend on the filesystem. - **An edited migration is refused.** A file whose checksum no longer matches what was recorded produces two installations with the same version number and different schemas. The fix is a new migration; editing is the mistake. One transaction per migration, not one around all of them. A single transaction looks safer and is worse: PostgreSQL rolls back the whole batch on any failure, so a long migration that succeeded is undone by a typo in the next one. ## Testing `tests/test_run_store_contract.py` is one suite run against **both** stores. The in-process one runs always; PostgresRunStore runs when a database is available: ```bash TAXPILOT_TEST_DSN=postgresql://localhost/taxpilot_test python -m pytest ``` Writing it this way is the point. A durable store tested only by its own bespoke tests, against semantics nobody wrote down, drifts from the one everything else was developed against — and the drift appears in production, after a restart. `tests/test_serialisation.py` covers the mapping exhaustively, with no database. It includes an introspection guard: every field on `WorkflowRun` and `StepRecord` must appear in its row. A field added later and forgotten here loses data silently, and only after a restart, by which point what it lost is a client's document that nobody filed. ### Verified against a real database `PostgresRunStore` and `0001_workflow_runs.sql` have been run against PostgreSQL 16.10. The full contract suite passes on both implementations — 44 tests, no skips — and the schema was inspected afterwards rather than inferred from green tests: three tables, the composite step key, and both partial indexes with their `WHERE` clauses intact. `python -m app migrate` applies cleanly to an empty database and reports "already up to date" on a second run. ## Running PostgreSQL locally Portable binaries, no service, no registry: ```bash C:/pgsql/bin/pg_ctl.exe -D C:/pgsql/data -l C:/pgsql/server.log -w start C:/pgsql/bin/pg_isready.exe -h 127.0.0.1 -p 5433 ``` Listening on **127.0.0.1:5433** — not 5432 — so it cannot collide with anything installed later. Credentials are `postgres` / `postgres`, local development only. ```bash TAXPILOT_TEST_DSN=postgresql://postgres:postgres@127.0.0.1:5433/taxpilot_test \ python -m pytest ``` ### ⚠️ It must start with a clean PATH Laragon puts PHP and MySQL on `PATH`, and both ship `libiconv-2.dll`, `libintl-*.dll` and `libcrypto-*.dll` — the same names PostgreSQL loads. With Laragon's PATH inherited, child processes die at DLL initialisation: ``` server process was terminated by exception 0xC0000142 ``` `initdb` succeeds, the postmaster starts and logs, and then every backend dies — which looks like a corrupt installation and is not. Start it with ``` PATH=C:\pgsql\bin;C:\Windows\system32;C:\Windows ``` and it works. Worth knowing before spending an hour re-downloading binaries. A second trap: editing `postgresql.conf` with PowerShell's `Set-Content -Encoding utf8` writes a BOM, and PostgreSQL rejects it with `syntax error in file … line 1`. Write it without one.