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 schema_migrations table (filename + SHA-256 checksum). 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.

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.