Agentic AI systems violate the implicit assumptions of database design

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

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

Related Resources

• Original Article: Databases Were Not Designed For This (Arpit Bhayani)
• Databricks Lakebase (DB supporting branching for Agents)