Reliability & Ops
Backup & Disaster Recovery
A daily, durable, restorable backup of D1 and Hermes SQLite to R2 — plus a tested rebuild runbook and break-glass kit.
Situation
The estate has no backup of its edge-canonical data. D1 holds conversations/messages/threads, memories, task_sessions, investigations — none derivable from the vault — and Hermes's Tower-side SQLite is likewise unbacked. The only existing export route emits derived daily summaries, not raw rows.
Cloudflare D1 Time Travel already gives in-account point-in-time restore (7 days free / 30 paid), but nothing for an off-platform artifact, a tested rebuild path, or account-loss/IdP-lockout. This spec supplies the missing pieces, built to the estate's existing Hub-job conventions.
Key decisions
R2 only, no off-Cloudflare copy
Account-loss is covered by the break-glass kit (Access/IdP recovery + encrypted secret backup) and the independently-hosted vault, so the D1 dump itself stays in a dedicated R2 bucket.
Hermes SQLite included now
A Tower-side job ships in this PR with HERMES_DB + a Tower CF token as TODO(alex) stubs, keeping D14's backup line in one sub-stream.
"Tested rebuild" = automated local restore + assert
An offline sqlite3 restore-verify runs in the existing test harness and proves the artifact is genuinely restorable; the runbook documents the real remote restore.
Retention: ~30 daily + ~12 weekly, pruned in-job
~3 months of coverage at bounded cost; an in-job prune is visible and offline-testable, unlike opaque R2 lifecycle rules.
Backup runs Hub/Tower-side via wrangler
wrangler d1 export is a control-plane op a Worker can't perform; running it off the Hub doesn't touch the edge-local invariant, which governs the request hot path only.
Architecture
VM Hub and Tower jobs export/dump, validate, gzip, and push to a dedicated mallory-backups R2 bucket with no Worker binding; a shared lib/backup.sh carries all the pure logic.
to_epoch, backup_key, prune_plan (daily+weekly tiering), validate_sql_dump, validate_sqlite_db — a silently-empty backup is a hard fail, never a quiet success.wrangler d1 export --remote → validate → gzip → r2 object put → prune → record_status + alert on any failure (page-worthy, unlike best-effort jobs).sqlite3 .backup (consistent online snapshot) → validate → gzip → R2 → prune; logs-and-skips cleanly until HERMES_DB is configured.d1/<date>.sql.gz and hermes/<date>.db.gz; no Worker binding — restore is operator-driven via wrangler.d1-backup/hermes-backup get a ~28h max_age_for so a silently-not-running backup shows stale/amber on the status surface.Risks & guardrails
R2 token scope (O1)
The .envrc CLOUDFLARE_API_TOKEN needs R2 Storage:Edit in addition to D1:Edit, or r2 object put 403s — the one external blocker.
R2 object listing for prune (O2)
Object listing has historically been thinner than put/get/delete in wrangler; fallback is a local manifest the job appends to and prunes against, so prune never depends on a remote ls.
FTS5 round-trip (O3)
FTS5 shadow tables don't survive export/import cleanly, so the runbook rebuilds indexes post-restore rather than trusting the dump.
Retention bug risk
prune_plan is called out as the highest-value test coverage — a retention bug would silently delete the backup you actually needed.
Delivery
PR-1a — mallory-runtime
lib/backup.sh, jobs/d1-backup.sh, tower/hermes-backup.sh, jobs/tests/backup.test.sh, config + README + status-surface wiring.
PR-1b — mallory (docs)
This spec plus docs/estate/06-backup-recovery.md (runbook + break-glass kit).
Offline test suite
jobs/tests/backup.test.sh covers prune_plan boundary cases, to_epoch, both validators, and a full restore-verify — all sqlite3-only, no wrangler, no network.
Held for Alex
R2 token scope, bucket creation, headless-wrangler-under-cron on the VM, Tower HERMES_DB config, and installing the two cron lines.
Out of scope
docs/superpowers/specs/2026-06-21-backup-and-dr-design.mdplan ·
docs/superpowers/plans/2026-06-21-backup-and-dr.md