Curious analysts and supporters
People who know the football question they want answered but do not want to write the query themselves.
03 · Agents · SQL analytics
Touchline
An agentic football data analyst that turns plain-English questions into read-only SQL, executes the query, and grounds its answer and charts in the returned rows.
Problem
Give an analyst flexibility without making the model the source of truth.
Static dashboards answer predefined questions. Football analysis often starts differently: “Which teams improved most after 2010?” or “How has this head-to-head changed?” Touchline lets a user ask a new question while keeping the calculation inspectable as SQL and returned rows.
Read-only by construction
The connection, database pragma and query validator all prevent the agent from modifying data.
Answers must trace to rows
A convincing sentence is not enough; every stated number is checked against a tool result.
Data
49,363 source records become 49,291 queryable completed matches.
The source snapshot contains 49,363 international match records. Seventy-two are future or unplayed 2026 fixtures with missing scores, so the build excludes them. The SQLite database used by the agent and browser demo therefore contains 49,291 completed matches from 1872 to 2026.
A long-form team_match view creates one row per team per match with goals for, goals against, venue and result. That makes team-centric questions easier to express and reduces repeated home-versus-away logic.
- Source snapshot
- 49,363 records, including 72 unplayed fixtures
- Analysis database
- 49,291 completed international matches
- Storage
- SQLite fact table plus a long-form team_match view
- Derived fields
- Year, winner, total goals, goal difference and W/D/L result
Approach
Use the model to plan; use the database to calculate.
A model-agnostic tool loop receives the schema and question, proposes a tool call, receives the execution result, and either repairs its query or answers. The loop is capped at six iterations. The same harness runs local Ollama models and a frontier API model so their differences are attributable to the “brain,” not a different agent.
When a local model prints SQL as prose rather than emitting a native tool call, a text-extraction fallback recovers the query and executes it. This forces the model to see real rows before answering instead of accepting an unsupported guess.
System architecture
A small agent loop with an inspectable security seam.
- InterpretModel reads the question, schema and tool contract.
- ValidateA guard accepts one SELECT or WITH statement only.
- ExecuteSQLite runs through an OS-level read-only connection.
- RepairQuery errors can return to the model for one more attempt.
- AnswerProse and optional chart are grounded in returned rows.
Defence in depth
The text guard improves feedback and blocks stacked or obviously unsafe statements. The stronger control is below it: the database itself is opened read-only with query_only, so a prompt cannot grant the model write permission.
Evaluation method
Score the answer a user reads, then diagnose how the agent got there.
Thirty natural-language questions span seven categories: lookup, aggregation, head-to-head, conditional, trend, superlative and multi-step. Each question has reference SQL; the gold answer is computed from the database rather than hand-entered.
Did the answer contain the gold value?
The final prose is checked with numeric tolerance, and accuracy carries bootstrap 95% confidence intervals.
Did every number come from a tool result?
This catches a partially correct answer that adds an invented statistic around the correct value.
What did reliability cost?
Valid-SQL rate, self-correction, tool calls, end-to-end latency and API token cost are recorded per run.
Results
The frontier model was more accurate — and faster — but not perfectly grounded.
100%
Answer accuracy · frontier-model run · 30 questions
76.7%
Best local answer accuracy · qwen2.5-coder 7B
US$0.008
Mean frontier-model API cost per question
| Model | Answer accuracy | Valid SQL | Grounded | Mean latency | Cost / question |
|---|---|---|---|---|---|
| Claude Opus 4.8 | 1.000 | 1.000 | 0.867 | 4.86 s | US$0.00798 |
| qwen2.5-coder:7b | 0.767 | 0.683 | 0.967 | 8.12 s | US$0 |
| llama3.1:8b | 0.733 | 0.857 | 0.833 | 5.36 s | US$0 |
The result is a trade-off, not a universal model ranking. The frontier run answered all 30 questions correctly and wrote valid SQL every time, but only 86.7% of responses passed the stricter numeric-grounding check. Qwen grounded numbers most consistently at 96.7% while producing executable SQL less often. Both local models cost nothing per call and kept processing on-device.
Failure cases & trade-offs
“Code model” was not shorthand for “better SQL agent.”
Native tool use failed locally
Qwen initially printed a query in prose and then hallucinated an answer without executing it. The extraction fallback made the query observable and forced a grounded second turn.
The local models failed differently
Qwen was stronger on several single-table conditions; Llama produced more valid SQL and handled head-to-head joins better. Their overall confidence intervals overlap.
Iteration caps are operational controls
One Qwen question entered a 37-second sequence of failed repairs. A hard loop and query timeout bound latency even when reasoning does not converge.
Self-training first regressed
An early fine-tune over-specialised: original-set single-shot accuracy fell from 60% to 40%. A broader bank and held-out gate were needed before a later round improved both tracked sets.
The browser demo replays planning
Its SQL execution and charts are live, but model plans come from recorded runs. Live planning would require a server and credentials that this static site deliberately does not expose.
What I would improve next
Make the tool boundary observable enough for production use.
- Expand the evaluation set and add paraphrase, ambiguity and adversarial prompt variants.
- Use an AST-based SQL policy with query-plan, row and runtime budgets.
- Add audit logging for prompts, tool calls, result hashes, corrections and final claims.
- Require explicit citations from each numeric sentence to the rows that support it.
- Route simpler questions to a local model and escalate uncertain cases using measured thresholds.
Live demo & source
Replay a real agent plan and execute its SQL in your browser.
The database and evaluation runs load only when the panel opens. The demo executes read-only SQL with sql.js and redraws the result locally; no user query or data is sent to a model API.
Load Touchline in this page
Faithful but lightweight: SQL and charts run now against the shipped database. The agent reasoning is replayed from recorded evaluation runs because live language-model planning needs a server.
Open this panel to load the in-browser database.