Agentic Text-to-SQL

A reference implementation of an agentic conversational analytics layer that turns any enterprise database into a self-service surface — so analysts, field sales reps, finance controllers, operations managers, and executives can ask business questions in plain language (any language) and get back grounded, auditable answers. No SQL skills required. No reporting backlog. No hallucinated numbers.

Every question is grounded in the live database schema. Every generated query is parsed, validated, and executed inside a strict read-only sandbox. Every step is captured in a verifiable trace, so analysts, auditors, and platform owners can always see what the agent did and why.

Problem Statement

Enterprise knowledge is often locked inside relational databases that are difficult to consume directly with LLMs:

• Schema sprawl. Production databases routinely contain hundreds of tables and thousands of columns, with cryptic naming conventions and business logic hidden inside joins. Dumping a full schema into the prompt is neither practical nor cost-effective.
• Ambiguous business semantics. The same term — active customer, revenue, churn — often means different things across teams, tables, and time periods. A model that misinterprets the semantics will silently return a confidently wrong answer.
• Hallucination and correctness risk. An ungrounded LLM can invent columns, fabricate values, or generate SQL that runs successfully but answers the wrong question — eroding trust the moment a business user notices the discrepancy.
• Security and blast radius. A naive text-to-SQL implementation exposes the database to injection, accidental mutations, runaway queries, and uncontrolled access to sensitive columns. Production data must never be one bad prompt away from being modified or exfiltrated.

This PoC explores how to safely enable “talk to your data” scenarios while preserving query correctness, schema grounding, traceability, and strictly controlled model behaviour.

Where it matters

Text-to-SQL is rarely “one use case.” In practice the same conversational layer is consumed by every function that lives downstream of a database:

Function	A question a real user would ask	Business value
Sales & field operations	“Which of my customers in Istanbul placed an order above ₺50K in the last 30 days?”	Reps prepare for client visits in seconds instead of waiting overnight for an analyst.
Finance & FP&A	“What is the gross margin trend on the Enterprise tier this year?”	Controllers self-serve ad-hoc questions without raising a BI ticket.
Customer success & retention	“List all accounts at risk of churn with open support tickets older than 7 days.”	Proactive retention plays before the renewal call — not after.
Marketing & growth	“Top 5 acquisition channels by 90-day customer lifetime value.”	Campaign optimisation grounded in actuals, not in gut feel.
Operations & supply chain	“Which SKUs are below 14-day cover in the Ankara warehouse?”	Floor managers act on live data instead of yesterday’s report.
Executive & board reporting	“Compare Q3 revenue by region versus plan.”	The same conversational layer serves the headline KPI and the drill-down behind it.
Internal data & analytics teams	“Write the query for me, then I’ll tune it.”	Analysts use the agent as a copilot for fast first drafts on unfamiliar schemas.

Across every persona the value proposition is identical: remove the SQL bottleneck between business users and their data, without giving up the correctness, auditability, and security guarantees that an enterprise database requires.

Demo

Empty state	Answer + chart + SQL + trace

Architecture

The diagram below is the target production architecture. The repo today ships the core agent loop, the SQL validator, and the read-only sandbox. Schema RAG, semantic authorization, PII filtering, caching, and the LLM-as-Judge evaluator are tracked in the Roadmap.

Same diagram as Mermaid (GitHub-native, editable)

flowchart LR
    User([Corporate User]) --> FE[Front-End<br/>React]
    FE --> Auth

    subgraph BE["API Gateway / Backend (FastAPI)"]
        direction TB
        Auth[Auth & Gateway] --> SemAuth[Semantic<br/>Authorization]
        SemAuth --> Orch{{AI Orchestrator}}
        Orch <--> PII[PII & Security<br/>Filter]
        Orch <--> Cache[Caching Logic]
        Orch --> Engine[SQL Engine & Validator<br/>Strict Sandbox · Read-Only]
        Engine --> Orch
    end

    Orch -- Schema Lookup --> Search[(Azure AI Search<br/>Metadata Index)]
    Orch -- SQL Gen / Reasoning --> LLM[OpenAI GPT API]
    Engine -- Query Execution --> DB[(Corporate Database<br/>SQL)]
    DB -. Error Feedback /<br/>Self-Healing .-> Orch

    BE --> Obs[Observability & Tracing<br/>Azure Monitor]
    Obs --> Eval[Evaluation<br/>LLM-as-a-Judge]

    classDef sec  fill:#fee,stroke:#c66,color:#511;
    classDef ext  fill:#eef,stroke:#88a,color:#223;
    classDef obs  fill:#ffe,stroke:#cc6,color:#440;
    class PII,Engine sec
    class Search,LLM,DB ext
    class Obs,Eval obs

Loading

This is not a single LLM prompt. It is a tool-using agent that discovers the schema, validates every query against an AST parser, executes against a read-only DB, and self-heals when SQL fails — all while returning a verifiable trace.

Agent loop (one request, current implementation)

sequenceDiagram
    autonumber
    participant U as User
    participant A as Agent (LLM)
    participant V as Validator (sqlglot)
    participant D as SQLite (read-only)

    U->>A: "Top 5 best-selling products by revenue"
    A->>D: list_tables()
    D-->>A: [customers, orders, order_items, products]
    A->>D: get_schema(["products","order_items"])
    D-->>A: columns, types, FKs, sample rows
    A->>V: run_sql("SELECT ...")
    V->>V: parse AST · SELECT-only · single stmt · inject LIMIT
    alt valid
        V->>D: execute (mode=ro)
        D-->>A: rows
    else invalid / runtime error
        V-->>A: error message
        A->>A: self-heal (next turn)
    end
    A->>U: submit_answer(answer, sql, chart, trace)

Loading

Solution approach

Most "text-to-SQL" demos hand the LLM a schema and pray for valid SQL on the first try. Real databases break that assumption — schemas are large, joins are ambiguous, and one wrong column name fails the whole answer.

This project takes the agentic approach instead:

• The model discovers the schema with tools, it isn't dumped into the prompt.
• Every generated query is validated by an AST parser before it touches the DB.
• The DB is opened read-only at the driver level — even a perfect SQL injection cannot mutate state.
• When a query fails, the error is fed back and the agent tries again.
• A trace of every tool call is returned so you can audit what the agent did.

Code layout

Layer	Responsibility	File
API	/ask, /schema, /health + static UI	src/main.py
Agent	OpenAI tool-use loop, self-heal, trace	src/agent.py
Validator	sqlglot — SELECT-only, single-statement, LIMIT injection	src/validator.py
DB	SQLite in read-only URI mode + schema introspection	src/db.py
Seed	Sample sales schema (4 tables, ~150 rows)	src/seed.py
UI	Single-file browser UI	static/index.html

Defense in depth

1. Read-only at driver level — SQLite opened with mode=ro URI, so even a successful injection cannot write.
2. AST validation — sqlglot parses the SQL; only a single SELECT is allowed. INSERT / UPDATE / DELETE / DDL / PRAGMA / ATTACH are rejected.
3. LIMIT injection — if the model forgets a LIMIT, one is added automatically (DoS protection).
4. Row cap to model — only the first N rows are returned to the LLM (token + cost protection).

Project structure

agentic-text-to-sql/
├── src/
│   ├── agent.py        # OpenAI tool-use loop, self-heal, trace
│   ├── config.py       # Settings loaded from .env
│   ├── db.py           # Read-only SQLite + schema introspection
│   ├── main.py         # FastAPI app: /ask, /schema, /health
│   ├── seed.py         # Creates sample sales DB
│   └── validator.py    # sqlglot AST validator + LIMIT injection
├── static/
│   └── index.html      # Single-file browser UI
├── tests/
│   └── test_validator_smoke.py
├── docs/
│   └── screenshots/    # README assets
├── data/               # SQLite file lives here (gitignored)
├── .env.example
├── requirements.txt
└── README.md

Quick start

# 1. Clone & enter
git clone https://github.com/UmitFSD/agentic-text-to-sql.git
cd agentic-text-to-sql

# 2. Create a virtual environment
python -m venv .venv
.\.venv\Scripts\Activate.ps1     # macOS/Linux: source .venv/bin/activate

# 3. Install dependencies
pip install -r requirements.txt

# 4. Configure
copy .env.example .env           # macOS/Linux: cp .env.example .env
# edit .env -> set OPENAI_API_KEY

# 5. Seed the sample database
python -m src.seed

# 6. Run
uvicorn src.main:app --reload --port 8000
# open http://localhost:8000

Try these questions

• How many orders have been delivered in total?
• Top 5 best-selling products by total revenue
• Which city has the most customers?
• Average order value per customer segment
• List all orders from Acme Robotics in the last 90 days
• Hangi kategoride en çok ürün satıldı? (Turkish — works too)

API

POST /ask

POST /ask
Content-Type: application/json

{ "question": "Top 5 best-selling products by total revenue" }

Response:

{
  "answer": "The top 5 products by revenue are ...",
  "sql": "SELECT p.name, SUM(oi.quantity * oi.unit_price) AS revenue ...",
  "reasoning": "Joined order_items with products and summed line totals.",
  "columns": ["name", "revenue"],
  "rows": [["Standing Desk", 12345.67], ...],
  "row_count_total": 5,
  "truncated": false,
  "turns_used": 4,
  "chart": { "type": "bar", "x": "name", "y": "revenue" },
  "trace": [
    {"turn": 1, "tool": "list_tables",   "duration_ms": 1, ...},
    {"turn": 2, "tool": "get_schema",    "duration_ms": 2, ...},
    {"turn": 3, "tool": "run_sql",       "duration_ms": 3, ...},
    {"turn": 4, "tool": "submit_answer", "duration_ms": 0, ...}
  ]
}

GET /schema

Returns the full schema (tables, columns, types, FKs) the agent sees.

GET /health

Returns the loaded model, the discovered tables, and a status flag.

Configuration

All settings live in .env (see .env.example):

Variable	Default	Purpose
OPENAI_API_KEY	—	required
OPENAI_MODEL	gpt-4o-mini	any tool-use capable OpenAI model
DB_PATH	data/sales.db	SQLite file the agent queries
MAX_AGENT_TURNS	8	max self-heal iterations
MAX_ROWS_TO_MODEL	20	rows handed back to the LLM
MAX_QUERY_LIMIT	1000	LIMIT injected when missing

Tests

pytest -q

The smoke tests exercise the SQL validator on a representative attack and shape corpus (DDL, multi-statement, comments, PRAGMA, ATTACH, etc.).

Troubleshooting

Symptom	Likely cause	Fix
OPENAI_API_KEY is missing	.env not loaded	Copy .env.example to .env and set the key.
no such table: ... on /health	DB not seeded	Run python -m src.seed.
unable to open database file	SQLite cannot find the path	Check DB_PATH in .env is relative to where you run uvicorn from.
Agent gives up after N turns	Schema is too large or question is ambiguous	Raise MAX_AGENT_TURNS, or rephrase the question.
422 from /ask	Validator rejected the SQL	Read detail — usually the model wrote a non-SELECT or multiple statements. The agent normally self-heals; a 422 means it could not.

Roadmap

• MVP: tool-use loop, validator, self-heal, read-only DB
• Auto chart hint in API response
• Schema RAG (Chroma) for large schemas where dumping all tables is too costly
• Few-shot example bank — inject similar past Q→SQL pairs into the prompt
• LLM-as-Judge evaluation harness (evals/ folder with 50 seed questions)
• Row-level security: per-user table allowlist
• PII redaction filter on result rows
• Streaming responses (SSE) for long queries
• PostgreSQL backend (currently SQLite only)
• OpenTelemetry traces → any observability backend

License

MIT