Persistence and backups
Postgres 16 is the system of record and holds the spend ledger. This page covers what is stored, the persistent volume, the restart proof, the nightly pg_dump shipped by restic, the restore drill, audit retention and what is not covered. Source decision: ADR 0006 (accepted by the owner 2026-11-15, applied and proven 2026-11-16).
What persists where
| Data | Store | Survives a restart | Backed up |
|---|---|---|---|
| Spend ledger and daily limits | Postgres usage | yes with the volume | volume file copy plus nightly dump |
| Sessions, messages | Postgres sessions, messages | yes | same |
| Kill-switch setting | Postgres settings, key agents_disabled | yes | same |
| Alert dedup | Postgres alerts_sent | yes | same |
| Gateway audit | Postgres gateway_audit | yes | same; no automatic purge |
| Factory jobs, steps, artifacts, approvals, events | Postgres factory_* | yes | same |
| CodeCrucible runs, mutants, baselines, cache | Postgres quality_* (migrations 0006, 0007 not run anywhere yet) | n/a | n/a |
| Workflow history | Shared Temporal cluster | per that cluster | not by this repo (Unverified) |
| Gateway registrations and tokens | gateway memory | no (fail closed, callers re-register) | no |
| API throttles | API memory | no | no |
Core tables after the first migration (11): usage, sessions, messages, settings, alerts_sent, gateway_audit, factory_jobs, factory_steps, factory_artifacts, factory_approvals, factory_events. Migrations 0006_quality and 0007_quality_submissions add quality_runs, quality_functions, quality_mutants, quality_baselines, quality_chunk_cache and quality_submissions. The full schema is in Data model.
The ledger is spend authority. A wipe resets the daily caps (client 2.00, global 5.00 USD on the core host) and drops sessions, so a Postgres without a volume is a financial control failure, not just data loss.
The volume (ADR 0006)
Context: Postgres first ran without a volume, so any restart dropped the schema and the spend ledger, and migrations had to be repeated.
Decision: add volume support to the hostinger apps role (volumes: [{containerPath, volumeName}], named volumes only), register Postgres as a persistent CapRover app, mount agentforge-postgres-data at /var/lib/postgresql/data, and add the volume to the host's restic list. Rejected: hosted Supabase Postgres (untested with the ledger's transactions through the pooler, adds internet latency and a credential) and leaving it as it was.
Applied 2026-11-16 by the hostinger session with the owner's go (hostinger PR #104):
agentforge-postgreswas deleted and re-registered as a persistent CapRover app (volumes cannot be added to a non-persistent app). This was a planned one-time wipe.- The volume
agentforge-postgres-datawas present, PGDATA initialised on it (base,global,pg_hba.conf, owner postgres, mode 700), service 1/1. - Restic list and volume-prune protection applied after the apply.
node dist/migrate.jsexit 0, 11 tables.- A second apply showed no drift (
Version.Indexunchanged). - Smoke test after the change: session create 201, message 202, turn
completed, close 202,/v1/usageowner spend 0.00037995 USD (a fresh ledger), 0 stale reservations, 0 running workflows.
Restart proof
Performed 2026-11-16 by the hostinger session with the owner's go, with no turn running (0 workflows Running in the agentforge-prod namespace at 13:41:41Z).
Row counts before and after: sessions 1, messages 1, usage 1, settings 0, gateway_audit 3, migrations 6. The named volume keeps schema and ledger across a task restart.
Pre-restart checklist that came from the dossier: confirm no turns are running with temporal workflow count on the namespace, then let the restart happen, then rerun the smoke test.
Nightly pg_dump with restic
Applied 2026-11-16 (hostinger PR #105, owner's go, after the restart proof passed):
| Item | Value |
|---|---|
| Script | /usr/local/sbin/pg-dump-backup.sh on the core host |
| Schedule | 03:40 (host clock; the timezone is not recorded in the source) |
| Command | pg_dump -Fc inside the container with its own environment (no credential on the host) |
| Shipping | streamed by restic to the host's own repo on off-host object storage |
| First snapshot | 33 KiB; pg_restore --list showed 76 entries |
| Restore check | a full restore into a throwaway Postgres container matched the live row counts (sessions 2, messages 2, usage 2, gateway_audit 6, migrations 6) |
Not done: alerting and log rotation for the dump log; behavior of the container selection during a rolling update. The file-level volume copy is a copy of a running PGDATA, so it is crash-consistent only. The logical dump is the consistent backup.
Recovery objectives: RPO is up to 24 h (nightly dump). RTO has not been measured on a real restore; the repository drill table records "UNVERIFIED".
Restore drill
The repository runbook (docs/deploy-api-coolify.md, section 4, OPS-1) defines the drill. It was written for a Coolify-managed backup; the actual backup is the restic dump above, but the procedure applies unchanged to a custom-format dump.
- Start the clock. Start a scratch Postgres 16 that is not production:
docker run -d --name af-restore -e POSTGRES_PASSWORD=scratch -p 5440:5432 postgres:16
- Download the backup and restore it. Stop the clock when the scratch database answers queries; that duration is the RTO.
pg_restore --no-owner --dbname postgres://postgres:scratch@localhost:5440/postgres --create <file>
Use psql -f instead if the backup is plain SQL.
- Run on both scratch and production; the two results must be equal:
SELECT count(*), SUM(COALESCE(cost_usd, amount_usd)) FROM usage WHERE created_at < '<backup time>';
- Record the backup time, backup age, both query results and the RTO in
docs/p2-findings.mdand in the runbook table. docker rm -f af-restoreand delete the downloaded file.
Status of the drill table in the repository: one row, all fields "UNVERIFIED". The restore that was performed on 2026-11-16 compared row counts of six tables (not the ledger sum query, and not timed).
After a real restore the ledger rolls back to the backup time, so spend after it is not counted for the rest of that UTC day. Set AGENTS_DISABLED=true until the operator has reviewed the restored day, then set it back to false. See Observability and alerts.
The Coolify backup in the runbook (not used)
The runbook also describes a Coolify scheduled nightly backup to off-host S3-compatible storage and a cron-container fallback (pg_dump --format=custom --no-owner "$DATABASE_URL" > agentforge-$(date -u +%F).dump). That was never used; the hostinger restic job replaced it.
Audit retention
There is no automatic purge of gateway_audit. Keep 90 days with this operator query (monthly, by hand or a scheduled task) and watch the table size:
DELETE FROM gateway_audit WHERE at < now() - interval '90 days';
SELECT pg_size_pretty(pg_total_relation_size('gateway_audit'));
gateway_audit rows hold no prompts, bodies, tokens or headers; tool calls carry argsSha256, never arguments.
Migrations
Migrations are SQL files under packages/db/drizzle/ (0000_init, 0001_usage_checks, 0002_sessions, 0003_gateway, 0004_audit_target_len, 0005_factory, 0006_quality, 0007_quality_submissions) applied by node dist/migrate.js in the api image. The server never migrates at boot. CI guards drift: it regenerates with pnpm --filter @agentforge/db db:generate and fails on an Error line, a git diff in packages/db/drizzle, or any untracked change there (drizzle-kit exits 0 even when the schema import fails).
Fresh database lesson: a fresh database needs node dist/migrate.js once (the first smoke test failed with relation "sessions" does not exist). With a volume this is once per schema change.
Unverified
- Persistence across a full Postgres host reboot (only a task restart was proven).
- RTO of a real restore, and the ledger-sum comparison from the runbook.
- Temporal cluster backups (outside this repository).
- Alerting on a failed dump.
Sources
docs/sessions/2026-11-15-app03-deploy-and-smoke/adr/0006-postgres-persistent-volume.mddocs/sessions/2026-11-16-codecrucible-and-gateway-cidr/README.md,SUMMARY.mddocs/p2-findings.md("App-03 Postgres persistent volume (2026-11-16)", "App-03 Postgres volume restart proof and nightly pg_dump (2026-11-16)")docs/deploy-api-coolify.md(section 4, OPS-1 restore drill; migrations)docs/deploy-gateway-coolify.md(section 4, audit retention)packages/db/src/schema.ts,packages/db/src/stores.ts(isDisabled)packages/db/drizzle/*.sqlapps/api/src/migrate.ts.github/workflows/ci.yml(migrations drift step)