Lint rules: migrations

database:
  migration_safety:
    enabled: true                  # false turns the whole lane off
    unsafe_add_column: error       # error | warn | off  (also governs set-not-null-without-backfill)
    destructive_change: error      # error | warn | off
    volatile_default: warn         # error | warn | off
    require_compat_declaration: false   # true makes the two migration-compat rules below apply

-- bad
ALTER TABLE orders DROP COLUMN notes;

-- fix: stop reading the column in one release, drop it in a later migration.
-- If the loss is intended, say so in the file:
-- forge:allow-destructive
ALTER TABLE orders DROP COLUMN notes;

✗ db/migrations/20261103000100_add_orders_status.up.sql:2 [unsafe-add-not-null-column] ADD COLUMN ... NOT NULL without a DEFAULT fails on populated tables; add nullable column, backfill, then SET NOT NULL

-- bad
ALTER TABLE orders ADD COLUMN status TEXT NOT NULL;

-- fix: nullable, backfill, then tighten, all in one migration
ALTER TABLE orders ADD COLUMN status TEXT;
UPDATE orders SET status = 'open' WHERE status IS NULL;
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

-- bad
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

-- fix: backfill first, in the same file (a generated column added in the file is also accepted)
UPDATE orders SET status = 'open' WHERE status IS NULL;
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

-- bad
ALTER TABLE orders ADD COLUMN opened_at TIMESTAMPTZ DEFAULT clock_timestamp();

-- fix: add it nullable, backfill, then set the default for new rows
ALTER TABLE orders ADD COLUMN opened_at TIMESTAMPTZ;
UPDATE orders SET opened_at = created_at WHERE opened_at IS NULL;
ALTER TABLE orders ALTER COLUMN opened_at SET DEFAULT now();

db/migrations/20261103000000_create_orders.down.sql   # bad: delete it

# bad
db/migrations/20261103000000_add_status.up.sql
db/migrations/20261103000000_add_note.up.sql

# fix: re-version the newer file
forge db migration new add_note

# bad
db/migrations/0007_add_note.up.sql
# fix: create the file with forge, which stamps a fresh UTC version
forge db migration new add_note

# main already has 20261103120000_x.up.sql
db/migrations/20261103090000_y.up.sql      # bad: below the head
# fix: re-version it above the head
forge db migration new y

-- bad: two statements in one file
CREATE INDEX CONCURRENTLY orders_customer_idx ON orders (customer);
ALTER TABLE orders ADD COLUMN note TEXT;

-- fix: each such statement gets a migration file of its own, holding only that statement
-- 20261103000200_orders_customer_idx.up.sql
CREATE INDEX CONCURRENTLY orders_customer_idx ON orders (customer);

-- bad
ALTER TYPE order_status ADD VALUE 'refunded';
UPDATE orders SET status = 'refunded' WHERE refunded_at IS NOT NULL;

-- fix: add the value in one migration, use it in the next

-- bad: on a migration that is not the first, or a declaration the migrator cannot read
-- fix: declare the replaced history on the FIRST migration only (the tool is goose or forge)
-- forge:adopts forge 20260101000001 20260101000002

-- bad: no declaration (undeclared)
ALTER TABLE orders ADD COLUMN note TEXT;

-- bad: a declaration with no reason
-- forge:backward-compatible

-- bad: both declarations (conflict; the migrator reads it as NOT compatible)
-- forge:backward-compatible — old code ignores the column
-- forge:breaks-rollback old code reads the dropped column

-- fix: one declaration, with the reason on the same line
-- forge:backward-compatible — new nullable column, the running release never reads it
ALTER TABLE orders ADD COLUMN note TEXT;