PgVouch

PgVouch

An MCP server + CLI that lets AI assistants safely inspect, plan and _prove_ PostgreSQL migrations. The LLM proposes; deterministic code decides.

Real output against the seeded demo databases (1M transactions, 2M ledger entries) with the target broken as in step 2 below. Typing is real-time; command run times are shortened (the verify run took 14.0 s).

The problem

Writing ALTER TABLE is the easy part of a database migration. The hard parts are:

1. What differs? Schemas drift between environments (hotfixes, failed migrations, manual changes).
2. What will it lock? CREATE INDEX on a busy table blocks every write until it finishes. A "1 ms" ALTER TABLE can still cause an outage by waiting in the lock queue.
3. Is the data really the same? "Row counts match" is not proof.
4. Which rows are wrong? When proof fails, you need the exact rows, not "something in this 2M-row table".

AI assistants now write migrations, but they hallucinate objects, ignore locks and can't prove anything. PgVouch gives them (and you) read-only tools that answer those four questions, and it wraps every AI suggestion in deterministic checks.

Results (measured, not estimated)

All numbers come from npm run eval against the seeded Docker databases. The full tables are in evals/.

Application stall during the migration, measured on the 1M-row table with a probe query every ~10 ms (evals/results-rewrite.md):

What the LLM numbers show: a small local model is not reliable at migration planning. That's why PgVouch never trusts it. The validator rejected 14/17 plans (unparseable SQL, hallucinated or duplicate objects, blocking DDL). The 3 it accepted were *safe but incomplete*, and only the shadow run caught that (e.g. a missing index and a sequence left out when re-creating a dropped table). Deterministic rules plans passed all 17 (27/27 with the scenarios added later). Details: evals/results-llm.md.

What changed because of it: those numbers were measured when the validator alone decided. Now an LLM plan is accepted only if it passes the validator and a shadow run whose result matches the source exactly; a shadow failure is sent back to the LLM for one retry, then PgVouch falls back to the rules plan. So the 3 incomplete plans above would now be rejected. The LLM eval has not been re-run with the new pipeline yet, so there is no new number here.

The trade-off is visible: the rewrites that need a backfill take much longer in total, but the application keeps running. These are single runs on a laptop (Apple M1, 8 cores, Docker Desktop). Expect the absolute numbers to vary; the gap is the point.

How it works

flowchart LR

A["AI assistant (Claude Code, Cursor)"] -- "MCP (JSON-RPC, stdio)" --> S U["You (terminal)"] -- CLI --> S subgraph S["PgVouch core"] I["F1 introspect pg_catalog"] --> D["F2 diff (pure function)"] D --> P["F7 planner LLM + guardrail validator"] L["F5 lock analyzer (Postgres parser)"] --> R["F6 safe rewrites"] R --> P P --> SH["F10 shadow run (throwaway container)"] V["F3 chunked checksums"] --> B["F4 bisection"] P --> RC["F12 receipt (SHA-256)"] end S -- "read-only SQL" --> SRC[("source DB")] S -- "read-only SQL" --> TGT[("target DB")] P -. "structured facts only, no rows" .-> LLM["Ollama (local) or Gemini"]

Golden rule: the LLM never touches a database and never decides pass/fail. It proposes; the parser, the rules and the shadow run decide.

Get started

There are two ways to use PgVouch:

Option A: use the npm package

1. Try it now, no database needed. Point it at any migration file:

npx pgvouch locks my_migration.sql --offline     # the locks each statement takes, and the risk

npx pgvouch rewrite my_migration.sql --offline # a safe, non-blocking version of the same migration

No migration file handy? Create one: echo "CREATE INDEX idx_amount ON transactions (amount);" > my_migration.sql

2. Install it (optional: npx pgvouch works without installing):

npm install -g pgvouch

pgvouch --help

3. Point it at your databases. Create a read-only role on each database (the SQL is here), then put a .env file in the folder you run PgVouch from:

SOURCE_DATABASE_URL=postgres://pgvouch_ro:PASSWORD@staging-host:5432/your_db

TARGET_DATABASE_URL=postgres://pgvouch_ro:PASSWORD@prod-host:5432/your_db PGVOUCH_LLM=none # rules-only plans; set it to ollama to let a local LLM propose plans

Variables already set in your environment take precedence over .env. Then check that both connections are read-only:

pgvouch doctor   # must say read_only=true and write privileges: none

4. Run it:

pgvouch diff                                      # schema drift (exit code 1 if any)

pgvouch verify --rows # checksums + exact differing rows pgvouch verify --rows --recheck 3 # target still being replicated to: ignore rows in flight pgvouch locks my_migration.sql # lock impact per statement, with real table sizes pgvouch preflight my_migration.sql # would it have to wait for locks RIGHT NOW? pgvouch rewrite my_migration.sql # safe multi-step script pgvouch review --format markdown migrations/*.sql # PR-comment report (offline, used by the GitHub Action) pgvouch plan --no-llm # rules-only plan that makes target match source pgvouch plan # LLM plan (Ollama) behind guardrails pgvouch shadow # plan, then prove it on a throwaway container (needs Docker) pgvouch receipt --shadow # hashed audit record pgvouch receipt-verify pgvouch-receipt.json

locks --fail-on high exits with code 1, so it can gate a CI pipeline on risky migrations.

preflight answers a different question: not "what will this lock?" but "is anyone holding or waiting for a conflicting lock right now?". It lists the sessions it would queue behind (pid, user, application, state, transaction age; never their query text), and flags that CREATE INDEX CONCURRENTLY waits for every older transaction in the database. It exits with code 1 if the migration would wait. PgVouch never terminates sessions.

5. Also: use it from an AI assistant (npx -y pgvouch mcp), or review every migration pull request with the GitHub Action (step 4 there).

Option B: clone the repo (demo databases included)

The repo includes two seeded Postgres databases, so you can try every feature without touching a real database. Requirements: Node 22 (or 20.12+) and Docker.

git clone https://github.com/Shubh-Sgr/pgvouch.git && cd pgvouch

npm in