Backup/DR runbook (Neon branch restore)

[Taleef7]

Context

The live Neon project (workwell-twh) has no documented backup/disaster-recovery runbook today — docs/DEPLOY.md's "Rollback" section covers redeploying a known-good container image, and separately notes Neon branches "can still be promoted from the dashboard if a data rollback is needed," but there is no tested, written procedure for restoring from a Neon branch/point-in-time snapshot, no defined recovery-point/recovery-time objectives, and no documented drill. Acceptable at demo scale (synthetic data, disposable); once real data is held, undocumented recovery is a compliance and operational risk, not just an inconvenience.

A backup/DR runbook needs to define: what Neon's point-in-time-recovery window actually covers on the provisioned tier, a step-by-step branch-restore procedure, RPO/RTO targets, and a periodic drill to prove the procedure actually works (not just that it's written down).

Spec to be written when scheduled — see docs/PRODUCTION_READINESS_2026-07.md.

[Taleef7]

Runbook is up — PR #290 (docs/BACKUP_DR_RUNBOOK.md)

Written against the live Neon project (read 2026-07-13), not assumptions. One finding is material:

The PITR window is 6 hours

history_retention_seconds = 21600. Point-in-time restore is the only recovery mechanism — there is no scheduled pg_dump, no snapshot schedule, no export. So a destructive change (bad bulk op, accidental DELETE, mis-scoped seed rollback) is recoverable only if noticed within six hours. Made in the evening, noticed next morning ⇒ unrecoverable.

Tolerable today (the demo data is mostly regenerable — see below). A compliance-grade incident the moment real data lands, which is exactly the transition PRODUCTION_READINESS_2026-07.md says must not happen by accident.

Three owner decisions

1. Raise history_retention_seconds — 7 days is the conventional floor with a human in the loop (may affect the storage bill, hence a decision not a task).
2. Add a nightly logical dump to object storage. ⚠️ This shares its blocker with Evidence upload: wire a managed S3/R2 BUCKET (currently ephemeral in-container fs) #167 — there's no managed bucket yet. Provisioning one bucket unblocks both evidence persistence AND DB backups, which makes Evidence upload: wire a managed S3/R2 BUCKET (currently ephemeral in-container fs) #167 materially more valuable than it looks on the M3 list.
3. Protect the production branch (protected: false today).

Also in the runbook

• Restore procedures — including the classic failure mode: if a restore mints a new branch, the connection string changes and you must repoint DATABASE_URL_TWH + redeploy, or the app keeps writing to the damaged branch.
• An RPO/RTO table (container loss and bad deploys are already RPO 0 — the reconciler and immutable image tags cover them; rows 4–5 are the gaps).
• What's regenerable vs what a backup actually protects: the 120k scale tenant, quality/trend history, VSAC sets, measures and directory are all re-seedable from code. The only non-regenerable rows are the audit ledger, real case state, and human person_links decisions — and they're small, which is a further argument for the nightly dump being cheap.
• A zero-risk restore drill on a Neon branch (owner step — needs the console). A runbook that's never been executed is a hypothesis.

[Taleef7]

Progress 2026-07-14 (PR #291 + owner ops):

• ✅ Second line of defence LIVE: backup-neon-nightly.yml — nightly pg_dump --schema=workwell_spike -Fc over the direct (de-poolered, param-normalized) connection → s3://workwell-twh-evidence/db-dumps/ (30-day lifecycle). First dump verified in S3 (workwell_spike-2026-07-14T162246Z.dump, 5.9 MB). Written by a dedicated IAM principal (PutObject on db-dumps/* only); the app user has an explicit deny on db-dumps/*.
• ✅ §6 drill EXECUTED on a live Neon branch: delete → PITR restore (^self@T0, requires --preserve-under-name) → rows returned, zero production impact.
• ⚠️ Finding: the 6-hour PITR window is the Free plan's MAXIMUM (API rejects 604800: "max: 21600") and Free allows zero protected branches (BRANCHES_PROTECTED_LIMIT_EXCEEDED). Runbook §2 items 1+3 therefore collapse into one owner/billing decision: upgrade the Neon plan (Launch = 7-day restore + protected branches). That decision is what keeps this issue open — good agenda item for the MIE hosting/BAA conversation (Q C14).