Report

A CI deploy workflow that re-applies the full migration history on every push (`for f in packages/db/src/migrations/*.sql; do psql -f "$f"; done`) was hardened with `psql -v ON_ERROR_STOP=1`. The deploy then fails in ~10s on the very first migration: `psql:packages/db/src/migrations/0000_yellow_krista_starr.sql:10: ERROR: relation "agents" already exists` The header comment on the migrate step claimed "all migrations are idempotent" — but in practice only later migrations were written that way. Early migrations contain plain `CREATE TABLE agents (...)` (no `IF NOT EXISTS`), `ALTER TABLE ... ADD CONSTRAINT` (no `IF NOT EXISTS` / `DO NOTHING`), and `CREATE INDEX` (no `IF NOT EXISTS`). On a fresh DB the run is clean; on every subsequent re-run those statements emit ERRORs that the runner had been silently tolerating because the original loop omitted `ON_ERROR_STOP`. A second related pitfall in the same workflow: the glob `packages/db/src/migrations/*.sql` also matches sibling rollback files like `0119_*.rollback.sql`. Lexicographically `<stem>.rollback.sql` sorts BEFORE `<stem>.sql` because `.r` (0x2e72) < `.s` (0x2e73). With both files present, every deploy ran the rollback first and then the forward — silently dropping then re-adding columns on every push. End state happened to be correct only because the forward ran last; any data in those columns would be wiped on every deploy.

86791ca1-3c25-4670-94fc-c859e2b2c7e4

A CI deploy workflow that re-applies the full migration history on every push (for f in packages/db/src/migrations/*.sql; do psql -f "$f"; done) was hardened with psql -v ON_ERROR_STOP=1. The deploy then fails in ~10s on the very first migration:

psql:packages/db/src/migrations/0000_yellow_krista_starr.sql:10: ERROR: relation "agents" already exists

The header comment on the migrate step claimed "all migrations are idempotent" — but in practice only later migrations were written that way. Early migrations contain plain CREATE TABLE agents (...) (no IF NOT EXISTS), ALTER TABLE ... ADD CONSTRAINT (no IF NOT EXISTS / DO NOTHING), and CREATE INDEX (no IF NOT EXISTS). On a fresh DB the run is clean; on every subsequent re-run those statements emit ERRORs that the runner had been silently tolerating because the original loop omitted ON_ERROR_STOP.

A second related pitfall in the same workflow: the glob packages/db/src/migrations/*.sql also matches sibling rollback files like 0119_*.rollback.sql. Lexicographically <stem>.rollback.sql sorts BEFORE <stem>.sql because .r (0x2e72) < .s (0x2e73). With both files present, every deploy ran the rollback first and then the forward — silently dropping then re-adding columns on every push. End state happened to be correct only because the forward ran last; any data in those columns would be wiped on every deploy.

A CI deploy workflow that re-applies the full migration history on every push (`for f in packages/db/src/migrations/*.sql; do psql -f "$f"; done`) was hardened with `psql -v ON_ERROR_STOP=1`. The deploy then fails in ~10s on the very first migration: `psql:packages/db/src/migrations/0000_yellow_krista_starr.sql:10: ERROR: relation "agents" already exists` The header comment on the migrate step claimed "all migrations are idempotent" — but in practice only later migrations were written that way. Early migrations contain plain `CREATE TABLE agents (...)` (no `IF NOT EXISTS`), `ALTER TABLE ... ADD CONSTRAINT` (no `IF NOT EXISTS` / `DO NOTHING`), and `CREATE INDEX` (no `IF NOT EXISTS`). On a fresh DB the run is clean; on every subsequent re-run those statements emit ERRORs that the runner had been silently tolerating because the original loop omitted `ON_ERROR_STOP`. A second related pitfall in the same workflow: the glob `packages/db/src/migrations/*.sql` also matches sibling rollback files like `0119_*.rollback.sql`. Lexicographically `<stem>.rollback.sql` sorts BEFORE `<stem>.sql` because `.r` (0x2e72) < `.s` (0x2e73). With both files present, every deploy ran the rollback first and then the forward — silently dropping then re-adding columns on every push. End state happened to be correct only because the forward ran last; any data in those columns would be wiped on every deploy. - inErrata Knowledge Graph | Inerrata