data contracts enforcing agent written 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

The agent is guessing table and column names instead of reading them from the catalog. Pull the live schema from information_schema, put it in the agent's context, and validate every generated query against that schema before it runs. Hallucinated names get caught pre-execution instead of failing (or worse, silently returning empty) at runtime.

Symptom

The agent generates confident SQL referencing tables or columns that are not in the warehouse. Queries fail with "relation does not exist", or return empty results when the agent invents plausible-sounding names. The pattern repeats across runs because nothing corrects the agent's mental model of the schema.

Steps

1. Pull the live schema from the catalog. This is ground truth; the agent must never guess names:

SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

Expected output: the real tables and columns. Save this as the allowlist every query is checked against.

2. Inject the schema into the agent's prompt context, scoped to the schemas it may query. Keep it compact: table names with their columns, refreshed each run or cached with a short TTL.

Expected output: the agent's generated SQL references only listed tables and columns, because it can see what exists.

3. Validate every generated query against the catalog before executing: parse the identifiers and check each against the schema from step 1. Reject or repair on the first unknown identifier instead of running it.

Expected output: hallucinated names are caught pre-execution; the agent gets the validation error plus the real schema and retries with correct names.

4. Log every rejected query with the hallucinated identifier. If one table name keeps appearing, it is either a stale schema cache or a naming convention the agent expects; fix the cache or add the alias mapping.

Expected output: repeat hallucinations drop to zero after the cache or mapping fix.

When to use

• Agent-generated SQL fails on tables or columns that do not exist
• The agent works on one schema fine but invents names in another
• You are giving an agent write access to run queries at all

When not to use

• The tables exist but the query logic is wrong (that is a SQL-logic problem, not hallucination)
• A human writes the SQL (they can read the schema themselves)
• The failure is permissions, not missing objects (check grants)

Variant phrasings

agent keeps using an old table name after a rename

The schema cache is stale, or the old name is baked into the prompt. Refresh the cache and grep the prompt for the old name.

agent invents columns that sound right but do not exist

Same fix. Column-level validation in step 3 catches these; table-only checks do not.

Why it happens

LLMs complete patterns; without the schema in context, the model fills in the most plausible table and column names from its training data. It has no way to know your warehouse calls it fact_orders_v2 instead of orders. Schema grounding replaces guessing with lookup.

Edge cases

• Very wide schemas do not fit in context; scope to the relevant schemas or summarize to table names plus a column list on demand.
• Temp tables and CTEs the agent creates itself are not in the catalog; exempt the agent's own session objects from validation.
• Case sensitivity: quoted identifiers in some warehouses are case-sensitive; normalize case the same way the warehouse does before comparing.

Provenance

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

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

Thread query: data agent hallucinated a table that does not exist: schema-ground every query

TL;DR: The agent is guessing table and column names instead of reading them from the catalog. Pull the live schema from information_schema, put it in the agent's context, and validate every generated query against that schema before it runs. Hallucinated names get caught pre-execution instead of failing (or worse, silently returning empty) at runtime.

Not specified by the original author.

TL;DR

No contract means no owner to invent: the agent assigned a data contract owner for a table that has no contract, so look the owner up in the contract registry or the catalog, and report the table as unowned when no record exists. An invented owner routes alerts and approvals to a person who never agreed to them, and the real gap, a table with no owner, stays hidden behind the plausible name.

agent hallucinating data contract owner for table with no contract caused routing failure error

Use this when

• The check or run fails with this symptom: an agent hallucinates a data contract owner for a table with no contract and causes a routing failure.
• You are working on an agent workflow routing data quality alerts or approvals by contract ownership and the failure repeats on the same data or configuration.
• You need the failing rows, rule, or stage isolated before changing thresholds or code.

Not for this skill when

• A table whose contract exists in a different registry than the one searched, which needs the registry list fixed rather than an unowned report.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Search the contract registry and the catalog for the table. Verify: no contract and no owner record exist for it
2. List every place the agent sent the invented owner. Verify: each misrouted alert or approval is identified
3. Mark the table unowned in the workflow output and route it to the default triage queue. Verify: the gap is visible with the table named
4. Notify the misrouted recipient that the assignment was an error. Verify: the correction is on record
5. Add a workflow rule that owner fields come only from a registry lookup that can return empty. Verify: an empty lookup produces unowned, never a generated name

Variant phrasings

agent hallucinating data contract owner no contract routing

The shorter phrasing focused on the named failure.

invented table owner agent routing failure

The symptom phrasing a searcher tries next.

unowned table contract owner lookup fix

The variant naming the failing stage or tool directly.

Compatibility: Data contract registries and catalogs, agent routing workflows for alerts and approvals

Why it happens

Agents asked for an owner produce one, because an empty field feels like a failed task. Ownership is a fact stored in registries and contracts, not a property inferable from a table name, and guessing it misroutes accountability while concealing the missing contract.

Edge cases / pitfalls

• Table names that contain a team name are hints for a human search, not evidence of ownership; verify in the registry anyway.
• An unowned table report should include usage and criticality so the triage queue can prioritise assigning a real owner.

Provenance

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

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

Thread query: agent hallucinating data contract owner for table with no contract caused routing failure error

TL;DR: No contract means no owner to invent: the agent assigned a data contract owner for a table that has no contract, so look the owner up in the contract registry or the catalog, and report the table as unowned when no record exists. An invented owner routes alerts and approvals to a person who never agreed to them, and the real gap, a table with no owner, stays hidden behind the plausible name.