Parth TiwariStart a project
MenuClose

Case 06 / Text-to-SQL system

QueryPilot

QueryPilot turns a question into read-only SQL only after schema retrieval, static criticism, database execution, and one bounded correction path have all had a chance to reject it.

Read the evidence record View repository
Role
AI engineer
Built at
Personal project
Contribution
Retrieval, generation, validation, correction, and evaluation
Status
Shipped
The real QueryPilot FastAPI documentation showing the served text-to-SQL API surface
Real API captureSurface evidence, not query-quality proof

Measured, with the denominator attached

Proof before polish.
63 to 67 of 70

QueryPilot's correction loop moved execution success from 63 to 67 queries on the 70-query core set.

70 core benchmark queriesVerified 2026-08-28
What it is

01 / What it is

The useful result is the SQL, its checks, and the path that produced it.

QueryPilot lets a person ask a PostgreSQL question in plain language and inspect the SQL that ran.

It links the question to relevant schema, generates SQL, checks it, executes read-only, and retries with the failure reason when needed.

Built for

A developer or analyst who needs inspectable read-only PostgreSQL queries from natural language and wants failure, correction, and safety boundaries visible.

I built the schema linker, SQL generator, critic and safety checks, read-only execution layer, bounded correction loop, FastAPI surface, benchmark datasets, and the denominator audit behind the published correction result.

Contribution record

02 / Product proof

Read the source evidence before the argument.

Committed correction trace

Committed correction tracehard_001 needed three attempts
Question
Find customers who placed more orders than the average customer
Schema context
orders, customers, order_items, products
Rejected attempt
Invalid multi-CTE SQL with incorrect join syntax
Correction
Subquery-based average order count
Attempts
Three
Execution
Succeeded
Source: Day 6 full results plus the matching Day 5 failure record
This is a readable rendering of committed evaluation artifacts. The API screenshot proves the surface exists; this trace carries the primary system evidence.
01
Schema recordContext for hard_001
Primary
orders and customers
Additional
order_items and products
Isolation
Schema-scoped retrieval
Source: day6_full_results.json
Retrieve only relevant schema

The question is embedded against isolated schema profiles so generation receives the likely tables and relationships instead of the whole database.

02
Correction recordFailure becomes context
Failure
Invalid join structure
Feedback
Database and critic reason
Guard
Bounded attempts plus normalised SQL comparison
Source: Committed correction loop and evaluation docs
Reject and correct once

Static checks and database failures become structured feedback, while normalised comparison prevents cosmetic rewrites from consuming the retry budget.

03
The real QueryPilot API documentation for the read-only query and schema endpoints
Execute read-only

Only checked SQL reaches PostgreSQL, and the result keeps the generated statement and attempt count visible.

03 / Problem and responsibility

A query can execute successfully and still answer the wrong question.

Text-to-SQL can produce valid-looking queries that reference the wrong table, expose sensitive intent, or fail only when the database plans them.

A reliable system needs bounded correction and an honest definition of what its benchmark actually measures.

Retrieval
Schema profiles, isolated vector retrieval, table selection, and database context boundaries.
Agent graph
Generation, critic validation, safety checks, bounded correction, and retry comparison.
Evaluation
Core and adversarial sets, execution records, denominator correction, and failure taxonomy.

04 / Research that changed the build

Research only matters here when it changed a decision.

  1. 01

    Schema-linking experiments

    Generation quality depended more on complete, relevant schema context than on adding more prompt prose.

    Schema profiles and retrieval became a first-class stage before SQL generation.
  2. 02

    Critic and database validation

    Static validity and actual PostgreSQL execution expose different errors.

    Both checks feed one structured correction context rather than being reported as the same failure.
  3. 03

    Day 6 core and adversarial evaluation

    The 70 core questions and 12 adversarial questions test different contracts and cannot share one success denominator.

    The published lift is attached only to the core set and adversarial behaviour stays separate.
  4. 04

    Safety-correction failure review

    A correction loop can repair syntax by weakening the guard that rejected an unsafe query.

    Safety intent is rechecked after correction instead of trusting a query because the database now accepts it.

05 / Architecture

Return database and critic failures as structured correction context, with a fixed attempt limit.

A schema linker narrows the context, the generator writes SQL, and the critic applies static and safety checks before read-only execution.

Execution failures can re-enter one correction path. The evaluation records execution success, not semantic correctness of the business answer.

Question to schema context to critic to bounded correction
  1. 01QuestionNatural language enters with one database scope
  2. 02Schema linkRetrieve relevant tables, columns, and relationships
  3. 03Generate and criticWrite SQL, apply static and safety validation
  4. 04CorrectUse structured failure feedback within a fixed attempt budget
  5. 05ExecuteRun read-only and return SQL, result, and trace metadata

06 / Decisions and trade-offs

What I chose, what I rejected, and what the choice costs.

DecisionRejectedTrade-off
Give failed SQL a bounded correction path.

Retry indefinitely or return every first failure to the user.

Some recoverable queries still fail after the limit, while latency and behaviour remain bounded.

Keep core and adversarial evaluations separate.

Combine 82 questions into one headline success rate.

The result is less compact, but the correction and safety denominators stay truthful.

Reapply safety after correction.

Treat a corrected, executable query as safe by definition.

A technically valid query may still be blocked, while correction cannot silently erase the original guard.

07 / Evidence

Correction improved execution on the 70-query core set. Nothing more is implied.

63 to 67 of 70

QueryPilot's correction loop moved execution success from 63 to 67 queries on the 70-query core set.

70 core benchmark queriesVerified 2026-08-28

The 63-to-67 result is a four-query recovery on the 70-query core set. The 12 adversarial queries remain separate, and a successful execution may still be semantically wrong.

08 / What failed

The failures are part of the architecture record.

  1. 01

    The correction number had the wrong denominator

    What I saw
    Portfolio copy attached the lift to all evaluated queries.
    Why
    The 70-query core set and 12-query adversarial set were collapsed in the summary.
    What changed
    The public claim now says 63 to 67 of 70 and keeps the adversarial set separate.
    Still true
    Execution success still does not establish semantic correctness.
  2. 02

    Correction could weaken safety

    What I saw
    A failed query could become executable after rewriting the structure that originally triggered a guard.
    Why
    Syntax recovery and intent safety were treated as one pass.
    What changed
    Safety is checked again after correction and remains independent of database acceptance.
    Still true
    Adversarial coverage is finite and does not prove resistance to every unsafe formulation.
  3. 03

    A corrected query still answered the wrong thing

    What I saw
    A statement could run successfully while using a semantically unsuitable table, join, or aggregation.
    Why
    The evaluation's success field records execution, not business-answer correctness.
    What changed
    The case study names the metric as execution success and refuses to call it semantic accuracy.
    Still true
    A gold-answer or human semantic evaluation is still needed.
Read the denominator correction

09 / Limits and future

What the product cannot do yet, beside what may come next.

Current limitations

  • The published result measures execution success on one 70-query core set, not semantic answer accuracy.
  • The 12 adversarial questions are separate and are not included in the correction-lift denominator.
  • The system is bounded to read-only PostgreSQL patterns and a known schema profile.
  • The API capture proves the served interface, not that every endpoint or external deployment is currently available.

Deliberate boundaries

  • Describe execution success as semantic accuracy.
  • Combine the core and adversarial denominators into one lift claim.
planned

Semantic answer review

Add gold results or human review so execution success can be separated from whether the query answered the intended business question.

investigating

Safety-preserving correction

Expand tests where a syntax fix changes sensitive intent, table access, or the meaning of a previous rejection.

planned

Inspectable trace export

Persist schema context, rejected SQL, failure feedback, corrected SQL, and result in one publication-safe trace artifact.

10 / Stack and sources

The systems underneath it, and where this account comes from.

  • Python
  • FastAPI
  • LangGraph
  • PostgreSQL
  • ChromaDB
  • Docker
Audited internallyQueryPilot committed repository and Day 6 evaluation
Audited internallyReal served API capture