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

This is inconsistent between staging and production, and the docs previously claimed otherwise. 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. That was accurate for production and wrong for staging — staging has been running on RDS the entire time this session's work assumed otherwise. See "How this was discovered" below.

Update, 2026-09-08 (Phase 1 complete): staging's secrets pipeline (deploy/secrets.sh) now formally knows about the RDS DATABASE_URL via SSM, redeployed and verified healthy end-to-end, and the orphaned self-hosted containers/volume on staging have been removed. The inconsistency between environments described above is now fully documented and no longer silent — it's a known, tracked gap (Phase 2 below closes it by moving production to RDS too), not an undiscovered landmine.

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.
• [ ] 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.
• [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

• [ ] Provision a new RDS Postgres instance in the Production AWS account (sizing informed by current traffic — likely db.t3.micro to start, matching staging).
• [ ] Use the already-tested backup/restore pipeline from #157 as the migration vehicle: pg_dump production's current self-hosted Postgres, restore into the new RDS instance, verify the schema gate and table counts match exactly before cutting over.
• [ ] Add DATABASE_URL to production's SSM parameters and secrets.sh, cut the running containers over, verify /healthz and real functionality against RDS.
• [ ] Keep the old self-hosted Postgres container and volume stopped-but-retained for a safety window (recommend 2+ weeks, informed by this session's lesson about not noticing a stopped database) before fully decommissioning.

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).