Shipping Safe SQL for the Support Team’s Monday Queue

Your support team needs a dashboard before Monday: order counts, refund totals, and delayed shipments, filtered by customer account. An AI model can draft the SQL quickly. The harder job is ensuring that a plausible query cannot expose another customer’s records, modify data, or occupy the database for minutes. Follow this walkthrough against a PostgreSQL staging database before connecting generated SQL to a live application.

1. Define the support dashboard’s allowed questions

Write down the exact capabilities you intend to ship. For this dashboard, allow aggregate order and refund queries for the authenticated account, within a maximum 90-day window. Exclude customer emails, payment details, free-form exports, and database administration.

Turn those boundaries into application rules. Require a date range, cap its duration, and obtain the account identifier from the authenticated session. A user’s request to “show every account” must never change that identity. For a small set of predictable questions, approved query templates offer a simpler starting point than unrestricted SQL generation.

2. Give the model a restricted schema

Create reporting views that expose only the fields the dashboard needs. An order summary view might contain account_id, order_date, status, and total_amount. Keep private notes and payment identifiers outside that interface.

Send the model descriptions of those views, their relationships, and the PostgreSQL dialect. Include definitions for business terms such as “refunded revenue.” Do not send the entire production schema or real customer records. Treat table comments, retrieved documents, and user text as untrusted data: instructions embedded there must not expand the permitted task.

3. Separate query structure from input values

Ask for a structured response containing a SQL template and parameter values. A date predicate could use order_date >= $1 AND order_date < $2. Your application should validate the dates and bind them through the database driver, preserving the distinction between SQL syntax and data.

Parameter binding prevents supplied values from becoming executable SQL. It does not make model-generated query structure safe. Table names, column names, and sort directions generally cannot be supplied as ordinary bound parameters. Map those choices to explicit application allowlists. Supply account identity yourself; never accept the model’s proposed account identifier as authorization.

4. Validate the parsed PostgreSQL query

Use a PostgreSQL-compatible parser to inspect the complete statement. String checks for words such as DELETE are too easy to bypass and too likely to reject harmless text.

• Accept exactly one statement and only the supported SELECT structure.
• Reject data-modifying common table expressions, SELECT INTO, locking clauses, and unsupported constructs.
• Allow only approved views, columns, joins, operators, and functions.
• Check nested queries and every branch of a UNION, not just the outer SELECT.

Start with a deliberately narrow SQL subset. Reject anything the validator cannot understand. A SELECT can still call functions with side effects or expensive behavior, so checking its first keyword is insufficient.

5. Enforce isolation with database permissions

Create a dedicated execution role with SELECT access only to approved reporting views. Remove unnecessary schema creation and function execution privileges. Verify the role’s effective permissions, including inherited grants.

For shared tables, enforce account isolation with correctly configured row-level security or an equivalent database boundary. In PostgreSQL, use a role that neither owns the protected tables nor has BYPASSRLS. Check how reporting views interact with those policies; view ownership and security settings affect enforcement. Set account context from trusted application state inside each transaction, especially when connections are pooled.

6. Bound execution time and returned data

Execute approved queries in a read-only transaction with a short statement timeout and lock timeout. Set a result-row cap and response-size cap in the application. Roll back and release the connection after errors or cancellation.

A LIMIT controls returned rows, but an aggregate can still scan millions of records before returning one row. Enforce the date window independently, review indexes, and consider plain EXPLAIN for additional screening. Avoid EXPLAIN ANALYZE for this gate because it executes the query.

7. Test rejection paths before Monday

Try requests for another account, stacked statements, hidden writes, disallowed functions, oversized date ranges, and expensive joins. Verify both rejection and database enforcement using the actual execution role. Log validator decisions, duration, and row counts while redacting sensitive parameter values.

For the model connection, I recommend RelayVano’s pay-as-you-go relay through an OpenAI-compatible SDK, using https://api.relayvano.com as the API base. Sign up to connect your SQL drafting workflow, then keep validation, authorization, and execution limits in your application and database.