Shamratha G

All Projects
GenAI + Data

Text2SQL — NL SQL with Guardrails

A production-minded text-to-SQL system: ask questions in plain English against a real analytical database, and the LLM-generated SQL passes through a deterministic AST guardrail layer, a read-only sandbox, and a multi-signal validation pipeline before results reach you. Every answer carries a confidence score with a full breakdown, and the whole thing ships with an 82-case eval suite reporting exact Clopper–Pearson confidence intervals.

Python FastAPI DuckDB sqlglot Groq (Llama 3.3 70B) Pydantic
Source Code Live Demo

Problem Statement

  • Text-to-SQL demos are easy; production text-to-SQL fails in two ways that matter.
  • Destructive queries: an LLM asked to 'clean up test data' will happily write DELETE or DROP.
  • Confidently wrong answers: SQL that parses and runs but answers a different question than the one asked.
  • Prompt injection phrased as innocent questions can push the model into emitting dangerous SQL.
  • Most projects assert safety; almost none measure it with a reproducible eval.

System Overview

NL question→Schema-aware LLM generation→AST guardrails→Read-only DuckDB sandbox→Validation & confidence scoring→Dashboard
  • Schema-aware prompting auto-introspects tables, types, foreign keys, and sample values; the LLM layer is provider-abstracted (Groq Llama 3.3 70B or Claude) with schema-validated structured output and ambiguity detection that returns clarification requests.
  • Deterministic guardrail middleware parses every query to a sqlglot AST: single-statement only, SELECT-whitelist, blocks DDL/DML (including CTE-hidden writes), admin/exfiltration/introspection functions, deep nesting, and injects or clamps LIMIT — 25/25 dangerous queries blocked in eval.
  • Second defense layer executes on a read-only DuckDB connection with a wall-clock timeout and captured EXPLAIN plans, so even a guardrail bypass cannot write.
  • Hallucination detection blends five signals — back-translation alignment, multi-query agreement, model self-confidence, result sanity, schema coverage — into a weighted confidence score with a full breakdown in the UI.
  • 82-case eval suite (golden SQL, adversarial SQL, NL prompt injection, ambiguity) reports exact Clopper–Pearson confidence intervals; 96% execution accuracy on evaluated golden cases.
  • FastAPI service with a self-contained HTML/JS dashboard and a feedback flywheel: thumbs-up answers become few-shot examples, thumbs-down become eval candidates — tested end-to-end.

What I Built

  • Full pipeline: schema-aware prompting (auto-introspected tables, types, FKs, sample values) → LLM → guardrails → sandbox → validation → confidence score.
  • SQL-AST guardrail middleware (sqlglot): SELECT-only whitelist, multi-statement smuggling blocks, CTE-hidden-write detection, admin/exfil/info-disclosure blocks, nesting-depth limits, LIMIT inject/clamp.
  • Sandboxed execution: read-only DuckDB connection (writes impossible at engine level), wall-clock timeout, EXPLAIN captured for auditability.
  • Hallucination detection: back-translation alignment, multi-query agreement (independent second SQL strategy), result sanity checks, and schema-coverage verification blended into a weighted confidence score.
  • Ambiguity handling: structured clarification requests with multiple interpretations instead of guessing.
  • 82-case eval suite: 25/25 dangerous/adversarial SQL blocked (100%, CI [86%, 100%]), 5/5 NL prompt-injection attacks defended, 26/27 execution accuracy (96%) on golden SQL with Llama 3.3 70B.
  • Feedback flywheel: thumbs-up answers become few-shot examples, thumbs-down become eval candidates — round-trip tested end-to-end.
  • 62 unit tests with 88–96% coverage on the safety-critical modules (guardrails, sandbox, confidence math); deployed live on Render with self-seeding startup.

Key Decisions & Tradeoffs

  • Two independent defense layers: even if the AST guardrail were bypassed, the read-only database connection makes writes impossible — the guardrail is the guarantee, the LLM layer is best-effort.
  • Parse to an AST instead of string matching, so obfuscation (DrOp TaBlE, inline comments, statements hidden after --) is caught at the syntax-tree level.
  • Provider-abstracted LLM layer (Groq free tier or Claude, auto-detected from .env) with JSON-mode structured output plus repair retry.
  • Report eval results with exact Clopper–Pearson 95% CIs and state unmeasured cases explicitly, rather than hiding small-N uncertainty.
  • DuckDB embedded engine: real SQL with EXPLAIN, zero external database to provision, deploys as a single web service.

Why It Matters

It treats LLM-generated SQL as untrusted input and proves the safety claim with a reproducible, offline eval — 0 unsafe queries executed across all 82 cases, including 3 where the model itself complied with an injection and the guardrail caught it.

What I'd Improve Next

  • Complete the eval run: the remaining 13 golden and 3 open-ended cases have never been executed due to free-tier token caps.
  • Calibrate the 0.6 low-confidence threshold against labelled correct/incorrect results, sweeping for the best F1 or required recall instead of the current hand-picked default.
  • Strengthen multi-query agreement by generating the second query with a different model (or a human-authored reference) so shared semantic misunderstandings no longer inflate confidence.
  • Re-run clarification and injection evals on the primary 70B model so all reported numbers reflect the same system.
  • Broaden database support beyond DuckDB (e.g. Postgres) while keeping the AST guardrail and read-only sandbox guarantees.