Section 8 — Database Security, Migrations, and Backups

Companion to musehub-production-readiness-checklist.md.

Backups — verified live on staging via SSM: none exist

Checked directly on the staging instance:

crontab -l  → "no crontab for root"          # backup.sh's cron job is NOT installed
/opt/backups/musehub/  → one file:
  musehub_staging_manual_20260613_003724.sql.gz  (20 bytes, ~2 months old)
which rclone  → not found

One manual backup exists, it's ~2 months stale, and it's 20 bytes — almost certainly an empty or failed dump, not a usable backup. rclone (required for the R2 off-disk tier in backup.sh) isn't installed. This confirms docs/infrastructure.md's "No automated backup is configured yet" for the backup path specifically (unlike object storage, which turned out to be live — see Section 0). There is currently no usable backup of the production database anywhere.

Database security

• [x] Keep the database off the public internet. — confirmed (Section 5), no public exposure
• [ ] Encryption at rest / in transit — not verified in this pass (self-hosted Postgres in a Docker container; would need to check the container's TLS config and the underlying EBS volume encryption setting directly)
• [ ] Separate roles for application runtime / migrations / read-only / administration — not done. A single musehub Postgres role is used for everything: app queries, Alembic migrations, and admin psql access (per docs/infrastructure.md's documented psql command). This is a real gap worth closing before launch — migrations running with the same role as request-serving traffic means an app-level SQL injection would have migration-level privileges.
• [ ] Deletion protection — not enabled (this is an EC2+Docker Postgres, not RDS, so "deletion protection" means something different here: protecting the EBS volume/snapshot lifecycle rather than an RDS API flag — not configured either way)
• [ ] Audit logging — not enabled
• [ ] Monitoring (storage/CPU/memory/connections/locks/slow queries) — partially: slow_query_threshold_ms exists in musehub/config.py (app-level slow-query logging at the ORM layer), but there's no infrastructure-level DB monitoring (no CloudWatch Agent metrics confirmed, Section 0)

Migrations — mostly solid, one real risk found

• 75 Alembic migrations, run automatically as an explicit deploy step, before the blue/green slot swap (forward-compatible ordering — correct).
• Schema-parity gate exists and hard-fails the deploy: musehub.db.schema_gate compares live schema against ORM models after migrations run, filtering out benign diffs (alembic_version table, equivalent server defaults, column comments) and failing the deploy on real drift. This is a genuinely good safety net — better than most self-hosted setups have.
• ⚠️ Failure-handling risk: if alembic upgrade head fails, deploy.sh responds by running alembic stamp --purge head (discards Alembic's revision history and force-marks the DB as being at head) and retrying — with a comment claiming this is a safe no-op "when the schema already matches head." But the script doesn't distinguish why upgrade failed — a stale revision ID (the stated intent) and a genuinely broken migration look the same to this logic. The follow-up schema_gate hard-fail is the real safety net here, not the stamp-and-retry itself — worth explicitly documenting that reliance, since if schema_gate ever had a false negative, a broken migration could silently ship.
• [ ] Only one migration runner executes at a time — not independently verified (single deploy process today, so unlikely to collide, but no explicit lock mechanism confirmed)
• [ ] Back up before high-risk migrations — moot until backups exist at all
• [ ] Expand-and-contract discipline — not reviewed across the 75 migrations in this pass
• [ ] Rollback/forward-repair procedures — not documented
• [ ] Record migration state/duration, alert on failure — not done (no alerting exists, Section 12)
• [x] Older/newer app versions coexisting during blue/green — the pipeline's ordering (migrate before starting the new slot, old slot still serving on the old code during migration) implies this is intentional, but expand-and-contract discipline (the actual guarantee) isn't verified

The actual work items here

1. Get a real backup running — install rclone, configure the R2 remote, install the backup.sh cron job, and verify a real (non-empty, current) backup lands both locally and in R2. This is the single highest-priority item in this whole checklist — there is currently zero recovery path for the production database.
2. Perform an actual restore test once backups exist — checklist explicitly requires this before launch, not just "backups are running."
3. Split the Postgres role — at minimum, separate migration/admin access from the app's runtime role.
4. Define RPO/RTO (Section 0, still open) so backup cadence and retention can be sized correctly rather than guessed.