Read-only text-to-SQL assistant

The problem you will solve

A fictional shop owner wants to ask which products sold most last month. Build an assistant that proposes a query, explains its assumptions and returns a bounded result. Query generation must not grant access: database permissions and deterministic application checks enforce the boundary even when the model follows a malicious instruction.

Before you start

SQL joins and aggregates, parameter binding, database permissions and model-output validation.

Suggested tech stack

• Python
• SQL
• SQLite
• FastAPI
• SQL parser

Data plan

Seed fictional products, orders and refunds in a local database. Write 40 questions with expected result tables, including ambiguous dates, invalid columns and forbidden modification requests. Keep schema and test-data versions fixed.

Hardware and cost

The sandbox database runs on CPU. Use a local model that fits your hardware or a capped hosted API for SQL proposals. Google text-to-SQL material is background; no managed cloud database is required. Never point this demo at a production database.

Define the input and output

Use this illustrative contract as a starting point, then specify required fields, validation and error responses in your own implementation. It is not a complete API schema.

Input: {"question":"Which three products sold most in September 2026?"}
Output: {"sql":"SELECT ...","parameters":[],"assumptions":["Net units after refunds"],"rows":[],"status":"answered"}
Alternative status: needs_clarification / denied

Your five build milestones

Finish and test one milestone before extending the system. Keep an experiment log with the decision, the alternative you rejected and the evidence behind the choice.

1. Specify meaning and access

   Define net sales, timezone, allowed tables and the read-only user. Create a limited query interface; the model cannot choose connection credentials, databases or filesystem paths.

2. Build reference answers

   Write hand-checked SQL and expected results for the test questions. Include empty results and ties so a plausible-looking answer cannot pass without correct execution semantics.

3. Generate structured proposals

   Provide only the permitted schema and request SQL plus bound parameters and assumptions. Ask for clarification when a necessary definition is missing instead of inventing a business rule.

4. Enforce query restrictions

   Parse proposals with a maintained SQL parser, reject multiple statements and non-allowlisted constructs, then execute with read-only permissions, result limits and a timeout. A SELECT keyword check alone is not a security boundary.

5. Demonstrate safe failures

   Show the question, reviewed SQL, bounded result and limitations. Test denied writes, expensive joins and injected instructions, and preserve rejection reasons in a redacted audit log.

How to evaluate your project

Set your acceptance criteria before looking at the final test results. The checks below are proposed experiments, not measured outcomes or a guarantee that a particular model will pass.

• Compare returned result tables with reference answers; syntactically valid SQL is not necessarily correct SQL.
• Require forbidden writes to fail at the database layer as well as application validation; include resource-exhaustion cases.
• Report clarification rate, incorrect-answer rate and execution latency separately with query counts.

Save your test cases, run command, dependency versions and results together. Compare on identical inputs, retain failures in the denominator and report sample sizes. If a change improves one metric but harms another, explain which trade-off fits your use case.

Failure modes worth demonstrating

• Giving a model a privileged database connection or relying on a prompt to block writes.
• Using string substitution instead of bound values, or interpreting an empty result as a system error.

Optional extension

Add a query-cost preview and a review screen that explains joins before the user runs a bounded query.

Turn the build into portfolio evidence

Your repository should let another person understand the problem and reproduce the core evaluation without needing your private credentials.

• A README with problem, intended user, architecture, setup commands and a small input/output example.
• A permitted sample dataset, its origin or generator, a frozen test split and a single evaluation command.
• A results table comparing your baseline with your final approach, including failures and sample counts.
• A short demo or screenshots, a limitations section and one decision you would change with more time.
• Environment-variable names without secret values, a dependency list and attribution/license notices for any reused code or datasets.

Resume framing

Describe this as a personal or educational project. State what you implemented, how you tested it and what your measured results were. Replace placeholders with your own evidence; do not claim employer impact, production traffic or accuracy you did not measure.

Built read-only text-to-sql assistant using Python, SQL, SQLite; evaluated against [baseline] on [N held-out cases], documented [measured result] and [important limitation].

Be ready to explain your decisions

• Why does generating valid SQL not establish safe access?
• How do you evaluate two different queries that should mean the same thing?

Answer with a concrete failure from your implementation, the options you considered, your measured evidence and the limitation that remains.

Google documentation and GitHub references

The project scope, milestones and evaluation plans above are original AIJobFever educational material. These official Google resources offer background or implementation examples; they are not finished implementations of this blueprint. No Google endorsement is implied. Review repository licenses, supported platforms and current costs before reusing code or provisioning services.

• Google Cloud: text-to-SQL techniques ↗