# pg-migration-guard

**An MCP server and CLI that stops AI coding agents (and humans) from shipping Postgres migrations that lock production tables.**

AI coding tools are great at migration syntax. They don't know your table has 40 million rows, that `CREATE INDEX` blocks every write until it finishes, or that a "harmless" `ALTER TABLE` waiting on a lock queues every query behind it. The migration finishes in 10 ms on a laptop and takes the checkout API down in production.

`pg-migration-guard` gives the agent that missing knowledge. Connect it to Claude Code, Claude Desktop, Cursor or any MCP client, and the agent can check a migration before it runs it. Each finding comes with the safer rewrite.

```
[HIGH] line 6  create-index-not-concurrently
  CREATE INDEX idx_orders_customer ON orders (customer_id)
  Why: CREATE INDEX takes a SHARE lock: reads keep working, but every INSERT, UPDATE and
       DELETE on the table waits until the index is built. On a large table that can be minutes.
  Fix: Use `CREATE INDEX CONCURRENTLY` (outside a transaction).
```

- **Zero runtime dependencies.** The MCP protocol layer (JSON-RPC 2.0 over stdio) is implemented directly. Nothing to audit in your supply chain.
- **Deterministic.** Rules, not a model. Same SQL in, same answer out. That makes it safe to use as a gate.
- **Works in CI too.** The CLI exits non-zero on high-severity findings.

## What it checks

| Severity | Rule | Why it hurts |
|---|---|---|
| high | `create-index-not-concurrently` | `SHARE` lock blocks all writes during the build |
| high | `concurrently-in-transaction` | `CONCURRENTLY` fails inside a transaction (most migration tools use one) |
| high | `reindex-not-concurrently` | Blocks writes, and reads that use the index |
| high | `add-column-rewrite` | Volatile default, serial, or stored generated column rewrites every row (any default before PG 11) |
| high | `set-not-null` | Full scan under `ACCESS EXCLUSIVE` (recognises the PG 12+ `CHECK ... VALIDATE` workaround) |
| high | `alter-column-type` | Usually a full table and index rewrite |
| high | `add-foreign-key-validated` | Scans while holding `SHARE ROW EXCLUSIVE` on *both* tables |
| high | `add-unique-or-pk-without-index` | Builds a unique index under `ACCESS EXCLUSIVE` |
| high | `full-table-lock-maintenance` | `VACUUM FULL` / `CLUSTER` lock the table for the whole run |
| medium | `missing-lock-timeout` | A blocked `ALTER` queues every query behind it |
| medium | `add-check-validated` | Full scan under `ACCESS EXCLUSIVE` |
| medium | `add-column-not-null-no-default` | Fails on any non-empty table |
| medium | `drop-index-not-concurrently` | `ACCESS EXCLUSIVE` on the table |
| medium | `rename` / `drop-column` / `drop-table` | Breaks app instances still running the old code |

Tables created earlier in the same migration are exempt, because they are empty.

## Use it from an AI agent (MCP)

```bash
git clone https://github.com/saimulhasnain-dev/pg-migration-guard && cd pg-migration-guard
npm install && npm run build
```

**Claude Code**

```bash
claude mcp add pg-migration-guard -- node /absolute/path/to/pg-migration-guard/dist/src/server.js
```

**Claude Desktop / Cursor** (`claude_desktop_config.json` or `.cursor/mcp.json`)

```json
{
  "mcpServers": {
    "pg-migration-guard": {
      "command": "node",
      "args": ["/absolute/path/to/pg-migration-guard/dist/src/server.js"]
    }
  }
}
```

Then add one line to your project's `CLAUDE.md` / agent rules:

> Before running or committing any Postgres migration, call `analyze_migration` and apply its fixes.

### Tools

| Tool | Input | Returns |
|---|---|---|
| `analyze_migration` | `sql` (required), `postgres_version` (default 16), `assume_transaction` | Readable report + `structuredContent` with every finding |
| `list_rules` | none | All rules and severities |

## Use it in CI

```bash
node dist/src/cli.js migrations/*.sql                 # fail on high
node dist/src/cli.js --fail-on medium --tx --pg 15 db/migrate/*.sql
node dist/src/cli.js --json migrations/2026_add_index.sql
```

`--tx` tells it your tool wraps each file in a transaction (Rails, Django, Prisma and Knex do by default), so `CONCURRENTLY` inside one gets flagged.

## Try it

```bash
npm run build
node dist/src/cli.js examples/risky.sql   # 5 high, 2 medium
node dist/src/cli.js examples/safe.sql    # the same changes, done safely: clean
npm test                                  # 19 tests, including a real MCP stdio session
```

## Limitations

It's pattern-based, not a full SQL parser. It doesn't look inside `DO` blocks or function bodies, and it can't know your table sizes. A finding on a 200-row lookup table may be fine to ignore. Treat it as a reviewer that never forgets the lock rules, not as a replacement for knowing your data.

## License

MIT