Review: P2 off-cluster postgres backup destination (DR, not just resilience)

Verdict: NEEDS_REFINEMENT

Template Completeness

• [x] Type — Feature
• [x] Lineage — discovered during #297 scoping, documented
• [x] Repo — forgejo_admin/pal-e-platform
• [x] User Story — present, names downstream stakeholders (Lucas, Marcus, agents)
• [x] Context — thorough, enumerates destinations and trade-offs
• [x] File Targets — present (but one is wrong; see File Targets section)
• [x] Test Expectations — present
• [x] Constraints — present (cost target, no-data-loss, scratch-env test)
• [x] Acceptance Criteria — 8 items, mostly verifiable (two problematic; see AC section)
• [x] Checklist — deferred to AC, acceptable
• [x] Out of Scope — present, sensible carve-outs
• [x] Environment — present
• [x] Related — present

Traceability

• [x] story:superuser-recover label — verified on project-pal-e-platform user-stories table: "I can recover from failures using documented SOPs. Every failure mode has a runbook."
• [x] arch:cnpg label — applied to board item
• [ ] arch note MISSING — arch-cnpg does not exist in pal-e-docs. [SCOPE] Create architecture note arch-cnpg documenting the CNPG operator, both clusters (pal-e-postgres, woodpecker-db), bucket (s3://postgres-wal/), and credentials (cnpg-s3-creds). Needed for future work on this component.
• [x] Forgejo issue — #299 open, body populated via template.
• [ ] SOP note MISSING — ticket AC references sop-postgres-restore, but no such note exists in pal-e-docs. [SCOPE] Create sop-postgres-restore as part of sibling ticket #298 (restore drill) BEFORE this ticket executes, so the AC "sop-postgres-restore updated with off-cluster steps" has something to update.

File Targets

• [x] pal-e-services/terraform/cnpg.tf — verified. Lines 123-145 contain the barmanObjectStore block with destinationPath = "s3://postgres-wal/" and endpointURL = "http://minio.minio.svc.cluster.local:9000". Matches ticket claim exactly.
• [x] pal-e-services/terraform/k3s.tfvars (gitignored) — exists, confirmed not tracked.
• [x] pal-e-services/terraform/k3s.tfvars.example — exists (103 lines).
• [ ] ISSUE — woodpecker-db destination is NOT modifiable from pal-e-services/terraform/cnpg.tf. Ticket says "every other CNPG cluster on the platform (today: woodpecker-db) should likely get the same treatment." But the woodpecker-db barmanObjectStore block lives in pal-e-platform/terraform/modules/ci/main.tf lines 86-88 (destinationPath s3://postgres-wal/woodpecker/). Touching both requires changes in TWO repos. Either: (a) narrow this ticket to pal-e-postgres only and file a sibling for woodpecker-db, or (b) broaden File Targets to include pal-e-platform/terraform/modules/ci/main.tf and mark both repos in Repo field. [BODY] Fix File Targets to reflect reality; pick (a) or (b).
• [ ] MISSING file target — cnpg-s3-creds secret + verify CronJob. Credentials are provisioned in pal-e-platform/terraform/modules/database/main.tf lines 47-56 (not pal-e-services as the ticket implies). The ticket says "New cnpg-s3-creds-offcluster k8s secret (or extend existing)" — the existing secret lives in pal-e-platform, so the off-cluster secret likely belongs there too. Also, lines 84-200 of the same file contain cnpg_backup_verify CronJob that currently checks pal-e-postgres and woodpecker prefixes against MinIO — it will need updating when destinations change. [BODY] Add pal-e-platform/terraform/modules/database/main.tf to File Targets with both the secret and CronJob call-outs.
• [x] sop-postgres-restore and convention-postgres-backup-destination — new pal-e-docs notes, not code files. Acceptable as deliverables.

Repo Placement

Dependencies

• #297 (P0 tf-state drift) — ticket says "apply through PR #297-style review-fix-Lucas-approve loop (and by then #297 is DONE so the apply is the canonical sop-platform-tf-changes flow)." This phrasing is aspirational, not a hard dependency. Parallel work is safe if this ticket's code changes are restricted to pal-e-services/cnpg.tf (which is the one file #297 is actively stabilizing). If #297 is still reconciling drift in the same file, a merge collision is likely. [BODY] Explicitly mark "blocked by #297" OR state "can run in parallel; rebase required if #297 moves cnpg.tf".
• #298 (restore drill) — ticket says sibling. Verified open. AC here depends on the validation-postgres-restore drill existing and passing. Hard dependency: #298 must produce sop-postgres-restore and a working drill before #299 can satisfy its AC "validation-postgres-restore re-run against off-cluster backup PASS." [BODY] Mark "blocked by #298 until sop-postgres-restore published."
• plan-pal-e-backup (existing active plan) — MAJOR OVERLAP. plan-pal-e-backup already exists with project-pal-e-backup and 7 phases. Phase 2 "Database Backups" is explicitly scoped as daily pg_dump to Backblaze B2 (decisions section: "Backblaze B2 recommended $6/TB", "Daily pg_dump over CNPG migration", "Unified bucket structure one bucket directory-per-service"). This ticket's approach (CNPG-native barmanObjectStore pointed at off-cluster S3) is a DIFFERENT technical approach. These two cannot both execute without a scope-overlap conflict. This needs a human decision before #299 advances: either kill plan-pal-e-backup Phase 2 (or kill #299), OR harmonize them as two complementary layers (pg_dump daily + WAL continuous streaming). [SCOPE] Ava + Lucas must reconcile plan-pal-e-backup Phase 2 vs. #299 before this moves to todo.
• No ticket blocked by #299 found — work can be sequenced freely within the dependency graph above.

Acceptance Criteria

• AC: "Cluster-loss simulation in a SCRATCH environment." Realistic concern: platform runs on a single archbox node. There is no "scratch environment" today. A realistic scratch target is either (a) a Hetzner VPS spun up for the drill (plan-pal-e-backup Phase 7 also assumes this), (b) a local VM or kind cluster, or (c) a docker-compose CNPG operator standalone. Without naming one, this AC is untestable. [BODY] Name the scratch environment (even provisionally — "Hetzner VPS per plan-pal-e-backup Phase 7"). Without this, a dev agent cannot satisfy the AC.
• AC: "Apply through PR #297-style review-fix-Lucas-approve loop (and by then #297 is DONE)." The parenthetical embeds a sequencing assumption. Either make it a hard dependency ("blocked by #297") or remove the phrase and just reference sop-platform-tf-changes. [BODY] Tighten.
• AC: "Follow-up ticket filed: same treatment for woodpecker-db." Good hygiene. Keep.

• Encryption at rest: ticket body says "Out of Scope — Backup encryption at rest (separate ticket if not already provider-default)." Given postgres holds PII (journal entries, user identifiers, feedback_funnel_requires_auth.md cites 4-hour PII leak), encryption-at-rest is NOT an acceptable "out of scope." Needs an explicit AC: "Chosen provider encrypts at rest by default (documented in convention-postgres-backup-destination) OR WAL/base-backup files are encrypted client-side (e.g., via --encryption flag or age pre-upload)." [BODY] Add encryption AC.
• Data residency: Providers enumerated (B2, AWS S3, R2, DO Spaces) span multiple jurisdictions. No residency requirement is stated. If Lucas wants US-only, say so; if "don't care," say that too. [BODY] Add a one-line data-residency constraint in Constraints (can be "US-only acceptable, EU acceptable, not PRC/RU" or similar).
• Cutover safety: AC "No data loss during the cutover" is stated but the mechanism is hand-wavy ("Existing in-cluster backups must keep working until the off-cluster destination is verified"). Concrete mechanism matters: does this mean dual-destination for N days (which CNPG doesn't trivially support), or swap-with-rollback, or something else? Classic footgun: if the new destination is applied and WAL archive to old destination stops immediately, PITR gap equals time-from-apply to first-successful-new-backup. [BODY] Spell out cutover: recommend "parallel base backup complete before old destination decommissioned; WAL archive gap tolerance documented."

Blast Radius

• Same bug elsewhere? Yes — every CNPG cluster on the platform has the same "backup-to-same-cluster-MinIO" problem. woodpecker-db confirmed (ci/main.tf). Future CNPG clusters will inherit it unless a convention (convention-postgres-backup-destination) lands to force off-cluster from the start. The convention-note AC is GOOD; it prevents regression.
• cnpg_backup_verify CronJob impact: pal-e-platform/terraform/modules/database/main.tf:84 runs daily checking MinIO backup freshness. If destinations move but this CronJob doesn't, alerting goes stale. Must be updated in lockstep. Flagged above in File Targets.
• Non-CNPG postgres (plain pods): basketball-api and mcd-tracker run plain Postgres pods, not CNPG. They have no backup today. Out of scope here (plan-pal-e-backup Phase 2 addresses them via pg_dump CronJobs). Note for Ava: the conflict with plan-pal-e-backup is that plan would get ALL databases (including plain pods) to off-site with one approach; #299 only addresses CNPG clusters.
• Cost ceiling realism: $5/month target — paledocs + twitch2kwager DB sizes small (well under 10GB). At Backblaze B2 $6/TB/month that's negligible (fractions of a cent). R2 has no egress but S3-API egress costs are zero on reads anyway. AWS S3 Standard-IA at $0.0125/GB would be ~$0.13/mo for 10GB. All named candidates fit the budget easily. Budget is not a meaningful constraint — decision driver should be operational simplicity and encryption defaults, not cost. [BODY] Tighten Constraints: replace "under $5/month" with "cost not a driver at current volumes; optimize for operational simplicity + encryption-at-rest default."
• Provider-selection sub-scope: Ava asked whether provider selection should be a sibling spike. Dottie view: no. Evaluating 4 providers for a narrow CNPG S3-API target is maybe 2 hours of doc-reading; does not need its own spike. But the first AC ("Provider chosen and documented in convention-postgres-backup-destination with rationale") implicitly IS the decision gate, and it needs Lucas sign-off before execution. Recommend marking the first AC as "review-fix-Lucas-approve gate before other AC start."

Decomposition Assessment

Recommendation

• [SCOPE] BLOCKER: Reconcile with plan-pal-e-backup Phase 2. Two active approaches to "off-site postgres backup" cannot both execute. Ava + Lucas decide: (a) kill plan-pal-e-backup Phase 2 and adopt the CNPG-native approach in #299, (b) kill #299 and use the pg_dump approach, or (c) both layers coexist (continuous WAL to off-cluster via CNPG + daily pg_dump to same bucket for extra safety).
• [SCOPE] Create arch-cnpg note in pal-e-docs documenting the CNPG operator, both clusters, bucket, credentials.
• [SCOPE] Create or coordinate sop-postgres-restore as part of #298 (sibling) BEFORE #299 executes, so #299 has something to update.
• [BODY] Fix File Targets: woodpecker-db backup config lives in pal-e-platform/terraform/modules/ci/main.tf, not pal-e-services/terraform/cnpg.tf. Either narrow ticket to pal-e-postgres only (sibling for woodpecker) or broaden File Targets + Repo to cover both repos.
• [BODY] Add pal-e-platform/terraform/modules/database/main.tf to File Targets (the cnpg-s3-creds secret + cnpg_backup_verify CronJob both live here and need updates).
• [BODY] Name the scratch environment for cluster-loss simulation (e.g. "Hetzner VPS, provisioned per plan-pal-e-backup Phase 7"). Without this, the AC is untestable.
• [BODY] Mark dependency on #298 as hard (AC "validation-postgres-restore re-run PASS" requires #298 to have delivered the drill).
• [BODY] State #297 relationship explicitly: either "blocked by #297" or "parallel OK, rebase if cnpg.tf moves."
• [BODY] Add encryption-at-rest AC (PII data; "out of scope" is wrong here).
• [BODY] Add data-residency constraint (one line in Constraints).
• [BODY] Spell out cutover mechanism (parallel dual-destination OR swap-with-PITR-gap-bounded). Classic footgun: WAL archive gap between old destination stopping and new destination starting.
• [BODY] Replace "$5/month cost target" with operational-simplicity + encryption-defaults driver.
• [BODY] Tighten AC about #297 — drop the parenthetical aspiration, just reference sop-platform-tf-changes.