# Production DB Backup and Recovery (Supabase, Cost-Optimized)


## Visual Context

Canonical visual owner: [Operations Index](README.md). Use that map for the top-down system view; this page is the narrower detail beneath it.

This runbook defines production recovery readiness with daily logical backups
instead of PITR-gated deployment.

Scope:
- Production remains on Supabase.
- Backups are generated by Python (`pg_dump`) in Cloud Run Jobs.
- Backup artifacts are stored in GCS with lifecycle retention.
- Backup bucket is cost-hardened: UBLA + PAP enforced, versioning off, soft delete disabled.

---

## Recovery Profile (Cost-Optimized)

- Effective RPO target: `<= 24 hours` (daily backups)
- RTO target: `<= 2 hours` (validated by monthly restore drill)
- Retention target: `14 days`

---

## Enforced Controls

1. Daily logical backup cron (Cloud Scheduler -> Cloud Run Job):
- runs `scripts/ops/supabase_logical_backup.py`
- writes backup dump + manifest to GCS
- emits structured `backup.summary` log entry

2. Pre-deploy backup freshness gate in production deployment workflow:
- runs `scripts/ops/logical_backup_freshness_check.py`
- blocks deploy when latest successful backup is stale or missing
- optional manual predeploy job execution via workflow input `run_predeploy_backup_job=true`

3. Migration governance gate before production backend deploy:
- enforces monotonic numbered migration files in `consent-protocol/db/migrations`
- enforces the production-pinned contract in `consent-protocol/db/contracts/prod_core_schema.json`
- allows the local repo to be ahead of production while production remains pinned to its approved migration floor
- runs read-only live schema drift checks for production-critical tables/columns

4. Release evidence artifact:
- generates `prod_migration_release_manifest.json`
- records git SHA, operator, migration files/hash, and backup evidence fields:
  - `backup_object_uri`
  - `backup_checksum_sha256`
  - `backup_completed_at`
  - `backup_report_status`

5. Daily posture workflow:
- `.github/workflows/prod-supabase-backup-posture.yml`
- validates logical backup freshness and uploads JSON report artifact

---

## Runtime Config and Secrets

Provisioning bootstrap (idempotent):

```bash
PROJECT_ID=hushh-pda REGION=us-central1 bash deploy/backup/setup_prod_logical_backup.sh
```

Optional image override during bootstrap:

```bash
PROJECT_ID=hushh-pda REGION=us-central1 \
BACKUP_JOB_IMAGE=gcr.io/hushh-pda/consent-protocol:backup-job-YYYYMMDD-HHMMSS \
bash deploy/backup/setup_prod_logical_backup.sh
```

Required runtime env for backup job:
- `BACKUP_BUCKET`
- `BACKUP_PREFIX`
- `BACKUP_RETENTION_DAYS`
- `BACKUP_MAX_AGE_HOURS`

Required DB access:
- `DB_HOST`, `DB_PORT`, `DB_NAME` (env)
- `DB_USER`, `DB_PASSWORD` (Secret Manager)

---

## Local Verification Commands

Run logical backup (manual):

```bash
python3 scripts/ops/supabase_logical_backup.py \
  --project-id hushh-pda \
  --db-host "$DB_HOST" \
  --db-port "${DB_PORT:-5432}" \
  --db-name "${DB_NAME:-postgres}" \
  --db-user "$DB_USER" \
  --db-password "$DB_PASSWORD" \
  --bucket hushh-pda-prod-db-backups \
  --prefix prod/supabase-logical \
  --retention-days 14 \
  --report-path /tmp/prod-logical-backup-report.json
```

Run freshness gate (read-only):

```bash
python3 scripts/ops/logical_backup_freshness_check.py \
  --project-id hushh-pda \
  --bucket hushh-pda-prod-db-backups \
  --prefix prod/supabase-logical \
  --max-age-hours 30 \
  --report-path /tmp/prod-backup-posture-report.json
```

Note: the freshness checker uses `google-cloud-storage` and requires ADC-capable credentials
(`gcloud auth application-default login`) or service-account credentials.

Migration guard (read-only):

```bash
./bin/hushh db report-prod-posture
```

UAT uses a different contract because it is the latest integration lane:

```bash
./bin/hushh db verify-uat-schema
```

Local contract alignment check:

```bash
./bin/hushh db verify-release-contract
```

Generate release manifest:

```bash
python3 scripts/ops/generate_migration_release_manifest.py \
  --output /tmp/prod-migration-release-manifest.json \
  --environment production \
  --backup-report-path /tmp/backup-posture-report.json
```

---

## Restore Drill Cadence

Weekly integrity verification:
- confirm latest backup manifest is fresh and `status=ok`
- confirm backup object exists and checksum is present

Monthly restore drill:
1. Restore latest logical dump into isolated non-prod Postgres/Supabase target.
2. Run sanity checks:
- key table counts (`consent_audit`, `vault_keys`, `pkm_index`, `pkm_blobs`, `kai_market_cache_entries`, `tickers`)
- coherence checks (PKM blob/index integrity)
3. Record:
- drill start/end time
- observed restore duration (RTO)
- pass/fail and remediation actions
