# Discord Text2SQL MCP

[![CI](https://github.com/JstnClmnt/discord-text2sql-mcp/actions/workflows/ci.yml/badge.svg)](https://github.com/JstnClmnt/discord-text2sql-mcp/actions/workflows/ci.yml)
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](LICENSE)

Local-first, MCP-native Text2SQL Discord assistant. Claude selects and queries one of four BIRD
Mini-Dev domains through a model-independent MCP data service. The two local processes communicate
over Streamable HTTP; the data service also supports stdio clients.

> [!IMPORTANT]
> **Project status: experimental portfolio project.** The complete offline synthetic suite passes,
> but the latest authorized live holdout run did not meet its acceptance gate: 4/28 queries were
> execution-equivalent, and reported dataset attribution was 5/28. This repository
> demonstrates the architecture, safety controls, evaluation discipline, and remaining engineering
> challenges; it is not presented as a production-ready analytics system. See the
> [published evaluation results](docs/EVALUATION_RESULTS.md) for the recorded outcome and caveats.

## What It Demonstrates

- A Discord interface backed by a model-independent, MCP-native orchestration loop.
- Hybrid semantic/BM25 grounding over public Text2SQL data without redistributing the dataset.
- Typed query planning, independent intent review, schema-aware SQL review, and exact reviewed-SQL
  fingerprint enforcement.
- Immutable, read-only SQLite access with parser and authorizer controls, deadlines, and row caps.
- Structured answers with SQL, preview rows, CSV export, charts, summaries, follow-ups, usage, and
  latency diagnostics.
- Reproducible offline tests plus resumable, leakage-conscious live evaluation tooling.

## Architecture

```mermaid
flowchart LR
    user["Discord user"] --> host["Discord host<br/>conversation, limits, artifacts"]
    host <--> provider["Model provider<br/>planning and explanation"]
    host --> review["Grounding, typed QuerySpec,<br/>intent and SQL review"]
    review <--> client["MCP client"]
    client <--> service["Local MCP data service"]
    service --> retrieval["Dataset catalog<br/>and hybrid retrieval"]
    service --> database["Immutable read-only<br/>SQLite databases"]
    host --> answer["AnswerBundle<br/>SQL, rows, CSV, chart, summary"]
    answer --> user
```

## Prerequisites

- Windows and Python 3.11
- [`uv`](https://docs.astral.sh/uv/)
- Approximately 2 GB of free space for the BIRD package, selected databases, model cache, and indexes

## Environment

Create the user-managed environment and install the dependency set:

```powershell
uv venv --python 3.11 discord_sql
uv pip install -r requirements-dev.lock --python discord_sql\Scripts\python.exe
```

`requirements-dev.txt` defines the accepted dependency ranges, while `requirements-dev.lock`
records the tested Windows/Python 3.11 resolution used by local validation and CI. Regenerate the
lock deliberately after changing a requirement:

```powershell
uv pip compile requirements-dev.txt --output-file requirements-dev.lock --python-version 3.11 --python-platform windows --generate-hashes
```

`discord_sql/`, `.env`, downloaded data, and generated indexes are ignored by Git.

Copy `.env.example` to `.env` if you need to override defaults. The unauthenticated MCP server is
intentionally restricted to `127.0.0.1`, `localhost`, or `::1`.

For the Discord host, set `DISCORD_BOT_TOKEN` and `ANTHROPIC_API_KEY` in `.env`. In the Discord
Developer Portal, enable the Message Content privileged intent and invite the bot with permission to
view channels, send messages, and attach files. The host responds to DMs and real mentions; it
ignores bot messages and unrelated guild traffic.

## Prepare BIRD Mini-Dev

Download the official package and extract only `financial`, `debit_card_specializing`,
`california_schools`, and `student_club`:

```powershell
discord_sql\Scripts\python.exe -m discord_text2sql data bootstrap
```

The downloader resumes interrupted Google Drive transfers. Preparation validates the expected 140
examples and writes a deterministic 112-retrieval/28-holdout split plus source SHA-256 checksums.

Build the Chroma collections (the first run downloads `all-mpnet-base-v2`):

```powershell
discord_sql\Scripts\python.exe -m discord_text2sql data index
```

Both steps can be requested together with `data all`.

## Run the MCP Server

Streamable HTTP, available at `http://127.0.0.1:8000/mcp`:

```powershell
discord_sql\Scripts\python.exe -m discord_text2sql mcp --transport streamable-http
```

stdio for MCP Inspector and desktop clients:

```powershell
discord_sql\Scripts\python.exe -m discord_text2sql mcp --transport stdio
```

The service exposes:

- `list_datasets`
- `search_datasets`
- `search_context`
- `get_schema`
- `get_distinct_values`
- `execute_sql`

It also provides dynamic `dataset://{dataset_id}/schema`, `/documentation`, and `/examples`
resources.

## Run the Discord Bot

Keep the Streamable HTTP MCP server running in one PowerShell window. Start the Discord process in a
second window:

```powershell
discord_sql\Scripts\python.exe -m discord_text2sql discord
```

Claude first receives a hybrid semantic/BM25 catalog ranking with governed reference-card IDs. It
may inspect more than one candidate database, but the question locks to exactly one database when
the QuerySpec freezes. The main agent makes a small grounded-dataset request; a focused planner
materializes the full typed QuerySpec and gets one structured repair attempt when validation fails.
A separately prompted intent auditor then approves the schema-valid QuerySpec or makes one
evidence-backed revision before SQL generation. Exact text predicates must also be
verified against distinct values from their precise schema column. Every SQL candidate must then
pass the orchestrator-local AST review and be read by Claude in a later turn before the exact
reviewed SQL can execute. The typed QuerySpec/reviewer supports direct and aggregate outputs,
scaled aggregate ratios/differences, governed day/month/year buckets, and pure disjunctive filter
groups while continuing to block unmodeled formulas. If the domain remains
ambiguous after metadata inspection, the bot presents a dataset selection menu and
resumes after the requester chooses. Text clarification resumes only when the requester replies to
the bot's clarification message; `cancel` discards it, and unrelated questions replace it. Each
channel or DM has an independent ten-turn in-memory history, concurrent work is serialized per
conversation, and requests are rate-limited per user. If the MCP process restarts, the host replaces
its connection and retries the interrupted read-only MCP operation once. Each attempt has the
`MCP_OPERATION_TIMEOUT_SECONDS` deadline so one hung request cannot wedge every conversation.

QuerySpec planning is limited to two host requests and a cumulative
`QUERY_SPEC_TIMEOUT_SECONDS` deadline (60 seconds by default). A failed or looping planner therefore
returns control for clarification or a controlled error before the outer Discord/evaluation request
deadline.

The provider interface keeps the agent host model-independent. Anthropic is the initial provider,
using `claude-sonnet-5` by default and one `claude-opus-4-8` escalation attempt. Intent review uses
an isolated call to the active phase model and is included in usage accounting. The loop allows at
most fourteen total tool calls by default, one primary-model SQL correction, at most one subsequent
escalation-model SQL execution, and one clarification round. Stable system/tool prefixes use the
provider's five-minute prompt cache. It does not send non-default sampling parameters.

## Answers and Artifacts

Every successful query is normalized into an `AnswerBundle` containing the selected dataset, SQL,
columns and rows, summary, optional chart specification, five suggested follow-ups, usage, latency,
escalation state, and independently tracked post-processing errors. Discord always shows the full
generated SQL and up to ten preview rows. Results longer than the preview include a UTF-8 CSV
attachment; spreadsheet-formula prefixes are escaped before export.

Claude can request only a declarative `bar`, `line`, `scatter`, or `pie` chart using columns that
exist in the query result and a numeric y-axis. The host never executes model-generated Python.
Trusted, browser-free Matplotlib Agg code renders at most `CHART_MAX_POINTS` points to PNG in a
disposable worker process. Timed-out workers are terminated. A chart, summary, follow-up, CSV, or
attachment failure is logged and degraded independently so successful SQL results remain visible.

`CHART_RENDER_TIMEOUT_SECONDS` includes Windows process startup, Matplotlib imports, first-use font
cache work, and rendering, so configuration values below ten seconds are rejected.

Five distinct follow-up questions appear in a requester-only Discord selection menu. Choosing one
resumes the same channel or DM conversation and uses its bounded history.

## SQL Safety

User/model SQL is parsed as SQLite by SQLGlot and must contain exactly one read-only query. SQLite
opens the database in immutable read-only mode and independently authorizes each operation. Writes,
DDL, transactions, pragmas, attached databases, and extension loading are denied. Execution has a
five-second default deadline and results are capped at 500 rows.

## Validation

```powershell
discord_sql\Scripts\ruff.exe check .
discord_sql\Scripts\pytest.exe -q
discord_sql\Scripts\python.exe -m discord_text2sql doctor
```

`doctor` performs a real isolated Matplotlib Agg PNG render in addition to checking imports and
configuration, so a clean report confirms that the static renderer can actually produce a PNG.

Tests are offline and use synthetic SQLite databases, scripted model/tool clients, and a deterministic
fake embedding function; they do not require Discord, Anthropic, BIRD, or Hugging Face credentials.

See [`THIRD_PARTY_DATA.md`](THIRD_PARTY_DATA.md) for BIRD attribution and license information.

## License

The repository source code is available under the [MIT License](LICENSE). BIRD Mini-Dev is downloaded
separately, is not redistributed by this repository, and remains subject to its own CC BY-SA 4.0
terms described in [`THIRD_PARTY_DATA.md`](THIRD_PARTY_DATA.md).

## Operations and Evaluation

- [Complete local Windows setup](docs/LOCAL_SETUP.md)
- [Discord application and permission setup](docs/DISCORD_SETUP.md)
- [MCP Inspector over stdio or Streamable HTTP](docs/MCP_INSPECTOR.md)
- [Offline and opt-in live holdout evaluation](docs/EVALUATION.md)
- [Published live-evaluation results](docs/EVALUATION_RESULTS.md)
- [Troubleshooting and structured logs](docs/TROUBLESHOOTING.md)