new-migration

name	new-migration
description	Use this skill when adding a new dbmate migration to db/migrations/. Enforces the YYYYMMDDHHMMSS filename prefix and the idempotency requirement. Migrations must be safe to apply against existing instances that may already have partial state.

Create a new dbmate migration

Migrations live in db/migrations/ and are managed by dbmate. Two rules apply:

1. Filename uses a 14-digit YYYYMMDDHHMMSS prefix, not Unix epoch seconds.
2. The SQL must be idempotent so it is safe to run against existing instances with partial state.

Rule 1: filename format

20260410120000_add_widgets.sql       # correct (date prefix)
1744300800_add_widgets.sql           # WRONG (epoch seconds)

Why

dbmate sorts migrations lexicographically by filename. Existing migrations use 14-digit date prefixes starting with the year (currently 2026...). Unix epoch seconds are 10 digits starting with 17..., which sorts before any 14-digit year prefix. A new migration with an epoch prefix would be inserted before 20260211052804_create_omniglass_schema.sql, attempting to alter or query tables that do not yet exist when migrations replay from scratch.

This has happened before. The fix was painful.

Rule 2: idempotency

Every new migration must be safe to run against an instance that may already have part or all of the change applied — whether from a prior partial run, manual recovery, or schema drift. A migration that fails because "table already exists" or "column already exists" is broken, not defensive.

Use the guarded forms:

-- migrate:up

-- Tables
CREATE TABLE IF NOT EXISTS omniglass.widgets (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- Columns
ALTER TABLE omniglass.widgets
    ADD COLUMN IF NOT EXISTS description TEXT;

-- Indexes
CREATE INDEX IF NOT EXISTS widgets_name_idx
    ON omniglass.widgets (name);

-- Seed data
INSERT INTO omniglass.widget_types (name, display_name)
VALUES ('default', 'Default')
ON CONFLICT (name) DO NOTHING;

-- migrate:down
DROP TABLE IF EXISTS omniglass.widgets;

For changes Postgres does not natively guard (e.g. enum values, complex constraints, function definitions), wrap them in a DO $$ ... END $$ block that checks pg_catalog first:

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_type t
        JOIN pg_enum e ON t.oid = e.enumtypid
        WHERE t.typname = 'widget_status' AND e.enumlabel = 'archived'
    ) THEN
        ALTER TYPE omniglass.widget_status ADD VALUE 'archived';
    END IF;
END $$;

The down migration must also be idempotent — use IF EXISTS on DROP statements.

Why idempotency

dbmate tracks completed migrations, so in the simple case it does not re-run them. But:

• A migration that fails partway through leaves the instance in a partial state, and the retry must succeed.
• Manual recovery, hotfixes, or out-of-band schema changes can leave existing instances with the new object already present.
• New columns added to Zabbix tables via zabbix-patch (using the og_ prefix) may already exist in some instances by the time a corresponding dbmate migration touches them.

A migration that hard-fails in any of these cases blocks all subsequent migrations and requires hand intervention. Idempotency removes that class of failure entirely.

Procedure

1. Find the latest existing migration

ls db/migrations/ | tail -1

2. Pick a timestamp strictly greater than the latest

The simplest approach: use the current UTC date and time, formatted as YYYYMMDDHHMMSS:

date -u +%Y%m%d%H%M%S

If that value is somehow not strictly greater than the latest existing filename (e.g. clock skew, or a teammate just merged a future-dated migration), increment by one second and try again.

3. Create the file

touch db/migrations/<timestamp>_<snake_case_description>.sql

Description is short and snake_case: add_og_min_down_severity_to_sla, create_systems_tables, update_dark_graph_theme_colors.

4. Write the migration

dbmate uses an up/down format. Both directions must use the guarded forms from Rule 2 — see the "Rule 2: idempotency" section above for the full set of patterns (tables, columns, indexes, seed data, enum values).

The minimal shape:

-- migrate:up
CREATE TABLE IF NOT EXISTS omniglass.widgets (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- migrate:down
DROP TABLE IF EXISTS omniglass.widgets;

5. Validate

ls db/migrations/ | tail -3   # confirm the new file sorts last

Then run the migration in the dev database to confirm both up and down work cleanly.

Scope reminder

dbmate migrations are for Omniglass-owned tables only (in the omniglass schema). Modifications to Zabbix tables go through source patches via the zabbix-patch skill, not through dbmate.