Quickstart · Integration guide · Architecture · Security

A production-oriented text-to-SQL agent with read-only execution controls, business-semantic result validation, and at most one bounded repair attempt.

The repository focuses on one production issue: SQL can be valid and executable while still answering the wrong business question. It therefore reports execution success and result correctness separately.

What it does

Input:

How many support cases were opened by APAC customers?

Output:

• generated SQL;
• query result;
• assumptions made by the generator;
• validation and execution trace;
• number of generation attempts;
• separate execution and semantic evaluation results.

The included example uses synthetic B2B SaaS data. “Customer region” is defined as workspaces.billing_region. A query that instead filters support_cases.handling_region executes successfully, but fails the result-level evaluation.

Architecture

The model and database are adapters. The control flow stays explicit: the model returns a typed decision, deterministic checks reject unsafe or invalid SQL, the database enforces read-only execution, and evaluation distinguishes “the query ran” from “the result is correct.” See the architecture decisions for the boundaries and trade-offs.

Run it

Requirements: Python 3.9+. The default demo has no external dependencies and does not call an LLM.

git clone <repository-url>
cd production-text-to-sql-agent
python -m venv .venv
source .venv/bin/activate
python -m pip install -e .

text-to-sql demo
text-to-sql ask "How many support cases were opened by APAC customers?"
text-to-sql-eval

Run the complete local check without installing the package:

make preflight

Components

Component	Default implementation	Replace it with
SQL generator	DemoGenerator, a deterministic zero-key demo	Any class implementing generate() and repair()
Model API	OpenAICompatibleGenerator	A provider SDK, local model, or internal inference service
Schema context	data/catalog.json	A generated catalog, data dictionary, or schema retrieval result
Query backend	SQLiteBackend	A PostgreSQL, Snowflake, BigQuery, or warehouse-specific backend
Validation	SQLite syntax/name checks plus query restrictions	A dialect-aware parser and organization policy checks
Evaluation set	data/eval_cases.json	Reviewed questions and expected results from your domain
Interface	CLI and Python API	HTTP API, chat UI, notebook, internal tool, or agent tool

The pipeline depends on two small interfaces:

class SQLGenerator:
    def generate(question, context) -> SQLDecision: ...
    def repair(question, context, sql, error) -> SQLDecision: ...

class QueryBackend:
    def validate(sql) -> ValidationResult: ...
    def execute(sql) -> QueryResult: ...

This keeps model integration separate from database-specific validation and execution.

Use it from Python

from text_to_sql.database import SQLiteBackend
from text_to_sql.demo_data import build_database
from text_to_sql.generators import DemoGenerator
from text_to_sql.pipeline import TextToSQLPipeline

pipeline = TextToSQLPipeline(
    generator=DemoGenerator(),
    backend=SQLiteBackend(build_database()),
)

report = pipeline.ask("Show active MRR by customer region")
print(report.sql)
print(report.result.rows)
print(report.trace)

See docs/INTEGRATION.md for model, catalog, database, API, and evaluation integration examples.

Connect an LLM

The included HTTP adapter works with OpenAI-compatible chat-completions endpoints:

export LLM_BASE_URL="https://provider.example/v1"
export LLM_MODEL="your-model"
export LLM_API_KEY="..."

text-to-sql ask "Show active MRR by customer region" \
  --provider openai-compatible

The model response is treated as untrusted input. It still goes through deterministic validation and the database backend.

Connect another database

Implement QueryBackend for the target dialect and database. The backend owns:

• SQL parsing and dialect checks;
• table, column, and function policy;
• row limits and statement timeouts;
• read-only credentials and database permissions;
• conversion to the shared QueryResult type.

The included SQLiteBackend is a runnable example, not a universal database adapter. A production PostgreSQL or warehouse integration should use a real read-only role and server-side timeout. Prompt instructions are not an authorization boundary.

Evaluation

text-to-sql-eval

The report contains:

• execution_pass_rate: SQL passed controls and ran;
• semantic_pass_rate: returned columns and rows matched the reviewed expected result;
• per-case skill and failure category.

The test suite includes a query that uses the wrong region field. Its execution passes and its semantic evaluation fails. This is a regression test for business meaning, not a claim that the small synthetic set measures general model accuracy.

Current scope

Implemented:

• structured query or clarify decisions;
• explicit schema and business-term catalog;
• single-statement, read-only SQLite validation;
• database authorizer, row limit, fetch limit, and timeout;
• at most one repair after validation or execution failure;
• stage-by-stage trace;
• result-level evaluation;
• zero-key demo and OpenAI-compatible model adapter.

Not implemented:

• PostgreSQL or warehouse adapters;
• schema retrieval for large catalogs;
• authentication, tenant policy, or row-level security;
• HTTP service or web UI;
• production telemetry backend;
• a statistically meaningful model benchmark.

These are integration points, not features that this version claims to provide.

Repository layout

data/                   synthetic catalog and eval cases
src/text_to_sql/        pipeline, interfaces, adapters, validation, evaluation
tests/                  safety, repair, integration, and semantic regression tests
scripts/preflight.py    local release check
docs/                   integration, architecture, provenance, and release notes

Safety and provenance

The repository uses independently created synthetic data and a new implementation. It does not include an employer assessment prompt, supplied dataset, answer key, candidate response, recruiter correspondence, or credential. See docs/IP_AUDIT.md and SECURITY.md.

License

MIT.