Sefism is free for learners. We are here to help you grow, one step at a time.Join for free
Sefism.
AI & ML

Guarded Text-to-SQL Assistant for Small Business Sales Data

A final year project that lets a shop or pharmacy owner ask questions about their own sales data in plain English or Roman Urdu and get correct answers, with SQL that is checked before it runs. You build schema-aware retrieval, a read-only validator and a test set of real owner questions to measure execution accuracy.

Topics
Difficulty

Intermediate

A small team, or one strong student willing to learn something new.
Effort

2 semesters · 2 students

Deliverables

6 to ship

3 optional extras

Suggested stack

PythonLLM API or small open modelsqlglotPostgreSQLDuckDBFastAPIReact
A suggestion, not a requirement. Swap anything for what you already know.

What you should ship

  • A realistic point-of-sale schema (products, sales, customers, suppliers, returns, stock) seeded with at least a year of synthetic data that follows real seasonal patterns.
  • A test set of at least 200 questions collected from real shop owners, each with a verified SQL answer and expected result.
  • Schema-aware prompting with retrieval of relevant tables, column descriptions and example queries.
  • A SQL validator that parses every query, allows only SELECT on approved tables and columns, enforces row limits and timeouts, and rejects anything else.
  • A self-check loop that runs the query on a sample, catches errors or empty results, and retries once with the error message.
  • A chat interface that shows the answer, a small chart where useful, and the SQL behind it for users who want to see it.

If you have time left

  • Clarifying questions when a question is ambiguous ("last month" versus "the last 30 days").
  • A comparison between a hosted LLM and a fine-tuned small open model on the same test set.
  • Connectors for two common local POS exports (CSV or Excel) mapped to the schema.

The problem owners have with their own data

A pharmacy, grocery shop or small clothing store with a point-of-sale system has years of useful data and almost no way to ask it anything. The owner wants to know which items sold less this Ramadan than last, which supplier's products get returned most, or how much stock of a slow item is sitting on shelves. The POS offers a few fixed reports. Anything else means exporting to Excel and asking a nephew.

Language models can write SQL, which suggests a simple answer. The danger is that a confident wrong number is worse than no number, and a model with database access must never be able to change or leak data.

What already exists and the gap

  • Vanna is an open-source text-to-SQL framework that trains on your schema and example queries and can run with local models.
  • Power BI Q&A and ThoughtSpot offer natural language questions over business data, priced and designed for companies with analysts.
  • Research benchmarks such as BIRD (95 real databases across 37 domains) and Spider show that accuracy drops sharply on messy real schemas and domain knowledge.

The gap is a small-business assistant evaluated on the questions owners actually ask, with strict read-only guarantees and an honest accuracy number.

What you build

A question answering service in front of a read-only database. The user types a question. The system retrieves relevant tables, column descriptions and a few similar example questions with their SQL, then asks the model for a query. A validator built on a SQL parser (sqlglot) checks the query: SELECT only, approved tables only, no system catalogues, a forced LIMIT. The query runs under a read-only database role with a statement timeout. If it errors or returns something implausible, the system retries once with the error. The answer is shown as a number, a table or a small chart, with the SQL available on request.

The technically interesting part

Building a test set that reflects reality, and measuring against it. Public benchmarks tell you little about a pharmacy owner's questions. Interview at least five owners, collect 200 questions in their own words (including Roman Urdu phrasing), write and verify the SQL for each, and measure execution accuracy: does the returned result match the verified result? Then run ablations: without retrieval, without examples, without the retry loop, with a smaller model. Show your evaluator which component contributes how much. A second strong result is a security test: a list of attempts to write, drop, read other tables or inject instructions through data values, all of which the validator must reject.

How to evaluate it

  • Execution accuracy on your 200-question set, broken down by question type (totals, comparisons, time ranges, rankings).
  • Ablation table for retrieval, examples, retry and model size.
  • Security suite: every malicious or out-of-scope query rejected, with zero false passes.
  • Median latency and cost per question.
  • A usability session with at least five owners or shop managers answering ten of their own questions with the tool.

Suggested plan

  1. Month 1: owner interviews, schema design, synthetic data generator.
  2. Month 2: question collection and verified SQL answers.
  3. Month 3: baseline prompting, retrieval of schema and examples.
  4. Month 4: validator, read-only role, retry loop, security suite.
  5. Month 5: chat interface and charts, ablation runs.
  6. Month 6: usability sessions and write-up.

Path to a product

The natural first customer is a local POS software vendor that wants an "ask your data" feature without building it; license the engine to them per installation. The alternative is a direct subscription for shops that upload a nightly CSV export. Adding a new POS means writing a schema mapping and a handful of examples, which you can document as a repeatable onboarding step.

Scope warning

Do not allow any write access, ever, and do not connect to live production databases during the project. Do not build a general BI tool with dashboards and drag-and-drop charts. Do not report accuracy only on public benchmarks; your own owner question set is the point.

Learn what you need on Sefism

Get help with this idea

Your test set and security suite are where most of the credit lies. A mentor can review your question collection plan and validator rules before you build the interface. Bring your draft scope, the dataset you plan to use and your evaluation table, and we can look at them together. The build is yours; the guidance is on scope, stack and how to measure it. If you are new here, create a free account first.

Ideas and guidance, not finished projects

These are project ideas and scoping guidance, published free for students to use as a starting point. I do not build, write, or sell final-year projects, and I do not complete coursework for anyone. Take an idea, make it yours, and build it.

Not sure this one fits you?

I help students pick an idea that matches their skills and their deadline, then scope it down until it is finishable. Guidance only, never done for you.

Discuss this idea
Work with me

Stuck on something specific?

Writing only gets you so far. If you want an answer to your situation rather than the general case, book a session and we will work through it together. Sessions are free for approved Sefism members, and a few slots open each week.

Book a free sessionSee all services

Follow along

New writing, resources and project ideas land here first.