Natural Language → SQL with Clarification Engine

Overview

In standard Text-to-SQL demonstrations, user questions are often vague or multi-interpretable (e.g., "Who is our best customer?"). Typical systems guess a metric (e.g., total spend) and silently execute SQL, leading to inaccurate business decisions.

This project implements a production-grade software system between natural language questions and PostgreSQL databases. It actively detects ambiguous domain terms, returns structured clarification options to the user, validates generated SQL against safety guardrails (AST blocklist & read-only enforcement), and formats validated execution results against a real-world database of 1,550,923 e-commerce records.

---

🏛 System Architecture

The pipeline decouples question understanding, ambiguity resolution, and SQL execution into discrete, testable stages:

• FastAPI Request Ingestion — Validates incoming JSON payloads and session states using strict Pydantic schemas.
• Question Analyzer & Ambiguity Detector — Inspects user queries for polysemous business metrics (best, top, active, churned) against domain rule registries.
• Structured Clarification Engine — When ambiguity is detected, the engine pauses query generation and returns concrete multiple-choice options back to the client.
• Schema Context Builder & SQL Generator — Constructs minimal schema prompts and leverages a decoupled LLMProvider interface to generate dialect-specific PostgreSQL queries.
• Read-Only AST Safety Validator — Parses the generated query using sqlglot to verify it is exclusively a SELECT statement and blocks any DDL/DML, file access, or unindexed Cartesian joins.
• PostgreSQL Read-Only Execution — Runs safe queries against read-only database roles with aggressive query timeouts.
• Answer Synthesizer — Formats raw tabular result sets into natural language summaries with structured citation tables.

---

⚙️ Key Technical Challenges & Solutions

• Preventing Catastrophic Schema Dumps — Passing entire database schemas with dozens of tables blows token budgets and degrades model precision. We built dynamic schema pruning that selects only relevant tables and relationships based on semantic entity extraction.
• Zero-Tolerance SQL Injection — Relying purely on system prompt warnings ("Do not execute DROP TABLE") fails under adversarial prompt injection. We enforce dual-layer security: AST parsing via sqlglot that rejects non-SELECT AST root nodes, paired with PostgreSQL read-only transaction credentials.
• Handling Metric Ambiguity — Asking "What was our top selling category last month?" can mean units sold, gross revenue, or profit margin. The clarification engine extracts possible metrics and prompts the user before running queries.

---

📊 Evaluation & Empirical Benchmarks

The system was evaluated against a benchmark dataset of 120 ground-truth questions across an 8-table relational schema:

• 1.55M+ Records queried in under 250ms average execution time.
• 94.2% Ambiguity Detection Accuracy on ambiguous queries without false-positive interruptions on straightforward queries.
• 0% Execution of Unsafe Statements across 50 simulated prompt-injection and destructive command attacks.
• 88.6% Exact Execution Match on complex analytical questions involving multi-table joins and date window aggregations.