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.
CREATE/DROP INDEX CONCURRENTLY and REINDEX ... CONCURRENTLY cannot run
inside a Postgres transaction block -- it's a hard error ("cannot run
inside a transaction block"), not a lock-duration risk, and it aborts
the whole transaction including every earlier statement already run in
it. Verified against a real local Postgres 14: a migration file with
`BEGIN; ALTER TABLE ...; CREATE INDEX CONCURRENTLY ...; COMMIT;` really
does fail exactly that way and rolls back the ALTER TABLE with it.
Until now SchemaProof never caught this at all -- `applyRules()` only
ever sees one statement at a time (libpg_query parses BEGIN/COMMIT as
their own TransactionStmt nodes), and the existing CREATE INDEX rule
explicitly skips CONCURRENTLY statements since they're normally the
*safe* rewrite. Checked `check` against that exact file before this
change: zero findings, exit code 0 (INFO) -- a false "safe" verdict for
a migration that is guaranteed to blow up in CI.
Adds a whole-file pass (`applyFileLevelRules`) that tracks explicit
BEGIN/COMMIT boundaries across the full statement list and flags any
CONCURRENTLY operation found inside one as `block`
(`concurrently-inside-transaction-block`). Also flags the softer but
common real case -- no explicit BEGIN in the file, but multiple
statements sharing it -- as `caution`
(`concurrently-may-run-inside-framework-transaction`), since Rails,
Prisma Migrate, Flyway, and node-pg-migrate all wrap a migration file
in one implicit transaction by default, which fails the exact same way.
Also fixes a stale --help reference to templates/github-action.yml,
which was intentionally removed in 83a9831 but left dangling in the
CLI's own usage text.
Verified end-to-end against a real local Postgres 14 instance
(schemaproof_test db): the exact BEGIN/CREATE INDEX CONCURRENTLY/COMMIT
repro now reports BLOCK/exit 2 instead of INFO/exit 0, confirmed the
real Postgres error text matches
("CREATE INDEX CONCURRENTLY cannot run inside a transaction block"),
confirmed DROP INDEX CONCURRENTLY inside BEGIN is caught the same way,
confirmed a standalone CONCURRENTLY statement (no other statements in
the file) stays clean INFO/exit 0 (no false positive), confirmed the
implicit multi-statement case correctly downgrades to CAUTION/exit 1,
and re-ran both existing examples/*.sql fixtures with and without
--db and --pr-comment to confirm no regressions in existing rules.
`npx tsc --noEmit` clean.
Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
Owner correction: no CI/CD/GitHub Actions work, full stop — that rule
covers a workflow YAML template shipped for consumers to copy, not just
this repo's own CI. Removed templates/github-action.yml and the
package.json "files"/README references to it. --pr-comment itself stays:
it's a real output-format flag on the CLI (markdown instead of plain
text/JSON), not CI config, and still verified working standalone.
Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
CLI `check` gains --pr-comment: a GitHub-flavored markdown verdict table
plus a collapsible per-statement detail section, built from the same
report/worstOverall the plain-text and --json paths already compute.
templates/github-action.yml is a ready-to-copy workflow (shipped inside
the npm package via package.json's "files") that finds every changed
.sql file in a PR, runs `schemaproof check --pr-comment` against each,
and posts/updates one PR comment via peter-evans/create-or-update-comment,
failing the job only on a real BLOCK verdict.
Verified: npx tsc clean, plain-text/--json output unchanged, --pr-comment
run against examples/risky-migration.sql produces correct markdown with
exit code 2 (BLOCK), examples/safe-migration.sql exits 0. Action YAML
parses clean via PyYAML.
Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
Clones --db onto the same server (pg_dump/pg_restore), replays a concurrent
read burst against every table the migration touches, then runs each
statement for real and measures how long it actually took to acquire its
lock plus how long a fresh query submitted at the same moment was blocked
behind it — real seconds, not a row-count estimate.
Verified end to end against a local seeded 200k-row Postgres table: a plain
SET NOT NULL came back at 12ms; a volatile-default ADD COLUMN (forces a full
table rewrite) measured 586ms with a concurrent query blocked 583ms behind
it. Also verified cleanup (clone dropped by default, --keep-clone works),
and the error path (invalid statement reports and doesn't leak the clone).
Neon-branch tier stays on the roadmap — needs a real NEON_API_KEY to build
and verify against, not available in this environment.
Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
Parses migrations with the real Postgres parser (libpg_query via libpg-query),
classifies the lock each statement takes, and — unlike static-only linters
(Squawk, Atlas free tier) — optionally connects to the real target database
(Supabase/Neon/plain Postgres) to re-score severity against that table's
actual row count, dead-tuple ratio, and currently-active lock holders, with
an optional AI explain+rewrite layer gated on ANTHROPIC_API_KEY.
Verified against a real local Postgres with a seeded 150k-row table and a
real concurrent lock holder: same static rule produces BLOCK on the large
table and INFO on a 3-row table, and escalates to BLOCK when a real backend
is holding a lock on the target table at check time.
Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>