locksmith-mcp

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.

🛑 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:

-- 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:

npx locksmith-mcp

Use with Claude Code

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

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

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

Run from source instead

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

npx @modelcontextprotocol/inspector npx -y locksmith-mcp

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

Development

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