# TextToSQL MCP Server

A read-only, schema-aware **TextToSQL [MCP](https://modelcontextprotocol.io) server**. It fronts a SQL
warehouse (PostgreSQL or SQLite) and exposes six tools any MCP-capable host or
agent can drive: refine a question, browse curated schema knowledge, validate,
explain, and run SELECT statements — with independent read-only safety layers
in between. Schema knowledge lives as **skills-as-markdown**: generated drafts
you curate once, then freeze. The server ships *not yet configured*; a bundled
SQLite demo database lets you run the full lifecycle end-to-end in about five
minutes.

## 5-minute demo quickstart

The repo bundles the [SQL Murder Mystery](https://github.com/NUKnightLab/sql-mysteries)
dataset (`demo/murder_mystery.db`, 9 tables, ~57k rows — MIT licensed, see
[THIRD_PARTY_LICENSES.md](THIRD_PARTY_LICENSES.md)).

```bash
uv sync
cp .env.demo .env                       # SQLite DSN, INTROSPECTION=true
ollama pull gpt-oss:120b                # or set LLM_FALLBACK_API_KEY (see LLM configuration)
uv run python scripts/verify_role.py    # confirms the read-only guard is active
uv run python -m texttosql_mcp.run_http --port 8765
```

In a second terminal:

```bash
curl -s http://localhost:8765/health | python -m json.tool
curl -s -X POST http://localhost:8765/admin/regenerate_skill | python -m json.tool
#   -> 9 tables discovered, 6 FKs; skill drafts under skills/; baseline fixtures written
uv run --extra dev pytest tests/test_fixtures.py -v
#   -> EXPECTED: several fixtures FAIL against the raw drafts (integer dates,
#      lowercase enums, the ssn join). That failure is the point — the drafts
#      contain no semantic knowledge yet.
```

Now author the curated sections (see *The three phases* below — fill the
`<TODO>` blocks in `skills/` using `POST /admin/probe_table` samples), then:

```bash
uv run --extra dev pytest tests/test_fixtures.py -v   # all demo fixtures pass
# flip INTROSPECTION=false in .env, restart the server
uv run --extra dev pytest tests/test_fixtures.py -v   # still green; admin endpoints now 403
# connect any MCP-capable client/agent to http://localhost:8765/mcp
```

## Architecture

An MCP host (any agent framework, an MCP inspector, or a plain script using the
MCP SDK) connects over Streamable HTTP and drives the tool set; the server owns
schema knowledge (skills), safety, and DB access. The only server-side LLM call
is `refine_question` — everything else is deterministic.

```
┌─────────────────────────────────────────────────────────┐
│ MCP host / agent (any MCP-capable client)               │
└──────────────────────────┬──────────────────────────────┘
                           │ Streamable HTTP  POST /mcp/
┌──────────────────────────▼──────────────────────────────┐
│ TextToSQL MCP server (FastAPI + FastMCP)                 │
│                                                          │
│  refine_question ──► LLM (translate + vocabulary map)    │
│  list_tables / get_table_schema ──► skills/*.md (cached) │
│  validate_query ──► sqlglot AST guard                    │
│  explain_query ──► EXPLAIN guard (cost / plan hints)     │
│  run_query ──► validate + explain + execute (row-capped) │
│  introspect_schema ──► Phase 1 only (INTROSPECTION=true) │
│                                                          │
│  read-only connection (RO role / PRAGMA query_only)      │
└──────────────────────────┬──────────────────────────────┘
                           │ async SQLAlchemy
                ┌──────────▼──────────┐
                │ PostgreSQL / SQLite │
                └─────────────────────┘
```

## The three phases

**Phase 1 — bootstrap skills (INTROSPECTION=true).**
`POST /admin/regenerate_skill` sweeps the schema (tables, columns, PKs, FKs,
indexes, low-cardinality enums, sample rows) and renders draft skill files:
`skills/sql_schema.skill.md` (master) plus `skills/tables/<table>.md`. Drafts
auto-fill everything mechanical and leave `<TODO>` markers in the semantic
sections. A baseline fixture file (row counts per table) is also written.

**Phase 2 — curate and iterate.**
Fill the curated sections — the master's OVERVIEW, VOCABULARY,
CROSS_TABLE_EXAMPLES and PITFALLS, and each table's PURPOSE, EXAMPLES,
PITFALLS — using `POST /admin/probe_table?name=<table>` to inspect real rows.
Iterate with the NL→SQL fixture harness (`pytest tests/test_fixtures.py`):
each fixture asks a question, lets the LLM draft SQL against your skills, runs
it, and asserts tables used, clauses, values, and row counts. Re-runs of
Phase 1 preserve curated sections (`merge_preserving_curated`).

**Phase 3 — freeze.**
Set `INTROSPECTION=false` and restart. The introspection tool and admin
endpoints disappear (`tools/list` shows exactly six tools; admin returns 403).
Skill files are the frozen contract; edits to them hot-reload on the next call
(mtime cache), no restart needed.

## Pointing at your own database

Configuration is **env-only** — no DSN, schema name, or threshold lives in
source. Copy `.env.example` to `.env` and set:

| Variable | Meaning |
|---|---|
| `DATABASE_URL` | Async SQLAlchemy DSN (required) |
| `SCHEMA_NAME` | Schema to introspect/query (required; SQLite: `main`) |
| `INTROSPECTION` | `true` during Phases 1–2, `false` in production |
| `INTROSPECTION_TABLE_PREFIXES` | CSV of table-name prefixes to limit scope (empty = all) |
| `ALLOWED_QUERY_SCHEMAS` | Extra schemas SELECTs may reference (empty = `SCHEMA_NAME` only) |
| `SAMPLE_ROW_LIMIT` | Sample rows per table in drafts |
| `SKILL_DIR` / `FIXTURE_DIR` | Where skills / fixtures live |
| `EXPLAIN_WARN_COST` / `EXPLAIN_HARD_COST` | Cost guard thresholds (PostgreSQL only) |
| `DEV_DB_VERSION` | Bump to invalidate the harness result cache |
| `MCP_HTTP_PORT` | HTTP port (default 8765) |

**PostgreSQL** — use a read-only role:

```sql
CREATE ROLE warehouse_read LOGIN PASSWORD '...';
GRANT USAGE ON SCHEMA analytics TO warehouse_read;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO warehouse_read;
ALTER ROLE warehouse_read SET default_transaction_read_only = on;
ALTER ROLE warehouse_read SET statement_timeout = '30s';
```

```ini
DATABASE_URL=postgresql+psycopg://warehouse_read:...@host:5432/db
SCHEMA_NAME=analytics
```

**SQLite** — point at any `.db` file; every connection gets
`PRAGMA query_only=ON`, so writes are refused at the connection level:

```ini
DATABASE_URL=sqlite+aiosqlite:///./path/to/your.db
SCHEMA_NAME=main
```

**Bring your own CSVs** — build a demo DB from flat files (stdlib only):

```bash
python scripts/csv_to_sqlite.py my.db --csv orders=./orders.csv --csv customers=./customers.csv
```

PKs/FKs are not inferred; add them by hand if you want the JOINS section of the
master skill populated (convention-only joins work too — the renderer handles
the no-FK case).

Run `uv run python scripts/verify_role.py` after any connection change — it
fails loudly if the connection can write.

## LLM configuration

The LLM drives the `refine_question` tool and the pytest harness; the other
five tools never call one. Any OpenAI-compatible endpoint works.

```ini
# Primary (default: local Ollama)
LLM_BASE_URL=http://localhost:11434/v1
LLM_MODEL=gpt-oss:120b            # or gpt-oss:20b on smaller machines
LLM_API_KEY=ignored

# Automatic fallback when the primary is unreachable (probe once per process).
# Enabled by setting the key; default target is OpenRouter's automatic router.
LLM_FALLBACK_BASE_URL=https://openrouter.ai/api/v1
LLM_FALLBACK_MODEL=openrouter/auto
LLM_FALLBACK_API_KEY=
LLM_PROBE_TIMEOUT=2
```

On first LLM use, the server probes `GET {LLM_BASE_URL}/models`; if unreachable
and a fallback key is set, all calls transparently use the fallback (a warning
is logged once). `GET /health` surfaces the resolved endpoint and
`fallback_active` so a dead primary is a one-line diagnosis, not a mid-run
timeout.

## Safety model

Three independent layers on PostgreSQL; two layers plus plan hints on SQLite:

1. **Read-only connection.** PostgreSQL: RO role with
   `default_transaction_read_only=on` + `statement_timeout`. SQLite:
   `PRAGMA query_only=ON` on every pooled connection.
2. **AST validation (`validate_query`).** sqlglot rejects anything that is not
   exactly one SELECT — including CTE-hidden writes — plus references to
   schemas outside the allowlist.
3. **EXPLAIN guard (`explain_query`).** PostgreSQL: two-tier cost thresholds
   (warn / reject) with rewrite hints derived from the plan. SQLite:
   `EXPLAIN QUERY PLAN` has **no cost estimates** — the cost guard is a
   PostgreSQL feature; SQLite mode still catches invalid statements pre-run and
   degrades to plan-shape hints (full scans, temp B-trees, automatic indexes).

`run_query` re-runs layers 2–3 internally and enforces a server-side row cap
(default 1,000, max 10,000) regardless of any LIMIT in the SQL.

## Tracing (Langfuse)

Optional, off by default. Set `LANGFUSE_ENABLED=true` plus
`LANGFUSE_PUBLIC_KEY` / `LANGFUSE_SECRET_KEY` / `LANGFUSE_BASE_URL` to emit one
span per MCP tool call, a generation span for `refine_question`, and harness
spans per fixture. Clients that pass `session_id` into tool calls get their
trace stitched with the server's spans under one session. When disabled, the
SDK is never imported.

## Tests

```bash
# Unit layer — no DB, no LLM required
uv run --extra dev pytest tests/test_config_env_only.py tests/test_safety.py \
    tests/test_tools.py tests/test_refine_question.py -v

# Fixture harness — needs the configured DB and a live LLM endpoint
uv run --extra dev pytest tests/test_fixtures.py -v
```

Fixture runs write reports to `tests/reports/<ISO>/` (gitignored) and cache
verified SQL results in `tests/.db_cache/` keyed on
`(normalized_sql, db_fingerprint)` — the LLM call is never cached.

## Operational notes

- **Skill hot-reload:** skill files are cached by mtime; edits are picked up on
  the next MCP call. `POST /admin/skill_reload` force-invalidates (Phase 1–2).
- **Phase 1 backups:** re-running introspection backs up prior skills to
  `skills/.bak-<ISO>/` (retention `INTROSPECTION_BACKUP_RETENTION`).
- **Table menu is authoritative:** a table missing from the master file's
  TABLES section is invisible to clients even if it exists in the DB.
- **Windows:** the launcher (`texttosql_mcp.run_http`) pins a
  `SelectorEventLoop`, required by psycopg async.

### Troubleshooting

| Symptom | Likely cause | Fix |
|---|---|---|
| Tool calls return HTTP 500 "Task group is not initialized" | Mounted app without the FastAPI lifespan | Start via `texttosql_mcp.run_http`, not a bare `uvicorn app:...` |
| `validate_query` rejects with `forbidden_schema` | SQL references a schema outside the allowlist | Use unqualified/`SCHEMA_NAME` tables, or opt in via `ALLOWED_QUERY_SCHEMAS` |
| Every query rejected with high cost | Thresholds too low for your warehouse | Raise `EXPLAIN_WARN_COST` / `EXPLAIN_HARD_COST` (PostgreSQL only) |
| Accented/case-variant text doesn't match | Case-sensitive `=` comparison | Use case-insensitive matching (`ILIKE` on PostgreSQL, `LIKE` on SQLite) |
| `refine_question` returns the question unchanged | LLM endpoint down and no fallback key | Check `GET /health` → `llm.fallback_active`; set `LLM_FALLBACK_API_KEY` |
| Fixtures re-execute all SQL after a DB reload | Fingerprint changed (expected) | Bump `DEV_DB_VERSION` only when you *want* invalidation |

## Repository layout

```
src/texttosql_mcp/
  server.py           # FastMCP tool registration (six tools)
  server_http.py      # FastAPI app: /mcp mount, /health, /admin/* (Phase 1)
  server_stdio.py     # stdio transport alternative
  run_http.py         # launcher (Windows-safe event loop)
  config.py           # env-only Settings (pydantic-settings)
  db.py               # async engine + read-only guard + db_fingerprint
  llm.py              # ChatOpenAI factory with automatic fallback
  tools/              # refine_question, list_tables, get_table_schema,
                      # validate_query, explain_query, run_query, introspect_schema
  safety/             # sqlglot AST guard, EXPLAIN guard, plan hints
  introspection/      # schema sweep, skill renderer, fixture stub
  skill/              # mtime-cached loader + section parser
skills/               # curated knowledge (generated locally; gitignored)
tests/                # unit tests + NL→SQL fixture harness
demo/                 # bundled SQLite demo DB + dataset license
scripts/              # verify_role.py, csv_to_sqlite.py
```

## License

MIT — see [LICENSE](LICENSE). Bundled demo dataset attribution:
[THIRD_PARTY_LICENSES.md](THIRD_PARTY_LICENSES.md).