Database migrations

Migrations live in one file: backend/internal/repository/postgres/migrate.go, as a slice of {version, sql} pairs.

Around 257 migrations at the time of writing.

How they run

On start, the backend:

1. Creates schema_migrations if it does not exist.
2. Sorts the slice by version — ascending, regardless of where the entry sits in the file.
3. Skips any version already recorded.
4. Applies the rest, recording each.

An advisory lock is taken around the whole run, so two replicas starting together do not race.

Adding one

{"258_thing_that_changed", `
-- ── Why this table exists ──────────────────────────────────────────
--
-- The reason, in prose, for whoever reads this in a year.
CREATE TABLE IF NOT EXISTS thing (
    org_id     UUID        NOT NULL,
    ...
    PRIMARY KEY (org_id, ...)
);
CREATE INDEX IF NOT EXISTS idx_thing_q ON thing(org_id, bucket);
`},

The two rules

1. Backwards-compatible for one release

During a rolling deploy both versions are serving. The old code must work against the new schema for the length of the rollout.

• Adding a column: fine. Give it a default.
• Adding a table or index: fine.
• Dropping or renaming a column: two releases. Add the new one and start writing both; migrate; remove the old one next release.

This is also what makes a rollback within a release always safe.

2. Idempotent

IF NOT EXISTS everywhere. A migration that has partially applied — the process died halfway — must be safe to run again.

JSONB instead of a column

Several features store structured settings as JSONB. Adding a field to such a structure needs no migration: the Go struct is marshalled whole.

This is why, for example, clock alignment was added to SQL alert schedules with no schema change — schedule was already JSONB.

Use this deliberately, not as a habit. A field you will want to query or index belongs in a column.

Checking what has run

SELECT version FROM schema_migrations ORDER BY version DESC LIMIT 10;

Retention

Migrations create tables; the cleanup worker prunes them. A new high-volume table needs an entry in the cleanup worker's retention list, or it grows forever. See Data retention.