Text-to-SQL Interface with Guardrails and Hallucination Detection

Reviewers can inspect SQL, assumptions, schema lineage, policy checks, and a bounded result preview before one single-use execution; unsafe, malformed, identifier-exposing, and hallucinated-schema queries fail closed.

Open project Source

Text-to-SQL Interface with Guardrails and Hallucination Detection project cover

STAKEHOLDER VIEW

What this project is for.

Problem: Natural-language analytics is attractive because it lowers the barrier to data access, but executing model-generated SQL directly creates obvious security, privacy, and correctness risk. This project turns that risky shortcut into a reviewable workflow: the model may propose, a deterministic policy engine must validate, and a person must approve one bounded read-only preview.
Intended user: A data analyst or technical reviewer evaluating governed natural-language access to a classified synthetic payments model.
Decision supported: Whether a proposed analytical query is within the classified schema and safe enough for one reviewed execution.
Outcome: A guarded Text-to-SQL first demo that makes model output inspectable, evidence-linked, and explicitly non-production.
What to try: Choose or enter an analytical question over the disclosed semantic catalog
Important limitation: The current endpoint is anonymous and has no caller authorization.

Generate, review, and approve one bounded query

01Choose or enter an analytical question over the disclosed semantic catalog
02Inspect proposed SQL, assumptions, referenced fields, policy checks, and EXPLAIN summary
03Approve one short-lived token-bound execution or let the system refuse the request
04Review a maximum 100-row preview that excludes identifier-like fields

Technical reviewArchitecture, evidence, controls, deployment, and trade-offs

ARCHITECTURE

One source, one rendered system view.

FastAPI separates proposal from execution. Azure OpenAI receives only an identifier-excluding semantic catalog through Microsoft Entra authentication; SQLGlot checks statement shape, functions, tables, columns, identifier projection, and ranking semantics; DuckDB EXPLAIN and resource limits run before a short-lived approval can be consumed. Execution revalidates both SQL policy and the active snapshot checksum.

System architecture showing an analyst, synthetic data and semantic catalog, FastAPI proposal and approval interfaces, Azure OpenAI, SQLGlot and DuckDB validation, ephemeral approval storage, read-only execution, privacy boundary, telemetry, deployment controls, and evaluation. — Pan to inspect the system; zoom controls are available when detail is needed.

End-to-end flow

01Curated semantic catalog and explicit data classification
02Azure OpenAI SQL proposal generation through Microsoft Entra ID
03Parser-backed table, column, function, statement, and resource policy checks
04Human review with short-lived single-use approval
05Policy and snapshot revalidation before read-only DuckDB execution
06Bounded result preview with privacy-preserving lifecycle events

Technology stack

Microsoft Azure

Azure OpenAIMicrosoft Entra IDAzure Container Apps

PythonFastAPIDuckDBSQLGlotDockerOpenTelemetry

Technology decisions

Decision	Why	Alternative	Trade-off
Azure OpenAI with Microsoft Entra ID	Keeps provider credentials out of the repository and supports managed identity in Azure.	Static API keys	Identity and role configuration add operational setup but remove long-lived application secrets.
SQLGlot plus DuckDB EXPLAIN	Provides parser-backed structural policy and database-level validation before execution.	Regex filtering or direct model execution	The policy intentionally rejects ambiguous or unsupported SQL and still requires semantic human review.
Ephemeral SQLite approval store	Provides five-minute single-use approval persistence for a one-replica anonymous demo.	Managed transactional database	Simple and low-cost, but restart-sensitive and unsuitable for protected multi-replica hosting.

Evaluation and evidence

Metric	Plain-language meaning	Score / value	Dataset / scenario	Threshold / baseline	Interpretation	Evidence
deterministic policy outcomes	What deterministic policy outcomes measures for the stated evaluation scope.	18/18	18 trusted and refused cases over the committed synthetic fixture	No threshold or baseline recorded.	The fixture and case set are intentionally bounded and do not establish open-ended semantic correctness.	evaluation.local-policy
public status-only checks	What public status-only checks measures for the stated evaluation scope.	6/6	Root, evaluation, examples, safe preview, active fixture boundary, and deployed control configuration	No threshold or baseline recorded.	Status-only checks do not establish availability, caller authorization, durable limits, or production readiness.	deployment.anonymous-live-demo, security.demo-fixture-boundary

These are deterministic local policy evaluation plus live status-only integration checks on an anonymous demo results, not a production service-level objective. Unknown values are shown as “Not recorded”; units and special characters retain their source meaning.

Technical terms and value conventions

Term	Plain-language use	How this project uses it
Evaluation score	A measured result from a defined test or benchmark, not a general promise.	Read it with the dataset, scenario, and limitation shown in the evaluation table.
Unknown / null	A value that was not observed or is intentionally not claimed.	Rendered as “Not recorded” rather than silently converted to zero or omitted.
Special characters	Symbols such as %, currency, Unicode, and escaped input retain their source meaning.	Units and encoding are explained where they affect the decision.

Data boundary

Classification	Source	Permitted use	Excluded data
synthetic	Hand-authored synthetic demo fixture inspired by the MoMTSim V2 synthetic mobile-money dataset (DOI 10.17632/zhj366m53p.2)	Portfolio demonstration, deterministic evaluation, and bounded analyst-workflow review	Source initiator and recipient account identifiers; Real customer, cardholder, bank, credential, and personal data; Identifier-like fields in model context and public result previews; The locally generated 4,225,958-row approved DuckDB snapshot

Anonymous, non-production demonstration using synthetic data only. It is not a bank system, fraud decision service, security posture, availability claim, or production-readiness claim.

Security and privacy controls

Control	Implementation	Evidence	Limitation
Parser-backed read-only SQL policy	SQLGlot validates statement shape, functions, tables, columns, identifier projection, and resource boundaries before DuckDB EXPLAIN or execution.	evaluation.local-policy	A syntactically safe query can still be semantically wrong, so human review remains mandatory.
Data and container boundary	Raw source data and approved snapshots are excluded from the image; model context and previews omit identifier-like fields.	security.container-data-boundary, disclosure.synthetic-data	The public fixture remains a small hand-authored synthetic demonstration.
Short-lived single-use approval	Execution requires a five-minute token-bound approval and revalidates SQL policy plus the active snapshot checksum.	integration.entra-proposal-lifecycle	Approval state is ephemeral SQLite and is not durable across restarts or replicas.
Anonymous-demo controls	The public revision exposes rate, proposal-budget, and aggregate status controls.	deployment.anonymous-live-demo, security.demo-fixture-boundary	Controls are process-local and reset after scale-to-zero or restart; there is no caller authorization.
Bounded operational observation	The existing non-production target has a redacted owner notification route, a $30 monthly budget, and aggregate stderr and latency alerts.	operational.m6-alert-readiness	This creates no availability SLO, on-call rota, durable-state guarantee, or production claim.

Deployment and cost boundary

Provider	Runtime	State	Exposure	Verified	Production claim
Azure Container Apps	Scale-to-zero FastAPI container using the committed demo fixture and ephemeral SQLite proposal state	live	anonymous	2026-07-24T04:25:22.107862Z	No

Component	Boundary	Implication
Azure Container Apps	Consumption profile with zero-to-one replicas and no automatic expiry.	The demo can cold start; no availability SLO is claimed.
Azure OpenAI	Proposal-only model call with a per-process budget and a classified semantic catalog.	Local latency and cost observations are not production cost or performance claims.
Proposal and analytics storage	Ephemeral SQLite plus a read-only synthetic fixture in the public demo.	Durable multi-replica operation requires an owner-approved transactional store.

Known limitations

The current endpoint is anonymous and has no caller authorization.
The deployed rate and proposal-budget controls are process-local and reset after scale-to-zero or restart.
Approval state is ephemeral and single-replica.
The model can still propose semantically wrong SQL, so human review remains mandatory.
The anonymous demo has no automatic expiry; the owner remains responsible for ongoing cost and operational review.

Scalability roadmap

Replace ephemeral SQLite with an owner-approved transactional store and prove cross-replica single-use consumption
Enable single-tenant Microsoft Entra authentication and verify unauthenticated denial
Replace process-local rate and proposal budgets with durable distributed controls before multi-replica hosting
Add alerting, budget ownership, availability/error monitoring, and an owner-approved load envelope
Expand semantic evaluation with reviewer-approved equivalent SQL rather than literal query matching

All projects