## 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.

```text
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:

```sql
-- 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:

```python
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:

```sql
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:

```python
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:

```sql
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