A full-stack Natural Language to SQL query engine built for the Tecnots AI Engineer assessment. Ask questions about an e-commerce database in plain English — the app generates the SQL, shows it to you transparently before executing, and returns live results.
- Python 3.10+
- Node.js 18+
- An Anthropic API key
cd backend
# Install Python dependencies
pip install -r requirements.txt
# Create your .env file from the example and add your key
copy .env.example .env
# Edit .env and set: ANTHROPIC_API_KEY=sk-ant-api03-...
# Seed the database (creates ecommerce.db with 50+ rows per table)
python seed.py
# Start the API server
uvicorn main:app --reload --port 8000API available at http://localhost:8000
Interactive docs at http://localhost:8000/docs
cd frontend
# Install dependencies
npm install
# Start the dev server
npm run devApp available at http://localhost:5173
Both servers must run simultaneously. The Vite dev proxy forwards all
/apicalls tolocalhost:8000, so no browser CORS issues.
| Layer | Technology | Why |
|---|---|---|
| Backend framework | FastAPI | Async-native Python, auto-generates OpenAPI docs, Pydantic validation built in — fastest path to a clean, self-documenting API |
| Database | SQLite (file-based) | Zero infrastructure — one .db file, full relational SQL support, ACID-compliant, perfect for a self-contained assessment demo |
| DB driver | Python sqlite3 stdlib |
No ORM needed for a read-only query engine; direct driver gives full control and clean error messages |
| Fake data | Faker | Realistic, locale-aware fake data across all 4 tables in ~50 lines of Python |
| LLM | Claude Sonnet 4.6 (Anthropic) | Best-in-class instruction-following makes the JSON schema prompt reliable; native refusal behaviour handles mutation requests at the model level |
| Frontend framework | React + Vite | Instant HMR speeds iteration; React state maps cleanly onto query → SQL → results → history lifecycle |
| Styling | Tailwind CSS v4 | Utility-first removes the need for a separate design system; the @tailwindcss/vite plugin makes setup a one-liner |
customers
id INTEGER PK
name TEXT
email TEXT UNIQUE
city TEXT
signup_date TEXT (ISO-8601 date)
products
id INTEGER PK
name TEXT
category TEXT (Electronics, Clothing, Books, Home & Kitchen, Sports, Beauty)
price REAL
stock_quantity INTEGER
orders
id INTEGER PK
customer_id INTEGER FK → customers.id
order_date TEXT (ISO-8601 date)
status TEXT (pending / shipped / delivered / cancelled)
order_items
id INTEGER PK
order_id INTEGER FK → orders.id
product_id INTEGER FK → products.id
quantity INTEGER
unit_price REAL (snapshot — see trade-offs)
customers ──< orders ──< order_items >── products
1:many 1:many many:1
- One customer can place many orders
- One order contains one or more order_items
- Each order_item references exactly one product
Seeded with: 50 customers · 60 products · 80 orders · 248 order_items
User input (natural language)
│
▼
[1] Input validation 400 if < 5 characters
│
▼
[2] Schema introspection Live DDL-style string built from sqlite_master
│
▼
[3] Claude LLM call Schema-grounded prompt → JSON {sql, reasoning}
│ Model self-declines mutations → sql: null → 400
▼
[4] safety.py backstop BLOCKED_KEYWORDS scan + must-start-with-SELECT
│ → 403 if any check fails
▼
[5] Read-only SQLite URI ?mode=ro — DB rejects writes at driver level
│ OperationalError (bad table/column) → 422
▼
[6] Response {sql, reasoning, columns, rows, row_count}
0 rows → 200 OK, not an error
SQLite is used for zero-setup speed and self-containment. In production with multiple concurrent writers, PostgreSQL with connection pooling (e.g. asyncpg + PgBouncer) would be more appropriate. SQLite's read concurrency is fine for this read-only query engine.
The system uses two independent layers that each independently prevent mutations:
Layer 1 — Model self-decline (llm.py + prompt engineering)
The system prompt instructs Claude to return "sql": null for any mutation request. When the model complies, main.py returns a 400 before ever reaching the safety checker. This was verified in testing — "delete all orders" caused the model to return sql: null with a clear explanation.
Layer 2 — safety.py keyword scanner (independent backstop)
The is_safe_sql() function in safety.py scans the raw SQL string for BLOCKED_KEYWORDS = ["INSERT", "UPDATE", "DELETE", "DROP", "ALTER", "TRUNCATE", "CREATE"] using whole-word regex, strips string literals first to avoid false positives (e.g. WHERE name LIKE 'DROP%' is correctly allowed), checks for multi-statement injection (; + more SQL), and requires the statement to begin with SELECT.
This backstop was explicitly verified: the model was temporarily forced to return {"sql": "DROP TABLE customers", ...} bypassing its own refusal. The safety scanner caught it with a 403 Forbidden — the table was not dropped, and the server did not crash. The read-only SQLite URI (?mode=ro) provides a third layer beneath both.
database.py opens SQLite as file:path?mode=ro via URI syntax. Even if both application-layer safety checks were somehow bypassed, the SQLite C library itself would reject any write operation at the driver level.
order_items.unit_price stores the price at the time of purchase, not a live foreign key to products.price. This is standard e-commerce data modelling — if a product's price changes later, historical order revenue calculations must remain correct. This is intentional, not an oversight.
Query history is stored in React component state and lost on page refresh. Given the assessment's time constraint, localStorage persistence or a backend /api/history endpoint (backed by a query_log table) were deferred. The architecture makes either trivial to add.
Identical natural language queries call the LLM every time. A production system would hash the NL input, cache (sql, result) with a TTL, and invalidate on schema change. This was noted as a potential optimisation but not implemented within the time budget.
For databases with 100+ tables, the full DDL prompt could approach LLM context limits. The production approach would embed table descriptions and use vector search to retrieve only the most relevant schema subset — lightweight RAG over schema. For 4 tables, the full schema fits comfortably in the prompt.
Built using Google Antigravity as an agentic IDE. The assistant was used to:
- Scaffold boilerplate: FastAPI structure, Faker seed script, React component shells
- Generate the initial safety checker, which was then reviewed and hardened
Architectural decisions made independently before directing the agent:
- File separation (safety.py, query_runner.py, schema.py — each with one responsibility)
- The string-literal stripping in
is_safe_sql()to prevent false positives - The read-only SQLite URI pattern
- The
unit_pricesnapshot design decision - The two-layer safety architecture and the backstop verification methodology
All generated code was reviewed before execution. The safety checker was explicitly stress-tested by forcing a bypass.
list all customers
show all orders placed in the last 30 days
how many products are out of stock
find the top 5 customers by total spend
show total revenue by product category
which customers have never placed an order?
list all delivered orders from 2025
what is the average order value?
show products with less than 50 units in stock
which product categories generate the most revenue?