Database Migrations

SQL migrations live in zmsbackend/migrations/. The migration runner is bin/migrate in the zmsbackend module:

zmsbackend/bin/migrate

For a full local database reset (base schema, test data, migrations, cron jobs), see Local Database and Cache Operations.

Running migrations locally

Check which migrations are pending (dry run):

podman exec -it zms-web bash -lc "cd zmsbackend && bin/migrate"

Apply all pending migrations:

podman exec -it zms-web bash -lc "cd zmsbackend && bin/migrate --update"

Apply only expand-phase migrations:

podman exec -it zms-web bash -lc "cd zmsbackend && bin/migrate --update --phase=expand"

Apply only contract-phase migrations:

podman exec -it zms-web bash -lc "cd zmsbackend && bin/migrate --update --phase=contract"

Locally you usually run --update without --phase to apply everything at once. The phase flags are mainly for testing the same order used in deployment.

Expand and contract

Breaking schema changes (renames, drops) are split into two steps so old and new application code can run at the same time during rollout:

1. Expand — add the new schema (new column, view, backfill, sync triggers). Old code keeps working.
2. Deploy code — release that uses the new names.
3. Contract — remove the old schema (drop column, drop view, drop triggers). Only safe once nothing uses the old names.

Additive changes (ADD COLUMN, new tables) only need an expand step — no contract file.

For the full deployment pipeline and MariaDB patterns, see Zero-downtime deployments and database migrations.

Filename prefixes

All migration files go in zmsbackend/migrations/. The filename tells the runner which phase applies:

Change type	Filename pattern	--phase	Example
Additive only (ADD COLUMN, new table, indexes, seeds)	no prefix	expand	20260702-add-standort-custom-text-field3.sql
Expand step of a rename or dual-column transition	*-expand-*	expand	20260702-expand-standort-add-scope-id.sql
Contract step (drop column, drop view, drop trigger)	*-contract-*	contract	20260702-contract-drop-standort-view.sql

Quick rules:

• ADD COLUMN → unprefixed (or *-expand-* if paired with a later contract). Never *-contract-*.
• DROP COLUMN / DROP TABLE / DROP VIEW → *-contract-* only. Never unprefixed.
• RENAME COLUMN / RENAME TABLE → never unprefixed. Use an *-expand-* + *-contract-* pair in the same release.

Without --phase, bin/migrate runs every pending file (legacy behaviour). With --phase=expand, it skips *-contract-* files. With --phase=contract, it runs only *-contract-* files.