NL-to-SQL Engine

A natural-language-to-SQL analytics engine. Users ask questions in plain English ("top 10 vendors by invoice amount in 2025"); the engine generates a read-only SQL query against a validated database schema, executes it, and returns a conversational answer plus a structured presentation payload (narrative, tables, charts) for UI rendering.

The backend is a FastAPI service; a React (Vite + TypeScript) single-page chat UI is bundled as a proof-of-concept client. Production integrations should call the API directly.

Key Features

• Natural language → SQL using an LLM (Azure OpenAI or OpenAI), grounded in a curated schema catalog, business glossary, and known column value domains so the model does not invent tables, columns, or filter literals.
• Multi-module schema routing. Every question is routed to exactly one business module — Procure-to-Pay (p2p), Travel & Expense (te), or Order-to-Cash (o2c) — each with its own table/column allowlist and join rules. An explicit module in the request wins; otherwise a semantic classifier decides, with P2P as the fallback.
• Fail-closed SQL validation. Generated SQL must pass a two-tier validator (sqlglot AST analysis when available, hardened regex otherwise) that enforces: a single read-only SELECT, no DDL/DML, no system catalogs, and a per-module table + column lock. Modules are mutually isolated — a P2P query can never touch T&E or O2C tables.
• Answer grounding guard. Numbers in the generated answer are checked against the actual result rows; ungrounded figures are flagged.
• Multi-tenant execution. Requests carrying a row_id are resolved through a master database to the correct tenant database; requests without one use a configured default connection.
• JWT-secured API with an OAuth2-style client-credentials token endpoint for machine-to-machine callers.
• Conversation persistence (optional, separate chat database via SQLAlchemy
  • Alembic) and CSV export of full result sets via signed export tokens.

Architecture

                            ┌─────────────────────────────────────────────┐
 React SPA / API client     │              FastAPI backend                │
 ──────────────────────►    │                                             │
  POST /api/chat            │  1. Auth (JWT)                              │
  Authorization: Bearer     │  2. Tenant resolution (row_id → tenant DB)  │
                            │  3. Module routing (p2p / te / o2c)         │
                            │  4. SQL generation (LLM + schema prompt)    │
                            │  5. SQL validation (allowlist, read-only)   │
                            │  6. Execution (pyodbc, read-only, capped)   │
                            │  7. Answer synthesis + grounding check      │
                            │  8. Presentation payload (table/chart)      │
                            └─────────────────────────────────────────────┘
                                              │
                                              ▼
                                SQL Server / Azure SQL (read-only)

Repository layout

• backend/ — FastAPI app.
  • app/routes/ — chat.py (POST /api/chat), token.py (client-credentials JWT), client_config.py, health.py.
  • app/text_to_sql.py, app/hybrid_chat.py — NL→SQL pipeline and optional hybrid SQL/RAG answering.
  • app/schema/ — one submodule per business domain (schema_p2p.py, schema_te.py, schema_o2c.py) plus shared prompt/allowlist loaders.
  • app/sql_validate.py — fail-closed SQL safety validation.
  • app/data/ — schema column allowlists and categorical value hints used for prompt grounding. (Optional curated data dictionaries and an analytics pattern catalog can be dropped in here to enrich prompts; the loaders degrade gracefully when they are absent.)
  • app/models/ + alembic/ — chat persistence ORM and migrations.
  • tests/ — unit, adversarial-validation, and eval test suites.
• frontend/ — React 18 + Vite + TypeScript PoC chat UI (vitest + Playwright).
• infra/ — Azure Container Apps / ACR Bicep templates for azd deployment.
• .github/workflows/ — CI quality gates and a manual chat-DB migration workflow.

Prerequisites

• Python 3.12+ (developed on 3.14)
• Node.js 20+ and npm (frontend)
• Microsoft ODBC Driver 18 for SQL Server
• A SQL Server / Azure SQL database containing the analytics schema, with a read-only login
• An OpenAI or Azure OpenAI API key

Installation

git clone https://github.com/<your-account>/NL-to-SQL-Engine.git
cd NL-to-SQL-Engine

# Backend
python -m venv env
env\Scripts\activate            # Windows (source env/bin/activate on Linux/macOS)
cd backend
pip install -r requirements.txt

# Frontend (optional PoC UI)
cd ../frontend
npm install

Configuration

Copy the template and fill in your values (never commit the result):

cd backend
cp .env.example .env

Minimum required settings:

Variable	Purpose
DATABASE_URL	ODBC connection string for the default (single-tenant) analytics DB. Use a read-only user.
OPENAI_API_KEY	OpenAI or Azure OpenAI key.
AZURE_OPENAI_ENDPOINT / AZURE_OPENAI_DEPLOYMENT	Required when USE_AZURE_OPENAI=true.
JWT_SECRET	HS256 secret for API token validation (or JWT_PUBLIC_KEY_PATH for RS256).

Common optional settings:

Variable	Purpose
MASTER_DATABASE_URL	Enables multi-tenant routing: row_id in a request is resolved to a tenant connection string.
CHAT_DATABASE_URL	Enables server-side conversation persistence (run alembic upgrade head once configured).
API_CLIENT_ID / API_CLIENT_SECRET	Enables POST /api/auth/token for machine-to-machine callers.
MAX_ROWS, QUERY_TIMEOUT_SECONDS	Result-size and execution-time caps.
EXPOSE_SQL	When true, responses include the generated SQL (debugging only).
CORS_ALLOW_ORIGINS	Browser origin allowlist for the PoC UI.

Settings are cached at startup — restart the backend after any .env change.

Running

# Backend (from backend/)
python -m uvicorn app.main:app --reload      # http://localhost:8000, health at /health

# Frontend (from frontend/)
npm run dev                                  # http://localhost:5173, proxies /api → :8000

A Dockerfile is included that serves the built frontend and API from a single container, and infra/ + azure.yaml support azd up deployment to Azure Container Apps.

Usage

1. Obtain a token (machine-to-machine)

POST /api/auth/token
Content-Type: application/x-www-form-urlencoded

grant_type=client_credentials&client_id=<id>&client_secret=<secret>

2. Ask a question

POST /api/chat
Authorization: Bearer <access_token>
Content-Type: application/json

{
  "message": "top 10 vendors by total invoice amount in 2025",
  "user_id": "analyst-42",
  "module": "p2p",
  "conversation_id": "optional-existing-conversation-id"
}

• module is optional (p2p, te, o2c); omit it to let the semantic router decide. Common spellings such as procure-to-pay, t&e, or order-to-cash are accepted.
• row_id (optional) selects a tenant database via the master DB lookup.

The response contains a natural-language answer, a structured presentation payload (narrative / table / chart blocks), and a conversation_id for follow-up questions. Raw rows are not returned inline; table blocks may include an export token usable with POST /api/export/csv.

Example questions the engine handles:

• "How many vendors do we have per country?"
• "Show monthly expense report totals for 2025 as a bar chart"
• "List overdue receivables by customer, highest first"

Testing

# Backend (from backend/) — unittest, no live DB or LLM required
python -m unittest discover -s tests

# Frontend (from frontend/)
npm test          # vitest unit tests
npm run e2e       # Playwright end-to-end (requires running app)

The backend suite includes adversarial SQL-validation tests (injection attempts, cross-module access, system-catalog probing, stacked statements) and pipeline tests with mocked LLM/DB layers.

Security Model

• Database access is read-only by design: every generated statement must pass the allowlist validator before execution, and the DB login should also be read-only as defense in depth.
• Table and column allowlists are generated from the live schema (backend/scripts/regen_*_columns.py) and loaded at startup — restart after regenerating.
• All /api/* routes (except /health) require a Bearer JWT.
• Secrets live only in backend/.env (gitignored); backend/.env.example documents every setting with placeholders.

Limitations

• SQL Server / Azure SQL only — execution uses pyodbc with the ODBC Driver 18 dialect; other databases would need new execution and validation wiring.
• Schema-bound — the engine only answers questions over the allowlisted analytics tables; arbitrary databases require regenerating allowlists and curating the schema glossary/value hints.
• LLM-dependent — SQL quality depends on the configured model. Validation guarantees safety (read-only, allowlisted), not correctness of intent; the grounding guard flags, but does not block, unverified answer numbers.
• O2C module caveats — join rules were derived from ETL stored procedures rather than declared PK/FK constraints.
• Export tokens are in-process — CSV export tokens are held in memory, so multi-replica deployments need sticky routing or a shared token store (keep maxReplicas=1 until then).
• The bundled frontend is a proof of concept, not a production client.

Provenance

This repository is a sanitized extraction from a private production codebase: all client/company names, credentials, proprietary business-rule catalogs, and data dictionaries have been removed or genericized, and it is published here as a standalone portfolio artifact under the MIT license below.

License

MIT — see LICENSE.