ADR-095: Backup pipeline — daily pg_dump to Hetzner Object Storage with retention

Status: Accepted Date: 2026-04-25

Context

ADR-092 §"Data persistence" landed three persistence shapes for project databases (ephemeral, durable, off-host) and then explicitly punted on backups: "Persistence is not the same as backups. Even durable mode loses you the data to a corrupted volume or an accidental DROP TABLE. A separate backup pipeline (pg_dump to Hetzner Object Storage on a daily cron, with retention) is deferred to its own ADR; this ADR only covers 'does data survive normal deploy + provision lifecycle.'" That ADR is this one.

The risk surface today is concrete and asymmetric. Setting db.persistence: durable already buys us survival across hcloud server delete (the Hetzner block volume mounted at /srv/<project>-data re-attaches by name on the next provision), and across container recreation, host restart, and docker compose down && up. What durable does not buy us is recourse against four real failure modes, each of which the fleet has hit at least once across other systems already running: (1) Hetzner block-volume corruption — rare but documented in their incident history; the volume comes back read-only or zeroed and re-attach does not save you. (2) Application-level data loss — a buggy migration that runs ALTER TABLE ... DROP COLUMN against the wrong table, an admin-tool form (per ADR-093) that issues a destructive query without a confirmation gate, an operator typo in a psql shell during incident triage. The volume is healthy; the bytes are gone. (3) Logical corruption that isn't caught for hours or days — a code bug that writes wrong rows for a week before someone notices; "restore the volume" doesn't help because the volume's current state IS the bug. You need a historical snapshot to roll back to. (4) Accidental host destroy with the wrong project name — hcloud volume delete <wrong-name> removes the volume too. None of these are exotic; all of them are routine for any team that operates databases at all.

The fleet is already running mode: local + persistence: durable on warp tonight (warp-01, 10 GB Hetzner volume at /srv/warp-data) and distill is queued next with the same shape. By the end of the month several projects will have user-facing state worth preserving, and right now zero of them have a backup. The right time to lock the backup contract is before the second project lands on durable storage, not after the first incident — because retrofitting backups to N projects is N project-side migrations, whereas locking the shape now means project N+1 inherits backups by manifest declaration with no code change in the project repo.

The shape question is whether backups are a per-project concern (each project owns its dump cron, each project picks its own bucket layout, each project writes its own retention) or a host concern (the host pipeline runs one backup job per project on the box, with one bucket layout and one retention policy, configured from the project's deploy manifest). ADR-092's cut answers that for us: backups are operationally identical to observability scrape registration and security daemon configuration — a host-level concern that flexes per-project via the manifest. Per-project ownership would re-introduce exactly the conditional shape ADR-092 set out to kill.

Decision

Every project deployed with db.mode: local (or db.mode: shared) AND db.persistence: durable (or off-host where the project owns its DB) gets a daily backup pipeline owned by the host role. The pipeline pg_dumps the project's database to Hetzner Object Storage with N-day retention, configured via a new db.backup: block on the deploy manifest. Restore is a documented operator runbook, not a UI button. Off-host backups (where db.mode: shared and the data lives on dark-factory-db) are explicitly out of scope for this ADR — they are dark-factory-db's responsibility under whatever follow-up ADR makes shared mode operational.

The manifest shape:

project: warp
hostname: warp.gyrum.ai
db:
  mode: local
  persistence: durable
  volume_gb: 10
  backup:
    enabled: true              # default: true when persistence=durable, else false
    schedule: "0 3 * * *"      # cron, default "0 3 * * *" (03:00 UTC daily)
    retention_days: 14         # default 14
    target: hetzner-object-storage    # default; one of [hetzner-object-storage]
    bucket: gyrum-backups      # default; resolved per-environment by the host role

Defaults are tuned so the common case is zero-config: a project that says nothing about backups but sets persistence: durable gets daily 03:00 UTC dumps with 14-day retention to the fleet's shared gyrum-backups bucket, partitioned by project under s3://gyrum-backups/<project>/. A project that wants more (twice-daily, 90-day retention) overrides the relevant fields. A project that explicitly wants no backup despite durable persistence (rare; useful for a dev-only durable volume) sets backup.enabled: false and accepts the operational consequences.

Three rules follow from the cut and are non-negotiable:

Backup is a host concern, configured by the project manifest. The host role (extending the role ADR-092 already defines) installs a systemd timer per project on the box. The timer fires the cron schedule, runs docker exec <project>-postgres-1 pg_dump -U <user> <db> | gzip | s3cmd put - s3://<bucket>/<project>/$(date -u +%Y%m%dT%H%M%SZ).sql.gz, and emits a structured log line with status, duration, and bytes-written for the observability stack to scrape. Retention is enforced by a sibling timer that runs once daily and deletes objects older than retention_days from the project's bucket prefix. The project repo contains zero backup code; the project compose file is unchanged from ADR-092's shape.
Restore is a documented runbook, not a UI button. The ADR-093 tools surface intentionally does not gain a kind: restore-backup handler. Restore is destructive (it overwrites the live database with a historical snapshot, losing every write between the dump and now), order-sensitive (you almost always want to take a fresh dump first, then stop the application, then restore, then restart), and slow enough (minutes-to-hours on a non-trivial database) that a one-click affordance is the wrong shape — operators should be reading a checklist, not pressing a button under time pressure. The runbook lives at dark-factory/docs/runbooks/restore-project-database.md and the procedure is: (a) silence the project's alerts via Alertmanager (per ADR-088), (b) snapshot the current state to a pre-restore-<timestamp> object so the restore itself is reversible, (c) docker compose stop <project> to take the application offline, (d) s3cmd get the target dump, (e) gunzip | docker exec -i <project>-postgres-1 psql -U <user> <db>, (f) docker compose start <project>, (g) verify via the project's smoke checks, (h) lift the alert silence. Every step is a human decision; none is automated tonight. If a year from now restores are routine enough to want automation, that ADR can be written then.
Off-host backups are out of scope. A project running db.mode: shared (data on dark-factory-db.gyrum.internal) or db.mode: external (data on a managed Hetzner postgres or AWS RDS or developer laptop) has no DB bytes on its host; the host's backup role is a no-op for that project's manifest entry. Backing up shared-mode databases is dark-factory-db's responsibility under whatever follow-up ADR makes shared mode operational (the same ADR that defines per-project database+role provisioning, firewall rules, and noisy-neighbour mitigation). Backing up external databases is whoever runs that database's responsibility — definitively not the project host's job.

Consequences

What becomes easier:

A new durable-storage project gets backups for free. Setting persistence: durable in the manifest now implies backup.enabled: true by default. The operator does not write cron, does not pick a bucket, does not write retention logic — the host role does all of that from the manifest. The cost of "we should back this up" drops from "write and review a backup playbook" to a single manifest-line review during the deploy PR.
Recovery RTO is bounded by dump size, not by re-deriving state. A 14-day window of daily dumps at 14 retention slots gives the operator 14 distinct points to roll back to. Recovery of a 1 GB database from a Hetzner Object Storage dump is single-digit minutes (s3cmd get + psql restore); recovery from "we have no backup" is best-effort forensics on whatever logs and replays survived.
Blast radius of hcloud volume delete <wrong-name> collapses. The disaster scenario where the wrong volume gets deleted goes from "data is gone, period" to "data is gone, restore from yesterday's 03:00 dump, lose at most 24 hours of writes." The 24-hour window is a tunable; projects that need tighter RPO override schedule.
Observability is uniform across the fleet's backup story. Because backups are a single host-role pattern, one dashboard panel ("backup runs in the last 24h, by project, status, duration, bytes") covers every backed-up project. A failed backup (non-zero exit, missing object in the bucket, retention not enforced) becomes a single alert rule per ADR-024, not N rules across N project repos.
The dark-factory-db follow-up ADR has a precedent to follow. When shared-mode goes operational, its backup story can be written as a delta against this ADR ("same dump shape, same retention default, but the cron lives on dark-factory-db and dumps every project's database in one pass") rather than from scratch.

What becomes harder:

Hetzner Object Storage is now a hard runtime dependency for any durable-storage project. A backup target outage (Hetzner's S3-compatible service is region-bound and has had multi-hour incidents) means backups silently start failing for the duration. We mitigate by (a) the observability dashboard surfaces age-of-most-recent-successful-backup per project, so a multi-day outage gets noticed before the retention window closes, (b) the alert from rule above fires after 26 hours without a successful backup (one cron's worth of slack), (c) Hetzner Object Storage outages are correlated across the fleet rather than per-project, so a single page covers every project's missed backup. Cross-cloud redundancy (mirroring backups to a second provider's bucket) is deferred to a follow-up ADR if it becomes a real concern; not paying for it tonight.
Bucket access credentials are now a host concern. Each provisioned host needs an s3cmd config with the right Hetzner Object Storage access keys, scoped to write-only (and list, for retention) on s3://gyrum-backups/<project>/* and explicitly NOT read on other projects' prefixes. The host role provisions the credentials per-project from the fleet's vault (already used for GHCR auth per ADR-092). Credential rotation is on the same cadence as ADR-028.
Cost meter ticks up, slowly but unboundedly. Hetzner Object Storage at €5.99/TB/month, 14-day retention of a 1 GB database compresses to ~100 MB per day = ~1.4 GB per project per retention window = roughly €0.01/month per durable-storage project at this size. Trivial today; non-trivial when projects have multi-GB databases with longer retention. We accept this — backup cost is an order of magnitude below host cost, and projects with substantially larger databases will get manifest-side discussion of retention vs cost when their PRs land.
Restoring is operator-toil. Running the runbook end-to-end is 15 minutes of focused work; most of that is the human verification gates, not the bytes moving. We accept this on the YAGNI-for-restore axis: until the fleet has done a non-drill restore at all, we have no signal on which steps are friction worth automating versus which are gates worth keeping.
The manifest schema gains a recursive nested block (db.backup:) that the validator must handle. Implementation cost; not a fleet-design cost. ADR-092 already established the shape for db.mode + db.persistence + db.volume_gb; db.backup is a fourth sibling.

What we have signed up to operate:

A backup task within the host ansible role (extends the role ADR-092 already defines), parameterized over the manifest's db.backup block. Renders one systemd timer per project on the host plus one shared retention timer.
An s3cmd configuration template at dark-factory/ansible/templates/backup/s3cmd.conf.j2 populated from the host's vault (Hetzner Object Storage credentials).
A runbook at dark-factory/docs/runbooks/restore-project-database.md with the eight-step procedure above. Updated whenever a real restore happens and surfaces a friction point.
An extension of the gyrum-catalog/pkg/deploy.Manifest schema with the db.backup block + JSON Schema mirror + validator-CLI fixtures. Follow-up implementation PR; not part of this ADR.
A dashboard panel + alert rule for "backup health" — added to the platform dashboard (per ADR-015), one row per durable-storage project, with green/yellow/red on age-of-most-recent-successful-backup.

What we revisit:

If a project ever exceeds the 14-day default retention without manifest override (i.e. operators routinely change it on PR), the default is wrong and gets bumped fleet-wide. The signal is in the manifest review log.
If Hetzner Object Storage outages cause real data-loss exposure (i.e. an outage spans the retention window of an actual incident), cross-cloud redundancy goes from deferred to required and gets its own ADR.
If restore frequency crosses ~one per quarter, the runbook gets a tooling pass — likely a gyrum-restore-database <project> <dump-timestamp> script that wraps the eight steps, gates each with a confirmation, and reports progress. Still not a UI button; just a CLI affordance.
If the validator-mirror cost of nested manifest blocks (db.mode + db.persistence + db.volume_gb + db.backup.{enabled,schedule,retention_days,target,bucket}) starts producing schema bugs, the db: block factors into a typed substructure with its own test fixtures rather than living inline in the top-level manifest.

Alternatives considered

Continuous WAL streaming via pgBackRest (or wal-g, barman, etc). Lower RPO (minutes, not 24 hours), point-in-time recovery to any second within the retention window, smaller restore deltas. Loses on operational complexity at the fleet's current scale: pgBackRest is a moving system with its own daemons, its own configuration grammar, its own failure modes (broken streams, failed archives, missing WAL files), and its own monitoring story. The team's current pain is "we have NO backup" — daily pg_dump solves 95% of the actual risk surface for ~5% of the operational cost of streaming. Reconsider when (a) a project's RPO requirement is genuinely sub-day (no project's is today), or (b) the fleet has a dedicated DB-ops person whose time it's worth spending. Until then, daily dump is the right shape.
Managed Hetzner postgres (or AWS RDS, or any cloud provider's managed postgres) for durable-storage projects. Provider owns backup, point-in-time recovery, replication, the lot — the project just gets a connection string. Loses on cost and on the cut: managed postgres is €15-30/month per project (vs ~€0.01/month for our manifest-driven dumps), most of which is paying for HA features (multi-AZ replication, automatic failover) that nobody on the fleet currently needs. Also re-introduces a per-project conditional shape (some projects use managed pg, some use local pg, the manifest has to branch) that ADR-092 set out to kill. The right time to consider managed postgres is when a project's HA requirements genuinely cross "single-AZ host with daily dumps is fine" — not before. When that day comes, db.mode: external is already the escape hatch.
No backup at this scale (the YAGNI-on-backup framing). The argument: at fleet size and project scale, the cost of building and operating the backup pipeline outweighs the expected loss from incidents that haven't happened yet. Loses on asymmetry: building the pipeline once costs ~one engineer-day (host role task + manifest schema + runbook); the cost of one realized incident without backups is at minimum days of forensic work and at worst the project's data, period. Backup is the canonical example where YAGNI is the wrong heuristic — the pipeline is cheap, the disaster is expensive, and the disaster has nonzero probability per project per year.
Per-project ad-hoc backup (each project's repo ships its own backup container or cron entry in its compose). Simpler in some axes — the project owns its backup story end-to-end, no cross-cutting host role. Loses on every dimension this ADR has named: each project re-invents the dump shape, each project picks its own bucket layout (or worse, doesn't pick one), retention drifts per-project, the fleet's observability dashboard fragments into N panels, and the second project on the same host (per ADR-092) gets no benefit from backup work the first project did. Exactly the conditional-shape failure mode ADR-092 was written to kill.
Backup to the host's own block volume rather than off-host object storage. Cheaper still (no Object Storage fees, no external dependency). Loses catastrophically on the failure mode that motivates backup in the first place: a Hetzner block-volume corruption or a hcloud volume delete that takes out the project's data ALSO takes out the backup. Off-host storage is the entire point; on-host backup is a snapshot, not a backup.

Supersedes: none. Implements the deferred backup pipeline that ADR-092 §"Data persistence" called out by name. Extends the deploy manifest schema (per ADR-092 + ADR-093) with a db.backup: block. Operates within the host role pattern established by ADR-092 (host-owned, project-configured-via-manifest). The dark-factory-db follow-up ADR (when shared-mode goes operational) will reference this ADR as the precedent for its own per-project dump pipeline. Superseded by: {{leave blank until a later ADR reverses this one}}