Upgrades and Migrations

Upgrades and migrations

This page covers the rules for changing the database schema, why they are strict, and what CI checks. It is for anyone editing packages/database/prisma/schema.prisma.

The one rule

Never edit a migration that has already been applied. Any migration merged to dev or main is applied somewhere: a contributor's local database, a CI run, or a deployment. Fix a mistake with a new migration.

Prisma stores a checksum of every migration it has applied. Editing an applied migration therefore does not fail where you made the change. It fails later, during migrate deploy against an environment that already ran the old version, which is the worst possible place to find out.

CI enforces this on every pull request that touches packages/database. The check diffs the migrations directory against the base and allows additions only. Renames count as a delete plus an add, and the diff runs with rename detection disabled specifically to catch a migration being moved rather than added.

Migrations present today

Two.

0000000000000_init creates the schema and a minimal eight-column AuditEvent table. That is deliberate history: the audit table was the first thing the repository could store.

20260813000000_emr_data_model adds the whole EMR model: 59 types, 46 tables, and an alter of AuditEvent rather than a create.

The second migration is worth reading for one reason. It was generated by prisma migrate diff against the applied init state, so it reproduces the schema exactly, and exactly two things were added by hand with nothing generated removed: a header comment, and a precondition guard.

The guard aborts the migration if AuditEvent is not empty. The migration converts the id column from text to UUID and adds four non-nullable columns, including tenantId, seq, and the two hash columns, and there is no correct backfill for any of them. A fabricated tenant or a fabricated hash would silently forge the audit chain. Failing to run is the right outcome; guessing is not.

That is the pattern to follow: if a migration cannot correctly transform existing data, make it refuse to run rather than write something plausible.

Making a schema change

# 1. Edit the schema
$EDITOR packages/database/prisma/schema.prisma

# 2. Generate a migration and apply it locally
pnpm --filter @openrunic/database db:migrate

# 3. Regenerate the client
pnpm --filter @openrunic/database db:generate

# 4. Update the Zod write contract and enum tuples if needed
$EDITOR packages/database/src/schemas/ packages/database/src/enums.ts

# 5. Test
pnpm --filter @openrunic/database test
pnpm --filter api test

Do not run prisma format. It reflows the whole file and produces a diff nobody can review alongside a real change.

If you add an enum value, add it to the matching tuple in src/enums.ts. The parity proof at the bottom of that file turns a mismatch into a type-check failure rather than a runtime surprise.

If you add a model, add it to the tenant-scoped model list in src/tenant.ts. An integration test asserts the list against the schema, so forgetting fails the suite rather than silently opting the model out of tenant scoping.

What CI checks

The migration stage runs only when @openrunic/database is affected, so it stays off the critical path for most pull requests. When it runs, against a real Postgres 17 service container:

1. Immutability. Only additions under prisma/migrations.
2. Validity. prisma validate.
3. Applicability. prisma migrate deploy against an empty database, which proves the whole history applies in order.
4. No drift. prisma migrate diff --from-migrations ./prisma/migrations --to-schema ./prisma/schema.prisma --script, with comments and blank lines stripped. Anything remaining is real drift: either the schema changed without a migration, or a migration changed without the schema.

The migration stage exists so migration SQL is executed by CI from day one. Without it, a schema change merges green and first runs when an operator applies it by hand against a live database.

The drift check is the part that earns its keep, because it catches the two mistakes that are invisible in a code review of the schema alone.

Expand and contract

For a change that would break a running deployment, split it across releases rather than doing it in one migration:

1. Expand. Add the new column or table as nullable, deploy, and have the application write both.
2. Backfill. Migrate existing rows, in a migration or as a job, depending on volume.
3. Switch. Move reads to the new shape and deploy.
4. Contract. Drop the old column in a later migration, once nothing reads it.

Each step is its own migration and its own pull request. This is why the form engine promotes into a fixed table rather than generating per-form DDL: dynamic DDL would fight this discipline directly.

Upgrading a deployment

There is no released version and no upgrade procedure yet. What is settled:

• The boot-time migrate container being built on the feat/ops-selfhost branch applies pending migrations before the API is allowed to start, and is idempotent so repeated runs are safe.
• Zero-downtime upgrades depend on the expand-and-contract discipline above. There is no substitute for it at the application layer.
• Back up before upgrading, and know that you can restore. See Backup and restore.

Upgrading the toolchain

Node, pnpm, and the framework versions are pinned in one place each, listed on Dependency policy. Because the repository declares one Node version for everything, a runtime upgrade touches every workspace at once. That was an accepted cost of the monorepo, recorded in ADR-0001.

The CI Postgres image is pinned to postgres:17-alpine. Once a production database exists, pin it to the same major that runs there. Migration SQL proven against a newer server than production is not proof.

Related pages

• Data model
• Backup and restore
• CI pipeline
• Self-hosting
• Troubleshooting