MuseHub Database Architecture — Canonical Source of Truth

This is the authoritative reference for how MuseHub's database is provisioned across every environment. If any other doc (docs/infrastructure.md, docs/db-management.md, any production-readiness doc) disagrees with this one, this doc wins — update the other one to match. Never store secret values (passwords, full connection strings with credentials) in this file. Hostnames and instance identifiers are not secrets and are documented here; passwords are not, and live only in AWS SSM Parameter Store.

Current state (as of 2026-09-08) — verified live, not assumed

Historical note: this doc originally described an inconsistency between staging (RDS since 2026-04-05, previously undocumented) and production (self-hosted Postgres). Multiple docs (docs/infrastructure.md, production-readiness Sections 5/8/12) stated "self-hosted PostgreSQL on EC2, no RDS" as if it were true everywhere — accurate for production only. See "How this was discovered" below for the original investigation.

Update, 2026-09-08 (Phases 0–2 complete): both environments are now on managed AWS RDS. Staging's secrets pipeline (deploy/secrets.sh) was fixed first (Phase 1) and redeployed/verified healthy. Production was then migrated (Phase 2): a new, encrypted-at-rest RDS instance was provisioned, data migrated via pg_dump/pg_restore (74 tables, 6 repos, 5 identities — verified matching on both sides before cutover), DATABASE_URL wired through SSM the same way as staging, and production redeployed and verified live (https://musehub.ai/healthz → {"status":"ok","db":true,"storage":true}, plus a real repo-list query against the live API to confirm actual data, not just a health-check ping). The old production self-hosted container was archived (final pg_dump to /opt/backups/) and stopped — retained, not removed, through the 2+ week safety window (target: ~2026-09-23). See Phase 2 below for full detail.

How this was discovered, and what it means (2026-09-08 investigation)

While verifying Section 5 (Production Architecture) for production-readiness, a routine deploy to staging failed at the migration step (DATABASE_URL not found). Investigation found:

1. Staging's currently-active app container (musehub-green) was started 10 days earlier (2026-08-29, during rollback testing) and has been running continuously since — it was never redeployed after today's secrets migration (#156) rewrote .env.

2. That container's environment has a real DATABASE_URL pointing at musehub-staging-db.<account>.us-east-1.rds.amazonaws.com — an RDS instance that has never appeared anywhere in this project's docs, IAM inventory, or cost tracking.

3. RDS was created 2026-04-05 — the same day as the original musehub-infra/musehub-app IAM users — meaning it's part of staging's original architecture, not a stray leftover.

4. musehub_postgres (the Docker container everyone, including multiple past agent sessions, assumed was staging's database) has been stopped for 2 months (Exited (0)), yet it has real accumulated data — meaning self-hosted Postgres genuinely ran on staging for some period before silently reverting to (or never actually leaving) RDS. Nobody noticed because zero monitoring or alerting existed on staging until #160 (2026-09-08).

5. Both databases had real, divergent data:

   | | RDS (live) | Docker musehub_postgres (frozen 2mo ago) | |---|---|---| | repos | 28 | 35 | | identities | 8 | 2 | | commits | 11,555 | 2,116 |

   Investigated the 7-repo difference directly: 33 of the Docker container's 35 repos are disposable benchmark artifacts (bench-clone-xs-*, bench-push-xs-*, etc.) from a single test run on 2026-05-13. The only 3 non-benchmark repos (aaronrene/knowtation, gabriel/muse, gabriel/musehub) already exist in RDS, recreated later with their own real, current history. Nothing unique or valuable is trapped in the stopped container. Confirmed via direct query on both databases, not inferred.

Root cause of the silent divergence: deploy/secrets.sh only ever knew about DB_PASSWORD (for a self-hosted Postgres connection string built at the app level) — it never knew staging actually needed a full DATABASE_URL pointing at RDS. When secrets.sh regenerated .env from SSM (2026-09-07, as part of #156), it silently produced a .env with no DATABASE_URL at all. The only reason this didn't break staging immediately is that the already-running container doesn't re-read .env after startup — the break would have surfaced on the next real deploy.

Target architecture (decided 2026-09-08)

Standardize on AWS RDS for both staging and production. Local dev keeps its own Docker Compose Postgres (deliberately — fast, disposable, no AWS dependency for day-to-day development).

Why RDS over self-hosted, now that both have been run in anger:

• RDS has been running unattended for 5 months with native automated backups and point-in-time recovery (7-day retention) — zero manual ops burden, whereas the self-hosted pattern required building a whole custom backup pipeline from scratch (#157: rclone, a dedicated R2 bucket, a scoped token, a cron job) just to reach parity.
• Managed patching, monitoring integration (AWS/RDS CloudWatch metrics — including DatabaseConnections, which #160 incorrectly assumed was inapplicable and removed an alarm for), and the option of Multi-AZ failover if/when availability targets (#149) demand it.
• Removes a real, previously-undetectable failure mode: a self-hosted Postgres container can be silently stopped (as staging's was, for 2 months) with nothing noticing. RDS's own health/status is independently monitored by AWS regardless of what's happening on the EC2 instance.

Migration plan — phased, no step skipped

Phase 0 — Immediate housekeeping (safe, do first, low risk)

• [x] Take one final pg_dump of the stopped musehub_postgres container's data and store it in the same R2 backup bucket pattern as production's backups — cheap insurance given it's confirmed to contain no unique data, not because it's expected to be needed. Done 2026-09-08: dumped, compressed (~89MB), and uploaded to s3://musehub-staging/_archive/staging-legacy-postgres-archive-20260908.sql.gz (R2, via staging's existing blob-storage credentials). Container stopped again immediately after.
• [x] Correct every doc that currently claims "self-hosted Postgres only, no RDS" — docs/infrastructure.md, production-readiness Sections 5/8/12 and their closing comments. Done 2026-09-08: added correction notes to docs/infrastructure.md, docs/db-management.md, docs/pre-launch-checklist.md, docs/musehub-production-readiness-checklist.md, and production-readiness Sections 0, 5, 8, 12 — including reversing musehub-production-infrastructure-launch-todo.md's prior conclusion that the RDS instance was "very likely orphaned" and safe to decommission.
• [x] Leave musehub_postgres/musehub_runner stopped (their natural current state) until Phase 1's cleanup step — don't start them again except for the Phase 0 archival dump. Verified stopped after the dump.

Phase 1 — Formally correct staging's secrets pipeline (small, low risk) — DONE 2026-09-08

• [x] Add DATABASE_URL as a real SSM SecureString parameter for staging (/musehub/staging/DATABASE_URL), so secrets.sh writes it into every future .env — this closes the exact gap that caused the silent divergence. Value was extracted from the live container and written directly via a temporary, narrowly-scoped IAM grant on the EC2 instance role (revoked immediately after) — it never transited a local shell or terminal.
• [x] Update deploy/secrets.sh to fetch and write DATABASE_URL when present, for both environments. When the SSM override is absent, it now falls back to constructing the self-hosted URL from DB_PASSWORD (production's current pattern) — this also fixes a previously-unknown general gap: secrets.sh never wrote DATABASE_URL at all before this change, for either environment. Every environment's working value came only from setup-ec2*.sh's one-time initial .env write and would have been silently dropped the next time secrets.sh ran — the same failure mode that hit staging could have hit production too.
• [x] Redeployed staging for real (bash deploy/push.sh staging) — migrations ran cleanly against RDS, schema gate passed (73 tables in sync), health check passed, blue/green swap completed. Verified live: curl https://staging.musehub.ai/healthz → {"status":"ok","db":true,"storage":true}.
• [x] Removed musehub_postgres, musehub_runner, and the musehub_postgres_data volume from the staging instance after the deploy above confirmed RDS connectivity end-to-end. Re-verified /healthz still healthy after removal.
• [x] Verified staging RDS baseline hardening:
  • PubliclyAccessible: false ✅
  • Security group (sg-0cbdd02a95bcf47cf) restricts port 5432 to the VPC CIDR (172.31.0.0/16) only — no broader exposure ✅
  • DeletionProtection: true ✅
  • BackupRetentionPeriod: 7 (days) ✅
  • StorageEncrypted: false ⚠️ — gap, not yet fixed. RDS storage encryption cannot be enabled on an existing instance; it requires a snapshot-and-restore into a new encrypted instance (brief downtime or a blue/green RDS switch). Tracked as a Phase 1 follow-up, not blocking — staging holds no regulated data, but this should close before Phase 2 provisions production's instance (provision it encrypted from the start there).
  • MultiAZ: false — expected/acceptable for staging; not a gap.

Phase 2 — Migrate production from self-hosted Postgres to RDS — DONE 2026-09-08

• [x] Provisioned musehub-production-db (db.t3.micro, postgres 16.13, gp3 20GB, single-AZ, us-east-1) in the Production AWS account (672469410277) — matches staging's sizing (the live self-hosted DB was only ~116MB / 6 repos / 5 identities, plenty of headroom). Provisioned with the Phase 1 lesson applied from the start: --storage-encrypted, --deletion-protection, --no-publicly-accessible, 7-day automated backups, a dedicated subnet group (musehub-db-subnets) and security group (musehub-production-rds-sg, port 5432 scoped to the VPC CIDR 172.31.0.0/16 only — same pattern as staging).
• [x] Migrated data via pg_dump -F c from the self-hosted container, pg_restore into the new RDS instance (via a throwaway postgres:16 client container on the same EC2 instance, over the VPC-internal network — no data left the AWS network). Verified matching before cutover: 74 tables, 6 repos, 5 identities on both sides.
• [x] Added DATABASE_URL to /musehub/production/DATABASE_URL (SSM SecureString, written via the same temporary narrowly-scoped IAM grant pattern as staging — revoked immediately after) and ran the same deploy/secrets.sh fix already used for staging. Redeployed via bash deploy/push.sh prod — migrations ran cleanly against RDS, schema gate passed (73 tables in sync — one fewer than the raw pg_dump count above because Alembic's own version table isn't counted the same way by the two queries; not a discrepancy), health check passed, blue/green swap completed. Verified live: /healthz → {"status":"ok","db":true,"storage":true}, plus a real muse hub repo list --hub https://musehub.ai query returned actual repo data (not just a health-check ping).
• [x] Archived the old self-hosted container's final state (pg_dump to /opt/backups/prod-legacy-postgres-final-20260908.dump, ~30MB) and stopped it (docker stop --time 15, graceful) — retained, not removed, through a 2+ week safety window (target: decommission no earlier than ~2026-09-23). Do not docker rm or remove its volume before that date without re-confirming production has been stable on RDS the whole time.
• [ ] Note: one deploy attempt during this migration failed with Your authorization token has expired immediately after a successful docker login — reproduced twice via push.sh, then succeeded both via a manual login+pull on the instance and on the next push.sh retry afterward. Root cause not identified (possibly ECR token propagation lag right after issuance); not a DATABASE_URL/RDS issue. Worth keeping an eye on in future deploys — if it recurs, it may need a retry loop added to deploy.sh's image pull step.

Phase 3 — Retire the manual backup pipeline

• [ ] Once both environments are on RDS, the custom rclone/R2/cron pipeline built for #157 (and planned for staging in #180) is no longer the primary backup mechanism — RDS's native automated backups and PITR replace it.
• [ ] Decide whether to keep periodic pg_dump-to-R2 as a secondary, belt-and-suspenders backup even with RDS's native ones (cheap, reasonable, not mandatory).
• [ ] Update #157/#180 to reflect the revised plan rather than "build the same manual pipeline for staging too."

Phase 4 — Documentation and ticket reconciliation

• [ ] Keep this doc as the living source of truth as each phase lands (update the "Current state" table at the top, don't leave it describing an intermediate state).
• [ ] Update docs/infrastructure.md and all affected production-readiness docs/tickets once the migration is real, not just planned.
• [ ] Re-verify Section 5's (#154) single-self-hosted-Postgres SPOF finding — it goes away entirely once both environments are on managed RDS.
• [ ] Restore a real DB-connections alarm in deploy/cloudwatch-alerts.sh (#160 removed one pointed at AWS/RDS, believing it was inapplicable — it's actually exactly right once this migration is real for both environments).