# 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 | Environment | Actual database | How it got there | |---|---|---| | Local dev | Docker Compose Postgres container (`musehub_postgres` in the compose stack — see `docs/db-management.md`) | Deliberate, correct, unaffected by anything below | | **Staging** | **AWS RDS** (`musehub-staging-db`, `db.t3.micro`, `us-east-1`, created 2026-04-05) | Original architecture from day one — see history below | | **Production** | **AWS RDS** (`musehub-production-db`, `db.t3.micro`, `us-east-1`, created 2026-09-08, **encrypted at rest**) | Migrated from self-hosted Postgres 2026-09-08 — see Phase 2 below. Old self-hosted container stopped-but-retained through ~2026-09-23. | **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..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 — 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. - [ ] **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). - [ ] **Not yet done**: fix staging's `StorageEncrypted: false` gap from Phase 1 (needs a snapshot-and-restore into a new encrypted instance, same shape as Phase 2's production migration — lower urgency since staging holds no regulated data, but should not stay open indefinitely). - [ ] **Not yet done**: root-cause the transient `Your authorization token has expired` ECR error hit during Phase 2's production deploy (see Phase 2 note above) — currently unexplained, resolved itself on retry both times it occurred.