Natural Language → SQL Analytics Assistant
Ask business data questions in plain English — get safe, validated SQL with grounded explanations.
Built by naman · AI Developer Intern
The system, up close
Real captures from the running system — the demo, screenshots and architecture come first, then the deep dive.
Demo
Ask a business question in plain English and watch it become validated SQL, a result and a grounded explanation.
The demo shows the chat interface converting real questions into validated SQL.
Screenshots
Architecture
Why this needed to be built
Business users often need answers from data but should not (and cannot) write SQL directly. Hand-built dashboards only ever cover predefined questions.
Users want to ask questions in plain language and receive trustworthy, well-explained answers — without ever exposing the database to destructive queries or privileged credentials.
What we built, and how it changes the workflow
We built an AI analytics assistant that converts natural-language business questions into safe, validated SQL, executes it against a read-only relational database, and returns structured results with natural-language explanations.
Under the hood, the system discovers the live schema, selects only the relevant tables and join paths, and asks an LLM to write SQL within that authorized boundary. Every query is parsed to an AST (via sqlglot) and rejected if it is destructive, malformed, multi-statement or touches an unauthorized table.
Validated SQL runs through a dedicated SELECT-only PostgreSQL role with time and row limits, so read-only execution is enforced at the database layer — the LLM is never treated as a security boundary.
Core design principle
- Security by defense in depth: prompt rules + AST-based SQL validation + allowed-schema boundary + a dedicated read-only database role + query limits.
- The LLM is never treated as a security boundary.
What it can do
A focused set of capabilities that define the system — no marketable filler, only what actually runs.
Dynamic schema discovery
Introspects live tables, columns, types, primary keys and foreign keys automatically.
Relevant schema selection
Join-aware selection of the tables and columns that matter for each question.
LLM SQL generation
Provider-agnostic generation limited to the authorized schema and permitted operations.
AST-based SQL validation
sqlglot parses every query and rejects destructive, malformed, multi-statement or unauthorized queries.
Read-only execution
A dedicated SELECT-only PostgreSQL role with configurable timeout and row limits.
Grounded explanations
The LLM summarizes results strictly from the returned data — no invented metrics.
Structured query logging
Every query is logged with errors handled gracefully and sanitized responses.
Analytics chat frontend
Dark/light theme, collapsible sidebar, SQL syntax highlighting, provider/model settings and live processing stages.
The end-to-end workflow
The system processing path, in order.
- 1
Schema discovery
Introspects the live database — tables, columns, types, primary and foreign keys.
- 2
Relevant schema selection
Picks the tables and columns that matter for the question, including join paths.
- 3
SQL generation
The LLM writes SQL limited to the authorized schema and permitted operations.
- 4
Validation
Every query is parsed to an AST and checked for destructive, malformed, multi-statement or unauthorized queries.
- 5
Read-only execution
Validated SQL runs through a dedicated SELECT-only role with time and row limits.
- 6
Result processing
Rows are normalized into a structured, JSON-safe format.
- 7
Explanation
The LLM summarizes results grounded strictly in the returned data.
Stack & tech choices
- Backend
- Python 3.11, FastAPI, Pydantic v2, SQLAlchemy, Uvicorn
- SQL safety
- sqlglot (AST validation), dedicated read-only DB role
- Database
- PostgreSQL 16 (Docker)
- LLM calls
- httpx, OpenRouter (OpenAI-compatible), mock provider for tests
- Frontend
- React 18, TypeScript, Vite, Tailwind CSS, react-syntax-highlighter, lucide-react
- Testing
- pytest — 130 tests, 129 passing, 1 skipped (needs live PostgreSQL)
- Deployment
- Docker, docker-compose, nginx reverse proxy
Tested, verified and honestly scoped
Validation & tests
A 130-test suite verifies success paths, failure handling and security rejections end-to-end (129 passing, 1 skipped without a live database).
- Success — query execution with result verification (e.g. revenue question → SUM(quantity × unit_price) → $8,400,050.00)
- Failure — invalid SQL, unknown tables and execution errors handled cleanly
- Security — DELETE, DROP, UPDATE, INSERT, multi-statement, SELECT INTO and unauthorized tables all rejected
- Sanitization — error messages never leak SQL or table names
- Health endpoint reports live schema tables for quick verification
Security & hardening
- Prompt rules — system instructions plus a strict schema boundary
- AST-based SQL validation (sqlglot) rejects destructive and multi-statement queries
- Allowed-schema boundary — only relevance-selected tables are exposed to the LLM
- Dedicated read-only role — execution runs as a SELECT-only user with no DDL/DML privileges
- Query limits — configurable timeout (default 10s) and max result rows (default 1000)
- No secrets in code — all credentials in environment variables; .env is git-ignored
Honest limitations
Natural Language → SQL Analytics Assistant is an internally developed engineering prototype. We state limitations rather than overclaim production readiness.
- Open-ended natural-language questions require a real LLM API key; the mock provider is deterministic (tests/demos only)
- Explanation accuracy is bounded by the LLM and the returned data
- Single, first-party database instance (one schema namespace)
Where this can go next
Realistic next steps we've scoped — not aspirational marketing copy.
- Query repair loop with bounded retries
- Chart generation (line, bar, pie) when appropriate
- Query history and saved questions
- Role-based access control
- Multi-database and multi-tenant support
- Semantic business metrics layer
Have a similar workflow?
Zenera Labs can build a custom version for your business.
This is one of naman's internally developed systems. Tell us about your requirement and we'll scope a tailored build around it.
Other systems we've built internally
AI Contract Analysis & Risk Detection
Faster document review with traceable evidence — upload a contract and get risk findings with source-verified citations.
naman · Engineering Portfolio
View case study
SentinelAI — Security Log Anomaly Detection
Risk-scored alerts from security events — a full-stack Mini-SIEM with ML detection, attack-chain correlation and explainable alerts.
pragna · Engineering Portfolio
View case study
AI Data Analyst & Business Intelligence Agent
Evidence-grounded business intelligence from spreadsheets — upload CSV/XLSX, get analysis, visualizations and explained answers.
priyanka · Engineering Portfolio
View case study