MCP database queries: prove negative answers before saying none exist

“No failed payments exist” is a much stronger claim than “this query returned zero rows.”

The result may be empty because the tenant scope was wrong, yesterday's partition has not arrived, one source timed out, a join removed unmatched records, the user cannot see the relevant rows, or the connector stopped before checking every page.

Production MCP database queries need a negative-answer contract.

Separate zero, none, hidden, and unknown

Use typed result states instead of collapsing every empty output into the same message:

• none found: the complete authorized population was checked;
• no visible matches: nothing matched within the caller's permitted scope;
• source unavailable: one or more expected sources could not be checked;
• incomplete: pagination, timeout, truncation, or budget ended the search;
• not answerable: the requested concept is not represented reliably;
• unknown: evidence is insufficient for an absence claim.

The final prose must preserve the typed state. “No visible matches” must not become “none exist.”

Define the population first

An absence claim needs explicit tenant, environment, time window, timezone, entity state, source systems, and business definition. The tool should clarify missing scope rather than inherit a convenient value from an unrelated conversation turn.

For wider population evidence, use the AI database answer completeness contract.

Prove source coverage and freshness

Record which sources, partitions, replicas, and indexes were expected and observed. Include their watermarks and the reporting cutoff. A zero result from a replica that is 20 minutes behind cannot prove that no event happened in the last five minutes.

When one source is missing, either return a named partial result or refuse the negative claim according to workflow policy. Do not average away the missing source.

Make authorization visible

Row-level security and scoped views can correctly hide records from the caller. That means an empty result may prove only that the caller is not authorized to see a match.

Return the effective principal, tenant, role, policy version, and scope class without revealing hidden values. This lets the assistant say “no matching records are visible in your authorized scope” rather than making a global assertion.

Related: scoped database access for AI agents.

Test query semantics that erase rows

Inner joins, NULL comparisons, timezone conversion, enum mappings, soft-delete filters, and data-type casts can turn present records into zero results. Review these semantics as part of the approved operation.

Track counts before and after material joins. Represent unknown and unmatched values explicitly. A missing customer lookup should not silently erase the failed payment being investigated.

Do not infer absence from a bounded sample

Row limits, page limits, statement timeouts, and cost budgets are necessary, but an incomplete search cannot support “none found.” Return completion and truncation state structurally.

Use explicit PostgreSQL result limits and truncation so a protective bound never becomes a false negative.

Concrete example: failed payments

A support user asks whether a customer had any failed payments today. The operation expects the billing ledger and event stream through 14:00 UTC.

The ledger contains no match, but the event-stream consumer is delayed and its watermark is 13:42. The correct answer is not “no failed payments.” It is “no matching ledger record is visible; event coverage is incomplete for the last 18 minutes.”

When the watermark catches up, the operation can be rerun under the same normalized scope and produce a complete negative answer with evidence.

Return a negative-answer receipt

Include normalized scope, approved operation and metric versions, effective principal and policy, expected and observed sources, watermarks, row counts before and after joins, null and unmatched counts, pagination state, limits, completion decision, and trace ID.

The receipt supports review without logging sensitive rows that the caller was not allowed to see.

Test the empty path

1. Delay one expected source while the others return zero.
2. Query through a principal that cannot see a known matching row.
3. Add an unmatched foreign key and compare inner and outer joins.
4. Insert events around timezone and cutoff boundaries.
5. Force pagination, timeout, cancellation, and truncation.
6. Change schema or metric definitions without refreshing the operation.
7. Verify the prose preserves none, hidden, incomplete, and unknown states.

Where Conexor fits

Conexor provides MCP infrastructure for connecting AI clients to databases and APIs through governed tools. Structured scope, source evidence, result limits, and audit receipts help an assistant distinguish an empty query from a defensible absence claim.

Explore governed AI database access

For the wider evidence chain, see audit-ready MCP database workflows.