Backup & Restore Procedure

Status: the backup is real and operating; the restore has never been tested

docs/runbooks/db-recovery.md (status line: "Operativ. Quartalsweiser Test verpflichtend") is the authoritative source for this procedure — this document restates it in ISO-procedure shape and adds the restore-test procedure that runbook's own checklist and decision log admit doesn't exist yet. Say this plainly: the nightly backup runs and has for some time; a full restore has never once been executed, tested, or verified. That is register/risks.yaml RISK-004 (inherent score 10, treatment: mitigate, status: open) and GOV-08 objective 2, target 2027-02-01.

1. Backup mechanism (real, operating today)

• What: nightly full pg_dump of the stratify-db container only — not the Loki

(supabase-db) instance, not the wait-what Ecosystem stack.

• Where it runs: against the self-hosted Supabase EU Postgres on the Hetzner box

(128.140.8.187), via scripts/hetzner-backup-postgres.sh.

• Where dumps land: /data/stratify/backups/stratify-<UTC-stamp>.sql.gz on the server.
• Retention: 14 dumps (STRATIFY_BACKUP_KEEP, default 14), rotated by the script.
• Schedule: nightly 05:30 UTC, via /etc/cron.d/stratify

(infra/hetzner/cron-stratify.example). Log: /var/log/stratify-backup.log.

• Stated SLOs: RPO ≤ 24 h (bounded by the nightly cadence), RTO ≤ 60 minutes. Before a planned

risky intervention, run the backup manually first to tighten RPO to effectively zero:

  ./scripts/hetzner-backup-postgres.sh

• Cloud PITR is a demoted legacy fallback — relevant only if some stack still runs on Supabase

Cloud, which Stratify production does not (cutover completed 2026-06-03).

2. Restore-test procedure — the actual gap this document closes

This is a drill procedure that has never been run, written so that when it finally is, it happens against a defined checklist rather than improvised on the day. Cadence: quarterly, per db-recovery.md's own stated intent ("Quartalsweiser Test verpflichtend") and its rotation table:

This procedure exists specifically to make the Q1-style full-restore drill happen for the first time and get it logged — db-recovery.md's own decision log has an open TODO row: "TBD | Erster vollständiger Hetzner-Restore-Drill abgeschlossen" (first full Hetzner restore drill completed). That row should be filled in as a direct result of running this procedure once, and re-filled at every subsequent quarterly cycle.

Steps — full restore drill (Q1-style)

1. Select the dump. Pick the most recent /data/stratify/backups/stratify-*.sql.gz.
2. Restore into a throwaway database, never into prod:

   gunzip -c /data/stratify/backups/stratify-<stamp>.sql.gz \
     | docker exec -i stratify-db psql -U postgres -d postgres_restore

1. Verify data integrity. At minimum:

• Row counts on a handful of high-value tables (subscribers, strategy_mandates, signals,

audit_log) are non-zero and roughly consistent with what's expected for the dump's timestamp.

• Schema matches — no missing tables, no failed statements during the restore (check

psql's own output for errors, not just exit code).

• Run `verifyChain()` against the restored `audit_log` table

(apps/web/lib/audit/chain.ts:101-137) — this is the specific integrity check this procedure calls out as mandatory post-restore, not optional. verifyChain() re-walks the table in pages of 1000 and reports { ok: true, rows: N } or { ok: false, firstBadId }. A restored backup whose chain doesn't verify is itself a finding — it means either the dump captured a mid-write state inconsistently, or (more concerning) the chain was already broken in production before the dump was taken. Either way, do not treat a passing restore as synonymous with a passing chain check — run both.

1. Time the drill. Record wall-clock time from "start of restore command" to "restore

verified" — this is the actual RTO measurement to compare against the ≤60 minute stated SLO. The stated SLO has never been empirically validated before this procedure exists; the first drill's timing is the first real data point for it.

1. Tear down the scratch database once verification is complete — it contains real production

data and should not persist longer than the drill requires.

1. Log the result. Write the drill outcome into db-recovery.md's decision log (date, what

was tested, RTO measured, verifyChain() result, any findings) and additionally into docs/compliance/isms/evidence/ as a dated record (see below) — the runbook's own log and the ISMS evidence trail should both reflect it, since the runbook is the operational source of truth and the evidence folder is what this ISMS points to as proof the control operates.

Steps — single-table restore drill (Q2-style)

1. Simulate a TRUNCATE scenario: restore the latest dump into a scratch DB (as above), then

pg_dump --table=<name> from the scratch DB and psql --data-only --table=<name> the result against a separate scratch target (never prod, even in a drill) to confirm the single-table extraction path actually works end to end.

1. Log the same way as the full-restore drill.

Steps — outage-failover drill (Q4-style)

1. Toggle STRATIFY_READ_ONLY=1 in a non-prod context (or during a genuinely low-traffic window

if tested against prod, with explicit prior sign-off — this drill is inherently more invasive than the DB-only drills above) and confirm server actions fail soft with the expected message rather than hard-erroring.

1. Log the result.

3. Real incident recovery scenarios (already documented, cited here for completeness)

db-recovery.md §3 documents five real scenarios this procedure doesn't restate in full — cited rather than duplicated, per this ISMS's general approach to operational runbooks:

• §3.1 Accidental DROP TABLE
• §3.2 Single-table data loss (accidental TRUNCATE)
• §3.3 Corrupted audit_log chain (P0 Compliance) — see PRO-08,

which generalizes this into the broader incident-response decision tree

• §3.4 Hetzner/Supabase host outage
• §3.5 Legacy Supabase Cloud PITR path (not applicable to current production)

Every real recovery event (not just drills) should be logged into audit_log directly (actorRole: 'system', eventType: 'ops.recovery_drill' for drills specifically, per db-recovery.md:109) in addition to the evidence-folder record below — this makes the recovery event itself part of the tamper-evident audit trail, not just a markdown note.

Evidence

Each drill (and each real recovery event) produces a dated markdown entry under docs/compliance/isms/evidence/, e.g. evidence/2026-MM-DD-restore-drill-q1.md, containing:

• Date, drill type (or real-event type), operator
• Dump used (filename/timestamp)
• Steps executed and their outcomes
• verifyChain() result (for any drill touching audit_log)
• Measured RTO vs. the ≤60 min stated SLO
• Any findings, and whether they were logged as a risks.yaml entry or a GitHub issue

Plus the corresponding row added to db-recovery.md's own decision log, and the audit_log entry per ops.recovery_drill above.

Review

Reviewed quarterly, aligned to the drill cadence table in §2 — each quarter's drill is itself the review trigger. If a quarter's drill is skipped, that skip should be logged as explicitly as a completed drill would be, not silently allowed to lapse.