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 ⚠️ → fixed 2026-09-08. RDS storage encryption can't be enabled on an existing instance, so this required a snapshot-copy-restore: manual snapshot of musehub-staging-db → copy-db-snapshot with a KMS key (produces an encrypted copy) → restore-db-instance-from-db-snapshot into a new instance, musehub-staging-db-v2. Verified data matched exactly before cutover (74 tables, 28 repos, 8 identities on both sides). DATABASE_URL updated in SSM, staging redeployed, verified live via /healthz and a real muse hub repo list query. musehub-staging-db (the old, unencrypted instance) is retained, not deleted, through the same 2+ week safety window pattern used elsewhere in this plan (target: ~2026-09-23) — it is no longer receiving traffic.
  • 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 — DONE 2026-09-08

• [x] Both environments are now on RDS, so 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 (7-day retention, both environments) are.
• [x] Found and fixed a real, active bug during this pass: production's deploy/backup.sh cron (daily, 3 AM) still docker exec'd into musehub_postgres, which Phase 2 stopped — confirmed it had already silently produced an empty/broken backup file the same morning this was discovered. Rewrote deploy/backup.sh to connect via DATABASE_URL (works identically against RDS or self-hosted Postgres — it's just a connection string) instead of docker exec, so it no longer depends on a specific container existing.
• [x] Decision: keep pg_dump-to-R2 as a secondary, belt-and-suspenders backup — cheap insurance against failure modes RDS's own backups don't cover (accidental instance/snapshot deletion, an account-level incident), and production's R2 bucket/rclone setup already existed from #157. Changed frequency from daily to weekly (Sundays, 3 AM) since RDS already covers day-to-day recovery — re-installed as a weekly cron on production, test-run confirmed working (30MB dump, uploaded to R2 successfully). Not added to staging — staging is non-critical and RDS-native backups alone are judged sufficient there; can revisit if that changes.
• [x] Updated #180 (see below) to reflect that its "staging has zero backups" item is resolved by RDS's native backups, not by replicating the manual pipeline there.

Phase 4 — Documentation and ticket reconciliation — mostly DONE 2026-09-08

• [x] Keep this doc as the living source of truth as each phase lands — the "Current state" table above reflects both environments on RDS, not an intermediate state.
• [x] Updated docs/infrastructure.md (Phase 0) and production-readiness Section 5 (05-production- architecture.md) to reflect the real, completed migration — including correcting the SPOF finding to be precise: RDS closes the "no managed backups/patching at all" gap but does not eliminate the single-AZ single-point-of-failure outright (see Section 5's update). Sections 8 and 12 were updated in Phase 0; Section 0 in Phase 0 as well.
• [x] Restored a real AWS/RDS DatabaseConnections alarm in deploy/cloudwatch-alerts.sh for both environments (musehub-{env}-db-connections-high, threshold 40 connections, 2 consecutive 5-min periods) — ran the script for real against both, confirmed both alarms exist live via describe-alarms. #160's removal was correct for the assumptions at the time (no known RDS anywhere) and is now superseded now that both environments genuinely run on RDS.
• [x] Fixed staging's StorageEncrypted: false gap (2026-09-08) via snapshot-copy-restore — see Phase 1's updated hardening checklist above for full detail. Both environments' active RDS instances are now encrypted at rest.
• [ ] Not yet done: enable RDS Multi-AZ for production (decision pending — see Section 5's updated SPOF note; roughly doubles instance cost, gated on availability targets from #149).
• [x] Mitigated (not root-caused) the transient Your authorization token has expired ECR error hit during Phase 2's production deploy: deploy/deploy.sh now retries the full login+pull cycle up to 3 times with a 5s backoff, re-fetching a fresh token each attempt. Exact root cause is still unknown (possibly ECR token propagation lag) but the fix addresses the failure mode regardless.