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.

Related MCP server: support-ticket-mcp

The data at a glance

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

Architecture

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:

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.