schemaproof/examples/risky-migration.sql
caelum0x 3eb9b14677 feat: initial SchemaProof CLI — live-database-aware Postgres migration safety checker
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>
2026-10-26 15:53:29 +03:00

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);