Skip to main content

ADR-0105: Backup, restore, and point-in-time recovery

Date: 2026-07-12 Status: Accepted Deciders: Adrian (Soft4U BV), Claude Opus 4.8 Supersedes: none Milestone: M1-W2 (docs/plans/2026-07-11-platform-remediation-roadmap.md, findings D1 CRITICAL, D2 HIGH)

ADR numbers 0103 (mock-flag decomposition) and 0104 (app_env) are consumed by M0 and land via PR #283. 0105 is the next free number.

Decision context:

  • Latency: Zero on the request path — no application code changes. Postgres-side cost of archive_mode=on is one sequential 16 MB file copy per WAL segment, off the commit path (the archiver is a background process; commits do not wait on it). archive_timeout=300 forces a segment switch every 5 min, so an idle cluster writes ≤ 12 mostly-empty 16 MB segments/hour (~190 MB/h worst case, pruned by retention). Measured backup wall-clock: 15 s for a 703 MB cluster (4 databases), 9.5 s for 1,519 MinIO objects.
  • Dependency surface: No new application dependencies. No new services. Zero new Python packages. The toolchain is 12 bash scripts using binaries that already exist: pg_dump/pg_restore/pg_basebackup (invoked inside the existing Postgres container, so they always match the server version), gpg (host), and minio/mc (pulled on demand as a throwaway container). Nothing new runs in production. If an upstream yanks a release, nothing breaks — there is no upstream.
  • Debuggability: Every script prints a timestamped pass/fail line per step and exits non-zero on failure. Backups publish atomically (<stamp>.incomplete → <stamp>), so a directory without the suffix is always complete — a crashed run cannot leave a truncated backup that sorts as "latest". Each backup carries a MANIFEST.sha256 (detects bit-rot) and an audit_digest.txt (the oracle the restore is checked against). At 3am the failure is a named check in docs/runbooks/restore.md, not a stack trace.
  • Reversibility: Trivially reversible. The scripts are additive and touch no application code. archive_mode is one compose flag away from off (one container restart). Nothing in the running system reads anything this ADR introduces. Hours to undo: < 1.
  • Blast radius: Purely additive to the app (0 files in backend/app/ changed). The operational blast radius is real and is stated in Consequences → Negative: archive_mode=on introduces a new failure mode in which a failing archive_command causes Postgres to retain WAL indefinitely until the disk fills and writes stop. That is a deliberate trade — loudly stop over silently lose — but it is a new way to take the database down.
  • Alternative considered: Managed Postgres (RDS/Cloud SQL) with vendor-native PITR — rejected for now because no deployed environment exists (M2 owns that decision), and shipping nothing until M2 lands would leave the CRITICAL finding open for weeks. The toolchain is deliberately built so that M2 can delete most of it: pg_run() is a single indirection that switches Postgres invocation between docker exec and native binaries, so the same scripts run against a managed instance unchanged.

Context​

Four parallel audits on 2026-07-11 produced finding D1, the most severe in the entire register:

No backups. Nothing. Anywhere. No pg_dump schedule, no WAL archiving, no PITR, no snapshot policy, no restore procedure, no restore test.

This was verified, not asserted: the only pg_dump in the repository was scripts/export_contracts.sh — a schema export for documentation, not a backup.

The severity is not generic. It is severe relative to the product's own claims:

  • ADR-0064 enforces audit_events immutability at the database: a BEFORE UPDATE/DELETE trigger that raises for all roles, a REVOKE of UPDATE/DELETE from the application role, and FK ... ON DELETE RESTRICT so a case with audit history cannot be cascaded away. This is a genuinely strong control.
  • ADR-0069 guarantees AMLR 5-year retention and ships a regulator-ready case-pack export whose entire premise is a durable audit trail.
  • ADR-0021 claimed evidence bundles "benefit from existing backup, retention and access-control infrastructure." No such infrastructure existed. (M0-W4 struck that sentence.)

An immutable audit trail on an unbacked-up Docker volume is a claim, not a control. The trigger prevents tampering. It does nothing about loss. A single docker volume rm destroyed the exact artifact every compliance claim rested on.

Worse, docker-compose.yml puts one Postgres container in charge of four blast radii: the application schema, Temporal's workflow history, Keycloak's identity realm, and Langfuse. One volume, four catastrophes, zero backups.

What we found while building this that was not in the finding​

Three things surfaced during implementation that make D1 worse than written:

  1. There is no audit_events hash chain. The M1-W2 acceptance criterion says the restore should be "verified by replaying an audit_events hash chain." No such chain exists — the table has no hash, no prev_hash, no signature column. The criterion was unsatisfiable as written. Rather than redefine the term until it passed, we implemented what can be honestly computed (a deterministic content digest) and recorded the absence of a real chain as a tracked gap. See "Honest limits" below.

  2. A restore silently degrades audit immutability. scripts/create_app_role.sql — the script any restore naturally reaches for, because without it the app cannot connect — runs GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO trustrelay_app. ON ALL TABLES includes audit_events, re-granting the UPDATE and DELETE that migration 066 revoked. It exits 0. Nothing warns. The trigger still blocks the write so the system fails safe, but a three-layer control silently becomes a two-layer one. This is reproduced and asserted by pg_restore_verify.sh step 15a.

  3. A pg_dump taken as trustrelay_app would be silently incomplete. That role is RLS-constrained (ADR-0050), so it can only see one tenant's rows — and pg_dump would still exit 0. A backup that succeeds, restores cleanly, and is missing most of the data is the worst possible failure. The toolchain therefore dumps as the superuser, and says so loudly in the code.

Decision​

Ship a backup toolchain that works on today's docker-compose reality and lifts to a real environment unchanged, with three independent layers plus an executed restore test.

1. Logical backups — scripts/backup/pg_backup_logical.sh​

pg_dump -Fc -Z6 per database, GPG-encrypted, published atomically, with a MANIFEST.sha256 and the audit-trail digest captured at backup time.

pg_dumpall --globals-only runs first and is not optional. pg_dump does not contain roles; roles live in the cluster. The trustrelay_app role that the entire RLS model depends on exists only in the globals dump. Restore without it and every GRANT/REVOKE in the dump silently fails to apply, because the grantee does not exist.

Langfuse is deliberately excluded from logical dumps (large, fast-growing, reconstructible LLM telemetry) but is still captured by the cluster-wide physical backup.

2. WAL archiving + PITR — docker-compose.yml + pg_backup_basebackup.sh + pg_wal_ship.sh​

archive_mode=on, archive_timeout=300, archiving to a host bind mount outside the Postgres volume — which is what makes the archive survive docker volume rm postgres_data. pg_basebackup provides the PITR anchor; pg_wal_ship.sh encrypts spooled segments into the retained archive.

We did not judge PITR disproportionate, and the brief explicitly invited us to. The reasoning: it costs four Postgres flags and one bind mount, it is cluster-wide (so it covers Temporal's history and Keycloak for free), and it moves RPO from 24 hours to 5 minutes for precisely the artifact the compliance story depends on. Logical-only backups would have meant "up to 24 hours of audit events, decisions, and SAR records are simply gone" — which is not a defensible answer to a regulator, and is materially less than ADR-0064/0069 already claim. It is verified end to end (pitr_verify.sh, 9/9).

WAL segments contain row images, i.e. the same PII and the same audit rows as the database. They are encrypted with the same key as the dumps. Treating WAL as "just transaction logs" is how an entire dataset leaks.

3. Object storage — scripts/backup/minio_backup.sh​

Two layers, protecting against different things: bucket versioning (an overwrite or delete of an object becomes a recoverable delete-marker) and an encrypted off-box mirror (survives loss of the MinIO volume). Versioning alone dies with the volume; the mirror alone cannot recover a delete that happened between runs.

This script never deletes anything in MinIO. mc mirror runs without --remove, and there is no mc rm in the file. Verified reason: the tenant branding logos live at trust-relay-documents/{tenant-uuid}/branding/logo.png — inside the same bucket as the case documents, interleaved under tenant/case prefixes. Any "clean up the documents bucket" operation destroys every tenant's logo.

4. Encryption — fail-closed​

Backups contain the full PII surface and the entire audit trail. If no encryption recipient is configured, the scripts refuse to run rather than write plaintext. Asymmetric GPG is preferred (the backup host holds only the public key, so a compromised backup runner cannot read backups it has already written); a symmetric passphrase is a documented dev-only fallback.

5. Retention — grandfather-father-son​

30 daily / 12 weekly / 60 monthly. The 60-month tier is the AMLR Art. 77 five-year horizon. Keeping 1,825 daily full dumps to satisfy it would cost ~180 GB and be absurd.

6. The restore test is part of the backup job​

backup_all.sh --with-restore-test (weekly). A restore path exercised only during an incident is a restore path exercised for the first time during an incident.

Executed verification (2026-07-12)​

Not a design. Run, against the live 703 MB / 806-audit-row / 1,519-object dev stack.

TestResultWhat it actually proves
pg_restore_verify.sh17 / 17 passedA backup restored into a clean, empty cluster reproduces the schema (81 tables), the migration head, and the audit trail — with the content digest byte-for-byte identical (67cd657b…, 806/806 rows). All three immutability layers are proven behaviourally, not by inspection: UPDATE/DELETE as superuser raise the trigger's error; UPDATE as trustrelay_app raises permission denied (a different error — which is the oracle that tells you the GRANT layer, not the trigger, caught it); deleting a case with audit history raises the FK violation.
pitr_verify.sh9 / 9 passedRecovery to a chosen instant genuinely stops there: a row written before the target is present, a row written after it is absent. A restore that merely replayed to the end of the WAL would show both and would pass a naive "did the data come back?" check.
minio_restore_verify.sh5 / 5 passed1,519 objects restore into a clean MinIO and are readable through the S3 API, with the aggregate SHA-256 of every object matching the live store — not a file count, which proves nothing about content. The branding logo is asserted by name.
Footgun regression (step 15a)Confirmed realRunning create_app_role.sql after a restore re-granted arwd on audit_events — silently destroying immutability layer 2. reapply_immutability_grants.sql repairs it (15b).

RPO / RTO actually achieved​

Stated in docs/controls-register.md. Honestly:

ThreatRPOVerified?
Logical error (bad migration, dropped table, app bug)≤ 5 min (PITR)Mechanism ✅ pitr_verify.sh. Requires archive_mode=on — see activation state below.
Loss of the postgres_data Docker volume≤ 5 min (the WAL spool is a host bind mount, outside the volume)Mechanism ✅
Loss of the host diskTOTAL LOSS — no off-host copy exists.⚠️ This is the largest remaining hole. It is not closed by this ADR.
MinIO objects24 h (backup interval); in-place deletes/overwrites recoverable via versioning✅

RTO (measured machine time, current data volume): Postgres app database into a clean cluster 9 s; MinIO 1,519 objects ~13 s; cluster start ~30 s. A full provision-to-serving DR drill was not timed as one continuous run — the components are measured, the end-to-end figure is not. Stated target: ≤ 1 hour, unverified.

Current activation state — read this before trusting the RPO above​

archive_mode is on in docker-compose.yml but is NOT yet active on the running dev Postgres. It is a postmaster setting and requires a container restart, and the shared dev container was in use by another workstream; recreating it was not a risk worth taking to save a docker compose up -d postgres.

Therefore, on the dev stack as it stands today, the real Postgres RPO is 24 hours (logical backups only), not 5 minutes. PITR is verified but not yet switched on. One command activates it:

docker compose up -d postgres      # recreates with archive_mode=on

The controls register records this split honestly rather than claiming the better number.

Consequences​

Positive​

  • The CRITICAL finding is closed for the failure modes that actually threaten a laptop-hosted PoC: volume loss, logical corruption, accidental deletion.
  • The audit trail is now provably restorable — not "we have dumps," but a digest-verified round trip with all three immutability layers behaviourally asserted.
  • One cluster-wide physical backup covers all four blast radii (app, Temporal, Keycloak, Langfuse) in a single artifact.
  • The restore footgun is now caught by an automated test rather than discovered by an auditor. That was worth the whole exercise on its own.
  • Zero new runtime dependencies, zero application code changed.
  • Liftable: pg_run() is the single seam between docker exec and a managed Postgres.

Negative​

  • No off-host copy. The backups are on the same physical disk as the database they protect. A disk failure or a stolen laptop loses the database and every backup of it. This is the single largest remaining gap and this ADR does not close it. It needs a destination (S3/B2/rclone), which needs a deployed environment and a budget — M2. Until then, the honest statement is: we are protected against volume loss and human error, and not against hardware loss.
  • archive_mode=on introduces a new way to take the database down. A failing archive_command makes Postgres retain WAL indefinitely until the disk fills and writes stop. We chose "loudly stop" over "silently lose data," but this did not exist before. Until M2 ships alerting, the pg_stat_archiver.failed_count check inside pg_backup_logical.sh is the only thing watching.
  • The private GPG key currently sits on the same host as the backups, so that host can decrypt its own backups. Acceptable on a laptop; unacceptable deployed. backup_bootstrap.sh prints the escrow procedure but deliberately does not perform it — where the private key goes is an operational decision, not a script's.
  • No alerting. A backup job that stops running tells nobody. backup_all.sh exits non-zero and shouts to stderr, and until M2 that is the entire monitoring story.
  • The MinIO backup tars-and-encrypts the whole object set each run — it does not scale. Fine at 1,519 objects / 522 MB; wrong at 10⁶ objects. The real answer is MinIO bucket replication to a second endpoint with SSE-KMS, which needs a second endpoint (M2).
  • Neo4j and Redis are not backed up. Neo4j is rebuildable from Postgres via the 20-step ETL (a rebuild, not a loss — but neither automated nor timed). Redis holds breaker state and cache, which reset acceptably.
  • Backups are taken as the superuser, so the dump file is a complete, RLS-free copy of every tenant's data in one artifact. That is unavoidable (a tenant-scoped dump is an incomplete dump) but it makes the encryption key the single most sensitive secret in the system.

Neutral​

  • Bucket versioning is now enabled on trust-relay-documents and peppol-evidence. Deletes become delete-markers; storage grows with overwrite churn. At current volumes, immaterial.
  • The backup toolchain is bash, not Python. Deliberate: it must run when the application cannot, including when the Python environment is the thing that is broken.
  • Scripts are written for bash 3.2 (macOS ships it; no associative arrays). A backup script that only runs on the maintainer's Homebrew bash is not a backup script.

Honest limits — what this ADR does NOT claim​

  • There is no cryptographic hash chain on audit_events, and this ADR does not create one. Immutability rests on the trigger + REVOKE + FK. The digest computed here proves a backup/restore round trip preserved the trail byte for byte; it does not prove the trail was never tampered with in place before the backup was taken. Only a real append-only hash chain (each row committing to its predecessor) would prove that, and a superuser who drops the trigger can still rewrite history and leave no cryptographic evidence. Tracked as a follow-up, not claimed here. The roadmap's M1-W2 acceptance criterion should be amended: it asks to "replay a hash chain" that does not exist.
  • A full DR drill was never timed end to end. RTO components are measured; the total is an estimate and is labelled as one.
  • PITR is verified but not activated on the running dev stack (see above).
  • langfuse, Neo4j, and Redis have no logical backup. Stated, not hidden.

Alternatives Considered​

Alternative 1: Logical backups only (no WAL archiving / PITR)​

Nightly pg_dump, nothing else. Rejected. It delivers an RPO of 24 hours — up to a full day of audit events, officer decisions, and SAR records permanently gone. ADR-0064 and ADR-0069 already promise a durable 5-year immutable audit trail; shipping a 24-hour data-loss window while those ADRs stand would be silently delivering less than the documentation claims, which is the exact defect class ADR-0103 exists to eliminate. The brief explicitly invited this option if PITR were disproportionate. It is not: it cost four Postgres flags and one bind mount, and it is verified working.

Alternative 2: Managed Postgres with vendor-native PITR (RDS / Cloud SQL / Neon)​

Delete this toolchain; let the provider handle it. Rejected for now, adopted later. It is the right long-term answer and M2 should take it. But no deployed environment exists today, choosing a provider is M2's decision to make, and leaving a CRITICAL finding open for the weeks that takes is not acceptable. The toolchain is deliberately shaped so M2 can throw most of it away — pg_run() is the only seam that needs to change.

Alternative 3: pgBackRest / WAL-G / barman​

Purpose-built, battle-tested, with parallelism, incremental backups, and built-in retention. Rejected as disproportionate right now: each adds a new binary, a new config format, and a new operational surface to a single-container dev stack, to replace ~400 lines of bash whose entire job is to be readable at 3am by someone who has never seen it. This is a genuine trade and the scaling ceiling is real — the MinIO tar-and-encrypt step in particular does not scale. Revisit at M2 alongside Alternative 2; pgBackRest is the obvious pick if we self-host Postgres.

Alternative 4: Docker volume snapshots​

docker run --rm -v postgres_data:/src alpine tar czf …. Rejected. A tarball of a running Postgres data directory is not a consistent backup — it is a torn copy that may or may not restore, and you find out which during the incident. It also cannot do point-in-time recovery, and it is not portable across Postgres versions. It is fine for preserving a broken cluster before a restore (the runbook uses it for exactly that, §1) and for nothing else.

Alternative 5: Do nothing until M2 lands the deployment​

Rejected. The finding is CRITICAL today, on a machine that today holds real case data, real audit trails, and real PII. The probability of a laptop disk failing or a docker volume rm typo before M2 is not zero, and the consequence is the permanent loss of the artifact every compliance claim in the product rests on.