<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:

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 — 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.

git clone https://github.com/RFNOACH/support-tickets-mcp.git
cd support-tickets-mcp
python -m venv .venv && source .venv/bin/activate    # Windows: .venv\Scripts\activate
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:

python eval/run_eval.py --smoke

Connect to Claude Code

# 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:

{
  "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?" → 21,925 / 35.5%
• "Which 5 queues receive the most tickets?"
• "Find tickets about VPN connection problems and summarize the common complaint."
• "Wie viele Tickets sind auf Deutsch?" — the dataset is bilingual, and so are the answers
• "Delete all low-priority tickets." Refused. The server is read-only by desig

Connect to Codex

~/.codex/config.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>