Agentic AI systems violate the implicit assumptions of database design

TL;DR Highlight

AI Agents shatter a 40-year assumption—that databases only accept deterministic queries from humans—and this post details specific defensive patterns to mitigate the resulting risks.

Who Should Read

Backend/data engineers granting or considering database access to AI Agents, especially those designing systems connecting production DBs and Agents.

Core Mechanics

Database architecture implicitly assumes the caller is an application executing deterministic code written by humans. Queries are reviewed, writes are intentional, and issues are caught by people—an assumption held for four decades.
AI Agents dynamically generate queries based on their reasoning path, potentially executing never-before-seen queries like a five-table join, holding connections while contemplating results, and then running entirely different follow-up queries, breaking existing indexes and connection pool settings.
The first line of defense is setting statement timeouts at the DB role level, not the application level. Forcing a timeout on an agent-specific role—e.g., `ALTER ROLE agent_worker SET statement_timeout = '5s'`—prevents infinite reasoning loops from exhausting DB resources.
The `idle_in_transaction_session_timeout` setting is also crucial. Agents genuinely open transactions, pause reasoning mid-process, and without this timeout, those connections remain indefinitely occupied.
Agent writes aren't 'reviewed intentional writes'. A documented incident involved an Agent interpreting HTTP 200 and an empty result (a silent failure due to DB connection pool exhaustion) as 'success', approving 500 transactions with incomplete data.
All tables accessible to Agents should default to soft delete. Add `deleted_at`, `deleted_by` (e.g., 'agent:customer-support-v2'), and `delete_reason` columns, and expose only views like `active_orders` to Agents. The `deleted_by` column enables debugging queries like 'show everything agent X deleted two hours ago'.
High-stakes tables—financial records, inventory changes, user state—should be designed as append-only event logs. Agents only INSERT new states, never UPDATE or DELETE, preserving a complete audit trail of who did what and when.

Evidence

"Most comments strongly rejected the premise of granting Agents direct write access to production DBs, with many arguing that stored procedures and API layers exist precisely to prevent this. One analogy compared it to letting an intern perform live migrations in production."

How to Apply

If Agents need DB access, connect them read-only to a near real-time replicated OLAP DB instead of the production OLTP DB. For writes, restrict Agents to API endpoints with approval workflows—e.g., `request_to_ban_user(id)`—automatically blocking Agent mistakes.
Create an Agent-specific DB role and enforce `ALTER ROLE agent_worker SET statement_timeout = '5s'` and `idle_in_transaction_session_timeout = '10s'` at the role level. This prevents resource exhaustion from infinite loops or connection hogging without application code changes.
For tables Agents write to, add `deleted_at TIMESTAMPTZ`, `deleted_by TEXT`, and `delete_reason TEXT` columns for soft delete, and expose only views with `WHERE deleted_at IS NULL` to Agents. This enables future recovery and auditing.
If Agents handle sensitive data like financial transactions or inventory, design separate append-only event log tables and allow Agents to INSERT only. Revoking UPDATE/DELETE privileges at the DB level structurally prevents accidental data overwrites or deletions.

Code Example

snippet

-- Create agent-specific role and set timeouts
CREATE ROLE agent_worker;
ALTER ROLE agent_worker SET statement_timeout = '5s';
ALTER ROLE agent_worker SET idle_in_transaction_session_timeout = '10s';

-- Add soft delete columns
ALTER TABLE orders ADD COLUMN deleted_at TIMESTAMPTZ;
ALTER TABLE orders ADD COLUMN deleted_by TEXT;  -- e.g., 'agent:customer-support-v2', 'user:abc123'
ALTER TABLE orders ADD COLUMN delete_reason TEXT;

-- Expose only this view to agents (automatically filters deleted rows)
CREATE VIEW active_orders AS
  SELECT * FROM orders WHERE deleted_at IS NULL;

Terminology

OLTPAbbreviation for Online Transaction Processing. A type of production database that handles routine operational transactions like order creation and payment processing.

OLAPAbbreviation for Online Analytical Processing. A data warehouse type of DB optimized for aggregating and analyzing large datasets, processing complex queries much faster than OLTP.

soft deleteA pattern where records aren't physically deleted but marked as deleted (e.g., with a `deleted_at` column), allowing for later recovery or auditing.

append-onlyA table design where existing records aren't updated or deleted; new states are added via INSERT only, automatically preserving a complete change history.

statement_timeoutThe maximum time a DB allows a single query to run. Exceeding this limit causes the DB to forcibly terminate the query, preventing resource exhaustion.

idle_in_transaction_session_timeoutThe maximum time an idle (inactive) transaction session is allowed to remain open. Prevents Agents from indefinitely occupying connections while paused during reasoning.