# sqlite-guard-mcp

**Let an AI agent work on your SQLite database without trusting it.**
An MCP server with four tools — `schema`, `query`, `execute`, `audit_log` — and
three guarantees:

1. **Reads can't write.** `query` runs on a separate connection opened
   `SQLITE_OPEN_READONLY` at the C level. A disguised write
   (`/* just checking */ UPDATE …`) isn't caught by a regex — it's refused by
   SQLite itself. Enforcement by construction, not by inspection.
2. **Writes are dry-run first.** `execute` runs the statement inside a
   transaction that is *always rolled back*, and reports what would have
   happened (`changes`, `lastInsertRowid`). Committing requires re-calling with
   `confirm: true` — the agent must state its intent twice, and its operator
   sees the intended effect in between.
3. **Committed writes leave a trail you can walk back.** Before any commit, the
   DB file is snapshotted (`VACUUM INTO` — transactionally consistent even
   under WAL with active readers). The write and its append-only audit row
   commit in the *same transaction*: you can't end up with a change that has
   no audit entry, or an audit entry for a change that didn't happen.

## Why this exists

I run a personal-finance dashboard whose UI is deliberately **read-only** —
every number in it is edited by AI agents over SQL. That architecture is
wonderful (no forms, no write endpoints, agents keep the books) right up until
an agent runs a plausible-looking `UPDATE` with a wrong `WHERE` clause.

The insight from operating that system: what agent SQL needs isn't a smarter
model, it's the same thing human ops needed for decades — **read/write
separation, a plan/apply step, backups, and an audit log**. This server
packages those four things behind MCP so any agent (Claude Code, or anything
else that speaks MCP) gets them for free on any SQLite file.

## Quick start

```bash
npm install
npm run demo        # full guardrail walkthrough on a temp DB — 10 seconds, no setup
npm test            # 10 tests: rollback semantics, backup consistency, audit atomicity
```

Wire it into Claude Code:

```bash
claude mcp add sqlite-guard \
  -e SQLITE_GUARD_DB=/path/to/app.db \
  -- npx tsx src/server.ts
```

Or inspect it interactively: `npx @modelcontextprotocol/inspector npx tsx src/server.ts`
(with `SQLITE_GUARD_DB` set).

## The tools

| Tool | Contract |
|---|---|
| `schema` | Every table with columns, types, PKs, row counts — the agent's map. |
| `query` | Read-only SQL with `?` params. Row-capped (`SQLITE_GUARD_MAX_ROWS`, default 200) so a `SELECT *` on a big table can't blow out the agent's context window; the real count is always reported. |
| `execute` | Single write statement with `?` params. Dry-run by default → `confirm: true` to commit (backup + audit). Single-statement only — which also kills `; DROP TABLE` piggybacking. |
| `audit_log` | The append-only trail of every committed write, newest first. |

## Design notes

- **The dry-run is real execution**, not `EXPLAIN`-based estimation: the
  statement genuinely runs (triggers, constraints, and all) and is rolled
  back. What you see is what a commit would do — including the constraint
  error you'd hit.
- **Restore is one file copy.** Backups are plain SQLite files named
  `<db>-backup-<timestamp>`; recovery from a bad committed write is
  `cp` + restart, and the audit row records exactly which snapshot predates
  which write.
- **`BEGIN`/`COMMIT` from the agent are refused** — transaction lifecycle
  belongs to the guard. Otherwise a stray `BEGIN` would let a later statement
  commit the "rolled-back" dry run.
- **The audit table is readable through `query` on purpose.** Transparency
  beats secrecy here: the agent can review its own history, and the operator
  can ask the agent to summarize what it changed and when.
- **Statement classification (`classify.ts`) is labeling, not security.** It
  tags audit rows and error messages; the security boundaries are the
  connection flag and the transaction protocol. Anything a regex decides, a
  determined input can undecide.

## Limitations (honest ones)

- Per-table allow/deny lists aren't implemented (SQLite's authorizer API isn't
  exposed by better-sqlite3); the boundary is per-database. Point the server
  at a database you intend agents to manage.
- `VACUUM INTO` needs SQLite ≥ 3.27 (2019); older builds fall back to a file
  copy, which is only safe when quiescent.
- One MCP server = one database file. Run several instances for several files.

## Stack

TypeScript · `@modelcontextprotocol/sdk` (stdio transport) · better-sqlite3 ·
zod · vitest.

## License

MIT © Brandon Ta