Skip to main content

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​

DataStoreSurvives a restartBacked up
Spend ledger and daily limitsPostgres usageyes with the volumevolume file copy plus nightly dump
Sessions, messagesPostgres sessions, messagesyessame
Kill-switch settingPostgres settings, key agents_disabledyessame
Alert dedupPostgres alerts_sentyessame
Gateway auditPostgres gateway_audityessame; no automatic purge
Factory jobs, steps, artifacts, approvals, eventsPostgres factory_*yessame
CodeCrucible runs, mutants, baselines, cachePostgres quality_* (migrations 0006, 0007 not run anywhere yet)n/an/a
Workflow historyShared Temporal clusterper that clusternot by this repo (Unverified)
Gateway registrations and tokensgateway memoryno (fail closed, callers re-register)no
API throttlesAPI memorynono

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):

  1. agentforge-postgres was 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.
  2. The volume agentforge-postgres-data was present, PGDATA initialised on it (base, global, pg_hba.conf, owner postgres, mode 700), service 1/1.
  3. Restic list and volume-prune protection applied after the apply.
  4. node dist/migrate.js exit 0, 11 tables.
  5. A second apply showed no drift (Version.Index unchanged).
  6. Smoke test after the change: session create 201, message 202, turn completed, close 202, /v1/usage owner 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):

ItemValue
Script/usr/local/sbin/pg-dump-backup.sh on the core host
Schedule03:40 (host clock; the timezone is not recorded in the source)
Commandpg_dump -Fc inside the container with its own environment (no credential on the host)
Shippingstreamed by restic to the host's own repo on off-host object storage
First snapshot33 KiB; pg_restore --list showed 76 entries
Restore checka 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.

  1. 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
  1. 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.

  1. 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>';
  1. Record the backup time, backup age, both query results and the RTO in docs/p2-findings.md and in the runbook table.
  2. docker rm -f af-restore and 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.md
  • docs/sessions/2026-11-16-codecrucible-and-gateway-cidr/README.md, SUMMARY.md
  • docs/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/*.sql
  • apps/api/src/migrate.ts
  • .github/workflows/ci.yml (migrations drift step)