Weekly off-Neon pg_dump backup (24h PITR is not enough)

[Sootopolis]

Problem

Neon free plan retains only 24 hours of PITR history. If a bad write lands and isn't caught within 24h, there's no recovery beyond restoring whatever stale dump happens to be lying around. Disaster-recovery floor is too low.

Proposal

Weekly pg_dump -Fc from one of the laptops (or a small cloud VM) to local disk + optional cheap object store.

# Sunday 04:00 UTC
0 4 * * 0 PGPASSWORD=<...> pg_dump \
  -h ep-divine-shape-ab2735ig.eu-west-2.aws.neon.tech \
  -U ccas_owner -d ccas \
  --sslmode=require -Fc \
  -f ~/ccas-backups/ccas-$(date +\%Y\%m\%d).dump

• Custom-format dump (-Fc) → ~25 MB compressed for current data size.
• Exclude api_response_cache / api_response_body / api_fetch_failure (rebuildable) → keep dumps small.
• Retention: 4-8 weekly snapshots, then rolling delete. Decide after first few dumps.
• Optional: rsync to S3 / B2 / Backblaze for off-site copy.

Scope

• Documented cron entry + one-shot test of the command.
• Optional: a tiny shell script in scripts/ (e.g. backup-neon.sh) that wraps pg_dump and prunes old dumps.
• Optional: README section "Backups".

Out of scope: automated restore drills (later).

[Sootopolis]

Shipped in #84 (merged to `main`, squash commit `2a284ce`).

`scripts/backup-neon.sh` — weekly `pg_dump -Fc` to local disk; reuses the app's `DATABASE_URL`/`DB_*` config (password via `PGPASSWORD`, never argv); schema-only excludes for `api_response_cache`/`api_response_body`/`api_fetch_failure`; atomic temp+mv; retention prune (`CCAS_BACKUP_RETAIN`, default 6). README Backups section documents the cron line + restore command.

Smoke-tested against prod Neon: 9.3M dump, valid archive (202 TOC entries), exclusions verified. Cron not yet installed — that's the operator step (the README has the line).

Out of scope, left as follow-ups noted in the issue: off-site copy (rsync→S3/B2), automated restore drills, systemd-timer promotion.