# Section 8 — Database Security, Migrations, and Backups

> Companion to [`musehub-production-readiness-checklist.md`](../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.