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>
23 lines
981 B
SQL
23 lines
981 B
SQL
-- A real migration file with several classic footguns, meant to be run
|
|
-- against a real seeded Postgres database with `schemaproof check`.
|
|
|
|
-- 1. Non-concurrent index on a 150k-row table: takes a SHARE lock for the
|
|
-- whole build, blocking writes.
|
|
CREATE INDEX idx_orders_status ON orders (status);
|
|
|
|
-- 2. SET NOT NULL forces a full-table verification scan under ACCESS
|
|
-- EXCLUSIVE.
|
|
ALTER TABLE orders ALTER COLUMN amount SET NOT NULL;
|
|
|
|
-- 3. Adding a FOREIGN KEY without NOT VALID scans + locks both tables.
|
|
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
|
|
FOREIGN KEY (customer_id) REFERENCES customers (id);
|
|
|
|
-- 4. DROP COLUMN on a column still read by the previous app release.
|
|
ALTER TABLE orders DROP COLUMN legacy_col;
|
|
|
|
-- 5. Same CREATE INDEX pattern against a tiny 3-row table -- the rule still
|
|
-- fires (it's always technically true) but live stats should show this
|
|
-- one is not actually dangerous today.
|
|
CREATE INDEX idx_tags_label ON tags (label);
|