RENEWA Knowledge Base

Database Migrations

3 min read

Database Migrations

Atlas (Ariga) manages database schema migrations with integrity verification.

Source of Truth

FileRoleEditable?
backend/src/db/schema.tsSchema definition (Drizzle ORM)YES — edit this only
backend/atlas/migrations/*.sqlGenerated SQL migrationsNEVER edit
backend/atlas/migrations/atlas.sumMerkle hash integrity fileNEVER edit

48 migrations as of 2026-06-12, starting from the 20260326170903_baseline.sql baseline.

Migration Workflow

  1. Edit backend/src/db/schema.ts
  2. Run make db-generate NAME=description (see Makefile Commands)
  3. Verify generated SQL in backend/atlas/migrations/
  4. Commit both the migration file and atlas.sum

Atlas uses an ephemeral dev database (atlas_dev on the Docker Compose Postgres) to compute schema diffs. Squash migrations within a PR.

Config data (rows the app needs to function in production) also ships as migrations: make db-generate NAME=config_<thing>. See Mock and Config Data.

N-1 Compatibility (expand → migrate → contract)

Every migration must keep the previous app image working — old machines serve traffic while release_command commits. Renames, drops, SET NOT NULL, and type changes are gated by scripts/check-migration-n1.sh in CI (PR#1938):

  • Table rename → ship an auto-updatable compat view under the old name in the same migration, declared with an in-file directive (shown without the leading SQL comment dashes): renewa:n-1-shim: <old> drop-with #<issue>.
  • The contract migration drops the shim via renewa:n-1-shim-drop: <old> + DROP VIEW.
  • While a shim is live, its drop-with issue must stay OPEN — closing it without the drop fails every migration PR.
  • Reviewed contract-phase changes are approved via the PR label migration-approved (the audit trail).

Deploy-time enforcement: backend/scripts/atlas-migrate.sh enforces the phase split per database — if a shim-create and its shim-drop are both pending on a DB with history (batched promotion), the contract migration is auto-deferred to the next deploy, or the release fails cleanly when deferral is unsafe. Fresh DBs apply everything.

Environment Gating

Destructive data migrations can be skipped per environment with an in-file directive (PR#1797): renewa:atlas:skip-on staging,production (written as a SQL comment in the migration file). atlas-migrate.sh reads it against APP_ENV.

Validation

CheckWhere
make db-validateAtlas hash-chain integrity locally
make db-lintOrdering + destructive ops vs main
make db-check-driftschema.ts matches cumulative migrations
make db-reset-migrationsReset atlas/migrations to main, keep schema.ts changes, regenerate
migration-tests.ymlCI (via workflow_call from pr-preview.yml): syntax, ordering, destructive ops, N-1 gate — see CI-CD Workflows
Pre-commit hookMigration consistency (scripts/check-migration-consistency.sh)
Pre-push hookatlas.sum integrity when migrations are touched

atlas.sum prevents tampering — any manual edit to migration files is detected. atlas.sum is append-only: if a newer migration landed on main, rename yours to a current timestamp and re-run atlas migrate hash; never re-hash from scratch.

Deployment Strategy

EnvironmentMethodConnection
Local / TestContainer startupStandard DATABASE_URL
Cloud (dev/staging/prod)Fly.io release_command → atlas-migrate.shDATABASE_URL_MIGRATION (migration-user, direct)

The migration-user has schema_admin privileges for DDL operations. The app-user (via PgBouncer) handles runtime queries. See Database Architecture for connection details and Deployment Rollback for why migrations must stay backward-compatible.

Key Files

  • backend/src/db/schema.ts
  • backend/atlas/migrations/ (+ atlas.sum)
  • backend/scripts/atlas-migrate.sh
  • scripts/check-migration-n1.sh
  • scripts/check-migration-consistency.sh
  • .github/workflows/migration-tests.yml

See Also

Graph View

Backlinks