caelum0x 5c3b2f55df fix: now()/CURRENT_TIMESTAMP/CURRENT_DATE falsely flagged as forcing a table rewrite
add-column-volatile-default treated every non-literal DEFAULT (any FuncCall
or SQLValueFunction) as "possibly volatile" and returned BLOCK, including
`DEFAULT now()` / `CURRENT_TIMESTAMP` / `CURRENT_DATE` -- one of the most
common real-world migration patterns (created_at/updated_at columns).

Verified live against a real seeded Postgres 14 instance (200k-row table,
not guessed from docs): ADD COLUMN ... DEFAULT now()/CURRENT_TIMESTAMP/
CURRENT_DATE left pg_relation_filenode() unchanged and
pg_attribute.atthasmissing=true (fast-default path, no rewrite) -- same as
a literal constant. By contrast DEFAULT random() (genuinely VOLATILE,
pg_proc.provolatile='v') did change the relfilenode and gave existing rows
distinct per-row values -- a real rewrite, confirming gen_random_uuid(),
nextval(...), clock_timestamp() must keep returning BLOCK.

Fix: treat SQLValueFunction nodes (CURRENT_TIMESTAMP/CURRENT_DATE/
CURRENT_TIME/etc.) as safe, and allow-list the now() FuncCall by name (it's
STABLE, not VOLATILE). Re-ran existing examples/safe-migration.sql and
risky-migration.sql -- no regressions (same CAUTION/BLOCK verdicts as
before). Added examples/stable-vs-volatile-default.sql as a real repro.
`npx tsc --noEmit` clean; `npm run build` + compiled bin/schemaproof.mjs
re-verified the same fix end to end.
2026-10-29 02:36:44 +03:00

SchemaProof

Know what a Postgres migration will actually do to your table, right now — not what it would do to an empty staging copy.

npx schemaproof check migration.sql --db "$DATABASE_URL"

SchemaProof is a free, open-source (MIT) CLI for Supabase, Neon, and plain Postgres teams. It parses your migration with the real PostgreSQL parser (libpg_query, the same parser engine psql itself links against), classifies the lock every statement takes, and — this is the part most migration linters skip — connects to the actual target database and reads its real pg_class / pg_stat_user_tables / pg_locks state before deciding how serious that lock really is for this table, right now.

Why this exists

Squawk, Atlas's free tier, and most migration linters are static: they parse your .sql file and never touch a database, on purpose, so they're fast in CI. That's a reasonable tradeoff, but it means CREATE INDEX ON orders(...) gets flagged identically whether orders has 12 rows or 12 million, and whether your database is sitting idle or already has 40 connections queued behind a lock on that exact table. The handful of newer tools that do read live stats (e.g. a bare reltuples estimate) are either Neon-only demo projects with no linting layer, or keep the live-stats feature behind a separate command with no AI explanation on top.

SchemaProof combines both halves in one tool, with every rule free forever (no paywalled tier), and an optional AI layer that turns the rule + live-stat output into a plain-English explanation and a ready-to-run safe rewrite — citing your table's actual row count, dead-tuple ratio, and currently-active blockers, not generic advice.

What it checks today

Lock-classification rules (work with or without --db):

Rule What it catches
create-index-without-concurrently CREATE INDEX without CONCURRENTLY — SHARE lock blocks writes for the whole build
set-not-null ALTER COLUMN ... SET NOT NULL — ACCESS EXCLUSIVE during the verification scan
add-fk-without-not-valid ADD CONSTRAINT ... FOREIGN KEY without NOT VALID — locks both tables during inline validation
add-check-without-not-valid ADD CONSTRAINT ... CHECK without NOT VALID — same inline-validation cost
add-unique-builds-index-inline ADD CONSTRAINT ... UNIQUE — builds its backing index inline instead of CONCURRENTLY
alter-column-type ALTER COLUMN ... TYPE — full table rewrite under ACCESS EXCLUSIVE
drop-column Destructive + breaks any still-running old app instance mid-deploy
add-column-volatile-default ADD COLUMN ... DEFAULT random() / gen_random_uuid() / nextval(...) / clock_timestamp() (a genuinely VOLATILE default) — defeats Postgres's fast-default optimization, forces a rewrite. now() and CURRENT_TIMESTAMP/CURRENT_DATE/CURRENT_TIME are STABLE, not volatile, and do not trigger this rule — verified live that they take the same fast-default path (no rewrite) as a literal constant
add-notnull-column-without-default Will outright fail against any non-empty table
delete-without-where / truncate-table Data-loss guardrails
concurrently-inside-transaction-block CREATE/DROP INDEX CONCURRENTLY or REINDEX ... CONCURRENTLY wrapped in an explicit BEGIN/COMMIT in the same file — a guaranteed Postgres error ("cannot run inside a transaction block"), not a lock risk, that also rolls back every earlier statement in that transaction
concurrently-may-run-inside-framework-transaction Same CONCURRENTLY statement with no explicit BEGIN but sharing a file with other statements — most migration runners (Rails, Prisma Migrate, Flyway, node-pg-migrate) wrap a whole file in one implicit transaction by default, which fails the same way

With --db <connection-string>, every lock-sensitive finding above is re-scored against this database's real facts right now: pg_class row-count estimate and on-disk size, pg_stat_user_tables dead-tuple ratio and last autovacuum time, and an active pg_locks ⨝ pg_stat_activity scan for backends currently holding or waiting on a lock against that exact table. A rule that's always "block" in a static linter can come back info here if your table is genuinely tiny today — and escalate to block if something is already queued behind a lock on it at this exact moment.

Set ANTHROPIC_API_KEY (optional, same env-gated pattern as this project's sibling tools) to also get an AI-written explanation that cites those same real numbers plus a safe-rewrite SQL block. Unset, SchemaProof is still fully useful — the rule engine's own detail + live liveReasoning text is real, filled with that run's actual numbers, never canned.

Example (real run, not illustrative)

Seeded a local Postgres with a real 150,000-row orders table and a 3-row tags table, then ran the same CREATE INDEX ... (no CONCURRENTLY) pattern against both inside one migration file:

$ schemaproof check examples/risky-migration.sql --db "$DATABASE_URL"

CREATE INDEX idx_orders_status ON orders (status)
✖ [BLOCK] create-index-without-concurrently
  live: "orders" has an estimated 150,000 rows (15.4 MB) right now -- large
  enough that the lock this statement takes will be held for a duration
  proportional to that, not the few milliseconds a small staging copy shows.

CREATE INDEX idx_tags_label ON tags (label)
i [INFO] create-index-without-concurrently
  live: "tags" has only an estimated 3 rows right now, so this specific lock
  would likely clear in milliseconds today -- the rule is still correct in
  general, re-check this once the table has grown.

Overall verdict: BLOCK

Same SQL pattern, same static rule — different real verdict, because the two tables actually are different risks. With a second terminal holding a real transaction-level lock on orders during the same run, the tool also picked up 1 active backend(s) ... holding or waiting on a lock against "orders" at this exact moment and escalated accordingly — see examples/risky-migration.sql / examples/safe-migration.sql to reproduce.

Install

git clone https://github.com/caelum0x/schemaproof
cd schemaproof
npm install
npm run dev -- check path/to/migration.sql --db "$DATABASE_URL"

Or once published: npx schemaproof check migration.sql --db "$DATABASE_URL".

Provider notes

  • Supabase: detected from supabase.co/supabase.com host. If your connection string uses the pooled port 6543 (transaction-mode pgbouncer), SchemaProof warns that multi-statement safe rewrites (NOT VALID + VALIDATE CONSTRAINT, CREATE INDEX CONCURRENTLY) need a real session — use the direct connection (port 5432) or session-mode pooler.
  • Neon: detected from neon.tech/neon.build. Safe to point directly at a branch connection string — a branch is already a disposable copy-on-write clone, so there's no separate "test database" step needed.
  • Plain Postgres (RDS, local, anything else): works the same way, provider note is skipped.

Rehearse: real observed lock-wait time, not an estimate

npx schemaproof rehearse migration.sql --db "$DATABASE_URL"

check --db re-scores static rules against your table's real row count and current lock state, but it still never actually runs the migration. rehearse does: it clones --db onto the same Postgres server (needs CREATEDB there — the same local trust a normal createdb would need), restores the real data via pg_dump/pg_restore, replays a concurrent read burst against every table the migration touches, then runs each statement for real and reports how long it actually took to acquire its lock plus how long a fresh query submitted at the same moment was blocked behind it. The clone is dropped afterwards (--keep-clone to inspect it instead).

Real example, same seeded 200k-row table used above: a plain SET NOT NULL came back at 12ms (cheap, as the static rule-of-thumb would suggest), but ADD COLUMN ... DEFAULT md5(random()::text) — a volatile default that defeats the fast-default optimization and forces a full rewrite — measured 586ms, with a concurrent query genuinely blocked for 583ms behind it. That's the actual number your production deploy would stall for, not a row-count heuristic.

This is the local-clone tier. A Neon-branch tier (apply against a real copy-on-write branch instead of a same-server clone, so a slow pg_dump of a huge table is never on the critical path) is still on the roadmap — it needs a real NEON_API_KEY to build and verify against.

CI integration

Exit codes are CI-gate-ready: 0 = info/clean, 1 = caution, 2 = block. The simplest GitHub Actions step for your project:

- run: npx schemaproof check db/migrations/latest.sql --db "${{ secrets.STAGING_DATABASE_URL }}"

For a PR comment with the real verdict instead of just a pass/fail check, check takes a --pr-comment flag that prints a GitHub-flavored markdown report (a verdict table plus a collapsible per-statement detail section) instead of the plain-text or --json output — pipe it into whatever posts PR comments in your own CI setup:

npx schemaproof check migration.sql --db "$DATABASE_URL" --pr-comment

Roadmap

  • Neon-branch tier for rehearse (apply to a real disposable copy-on-write branch instead of a same-server clone) — needs a real NEON_API_KEY to build and verify against.
  • Supabase/Neon preview-branch GitHub Action that comments the verdict on the PR.
  • Postgres-version-aware rule tuning (e.g. PG17's ALTER TABLE ... ADD COLUMN fast-default edge cases).

License

MIT. See LICENSE and NOTICE.md for the one OSS dependency this project is built directly on top of (libpg-query, the real Postgres parser).

Description
Live-database-aware Postgres migration safety checker for Supabase
Readme MIT 74 KiB
Languages
TypeScript 79.1%
JavaScript 20.9%