MCP SQLite Server Runbook

MCP SQLite Server Runbook

Read-only MCP server exposing the prod SQLite DB to AI agents over Tailscale.

What this is

A Model Context Protocol server that lets
MCP-capable agents (Claude Code, Cursor, opencode, Claude Desktop via
mcp-remote) run read-only SQL queries against the production database
without direct DB access.

Agents get read_query, list_tables, describe_table tools. Write tools
(write_query, create_table, drop_table) exist in the tool surface but
fail at runtime: the DB is opened with readonly: true.

Architecture

MCP client (Claude Code, Cursor, etc.)
    │  HTTPS db.usebrioso.com/mcp (Tailscale, autogroup:member) +
    │  Authorization: Bearer <MCP_SQLITE_API_KEY>
    ▼
Caddy (100.105.198.36:443)  — checks the bearer header, 401s if missing/wrong
    │  reverse_proxy, Host normalized to localhost
    ▼
brioso-mcp-sqlite.service  (node22 + better-sqlite3, localhost:4984)
    │  readonly
    ▼
/opt/brioso/shared/data/local.db  (prod SQLite, WAL mode)

Three independent layers, in order:

1. Tailscale ACL (out-of-repo) — only tailnet clients can reach Caddy’s
   shared IP:port at all (autogroup:member — see §Tailscale ACL setup).
2. Caddy bearer-token gate — db.usebrioso.com/mcp requires
   Authorization: Bearer <MCP_SQLITE_API_KEY> (see deploy/caddy/Caddyfile
   and deploy/README.md’s “Custom subdomains” section); this server has no
   auth of its own, so this is the actual access-control boundary for it.
3. SQLite VFS — better-sqlite3 opens with { readonly: true, fileMustExist: true }. All write attempts fail with attempt to write a readonly database.

Why this fork and commit

Sebasteuo/sqlite-mcp-server
is a fork of etugrand/sqlite-mcp-server that adds Streamable HTTP transport
(the --http flag).

HEAD of the fork switched from native better-sqlite3 to sql.js (pure
JS/WASM), which loads the entire DB into memory and bypasses the native VFS:
readonly: true does not apply. We pin commit d264a86 (2026-03-07), the
last commit using native better-sqlite3 before the WASM switch.

To verify the pin rationale hasn’t drifted, check the fork’s recent commits:

gh api repos/Sebasteuo/sqlite-mcp-server/commits --jq '.[0:3] | .[] | .sha[:7] + " " + .commit.message'

The read-only patch

The upstream constructor opens writable:

this.db = new Database(path);

The install script patches src/index.ts to:

this.db = new Database(path, { readonly: true, fileMustExist: true });

This is applied via sed after checkout (see
deploy/scripts/install-mcp-sqlite.sh). If the install fails with
"expected 'new Database(path)' not found", upstream changed the
constructor: re-review before updating the pin.

The server has no --readonly flag and better-sqlite3 does not honor
file:...?mode=ro URIs, so a source patch is the only reliable enforcement
point. The alternative (wrapping every tool handler) is more code and more
surface area to get wrong.

WAL mode precondition

The prod DB must already be in WAL mode before the MCP server starts.
readonly: true + WAL works because SQLite reads existing -wal/-shm
files without writing. The constructor runs PRAGMA journal_mode = WAL: on
an already-WAL readonly DB this is a no-op (returns wal, does not throw).

Prod DB is already WAL (Drizzle Gateway requires it). If the DB is ever
rebuilt from a non-WAL backup, switch it to WAL before starting the MCP
service:

sudo -u web-services sqlite3 /opt/brioso/shared/data/local.db 'PRAGMA journal_mode=WAL;'

PII hygiene

The systemd unit sets StandardOutput=null. MCP query and response bodies
contain row data (potentially PII): suppressing stdout keeps them out of
journald. Startup messages and errors go to stderr, which still reaches the
journal (StandardError=journal).

Operations

Install / update

sudo /opt/brioso/repo/deploy/scripts/install-mcp-sqlite.sh
sudo systemctl restart brioso-mcp-sqlite

The installer is idempotent: if the pinned commit is already installed and
build/index.js exists, it skips. To force a rebuild:

sudo rm /opt/brioso/shared/.mcp-sqlite-version
sudo /opt/brioso/repo/deploy/scripts/install-mcp-sqlite.sh
sudo systemctl restart brioso-mcp-sqlite

To pin a new commit: edit VERSION in install-mcp-sqlite.sh, delete the
marker, re-run. Always review the constructor line in the new commit
before updating the pin (the sed patch targets new Database(path)
exactly).

Verify read-only enforcement

# 1. Service is running
systemctl status brioso-mcp-sqlite

# 2. Health endpoint responds
curl -s http://localhost:4984/health

# 3. Read works
sudo -u web-services /usr/local/bin/node22 -e '
  const Database = require("/opt/brioso/mcp-sqlite/node_modules/better-sqlite3");
  const db = new Database("/opt/brioso/shared/data/local.db", { readonly: true, fileMustExist: true });
  console.log(db.prepare("SELECT count(*) as n FROM sqlite_master").get());
'

# 4. Write fails (the security guarantee)
sudo -u web-services /usr/local/bin/node22 -e '
  const Database = require("/opt/brioso/mcp-sqlite/node_modules/better-sqlite3");
  const db = new Database("/opt/brioso/shared/data/local.db", { readonly: true, fileMustExist: true });
  try { db.exec("CREATE TABLE IF NOT EXISTS _ro_test (x)"); console.log("FAIL: write succeeded"); }
  catch (e) { console.log("OK: " + e.message); }
'
# Expected: OK: attempt to write a readonly database

If step 4 ever prints FAIL: write succeeded, treat it as a security
incident: the readonly patch is missing or ineffective. Re-run the installer.

Custom subdomain (Caddy)

MCP SQLite is reached in prod at https://db.usebrioso.com/mcp via the shared
Caddy instance — see deploy/README.md’s “Custom subdomains (Caddy)” section
for the full DNS/Caddy/ACL setup (shared with Drizzle Gateway on the same
domain’s / path, plus Arize Phoenix and apps/admin on their own
subdomains). MCP_SQLITE_API_KEY itself lives in Infisical under
/infra/caddy and syncs into /etc/caddy/caddy-secrets.env automatically on
every deploy (restarting brioso-caddy only if it changed) — rotate it there,
not by hand on the VPS. Verify:

curl -s https://db.usebrioso.com/mcp -H "Authorization: Bearer <MCP_SQLITE_API_KEY>"

Tailscale ACL setup (one-time, out-of-repo)

Shared with Drizzle Gateway, Arize Phoenix, and apps/admin — all four sit
behind the same Caddy instance on one IP:port (100.105.198.36:443), and
Tailscale ACLs are IP:port-scoped, not per-hostname or per-path, so they
can’t be split per subdomain. The ACL is autogroup:member:

{
  "action": "accept",
  "src": ["autogroup:member"],
  "dst": ["tag:server:443"]
}

This tailnet is now just the two engineer accounts, which is why
autogroup:member (previously avoided here — see history below) is safe:
unlike the old per-service tailscale serve setup, reaching
db.usebrioso.com:443 no longer implies prod DB query access by itself. The
actual access-control boundary for MCP SQLite is now the Caddy-layer bearer
token (MCP_SQLITE_API_KEY, see §Architecture and deploy/README.md) — the
ACL only gates who can reach Caddy at all. (Historical note: an earlier
user-scoped-only ACL model was used back when :8002 was reached directly
with no app-level auth in front of it; that constraint no longer applies now
that the bearer-token gate exists.)

Client configuration

Claude Code / Cursor / opencode

These clients support remote MCP URLs directly. Add to the MCP config:

{
  "mcpServers": {
    "brioso-sqlite": {
      "url": "https://db.usebrioso.com/mcp",
      "headers": {
        "Authorization": "Bearer <MCP_SQLITE_API_KEY>"
      }
    }
  }
}

Claude Desktop (requires mcp-remote bridge)

Claude Desktop does not support remote MCP URLs natively. Use
mcp-remote:

{
  "mcpServers": {
    "brioso-sqlite": {
      "command": "npx",
      "args": [
        "mcp-remote",
        "https://db.usebrioso.com/mcp",
        "--header",
        "Authorization: Bearer <MCP_SQLITE_API_KEY>"
      ]
    }
  }
}

Troubleshooting