Natural language SQL needs an answerability contract, not just a query

A syntactically valid SQL query can still be the wrong answer.

The user asks, “How many active customers do we have?” The system finds a customers table, guesses that status = 'active' is authoritative, counts every tenant, ignores trial accounts and timezone boundaries, and returns one confident number.

Natural language SQL needs an answerability contract that decides whether the system has enough governed evidence to answer at all.

Query generation is only one step

Turning text into SQL solves the translation problem. It does not resolve business meaning, identity, scope, freshness, completeness, or whether the requested conclusion is supported by the available source.

A production workflow should treat the possible outcomes as a typed set:

• answer: the request is defined, authorized, current, and complete enough;
• clarify: one or more required dimensions are ambiguous or missing;
• refuse: the user or workflow is not authorized for the requested operation;
• partial: a bounded subset is useful, and its limitations are explicit;
• stale: the source or schema is outside the approved freshness window;
• unknown: the available evidence cannot support the requested conclusion.

The goal is not to maximize the number of questions answered. It is to maximize useful answers without hiding uncertainty or crossing authority boundaries.

Define the answerability contract

For each supported question class, define:

• purpose and intended decision;
• approved metric or business definition and version;
• grain, dimensions, filters, time window, and timezone;
• authenticated user, tenant, role, environment, and region;
• approved sources, views, fields, and database identity;
• freshness, completeness, precision, and minimum sample requirements;
• row, byte, duration, concurrency, and cost limits;
• allowed result states and required evidence fields.

This contract can be implemented as workflow configuration, semantic metadata, approved tools, policy, and validation rules. It must live outside the model's conversational memory.

Start with the schema boundary in natural language SQL needs schema context, then add the business and evidence layers.

Resolve meaning before generating SQL

“Active customer” might mean a paying account, an account with a current subscription, a user who logged in recently, or an organization with usage above a threshold. Several definitions can be internally consistent and produce different numbers.

The system should resolve the request against an approved semantic definition. If no definition exists, or two definitions match, the correct outcome is clarification—not a plausible guess.

Return the metric name and version with the answer. This lets a reviewer distinguish a data change from a definition change.

Keep identity and scope structural

The model should not infer tenant, environment, or database role from the last chat message. Those values come from authenticated runtime context and policy.

Effective authority should be the narrowest intersection of the human user, organization, role, workflow, tool, environment, and data source. Enforce it through approved views, database roles, row-level policy, or a trusted service layer.

When required scope is absent, return clarify. When the requested scope is not allowed, return refuse. Missing context and denied access are not the same state.

Make zero, empty, missing, and unknown different

An empty result set can mean no matching records. It can also mean the source contains no rows, the time partition has not arrived, the tenant filter was wrong, a join removed unmatched data, the upstream sync failed, or the user cannot see the records.

The query result needs coverage evidence:

• source and observed time;
• requested and available time range;
• rows scanned or considered where observable;
• rows returned and omitted;
• missing partitions or unavailable dependencies;
• authorization and redaction outcome.

Only return a numeric zero when the contract proves the population was observed and no records matched.

Freshness is part of answerability

A correct calculation over yesterday's snapshot may not answer a question about “right now.” Define the maximum source age for each workflow and attach the observed timestamp to every result.

When the source is old but still useful, return a stale or explicitly bounded partial result. When freshness is essential to the decision, do not silently substitute the last known value.

Cache behavior, invalidation, and last-good results need the same treatment. See database connector freshness and invalidation.

Completeness must survive pagination and limits

An AI SQL assistant can receive the first 100 rows, sort them, and confidently describe the “top” customer even though 50,000 rows exist. A bounded response is safe only when the bound is visible.

Include total count where safely available, page or cursor state, applied limit, ordering, truncation, and whether aggregation occurred before or after the limit. If the requested conclusion requires the full population, a truncated result is not answerable.

The correct response may be a narrower clarification, an aggregate query, or a partial result with no global claim.

Validate the result, not only the SQL

Before rendering an answer, check:

1. the query used the approved source and metric version;
2. identity, tenant, environment, and time scope match the contract;
3. row, byte, duration, and cost limits held;
4. freshness and schema versions remain valid;
5. the result is complete enough for the requested conclusion;
6. redaction and precision rules ran before model context;
7. the final wording preserves partiality, staleness, and caveats.

A SELECT statement is not proof that these invariants held.

Use an evidence envelope

A useful answer should carry structured evidence alongside the display text:

• outcome state: answer, clarify, refuse, partial, stale, or unknown;
• metric and contract versions;
• source, schema, tenant, environment, and database role references;
• normalized filters, grain, timezone, and time boundary;
• observed time, row count, truncation, and redaction status;
• policy decision, tool version, and trace ID.

The MCP result-provenance envelope provides a deeper implementation pattern.

Test every result state deliberately

Build a replay set that contains more than clean questions:

• a defined metric with complete, current data;
• an ambiguous metric with two plausible definitions;
• a missing tenant or timezone;
• a cross-tenant and restricted-field request;
• a zero result over a proven population;
• an empty result caused by a missing partition;
• stale source data and a stale schema;
• truncated rows for a question requiring a global ranking;
• a timeout, cancellation, and unavailable dependency;
• prompt injection inside permitted text data.

Assert the result state and evidence, not just the prose. A system that always returns an answer has failed the answerability test.

Concrete example: monthly revenue

A user asks for this month's revenue compared with last month.

The contract resolves recognized revenue definition v4, legal entity, tenant scope, accounting timezone, closed-versus-open period behavior, approved finance view, currency normalization, freshness threshold, and required completeness.

If the current period is still open, the result can return a bounded comparison through the observed timestamp and label it partial. If exchange rates are missing, the system returns unknown for the consolidated total rather than summing incompatible currencies. If the user lacks finance access, it refuses before values reach model context.

The SQL matters. The contract determines whether its output answers the question.

A practical implementation sequence

1. Select five recurring business questions with named owners.
2. Define metric, grain, time, scope, freshness, and completeness for each.
3. Create approved views or narrow tools that match those definitions.
4. Implement the six typed result states.
5. Attach evidence fields before the model creates prose.
6. Build positive, ambiguous, denied, stale, partial, and unknown fixtures.
7. Review failures by state and root cause, not one average accuracy score.

Where Conexor fits

Conexor provides MCP infrastructure for connecting AI clients to databases and APIs through governed tools. An answerability contract helps teams turn that connection into a reviewable workflow by keeping identity, business meaning, scope, freshness, limits, and result evidence explicit.

Explore natural language SQL with Conexor

For a safe result surface, continue with data minimization for AI database queries.