Support Ticket

An MCP server that lets you ask natural-language questions about the Tobi-Bueck/customer-support-tickets dataset (bilingual EN/DE support tickets) and get accurate answers - through Claude Code or Codex, locally.

How it works (thin server, host does the reasoning)

The dataset is a mix of categorical fields (type, queue, priority, language, tags) and free text (subject, body, answer). Those need different tools:

Question type	Example	Tool
Counts / aggregates	"How many tickets per queue?"	query_tickets (SQL → DuckDB)
Group-bys / breakdowns	"Breakdown by priority and language?"	query_tickets
Most common tags	"What are the top ticket tags?"	query_tickets (UNION tag_1..tag_8)
Free-text / paraphrase	"What are customers saying about billing?"	search_tickets (embeddings)
See the data shape	(first call, always)	get_schema

The MCP host (Claude Code / Codex) is the chat interface and the LLM. It reads the schema, writes the SQL itself, and picks which tool to call. This server stays a thin, safe data layer - so the SQL tools need no API key.

You ─▶ Claude Code (writes SQL, routes) ─stdio─▶ this server ─▶ DuckDB + vector index

Tools

• get_schema() - columns, types, sample values for categoricals, null counts, usage notes. Call first.
• query_tickets(sql) - runs a read-only SELECT on the tickets table (columns: subject, body, answer, type, queue, priority, language, version, tag_1..tag_8, tags_all). Guards: single statement, SELECT/WITH only, no DDL/DML, SQL results capped at 500 rows, errors returned to the model for self-correction.
• search_tickets(query, k=5) - semantic search over subject+body via OpenAI embeddings (EN/DE). Returns up to k results (capped at 20), filtered by a similarity threshold so off-topic queries return nothing. Needs OPENAI_API_KEY.

Requirements

• Python 3.10+
• Packages in requirements.txt (mcp[cli], duckdb, pandas, numpy, openai, python-dotenv)

Setup

git clone <your-repo-url>
cd support-ticket-mcp

# create a virtualenv (optional but recommended)
python -m venv .venv
source .venv/bin/activate          # macOS / Linux
# .venv\Scripts\Activate.ps1       # Windows PowerShell
# .venv\Scripts\activate.bat       # Windows cmd

pip install -r requirements.txt

# 1) Download the dataset -> data/tickets.csv
python download_data.py

# 2) Only needed for semantic search: add your OpenAI key
cp .env.example .env        # Windows: copy .env.example .env  - then edit it

# 3) (recommended) prebuild the embedding index so the first search is instant
#    - otherwise it builds lazily on the first search_tickets call:
python -m support_mcp.build_index

Environment variables

Variable	Required	Purpose
OPENAI_API_KEY	only for search_tickets	embeddings for semantic search
OPENAI_EMBED_MODEL	no	embedding model, defaults to text-embedding-3-small
SEARCH_MIN_SIMILARITY	no	similarity threshold for search (0–1), defaults to 0.30
TICKETS_CSV	no	point the server at a custom CSV path

(The download source is set in download_data.py; override it with a DATASET_URL environment variable when running that script if needed.)

Run it standalone (sanity check)

python support_mcp/server.py        # starts on stdio; Ctrl-C to stop

Optionally inspect the tools without an LLM using the MCP Inspector (an official local debug UI for MCP servers):

npx @modelcontextprotocol/inspector python support_mcp/server.py

Connect to Claude Code

From the project directory, register the server (this is the form that works - note the -- before the command):

claude mcp add support-tickets -- python support_mcp/server.py

Then launch the interface and start asking:

claude

If the server can't find its packages, your virtualenv may not be active when Claude Code launches it - point the command at the venv's Python directly, e.g. .../.venv/bin/python support_mcp/server.py (or ...\.venv\Scripts\python.exe on Windows).

Connect to Codex

The server is host-agnostic. It was developed and tested with Claude Code; to use it from Codex, add it to ~/.codex/config.toml with its standard MCP config and run codex from the project directory:

[mcp_servers.support-tickets]
command = "python"
args = ["support_mcp/server.py"]
cwd = "/absolute/path/to/support-ticket-mcp"
env = { OPENAI_API_KEY = "sk-..." }

Example questions to try

• "How many tickets are there per queue?"
• "What's the breakdown by priority?"
• "Show the split between English and German tickets."
• "What are the 10 most common tags?"
• "How many tickets are type Incident with high priority?"
• "Find tickets about software crashing or freezing." (uses search_tickets)

Notes / data quirks handled on load

• priority is normalized to lowercase (low/medium/high) for predictable filters.
• The dataset is bilingual (en/de) - language lets you filter or split by it.
• body = the customer's message text, answer = the reply/response to the ticket.
• Tags span tag_1..tag_8 (often NULL); a tags_all column joins them for easy LIKE filters.

Safety & observability

• Engine-level sandbox: DuckDB runs with enable_external_access=false, so queries cannot read files or the network.
• Query guards: single statement, SELECT/WITH only, DDL/DML blocked (string literals stripped before scanning, so text like '%set up%' is fine), 500-row cap.
• Audit log + stderr logging: every executed query is appended as a JSON line to data/query_audit.log (timestamp, SQL, outcome, row count); tool calls are also logged to stderr, which the host displays.