[English](README.md) · [简体中文](README.zh-CN.md)

# BridgeMCP · Turn business data into an MCP service Claude can use

Register a SQLite file or a folder of CSVs and it instantly becomes four read-only tools that Claude Desktop / Claude Code can call directly.

Your database credentials never leave your server, and every query the AI runs is logged.

![Dashboard](docs/screenshot.png)

---

## What problem it solves

Your team is already using Claude, and the business side keeps asking "which channel had the highest average order value last month" — and every time, someone on the engineering side has to hand-write SQL and export a spreadsheet.
You'd love for the AI to just query it directly, but you can't hand over database credentials: one stray `DELETE`, or one full-table export, and you have an incident on your hands.
BridgeMCP sits in between as a gateway:

- **No connection details ever leave your server** — the database path and credentials live only on your own machine; Claude receives four restricted tools, not a database connection
- **Read-only, enforced three separate times** — SQL parsing, a read-only connection, and a SQLite authorizer callback; any single layer failing doesn't open the door to unauthorized access
- **Tables you don't want seen are invisible, not just hidden** — anything outside the table allow-list doesn't even show up in `list_tables`; querying it directly is rejected
- **Phone numbers and emails are masked automatically** — columns are flagged as sensitive by name at registration time and always come back masked; wrapping them in an alias or a function to dodge masking gets rejected outright
- **No single call can walk off with the whole database** — row count, byte size, and execution time are all capped, so even a full cross-join gets truncated instead of taking down the database
- **Every AI query is on the record** — each call is logged to the dashboard with the exact SQL, row count, and duration; rejected calls are highlighted red with the reason spelled out

### The security boundary (this is the entire point of the project)

Every row below is something you can trigger live from the dashboard's "Tool console":

| What the AI tries | Result |
| --- | --- |
| `UPDATE orders SET status='delivered'` | Rejected · only read-only queries are allowed; statement starts with UPDATE |
| `SELECT 1; DROP TABLE customers` | Rejected · only a single statement is allowed; multiple statements detected |
| `SELECT * FROM staff_salaries` | Rejected · table staff_salaries is not in the allow-list |
| `SELECT name, phone FROM customers` | Allowed · `phone` comes back as `176****8944` |
| `SELECT phone AS p FROM customers` | Rejected · reads a masked column without emitting it under its original name |
| `SELECT substr(phone,1,7) FROM customers` | Rejected · same reason — functions and expressions can't dodge masking either |
| `search(customers, keyword="176", columns="phone")` | Rejected · masked columns can't be used as a search condition, or you could reverse-lookup them |
| `SELECT name, sql FROM sqlite_master` | Rejected · sqlite_master is not in the allow-list |
| `WITH RECURSIVE n(x) AS (…) SELECT * FROM n` | Rejected · statement contains a forbidden operation: recursive CTE |
| A 46 × 85-row cross join | Allowed, but truncated · only the first 200 rows come back, flagged as truncated |
| A query that never finishes | Aborted · exceeds the 3000 ms execution limit, with a "narrow your scope" suggestion attached |

The three layers of defense split the work like this:

1. **Parse layer** — strips comments and string literals, then checks keywords and statement count. `SELECT '-- drop table'` isn't falsely flagged, and `SELECT 1 /* */ ; DROP TABLE t` can't hide either.
2. **Connection layer** — the SQLite file is opened with a `file:...?mode=ro` read-only URI; writes fail at the driver level.
3. **Authorization layer** — SQLite calls back on every single action as it compiles a statement; only SELECT and READ are allowed, and READ is checked column-by-column against the allow-list. ATTACH, PRAGMA, CREATE, and `load_extension` are all denied. **This layer doesn't depend on our regex being airtight.**

The `pragma_table_list` example above is the proof: it slips past the keyword matcher (`pragma_table_list` isn't `pragma`), but the authorization layer still blocks it — as an "unlisted table," not a banned keyword.

### Call auditing

Every call — including rejected ones — is written to the database: tool name, full arguments, status, rejection reason, row count, byte size, duration, and whether it came from MCP or HTTP.
Rejected rows show up highlighted red on the dashboard, with the reason written directly in the row — no hovering required. This is the part meant for your client to see: exactly what the AI queried, at a glance.

When you hand data to an AI, the client's real concern is never "can it query this" — it's "will I be able to see what it queried." The audit detail lays out the full SQL for every call, which tables and columns it touched, how many rows came back, and how long it took. Rejected requests spell out the reason too — not a vague "auditing enabled" badge, but something you can actually hand to a client and have them read every line themselves.

![Call audit detail](docs/audit.png)

---

## Quick start

```bash
git clone https://github.com/LuciferLiu/bridgemcp.git
cd bridgemcp

python -m venv .venv && source .venv/bin/activate
pip install -r requirements.txt

cp .env.example .env          # adjust row/byte/timeout caps if you want
python seed_demo.py           # generates a fictional e-commerce DB + an audit log
uvicorn app.main:app --port 8050
```

Open http://localhost:8050

The demo data is a fictional e-commerce business database (customers / orders / order items, including phone numbers and emails), plus a `staff_salaries` table that's deliberately kept hidden from the AI to demonstrate the allow-list.
Everything runs fully offline — no API key required.

### Connecting to Claude

The "Connect to Claude" section on the dashboard generates a config with your absolute path already filled in — just copy it. The example below uses `/path/to/bridgemcp`; swap in your actual local path, or better, use whatever the dashboard shows you:

```json
{
  "mcpServers": {
    "bridgemcp": {
      "command": "/path/to/bridgemcp/.venv/bin/python",
      "args": ["-m", "app.mcp_server"],
      "cwd": "/path/to/bridgemcp"
    }
  }
}
```

Save it to `~/Library/Application Support/Claude/claude_desktop_config.json` and restart Claude Desktop.
For Claude Code, one command does it:

```bash
claude mcp add bridgemcp -- /path/to/bridgemcp/.venv/bin/python -m app.mcp_server
```

From there, just ask "which channel had the highest average order value last month" in the chat. Claude will walk through
`list_tables` → `describe_table` → `query` on its own, and every step shows up in the dashboard's audit log.

### Registering your own data source

```bash
curl -X POST http://localhost:8050/api/sources \
  -H "Content-Type: application/json" \
  -d '{
    "name": "erp",
    "kind": "sqlite",
    "path": "/data/erp.db",
    "description": "ERP read-only replica"
  }'
```

Registration auto-detects the schema and flags columns like `phone` / `email` / `id_card` as masked based on their name.
From there, use the dashboard to flip each table visible or hidden. For CSVs, set `kind` to `csv` and point `path` at a directory —
each `.csv` file inside becomes one table.

### Docker

```bash
cp .env.example .env
docker compose up -d
```

In `docker-compose.yml`, business data is mounted `:ro` — the container never gets write access either.

---

## Configuration

Everything is controlled via environment variables; see `.env.example`:

| Variable | Default | Description |
| --- | --- | --- |
| `DATABASE_URL` | `sqlite:///./bridgemcp.db` | Metadata DB (source registry + audit log); relative paths resolve against the project root |
| `MAX_ROWS` | `200` | Max rows returned per query — even if the AI passes a larger `limit`, it's clamped to this |
| `MAX_RESULT_BYTES` | `65536` | Byte cap per result; anything past it is dropped and flagged `truncated` |
| `QUERY_TIMEOUT_MS` | `3000` | Max execution time for a single query; aborted on timeout |
| `MASK_COLUMN_PATTERNS` | `phone,mobile,tel,email,…` | At registration, any column name matching one of these fragments is auto-flagged as masked |
| `PORT` | `8050` | Port for the dashboard and HTTP debug API |

The three caps are read at request time — change them and restart, no need to re-register any data source.

---

## Architecture

```
app/
├── main.py           FastAPI routes: dashboard API + HTTP debug endpoint
├── mcp_server.py     MCP server entrypoint (stdio), python -m app.mcp_server
├── tools.py          The four tool implementations + per-call audit logging
├── guard.py          The security boundary: SQL parsing, read-only connection, authorizer, caps, masking
├── reason_i18n.py    Bilingual templates for rejection reasons, shared by audit logging and the dashboard
├── introspect.py     Data source introspection (SQLite / CSV); CSVs load into an in-memory DB
├── models.py         SQLAlchemy models
├── schemas.py        Pydantic request/response validation
├── config.py         Environment variables
├── db.py             Engine & session (with PRAGMA foreign_keys=ON)
└── static/
    └── index.html    The dashboard (vanilla JS, no build step, ships with an EN/中文 toggle)
```

Three trade-offs were made deliberately:

**Security doesn't hinge on "parsing SQL correctly."** Any regex-based read-only check can be beaten by a sufficiently creative query.
So the real backstop is SQLite's own authorizer callback — it approves or denies each action at compile time, and it doesn't care whether our regex missed something.
The cost is that this mechanism is tied to SQLite; porting to Postgres would mean swapping it for a read-only role plus the equivalent of `SET TRANSACTION READ ONLY`.
What you get in return: even if the parse layer is bypassed, unauthorized access still can't happen.

**Masked columns wrapped in an alias or function are rejected outright, rather than best-effort masked.** Masking can only match by output column name — `SELECT substr(phone,1,7)` produces an output name that doesn't match, and "best-effort" masking that can be dodged is functionally no masking at all.
So the model is forced to rewrite the query instead — and the rejection message already tells it how. A handful of legitimate queries get caught in the net, but masking isn't something that gets to be probabilistic.

**CSV sources are loaded into an in-memory SQLite database on every query.** A separate pandas-style filtering path for CSVs was an option, but that would mean writing the security logic twice — and the second copy is exactly where a hole would end up. Reusing the same `guard` matters more.
The cost is that this doesn't scale to CSVs in the hundreds of megabytes — at that point, convert to SQLite first and register that instead.

---

## MCP tools

| Tool | Arguments | Description |
| --- | --- | --- |
| `list_tables` | `source?` | Lists visible tables, with row counts, column names, and masked columns |
| `describe_table` | `table`, `source?` | Column structure, types, masking flags, plus 3 masked sample rows |
| `query` | `sql`, `limit?`, `source?` | Executes a single read-only SELECT / WITH statement |
| `search` | `table`, `keyword`, `columns?`, `limit?`, `source?` | Fuzzy keyword match across text columns; masked columns are excluded from search |

`source` can be omitted when only one data source is registered; with multiple sources it must be given explicitly,
otherwise you'll get a rejection listing the available data source names.

---

## HTTP API

Interactive docs are available at `/docs` once the server is running.

| Method | Path | Description |
| --- | --- | --- |
| `GET` | `/api/stats` | Dashboard summary: source count, visible table count, 24h calls, 24h rejections |
| `GET` | `/api/limits` | Currently effective row / byte / timeout caps |
| `GET` | `/api/sources` | List of data sources, including each table's schema and exposure policy |
| `POST` | `/api/sources` | Register a data source (schema is introspected immediately; a failed introspection isn't persisted) |
| `POST` | `/api/sources/{id}/refresh` | Re-introspect the schema, preserving the existing allow-list and masking config |
| `DELETE` | `/api/sources/{id}` | Delete a data source and its table metadata |
| `PATCH` | `/api/tables/{id}` | Update a table's `is_allowed` / `masked_columns` |
| `GET` | `/api/calls` | Call audit log |
| `GET` | `/api/mcp-config` | Generates a Claude Desktop config with the absolute path already filled in |
| `POST` | `/api/tools/{name}` | Try a tool from the browser; runs through the exact same execution and audit path as MCP |

Example debug call:

```bash
curl -X POST http://localhost:8050/api/tools/query \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT channel, ROUND(SUM(amount),2) AS gmv FROM orders GROUP BY channel ORDER BY gmv DESC"}'
```

`POST /api/tools/{name}` returns 200 even for rejected calls, with `{"error": "...", "rejected": true}` in the body.
That's deliberate: "rejected by security policy" is a normal business outcome, not an HTTP-layer error — and it needs to land in the audit table exactly like a successful call does. Even an unknown tool name gets logged; "someone is probing with the wrong tool name" is itself worth keeping a record of.

---

## Notes

**This is a gateway, not a database proxy.** It only does table-level allow-listing and column-level masking — there's no row-level permission model (e.g. "sales reps can only see their own customers"). If you need row-level isolation, build a view in your business database and register only the view.

**Point this at a read-only replica or a read-only account.** BridgeMCP opens the file read-only on its own, but the first layer of defense in depth is always "never grant write access in the first place." The metadata database (registry + audit log) and the business database are two separate databases and never get written to interchangeably.

**Auditing is written synchronously.** Every tool call costs one extra write, in exchange for the guarantee that "the log always exists alongside the result." If call volume outgrows what SQLite can handle, swap `DATABASE_URL` for Postgres, or switch auditing to an async batched write.

**Rejection messages shown to the model are deliberately verbose.** Every rejection tells the model exactly how to rewrite the query — otherwise it just keeps retrying with random variations, generating a dozen useless audit entries in the process.

## License

MIT