io.github.cxk280/locksmith

Official

by cxk280 · TypeScript

Lints PostgreSQL migrations for dangerous locking operations and suggests safe rewrites.

Lint PostgreSQL migrations to prevent dangerous locking and suggest safe rewrites.

README.md6 KBView on GitHub

Locksmith 🔒

An MCP server that catches dangerous SQL migrations before they lock your database.

Most migrations look harmless and then take down production: a plain CREATE INDEX blocks every write for the length of the build; ALTER COLUMN ... TYPE rewrites the whole table under ACCESS EXCLUSIVE; SET NOT NULL scans every row. Which operations are safe — and the rewrite that makes the dangerous ones safe — is knowledge that lives in senior engineers' heads.

Locksmith encodes that knowledge as a tool an LLM agent (or a human) can call. Give it a PostgreSQL migration; it returns a PASS / REVIEW / BLOCK verdict, a finding for each risky statement (which lock it takes, why that's dangerous), and a concrete safe rewrite.

code

🛑 BLOCK — do not ship as written

## 🛑 CREATE INDEX without CONCURRENTLY  `create-index-non-concurrent` (line 5)
> `CREATE INDEX idx_users_email ON users (email)`
Problem: This index build will block all writes to the table until it completes.
Lock taken: SHARE (blocks writes)
Fix: Build the index with CREATE INDEX CONCURRENTLY, which does not block writes.
Suggested rewrite:
  CREATE INDEX CONCURRENTLY idx_users_email ON users (email)

Why an MCP server?

An agent can often reason about lock semantics — but not reproducibly, and not in a way you can test, audit, or trust to gate a deploy unsupervised. The same prompt may approve a table-rewriting ALTER one run and flag it the next, or miss it entirely in a long migration. Locksmith turns that probabilistic capability into a deterministic, tested tool: the lock semantics were verified once (against the PostgreSQL docs and the parser's real behavior, with a test suite pinning each rule) and now run identically every time, returning the same verdict plus a paste-ready fix — so "review this migration" stops being a guess.

Capabilities

Tools

analyze_migration(sql, assumeLargeTables?) → verdict + findings + safe rewrites (both human-readable Markdown and validated structured output).
explain_lock(query) → what a given Postgres lock mode blocks and what takes it.

Resources

locksmith://lock-matrix — the PostgreSQL table-level lock compatibility matrix.
locksmith://rules — the full rule catalog (id, severity, rationale) as JSON.

Prompts

review-migration — analyze a migration and summarize the risk as a PR comment.

Rule catalog

Rule	Severity	What it catches
`create-index-non-concurrent`	critical	`CREATE INDEX` without `CONCURRENTLY` (blocks writes)
`index-concurrently-in-transaction`	critical	`CONCURRENTLY` inside `BEGIN/COMMIT` (Postgres rejects it)
`add-column-not-null-no-default`	critical	`ADD COLUMN NOT NULL` with no default (fails / rewrites)
`alter-column-type`	critical	`ALTER COLUMN ... TYPE` (full table rewrite)
`add-column-volatile-default`	warning	`ADD COLUMN ... DEFAULT now()` etc. (rewrites table)
`set-not-null`	warning	`SET NOT NULL` (full scan under exclusive lock)
`add-foreign-key-validating`	warning	`ADD FOREIGN KEY` without `NOT VALID` (locks both tables)
`add-check-constraint-no-not-valid`	warning	`ADD CHECK` without `NOT VALID` (full scan)
`drop-column-or-table`	warning	destructive + breaks deployed code
`rename-column-or-table`	warning	breaks running app code

Suppressing a rule

Acknowledge a deliberate risk inline, eslint-style:

sql

-- locksmith:disable create-index-non-concurrent
CREATE INDEX idx_users_email ON users (email);

A bare -- locksmith:disable suppresses all rules for the next statement.

Install & run

No clone or build required — run it straight from npm:

bash

npx locksmith-mcp

Use with Claude Code

bash

claude mcp add locksmith -- npx -y locksmith-mcp

Or add to any MCP client config (Claude Desktop, etc.):

json

{
  "mcpServers": {
    "locksmith": { "command": "npx", "args": ["-y", "locksmith-mcp"] }
  }
}

Run from source instead

bash

git clone https://github.com/cxk280/locksmith.git && cd locksmith
npm install && npm run build
# then point your client at:  node /absolute/path/to/locksmith/dist/index.js

Try it with the MCP Inspector

bash

npx @modelcontextprotocol/inspector npx -y locksmith-mcp

Then call analyze_migration with the contents of examples/dangerous.sql.

Development

bash

npm run dev    # run from source with tsx
npm test       # vitest: per-rule + golden tests on examples/

Design notes

Deterministic. No clock, randomness, or network — same SQL in, same verdict out. That's what makes it testable and safe to drop into CI.
Hybrid parsing. Statements are parsed to an AST (pgsql-ast-parser) when the parser supports them; rules fall back to normalized text for Postgres clauses the parser doesn't model (NOT VALID, CONCURRENTLY). An unparseable statement degrades to a "review manually" note — the linter never fails closed on input it doesn't understand.
Composable rules. Each rule is a pure function with its own metadata in its own file; adding one is a one-file change plus a line in the registry.
Advisory, not a prover. Locksmith complements review; assumeLargeTables defaults to true so it errs toward flagging.

Roadmap

Remote (Streamable HTTP) transport for hosted deployments.
Additional dialects (MySQL, SQLite).
Optional live DB introspection to suppress findings on known-small tables.
A GitHub Action wrapping the same engine to gate PRs.

License

MIT

Install

claude_desktop_config.json

{
  "mcpServers": {
    "locksmith": {
      "command": "npx",
      "args": [
        "-y",
        "locksmith-mcp@0.1.1"
      ]
    }
  }
}