<p align="center">
  <img src="assets/banner.webp" alt="CHEQ Support Tickets MCP — ask 61,765 support tickets anything" width="100%">
</p>

<p align="center">
  <img src="https://img.shields.io/badge/python-3.10%2B-blue" alt="Python 3.10+">
  <img src="https://img.shields.io/badge/MCP-stdio-8A2BE2" alt="MCP stdio">
  <img src="https://img.shields.io/badge/DuckDB-BM25%20FTS-fff100" alt="DuckDB">
  <img src="https://img.shields.io/badge/API%20keys-none%20required-E8117F" alt="No API keys">
  <img src="https://img.shields.io/badge/SQL-read--only-2ea44f" alt="Read-only SQL">
</p>

# Support Tickets MCP Server

**Ask questions in plain English. Get exact, auditable answers computed over 61,765 real customer-support tickets.**

This is an MCP server that turns Claude Code (or Codex) into a support-operations analyst. The model does the reasoning; this server does the computing — every number in every answer comes from a real SQL query or search over the data, never from the model's imagination.

Built for the **CHEQ AI Engineer home assignment** by **Noach Ramallo**.

---

## See it answer

Real transcript, real data (verified against ground truth in `eval/`):

> **You:** Which queue has the highest share of high-priority tickets, minimum 50 tickets?
>
> **Claude:** *[calls `describe_tickets`, then `query_tickets`]*
> **Service Outages and Maintenance — 70.8% of its tickets are high priority**, the clear outlier across all 52 queues. Computed via:
> ```sql
> SELECT queue, round(100.0*count(*) FILTER (WHERE priority='high')/count(*),1) AS pct_high
> FROM tickets GROUP BY 1 HAVING count(*) >= 50 ORDER BY pct_high DESC
> ```

> **You:** Delete all low priority tickets
>
> **Claude:** *[calls `query_tickets`, which refuses]* I can't — this server is strictly read-only. Write statements are blocked by design.

## The data at a glance

| Metric | Value |
|---|---|
| Total tickets | **61,765** |
| High priority | **21,925** (35.5%) |
| Languages | German **33,504** · English **28,261** |
| Busiest queue | Technical Support (**14,186** tickets) |
| Highest-pressure queue | Service Outages and Maintenance (**70.8%** high priority) |
| Most common tag | Security (**9,156**) |

Dataset: [`Tobi-Bueck/customer-support-tickets`](https://huggingface.co/datasets/Tobi-Bueck/customer-support-tickets) — subjects, bodies and agent answers plus structured fields (`queue`, `priority`, `type`, `language`, `tag_1–8`).

## Architecture

<p align="center">
  <img src="assets/architecture.svg" alt="Architecture: Claude Code (the brain) calls the MCP server (the hands) which computes over DuckDB (the memory)" width="100%">
</p>

The design follows one principle: **the LLM already lives in the MCP client, so the server contains no LLM call and needs no API key.** The server is the *hands*, not the *brain* — it gives the client's model safe, grounded, exact access to the data:

| Tool | What it does | Why it matters |
|---|---|---|
| `describe_tickets` | Schema, row counts, null stats, and the **real values** of every categorical column | Grounds the model — it never hallucinates column names or filter values (the #1 accuracy risk in NL→SQL) |
| `query_tickets(sql)` | Read-only SQL (DuckDB dialect) | Exact counts, breakdowns, rankings, percentages |
| `search_tickets(query, …filters)` | BM25 full-text search over subject/body/answer | Topical questions ("tickets about VPN outages") with zero embedding infrastructure; auto-falls back to keyword search if the FTS extension is unavailable |
| `get_tickets(ids)` | Full ticket text by id | Search returns snippets to keep context small; this fetches the detail |

Plus an MCP **prompt** (`ticket_analyst`) that encodes the grounded workflow: describe → query/search → answer strictly from tool output, showing the computation used.

**Guardrails** (verified by the test suite): read-only database connection, single-statement whitelist (`SELECT`/`WITH`/`SUMMARIZE`), keyword denylist as defense-in-depth, row caps, cell truncation, and query timeouts. `DROP`, `INSERT`, multi-statement and `COPY` attempts are all refused.

## Quickstart (3 commands after clone)

Requires Python 3.10+ and ~500 MB disk. **No API keys.**

```bash
git clone https://github.com/RFNOACH/support-tickets-mcp.git
cd support-tickets-mcp
python -m venv .venv
.\.venv\Scripts\Activate.ps1
pip install -r requirements.txt
python scripts/load_data.py        # one-time: downloads the dataset, builds DuckDB + BM25 index
```

Offline demo alternative (bundled synthetic sample, no download): `python scripts/load_data.py --sample`

Verify the installation:

```bash
python eval/run_eval.py --smoke
```

## Connect to Claude Code

```bash
# macOS / Linux (from the repo directory)
claude mcp add support-tickets -- "$(pwd)/.venv/bin/python" "$(pwd)/server.py"

# Windows (PowerShell)
claude mcp add support-tickets -s user -- C:\path\to\support-tickets-mcp\.venv\Scripts\python.exe C:\path\to\support-tickets-mcp\server.py
```

Or via `.mcp.json` in your project:

```json
{
  "mcpServers": {
    "support-tickets": {
      "command": "/absolute/path/to/repo/.venv/bin/python",
      "args": ["/absolute/path/to/repo/server.py"]
    }
  }
}
```

Start `claude`, check `/mcp` shows `support-tickets` with 4 tools, and ask:

- *"How many tickets are high priority, and what share is that?"* → 
- *"Which 5 queues receive the most tickets?"*
- *"Find tickets about VPN connection problems and summarize the common complaint."*
- *"Wie viele Tickets sind auf Deutsch?"* — 
- *"Delete all low-priority tickets."
- *"What is the least common category of tickets."
- *"How many tickets we received last month about password reset."

## Connect to Codex

`~/.codex/config.toml`:

```toml
[mcp_servers.support-tickets]
command = "/absolute/path/to/repo/.venv/bin/python"
args = ["/absolute/path/to/repo/server.py"]
```

## Model / API configuration

Per the assignment spec ("Any LLM is fine"), the solution uses the **MCP client's own model** — developed and verified with **Claude (Sonnet 4.x) via Claude Code**. Any MCP-capable client works. **No API key or environment variable is required** because the server makes no LLM calls of its own.

Optional environment variable: `TICKETS_DB` — path to the DuckDB file (default `data/tickets.duckdb`).

## Evaluation — how we know the answers are correct

The NL→SQL reasoning happens in the client, so correctness is verified by comparison:

1. `python eval/run_eval.py` computes **ground truth** for 10 golden questions using reference SQL.
2. Ask the same questions in Claude Code (`eval/questions.md` lists 13, including text-search ones).
3. The numbers must match exactly — the server computes, it never estimates.

`--smoke` additionally exercises every tool and confirms the SQL guardrails block write attempts.

## Design principles — what this deliberately avoids

- **No second LLM inside the server** — the client already has one. Removing it removes cost, latency, key management and a failure mode.
- **No vector DB / RAG pipeline** — the data is largely structured; exact SQL beats approximate retrieval for operational questions, and DuckDB's built-in BM25 covers text search with zero extra infrastructure.
- **No agent frameworks, no fine-tuning, no hardcoded schema** — `describe_tickets` introspects live, so the server survives data changes.

Simplicity here is a feature: a reviewer goes from clone to answered questions in three commands.

## Production path

The tool contract is the product; the internals swap. In a real environment: point the same four tools at the governed warehouse (**Snowflake / Databricks**) with role-based access and PII masking, add a full audit log of every executed statement, rate limits, remote MCP transport behind SSO, scheduled data refresh instead of a one-time load, and promote the golden-question eval into CI — extended with an LLM-judged harness — so releases cannot silently degrade answer accuracy.

## Repository layout

```
server.py               # the MCP server (single file, ~300 lines)
scripts/load_data.py    # one-time dataset download + DuckDB/FTS build
scripts/make_sample.py  # regenerates the offline synthetic sample
eval/run_eval.py        # golden questions: ground truth + smoke tests
eval/questions.md       # the questions, for manual verification
data/sample_tickets.csv # synthetic offline sample (schema-identical)
assets/                 # banner + architecture diagram
docs/                   # one-page design document (PDF + generator)
```

## Attribution

Dataset: `Tobi-Bueck/customer-support-tickets` (Hugging Face), **CC BY-NC 4.0**, used for non-commercial evaluation. The bundled sample CSV is fully synthetic. CHEQ name and logo are used solely to identify the hiring assignment this project was built for.

---

<p align="center"><b>Noach Ramallo</b> · <a href="https://github.com/RFNOACH">github.com/RFNOACH</a> · Built for the CHEQ AI Engineer home assignment</p>