ON THIS PAGE (6 SECTIONS)
- ✓Vector stores cannot enforce foreign keys, provide serializable ACID isolation, or guarantee exactly-once tool execution.
- ✓Agent state must be cleanly split into Ephemeral Working State, Transactional Session State, and Archival Semantic Memory.
- ✓Tool execution idempotency keys must be enforced at the relational database constraint level, preventing duplicate side-effects on retry.
- ✓Turn concurrency requires PostgreSQL advisory locking or SELECT ... FOR UPDATE to prevent race conditions during parallel user turns.
A common architectural mistake in generative AI development is assuming that because an agent reasons over natural language, its underlying persistence layer should be built entirely on vector stores and loose key-value caches. In production, multi-agent workflows are distributed state machines executing non-deterministic loops across external APIs. If an agent crashes midway through a financial settlement or customer support triage, the system requires strict transaction guarantees, deterministic state transitions, idempotency deduplication, and forensic auditability. This requires relational, ACID-compliant database architecture at the core.
The Relational Reality of Agentic Workflows
While vector databases excel at cosine similarity search over static embeddings, they cannot enforce foreign key integrity, provide serializable transaction isolation, or guarantee that a financial refund tool is executed exactly once during an agent loop. An autonomous agent is fundamentally a state machine: it transitions from state A (parsing user prompt) to state B (proposing tool parameters) to state C (awaiting tool output) to state D (synthesizing user completion).
Treating these transitions as casual JSON blobs stored in Redis leads to race conditions, zombie executions, duplicate API calls, and irrecoverable state loss when node crashes occur. Compare this with our findings in event-driven AI workflows and human-in-the-loop state machines.
The Anatomy of Agent State: Ephemeral vs. Persistent
- Ephemeral Working State: The active scratchpad, intermediate reasoning chains, and token context for the current turn. This lives in fast cache memory and is persisted only upon turn finalization.
- Transactional Session State: The sequence of verified user messages, approved tool calls, tool results, and agent replies. Stored in relational tables with foreign keys and strict constraints.
- Episodic & Archival Memory: Long-term summaries, user preferences, and semantic knowledge extracted across historical sessions. Stored in relational tables paired with vector indexes (e.g., pgvector) for hybrid retrieval.
Production Schema Design: Sessions, Turns, and Tools
| 1 | CREATE TABLE agent_sessions ( |
| 2 | session_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), |
| 3 | tenant_id VARCHAR(64) NOT NULL, |
| 4 | user_id VARCHAR(64) NOT NULL, |
| 5 | current_state VARCHAR(32) NOT NULL DEFAULT 'IDLE', |
| 6 | active_model_id VARCHAR(64) NOT NULL, |
| 7 | created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), |
| 8 | updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), |
| 9 | CONSTRAINT check_session_state CHECK (current_state IN ('IDLE', 'PLANNING', 'EXECUTING_TOOL', 'AWAITING_INPUT', 'COMPLETED', 'FAILED')) |
| 10 | ); |
| 11 | |
| 12 | CREATE TABLE agent_turns ( |
| 13 | turn_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), |
| 14 | session_id UUID NOT NULL REFERENCES agent_sessions(session_id) ON DELETE CASCADE, |
| 15 | turn_index INT NOT NULL, |
| 16 | role VARCHAR(16) NOT NULL CHECK (role IN ('user', 'assistant', 'system', 'tool')), |
| 17 | content TEXT, |
| 18 | token_usage_json JSONB NOT NULL DEFAULT '{}'::jsonb, |
| 19 | created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), |
| 20 | CONSTRAINT uq_session_turn_index UNIQUE (session_id, turn_index) |
| 21 | ); |
| 22 | |
| 23 | CREATE TABLE agent_tool_executions ( |
| 24 | execution_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), |
| 25 | turn_id UUID NOT NULL REFERENCES agent_turns(turn_id) ON DELETE CASCADE, |
| 26 | tool_name VARCHAR(128) NOT NULL, |
| 27 | idempotency_key VARCHAR(128) NOT NULL UNIQUE, |
| 28 | input_parameters JSONB NOT NULL, |
| 29 | output_payload JSONB, |
| 30 | status VARCHAR(32) NOT NULL DEFAULT 'PENDING', |
| 31 | error_message TEXT, |
| 32 | started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), |
| 33 | completed_at TIMESTAMPTZ, |
| 34 | CONSTRAINT check_tool_status CHECK (status IN ('PENDING', 'RUNNING', 'SUCCESS', 'FAILED', 'TIMED_OUT')) |
| 35 | ); |
| 36 | |
| 37 | CREATE INDEX idx_sessions_tenant ON agent_sessions(tenant_id, updated_at DESC); |
| 38 | CREATE INDEX idx_tool_idempotency ON agent_tool_executions(idempotency_key); |
Tool Execution Idempotency and Deduplication
Because LLM reasoning loops can retry upon network failures or emit duplicate function calls across repeated turns, tool invocations must be strictly idempotent. The database schema enforces a unique constraint on the idempotency key: hash(sessionId + turnIndex + toolName + canonicalJSON(parameters)).
If the worker crashes and the agent restarts the turn, an attempt to re-execute the same tool with identical parameters hits the unique key constraint. The system simply loads the existing execution record and returns the cached output rather than re-invoking the external API.
Distributed Locking and Turn Concurrency
If a user sends a follow-up message while the agent is actively executing a long-running tool invocation, two parallel workers could attempt to mutate the same session state simultaneously. To prevent corrupting the turn sequence, platforms utilize PostgreSQL advisory locks or SELECT ... FOR UPDATE transactions keyed to the session ID, guaranteeing single-threaded turn execution per session.
Production Rule: Never store uncommitted agent reasoning in memory only. Write turn checkpoints transactionally so any crashed worker can resume from the exact last verified state.
Direct answers
Why not use DynamoDB or MongoDB for storing agent state?
While NoSQL databases offer rapid key-value lookups, they lack strict foreign key integrity, cross-collection transaction constraints, and atomic sequence guarantees. PostgreSQL provides ACID transactions, robust JSONB querying, and integrated vector indexing in a unified, proven engine.
How do you handle schema changes when tool parameter definitions evolve?
Tool parameters are stored as versioned JSONB objects accompanied by a tool_version field. The application layer maintains versioned Zod schemas to deserialize and validate historical payloads during debugging and playback.
References & Prior Art
PostgreSQL 16 Documentation: Concurrency Control and Transaction Isolation
PostgreSQL Documentation • PostgreSQL Global Development Group (2023)
Designing Data-Intensive Applications: The Big Ideas Behind Reliable, Scalable, and Maintainable Systems
Martin Kleppmann • O'Reilly Media (2017)
Idempotency in Distributed Systems and API Design
IETF HTTP Working Group • IETF Draft / RFC Standards Track (2023)
Barath
Product EngineerBarath builds production internal tools, double-entry financial ledgers, audit trail integrity, and supervised engineering workflows at Vasiva.
