LockSmith

LockSmith is a deploy gate for PostgreSQL schema migrations. It reads a migration, flags the statements that would block reads or writes, proves each lock by replaying the migration inside a real embedded Postgres, estimates how long production would be blocked, and hands the fix to IBM Bob. I built it solo for the IBM Bob 2.0 Hackathon on lablab.ai and submitted it on 27 September 2026.

Problem

Migrations go through the same pull-request review as application code, but their risk is invisible in a diff. CREATE INDEX without CONCURRENTLY holds a ShareLock that blocks writes for the whole build. ALTER COLUMN … TYPE takes an AccessExclusiveLock and rewrites the table. A column rename is instant, yet it breaks every running app instance still using the old name. Any heavy DDL without lock_timeout can queue behind one slow query and freeze everything behind it. Reviewers are expected to know all of this by heart. When they miss one, the first sign is a stalled deploy.

How it works

The pipeline has five steps.

1. Detect. Twelve deterministic rules (LS001–LS012) check the parsed SQL: non-concurrent indexes, NOT NULL adds, volatile defaults, type changes, validated foreign keys and checks, renames and drops still referenced by application code, missing lock_timeout, CONCURRENTLY inside a transaction, and un-batched backfills.
2. Prove. Every statement is replayed in PGlite, which is PostgreSQL compiled to WASM and run in-process. LockSmith reads pg_locks for the lock actually taken and compares relfilenode before and after to catch table rewrites.
3. Estimate. Declared production table sizes turn a lock into "about N seconds, about M writes blocked".
4. Rewrite. A custom IBM Bob mode, Migration Surgeon, plus a lock reference and a lock-audit skill, has Bob rewrite each flagged migration in its own subagent. Bob also updates the dependent app code, adding dual-writes for renames, and leaves unflagged files alone.
5. Re-verify. Bob's output goes back through the same engine, and the CLI exits non-zero while any critical finding remains.

The engine is TypeScript, the web report and analyser run on Next.js with Tailwind, and tests run on Vitest. LockSmith never connects to a real database.

Key decisions and trade-offs

• The model doesn't decide what's dangerous. The rules and the measured locks do, and Bob only does the judgement-heavy surgery. In the demo run, Bob withdrew its own column-drop step after noticing the app still wrote to that column. The gate would have caught that anyway.
• Measure where you can, and say so where you can't. CREATE INDEX CONCURRENTLY can't run inside the probe's transaction, so its lock is marked inferred in the UI rather than presented as measured.
• The durations are estimates. They come from declared row counts and fixed throughput constants, not from timing your hardware.
• Honest attribution. IBM Bob wrote most of the engine across six IDE tasks, including the rules, the probe, the mode and 53 of the tests, and I reviewed and re-ran each change. I wrote the spec, the fictional demo repository, the UI and the CLI output, using Claude Code as a coding assistant.

The demo repository is a fictional shop app with 8 pending migrations. As written, the gate fails with a risk score of 100 and 14 findings. After Bob's rewrite, it passes with 0 findings, and 6 dangerous migrations have become 13 ordered safe files.

What's next

The README roadmap lists a GitHub Action wrapper, Prisma, Rails and Flyway adapters, importing real pg_stat table sizes, and MySQL online-DDL rules. For now LockSmith covers PostgreSQL and plain SQL migration files only.

Schema migrations are reviewed as text, but they fail as locks. A one-line CREATE INDEX blocks every write for the whole build; ALTER COLUMN … TYPE takes an AccessExclusiveLock and rewrites the table. None of that shows up in a diff, and catching it means knowing lock semantics and doing table-size arithmetic in your head.

Twelve deterministic rules (LS001–LS012) decide what is risky. Every statement is replayed in PGlite, an in-process Postgres, so the lock is read from pg_locks rather than guessed. Declared table sizes turn each lock into a blocking-time estimate. IBM Bob, through a custom mode and skill shipped in the repo, rewrites flagged migrations into expand → backfill → contract steps, and the same gate re-checks Bob's output before CI lets the deploy through.

• On the fictional demo repository, the gate went from FAIL (risk 100, 14 findings) to PASS (risk 0) after IBM Bob's rewrite: 6 dangerous migrations became 13 ordered safe files.
• Verified on 27 Sep 2026: 61 of 61 tests passing, typecheck clean, production build succeeds.

• Put deterministic checks in front of the model. Fixed rules decide what is risky; the AI is used where judgement helps, and the same rules judge its work.
• Prove, then warn. Evidence from a real Postgres engine ends a review argument faster than a rule name does.
• Put the gate where the decision happens: in CI, before the deploy.