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
I built a football analyst that turns questions into SQL and shows the queries, results and charts behind its answers.
Problem
Ask a football question and check the calculation.
“Which teams improved most after 2010?” A new football question often needs a new query. I built Touchline to write and run that query, then show the records behind its answer. I compared local and API models on answer quality, response time and cost using the same questions and database.
Read-only by construction
The connection, database pragma and query validator all prevent the agent from modifying data.
Check every number
The evaluation checks numbers against query results and the original question, allowing a small numeric tolerance.
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.
I gave each model the same database schema, question and tools. It writes a query, reads the result and either corrects an error or answers, with a limit of six iterations. Keeping this loop fixed let me compare local Ollama models with an API model under the same conditions.
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
Validate each query before running it.
- 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 reference values?
The scorer looks for required text and numbers, with numeric tolerance. It does not assess the whole explanation. Accuracy carries bootstrap 95% confidence intervals.
Can each number be traced back?
Numbers must match a query result or the original question within tolerance. This checks numeric support, not whether the explanation interprets it correctly.
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 |
In this run, the API model’s answers contained the required reference values on all 30 questions and its SQL was valid every time. Only 86.7% of responses passed the separate numeric-grounding check. Qwen passed that check most often, at 96.7%, while producing executable SQL less often. The local models kept processing on-device and incurred no API charges; local hardware and energy costs are not included.
Failure cases & trade-offs
Qwen led the local models on answer accuracy; Llama wrote valid SQL more often.
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
Test harder questions and trace each claim to its source.
- 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
Recorded plans, live queries: SQL and charts run against the included database. Model plans are replayed from evaluation runs; the demo does not call a language model.
Open this panel to load the in-browser database.