Database migrations

The API uses a small forward-only migration runner to apply schema and data changes to the shared MySQL database. Before this existed, schema changes were applied by hand; the runner makes them ordered, tracked, and part of the deploy.

The runner lives at api/scripts/migrate.js; migrations are plain .sql files under api/sql/migrations/. The pattern mirrors the one already used in assistant/.

How it works

Migrations apply in filename order (0001_…, 0002_…).
Each applied file is recorded in a tracking table (filename + SHA-256 checksum). The API uses api_schema_migrations — distinct from the assistant’s schema_migrations, because both runners share the IBA schema and each one’s checksum guard expects every recorded filename to exist in its own migrations dir. Already-applied files are skipped on the next run, so the runner is safe to run on every deploy.
If a previously-applied file is edited, the checksum no longer matches and the runner aborts — you can’t silently rewrite history. To change something, write a new migration.
Each file runs in its own transaction, and --pretend lists what would run without applying anything.


cd api
npm run migrate:pretend   # show pending migrations, apply nothing
npm run migrate           # apply pending migrations in order

Authoring a migration

Two rules are easy to miss:

No DELIMITER. DELIMITER is a mysql-CLI directive — the server and the mysql2 driver reject it. Write stored procedures with plain ;; the runner sends the whole file and the server parses BEGIN…END bodies correctly. (The older hand-applied files under api/src/stored-procedures/ still use DELIMITER because they were applied via the CLI — don’t copy that style into sql/migrations/.)
Be idempotent. MySQL auto-commits DDL (CREATE/ALTER), so a migration that fails halfway is not rolled back. Use DROP … IF EXISTS, CREATE TABLE IF NOT EXISTS, INSERT … WHERE NOT EXISTS, and the INFORMATION_SCHEMA guard for ADD COLUMN, so a re-run is a no-op.

Filenames are NNNN_short_description.sql, zero-padded and monotonic. The authoring conventions live in api/sql/migrations/README.md.

The migration user

The runner authenticates as a dedicated DB_MIGRATE_USER with DDL privileges (CREATE/ALTER/DROP/CREATE ROUTINE/…), kept separate from the app’s least-privilege DB_USER. This keeps schema rights off the long-running app process — a bug in the app can’t drop a table.

Credentials live in Infisical, per environment (DB_MIGRATE_USER / DB_MIGRATE_PASSWORD) — each environment points at its own database. The runner only reaches for Infisical when the connection isn’t already supplied via env, so CI and local runs can target a specific database directly.

How migrations run at deploy

Migrations run as a DigitalOcean App Platform Pre-Deploy command (npm run migrate) on the API app — they apply before the new containers receive traffic:

Migration succeeds → DO proceeds and cuts traffic to the new version.
Migration fails → DO marks the deployment failed and keeps the current version live. The failing do-deployment.yml run goes red and the Slack failure alert fires.

Because the database is shared and migrations apply while the old code is still live (and stay applied even if a new deploy is later rolled back), migrations must be backward-compatible with the running code — additive changes, or stored-procedure replacements that keep the same signature.

Local development

docker-start.sh runs the migrate step automatically after it restores the S3 dump, so a fresh local database ends up on the repo’s schema rather than the schema frozen into the backup. Two local-only details:

It runs as root. A mysqldump doesn’t carry MySQL users, so the dedicated DB_MIGRATE_USER doesn’t exist in the local container — the script passes root (the container’s MYSQL_ROOT_PASSWORD) as the migrate user.
The step is non-fatal. If the runner aborts it prints a warning and the stack still comes up; a local checksum problem shouldn’t block dev.

A checksum mismatch during that step almost always means the S3 dump is stale — it came from a database whose api_schema_migrations recorded an older version of a migration that has since changed. The durable fix is a fresh backup (see the caveat below). To unblock a single local database, drop the offending tracking row and re-run the step so the runner re-applies the current file (safe because migrations are idempotent):


# in the api container, as root
mysql -e "DELETE FROM api_schema_migrations WHERE filename='<file>.sql';"
npm run migrate

Validation before merge

On PRs labelled deploy-preview-with-db, the preview workflow applies the branch’s migrations against the freshly-imported isolated preview DB (a real-schema copy from the latest S3 backup). A broken migration fails the preview job — and the PR check — before it can reach a production Pre-Deploy. The dry-run uses the real runner (the no-DELIMITER files don’t parse via the mysql CLI) over CA-verified TLS.

The S3 backup is a shared dependency

Both local dev and the preview DB restore from the same s3://…/db-backup/ dump, but nothing in this repo produces that dump — an out-of-band job writes it (historically a mysqldump cron on a droplet), and docker-start.sh / the preview workflow only ever read the latest object.

That makes the backup’s freshness load-bearing and currently unmonitored: if the producing job stalls or points at a lagging database, the “latest” backup silently goes stale. Everything downstream then inherits an old schema — a migration merged since that backup is missing, surfacing as an Unknown column error locally and as a checksum mismatch when the migrate step tries to reconcile. Production is unaffected (it migrates via its own Pre-Deploy job), so a symptom that appears “only locally / only in previews” is usually a stale-backup signal rather than a code bug.

The producer is now the version-controlled db-backup.yml workflow — a nightly mysqldump of the managed production database to s3://…/db-backup/ (carrying the api_schema_migrations table, so restores stay checksum-aligned with prod), with a Slack alert on failure as the freshness guardrail. Edits to already-applied migrations — the other half of this incident — are blocked at review time by migrations-guard.yml.