Database Agents That Know When They're Wrong

A SQL query runs without errors, returns a scalar number, and the number is wrong because an unindexed join fanned out and multiplied an aggregate sum.

No database alert triggers. The number flows straight into an analytics report. Somebody catches it weeks later, after decisions have already been made on it.

An agent that returns a clean, confident, wrong result set is worse than one that crashes. The crash at least tells you something is broken.

So the design goal is not accuracy. It is making the failures that stay silent become loud.

Chapter 0: Defining silent failure in query agents

A database query fails in three ways:

1. Syntax or execution failure: the query is invalid SQL. The database engine returns an error code, which is easy to catch and retry.
2. Intent mismatch: the SQL is valid, but answers a different question than intended due to ambiguous schema definitions.
3. Semantic shape error: the SQL runs against the intended schema, but unexpected null semantics, fan-out joins, or missing filters silently distort row counts or aggregates.

Only the first failure mode signals itself. The other two return clean tables. Guarding against silent failure requires deterministic application scaffolding, not longer system prompts.

Step 1: Benchmark metrics and annotation noise

On the BIRD leaderboard, read on 8 September 2026, the human baseline is 92.96% execution accuracy (measured with database engineers and students). Top automated pipelines reach roughly 82% on the test set:

These benchmarks provide external evidence strings alongside queries. Real production environments rarely provide pre-annotated schema hints.

On the Spider 2.0 leaderboard, read the same day, standard models show significant degradation:

Tengjun Jin, Yoojin Choi, Yuxuan Zhu and Daniel Kang's CIDR 2026 paper Text-to-SQL Benchmarks are Broken, circulated as the preprint Pervasive Annotation Errors Break Text-to-SQL Benchmarks and Leaderboards in January 2026, found annotation error rates of 52.8% on BIRD Mini-Dev and 66.1% on Spider 2.0-Snow. The Spider figure is worth reading twice: the earlier preprint put it at 62.8% and the published version at 66.1%, which is to say two careful passes over the same benchmark disagreed by three points about how wrong its labels are. When benchmark labels contain flawed assumptions around timestamp casting and null handling, leaderboard ranks measure annotation noise alongside genuine capability.

Select architectural patterns by evaluating ablation studies and failure modes within specific domain schemas rather than leaderboard rankings.

Step 2: High-value guardrails for query generation

Ablation studies on published Text-to-SQL pipelines point at which architectural layers return the most per unit of engineering effort:

1. AST parsing and syntax verification: immediate, local validation before database execution.
2. Execution error feedback loops: capturing database engine errors and supplying them to a repair agent.
3. Empty result heuristics: treating zero-row outputs as potential filter mismatches rather than definitive answers.
4. Candidate sampling and selection: generating multiple candidate queries and scoring them against schema constraints.
5. Assertion checks over output rows: validating row ranges, uniqueness, and aggregate boundaries.

AST parsing provides an effective first gate against unsafe queries. sqlglot parses SQL into a dialect-aware AST:

# sql_guard.py
"""AST-level checks over model-generated SQL."""
 
from sqlglot import exp, parse_one
from sqlglot.errors import ParseError
 
ALLOWED_TABLES = {
    "analytics.v_orders",
    "analytics.v_customer",
    "analytics.v_product",
}
 
FORBIDDEN = (
    exp.Insert, exp.Update, exp.Delete, exp.Drop, exp.Create,
    exp.Alter, exp.Merge, exp.Grant, exp.Command,
)
 
MAX_ROWS = 5_000
 
class RejectedQueryError(Exception):
    """Exception containing feedback for model query revision."""
    pass
 
def guard_sql(sql: str, dialect: str = "postgres") -> str:
    """Parses SQL and enforces structural safety rules via AST analysis."""
    try:
        tree = parse_one(sql, read=dialect)
    except ParseError as err:
        raise RejectedQueryError(f"SQL parsing failed: {err}") from err
 
    if not isinstance(tree, exp.Select):
        raise RejectedQueryError(f"Root AST node is {type(tree).__name__}, expected Select.")
 
    for node in tree.walk():
        if isinstance(node, FORBIDDEN):
            raise RejectedQueryError(f"Prohibited operation detected: {type(node).__name__}")
 
    # CTE names are valid identifiers that will not exist in the base table allowlist
    cte_names = {c.alias_or_name for c in tree.find_all(exp.CTE)}
    for table in tree.find_all(exp.Table):
        name = table.sql(dialect=dialect, identify=False)
        if name in cte_names:
            continue
        if name not in ALLOWED_TABLES:
            raise RejectedQueryError(f"Table '{name}' is not in the allowed schema list.")
 
    limit = tree.args.get("limit")
    if limit is None or int(limit.expression.this) > MAX_ROWS:
        tree = tree.limit(MAX_ROWS)
 
    return tree.sql(dialect=dialect)

Walking the AST and checking an explicit allowlist prevents circumvention techniques that bypass naive regex matchers.

Step 3: Enforce safety at the database engine layer

Application-level guards have edge cases. Put the security boundary where the model cannot reach it: PostgreSQL role permissions and connection parameters.

-- roles.sql
CREATE ROLE llm_agent LOGIN PASSWORD 'secure_agent_password';
 
ALTER ROLE llm_agent SET default_transaction_read_only = on;
ALTER ROLE llm_agent SET statement_timeout = '10s';
ALTER ROLE llm_agent SET idle_in_transaction_session_timeout = '15s';
ALTER ROLE llm_agent SET lock_timeout = '2s';
ALTER ROLE llm_agent CONNECTION LIMIT 8;
 
REVOKE ALL ON SCHEMA public FROM llm_agent;
GRANT USAGE ON SCHEMA analytics TO llm_agent;
 
-- Grant access to explicitly curated analytical views only
GRANT SELECT ON analytics.v_orders TO llm_agent;
GRANT SELECT ON analytics.v_customer TO llm_agent;
GRANT SELECT ON analytics.v_product TO llm_agent;
 
-- Pre-execution cost check:
-- Run EXPLAIN before running the real query. Reject queries exceeding cost thresholds.
EXPLAIN (FORMAT JSON)
SELECT c.region, sum(o.amount)
FROM analytics.v_orders o
JOIN analytics.v_customer c ON c.customer_id = o.customer_id
GROUP BY 1;

With default_transaction_read_only = on, any write operation emitted by the model triggers PostgreSQL error SQLSTATE 25006 (read_only_sql_transaction) regardless of prompt phrasing.

Running an EXPLAIN query before execution catches cartesian products and runaway table scans before they consume database resources.

Step 4: Classify query ambiguity before code generation

Most wrong answers come from ambiguous business terms, not invalid SQL:

• Metric definitions: whether "revenue" refers to gross bookings or net settled funds.
• Grain and deduplication: using COUNT(*) instead of COUNT(DISTINCT user_id) across joined tables.
• Time boundaries: inclusive versus exclusive date ranges and timezone conversions.
• Soft deletes: querying underlying tables without filtering for deleted_at IS NULL.

Define an explicit output schema that allows the agent to request clarification rather than guessing:

# contract.py
from dataclasses import dataclass
from typing import Literal, Optional, List, Dict, Any
 
Outcome = Literal["answer", "clarification_request", "refusal"]
 
@dataclass(frozen=True)
class Answer:
    kind: Outcome = "answer"
    sql: str = ""
    rows: Optional[List[Dict[str, Any]]] = None
    row_count: int = 0
    checks_passed: Optional[List[str]] = None
 
@dataclass(frozen=True)
class ClarificationRequest:
    kind: Outcome = "clarification_request"
    question: str = ""
    ambiguity_class: str = ""  # metric, grain, time_window, soft_delete
    plausible_interpretations: Optional[List[str]] = None
 
@dataclass(frozen=True)
class Refusal:
    kind: Outcome = "refusal"
    reason: str = ""
    attempted_sql: Optional[str] = None
 
QueryResult = Answer | ClarificationRequest | Refusal

Track the rate of clarification requests and refusals over time. If an agent answers 100% of user queries without ever requesting clarification, its ambiguity detection is non-functional.

Step 5: Screen result sets against data sanity assertions

Run lightweight programmatic assertions over returned data before passing outputs to downstream consumers:

# plausibility.py
from dataclasses import dataclass
from typing import List, Dict, Any
 
@dataclass
class AnomalyWarning:
    check_name: str
    message: str
 
def screen_result_rows(
    rows: List[Dict[str, Any]], 
    table_total_rows: int, 
    expected_scalar: bool = False
) -> List[AnomalyWarning]:
    """Evaluates result set shapes for common analytical failure modes."""
    warnings: List[AnomalyWarning] = []
    n = len(rows)
 
    if n == 0:
        warnings.append(AnomalyWarning("empty_set", "Query returned 0 rows. Verify filter values."))
 
    if n == table_total_rows and table_total_rows > 100:
        warnings.append(AnomalyWarning("unfiltered", f"Row count ({n}) equals full table size."))
 
    for row in rows:
        for col, val in row.items():
            if any(term in col for term in ["_pct", "_rate", "_share"]) and val is not None:
                try:
                    num = float(val)
                    if not (0.0 <= num <= 100.0 or 0.0 <= num <= 1.0):
                        warnings.append(AnomalyWarning("range_violation", f"Column '{col}' value {val} out of percentage bounds."))
                except ValueError:
                    pass
 
    if expected_scalar and n > 1:
        warnings.append(AnomalyWarning("cardinality_mismatch", f"Expected scalar value, received {n} rows."))
 
    return warnings

Connecting these components creates an execution graph with verification loops:

Step 6: Avoid treating document stores as relational SQL

Generating queries for document stores like MongoDB presents distinct failure modes:

1. Implicit schemas: missing relational foreign keys makes identifying join paths harder.
2. Polymorphic documents: fields can vary across records, causing aggregation operations to drop data unexpectedly.
3. Pipeline stage order sensitivity: $unwind on a non-existent field drops documents silently without throwing an exception.
4. Negative query semantics: {status: {$ne: "inactive"}} matches documents where status is missing entirely.

When this is the wrong choice

• A human reads every result before anyone acts on it. In exploratory analysis the analyst is already the sanity check. The ambiguity classifier costs a clarification round trip on questions they would have refined anyway.
• The question set is fixed. If the queries are a known set of reports, write the SQL once and let the model pick a report ID. AST allowlists and EXPLAIN gates exist to contain generated SQL. Do not generate SQL you did not need to generate.
• Nobody has written down the metric definitions. The clarification path only works when someone can answer "gross bookings or net settled funds". Ship the ambiguity classifier without that, and it stalls on every question instead of guessing on some.
• The latency budget is tight. The EXPLAIN pass, the sanity screen, and any repair loop each add a round trip before the caller sees a row.

Implementation checklist

1. Configure read-only database users with strict connection limits and statement timeouts.
2. Parse model-generated queries using AST libraries like sqlglot against table allowlists.
3. Run EXPLAIN queries to reject runaway cartesian products before execution.
4. Catch zero-row and full-table result anomalies before presenting data to users.
5. Provide explicit schema options for query refusal and clarification.