AI Game Analyst MCP server

AI Game Analyst

Try it live, ask a real question about the video game market and watch it write the SQL itself. (Free-tier host; the first request after a quiet stretch can take up to a minute to wake back up.)

Most portfolio AI agents are a system prompt wrapped around a chat UI. This one writes and runs real SQL against a database it built itself, corrects its own tool-call errors when they actually happen, and reports its own measured accuracy, retrieval quality, and per-question cost, numbers you can check yourself below, not claims asking to be taken on faith.

Built as a series of thin, working vertical slices; this snapshot is through Slice 65 (see PLAN.md for the full roadmap, ARCHITECTURE.md for a diagram-first tour of the current system, and DOCEXP.md for the full engineering log).

Tech stack

Languages

AI / Agent

Data

Backend

Frontend

Tooling and CI/CD

mypy and sqlglot (the real SQL parser behind the safety guard) round out the backend but don't have their own badge icons.

AI Engineer Features

• Agentic tool use with a measured self-correction rate, not a claim. The agent (router → retrieve_schema → agent ↔ execute_tools → build_chart_spec, see ARCHITECTURE.md) retries its own tool errors up to 3 times, and attempts/tool_errors are real counters, surfaced per request and aggregated live at /health as self_correction, not a one-off screenshot of it working once.
• RAG with retrieval quality that's measured, not assumed. Schema chunks are embedded and retrieved per question instead of always stuffing the whole schema into the prompt, and a hand-labeled recall@k eval gates that in CI, currently 1.000 recall at production's real top-k.
• A real eval harness that has actually found real bugs, not just a green checkmark. A golden question set (18 questions, grown live as real gaps were found) with ground truth computed from the database at eval time, deterministic checks, and an LLM-as-judge pass, with a real exit code, runs daily and on demand in GitHub Actions. It's caught a wrong regression check testing which tool ran instead of whether the answer was correct, a router misclassifying an analysis question, and a repeated hallucination pattern (below), every one written up in full rather than quietly fixed, see DOCEXP.md.
• A real, repeated hallucination, fixed structurally instead of prompted away. An LLM asked to restate a statistics tool's own outlier result kept padding its answer with an unverified extra name - confirmed twice, even after the prompt asking it not to got progressively more specific. The fix wasn't a third prompt attempt: for that result, the model no longer generates the fact in its final answer at all, it's rendered deterministically from the tool's real output, and the model only narrates around it. Full case study, including the statistically-invalid-outlier-test bug this same investigation surfaced next, in ARCHITECTURE.md.
• Real cost and token accounting, not an after-the-fact estimate. Every /ask response reports its own total_tokens and estimated_cost_usd (Groq's actual live on-demand pricing), logged per request and aggregated at /health as usage.
• A clarifying question that resolves, instead of a dead end. When the router genuinely can't tell what a question means, the reply completes it in one hop - combined with the original question into one plain string at the API boundary, no new graph state, no session store. Suggested follow-up chips (up to 3 per answer) are generated deterministically from the answer's own data, zero extra Groq calls, not a second model call spent on a nice-to-have. Deliberately scoped narrower than full chat memory, and the UI is built to never imply otherwise: a plain single-field form for the reply, never a chat thread. Full case study, including how the scope's one known edge case was verified live rather than assumed, in ARCHITECTURE.md.
• Honest about limits, by design, and about what actually has one. The forecast tool reports "insufficient history" instead of fabricating a number when a game's live player-count history is too young to project from. Groq's shared daily token budget gets an honest "check back tomorrow" message instead of a generic error when it's genuinely exhausted (it has been, more than once, during real testing). Render's free-tier cold start is a real, measured 40.4 seconds - disclosed as a number, not hidden, with a retry-with-backoff fix so a cold start recovers on its own instead of needing a manual refresh; once warm, real data loads in under 1.5 seconds. Ludo also doesn't carry a running conversation past completing its own clarifying question - disclosed, not discovered by a confused user. Full details in ARCHITECTURE.md.
• The same guarded tools, exposed two real ways. A web app for humans, and an MCP server (src/mcp_server/) exposing the identical run_sql/run_stats implementations to Claude Desktop, Claude Code, or Cursor, one safety boundary, not two implementations to keep in sync.

Measured results

Real numbers from a real run of python -m src.evals.run_evals against the golden question set, real Groq token usage, and Groq's actual current on-demand pricing, published as they came out, not re-run until clean. This suite also runs automatically in CI, daily and on demand (.github/workflows/run_evals.yml). See DOCEXP.md's Slice 24/27/30/36 entries for full methodology and caveats.

Metric	Result
Route accuracy	18/18
Deterministic checks	18/18
Avg LLM-judge score	5.0/5
Avg /ask latency	2.4s (n=18, real full-graph runs)
Avg cost per question	$0.00083 (Groq on-demand list price)
RAG retrieval recall@8	1.000 (15 hand-labeled questions)

The question set has grown live, from 5 to 18, specifically as real gaps were found rather than to pad a count, see ARCHITECTURE.md for the most recent addition this table reflects: a real, repeated hallucination the harness caught, fixed structurally, then re-confirmed live at a perfect 5.0/5.

Worth being honest about how an earlier version of this table got to 5/5 (on the original 5-question set): it showed a real, repeated 4/5 and described it as the same model bug recurring. That framing was wrong. Investigating it properly found the failing check itself was testing which tool the model used, not whether its answer was correct, so a genuinely right answer via plain SQL instead of the expected tool got marked as a failure every time. Fixed the check to verify the real returned value regardless of which tool produced it, then confirmed the fix for real in CI (fresh runner, freshly built catalog), not just locally. Full writeup in DOCEXP.md's Slice 30 and 36 entries, including the earlier, wrong framing, left visible rather than edited away.

Quickstart

Dependencies are managed with uv. Always include --extra agent, a bare uv sync installs only the lean ingestion/db base set and will actively uninstall FastAPI/LangGraph/scipy if they're already there, see pyproject.toml's comments for why the split exists.

uv sync --extra agent
cp .env.example .env
# then set GROQ_API_KEY (free tier: https://console.groq.com/keys)

python -m src.ingestion.ingest      # one-time; builds the local DuckDB catalog (a few minutes)
uvicorn src.api.main:app --reload   # serves the API

curl -X POST http://127.0.0.1:8000/ask \
  -H "Content-Type: application/json" \
  -d '{"question": "What are the 5 most-owned free-to-play games?"}'

Try a lookup ("Which game has the highest peak concurrent player count?"), an analysis ("Is the price difference between Action games and other games statistically significant?", a real run_stats p-value), a forecast ("How many players will Counter-Strike 2 have next month?", honest about insufficient history for most games right now), or a deliberately vague one ("Is this game good?", the agent asks which game instead of guessing). The response includes the SQL actually run, the raw rows, and which RAG-retrieved schema chunks were used, so retrieval is visible, not asserted.

Frontend (separate terminal, backend must already be running):

cd frontend
cp .env.local.example .env.local
npm install
npm run dev

Open http://localhost:3000. See frontend/README.md for the UI itself (a dark, neon-green identity; a real film-strip hero of live Steam cover art; a genre browser; charts; a step-by-step "show the work" trace).

Tests (88 pytest tests, real DuckDB fixtures, no mocks of this project's own DB layer; needs the dev extra):

uv sync --extra agent --extra dev
uv run pytest -v

Runs on every push/PR via .github/workflows/test.yml, alongside ruff and mypy. See PLAN.md for what else exists (an eval suite you can run yourself with python -m src.evals.run_evals, an optional live-player-count poller, and more).

Architecture

A LangGraph agent: router classifies each question (lookup / analysis / forecast / needs-clarification) with structured LLM output before any database work happens, then retrieve_schema → agent ↔ execute_tools → build_chart_spec runs a real, hand-written tool-calling loop with self-correction, not a prebuilt agent constructor. Every SQL query passes through a read-only, guarded connection enforced by a real SQL parser, independent of whatever the prompt told the model to do. Full diagrams, the node-by-node walkthrough, and the design choices worth defending are in ARCHITECTURE.md.

MCP server

src/mcp_server/server.py exposes run_sql and run_stats, the exact same guarded implementations the LangGraph agent uses, to any MCP-compatible AI app. Free to run: local stdio transport, no hosting, no LLM API key needed (the calling app supplies its own model).

uv run mcp dev src/mcp_server/server.py   # smoke test with the official MCP Inspector

Claude Code:

claude mcp add ai-game-analyst -- uv run --directory "C:\path\to\full_stack_project" python -m src.mcp_server.server

Claude Desktop (%APPDATA%\Claude\claude_desktop_config.json on Windows):

{
  "mcpServers": {
    "ai-game-analyst": {
      "command": "uv",
      "args": ["run", "--directory", "C:\\path\\to\\full_stack_project", "python", "-m", "src.mcp_server.server"]
    }
  }
}

Requires the database to already exist (python -m src.ingestion.ingest first).

Deploying

Live since Slice 19: backend on Render (free tier, Docker), frontend on Vercel (Hobby). A normal Python host for the backend rather than Vercel Python functions, deliberately, this stack's real footprint (an ONNX embedding model, scipy, a DuckDB file, multi-step LLM calls that can run 10 to 30 seconds) doesn't comfortably fit serverless limits.

Full setup steps, environment variables, and the caveats worth knowing (in-memory cache and rate limiter, Render's free-tier cold-start behavior, keeping data fresh via scheduled GitHub Actions) are in DOCEXP.md's Slice 19 entry and the workflow files themselves (.github/workflows/poll_player_counts.yml, .github/workflows/refresh_catalog.yml, .github/workflows/check_deploy_freshness.yml).

More

• PLAN.md, the full slice-by-slice roadmap, including what was considered and dropped
• ARCHITECTURE.md, a diagram-first tour of the current system and the design choices worth defending
• DOCEXP.md, the full engineering log: what broke, what got tried, what surprised me, including the mistakes
• frontend/README.md, the UI in detail