The brief
Most dashboards can only answer questions that someone anticipated when they designed the report. Football data makes that limitation obvious because a user may want a league table one minute, card totals the next, and a comparison across months after that.
PitchQuery was built to support those unplanned questions without pretending that model-generated SQL should be trusted automatically. The interface therefore shows the answer alongside the executed query, tool trace and any assumptions made.
The real design question: how do you make natural-language analysis flexible without turning the database into a black box?
From question to checked answer
The production snapshot covers 22 European competitions between 27 July 2018 and 31 May 2026. A reproducible Python pipeline downloads public historical CSV files, normalises the schemas and checks that the required coverage exists before packaging the data.
FastAPI and Pydantic provide the API and response contract. The browser and API share the same origin in production, which keeps the deployed architecture small and removes unnecessary cross-origin configuration.
The generated query is never the final authority
Every statement is parsed before execution. The validator rejects anything that falls outside the contract and returns it to the agent for correction.
Read-only statements
One SELECT or WITH statement per call. Writes and multiple statements are rejected.
Table allowlist
The normalised matches table is the only permitted analytical source.
Exact league scope
Every table scan must use the selected league, including each branch of a UNION.
Bounded output
A maximum result size is added when the generated query omits a limit.
The execution layer opens DuckDB in read-only mode as another boundary. Tests cover real query execution and deliberate attempts to write, use an unknown table, omit a league filter or partially scope a UNION.
Decisions that made the project more credible
Expose the SQL
Hiding the generated query would make the interface cleaner but much harder to audit. The final SQL is treated as part of the answer, not implementation noise.
Bundle a verified snapshot
The serverless API starts with real data immediately. The build script remains available for reproducibility, while coverage checks stop an incomplete refresh from silently replacing the production dataset.
Keep the boundary narrow
Allowing arbitrary tables would make the demonstration look more flexible, but it would weaken the contract. One approved table and an exact league filter make the behaviour easier to reason about and test.
What the data cannot answer
The source contains team-level match summaries rather than player events. A question such as “who received the most cards?” therefore means the team, not an individual player. The application reports that assumption instead of implying more detail than the dataset holds.
Natural-language querying also remains probabilistic. Guardrails reduce the space in which the agent can be wrong or unsafe, but a visible query and stated assumptions are still necessary because a syntactically valid query can misunderstand the user’s intent.