# 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** | Self-hosted PostgreSQL in a Docker container (`musehub_postgres`) on the app's own EC2 instance | Deliberately built this way during the 2026-08-25 production launch | **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. ## 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) - [ ] 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. - [ ] 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. - [ ] 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. ### Phase 1 — Formally correct staging's secrets pipeline (small, low risk) - [ ] 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. - [ ] Update `deploy/secrets.sh` to fetch and write `DATABASE_URL` when present, for both environments (staging now, production once Phase 2 lands). - [ ] Redeploy staging for real once the secrets pipeline is fixed, confirming the new deploy connects to RDS correctly (not silently falling back to a Docker Postgres that shouldn't exist anymore). - [ ] Remove `musehub_postgres` and `musehub_runner` containers and volumes from the staging instance once the Phase 0 archive is confirmed safe and Phase 1's redeploy is verified healthy. - [ ] Verify staging RDS baseline hardening: encryption at rest, deletion protection, security group scope (already confirmed `PubliclyAccessible: false`). ### 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).