Text-to-SQL production pattern

The goal of Text-to-SQL is not “produce something that runs.” It is to execute a correct query only when evidence, privilege, and cost boundaries are clear—and clarify or refuse everything else.

Recommended execution chain

natural-language question
  → resolve business entities, metric, time range, and grain
  → retrieve a versioned schema/metric contract and a few verified examples
  → generate a structured query plan and parameters, not directly executed free text
  → validate SQL AST, object/function allowlists, privilege, and cost
  → restricted role + read-only transaction + timeouts + result bounds
  → return result, metric definition, SQL fingerprint, truncation, and explainable errors

Model context should include schema version, table/column semantics, keys, enums, time zone, currency units, soft-delete rules, tenant boundaries, approved metric definitions, and allowed objects. Do not indiscriminately inject all DDL, sample customer data, or credentials.

Prefer structured tools

For common analytics, have the model produce domain parameters:

{
  "metric": "paid_order_revenue",
  "time_range": { "start": "2026-07-01", "end": "2026-08-01" },
  "group_by": ["day"],
  "filters": [{ "field": "region", "op": "eq", "value": "east" }],
  "limit": 100
}

The server maps metrics, fields, and operators to reviewed SQL. Only long-tail exploration enters a free-SQL lane, which must still parse an AST. A regex check for “starts with SELECT” is not a guardrail: CTEs, data-modifying CTEs, functions, COPY, multiple statements, and comment tricks defeat naive string checks.

Database execution envelope

BEGIN READ ONLY;
SET LOCAL statement_timeout = '3s';
SET LOCAL lock_timeout = '500ms';
SET LOCAL idle_in_transaction_session_timeout = '5s';

-- One policy-approved parameterized SELECT; server enforces row/byte bounds
SELECT date_trunc('day', paid_at) AS day, sum(total_cents) AS revenue_cents
FROM analytics.paid_orders
WHERE tenant_id = $1
  AND paid_at >= $2
  AND paid_at < $3
GROUP BY 1
ORDER BY 1
LIMIT 100;

COMMIT;

READ ONLY is defense in depth, not a complete sandbox. Allow only trusted functions and objects, execute as a dedicated low-privilege role, and bind tenant, environment, and parameters on the server. The model never supplies a connection string, role, or search_path.

Pre-execution checks

1. Allow one statement and approved AST nodes; reject DDL/DML, COPY, arbitrary functions, and administration objects.
2. Convert every value to a bound parameter; identifiers only come from the schema-contract allowlist.
3. Run EXPLAIN (FORMAT JSON) on expensive candidates and inspect objects, estimated rows, and total cost. Estimates are signals, not execution-time guarantees.
4. Require time ranges and row/byte/join bounds. Route bulk exports to a separate asynchronous product path.
5. Reject sensitive columns in policy or expose reviewed masked views; never rely on the model remembering not to select them.
6. Inject tenant scope through database RLS or server templates, never from the user's wording.

Correctness and refusal

A runnable query can still answer the wrong question. Evaluation sets should cover empty results, join duplication, time-zone boundaries, NULLs, refunds/cancellations, late data, tenant isolation, and ambiguous metrics. Assert the allow/refuse decision, result set, accessed objects, maximum cost, and explanation together.

Clarify instead of guessing when a metric has multiple business definitions, a date lacks year/time zone, a name maps to multiple IDs, the request requires a nonexistent historical snapshot, or the schema contract does not match deployment.

Read the PostgreSQL 18 READ ONLY transaction semantics and SQLSTATE appendix.