Text-to-SQL research project

Reproducible project foundation for the master thesis Natural Language to SQL Translation Using Large Language Models.

This project foundation uses a deterministic mock provider and a small SQLite fixture for executable smoke tests. DATA-001 freezes the Spider2-Lite SQLite benchmark protocol, DATA-003 provides its checksum-gated metadata loader, EVAL-001 provides structured execution comparison, EVAL-003 provides the official gold-result runner, SCHEMA-002 provides deterministic M-Schema prompts, LINK-001 records the completed linked-M-Schema B6 experiment, LINK-002 provides a recall-repaired B6R arm, RET-001 provides a checksum-gated Spider 1.0 train-only retrieval index, RET-002 provides completed B3/B4 few-shot experiments, DSPY-001 records the completed B5 optimization and development run, and SAFE-001 provides strict SQLite query validation against the canonical schema. The strict reference-SQL EVAL-002 path remains optional.

Requirements

• Python 3.11 or newer
• Python dependencies installed from requirements.lock (including DSPy 3.3.1, LiteLLM 1.99.0, Optuna 4.9.0, and the official Groq SDK)

Quick start

python scripts/create_fixture_db.py
PYTHONPATH=src python3 -m text2sql.cli \
  --question "List customer names" \
  --database data/fixtures/demo.sqlite \
  --output artifacts/reports/data001-smoke.jsonl

The command prints one JSON result and appends the same structured record to the requested JSONL file. The generation pipeline does not execute generated SQL. SAFE-001 provides static validation; SAFE-002 provides a separate bounded execution API and CLI. EVAL-001 remains the evaluation-only executor.

Explicitly validate and execute SQL on a static SQLite snapshot (Linux):

PYTHONPATH=src python3 -m text2sql.execution.cli \
  --database data/fixtures/demo.sqlite \
  --sql "SELECT first_name FROM customers ORDER BY customer_id"

SAFE-002 enforces a 10-second worker deadline, 1,000-row/1-MiB result limits, and a 256-MiB worker address-space limit. Input snapshots are limited to 64 MiB; WAL/journal-backed inputs require a separate snapshot export. See SQLite sandbox execution for API, limits, and statuses.

Run tests

PYTHONPATH=src python3 -m unittest discover -s tests -v

Optional editable install

python3 -m pip install --no-build-isolation -e .
text2sql --question "List customer names" --database data/fixtures/demo.sqlite

Current scope

Implemented:

• stable domain models;
• SQLite schema inspection;
• deterministic schema serialization for the baseline prompt;
• provider protocol, deterministic mock provider and official-SDK Groq adapter;
• validated canonical schema model with deterministic serialization and hashes;
• end-to-end pipeline;
• JSONL audit output;
• unit and integration tests;
• versioned configuration placeholders;
• preserved legacy notebooks;
• pinned Spider2-Lite source/evaluator checksums;
• deterministic database-disjoint SQLite split (31 development / 104 test);
• executable protocol validation and leakage-policy tests;
• documented benchmark, evaluation and reporting contract;
• pinned Spider2-Lite SQLite metadata loader;
• checksum validation before JSON parsing;
• deterministic normalized metadata and dataset manifest generation;
• strict 31-development / 104-test ID and database firewall;
• rejection of gold-like SQL fields during metadata ingestion;
• isolated read-only execution of generated and reference SQLite SQL;
• Spider2-Lite-compatible result comparison with order, condition-column, NULL and numeric-tolerance handling;
• structured evaluation results and exact-ID Execution Accuracy aggregation;
• local evaluator CLI and fixture-backed integration tests;
• deterministic db_id resolver, protected reference SQL store and Spider2 batch runner;
• strict duplicate/missing/extra prediction and reference coverage;
• official gold-result CSV variant evaluation with per-resource checksums;
• a default Spider2 preflight that is ready for all 31 development examples;
• deterministic lexical table/column schema linking with value matches;
• primary/foreign-key preservation and shortest-path FK closure;
• recall-safe full-schema fallback and per-selection audit metadata;
• frozen linked-M-Schema B6 config, 31-example audit, and completed negative live result;
• separate B6R hybrid full-compact/linked-detail prompt, config, audit, and tests.
• pinned official Spider 1.0 training source, license, archive and input checksums;
• deterministic 7,000-example / 140-database train-only retrieval index;
• exact Spider2 ID, database and normalized-question leakage firewall;
• version-controlled retrieval manifest and checksum-verifying artifact loader.
• frozen B3 fixed-random and B4 TF-IDF cosine selectors and experiment configs;
• few-shot M-Schema prompt integration and provider-free per-target retrieval audit.
• frozen DSPY-001/B5 signature, database-disjoint 21/10 development split, execution-result metric, offline audit CLI, completed MIPROv2 compile, and checksum-verified 31-example B5 result (4/31 correct, 28/31 executable);
• SAFE-001 parsed SQLite validation with a one-SELECT policy, canonical table/column binding, system and side-effect denial, auditable hashes, CLI, and adversarial tests.
• SAFE-002 isolated SQLite execution with validation-hash handoff checks, snapshot revalidation, Linux worker resource limits, bounded results, sanitized errors, and filesystem-preservation tests.
• REF-001/B7 completed: three generation strategies, deterministic ranking, at most one repair, and verified 31-example EVAL-003 results (5/31; B6R remains best at 6/31). Provider failures limit interpretation; see B7 results.

Not implemented yet:

• SEM-001 paired semantic-error corpus;
• typed semantic planning, SQL-skeleton retrieval, and the B7P first-pass arm;
• security evaluation;
• Gradio application built on the new pipeline.

Security

Do not copy active API keys, database passwords or production databases into this repository. The evaluation-only executor works on isolated in-memory SQLite copies. SAFE-001 provides AST-based SQL validation without evaluating the query. SAFE-002 explicitly executes validated SQL in a resource-limited process over an isolated snapshot; the generation CLI still does not execute SQL. This SQL boundary is not an OS container for arbitrary code or hostile database-engine exploits.

Dataset protocol

The headline benchmark is the pinned 135-example SQLite portion of Spider2-Lite, split by database into 31 development and 104 test examples. It is explicitly a custom research split, not the full Spider2-Lite leaderboard setting. See docs/experiments.md and validate it with the normal test command.

After placing the pinned upstream repository under data/raw/spider2, prepare the metadata with:

PYTHONPATH=src python3 -m text2sql.datasets.cli

The command verifies the source checksum and the version-controlled DATA-003 manifest before writing examples.jsonl and dataset-manifest.json under data/processed/spider2-lite-sqlite-v1/. It does not read or emit gold SQL and does not execute database queries.

Train-only retrieval index

After acquiring the official Spider archive as documented in data/README.md, build and verify the RET-001 artifact with:

PYTHONPATH=src python3 -m text2sql.retrieval.cli

The command verifies the pinned Spider 1.0 train and schema files before parsing, requires exact coverage of the frozen Spider2 firewall, rejects every Spider2 ID, database or normalized-question overlap, and writes 7,000 deterministic training entries under artifacts/retrieval/spider1-train-v1/. RET-002 connects the verified index to separately frozen B3/B4 few-shot prompts.

Audit both selectors over all 31 development targets without a provider key:

PYTHONPATH=src python3 -m text2sql.experiments.retrieval_cli \
  --experiment-config configs/experiments/exp005-b3.toml
PYTHONPATH=src python3 -m text2sql.experiments.retrieval_cli \
  --experiment-config configs/experiments/exp006-b4.toml

The audit records retrieved IDs, source databases, ranks and similarity scores, but does not call Groq or read Spider2 test outcomes. Completed development results are B3 4/31 and B4 5/31; the sealed test split was not opened.

DSPY-001 B5

Audit the frozen B5 inputs without a provider key:

PYTHONPATH=src .venv/bin/python -m text2sql.optimization.cli audit \
  --report artifacts/reports/dspy001-b5-audit.json

The frozen config uses MIPROv2 with 3 candidates and 5 trials, zero DSPy-level bootstrapped/labeled demonstrations, and a database-disjoint 21-example training/10-example validation split. The three permitted Spider 1.0 demonstrations remain inside each inherited B4 context.

Start a new paid compile only to reproduce the frozen optimization as an independent paid run:

PYTHONPATH=src .venv/bin/python -m text2sql.optimization.cli optimize

The command prints a unique recovery run ID near startup. If the process is interrupted, resume that exact run (within 72 hours) with:

PYTHONPATH=src .venv/bin/python -m text2sql.optimization.cli optimize \
  --resume-run-id run-YYYYMMDDtHHMMSSz-xxxxxxxxxxxx

A plain optimize command always starts an independent cache, so separate experiments cannot silently share model outputs. Resume restarts deterministic MIPROv2 orchestration and replays only identical successful LM calls; it is not an in-place Optuna snapshot. Cache identity covers config, code, dependency, model, endpoint, prompt, split, dataset, database and gold-result hashes. Different stochastic rollout_id values remain different cache keys. Corrupt, stale, mismatched, evicted, concurrent, secret-bearing, failed, empty, or truncated responses fail closed or are not cached.

The optimizer also enforces the frozen 8,000 TPM Groq tier with a 90% rolling safety budget (7,200 tokens). Cache hits bypass both Groq and the limiter and are reported separately from provider usage. A 429 honors Groq's retry delay and is retried up to eight times. A finish_reason="length" response stops the run before it can be cached or scored.

Recovery data lives under artifacts/dspy/dspy001-b5/checkpoints/<run-id>/; it contains an identity and status file, an integrity-ledger-backed LM cache, hash-only metric progress, and MIPRO diagnostic snapshots. It never contains the API key. On success the program and checksum-bound optimization manifest are written under artifacts/dspy/dspy001-b5/.

The completed frozen compile selected the original/default instruction with a 2/10 validation score. Its exact 31-example development run scored 4/31 (12.90%) with 28/31 executable queries, below B4 at 5/31 and B6R at 6/31. The program, optimization manifest, predictions, and EVAL-003 report are checksum-bound; the Spider2 test split remains sealed.

After a fresh compilation, run or resume exact 31-example generation and EVAL-003 scoring:

PYTHONPATH=src .venv/bin/python -m text2sql.optimization.cli run

Execution evaluator

Create the fixture database and compare two SQL statements:

python3 scripts/create_fixture_db.py
PYTHONPATH=src python3 -m text2sql.evaluation.cli \
  --database data/fixtures/demo.sqlite \
  --generated-sql "SELECT first_name FROM customers ORDER BY customer_id DESC" \
  --reference-sql "SELECT first_name FROM customers ORDER BY customer_id ASC" \
  --ignore-order

The evaluator runs each statement in a separate read-only in-memory copy and prints a structured JSON result. See docs/evaluation.md for comparison rules, exit codes and limitations.

The pinned Spider2 integration commands, required local layout, default gold-result mode, and optional strict SQL audit mode are documented in docs/spider2-evaluation-runner.md.

Repository map

• src/text2sql/ - reusable pipeline code;
• src/text2sql/retrieval/ - train-only retrieval index and leakage firewall;
• configs/ - versioned experiment and security configuration;
• configs/datasets/spider2-lite-sqlite-metadata-manifest-v1.json - frozen DATA-003 output contract;
• tests/ - tests that run without external services;
• data/fixtures/ - small synthetic test resources;
• notebooks/legacy/ - original proof-of-concept experiments;
• docs/ - architecture, experiment, decision and source records;
• docs/sources-and-references.md - living register of every paper, dataset, repository, documentation source and legacy input used by the project;
• docs/evaluation.md - EVAL-001 execution and comparison contract;
• docs/schema-linking.md - LINK-001 algorithm, frozen policy, offline audit, and B6 commands;
• docs/spider2-evaluation-runner.md - EVAL-003 default workflow and optional EVAL-002 audit mode;
• artifacts/ - generated results, excluded from Git by default.

Optional Groq smoke run

The mock provider remains the default. A Groq request is explicit and requires an environment key:

export GROQ_API_KEY="..."
python3 scripts/create_fixture_db.py
PYTHONPATH=src python3 -m text2sql.cli \
  --question "List customer names" \
  --database data/fixtures/demo.sqlite \
  --provider groq \
  --model-id openai/gpt-oss-120b \
  --temperature 0 \
  --max-tokens 1024 \
  --output artifacts/reports/groq-smoke.jsonl

EXP-001 baseline runner

The B0/B1 development runner is resumable and restricted to the frozen 31-example development split. It checkpoints each successful provider response before continuing and automatically runs EVAL-003 only after exact coverage. The completed EXP-001 run scored B0 at 0/31 and schema-aware B1 at 5/31 (16.13%); reports are stored in artifacts/reports/.

Load GROQ_API_KEY into the environment, then run B0 (question only):

PYTHONPATH=src .venv/bin/python -m text2sql.experiments.cli \
  --experiment-config configs/experiments/exp001-b0.toml \
  --predictions artifacts/experiments/exp001-b0-predictions.jsonl \
  --report artifacts/reports/exp001-b0-report.json

Run B1 (question plus complete simple schema):

PYTHONPATH=src .venv/bin/python -m text2sql.experiments.cli \
  --experiment-config configs/experiments/exp001-b1.toml \
  --predictions artifacts/experiments/exp001-b1-predictions.jsonl \
  --report artifacts/reports/exp001-b1-report.json

Re-running the same command resumes from its JSONL checkpoint and does not repeat completed IDs. Starting both fresh runs requires 62 Groq requests before retries.

SCHEMA-002 B2 result

The completed 31-example B2 run scored 2/31 (6.45%) with 22/31 executable SQL outputs, compared with B1 at 5/31 (16.13%) and 26/31 executable. B2 used 107,586 input tokens—109.8% more than B1—so full M-Schema did not improve this baseline. To reproduce or re-score the checkpoint, run:

PYTHONPATH=src .venv/bin/python -m text2sql.experiments.cli \
  --experiment-config configs/experiments/exp002-b2.toml \
  --predictions artifacts/experiments/exp002-b2-predictions.jsonl \
  --report artifacts/reports/exp002-b2-report.json

This uses the same model/settings and 31 development IDs as B1, with M-Schema as the only prompt-arm change. With the completed checkpoint present, it makes no Groq requests and regenerates the report. The 104-example test split remains sealed.

Schema-linking status

LINK-001 is DONE. Frozen B6 covers 31/31 development examples and scored 1/31 (3.23%) with 27/31 executable outputs. It used 18,287 input tokens, but its aggressive pruning removed identifiers needed by previously correct B1 examples. The negative result and artifact hashes are preserved.

LINK-002 is DONE. B6R keeps every column in selected tables and also shows the model the complete compact schema, while retaining linked M-Schema as priority detail. It covers 31/31 development examples and scores 6/31 (19.35%) with 25/31 executable outputs, improving by one correct example over B1.

Run the provider-free B6R audit:

PYTHONPATH=src .venv/bin/python -m text2sql.experiments.linking_cli \
  --experiment-config configs/experiments/exp004-b6r.toml \
  --output artifacts/reports/exp004-b6r-linking-audit.json

Re-score completed B6R (no provider calls with the complete checkpoint):

PYTHONPATH=src .venv/bin/python -m text2sql.experiments.cli \
  --experiment-config configs/experiments/exp004-b6r.toml \
  --predictions artifacts/experiments/exp004-b6r-predictions.jsonl \
  --report artifacts/reports/exp004-b6r-report.json

Prediction/report SHA-256 values are 46664f819c...6577 and aaa130ec8e...2fc. See docs/schema-linking.md for both frozen policies, comparative analysis, failures, limitations, and full hashes.