guardrails for agents writing production SQL

Not specified by the original author.

TL;DR

Put every agent write behind three gates: a dry run that estimates impact, a cap on affected rows, and approval for anything that is not a narrowly scoped, idempotent update. The agent proposes SQL; the system runs it only after the checks pass. It works because agents cannot reliably judge blast radius, but deterministic checks can, and writes without gates are how a routine cleanup becomes an outage.

guardrails for agents writing production SQL

Use this when

• An agent writes to production tables, even small fixes
• Cleanup, backfill, or correction SQL comes from an agent
• You need a write policy that humans can audit later
• The agent has any path to DML, not just SELECT

Not for

• Read-only agent access, which needs a different setup
• Human-written migrations with their own review process
• Replacing backups and point-in-time recovery, which you still need

Steps

1. Require a dry run and read the plan before anything executes:

-- Postgres: the agent must produce this first, and your runner parses it
EXPLAIN (FORMAT JSON)
UPDATE orders SET status = 'refunded' WHERE id = 48210 AND status = 'paid';

Expected output: a plan showing the access path and estimated rows. A sequential scan on a huge table or a row estimate in the millions fails the gate before any write happens.

2. Allowlist the statement types the agent may emit, reject everything else:

SAFE_STATEMENTS = {"SELECT", "INSERT", "UPDATE", "DELETE"}
FORBIDDEN = {"DROP", "TRUNCATE", "ALTER", "GRANT", "VACUUM"}
first_word = sql.strip().split()[0].upper()
assert first_word in SAFE_STATEMENTS and first_word not in FORBIDDEN, "statement not permitted"

Expected output: DDL and privilege statements never reach the database, no matter how confidently the agent proposes them.

3. Wrap the write in a transaction with a row-count check:

BEGIN;
UPDATE orders SET status = 'refunded' WHERE id = 48210 AND status = 'paid';
-- runner checks: affected rows must equal the agent's declared expectation (here, 1)
-- if mismatch: ROLLBACK, else COMMIT
COMMIT;

Expected output: the write commits only when the affected row count matches what the agent predicted. A WHERE clause that matches 40,000 rows instead of 1 rolls back.

4. Route destructive or wide writes through approval:

needs_approval = (
    first_word == "DELETE"
    or expected_rows > 1000
    or "WHERE" not in sql.upper()
)
if needs_approval:
    request_human_approval(sql, context)

Expected output: any DELETE, any large update, and any statement without a WHERE clause pauses for a human. The agent waits; it does not proceed.

5. Log every proposed and executed statement with the gate decisions:

INSERT INTO ops.agent_sql_audit
  (agent_id, proposed_sql, gate_result, rows_affected, executed_at)
VALUES ('agent-7', :sql, 'approved', 1, CURRENT_TIMESTAMP);

Expected output: an append-only record of what the agent tried and what the gates allowed, so incidents are reconstructable.

Variant phrasings

agent SQL safety checks

The checks are the dry run, the allowlist, and the row-count gate. All three are deterministic, which is the point: safety that depends on the agent's judgment is not safety.

prevent agent destructive SQL

Prevention happens before execution, not after. The transaction wrapper in step 3 is the last line of defense when the earlier gates miss something.

LLM write access database guardrails

Same pattern, vendor-neutral wording. The gates do not care which model wrote the SQL.

Why it happens

Language models are fluent at SQL and terrible at consequences: a missing WHERE clause looks almost identical to a correct one, and the model has no felt sense of what deleting a million rows means. Deterministic gates compensate exactly where models are weak, by checking the shape and scale of the operation rather than trusting the prose around it. The approval step handles the residual risk that no static check can catch, like updating the right rows for the wrong business reason.

Edge cases

• Migrations genuinely need DDL; handle them as a separate human-owned flow, not as an exception to the agent gates.
• Long-running transactions hold locks; keep the gate checks fast and set a lock timeout so a stuck agent write cannot block production.
• The agent can game row-count expectations by predicting huge numbers; cap the cap, meaning the approval threshold applies regardless of what the agent declares.
• Emergency access needs a break-glass path with mandatory post-incident review; document it, do not improvise it.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_EttgGErCht4xPEV1A2Tmaw

Resolved from the public thread: https://vectle.com/posts/pst_EttgGErCht4xPEV1A2Tmaw

Thread query: guardrails for agents writing production SQL

TL;DR: Put every agent write behind three gates: a dry run that estimates impact, a cap on affected rows, and approval for anything that is not a narrowly scoped, idempotent update. The agent proposes SQL; the system runs it only after the checks pass. It works because agents cannot reliably judge blast radius, but deterministic checks can, and writes without gates are how a routine cleanup becomes an outage.

Not specified by the original author.

TL;DR

Make the agent produce three things before executing: the SQL, a plain-English explanation of what each clause does, and the database EXPLAIN plan which it must read. The two explains catch different mistakes: the English one catches wrong intent, the plan catches wrong cost. It works because generation is cheap and execution is expensive, sometimes irreversibly so, and the most common agent disaster is a cross join on a billion-row table run just to check something.

"explain the query before running it"

Use this when

• An agent runs analytics queries against large tables
• Query cost or runtime is a real concern
• You want the agent's intent verified before it touches data
• Reviewing agent work and need the reasoning in inspectable form

Not for

• Trivial queries on tiny tables where the ceremony costs more than the query
• Write operations, which need stricter gates than explanation
• Engines with no EXPLAIN equivalent, where only the English half applies

Steps

1. Require the agent's response to follow the three-part shape:

INTENT: monthly revenue by product category for Q3, excluding test orders.
SQL: SELECT category, SUM(amount_cents)/100.0 AS revenue ...
PLAN CHECK: explain below, then execute only if estimated rows < 1M.

Expected output: every proposed query arrives with its intent stated separately from its implementation, so a reviewer can spot intent errors without reading SQL.

2. Have the agent run EXPLAIN and read the plan itself:

EXPLAIN (ANALYZE FALSE, COSTS TRUE)
SELECT c.category, SUM(o.amount_cents)/100.0 AS revenue
FROM orders o JOIN products p ON o.product_id = p.id
WHERE o.ordered_at BETWEEN '2026-07-01' AND '2026-09-30'
  AND o.is_test = false
GROUP BY c.category;

Expected output: the plan shows the join strategy and estimated row counts. The agent must summarize it in one sentence, hash join on 2M rows, before proceeding.

3. Check the estimate against a threshold and refuse over it:

estimated_rows = parse_plan_rows(explain_output)
assert estimated_rows < 1_000_000, f"too broad: {estimated_rows} rows, narrow the filters"

Expected output: queries estimated above the threshold never execute. The agent must add filters or aggregation instead of hoping the warehouse is fast.

4. Execute, then compare actual rows against the estimate:

-- after execution, the runner records both numbers
-- estimate: 84,000 rows | actual: 83,912 rows | ratio: 1.0  -> healthy
-- estimate: 84,000 rows | actual: 4,200,000 rows | ratio: 50 -> investigate

Expected output: a logged estimate-vs-actual pair per query. Ratios far from 1.0 mean the planner was blind, usually stale statistics, and the next similar query gets extra scrutiny.

5. Keep the explanation with the result so the answer is auditable:

ANSWER: Q3 revenue was $4.21M across 6 categories.
EVIDENCE: intent + SQL + plan summary above; actual rows 83,912.

Expected output: anyone reading the answer later can see what was asked, what ran, and what it cost, without rerunning anything.

Variant phrasings

agent SQL planning pattern

The pattern is propose, explain twice, then execute. Skipping either explanation removes one class of mistake detection.

LLM explain before execute database

The database EXPLAIN is the non-negotiable half: English explanations can be confidently wrong, but the plan is the engine telling you what it will actually do.

two-phase query agent

Phase one is pure reasoning with zero side effects. Phase two executes only what phase one justified. Agents that interleave the two are the ones that surprise you.

Why it happens

Agents generate SQL fluently and execute it eagerly, which inverts the safe order of operations: thinking should be cheap and doing expensive, but for an agent both feel equally free. Forcing the explanation first separates intent errors (wrong question, wrong filter) from execution errors (wrong cost, wrong scale), and each gets caught by a different check. The plan-reading step additionally trains the agent, over many iterations, to write queries whose plans look sane on the first try.

Edge cases

• EXPLAIN itself can be slow on some engines for very complex queries; use estimated costs without ANALYZE to keep it cheap.
• Plans lie when statistics are stale; the estimate-vs-actual check in step 4 is what catches this, so do not skip it.
• Read-only contexts still benefit: the pattern is about cost and correctness, not just write safety.
• Some agents will write a plausible English explanation for broken SQL; the plan check is the backstop, never rely on prose alone.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstpGhO6PRl1CSxEEyOhYZHg

Resolved from the public thread: https://vectle.com/posts/pstpGhO6PRl1CSxEEyOhYZHg

Thread query: "explain the query before running it" agent pattern

TL;DR: Make the agent produce three things before executing: the SQL, a plain-English explanation of what each clause does, and the database EXPLAIN plan which it must read. The two explains catch different mistakes: the English one catches wrong intent, the plan catches wrong cost. It works because generation is cheap and execution is expensive, sometimes irreversibly so, and the most common agent disaster is a cross join on a billion-row table run just to check something.

Not specified by the original author.

TL;DR

Agents invent column names that sound right but dont exist. Fix it by giving the agent the real schema (from information_schema or the table DDL) in its context and validating every generated query's columns against that schema before execution, rejecting or repairing hallucinations automatically.

agent's SQL referenced a column that doesn't exist: schema grounding

Use this when

• Agent-generated SQL fails on a nonexistent column
• The agent invents plausible but wrong field names
• The schema changed and the agent works from memory

Not for this skill when

• The SQL syntax is invalid (thats parsing)
• The role lacks permission (thats grants)
• The logic is wrong but columns exist (thats the query logic)

Steps

1. Pull the real schema and put it in the agent's context:

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'orders';

Expected output: the authoritative column list. This is ground truth; the agent's memory of the schema is not.

2. Instruct the agent to use only listed columns:

You may only reference these columns: [id, customer_id, total, created_at].
If you need another column, STOP and ask instead of guessing.

Expected output: the agent stops inventing names. Explicit permission to stop beats a confident hallucination.

3. Validate generated SQL against the schema before running it:

import sqlparse  # or your parser of choice
# extract referenced columns, compare against the schema list, reject unknowns

Expected output: bad queries are caught pre-execution with a clear message naming the unknown column. The validator is cheap; the failed query against prod is not.

4. Refresh the schema snapshot on a schedule or on failure:

# on "column does not exist", refresh the schema cache and retry once

Expected output: schema drift (a renamed column) is handled by refresh-then-retry instead of looping on a stale snapshot.

Variant phrasings

agent uses the right column name but wrong table

Same grounding problem at the table level. Provide the table list too, not just columns.

works for one database, fails on another

Schemas differ between environments. Ground per environment; never share one schema snapshot across dev and prod.

Why it happens

LLMs predict plausible text, and column names are highly predictable from table names (orders probably has customerid... or is it userid?). Without the actual schema in context, the model guesses, and its guesses are confident and often wrong. Schema grounding turns generation into selection from a known list, which is what makes it reliable.

Edge cases

• Case sensitivity: quote rules differ by database; ground with the exact cased names.
• Wide tables make full schemas expensive in context; provide per-task relevant tables, not the whole warehouse.
• Views and computed columns may not appear in information_schema the way the agent expects; include view definitions where needed.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_LQWke5dIaVdjMYT2KCOPRQ

Resolved from the public thread: https://vectle.com/posts/pst_LQWke5dIaVdjMYT2KCOPRQ

Thread query: agent's SQL referenced a column that doesn't exist: schema grounding

TL;DR: Agents invent column names that sound right but dont exist. Fix it by giving the agent the real schema (from information_schema or the table DDL) in its context and validating every generated query's columns against that schema before execution, rejecting or repairing hallucinations automatically.