Database Migrations

ChartChat uses a two-phase migration system — pre-deploy (schema changes) and post-deploy (data backfills). Understanding how this works is essential for any developer adding new tables, columns, or indexes.

---

The Two Types of Migrations

Type	Directory	What it does	When it runs
Pre-deploy	migrations/schema/	DDL — CREATE TABLE, ALTER TABLE, CREATE INDEX	Automatically on every server startup, before the app accepts traffic
Post-deploy	migrations/data/	Data backfills, heavy reindexing, long-running jobs	Queued via BullMQ after server starts; runs as a background AsyncJob

---

How the Server Tracks Migration State

The server uses a single PostgreSQL table called DatabaseMigration to track where it is:

CREATE TABLE "DatabaseMigration" (
  "id"          INTEGER NOT NULL PRIMARY KEY,
  "version"     INTEGER NOT NULL,   -- last completed pre-deploy migration number
  "dataVersion" INTEGER NOT NULL,   -- last completed post-deploy migration number
  "firstBoot"   BOOLEAN NOT NULL DEFAULT false
);

There is always exactly one row (id = 1) in this table.

Column	Meaning
version	The highest pre-deploy migration (vN) that has been run
dataVersion	The highest post-deploy data migration that has been run
firstBoot	true from first startup until every post-deploy migration completes for the first time

Special version values

Constant	Value	Meaning
MigrationVersion.NONE	0	No migrations run yet
MigrationVersion.UNKNOWN	-1	DatabaseMigration table does not exist yet
MigrationVersion.FIRST_BOOT	-2	Server is in first-boot mode (post-deploy not fully applied yet)

---

Pre-Deploy Migrations (Schema)

Files

packages/server/src/migrations/schema/
  index.ts       ← exports all migrations as v1, v2, ..., vN
  types.ts       ← PreDeployMigration interface
  v1.ts
  v2.ts
  ...
  v107.ts        ← latest

Each file exports a run(client: PoolClient): Promise<void> function that executes SQL via the fns.query() helper.

Example — v107.ts:

export async function run(client: PoolClient): Promise<void> {
  await fns.query(client, results, `CREATE TABLE IF NOT EXISTS "HospitalIntegration" (...)`);
  await fns.query(client, results, `CREATE INDEX IF NOT EXISTS ...`);
}

How the server decides what to run

On every startup, runAllPendingPreDeployMigrations() in database.ts:

1. Reads version from DatabaseMigration — this is the last completed migration number.
2. Iterates from currentVersion + 1 up to the total number of known migrations.
3. For each pending migration, calls migration.run(client).
4. After each one succeeds, immediately updates DatabaseMigration.version = i.

for (let i = currentVersion + 1; i <= getPreDeployMigrationVersions().length; i++) {
  const migration = getPreDeployMigration(i);
  await migration.run(client);
  await client.query('UPDATE "DatabaseMigration" SET "version"=$1 WHERE "id" = 1', [i]);
}

If the server has version = 104 in the database and the code has migrations up to v107, it will run v105, v106, and v107 in order on the next startup.

Concurrency lock

Before running any migrations, the server acquires a PostgreSQL advisory lock (pg_advisory_lock(1)). This prevents two server instances from running migrations simultaneously in a multi-instance deployment.

• Retries every 2 seconds, up to 30 attempts (60 seconds total).
• If it cannot acquire the lock, it throws and refuses to start.
• The lock is released when migrations finish (even on error).
• The migration connection has no statement timeout (SET statement_timeout TO 0) so long-running DDL is never killed mid-way.

First-time initialization

If DatabaseMigration does not exist when the server starts (UNKNOWN version), the table is created and initialized with version = 0 and firstBoot = true. Then all migrations run from v1 onward.

---

Post-Deploy Migrations (Data)

Files

packages/server/src/migrations/data/
  index.ts                    ← exports all data migrations as v1, v2, ..., vN
  types.ts                    ← PostDeployMigration interface
  data-version-manifest.json  ← version compatibility constraints
  v1.ts
  v2.ts
  ...

How they run

Post-deploy migrations are not run synchronously at startup. Instead:

1. On startup, maybeAutoRunPendingPostDeployMigration() checks if there is a pending post-deploy migration.
2. If yes, it queues a BullMQ job via queuePostDeployMigration().
3. The job runs as a background AsyncJob resource (visible in Admin → Super Admin → Async Jobs).
4. When the migration completes, markPostDeployMigrationCompleted() updates DatabaseMigration.dataVersion.

This approach means the server is online and serving traffic while data migrations run in the background.

How the server detects the next pending migration

const postDeployVersion = await getPostDeployVersion(client);  // reads "dataVersion"
const allVersions = getPostDeployMigrationVersions();          // [1, 2, 3, ..., N]

if (allVersions.includes(postDeployVersion + 1)) {
  return postDeployVersion + 1;  // this is the next one to run
}
return MigrationVersion.NONE;   // nothing pending

If dataVersion = 14 and the code has migrations v1–v25, the next pending migration is v15.

data-version-manifest.json — version gating

Each post-deploy migration has an entry in the manifest that specifies:

Field	Meaning
serverVersion	Minimum server version required before this migration is applied
requiredBefore	If the server reaches this version without applying the migration, it refuses to start

{
  "v1": { "serverVersion": "3.3.0", "requiredBefore": "4.0.0" },
  "v2": { "serverVersion": "4.1.0", "requiredBefore": "4.2.0" }
}

Three cases when evaluating a pending post-deploy migration:

Server version	Behavior
< serverVersion	Skip the migration entirely — server is not ready for it yet
>= serverVersion and < requiredBefore	Log a warning that the migration is pending, allow startup
>= requiredBefore	Refuse to start (in production and strict mode) — migration must be applied first

This gating is only enforced when NODE_ENV=production or MEDPLUM_ENABLE_STRICT_MIGRATION_VERSION_CHECKS=true.

firstBoot flag

firstBoot = true from the very first startup until all post-deploy migrations complete successfully for the first time. While firstBoot is true:

• The requiredBefore checks from the manifest are skipped — this prevents the server from refusing to start if it restarts mid-migration during initial setup.
• getPostDeployVersion() returns MigrationVersion.FIRST_BOOT (-2) instead of the actual dataVersion.

Once all post-deploy migrations complete, firstBoot is set to false and never goes back to true.

---

Writing a New Migration

Adding a new pre-deploy (schema) migration

1. Create packages/server/src/migrations/schema/vN.ts (where N is the next number):

import type { PoolClient } from 'pg';
import * as fns from '../migrate-functions';

export async function run(client: PoolClient): Promise<void> {
  const results: { name: string; durationMs: number }[] = [];

  await fns.query(client, results, `ALTER TABLE "Patient" ADD COLUMN IF NOT EXISTS "customField" TEXT`);
}

2. Add the export to migrations/schema/index.ts:

export * as v108 from './v108';

The server will automatically pick it up on the next startup.

warning

Always use IF NOT EXISTS / IF EXISTS in your SQL so migrations are idempotent — safe to re-run if interrupted.

danger

Never modify an existing migration file. If the migration has already run in any environment, changing it will have no effect (the version is already marked as done). Always create a new vN+1 file.

Adding a new post-deploy (data) migration

1. Create packages/server/src/migrations/data/vN.ts implementing the PostDeployMigration interface.
2. Export it from migrations/data/index.ts.
3. Add an entry to data-version-manifest.json:

"v26": {
  "serverVersion": "4.5.0",
  "requiredBefore": "4.6.0"
}

---

Checking Migration State

Via SQL

SELECT "version", "dataVersion", "firstBoot" FROM "DatabaseMigration" WHERE "id" = 1;

Via Admin Portal

Go to Admin → Super Admin:

• Get Schema Drift — shows SQL needed to bring the DB schema in line with what the code expects
• Reconcile Schema Drift — applies that SQL
• Async Jobs (/admin/super/asyncjob) — shows running/completed post-deploy migration jobs

Via server logs

On startup the server logs each migration as it runs:

Database pre-deploy migration { version: 'v105', duration: '142 ms' }
Database pre-deploy migration { version: 'v106', duration: '89 ms' }
Pending post-deploy migration { version: 'v15' }

---

Common Scenarios

Fresh database (first install)

1. DatabaseMigration does not exist → created, version=0, firstBoot=true
2. All pre-deploy migrations run (v1 → v107)
3. version=107 written to DatabaseMigration
4. Post-deploy migrations queued from v1 upward as background jobs
5. Once all complete, firstBoot=false

Upgrading an existing deployment

1. New code deploys with v108 in schema migrations
2. Server starts, reads version=107 from DatabaseMigration
3. Runs only v108, updates version=108
4. If a new post-deploy migration exists, queues it as a background job

Server crash mid-migration

• Pre-deploy: The version column is only updated after each migration succeeds. If the server crashes mid-migration, that migration will re-run from scratch on next startup. All SQL must be idempotent (IF NOT EXISTS).
• Post-deploy: The AsyncJob stays in accepted or in-progress state. On next startup, maybeAutoRunPendingPostDeployMigration() detects it and re-queues.

Two server instances starting simultaneously

The PostgreSQL advisory lock prevents both from running migrations at the same time. The second instance retries every 2 seconds for up to 60 seconds until the first instance releases the lock.