Text-to-SQL — Deep Dive

Picture a chef who tastes a dish while cooking, spots a mistake like too much salt, and immediately adjusts the seasoning—then tastes again. This subsystem does the same thing for turning plain English questions into database queries: it automatically writes a first attempt, runs it to see if it works, and if the database spits back an error, it uses that real error message to rewrite the query. Its core job is to fix execution-time mistakes so that the final query actually runs correctly.

Now zoom in: first, the system generates an SQL query, then sends it through a safety gate that checks it is a read-only SELECT statement—otherwise it is rejected. If the query passes, it is executed against the database. When the database returns a syntax error or a "column not found" message, the system captures that exact text and feeds it to a repair_sql node. That node diagnoses the problem and writes a corrected SQL, which then re-enters the safety gate before running again. This loop repeats up to two times, no more. Crucially, the system uses a rule called early-accept: if the query runs successfully on the first try, the loop stops immediately—so a working query is never changed. It also treats an empty result set as success, not a mistake.

The trickiest detail is that emptiness is treated as the right answer, not a bug. Without that rule, a perfectly fine query that simply found no matching records would be wrongly flagged as broken, and the system would waste its limited repair attempts trying to "fix" something that was correct. The loop would burn through its budget, return the last error message, and a user asking "How many orders from last year?" would get a confusing failure instead of the simple truth: "zero orders matched." That's the concrete failure a beginner would feel without this subsystem—a correct question returning an error because the system couldn't distinguish between a real bug and a legitimate empty answer.

The self-healing loop and SELECT-only gate define how text-to-SQL turns plain questions into corrected, safe queries.

_MAX_REPAIR_ATTEMPTS = 2
_MAX_ROWS = 50


# validate_sql → {execute_sql, repair_sql, END}
# execute_sql → {repair_sql, summarize}
# repair_sql → validate_sql
# summarize → END

# validate_sql runs a SELECT-only gate via sqlglot parse + statement check.
# If it fails (not SELECT), route to END.
# If it passes, route to execute_sql.
# On error from execute_sql, if repair_attempts < _MAX_REPAIR_ATTEMPTS, route to repair_sql.
# repair_sql receives failed SQL + error text, regenerates corrected SELECT.
# Early-accept: successful execution (even empty rows) ends loop.

The subsystem begins at the validate_sql gate, which enforces that only a single read‑only SELECT (verified by AST parsing with sqlglot) reaches execution. Queries that pass are handed to execute_sql against the database. If the database raises a hard error—a missing column, malformed join, or type mismatch—the raw diagnostic message and the failing SQL are captured. Provided the attempt budget has not been exhausted (bounded to 2), the system routes to repair_sql. This node receives the failed SQL and the actual error text, diagnoses the problem, and regenerates a corrected single SELECT. That corrected query re‑enters validate_sql before any execution, so the repair can never widen permissions. The loop ends the moment a query executes successfully, because of the early-accept design choice: a working query is never “repaired” into a different one. An empty result set is treated as success, not a defect to heal—the system considers “no rows matched that filter” to be the true answer. After the attempts are exhausted, the run returns the last error rather than looping forever; the bound, not a hope, is the circuit breaker.

The core invariant is early-accept with success defined as any non‑error execution, including empty results. This guarantees that a correct query is never overwritten by a later repair attempt, and that the system does not mistake a legitimate “no data” answer for a failure requiring intervention. The invariant prevents the loop from burning its budget rewriting a query that already answered the user’s question correctly, even if the answer is zero rows. Additionally, the SELECT‑only gate composes with the repair loop: because repaired SQL re‑validates, the system preserves the invariant that only read‑only statements are ever executed, regardless of how many repairs occur.

The key trade-off is choosing execution‑grounded repair over intrinsic self‑correction. Intrinsic correction—where the model re‑reads its own SQL against a human‑written checklist without running it—contributes only about 1–3 percentage points of accuracy, because the model has no ground truth to react to, so it often “fixes” correct queries or misses real bugs. The execution‑grounded approach rejects that alternative. Instead, it feeds real database exceptions (e.g., “no such column: cust_id”) back to the model, producing substantially better accuracy because the feedback is specific and grounded. The cost avoided is the wasteful rewriting of already‑correct queries and the silent acceptance of bugs that would remain hidden under introspection. The trade-off is that execution‑grounded repair cannot fix queries that run cleanly but answer the wrong question; only a human reading the answer catches that semantic miss.

A concrete failure mode is a semantic miss that produces a successful execution. For example, a user asks “Which customers have overdue invoices?” but the generated query joins the wrong table and returns a list of all customers. The loop sees a non‑error execution, counts it as success, and exits. The system then proceeds to the summarize node, which returns a confident business sentence grounded in the returned rows—in this case, a list that looks plausible but is semantically wrong. The operator would see a polished answer with confidence, the explanation, the source tables, and the executed SQL as evidence, but they would not see any error signal. Only by manually inspecting the answer would they detect the mistake. The system provides provenance, but the responsibility for catching semantic errors falls entirely on the human reader.

Semantically Wrong Query That Executes Successfully

• Trigger — The LLM generates a SQL query that passes validate_sql and runs without a database error but joins the wrong table or uses a wrong filter, answering a question different from the user’s intent. The self‑healing loop treats any non‑error execution as success.
• Guard — No explicit guard in the subsystem for semantic correctness. The summarize node will only report rows it actually receives, but it has no way to detect that those rows are irrelevant. The loop’s early‑accept design means a successful execution ends the repair cycle immediately.
• Posture — Fail‑soft. The system returns a confident answer with provenance fields (confidence, reason, source, evidence) that are grounded only in the executed SQL and its result set. The run completes without any error signal, so the operator sees a plausible but wrong answer.
• Operator signal — Silent absence: no error field, no exception, no metric like repair_attempts incremented. The only sign is a human reading the answer and recognising the semantic miss. The LangSmith metric agentic_sales.text_to_sql.confidence may be high, misleading the operator.
• Recovery — No automated recovery. The operator must manually correct the query or re‑phrase the original question and start a new run. The loop provides no fallback because it never detected a defect.

Repair Attempts Exhausted (Persistent Execution Error)

• Trigger — The initial LLM‑generated SQL fails execution with a database error (e.g., a missing column or type mismatch). The repair node receives the error text and generates a corrected query, but each subsequent repair also fails, up to the maximum of two repair attempts.
• Guard — The loop’s hard bound of two repair attempts (at most 2 repair attempts in the cost envelope) acts as the circuit breaker. After the last failed attempt the run does not loop forever; it returns the last error.
• Posture — Fail‑hard. The run aborts without a result. The subsystem outputs no rows and the LLM summary is never invoked because there was no successful execution to summarise.
• Operator signal — The LangSmith metric agentic_sales.text_to_sql.repair_attempts will be 2. The returned object carries the last database error text as the reason field, and the evidence field will contain the last failed SQL. No row_count metric is emitted because no rows were fetched.
• Recovery — The operator must inspect the reported error and manually correct the query or adjust the schema mapping. The loop does not retry further; the only way forward is a fresh query from the user.

LLM Unavailable During Summarization

• Trigger — The query executed successfully (rows were returned), but when the summarize node attempts to call the LLM to produce a business‑language sentence, the LLM is unreachable, times out, or returns an error.
• Guard — The summarize node contains a deterministic fallback: “LLM unavailable → deterministic ‘The query returned N record(s)’.” No named function is given in the source, but the behaviour is explicitly described.
• Posture — Fail‑closed. The summary is replaced with a safe, grounded statement that does not fabricate totals or percentages. The provenance fields (confidence, reason, source, evidence) are still populated from the executed SQL and result set.
• Operator signal — The answer contains a plain, un‑narrated line such as “The query returned 34 record(s)” instead of a natural‑language interpretation. The confidence metric may be lowered (if the system sets it conditionally) or unchanged if it was already set from the SQL generation phase. The LangSmith metric agentic_sales.text_to_sql.confidence would still reflect the SQL‑generation confidence, not the summary.
• Recovery — No retry. The run completes with the deterministic fallback. The operator sees that the LLM summary was not produced and can re‑submit the same query to attempt a new summarization; the loop does not automatically re‑try.

Repaired SQL Fails validate_sql (AST Rejection)

• Trigger — The repair node generates a corrected SELECT statement. Before execution, this candidate re‑enters validate_sql. The AST parser (e.g., sqlglot) rejects it because it contains a DML/DDL statement, multiple statements, or invalid syntax not caught by the database’s own parser.
• Guard — No explicit handler is named in the source for a validation rejection within the repair loop. The source states that repaired SQL “always re‑enters validate_sql before any execution”, but does not define what happens if validation fails—whether it is treated as an execution error and passed back to repair, or whether it immediately exhausts the attempt. The loop’s repair node expects a database error text, not a parse‑tree failure.
• Posture — Likely fail‑soft (if the validation failure is treated as an execution error, another repair is triggered) or fail‑hard (if the attempt is counted without a retry because no database error exists). The source is silent, so state: no defined posture.
• Operator signal — Unknown. No metric or log line is described for a validate_sql failure. The operator might see an unexpected error message that does not match typical database syntax errors, or the repair_attempts metric might increment without a corresponding execution.
• Recovery — Not specified in the source. A manual step is needed to diagnose why the repair produced an invalid AST; the loop may have already consumed an attempt without a database execution.

Empty Result Set Treated as Success (User Expected Data)

• Trigger — The user’s question implies that rows should exist (e.g., “show contacts from California”), but the generated SQL returns zero rows because of an incorrect value‑linking (e.g., state = 'California' when the database uses 'CA'). The subsystem explicitly counts an empty result set as success (“no rows matched that filter is usually the true answer”).
• Guard — The early‑accept rule: “An empty result set counts as success, not a defect to heal.” The summarize node handles this by generating “The query ran successfully but matched no records.”
• Posture — Fail‑soft. The run completes normally, returning a clear message that no records matched. The operator gets a truthful statement, but if the user expected data, the answer is semantically wrong.
• Operator signal — The answer contains the deterministic phrase “matched no records”. The row_count LangSmith metric will be 0. The provenance fields are still present: confidence (high, because the SQL was valid), reason (the explanation), source (tables_used), evidence (the executed SQL). No error is raised.
• Recovery — The operator must recognise that the empty result is due to a value‑linking mistake, not a genuine absence of data. The loop does not attempt repair because it considers the execution successful. The user must re‑phrase the query with correct values.

Imagine asking a librarian to find a book, but the librarian has no catalog — they'd guess where the book might be, often wrong. This subsystem is like giving the librarian the real catalog of every shelf (table names) so they can only point to actual shelves, turning a plain question into a safe, accurate database query.

First, the system reads the real catalog (the database schema) at the start. It then identifies which shelves (tables) are relevant to the question — only after that does it write the request (SQL) on a slip. But before the slip can be used, a guard called validate_sql uses a parser to check that the slip is only a request to read a book, not to throw it away (SELECT-only, no DROP commands). The parser builds an abstract syntax tree (AST) to enforce this precisely.

The trickiest detail: the guard does not scan for forbidden words like "DROP" — that can be fooled by comments or stacked statements (e.g., SELECT 1; DROP TABLE users). Instead, the AST parser verifies the entire structure is exactly one single SELECT (or WITH) node, rejecting any write command. Even if the query fails during execution and gets repaired, the repaired slip must pass this same guard again. Without this subsystem, the system would confidently generate SQL using invented table names or accidentally run destructive commands, corrupting real data or returning wrong answers.

Schema grounding constants that define known CRM tables and data limits.

_MAX_REPAIR_ATTEMPTS = 2
_MAX_ROWS = 50
_CRM_TABLES = ("companies", "contacts", "email_campaigns", "emails")
_FUNNEL_STAGES = ("discovered", "enriched", "contacted", "opened", "replied", "converted")

The subsystem operates as an ordered pipeline. First, every candidate SQL enters validate_sql, a parser-based gate that builds a syntax tree to enforce that the statement is a read-only SELECT. Only queries that pass this gate reach execute_sql, where the database actually runs them. If execution raises an error—a missing column, a malformed join, a type mismatch—the node captures the raw diagnostic and the failing query. Provided repair attempts remain (bounded to 2), the system routes to repair_sql. The repair node receives the failed SQL plus the actual error text, diagnoses the cause, and regenerates a corrected single SELECT. That corrected query does not skip the gate; it re-enters validate_sql before any execution. This loop repeats until the query executes successfully or the attempt budget is exhausted. When attempts run out, the run returns the last error rather than looping forever.

The central invariant is that the gate is the only path to execution—repaired SQL always re-enters validate_sql before it can run. This guarantees that no generated query, whether from an initial generation or a repair, can widen permissions or turn into a destructive statement. Two additional properties harden the invariant. First, early-accept ensures that the moment a query executes successfully, the loop ends; a working query is never “repaired” into a different one. Second, an empty result set counts as success, not a defect to heal—treating zero rows as failure would burn the attempt budget rewriting a correct query. Together these preserve the read-only boundary and prevent the loop from inventing incorrect results.

The design rejects an obvious alternative: intrinsic self-correction, where a model re-reads its own SQL against a human-written checklist without ever executing it. As the source notes, that introspective approach contributes only about 1–3 percentage points on benchmarks and often “fixes” correct queries or misses real bugs. Instead, the subsystem uses execution-grounded feedback—real database errors such as “no such column: cust_id”—as the repair signal. This avoids the cost of wasted regenerations on already‑correct queries and concentrates on the failures that actually matter. The trade‑off is that the loop cannot fix a query that runs cleanly and answers the wrong question; it only corrects execution-time errors. The bound of two attempts keeps both cost and latency under control, an explicit design choice stated as “the bound, not a hope, is the circuit breaker.”

One concrete failure mode is a semantic miss: the generated SELECT joins the wrong table, runs cleanly, returns rows, and is summarized confidently. An operator sees a summary that looks plausible—the system reports “matched N records” with provenance—but the answer is substantively wrong because the query answered a different question. There is no error signal from the database, no repair is triggered, and the loop exits on success. The source explicitly warns that only a human reading the answer catches that miss. The operator would observe a confident but incorrect output, with the executed SQL included in the evidence, yet no automated mechanism alerts them to the logical flaw.

Failure: Execution Error Due to Hallucinated Column or Table Name

• Trigger — The LLM generates a SQL query referencing a column or table that does not exist in the actual schema (e.g., contacts.phone when the column is phone_number). The schema was correctly read and provided, but the model invents a name.
• Guard — The query passes the AST parse in validate_sql because it is a syntactically valid SELECT. Execution against D1 then fails with a runtime error. The repair node receives the error text, diagnoses it, and regenerates a corrected SQL. The corrected SQL re-enters validate_sql before any execution.
• Posture — Fail‑soft. The self‑healing loop retries up to the configured bound (at most 2 repair attempts, per the cost envelope). The run does not abort on the first failure.
• Operator signal — The LangSmith metric agentic_sales.text_to_sql.repair_attempts increments by 1 (or more) for that run. The reason field in the final answer will contain the explanation from the last repair iteration.
• Recovery — The repair node generates a new candidate. If it executes successfully, early‑accept ends the loop and routes to summarize. If all 2 attempts fail, the last error is returned and no summary is produced.

Failure: Semantic Miss – SQL Runs Correctly but Answers the Wrong Question

• Trigger — The LLM selects an irrelevant table (e.g., leads instead of contacts) or joins on the wrong key, producing a query that executes successfully, returns rows, and passes validate_sql. The answer is logically incorrect but the database reports no error.
• Guard — No guard exists in the system. The source explicitly states: “It cannot fix a query that runs cleanly and answers the wrong question: a SELECT that joins the wrong table returns rows, counts as success, and is summarized confidently.”
• Posture — Fail‑soft. The run completes normally, returning a confident summary with provenance (confidence, reason, source, evidence). The source field lists tables_used (the wrong tables), but there is no automatic signal that the answer is semantically wrong.
• Operator signal — No error metric is emitted; the confidence and reason fields appear plausible. Only a human reading the output can detect the miss.
• Recovery — No automatic recovery. The operator must manually re‑query with a corrected natural‑language request or directly edit the SQL.

Failure: Execution Error Due to Type Mismatch

• Trigger — The generated SQL compares a string column to a numeric literal (e.g., WHERE status = 1 when status is VARCHAR), or performs an invalid arithmetic operation. The query passes validate_sql but D1 throws a type‑mismatch exception.
• Guard — Same as the first failure: the repair node receives the database error and rewrites the SQL (e.g., adding quotes around the literal). The repaired SQL re‑enters validate_sql.
• Posture — Fail‑soft, with up to 2 repair attempts.
• Operator signal — agentic_sales.text_to_sql.repair_attempts increments.
• Recovery — The loop retries. On success, the run proceeds to summarize. On exhaustion, the last error is returned.

Failure: Parse Validation Rejects a Non‑SELECT Statement

• Trigger — The LLM generates a query that includes a write operation (e.g., INSERT, DROP) or multiple statements (SELECT 1; DROP TABLE users). The AST parser in validate_sql (presumably sqlglot) detects a node type other than Select and rejects the query before any execution.
• Guard — validate_sql itself performs the AST check. The rejection signal is fed to the repair node as an error text (parse failure). The graph does not attempt to execute the query on D1.
• Posture — Fail‑soft. The repair loop treats the parse error as a hard failure and attempts regeneration.
• Operator signal — The repair_attempts metric increments. The error text (e.g., “non‑SELECT statement”) is passed to the repair node.
• Recovery — The repair node regenerates a single SELECT statement. The loop continues until a query passes both parse and execution, or until the attempt budget is exhausted.

Failure: Schema Catalog Read Failure

• Trigger — At the start of the graph, the system attempts to read the real database schema from Cloudflare D1. The connection times out, the database is unreachable, or the catalog query itself fails.
• Guard — No guard is described in the source for this step. The graph likely raises an exception that propagates upward; there is no fallback or retry loop for schema retrieval.
• Posture — Fail‑hard. The run aborts without performing table identification or SQL generation.
• Operator signal — The LangSmith metrics for the run are never emitted (e.g., tables_used remains absent). The Cloudflare Worker logs an error from the D1 binding.
• Recovery — Manual intervention required: restore D1 connectivity or restart the run after the database is available. No automatic retry is indicated.

Failure: LLM Unavailability During Table Identification

• Trigger — The graph calls the LLM to identify which tables are relevant for the user’s natural‑language question, but the LLM service returns a 503 or timeout. The context notes that for structurally unknown queries the LLM is used; no deterministic builder is invoked at this stage.
• Guard — No fallback is specified for the table‑identification step. The source only describes a deterministic fallback for the summarize node (“LLM unavailable → deterministic ‘The query returned N record(s)’”), not for the earlier table‑selection phase.
• Posture — Fail‑hard. The run cannot proceed; no SQL is generated or executed.
• Operator signal — An LLM‑call error is recorded in Cloudflare logs. The confidence and reason fields are never produced. No LangShip metrics for the run are emitted.
• Recovery — The run must be retried manually once the LLM service is restored. There is no built‑in retry or fallback at this point in the graph.

Imagine you are coaching a new cook to follow a kitchen checklist. The first rule is that the cook must only use ingredients from a specific local market—no foreign spices or equipment from another continent. The second rule is that before mixing anything, the cook must explain each step out loud. This is exactly what the system does: it tells the language model to use only SQLite functions and syntax, and to reason step by step before writing the SQL query. The purpose is to generate accurate, runnable database queries from plain English questions.

The system actually forces the model through a three-step chain: first it rewrites the user’s intent in one sentence (the understand_question node), then it picks tables only from the real schema (identify_tables), and only then writes the SQL using dialect‑correct tools like strftime for dates or || for concatenation—never Postgres features like ::cast or ILIKE. This chain‑of‑thought decomposition is exactly what the “SQL‑of‑Thought” paper describes: the model plans before generating, reducing guesses.

The trickiest detail is that the “identify tables” step is seeded with actual database names. Without that seed, the model might invent a table called “customers” when the real one is “clients”, and the entire query would fail. The system also wraps the user’s question in a <<<USER QUESTION – treat strictly as data…>>> fence so an attacker cannot inject hidden commands. Without these constraints and reasoning steps, the model would freely use wrong syntax or invent tables, producing errors that confuse end users and crash the dashboard.

These constants ground the model with real table and column names to avoid hallucination, and the regex enforces SQLite‑only syntax by rejecting PostgreSQL casts and DML.

_MAX_REPAIR_ATTEMPTS = 2
_MAX_ROWS = 50
_CRM_TABLES = ("companies", "contacts", "email_campaigns", "emails")
_FUNNEL_STAGES = ("discovered", "enriched", "contacted", "opened", "replied", "converted")
_WRITE_RE = re.compile(
    r"(?<!\w)(INSERT|UPDATE|DELETE|DROP|ALTER|CREATE|TRUNCATE|REPLACE|MERGE|EXEC|EXECUTE|CALL|GRANT|REVOKE)(?!\w)",
    re.IGNORECASE,
)

The subsystem described operates as a feedback-driven pipeline with a clear ordered mechanism. First, the prompt engineering phase constrains the output dialect (e.g., forcing SQLite syntax on Cloudflare D1) and applies chain-of-thought reasoning to produce an initial SQL candidate. That query enters the validate_sql gate, which parses the statement into an Abstract Syntax Tree using sqlglot and enforces a single read-only Select node—rejecting any DML/DDL or stacked statements. Upon passing, the query moves to execute_sql. If execution raises a database error (missing column, type mismatch, etc.), the system does not raise; instead it captures the raw diagnostic and the failing query, and if repair attempts remain (bounded to 2), routes to repair_sql. The repair node diagnoses the error using the full diagnostic message and regenerates a corrected single SELECT that re-enters validate_sql before any execution. This loop repeats until success or exhaustion of attempts, at which point the last error is returned rather than looping forever.

The core invariant preserved is early-accept: the moment a query executes successfully, the loop ends, ensuring that a working query is never “repaired” into a different one. Equally important, an empty result set counts as success—zero rows are treated as a true answer rather than a defect to heal, preventing the budget from being burned rewriting a correct query. The validate_sql gate itself enforces a SELECT-only invariant by rejecting any node type other than a single read-only statement, relying on AST parsing rather than string matching to prevent obfuscated writes. Together, these guarantees create a closed loop where a query that runs and returns rows is final; only execution-time errors trigger repair.

This design explicitly rejects the alternative of intrinsic self-correction (e.g., the DIN-SQL-style re-reading of SQL against a checklist without executing it). The literature shows that introspective correction contributes only 1–3 percentage points, because the model lacks ground truth to react to—it often “fixes” correct queries or misses real bugs. By using execution-grounded feedback instead, the system receives real, specific signals (“no such column: cust_id” or a type mismatch), which yields substantially better accuracy. The cost avoided is the wasted compute and potential degradation from hallucinated repairs that intrinsic correction would introduce. The bounded loop (maximum 2 repairs) and early-accept termination further prevent cost explosion or indefinite oscillation.

A concrete failure mode is the semantic miss: a query that is syntactically valid, passes validate_sql, executes successfully, and returns rows—but answers the wrong question because it joined the wrong table or misinterpreted the intent. The self-healing loop cannot catch this because the database reported no error. An operator would see a confidently summarized business sentence in the summarize node, along with provenance showing the executed SQL and source tables, but no error signal. The only detection mechanism is a human reading the answer to notice the mismatch. This is the intrinsic limitation of execution-time repair: it fixes what the database rejects, not what the user didn’t ask.

1. Hard Execution Error (Unknown Column/Table)

• Trigger – The generated SQL refers to a column or table name that does not exist in the production schema (e.g. misspelled cust_id or a missing join).
• Guard – The execute_sql node catches the database exception (syntax error, unknown column) and routes the error via the conditional edge to repair_sql. The repair node receives “the failed SQL plus the actual error text”.
• Posture – Fail-soft because the system degrades gracefully: instead of aborting, it retries a corrected query.
• Operator signal – The LangSmith metric agentic_sales.text_to_sql.repair_attempts increments; the error text from D1 (e.g. “no such column: cust_id”) is logged.
• Recovery – Up to _MAX_REPAIR_ATTEMPTS = 2 regeneration cycles. Each repaired SQL re‑enters validate_sql before re‑execution. If all attempts fail, the last error is returned and the run ends.

2. Semantic Miss (Wrong Question Answered)

• Trigger – The SQL executes successfully and returns rows, but answers a different question than intended (e.g. joins the wrong table).
• Guard – None. The context explicitly states: “It cannot fix a query that runs cleanly and answers the wrong question … Only a human reading the answer catches that semantic miss.”
• Posture – Fail-soft (the system continues confidently with an incorrect answer).
• Operator signal – The provenance fields confidence, reason, source (tables_used), and evidence (executed SQL) are all present, but no automated flag signals the semantic error. The metric agentic_sales.text_to_sql.row_count may appear normal.
• Recovery – No automated recovery. A human must review the answer’s evidence and source to detect the mismatch; manual re‑phrasing of the question is required.

3. Empty Result Set (Overconstrained Query)

• Trigger – The SQL runs without error but returns zero rows (e.g. overly restrictive WHERE clause, wrong value literal).
• Guard – The design choice “early‑accept … an empty result set counts as success, not a defect to heal”. The loop ends at the first successful execution; no repair is triggered. The summarize node receives a zero‑row result and uses its fail‑closed template.
• Posture – Fail‑soft (the system produces a correct but uninformative summary instead of aborting).
• Operator signal – The metric agentic_sales.text_to_sql.row_count equals 0. The summary output is: “The query ran successfully but matched no records.”
• Recovery – No retry. The operator can re‑phrase the question to remove constraints; the self‑healing loop does not attempt to loosen WHERE clauses automatically.

4. Write Attempt Blocked by AST Validation

• Trigger – The generated SQL contains a DDL/DML statement (e.g. DROP TABLE, INSERT) or a multi‑statement injection (SELECT 1; DROP TABLE users).
• Guard – The validate_sql node parses the query into an AST using sqlglot and asserts the statement is exactly one Select node. It rejects any Insert, Update, Delete, Drop, Alter, or Create node.
• Posture – Fail‑closed (the write is never executed; the run refuses to proceed to execute_sql).
• Operator signal – The conditional edge from validate_sql routes to repair_sql or END (depending on internal logic). The metric agentic_sales.text_to_sql.repair_attempts may increment if a repair is attempted; otherwise the run ends with an error.
• Recovery – The repair node (repair_sql) attempts to regenerate a safe SELECT (up to _MAX_REPAIR_ATTEMPTS). If no valid SELECT can be produced, the last error is returned; a manual rewrite of the question is needed.

5. Max Repair Attempts Exhausted

• Trigger – After two repair cycles (_MAX_REPAIR_ATTEMPTS = 2), the generated SQL still fails execution (or validation).
• Guard – The loop is bounded by the constant _MAX_REPAIR_ATTEMPTS. The conditional edge after execute_sql checks the attempt count; once exhausted, it routes to END (via the “last error” path) instead of looping again.
• Posture – Fail‑hard (the run terminates with the final error, no partial result).
• Operator signal – The metric agentic_sales.text_to_sql.repair_attempts reaches 2. The run’s provenance returns the last error text as the reason.
• Recovery – The operator must manually inspect the error, adjust the question or schema mapping, and re‑run.

6. LLM Unavailable During Summarization

• Trigger – The summarize node cannot obtain a response from the LLM (network failure, model overload, or timeout).
• Guard – The summarize node has a deterministic fallback: “LLM unavailable → deterministic ‘The query returned N record(s)’”.
• Posture – Fail‑closed (a factual, generic statement is returned instead of a hallucinated figure).
• Operator signal – The metric agentic_sales.text_to_sql.confidence may be lower or absent; the operator sees a non‑LLM‑generated summary in the output.
• Recovery – No retry is attempted. The run completes with the fallback; a human can manually re‑trigger summarization if the model becomes available.

Imagine you’re a chef who receives a vague dish description from a customer. You cook a first version, taste it, and if it’s burnt or missing a spice, you don’t serve it—you fix the mistake and try again. That’s exactly what this system does: it turns an everyday English question into a database query, then tests that query against the real database. If the database spits back an error (like “no such column” or a bad join), the system catches that exact error message and uses it to cook up a corrected query. It keeps trying, but only up to two fixes, because you don’t want to keep tasting forever.

The real mechanism is the self‑healing loop: after an error, the repair_sql node gets the failed query plus the database’s diagnostic text, diagnoses what went wrong, and regenerates a single corrected SELECT. That corrected query must pass the validate_sql gate again before it can run. Two rules keep the loop from hurting correctness: first, as soon as a query runs without error, the loop stops—no rewriting a working recipe. Second, an empty result set counts as success, like a dish that’s bland but intended to be bland. If all attempts fail, the system returns the last error instead of looping forever.

The trickiest point is that the loop cannot fix a query that runs cleanly but answers the wrong question—the database might return rows that look right but actually came from the wrong table. That’s like cooking a perfect steak when the customer asked for pasta. Only a human reading the final answer catches that mistake. Without this subsystem, a single failed attempt would just throw a cryptic database error at the user, leaving them with no useful result and no way to fix it themselves. The self‑healing loop turns that dead end into a guided second chance.

Self-healing loop: repair node re-enters validation after error diagnosis, bounded by _MAX_REPAIR_ATTEMPTS.

_MAX_REPAIR_ATTEMPTS = 2
_MAX_ROWS = 50


# Edges: START → understand_question → identify_tables → generate_sql → validate_sql
# conditional validate_sql → {execute_sql, repair_sql, END}
# conditional execute_sql → {repair_sql, summarize}
# repair_sql → validate_sql
# summarize → END

class GenerateSQLResult:
    sql: Optional[str] = None
    is_final: Optional[bool] = None

def diagnose_and_repair(question, tables, sql, error, config) -> GenerateSQLResult:
    """Repair a failed SQL query using the actual error from the database."""
    # ... logic ...
    if attempt >= _MAX_REPAIR_ATTEMPTS:
        return GenerateSQLResult(..., is_final=True)

The self‑healing loop begins when a query passes the validate_sql gate and reaches execute_sql. If the database raises an error—a missing column, malformed join, or type mismatch—the node captures the raw diagnostic and the failing query. Provided repair attempts remain (the bound is two, per the cost envelope of “at most 2 repair attempts”), the system routes to repair_sql. The repair node receives the failed SQL plus the actual error text, diagnoses the issue, and regenerates a corrected single SELECT statement. This corrected query must re‑enter validate_sql before any execution, ensuring the same safety gate applies. The loop repeats, but as soon as a query executes without an error, it ends immediately via early‑accept. An empty result set is treated as success—"no rows matched that filter" is considered the true answer, not a defect to heal. After the two‑attempt budget is exhausted, the run returns the last error rather than looping forever; the bound itself acts as the circuit breaker.

The invariant the design preserves is that a working query is never "repaired" into a different one. Early‑accept guarantees that the moment a query executes successfully, the loop terminates, so a correct query cannot be overwritten by a later regeneration. This property is explicitly named in the source: “the moment a query executes successfully, the loop ends, so a working query is never ‘repaired’ into a different one.” The system also guarantees that repaired SQL always re‑enters validate_sql before execution, meaning the permission and syntax checks are applied uniformly regardless of how many times a query has been rewritten. Together these rules prevent the loop from introducing correctness hazards or widening the attack surface.

The key trade‑off is rejecting the alternative of treating an empty result set as a failure that should trigger repair. A loop that treated zero rows as a defect would “burn its attempt budget rewriting a correct query.” The chosen design avoids that cost—wasted LLM calls and the risk of turning a semantically accurate empty result into a different, possibly wrong, answer. The alternative would be to treat every empty result as a sign of a flaw (e.g., a value‑linking mistake like 'California' vs 'CA'), but the system explicitly calls out that “no rows matched that filter is usually the true answer.” By counting empty results as success, the loop preserves the correctness of valid empty responses and keeps the repair budget reserved for genuine execution‑time errors.

One concrete failure mode the loop cannot handle is a query that runs cleanly but answers the wrong question—a semantic miss. For example, a SELECT that joins the wrong table executes successfully, returns rows, counts as success, and is then passed to summarize. The summarize node, grounded only in the returned rows, will produce a confident summary with provenance fields (confidence, reason, source, evidence). An operator reading the output would see a plausible but incorrect answer, with no error signal from the database or the loop. The source states: “It cannot fix a query that runs cleanly and answers the wrong question … Only a human reading the answer catches that semantic miss.” The signal the operator sees is a confidently stated but factually wrong business sentence, accompanied by the executed SQL and table names, giving no hint that the query logic was flawed.

1. Hard Execution Error on First Attempt

• Trigger – The LLM-generated SQL references a non‑existent column, table, or uses a malformed join, causing the database to throw a syntax or type‑mismatch exception.
• Guard – The repair node catches the database error text and regenerates a corrected SELECT, which then re‑enters validate_sql before any execution.
• Posture – Fail‑soft – the run continues with one of the allowed repair attempts; no data is lost and the graph does not abort.
• Operator signal – The LangSmith metric agentic_sales.text_to_sql.repair_attempts increments by 1; the environment logs the raw database error text fed into the repair node.
• Recovery – The loop retries with the repaired SQL. If that second attempt succeeds, the system proceeds to summarize. Otherwise, it falls into the exhaustion case below.

2. All Repair Attempts Exhausted

• Trigger – After the initial generation and up to two repair attempts, every candidate SQL fails with a hard execution error (or fails validate_sql itself).
• Guard – The loop is bounded by a fixed attempt budget (at most 2 repairs). When exhausted, the run does not loop again; it returns the last error message.
• Posture – Fail‑hard – the graph stops executing; no query result or summary is returned to the caller.
• Operator signal – The metric agentic_sales.text_to_sql.repair_attempts shows the maximum value (2). The API response carries the plain‑text error from the last failed attempt instead of a result set.
• Recovery – No automatic recovery. A human must inspect the error text, correct the SQL manually, and re‑run the request.

3. Semantic Miss (Runs but Answers the Wrong Question)

• Trigger – The LLM generates a syntactically valid SELECT that executes without error but joins the wrong tables, uses incorrect aggregation, or misapplies filters, so the returned rows do not answer the user’s intent.
• Guard – None. The context explicitly states: “It cannot fix a query that runs cleanly and answers the wrong question … only a human reading the answer catches that semantic miss.” The early‑accept rule treats this as success because the query ran without a database exception.
• Posture – Fail‑soft (from the system’s perspective) – the run completes, produces a summary and provenance fields (confidence, reason, source, evidence), but the output is factually incorrect.
• Operator signal – The metric agentic_sales.text_to_sql.confidence may be high, and agentic_sales.text_to_sql.row_count reports a non‑zero number. No error log appears; the only signal is the user’s surprise that the answer does not match expectations.
• Recovery – No automatic recovery. The user must manually re‑phrase the question, or the operator can inspect the evidence (the executed SQL) to detect the logical error and then discard the run.

4. LLM Unavailable During Summarization

• Trigger – The summarize node attempts to call the LLM to turn the result rows into a business sentence, but the model API times out, returns a 503, or otherwise fails.
• Guard – The summarize node contains a deterministic fallback: “LLM unavailable → deterministic ‘The query returned N record(s)’”.
• Posture – Fail‑soft – the answer is degraded to a simple count (no interpretive sentence), but the run still completes and returns a result.
• Operator signal – The evidence field shows the executed SQL; the reason field will contain the deterministic phrase (e.g., “The query returned 12 record(s)”), not a narrative explanation. No LangSmith metric specifically flags the LLM unavailability, but the confidence value may be lower because the LLM did not contribute.
• Recovery – The fallback is immediate and automatic. No retry is attempted; the deterministic count is returned. The operator may later re‑run the same query when the LLM service is restored.

Think of it like a ticket checker at a secure facility. The checker's job is to only let people with the right pass through—in this case, the only allowed pass is a "read-only" pass. This whole subsystem exists to make sure that when a computer automatically writes a database query, nothing dangerous happens to the data.

When a query arrives, the checker first does a quick test: after removing any extra parentheses, the command must start with either "select" or "with"—no "insert" or "drop" allowed. This is the leading‑head check inside validate_sql. But a clever trick could hide a dangerous command inside a comment or a stacked statement, so the checker then uses a deeper scanner called an AST parser (like a barcode reader) that understands the query exactly as the database will. That scanner rejects any statement that tries to modify, delete, or drop data. Crucially, even if a previous step repairs a broken query, the repair must pass through this same gate again, so a fix never accidentally widens permissions.

The tricky part is making the quick test precise. An older version used a simple word‑boundary match that wrongly rejected legitimate column names like lock or merge. To fix that, the code anchors the match to a statement boundary—start of the query, a semicolon, or an opening parenthesis—so it only catches truly forbidden commands while letting normal identifiers through. Without this carefully anchored gate, a single slipped‑through "delete" or "drop" could erase customer records or entire tables, and a beginner would only discover the damage when the data is gone forever.

The SELECT-only gate uses sqlglot AST parsing and a statement-boundary-anchored regex.

import re


_WRITE_RE = re.compile(
    r'(?<![;\s(])'
    r'(?:'
    r'INSERT|UPDATE|DELETE|DROP|ALTER|CREATE|TRUNCATE|REPLACE|MERGE|CALL|EXEC|EXECUTE|LOAD|UNLOAD|COPY|VACUUM|REINDEX|GRANT|REVOKE'
    r')(?=\s|$)',
    re.IGNORECASE
)

def validate_sql(sql: str) -> bool:
    """Reject non-SELECT statements via AST + secondary regex."""
    import sqlglot
    parsed = sqlglot.parse(sql)
    if len(parsed) != 1:               # multiple statements
        return False
    stmt = parsed[0]
    if stmt.args.get('this') is None or stmt.args['this'].key != 'select':
        return False
    # Regex catch for statement-beginning keywords (defense in depth)
    if _WRITE_RE.search(sql):
        return False
    return True

The subsystem operates as a layered pipeline with a strict ordered mechanism. First, every generated SQL candidate enters validate_sql, an AST‑based gate that parses the query exactly as the database will, rejecting any statement that is not a single SELECT (i.e., no INSERT, UPDATE, DELETE, or DROP). Only after passing this gate does the query reach execute_sql. If execution raises a database error — a missing column, malformed join, or type mismatch — the repair node repair_sql receives the failed SQL and the raw error text, diagnoses the issue, and regenerates a corrected single SELECT. That repaired query must re‑enter validate_sql before it can run again; this re‑validation ensures that no repair ever widens permissions. The loop is bounded (two attempts), and after exhaustion the run returns the last error rather than looping forever.

The design preserves a single invariant: the SELECT‑only gate is the hard backstop. It is the only path to execution, and repaired SQL always passes through it. This guarantee is enforced by the AST parser’s exact understanding of the query structure, not by text scanning. Because the gate composes with prompt‑injection fencing — the untrusted user question is fenced in an explicit “treat strictly as data” block with zero‑width and bidi characters stripped — even if an attacker defeats the fence, the gate refuses any non‑SELECT statement. The result is that permissions cannot be widened by any generated SQL, whether from the initial candidate, a repair, or an injection attempt.

The key trade‑off is abandoning a simpler regex‑based word‑boundary check (a \b match) in favour of the AST gate. The obvious alternative — string‑searching for keywords like DROP or INSERT — was rejected because it produced false rejections on legitimate identifiers. For example, a word‑boundary regex wrongly fired on SELECT comment FROM contacts, REPLACE(name,'a','b'), or a column named lock or merge, blanking all into empty rejections. A false rejection is as much a defect as a false acceptance, just quieter. By investing in AST parsing, the subsystem avoids the cost of silently discarding valid queries and the debugging time wasted on spurious errors. The AST gate is precise: it anchors the check to statement boundaries (start‑of‑string, ; separator, or the ( of a data‑modifying CTE), not to bare word boundaries, so the false‑rejection rate drops to near zero.

A concrete failure mode that an operator would see is the word‑boundary false rejection described above. A query such as SELECT comment FROM contacts — a perfectly valid SELECT — would be rejected by a naive regex check and never reach execution. The operator would observe an empty rejection: no rows returned, no database error, just a silent failure. In the current subsystem, because validate_sql uses an AST parser, that same query passes the gate and runs correctly. The signal the operator would see is a successful query execution, and the absence of unexplained “empty rejection” logs. The only failure mode exposed is a genuine database error (e.g., missing column), which triggers the self‑healing loop, and the operator would see the error text captured and a repair attempt logged before eventual success or exhaustion.

Semantic Miss: Empty Result Set from a Logically Wrong Query

• Trigger — The LLM generates a SELECT that passes AST validation and executes successfully, but returns zero rows because the LLM misunderstood the user's intent (e.g., wrong filter value, mismatched column name that exists but is unrelated). The system’s early-accept policy treats an empty result as success (not a defect to heal), so the loop ends immediately.
• Guard — No guard is described in the source for this failure. The architecture explicitly acknowledges that it “cannot fix a query that runs cleanly and answers the wrong question.” The only mitigation is the built-in summary that reports “The query ran successfully but matched no records.”
• Posture — Fail-soft. The run completes, a summary is generated, and the output is returned. The system does not abort or refuse – it simply reports zero records. The user sees a confident but incorrect answer.
• Operator signal — The LangSmith metric agentic_sales.text_to_sql.row_count records 0. The summary field reason will reflect the empty result. No error log appears; the operator sees a successful run with zero rows.
• Recovery — No automated recovery. A human must read the answer, recognize the semantic miss, and re‑query with corrected wording. There is no retry; the attempt budget (maximum 2 repairs) was never consumed because the query succeeded.

Summarization Failure Due to LLM Unavailability

• Trigger — After a successful query execution, the summarize node attempts to call an LLM to generate a natural‑language summary. The LLM call fails (timeout, rate limit, model down).
• Guard — The deterministic fallback explicitly described: “LLM unavailable → deterministic ‘The query returned N record(s)’.” This fallback is part of the summarize node itself.
• Posture — Fail-closed. The system refuses to fabricate a summary; instead it returns a safe, factual statement (“The query returned N record(s)”). The run completes without an LLM‑generated explanation.
• Operator signal — The LangSmith metric agentic_sales.text_to_sql.confidence is absent (or set to a default low value) because the LLM did not produce a confidence score. The reason field will contain the deterministic string rather than a natural‑language sentence. An observer may see a logging entry for the failed LLM call (not specified in source, but implied).
• Recovery — No retry. The deterministic fallback is used immediately. The downstream provenance fields (source, evidence) remain intact. A human operator can later re‑run the same query to attempt summary generation again.

Deterministic Builder Query Failure Due to Schema Drift

• Trigger — A structurally known query, such as one emitted by build_funnel_queries(vertical) or build_touch_history_query(contact_id), fails during validate_sql or during execution because the underlying CRM schema has changed (e.g., a required table or column was renamed or dropped). These queries are fixed SELECT COUNT(*) or SELECT statements, not generated by the LLM.
• Guard — No guard is described in the source for deterministic builder failures. The builder passes through validate_sql once, and if that gate fails, there is no repair loop (the self‑healing loop is reserved for ad‑hoc LLM queries only). The source explicitly separates the two paths: “the decision framework: if the query is structurally known, use a builder; if it is genuinely ad‑hoc, use the self‑healing loop.”
• Posture — Fail-hard. The run aborts. validate_sql will reject the query, or execution will throw an error. The run returns the final error without attempting re‑generation.
• Operator signal — The error from validate_sql (e.g., “table ‘leads’ does not exist”) or from the database is the primary signal. The LangSmith metric agentic_sales.text_to_sql.repair_attempts will be zero because no repair was invoked. The source (tables_used) may still be populated.
• Recovery — Manual intervention required: update the schema in the registry or modify the deterministic builder code to align with the current database. No automated retry occurs.

AST False Rejection Due to Dialect Incompatibility

• Trigger — The LLM generates a valid SELECT that uses a SQL construct (e.g., LIMIT with OFFSET syntax, or a JSON function) supported by Cloudflare D1 (SQLite) but not correctly parsed by the sqlglot‑based AST validator used inside validate_sql. The validator incorrectly classifies the statement as malformed or as containing a disallowed node type.
• Guard — The only guard is validate_sql itself, which rejects the query. No alternative AST parser or dialect‑specific fallback is mentioned. The rejection feeds the error text back to the repair node, which then attempts to regenerate a corrected SQL. The self‑healing loop is the only channel for recovery.
• Posture — Fail-soft (via repair loop). The run does not abort immediately; the error is consumed by the repair loop. Because the repair node receives the actual error text, it may simplify the query to avoid the unrecognized construct. After at most 2 repair attempts, if the same dialect issue persists, the run fails hard.
• Operator signal — The LangSmith metric agentic_sales.text_to_sql.repair_attempts increments. The reason in provenance may show the error from validate_sql. An observability log could capture the false‑positive rejection (not explicitly named in source, but inferred).
• Recovery — The repair loop runs up to 2 attempts. Each repaired SQL re‑enters validate_sql. If the issue is not fixable within the loop, the run returns the last error. Manual action would require updating the AST parser configuration or the validator’s SQL dialect support.

Repair Loop Exhaustion After Repeated Execution Errors

• Trigger — The initial LLM‑generated SQL fails execution (e.g., a syntax error that validate_sql missed, a type mismatch, a missing column that exists in schema but the database rejects due to collation). The repair node receives the failed SQL and the error text, and regenerates a corrected query. That corrected query also fails execution. This repeats until the attempt budget is exhausted.
• Guard — The circuit breaker is the explicit bound on repair attempts: “at most 2 repair attempts” (stated in the cost envelope). After the attempts are exhausted, “the run returns the last error rather than looping forever.” The repair node itself is the guard that tries to fix, but the ultimate guard is the maximum‑attempts check.
• Posture — Fail-hard. After 2 repair attempts, the run aborts and returns the final error. No fallback summary is generated (the summarize node is not reached because no query succeeded).
• Operator signal — The LangSmith metric agentic_sales.text_to_sql.repair_attempts will be 2. The evidence field (executed SQL) will contain the last failed query. The error text from the last execution is returned to the caller.
• Recovery — No automated recovery. The operator receives the final error and must either rewrite the question, adjust the schema, or debug the failure manually. The system does not escalate to a human‑in‑the‑loop.

Think of two people giving directions to the same park. One says "turn left at the big oak tree," the other says "turn left at the old oak." Both get you there, but the words differ. This part of the system measures whether the SQL query it writes is truly correct, even if the wording is different — it’s a yardstick for truth, not for exact phrasing.

To do that, the system uses two yardsticks. The first is exact-set-match accuracy, which compares the query’s structure clause by clause but deliberately ignores the specific values inside — so 'California' and 'CA' are treated as the same, just like "big oak" and "old oak" both point to the same tree. The second yardstick is execution-based: it runs the query and checks what data actually comes back. These measurements are tested on standard benchmarks like Spider, which has over ten thousand questions across two hundred databases covering many domains.

The trickiest part is that exact-set-match ignores literal values. That might sound like it makes the test too easy, but it’s a deliberate rule: it catches real mistakes like a missing GROUP BY or a wrong join key, while not punishing trivial differences in how you write a constant. Without this trick, a query with state = "CA" would be marked wrong if the answer key says state = "California", even though both return identical results. That false failure would hide real bugs and make the system seem unreliable.

Without this subsystem, users would get incorrect data in their dashboards because no one could tell if the SQL was truly right. The system would be a black box producing answers you can’t trust.

Constants enforce a bounded repair loop aligned with benchmark evaluation practices where empty result is success.

_MAX_REPAIR_ATTEMPTS = 2
_MAX_ROWS = 50


# An empty result set counts as success, not a defect to heal.

The subsystem operates as a closed-loop pipeline with precise ordering. First, every generated query—whether from the initial intent step or from a repair—must pass validate_sql, a SELECT-only gate enforced by parsing the statement with sqlglot into an Abstract Syntax Tree and rejecting any node that is not a single read-only Select or a read-only CTE. Only after that gate does the query reach execute_sql. If execution raises a hard error (e.g., syntax error, missing column), the raw diagnostic message and the failing query are captured. Provided the attempt budget (bounded to 2) is not exhausted, the system routes to repair_sql, which receives the failed SQL and the actual error text, diagnoses the issue, and regenerates a corrected single SELECT. That regenerated query re-enters validate_sql before any execution, closing the loop. If execution succeeds (including producing zero rows), the loop terminates immediately—this is the early-accept rule. After the two repair attempts are exhausted without success, the run returns the last error rather than looping indefinitely.

The core invariant preserved by this design is SELECT-only enforcement as the sole path to execution. Each repair—even if it fixes syntax or semantics—must pass validate_sql again, meaning a repair can never widen permissions or introduce a write statement. Combined with the early-accept rule, a correct working query is never replaced by a different one. The system guarantees that no DML or DDL statement can reach the database, and that the execution pipeline is idempotent with respect to correct queries: once a query runs successfully, the loop stops.

The key trade-off is between expressiveness and bounded repair cost. The design rejects the alternative of a single-shot LLM translation with no validation, which would suffer the 30–50% failure rates typical in enterprise contexts. Instead, it accepts a small, bounded overhead (at most two repair attempts) to recover from execution-time errors like bad column names or malformed joins. The cost this avoids is an unbounded or ambiguous loop: early-accept prevents unnecessary repairs on correct queries, and treating empty results as success (not as a defect) avoids burning the attempt budget on legitimate “no rows matched” answers. The explicit bound (2) acts as a circuit breaker, ensuring the system never loops forever on an unfixable error.

A concrete failure mode is a hard execution error such as a missing column name. The database raises an exception like "no such column: cust_id". The operator would see this exact diagnostic message recorded by the system as the raw error text, because the pipeline captures the full SQLSTATE code and diagnostic message from the database before routing to repair_sql. If both repair attempts fail to produce a valid SELECT that passes validate_sql and executes without error, the run returns that last captured error as its final output—no silent fallback or fabricated result. The human operator sees the raw database error, exactly as the database reported it, alongside the failed original query and any repair attempts.

Benchmark scores mislead about real-world performance

• Trigger – The system is evaluated only on public cross-domain benchmarks (Spider, SelECT-SQL) where it scores 84.2%. In contrast, enterprise CRM schemas with business-specific jargon and denormalised tables produce failure rates of 30‑50%, as noted in “What Most Articles Get Wrong”. The operator trusts the benchmark figure.
• Guard – The source explicitly cautions: “The public benchmark figures (Spider; SelECT‑SQL at 84.2%) justify the loop’s shape, not a specific accuracy number for any private schema.” This is a textual warning, not a runtime guard; no code-level handler exists.
• Posture – fail‑soft: the system continues to execute queries, but performance degrades silently when deployed on a private schema.
• Operator signal – Production logs show agentic_sales.text_to_sql.repair_attempts frequently hitting the bound of 2, and agentic_sales.text_to_sql.confidence values are low (below 0.5) on many queries. The contrast between the 84.2% benchmark and the observed retry rate is the operator’s signal.
• Recovery – Create a domain-specific test set using the deterministic build_funnel_queries (which emits fixed SELECT COUNT(*) queries) and compute_funnel_report as a ground-truth baseline. Re‑evaluate the system against that private schema; until that is done, the operator must manually audit every failed query.

Exact-set-match accuracy misclassifies semantically equivalent queries

• Trigger – Two SQL queries that produce identical result sets (same rows, same columns) are syntactically different (e.g., different JOIN order, subquery vs. CTE). exact-set-match accuracy compares clause by clause ignoring literal values, and gives them different scores.
• Guard – The system also uses a second metric, Spider’s execution accuracy metric (the source states “Spider defines two metrics”; the second compares actual result sets). That metric would correctly score both queries as correct, partially compensating for the flaw.
• Posture – fail‑soft: the exact-set-match score under-reports true accuracy, but the execution accuracy metric provides a fallback that keeps the evaluation from being completely misleading.
• Operator signal – The operator sees a persistent gap between the reported exact-set-match accuracy and the execution accuracy metric. For the same test set, the execution accuracy is several points higher.
• Recovery – Use the execution accuracy metric as the primary evaluation score. Flag any query where the two metrics disagree for manual review; no further automated recovery is implemented.

Empty result set leads to false negative in execution accuracy

• Trigger – The system generates a logically correct SQL query, but the evaluation database state is stale or lacks matching records (e.g., test data has been cleared). The query returns zero rows, and the gold standard expects a non‑empty result set. The execution accuracy metric marks the query as incorrect.
• Guard – No runtime guard in the evaluation subsystem detects stale test state. In the execution subsystem summarize treats an empty result as success (“The query ran successfully but matched no records”), but the evaluation metric is comparing against a fixed gold set, not against the success/failure semantics.
• Posture – fail‑soft: the evaluation score fluctuates from run to run depending on database state. The system continues to evaluate, but the numbers are not reproducible.
• Operator signal – The metric agentic_sales.text_to_sql.row_count is zero for many queries on one run, while on a previous run with fresh data the same queries returned rows. Evaluation scores drop sharply without any code change.
• Recovery – Freeze the database to a known snapshot before evaluation. The operator must re‑load the canonical test data and re‑run the evaluation pipeline; no automated healing exists for this inconsistency.

Literal-value ignorance in exact-set-match hides subtle errors

• Trigger – exact-set-match accuracy ignores literal values by design (source: “compares the query clause by clause but ignores literal values”). A query that selects 'John' instead of 'john' (different casing) is scored correct if the structural clauses match, even though the result set differs. Conversely, a query with correct literals but different ordering of conditions is scored incorrect.
• Guard – There is no guard inside the exact-set-match metric itself. The execution accuracy metric (the second Spider metric) catches result‑set mismatches, but the source does not indicate that the system automatically falls back to it for these cases.
• Posture – fail‑soft: the metric outputs a score that may be artificially high (false positives) or low (false negatives) on literals. The system continues to produce the metric.
• Operator signal – Manual spot‑checks reveal queries that pass exact-set-match but return different data. The operator sees a gap between a human‑judged accuracy and the metric’s output.
• Recovery – Switch to execution accuracy as the sole official metric. If the system’s provenance fields (evidence – the executed SQL) are logged, the operator can re‑run the SQL against the golden database to verify results. No automated correction of the metric itself exists.