P2: Prove production recovery and retention after business acceptance

[tomqwu]

Current Priority Decision

The Church/Basketball business milestone was accepted in #289 on 2026-09-15. Recovery and retention follow the authorized staging artifact and owner policy decisions.

Execution lane: phase: platform-later. Status: waiting for a target and owner-approved recovery/retention policy.

Completed

Owned fictional SQLite recovery exercises cover local application continuity. They do not prove production PostgreSQL backup, restore, aging or purge behavior.

Remaining

The owner must approve RPO, RTO, retention periods, key custody and legal-hold behavior. On an isolated authorized environment, prove scheduled backup aging, restore to a separate target, alerts, hold/cancel/restore edge cases and measured RPO/RTO. Never use production/customer data or destructive shared-environment operations for this evidence.

All tests and code review remain local. GitHub Actions does not run tests, and Ollama is not a code-review provider.

---

Current Implementation Handoff

Prepared 2026-09-13 for a lower-cost builder at source 21a4a804aa57580451edded04736b1f51aef7e48.
No CI checks. All implementation validation and code review run locally.
Use the shared builder contract and this issue's work packages; no xhigh model or automatic model upgrade is required. This is a detailed recommendation, not a claim that a smaller model cannot make mistakes or that tests have passed.

Risk/review focus: High: data loss, recovery and retention.
Start condition: WAITING ON OWNER POLICY AND TARGET. Do not run destructive retention or restore operations against shared or real data.

Source of Truth and Current State

• scripts/backup_database.sh
• scripts/restore_database.sh
• api/routers/organizations.py
• api/models.py
• docs/RUNBOOK.md
• docker-compose.yml
• alembic/versions/

SQLite backup raw-copies the DB while WAL may contain committed changes; restore targets roster.db. Organization cancellation records dates but those dates are not proof that retention/purge is operational.

This handoff supersedes stale implementation statements in the background below. Preserve existing successful behavior and tests. Recheck the current branch before editing; the baseline is a source pointer, not permission to discard newer changes.

Dependencies and Ownership

• Current execution prerequisite: owner reactivates this deferred track after P1: Accept complete Church and Basketball day-to-day business flows #289. Original provider/platform dependencies are retained as future acceptance, not blockers for the current business flow.
• Coordinate, but do not wait for entire tickets to close merely to inspect/write isolated tests: P2: Complete organization lifecycle and PostgreSQL release acceptance #253, P1: Make workflow notifications reliable; defer multiworker rollout #266, P2: Add production monitoring after business-flow acceptance #267, P1: Make maintenance and migration tools safe and current #283, P2: Reconcile platform assurances with later release evidence #284.
• The final commands below require P1: Repair local test entry points, coverage and isolation #281's safe local environment or an equivalently verified isolated setup. A missing dependency blocks only the slice that needs it; document that state.
• Shared source/ORM/migration changes are serialized per the master roadmap. Do not start concurrent edits to the same ownership area.

Recommended Decisions

1. Use SQLite's backup API or supported consistent snapshot, never cp a live WAL database. PostgreSQL backup uses a supported dump/snapshot/PITR path appropriate to the chosen host.
2. Restore into a new owned destination, verify integrity/migration head/row relationships, then run application acceptance before any owner-approved cutover. Keep the original intact; no automatic overwrite.
3. Suppress/reconcile queued side effects after restore to prevent re-sending old notifications or billing actions. P1: Make workflow notifications reliable; defer multiworker rollout #266 must distinguish pending, completed and uncertain intents.
4. Document retention across primary data, audit records, logs and backups. RPO<=24h/RTO<=4h are proposals only until approved and measured; never silently purge customer data.

Small Work Packages

Each item is one reviewable slice, not permission for one giant PR. Add the failing regression first; finish code, tests and affected docs for that slice together. Leave this issue open until all packages and original acceptance criteria are satisfied or explicitly revised by the owner.

• 268.1: Add source/target ownership and dry-run/refusal tests with synthetic paths; repair consistent backup creation under P1: Make maintenance and migration tools safe and current #283 safety primitives.
• 268.2: Create encrypted/checksummed backup metadata and restore-to-new-destination workflow; test corrupt/truncated/wrong-schema inputs.
• 268.3: Run a representative restored Church/Basketball dataset through auth/isolation/publication/inbox checks and record measured loss window/time.
• 268.4: Implement approved retention behavior and backup aging with explicit audit, hold/cancel/restore edge cases; write the drill record and P2: Reconcile platform assurances with later release evidence #284 runbook.

Required Regression Cases

These are specifications for tests to add/retain, not claimed execution results. Each new negative case must assert unchanged unauthorized state and zero forbidden side effects.

• T268-01: A committed SQLite WAL row survives backup/restore; corruption or mismatched metadata is rejected before target cutover.
• T268-02: Restore refusal leaves existing/unowned DB untouched; interrupted operation can resume/abort safely without data loss.
• T268-03: Restored tenant A cannot read B; published/accepted/swapped assignments and migration version are preserved.
• T268-04: Expired retention candidate under a hold is not purged; cancellation restored before deadline remains active; restored jobs do not resend completed messages.

Local Commands and Evidence

Existing targeted commands (paths checked against the audit source; run only after the stated safe preflight):

poetry run pytest tests/api/test_org_lifecycle.py tests/api/test_org_soft_delete.py tests/integration/test_organizations.py -q

Also run the shared formatting/lint/touched-type/unit/full-suite and local review protocol from #252 for the final pushed revision. Add new targeted tests to these commands when implemented. Run API and browser tiers in separate processes. Native, PostgreSQL, image, provider and operator drills require their explicit environment; an unavailable tool/target is blocked/not run, never a pass.

Schema and Compatibility

Purge/hold metadata or recovery-ledger fields need reviewed migrations only after retention policy is approved. Do not use an irreversible downgrade for recovery.

Stop Conditions

Backup destination/encryption custody, retention/legal-hold rules, RPO/RTO and cutover/purge approval are external owner decisions. No destructive real-data drills.

After two failed focused repair attempts without new diagnostic evidence, stop the affected package and post the exact failure, commands, suspected boundary and needed decision. Do not silently broaden scope, weaken tests or upgrade models. A fresh local reviewer checks: Review WAL consistency, encryption/key handling, exact restore destination, rollback of failed restore and side-effect replay behavior.

Completion Receipt

• Work-package and regression IDs above map to changed files and actual results.
• Commands, versions, dates, pass/fail/skip/not-run counts, logs/screenshots and tested head/base SHAs are linked.
• A separate local review records findings and resolution; self-review is labeled if used and is not misrepresented as independent review.
• Affected docs/README/playbooks/screenshots and dependency/roadmap status are reconciled, not left as unnamed follow-ups.
• If implementation is authorized through PR/merge, GitHub reports mergeable and the shared local-evidence requirements are met; reviewer agents never merge.
• No hosted CI check, status attestation, Ollama reviewer, live provider action, deployment, real-data purge or store submission was introduced by implication.

Copyable Builder Prompt

First read this issue's Current Priority Decision and #289. Run only its NOW slice.
If this issue is deferred, report that state instead of starting the older package list.
Implement the next ready work package in tomqwu/SignUpFlow issue #268.
Read its Current Implementation Handoff and #252 Builder Handoff Contract first.
Inspect current source and preserve newer/unrelated changes. Start with the
package's failing regression, then complete code, local tests, local review and
affected docs/assets together. Do not skip acceptance or invent passing evidence.
No CI checks or Ollama code review. Do not deploy, activate providers, purge real
data or submit to stores. Stop and report unmet prerequisites or policy decisions.
Record the package/test IDs and exact reviewed/tested source SHAs before claiming done.

---

Earlier Audit and Acceptance Context

Current policy (2026-09-13)

No CI checks. Everything is validated locally. This includes code review,
formatting, lint, type checks, migrations, all test tiers, security scans,
artifact checks and mobile validation. Do not add hosted jobs, required CI
statuses, synthetic success checks or an Ollama reviewer. GitHub is for source,
PRs, issues and publication, not validation.

Record commands, environment, results, limitations and reviewed head/base SHAs.
Builders merge only with completed local evidence and GitHub mergeability;
reviewer agents never merge. Real staging/provider/device acceptance remains
required where applicable, driven by authorized local operator tools.
Historical evidence and older comments do not override this policy.

Parent roadmap: #252

Priority: P1, blocks core production pilot. Phase: C. Suggested owner: Platform/data owner. Original estimate (superseded; re-estimate remaining work): 3-5 engineering days.

Historical audit evidence (recheck against current source)

scripts/backup_database.sh copies the live SQLite file; runtime SQLite enables WAL, so that copy alone is not a demonstrated consistent backup. scripts/restore_database.sh targets SQLite. docs/RUNBOOK.md offers manual PostgreSQL dump/restore commands, but no scheduled encrypted off-site backup, measured restore drill or point-in-time recovery evidence was found. Organization cancellation stores retention/deletion dates in api/routers/organizations.py without proving an operational purge workflow.

Source baseline: GitHub main 214e3f3f17a582d5f9b2063be6872ea2b1d25714, audited 2026-09-09. Infrastructure/provider claims marked unverified require actual staging evidence.

Implementation plan

1. Have the data owner set retention, deletion, export, RPO and RTO requirements for the pilot. Proposed starting targets: RPO <=24h and RTO <=4h; confirm against customer needs and actual hosting capability.
2. Automate consistent PostgreSQL backups or provider PITR, encrypt and store outside the primary failure domain, restrict access, and alert on backup age/failure. If SQLite stays supported, use a WAL-safe backup method.
3. Create a restore command/runbook that targets an explicitly isolated database, verifies schema version and integrity, and records elapsed time plus lost-data window.
4. Prove application login, organization isolation, schedule retrieval and messaging intent after restore; prevent restored jobs from accidentally re-sending completed customer messages.
5. Specify and implement cancellation retention/purge/export behavior, including audit logs and backup aging, without claiming deletion earlier than the system can honor. Require explicit authorization for real customer-data purge.

Acceptance criteria

• A scheduled backup is restored to a separate environment and passes application-level integrity tests.
• Measured RPO/RTO satisfy owner-approved targets and backup failure alerts reach an operator.
• Backup access, encryption, retention and recovery ownership are documented.
• Cancellation, restoration, export and eventual deletion behavior match the published product policy, including backups.
• No test or restore command can silently overwrite a shared/production database.

Dependencies

#253, #260, #265

Validation

Restore a representative synthetic dataset after simulated primary loss, record timings and checksums/counts, run tenant tests against the restored DB, and attach the drill report.

Whole-repository audit scope (2026-09-13)

Baseline: 21a4a804aa57580451edded04736b1f51aef7e48. This addendum assigns full-scope follow-through; it is not a new test pass or production sign-off. No CI checks; all review and validation runs locally.

Review current backup/restore scripts plus the six ad-hoc data migration scripts and cleanup helpers before operational use. backup_database.sh raw-copies roster.db; restore replaces the working database; cleanup_maintenance.sh removes WAL/SHM files. #283 owns tool-level safety and explicit target/confirmation; this issue owns consistency, encryption, retention and actual restoration. Include interrupted restore, integrity/schema mismatch, orphan records, deleted/canceled tenants, replay-safe task recovery, backup aging and measured RPO/RTO. Never run a destructive drill against shared/customer data. #284 consumes the verified operator runbook.

Keep evidence and disposition synchronized with master roadmap #252 and documentation ledger #277. Close only after the remaining acceptance criteria have linked local results; a planning/audit note is not completion.

[tomqwu]

Merged local recovery foundation in PR #323 (main 0657971).\n\nEvidence:\n- Validated branch head: 5997d05\n- Full local suite: 1,827 passed, 21 skipped, 0 failed/errors, including 73 Playwright tests. Report: test-artifacts/local-validation/20260915T043918.866073Z-99569-97e92ba0/report.json\n- Recovery drill: 12 passed; WAL-consistent snapshot, authenticated encryption, corrupt/wrong-key rejection, no-overwrite isolated restore, current migration head, restored Church and Basketball acceptance, key destroyed, no provider credentials or cutover. Report: test-artifacts/recovery-drill/20260915T043900.855305Z-99446-e5e4eb97/report.json\n- PostgreSQL acceptance: 8 passed against ephemeral loopback PostgreSQL 16.14. Report: test-artifacts/postgres-validation/2026-09-15T043900.768290-0000-99453-c9777a87/report.json\n- A failed-first full run exposed Alembic disabling existing application loggers in-process; fixed and covered before the complete exact-head rerun.\n- GitHub reported MERGEABLE/CLEAN at the validated head. No hosted tests or Ollama review ran.\n\nStatus:\n- 268.1-268.3 and T268-01 through T268-03 are implemented locally.\n- Keep #268 open. Remaining: scheduled PostgreSQL/off-site backups, retention, key custody, approved RPO/RTO, operator acknowledgment, production restore rehearsal, and cutover policy. No provider access, deployment, paid activation, destructive cutover, or branch deletion occurred.

[tomqwu]

Current-main recovery refresh

The owned recovery drill was rerun after PR #337 against exact main cb77a9fd295880359055e8aecc96bbb9d868c19e.

• make test-recovery: 12 passed / 0 failed / 0 skipped.
• Report: test-artifacts/recovery-drill/20260915T204824.548972Z-93216-331f13a2/report.json.
• Encrypted WAL-consistent backup and isolated no-overwrite restore passed.
• Integrity check and migration head b0d3f6a8c1e2 passed.
• Restored Church/Basketball application acceptance passed.
• Measured local backup duration: 0.009 s; restore duration: 6.529 ms; recovery-point age: 0.009 s.
• Recovery did not start outbound delivery, no cutover was attempted, and the temporary key was destroyed.
• External providers were disabled and not contacted.

Keep #268 open. The run used fictional owned local SQLite and does not establish scheduled PostgreSQL/off-site backup, approved retention/legal-hold behavior, key custody, operator alert receipt, owner-approved RPO/RTO, or production restore/cutover.