argen: emit CREATE TABLE in migrations when a new model is introduced (existing-install replay fails)

[Nikolo]

Summary

When a new model is introduced via argen, the generated migration +up section contains only ALTER TABLE ... ADD COLUMN IF NOT EXISTS statements — no CREATE TABLE. The schema.sql artefact does carry the CREATE TABLE IF NOT EXISTS, but schema.sql is only applied on fresh installs. On existing installs migrations are replayed sequentially against the live DB, and the migration that introduces the new table fails on its first ALTER:

ERROR:  relation "<new_table>" does not exist

Result: every release that adds a table breaks every existing deploy.

Concrete example (real prod incident)

Migration 005_add_table_vt_account_assignment_add_table_supervisor_pending_stops.sql (advix-consulting/virtualIT-orchestrator-v2) — +up:

ALTER TABLE vt_account_assignment ADD COLUMN IF NOT EXISTS vtid VARCHAR(128) NOT NULL DEFAULT '';
ALTER TABLE vt_account_assignment ADD COLUMN IF NOT EXISTS accountname VARCHAR(128) NOT NULL DEFAULT '';
ALTER TABLE vt_account_assignment ADD COLUMN IF NOT EXISTS spawnedat TIMESTAMP NOT NULL DEFAULT '0001-01-01 00:00:00';
CREATE UNIQUE INDEX IF NOT EXISTS vt_account_assignment_pkey ON vt_account_assignment (vtid ASC);
CREATE INDEX IF NOT EXISTS idx_vt_account_assignment_byaccountname ON vt_account_assignment (accountname ASC);
ALTER TABLE supervisor_pending_stops ADD COLUMN IF NOT EXISTS vtid VARCHAR(128) NOT NULL DEFAULT '';
ALTER TABLE supervisor_pending_stops ADD COLUMN IF NOT EXISTS sentat TIMESTAMP NOT NULL DEFAULT '0001-01-01 00:00:00';
ALTER TABLE supervisor_pending_stops ADD COLUMN IF NOT EXISTS reason VARCHAR(64) NOT NULL DEFAULT '';
CREATE UNIQUE INDEX IF NOT EXISTS supervisor_pending_stops_pkey ON supervisor_pending_stops (vtid ASC);
CREATE INDEX IF NOT EXISTS idx_supervisor_pending_stops_bysentat ON supervisor_pending_stops (sentat ASC);

schema.sql (same release) does have CREATE TABLE IF NOT EXISTS vt_account_assignment (...) and CREATE TABLE IF NOT EXISTS supervisor_pending_stops (...). But the deploy role applies migrations sequentially, never reapplying schema.sql. The migration fails immediately, and the staging deploy goes red.

This took down our staging deploy and would have hit prod on the next sync-upstream merge.

Expected behavior

When a migration introduces a new table (no prior migration touches it), argen should emit CREATE TABLE IF NOT EXISTS <new_table> (...) at the top of the +up section, before any ALTER TABLE/CREATE INDEX statements that target the new table:

-- +up
CREATE TABLE IF NOT EXISTS vt_account_assignment (
    vtid VARCHAR(128) NOT NULL DEFAULT '',
    accountname VARCHAR(128) NOT NULL DEFAULT '',
    spawnedat TIMESTAMP NOT NULL DEFAULT '0001-01-01 00:00:00',
    PRIMARY KEY (vtid)
);

ALTER TABLE vt_account_assignment ADD COLUMN IF NOT EXISTS vtid VARCHAR(128) NOT NULL DEFAULT '';
-- ... existing ALTERs, now no-ops on installs created by this migration but
--     still meaningful for any historical install lacking those columns.
CREATE INDEX IF NOT EXISTS idx_vt_account_assignment_byaccountname ON vt_account_assignment (accountname ASC);

This is symmetric with the existing pattern argen already uses for indexes (CREATE UNIQUE INDEX IF NOT EXISTS is emitted in both schema.sql and migrations).

Workaround (current)

We added a fork-owned migration 004a_advix_pre_create_vt_account_assignment_supervisor_pending_stops.sql in our infra repo (lex-sorted between 004 and 005) that pre-creates the missing tables before migration 005 runs. That keeps 005 (upstream-emitted, immutable from our side) functional, but it means every new-table migration from upstream needs a manual fork-side companion until argen is fixed.

Why this matters

• Fresh install: schema.sql only — fine.
• Existing install: migrations are the only path — and a migration that introduces a new table cannot succeed there, because no preceding migration created it.
• Workaround scales linearly with new tables and is invisible until staging deploy actually fails on the next-after-add release.

Proposed change

Update argen's migration generator: when a model is newly added (no prior migration mutates the table), emit a CREATE TABLE IF NOT EXISTS block at the top of the migration's +up section, mirroring the schema.sql definition for that table. Keep the existing ALTER TABLE ADD COLUMN IF NOT EXISTS statements after — they become no-ops for installs created by this migration, but stay relevant for any install that pre-existed the model.

The corresponding +down should drop the table (DROP TABLE IF EXISTS <new_table>) instead of dropping individual columns. Today's +down only contains ALTER TABLE ... DROP COLUMN IF EXISTS and DROP INDEX, leaving an empty table behind on rollback.

Affected projects

advix-consulting/virtualIT-orchestrator-v2 — migration 005_* is the current incident origin; production rescue tracked downstream as VT-573.

Related

• argen-generated migrations should be idempotent (emit IF NOT EXISTS for ALTER ... ADD COLUMN) #5 — ALTER ... ADD COLUMN IF NOT EXISTS (same idempotency family, different statement). The migration above already follows argen-generated migrations should be idempotent (emit IF NOT EXISTS for ALTER ... ADD COLUMN) #5 for ALTERs; the gap here is the missing CREATE TABLE.
• argen: нестабильная генерация schema.sql и schema.json #4 — argen determinism (unrelated, but in the same generator).