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

```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.

[`pgsql-ast-parser`]: https://github.com/oguimbal/pgsql-ast-parser

## License

MIT