Context
The brief: let a marketing team get answers from their analytics database without writing SQL. How do users split across segments, which products sell most, how did orders move last month. I built a prototype that translates a plain-English question into ClickHouse SQL, runs it, and returns a table, a chart and a short explanation in a chat interface.
It was built over a few days as a technical assessment prototype. It demonstrates an approach; it is not a product, and I claim no usage or impact numbers for it.
Constraints
- A hosted model with fast responses, so the chat feels like a conversation; Groq serves the model.
- ClickHouse's SQL dialect, which differs from the SQL most models learned: correlated subqueries are not supported, and a trailing semicolon breaks the client.
- Answers readable by people who do not read SQL: a table, a chart where one helps, and a sentence of explanation.
- Hosting on Vercel: the backend as a serverless function, the frontend as a static site.
Architecture
The frontend is a React chat interface with Redux Toolkit for state, Chart.js for charts and Tailwind for styling, built with Vite. It posts the question and a session id to a FastAPI endpoint. Small talk and introductions are answered without touching the database. For a data question, the agent builds a prompt from the table schemas and the dialect rules, asks the Groq-hosted model for SQL, extracts the statement from the reply and runs it against ClickHouse. If the model reaches for a correlated subquery, the agent asks once more for a rewrite with a join.
The result becomes a markdown table, and a chart type is chosen by rules on the shape of the result: a single value becomes a headline figure, one column a table, a category with a measure a bar chart, a date with a measure a line chart. A final model call writes the explanation from the rows. A loader script fills ClickHouse with a sample dataset of users, orders, products and group leaders.
Decisions and tradeoffs
- Rule-based chart selection instead of asking the model which chart to draw. Deterministic and testable, at the cost of being coarse.
- Dialect rules in the prompt, plus one targeted retry, rather than a SQL rewriter. Fast to build; the rules hold only as well as the model follows them.
- Session memory kept in process. Each session gets a short window of recent turns, but in the repository code the window is saved after every answer and never read back into the prompts, so follow-up questions do not yet see earlier turns. On a serverless backend, in-process memory would also vanish between cold starts.
- The guardrail gap, stated plainly. The generated SQL runs as it comes back, under the configured database user. Nothing in the code restricts it to reads: the extractor accepts INSERT, UPDATE and DELETE as readily as SELECT, and the prompt does not ask for read-only queries either. On a real database this is the first thing to fix: a read-only user, a parser-based allowlist of SELECT and WITH statements, a row limit and a statement timeout. I am flagging it here rather than hiding it.
Outcome
A live demo and a walkthrough video, linked below. The repository's sample questions show the intended scope: segment breakdowns, top products by revenue, orders over time and average order value by segment, each meant to come back as a table, a chart where the rules call for one, and a short explanation. Within that scope the prototype does what the brief asked; the two gaps above mark where it stops.
What I would change
- Read-only enforcement in the database and in the application, as above.
- Feed the session window into the SQL prompt, and keep it in Redis or PostgreSQL so it survives cold starts.
- Schema-aware retrieval of table and column descriptions, so the prompt scales past a handful of tables.
- A validation loop: parse the SQL, run it with a limit, and return errors to the model once.
- An evaluation set of questions with golden SQL, so prompt changes are measured.
Stack and links
FastAPI, LangChain and Groq on the backend, ClickHouse for storage, and React, TypeScript, Redux Toolkit, Chart.js, Tailwind CSS and Vite on the frontend, hosted on Vercel. The live demo and the video are linked below. The source repository is not public at the time of writing.