Learn Retail Mind
A complete walkthrough of this codebase, written so you can explain every design decision in an interview — not just describe what the code does, but say why it is that way and what breaks if it isn’t.
How to use this. Read a page here, then open the file it points at and read the real code. Every claim on these pages is tied to a specific file, and the code carries comments explaining the why at the point where it matters.
What this project actually is
Retail Mind is an API-first text-to-SQL platform. You connect a database, ask a question in English, and get back an answer, the SQL that produced it, the rows, and a chart spec.
POST /v1/query
{ "source_id": "...", "question": "Top 5 products by revenue" }
→ { "answer": "Espresso Blend led with $48,210...",
"sql": "SELECT p.name, SUM(oi.line_total) ...",
"data": { "columns": [...], "rows": [...] },
"chart": { "type": "bar", "series": [...] } }The interesting engineering is not “call an LLM”. It is everything around that call:
| The easy demo | What this codebase actually does |
|---|---|
| Paste the schema in the prompt | Real schemas exceed the context window — select ~8 relevant tables, then compress |
| Run whatever SQL comes back | Model output is untrusted input — six validation layers before it touches a database |
| ”It worked when I tried it” | An eval harness with execution accuracy, so a prompt change is measurable |
| Call the OpenAI SDK | A provider interface with six implementations, including local models |
| Retry on failure | Classify the failure first — retrying a daily quota once cost three hours |
| One user, one database | Per-key ownership, encrypted credentials, cross-tenant isolation |
The one-paragraph version
Retail Mind turns a natural-language question into SQL, runs it against the user’s own database, and explains the result. A nine-stage pipeline handles caching, intent classification, schema retrieval, SQL generation, validation, execution, answer synthesis and chart building. Two of those nine stages call a model; the rest are deterministic code. The system is multi-tenant, so data-source credentials are encrypted at rest and every read is filtered by ownership in the SQL itself. Generated SQL passes a six-layer read-only validator before execution. Quality is tracked with an execution-accuracy eval suite over a fixed dataset, so prompt and model changes are measured rather than guessed at.
Memorise that. It is the answer to “so what did you build?”
Reading order
Architecture
The layering rule, the request lifecycle, and where each concern lives. Start here — everything else assumes it.
The Query Pipeline
The nine stages in order, with the real code. This is the heart of the product and the thing you will be asked about most.
The AI Layer
The provider abstraction, prompt design, and the failure taxonomy that decides whether to retry.
Schema & Connectors
The retrieval problem: fitting a 400-table schema into a context window.
Security
Why the SQL validator is an allow-list, and how multi-tenancy is enforced.
Async, Jobs & Event Loops
Where Python’s concurrency model actually bites, with two production bugs as evidence.
Evaluation
How you prove a change helped. The most interview-differentiating section.
Deployment & Ops
Containers, migrations, infrastructure-as-code, CI, and free-tier constraints.
Bugs That Shipped
Nine real defects, each with symptom, root cause, fix, and the transferable lesson. Interviewers love these.
Interview Q&A
Around 60 questions with answers, grouped by round.
System Design Round
How to scale this to 10,000 tenants on a whiteboard.
Coding Round
Exercises drawn from this codebase, with solutions.
If you only have an hour
Those three carry most of the interview value. The pipeline is what you built; the bugs are what prove you actually built it; the Q&A rehearses the delivery.
Honesty matters in interviews
This project was written with heavy AI assistance. That is completely normal in 2026 and nobody sensible penalises it — but you must be able to defend every decision, because that is what is actually being tested.
The failure mode is not “you used AI”. It is being asked “why is chart generation not an AI call?” and having no answer. Everything on these pages exists so that you do.
Do not memorise these pages as scripts. Read the linked code until you could redraw it from memory. An interviewer will follow up, and follow-ups are where recited answers fall apart.
Known weaknesses — know these too
Being able to critique your own project is a strong signal. The honest list:
- Schema search is keyword-only.
pgvectoris installed and the embedding plumbing exists, but selection scores by token overlap. On a 400-table schema with names likedim_cust_x, semantic retrieval would beat it. This is the highest-value next piece of work. - One self-correction retry. A query failing validation twice gives up. Feeding back the database’s error, not just the validator’s, would recover more.
- 23 eval cases on one schema. Enough to catch regressions, too small for confident cross-model claims, and not cross-schema — which is where text-to-SQL systems usually break.
- No result streaming end to end. Connectors expose
execute_query_stream(), but the pipeline materialises rows before answering. - No per-key cost accounting. Tokens are tracked per query; there is no spend cap.
Say these before you are asked. It reads as engineering maturity.