Database Operations

The database is a single RDS PostgreSQL 18 instance per environment, in private subnets, fronted by an RDS Proxy for the application and reachable for humans and CI only through an SSM-relayed tunnel. This page explains who connects as what, how migrations reach the DB, and the ordering rule that keeps deploys from causing outages.

Access topology

The relay is a t3.micro EC2 in AccessStack, default stopped — scripts start it, tunnel through it, and stop it again.

The role model

Each role exists because two RDS constraints collide: (a) rds_iam and password auth are mutually exclusive per role, and (b) the Proxy and RDS’s managed master-credential rotation both require the master’s password.

Role	Auth	Purpose
`opsdna_admin`	password (RDS master)	The master user. Must stay password-auth — the Proxy registration and RDS rotation depend on it. Never put it in `rds_iam`.
`opsdna_app`	password via the Proxy	What the Lambdas connect as. Password set by `bootstrap-app-role.sh`.
`opsdna_migrator`	IAM token (`rds_iam`)	Dedicated migration role: `rds_superuser` + `CREATEROLE`, no password, no Secrets Manager secret, not on the Proxy. Connects direct-to-instance over the SSM tunnel. The CI deploy role is granted `rds-db:connect` for it.

The result: CI never reads the master secret, migrations get exactly the authority they need, and the master’s password lifecycle stays untouched.

Do not deploy the IAM_AUTH proxy configuration (defaultAuthScheme: IAM_AUTH in DatabaseStack, grantConnect) while the app roles are not in rds_iam. That auth-model switch is an open decision (OPS-448); deploying it halfway breaks login for the entire API. Prod uses SECRETS+password proxy auth today.

Connecting by hand


# terminal A — start the relay + SSM port-forward (holds the tunnel open)
bun --cwd infra db-shell
 
# terminal B — psql through the tunnel
bun --cwd infra psql

If psql fails with PAM authentication failed for user "opsdna_admin", that environment’s master is in rds_iam (staging is, deliberately) — mint an IAM auth token instead of using a password. The runbook (docs/runbooks/prod-redeploy-and-db-rebuild.md) has the full cheat-sheet.

Migrations: how they deploy

Migrations live in packages/persistence (baseline 0001 + post-baseline/00NN) and are applied with bun migrate:latest — see /persistence for authoring rules. In deploys, infra/scripts/ci-migrate.sh <env> runs that exact same command from the CI runner, fully non-interactively:

Resolve the relay instance ID and RDS endpoint from CloudFormation stack outputs.
Start the relay, open a background SSM port-forward, wait for the port.
Mint an RDS IAM token for opsdna_migrator and run bun migrate:latest.
Tear the tunnel down and stop the relay via trap — even on failure.

(An in-stack Trigger Lambda was tried and dropped: the 17 packages feeding the migration set each resolve their SQL directory from import.meta.url, which esbuild collapses to /var/task, where no .sql files ship.)

Code must never ship ahead of migrations

Both deploy pipelines run migrate as the first step of the same job as cdk deploy, so a failed migration stops everything and the Lambda code never swaps past the schema.

The outage pattern this prevents is real, not theoretical. Deploying application code that references tables or columns from migrations that haven’t been applied produces immediate 500s on every affected route — the code queries a schema that doesn’t exist yet. Recovery means either racing the missing migrations forward or rolling the code back, both under incident pressure. The pipelines make this impossible by construction; if you ever deploy outside them, run migrate:latest first, always.

Migration discipline

Applied migrations are immutable. Never edit committed migration SQL — the migration ledger checksums every file, and an edited applied migration hard-fails the next migrate:latest against a real environment. CI enforces this on every PR (the Migration immutability gate in ci.yml).
Never author a migration from a stale base. git fetch and check origin’s post-baseline head first, or two branches will claim the same migration number.
bun run verify runs on pglite and does not prove Postgres correctness. EXECUTE grants, RLS FORCE, and catalog invariants only fail on real Postgres — that’s the separate postgres integration CI job (against a postgres:18 service, matching prod). Run it locally for any schema change; see /persistence.
Read DATA-MODEL.md before any schema/FK/migration decision — it is the canonical entity model.

Backup and restore

BackupStack runs an AWS Backup vault with a multi-tier retention plan over RDS (plus RDS automated backups: 30 days prod, 1 day staging). A fresh DB rebuild is a runbook procedure, not just a restore: role passwords, rds_iam memberships, tenant bootstrap, and SSM secrets are operational state that a code redeploy does not reproduce — see Secrets and config and Runbook §3/§5d/§7.