NL2SQL Guardrails

A natural-language-to-SQL platform that turns plain-English questions into safe, read-only SQL, grounded in a retrieved schema/example knowledge base rather than the LLM guessing table and column names — and validated through multiple independent safety layers before anything ever touches a database.

Ships with a self-contained demo domain (customers/orders/products) on SQLite, so it runs end-to-end without any external database server.

Why this architecture?

Decision	Rationale
RAG-grounded schema + example retrieval	The LLM never invents table/column names from scratch — it's given the most relevant table descriptions and similar past question→SQL examples, retrieved by embedding similarity, to ground generation in the real schema.
In-memory embedding retriever (no external vector DB)	For a schema catalog of this size, an in-process cosine-similarity index is simpler to run and deploy than standing up a vector database — a deliberate build-vs-buy tradeoff based on scale.
Multi-layer SQL safety validation	No single check is trusted alone: (1) an allow-list requires the statement to start with SELECT/WITH and rejects any write/DDL keyword, (2) a statement-stacking check rejects chained statements (; DROP TABLE ...), (3) regex heuristics catch classic injection patterns (comment-based termination, OR 1=1, etc.), (4) an LLM arbiter gives a second opinion only on the ambiguous remainder (e.g. queries touching schema/system metadata tables).
Fail-closed by default	If any deterministic layer rejects a query, generation stops immediately — the LLM arbiter is only consulted for genuinely ambiguous cases, never used to override a deterministic rejection.
Query caching	Identical questions against the same datasource/dialect are served from cache (in-memory by default, Redis-backed when configured) instead of re-invoking the LLM.
Per-datasource semaphores	Concurrent requests against the same datasource are throttled via an asyncio.Semaphore per datasource name, protecting a shared database from a burst of concurrent LLM-driven queries.
Read-only execution	The demo executor opens SQLite in mode=ro, enforcing read-only access at the driver level as defense-in-depth beyond the SQL-level safety checks.

Architecture

flowchart LR
    Q[Natural-language question] --> Cache{Cached?}
    Cache -->|yes| Resp[Return cached response]
    Cache -->|no| Retrieve["Retrieve schema + example context
in-memory embedding similarity"]
    Retrieve --> Gen["Generate SQL
LLM, structured output"]
    Gen --> L1["Layer 1: allow-list
SELECT/WITH only, no write/DDL"]
    L1 -->|reject| Reject[Return rejection + reason]
    L1 -->|pass| L2["Layer 2: statement stacking check"]
    L2 -->|reject| Reject
    L2 -->|pass| L3["Layer 3: injection heuristics"]
    L3 -->|reject| Reject
    L3 -->|pass| Ambiguous{Touches schema/system tables?}
    Ambiguous -->|yes| L4["Layer 4: LLM arbiter"]
    Ambiguous -->|no| Exec
    L4 -->|unsafe| Reject
    L4 -->|safe| Exec["Execute read-only
per-datasource semaphore"]
    Exec --> Resp2[Return rows + cache result]

Loading

Project structure

app/
  catalog/
    retriever.py           In-memory cosine-similarity retriever over the schema catalog
    schema_catalog.py       Loads tables + example question/SQL pairs from YAML
  generation/
    embeddings.py            Question/catalog embedding client
    prompts.py                 Dialect-aware prompt construction
    sql_generator.py            Structured-output LLM SQL generation
  safety/
    validators.py              Deterministic allow-list, anti-stacking, injection heuristics
    llm_arbiter.py               LLM second opinion for ambiguous cases
  caching/cache.py             In-memory / Redis-backed query cache
  concurrency/semaphores.py    Per-datasource concurrency limiting
  execution/sqlite_executor.py  Read-only SQLite execution
  pipeline.py                    Orchestrates retrieval → generation → safety → cache → execution
  api/routes.py                  FastAPI endpoint (POST /api/query)
data/
  schema_catalog.yaml           Sample schema descriptions + NL→SQL examples
  seed_demo.sql                   Demo database schema + sample rows
scripts/init_demo_db.py          Builds the demo SQLite database from seed_demo.sql
tests/                             Unit tests for validators, retriever, cache, semaphores

Getting started

1. Configure environment

cp .env.example .env
# fill in OPENAI_API_KEY

2. Build the demo database

pip install -r requirements.txt
python -m scripts.init_demo_db

3. Run the API

uvicorn app.main:app --reload

curl -X POST http://localhost:8000/api/query \
  -H "Content-Type: application/json" \
  -d '{"question": "What is the total revenue by product category?", "datasource": "demo", "dialect": "sqlite"}'

Running tests

pytest

Tests cover the pure logic — SQL safety validators, the in-memory retriever's ranking, cache TTL behaviour, and the semaphore registry — without requiring API keys or a live database.

Tech stack

Python · FastAPI · LangChain (structured output) · OpenAI · NumPy (retrieval) · SQLite · Redis (optional caching) · Pydantic

License

MIT — see LICENSE.