# NL-to-SQL — MCP + GraphRAG

Natural-language querying over a SQLite database (the [Chinook](https://github.com/lerocha/chinook-database) music-store
sample: artists, albums, tracks, genres, customers, invoices, employees, playlists — 11 tables, 11 FK relationships),
built around two independent guardrail layers, a schema-graph retrieval step, and an LLM-driven generation pipeline.

## Two ways to use this

This repo is actually two separate products sharing the same guardrail and retrieval code:

1. **MCP server** ([server/main.py](server/main.py)) — exposes `list_schemas`, `get_table_metadata`,
   `retrieve_relevant_schema`, and `execute_safe_query` as tools for any MCP client (Claude Desktop, Claude Code).
   In this mode, the connected LLM does its own reasoning about which tables/SQL to use — our code only provides
   schema discovery, GraphRAG-assisted retrieval, and guardrailed execution. No OpenAI calls happen in this path.

2. **Standalone pipeline** ([pipeline/pipeline.py](pipeline/pipeline.py)) — a fully self-contained 5-stage
   NL-to-SQL system (Plan → Retrieve → Generate → Validate → Execute) that uses the OpenAI API directly. This is
   what the web app and CLI script call; Claude Desktop is not involved at all in this path.

These don't talk to each other — pick whichever fits how you want to interact with the database.

## Architecture

```
                    ┌─────────────────────────────────────┐
                    │         Schema Graph (NetworkX)      │
                    │  tables + columns as nodes,          │
                    │  FKs as edges — built once,          │
                    │  persisted to GraphML                │
                    └───────────────┬───────────────────────┘
                                    │
              ┌─────────────────────┼─────────────────────┐
              │                     │                     │
   ┌──────────▼─────────┐  ┌────────▼────────┐  ┌─────────▼──────────┐
   │     MCP Server      │  │  Pipeline (CLI)  │  │   Web App (FastAPI) │
   │  4 tools, stdio      │  │  scripts/        │  │  web/app.py          │
   │  Claude Desktop/Code │  │  run_pipeline.py │  │  + static HTML/JS    │
   └──────────┬───────────┘  └────────┬─────────┘  └─────────┬────────────┘
              │                        └──────────┬───────────┘
              │                                    │
              │                          pipeline/pipeline.py
              │                    Plan → Retrieve → Generate → Validate → Execute
              │                                    │
              └──────────────────┬─────────────────┘
                                   │
                      ┌────────────▼────────────┐
                      │   Guardrail layer         │
                      │  sql_guard.py (AST allow-  │
                      │  list) + read-only conn +  │
                      │  timeout + row cap         │
                      └────────────┬────────────┘
                                   │
                            db/chinook.db (SQLite)
```

## Guardrails

Security is layered, not a single check:

- **AST-based SQL validation** ([server/sql_guard.py](server/sql_guard.py)) — allowlist, not keyword blocklist.
  Parses generated SQL with `sqlglot`; only accepts a single `SELECT`/`UNION`/`INTERSECT`/`EXCEPT` statement, every
  table reference must exist in the live schema (CTE aliases correctly excluded), `load_extension`/`pragma_*` calls
  blocked, and a `LIMIT` is always injected/clamped to 1000 rows by rewriting the AST — never trusting whatever the
  caller or LLM wrote.
- **Read-only connection** ([server/query_executor.py](server/query_executor.py)) — SQLite opened via
  `mode=ro` URI, an independent backstop at the driver level even if a write somehow passed AST validation.
- **Timeout** — a progress-handler wall-clock timeout aborts long-running scans instead of blocking.
- **Plan-stage scope filtering** ([pipeline/plan_stage.py](pipeline/plan_stage.py)) — an LLM call classifies
  whether a question is in-scope before any SQL generation is attempted. This is a quality/cost filter, **not** the
  security boundary — the AST validator is what actually stops unsafe SQL regardless of what Plan decides.

## Multi-database support

The web app isn't limited to the bundled Chinook demo. Switch to "Upload your own" and provide either:
- a **SQLite `.db` file** — used as-is, no conversion
- a **MySQL or Postgres SQL dump** (`.sql`) — transpiled to SQLite statement-by-statement via `sqlglot`
  ([web/db_import.py](web/db_import.py)) and imported into a fresh, isolated per-session database

Each upload gets its own session ([web/sessions.py](web/sessions.py)): its own SQLite file, its own schema graph,
expiring after 24h, never touching the shared demo DB or another session's data. **You must supply your own OpenAI
API key to query an uploaded database** — it's used only for that session's requests and never persisted to disk;
the bundled demo continues to use the server's own key from `.env`, unaffected.

Import is deliberately best-effort, not all-or-nothing: dump syntax with no SQLite equivalent (MySQL's inline
`KEY`/`INDEX` clauses, Postgres's `CREATE SEQUENCE`/`CREATE EXTENSION`, session-config `SET` statements, schema
qualifiers like `public.customers` which SQLite would otherwise misparse as a cross-database reference) is
detected and skipped, with a report of what was skipped — not an opaque failure over one unsupported statement.
Capped at 20MB per upload; larger/async imports are a v2 concern.

## Setup

```bash
python -m venv .venv
.venv/Scripts/pip install -r requirements.txt      # Windows
# .venv/bin/pip install -r requirements.txt         # macOS/Linux

cp .env.example .env      # then edit .env with your real OPENAI_API_KEY

python scripts/build_graph.py    # build graphrag/schema_graph.graphml from db/chinook.db
```

## Running it

**MCP server** (for Claude Desktop/Code — see `.mcp.json`, already configured for this project):
```bash
python server/main.py
```

**Web app**:
```bash
uvicorn web.app:app --reload
# open http://127.0.0.1:8000
```

**CLI**:
```bash
python scripts/run_pipeline.py "Which artist has the most albums?"
python scripts/query_graph.py "your question"     # GraphRAG retrieval only, no LLM
```

## Testing

```bash
# Free, deterministic (no API calls):
python tests/test_sql_guard.py              # 21 adversarial/legit SQL cases
python tests/test_query_executor.py         # timeout, read-only backstop, row cap
python tests/test_retrieval.py              # GraphRAG retrieval regression cases
python tests/test_pipeline_stages_offline.py

python tests/test_db_import.py              # SQLite/MySQL/Postgres import + transpilation

# Real API calls (needs OPENAI_API_KEY):
python tests/test_plan_stage.py
python tests/test_generate_stage.py
python eval/run_eval.py                     # full 40-question golden set
```

## Eval harness

[eval/run_eval.py](eval/run_eval.py) grades on **executed results**, not SQL text — two differently-written
queries can both be correct, so it compares the pipeline's output rows against a hand-verified reference query's
output (value-subset matching, tolerant of extra descriptive columns and float precision). 30 legitimate questions
(counts, sums, joins, self-joins, nullable-FK handling, literal lookups) plus 10 adversarial (out-of-scope,
prompt injection, destructive intent) — currently **40/40**.

The first real eval run caught two genuine bugs (not eval-harness artifacts): Plan being too conservative about a
self-referencing FK relationship it had no schema access to verify, and Generate grouping by a non-unique display
column (`Playlist.Name` — two different playlists share that name in Chinook) instead of the primary key, silently
merging distinct rows. Both are fixed in the current prompts.

Wired into [.github/workflows/eval.yml](.github/workflows/eval.yml): deterministic tests run first (fail fast,
free), then the eval harness (needs an `OPENAI_API_KEY` repo secret), with the report uploaded as a CI artifact.

## Deployment

```bash
docker build -t nl2sql-mcp-graphrag .
docker run -p 8000:8000 --env-file .env nl2sql-mcp-graphrag
```

[render.yaml](render.yaml) is a Render blueprint (Docker runtime, free tier) — set `OPENAI_API_KEY` in the Render
dashboard after connecting the repo, it's intentionally not committed. *Note: the Docker build hasn't been verified
in this environment (no Docker available) — test `docker build .` locally before deploying.*

## Known limitations / v2

- **No multi-tenancy** — no row-level access control; deferred deliberately to keep v1 scoped.
- **No schema-drift detection** — `scripts/build_graph.py` must be re-run manually after a schema change; no
  polling/webhook invalidation.
- **Lexical/fuzzy schema matching**, not embeddings — free and deterministic, but can't bridge true vocabulary gaps
  beyond the small synonym map in [graphrag/matching.py](graphrag/matching.py) (e.g. "revenue" → `Invoice.Total`
  is hardcoded, not learned). Swap-in point is `match_schema_nodes()` if eval ever shows this as a bottleneck.
- **Retrieval is high-recall, not high-precision** — an FK column like `Invoice.CustomerId` legitimately contains
  the word "customer", so simple questions can pull in more tables than strictly needed. Generate has so far proven
  robust to this noise (see Phase 4 testing), but it's a known tradeoff, not a solved problem.
- **Dump transpilation isn't guaranteed complete** — best-effort, with a skip report for unsupported syntax (stored
  procedures, triggers, engine-specific types beyond what's already handled). Complex enterprise dumps may import
  partially. No background/async import, so upload is capped at 20MB and blocks the request until done.
- **No accounts** — sessions are anonymous and expire after 24h; there's no way to return to an uploaded database
  later or share it across devices.

## Project structure

```
db/            chinook.db (SQLite sample data)
server/        MCP server + AST guardrails (sql_guard.py, query_executor.py)
graphrag/      NetworkX schema graph, fuzzy matching, retrieval
pipeline/      5-stage LLM pipeline (plan/retrieve/generate/validate/execute)
web/           FastAPI app + static frontend + multi-DB upload/import/sessions
eval/          golden question set, grader, eval runner
scripts/       CLI entrypoints (build_graph, query_graph, run_pipeline, inspect_schema)
tests/         deterministic test suites
```