Define the trust boundary before tuning prompts
The main risk in Text-to-SQL is not whether a query compiles. It is whether the query exposes unauthorized rows, crosses a tenant boundary, or consumes enough resources to affect a production workload. Treat the language model as an untrusted query proposer, not as a database operator. It may interpret a question and suggest an intent, while deterministic services remain responsible for authorization, validation, and execution.
Start by identifying user roles, data classifications, permitted query patterns, and latency requirements. A finance leader, support agent, and external partner should not receive the same data merely because they asked the same question. If the use case needs only aggregates, do not expose record-level fields. If freshness matters, decide whether a read replica, warehouse, or governed cache is a safer target than the transactional database.
- Read-only by default: reject writes, DDL, stored procedures, and multiple statements.
- Minimum data scope: expose only approved datasets, fields, and time ranges.
- Identity-aware access: derive permissions from enterprise identity and roles, never from model judgment.
- Enforced tenant isolation: apply tenant predicates outside the prompt.
- Fail closed: ask for clarification when intent, authority, or cost cannot be established.
Reduce the model's world with a semantic layer
Sending the entire database schema in a prompt is usually both inaccurate and unsafe. Legacy tables, ambiguous columns, and complicated relationships give the model more opportunities to choose the wrong source while revealing unnecessary implementation details. A governed semantic layer provides a smaller contract: approved read-only views, business definitions, data classifications, and supported join paths. The model works with concepts such as recognized revenue or active customer rather than improvising over physical tables.
Schema retrieval can follow a RAG pattern. Use the question, user role, and business domain to retrieve a small set of relevant datasets, column descriptions, synonyms, and reviewed examples. This scales better than placing every schema object into every prompt. However, catalog descriptions and sample values are still untrusted data; malicious text embedded in metadata or source records must never override system policies.
- Stable business views: version the interface so physical schema changes do not silently alter generated queries.
- Explicit metric definitions: document time zones, currencies, cancellation rules, deduplication, and reporting periods.
- Approved relationships: constrain joins to known paths and grains.
- Field classification: attach sensitivity, masking, aggregation, and role requirements to each exposed field.
Put every query through a deterministic safety gate
Generated SQL must not travel directly to the database. Parse it with a dialect-aware parser, construct an abstract syntax tree, and inspect statement types, sources, columns, functions, subqueries, unions, and comments. Keyword filtering with regular expressions is not a sufficient control: aliases, nesting, comments, and dialect-specific syntax make string rules easy to bypass and difficult to maintain.
Do not ask the model to remember authorization predicates. An execution service should use the authenticated identity to invoke native row-level security or inject mandatory tenant, department, and date constraints into the parsed tree. Sensitive fields can be removed from the semantic layer, masked in results, or restricted to aggregate output. Hiding them only in the final interface is too late because raw values may already have entered model context, traces, or logs.
After syntax and policy validation, control query cost. An EXPLAIN step or database cost estimate can identify unsupported scans, excessive joins, and obviously expensive plans. Estimates are imperfect, so pair them with hard statement timeouts, scan budgets where available, concurrency limits, and maximum result sizes.
- Allow one SELECT statement or an approved parameterized template.
- Bind dates, tenant identifiers, statuses, and user-provided values as parameters.
- Add a sensible row limit, but do not confuse a result limit with a scan limit.
- Reject unbounded sensitive-detail queries and unknown functions.
- Serialize SQL again from the validated syntax tree instead of executing the model's original text.
Isolate execution, preserve evidence, and support refusal
A separate query broker should execute approved statements against a read replica, warehouse, or dedicated analytics endpoint. Give it narrow, rotating credentials and strict connection, memory, timeout, and result-size limits. This separation prevents a compromise of the model-facing service from becoming broad database access. For highly sensitive domains, return aggregates to the model for explanation rather than sending it unrestricted record-level results.
Observability should capture more than the final answer. Audit the authenticated user, original question, selected semantic datasets, generated and rewritten SQL, policy decision, execution status, duration, and result count. Avoid copying sensitive result bodies into ordinary application logs. The interface should also show the interpreted question, applied filters, data sources, and freshness timestamp so a business user can detect a mistaken assumption before acting on the answer.
Evaluation must cover ordinary questions as well as ambiguity, unauthorized requests, prompt injection, dialect differences, large scans, and incorrect joins. Measure whether the system selected the right source, followed policy, refused when uncertain, and behaved consistently for equivalent roles—not merely whether the prose answer looked convincing. Begin with a small set of valuable, well-defined read-only workflows and expand from audit evidence. Safe Text-to-SQL is a governed data product, not an unusually elaborate prompt.