# chat_sql

A governed **natural-language → SQL** agent for Claude, delivered as an **MCP
server**. Ask a question in plain English; the agent selects the right database,
writes dialect-correct SQL, runs it through security-enforcing tools, and returns
results — across ClickHouse, Teradata, PostgreSQL/GaussDB, MySQL, SQL Server, DB2,
Oracle, and MongoDB.

Its defining feature is that **the LLM is treated as untrusted**. It is
structurally unable to read raw PII, to modify any table it did not create, or to
reach a database outside the sanctioned tools — regardless of how it is prompted.

## Why it's safe by construction

```
LLM (untrusted)
   │  MCP over stdio — tool calls only
   ▼
mcp_server.py   ── UNPRIVILEGED face ──────────────────
   • no credentials, cannot import a connector
   • forwards every DB call over a local UNIX socket
   • run_python: no creds, no network (sandboxed)
   │  UNIX socket (0600)
   ▼
dbgateway.py   ── PRIVILEGED broker ───────────────────
   • the only holder of credentials + connectors
   • statement guard → write-guard → PII masking
   • returns sanitized rows only
```

| Guarantee | How |
|-----------|-----|
| **No raw PII to the LLM** | Results are masked in the broker: a PII registry + column-name heuristics (default-deny) + a cell-level pattern backstop. Identifiers return as `sha256` hashes or `***`; **aggregates return raw**. |
| **No writes to tables it didn't create** | Writes/creates allowed only on `tmp_` scratch tables recorded in an ownership ledger; everything else is refused before touching the DB. |
| **No direct connector access** | The `connectors` package won't import without a secret token that lives only in the broker — not in the LLM-facing process, not in `run_python`. |

Full threat model and control matrix: [`docs/SECURITY.md`](docs/SECURITY.md).

## Quick start

```bash
git clone <your-fork> chat_sql && cd chat_sql
uv venv && uv pip install -r requirements.txt      # install the DB drivers you need

cp database_creds.example.json database_creds.json # add your databases
cp pii_policy.example.yaml pii_policy.yaml          # classify your PII columns
cp .env.example .env                                # set CHATSQL_PII_SALT etc.
```

Register the MCP server with your client (`.mcp.json` or `~/.claude/settings.json`):

```json
{
  "mcpServers": {
    "chat-sql": {
      "command": "uv",
      "args": ["--directory", "/path/to/chat_sql", "run", "python", "mcp_server.py"]
    }
  }
}
```

For airtight `run_python` isolation, install **bubblewrap** (`bwrap`) or run the
broker as a separate OS user — see `docs/SECURITY.md §6`.

## Configuring databases

Each entry in `database_creds.json` has a short code (the `db_key` you pass to
tools) and a `service` that selects the connector:

```json
{
  "pg_demo": {
    "service": "postgres", "alias": "demo analytics",
    "host": "localhost", "port": 5432,
    "user": "…", "password": "…", "database": "analytics", "schema": "public"
  }
}
```

Supported `service` values: `clickhouse`, `teradata`, `mysql`, `postgres`
(`psql`/`gaussdb`), `mssql`, `db2`, `oracle`, `mongo`.

## Classifying PII

`pii_policy.yaml` pins which columns are masked and how (`hash` | `redact`). The
broker also applies name-heuristics (default-deny on identifier-looking columns)
and a raw-pattern backstop, so unlisted PII is still caught — but pin known PII
explicitly. See `pii_policy.example.yaml`.

## Tools

**Database** — `run_query`, `run_query_file`, `export_to_csv`, `load_file`,
`drop_table`, `list_databases`, `explain_query`, `table_info`, `search_schema`,
`long_running_queries`, `kill_query`.
**RAG examples** — `find_similar_queries`, `save_query_example`,
`delete_query_example` (needs a pgvector DB; set `CHATSQL_RAG_DBKEY`).
**Local compute** — `run_python` (sandboxed), `notify`.
**Optional groups** (off by default; enable with `CHATSQL_*_ENABLED=1`) —
WhatsApp (`whatsapp_*`, `transcribe_audio`), email (`send_email`),
Jupyter remote-exec (`jupyter_*`), wiki/memory (`wiki_*`).

## Use it as a Claude skill

`skills/chat-sql/SKILL.md` packages the workflow so you can drop it into a
`.claude/skills/` directory and invoke it by name. See that file for details.

## Running the security tests

```bash
uv pip install sqlglot pyyaml pytest
python -m pytest tests/ -v
```

The suite proves every control (PII masking, write-guard, connector isolation,
statement guard) with no live database required.

## License

MIT — see [`LICENSE`](LICENSE).