Agentic AI Database Design: PostgreSQL for Agent State and Conversation Memory

Why PostgreSQL Is the Best Foundation for Agentic AI State

Every agentic AI system needs persistent state. Conversations must survive server restarts. Agent decisions need audit trails. Tool executions require logging for debugging and compliance. And the growing importance of long-term memory means you need vector storage alongside relational data.

PostgreSQL handles all of these requirements in a single database engine. With JSONB for flexible document storage, pgvector for embedding similarity search, robust transaction support for state machine transitions, and mature tooling for migrations and backups — PostgreSQL is the most practical choice for agentic AI database design.

At CallSphere, every production agent system stores its state in PostgreSQL. We have iterated through multiple schema designs and landed on patterns that balance query performance, schema flexibility, and operational simplicity. This guide shares those patterns.

Core Schema: Conversations and Messages

The foundation of any agent database is the conversation model. A conversation contains messages from users, agents, and system events.

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgvector";

-- Conversations table
CREATE TABLE conversations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL,
    channel VARCHAR(50) NOT NULL DEFAULT 'web',
    status VARCHAR(20) NOT NULL DEFAULT 'active'
        CHECK (status IN ('active', 'paused', 'completed', 'escalated', 'failed')),
    current_agent VARCHAR(100),
    metadata JSONB DEFAULT '{}',
    started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_activity_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    completed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_conversations_tenant ON conversations(tenant_id);
CREATE INDEX idx_conversations_status ON conversations(status)
    WHERE status = 'active';
CREATE INDEX idx_conversations_last_activity ON conversations(last_activity_at DESC);

-- Messages table
CREATE TABLE messages (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
    role VARCHAR(20) NOT NULL
        CHECK (role IN ('user', 'assistant', 'system', 'tool_call', 'tool_result')),
    agent_name VARCHAR(100),
    content TEXT NOT NULL,
    token_count INTEGER,
    model VARCHAR(100),
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_messages_conversation ON messages(conversation_id, created_at);
CREATE INDEX idx_messages_role ON messages(conversation_id, role);

Design Decisions

UUID primary keys instead of serial integers. Agent systems are often distributed and UUIDs avoid coordination overhead.
tenant_id on conversations enables multi-tenant agent deployments. Every query should filter by tenant.
Separate role for tool_call and tool_result rather than lumping them under "assistant." This makes it easy to query tool usage patterns and debug tool execution failures.
token_count per message for cost tracking. Populate this at insert time — do not try to count tokens retroactively across millions of messages.
Partial index on active conversations. Most queries are about active conversations, and there are far fewer active than completed ones.

Agent State Machine

Agents transition through states: idle, thinking, calling_tool, waiting_for_user, handing_off, completed. Track these transitions explicitly for debugging and analytics.

CREATE TABLE agent_states (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
    agent_name VARCHAR(100) NOT NULL,
    state VARCHAR(50) NOT NULL
        CHECK (state IN (
            'idle', 'thinking', 'calling_tool', 'waiting_for_user',
            'waiting_for_tool', 'handing_off', 'completed', 'error'
        )),
    previous_state VARCHAR(50),
    trigger_event VARCHAR(100),
    state_data JSONB DEFAULT '{}',
    entered_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    exited_at TIMESTAMPTZ
);

CREATE INDEX idx_agent_states_conversation ON agent_states(conversation_id, entered_at DESC);
CREATE INDEX idx_agent_states_current ON agent_states(conversation_id, agent_name)
    WHERE exited_at IS NULL;

-- Function to transition agent state
CREATE OR REPLACE FUNCTION transition_agent_state(
    p_conversation_id UUID,
    p_agent_name VARCHAR,
    p_new_state VARCHAR,
    p_trigger VARCHAR DEFAULT NULL,
    p_data JSONB DEFAULT '{}'
) RETURNS UUID AS $$
DECLARE
    v_current_state_id UUID;
    v_current_state VARCHAR;
    v_new_id UUID;
BEGIN
    -- Close current state
    UPDATE agent_states
    SET exited_at = NOW()
    WHERE conversation_id = p_conversation_id
      AND agent_name = p_agent_name
      AND exited_at IS NULL
    RETURNING id, state INTO v_current_state_id, v_current_state;

    -- Insert new state
    INSERT INTO agent_states (
        conversation_id, agent_name, state, previous_state,
        trigger_event, state_data
    ) VALUES (
        p_conversation_id, p_agent_name, p_new_state, v_current_state,
        p_trigger, p_data
    ) RETURNING id INTO v_new_id;

    -- Update conversation's current agent
    UPDATE conversations
    SET current_agent = p_agent_name,
        last_activity_at = NOW(),
        updated_at = NOW()
    WHERE id = p_conversation_id;

    RETURN v_new_id;
END;
$$ LANGUAGE plpgsql;

Using a PostgreSQL function for state transitions ensures atomicity. The old state is closed and the new state is opened in a single transaction — no possibility of an agent being in two states simultaneously.

Tool Execution Logs

Every tool call an agent makes should be logged with its input, output, latency, and success status. This is non-negotiable for debugging production agent issues.

CREATE TABLE tool_executions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
    message_id UUID REFERENCES messages(id),
    agent_name VARCHAR(100) NOT NULL,
    tool_name VARCHAR(200) NOT NULL,
    tool_input JSONB NOT NULL,
    tool_output JSONB,
    status VARCHAR(20) NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'running', 'success', 'error', 'timeout')),
    error_message TEXT,
    duration_ms INTEGER,
    started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    completed_at TIMESTAMPTZ
);

CREATE INDEX idx_tool_exec_conversation ON tool_executions(conversation_id, started_at DESC);
CREATE INDEX idx_tool_exec_tool_name ON tool_executions(tool_name, started_at DESC);
CREATE INDEX idx_tool_exec_status ON tool_executions(status)
    WHERE status IN ('pending', 'running');
CREATE INDEX idx_tool_exec_errors ON tool_executions(tool_name, status)
    WHERE status = 'error';

Querying Tool Performance

-- Average tool latency by tool name (last 24 hours)
SELECT
    tool_name,
    COUNT(*) as call_count,
    ROUND(AVG(duration_ms)) as avg_ms,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY duration_ms) as p95_ms,
    ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'error') / COUNT(*), 2) as error_pct
FROM tool_executions
WHERE started_at > NOW() - INTERVAL '24 hours'
GROUP BY tool_name
ORDER BY call_count DESC;

This query powers the tool performance dashboard. When an agent starts failing, the first thing to check is whether a tool it depends on has degraded.

See AI Voice Agents Handle Real Calls

Book a free demo or calculate how much you can save with AI voice automation.

Book a Demo ROI Calculator

JSONB for Flexible Agent State

Agent state is inherently semi-structured. Different agent types store different context. A booking agent tracks selected dates and room types. A support agent tracks issue category and prior resolution attempts. JSONB lets each agent type store its own schema without migration overhead.

-- Store agent-specific context as JSONB
CREATE TABLE agent_context (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
    agent_name VARCHAR(100) NOT NULL,
    context JSONB NOT NULL DEFAULT '{}',
    version INTEGER NOT NULL DEFAULT 1,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(conversation_id, agent_name)
);

-- GIN index for querying inside JSONB
CREATE INDEX idx_agent_context_gin ON agent_context USING GIN (context);

-- Example: find all conversations where the booking agent
-- has a check_in_date in March 2026
SELECT c.id, ac.context
FROM conversations c
JOIN agent_context ac ON ac.conversation_id = c.id
WHERE ac.agent_name = 'booking_agent'
  AND (ac.context->>'check_in_date')::date
      BETWEEN '2026-03-01' AND '2026-03-31';

When to Use JSONB vs. Typed Columns

Use JSONB When	Use Typed Columns When
Schema varies per agent type	Schema is shared across agents
Fields are rarely queried individually	Fields are frequently filtered or joined on
Schema evolves rapidly during development	Schema is stable in production
Data is read as a blob and passed to the LLM	Data is used for analytics and reporting

Vector Columns for Long-Term Memory

Agents need memory that persists beyond the current conversation. A user who told the billing agent their preferred payment method last month should not have to repeat it. pgvector enables similarity search on embedded memories.

CREATE TABLE agent_memories (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL,
    user_id UUID,
    agent_name VARCHAR(100),
    memory_type VARCHAR(50) NOT NULL
        CHECK (memory_type IN ('fact', 'preference', 'interaction', 'summary')),
    content TEXT NOT NULL,
    embedding VECTOR(1536),
    importance FLOAT DEFAULT 0.5,
    access_count INTEGER DEFAULT 0,
    last_accessed_at TIMESTAMPTZ,
    expires_at TIMESTAMPTZ,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- HNSW index for fast approximate nearest neighbor search
CREATE INDEX idx_memories_embedding ON agent_memories
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

CREATE INDEX idx_memories_user ON agent_memories(tenant_id, user_id, memory_type);
CREATE INDEX idx_memories_expiry ON agent_memories(expires_at)
    WHERE expires_at IS NOT NULL;

Memory Retrieval Query

-- Find the 5 most relevant memories for a user given a query embedding
SELECT
    id,
    content,
    memory_type,
    importance,
    1 - (embedding <=> $1::vector) as similarity
FROM agent_memories
WHERE tenant_id = $2
  AND user_id = $3
  AND (expires_at IS NULL OR expires_at > NOW())
ORDER BY embedding <=> $1::vector
LIMIT 5;

The combination of vector similarity search with relational filters (tenant_id, user_id, expiry) is something PostgreSQL handles natively that dedicated vector databases require additional infrastructure to achieve.

Agent Handoff Tracking

In multi-agent systems, conversations transfer between agents. Track these handoffs for debugging and for understanding conversation flow patterns.

CREATE TABLE agent_handoffs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
    from_agent VARCHAR(100) NOT NULL,
    to_agent VARCHAR(100) NOT NULL,
    reason TEXT,
    context_passed JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_handoffs_conversation ON agent_handoffs(conversation_id, created_at);

-- Analyze handoff patterns: which agents hand off to which most often?
SELECT
    from_agent,
    to_agent,
    COUNT(*) as handoff_count,
    ROUND(AVG(EXTRACT(EPOCH FROM
        (lead(created_at) OVER (PARTITION BY conversation_id ORDER BY created_at) - created_at)
    ))) as avg_seconds_in_next_agent
FROM agent_handoffs
WHERE created_at > NOW() - INTERVAL '7 days'
GROUP BY from_agent, to_agent
ORDER BY handoff_count DESC;

Migration Strategy

Never modify production schemas directly. Use a migration tool — Alembic for Python, Prisma Migrate for Node.js, or plain SQL migration files with a runner like golang-migrate.

-- migrations/001_initial_schema.up.sql
-- Contains the CREATE TABLE statements above

-- migrations/002_add_conversation_tags.up.sql
ALTER TABLE conversations ADD COLUMN tags TEXT[] DEFAULT '{}';
CREATE INDEX idx_conversations_tags ON conversations USING GIN (tags);

-- migrations/002_add_conversation_tags.down.sql
DROP INDEX IF EXISTS idx_conversations_tags;
ALTER TABLE conversations DROP COLUMN IF EXISTS tags;

Every migration must have a corresponding down migration. Test both directions in your CI pipeline before deploying to production.

Frequently Asked Questions

Should I use pgvector or a dedicated vector database like Pinecone or Weaviate?

For most agentic AI systems, pgvector is sufficient and operationally simpler. You avoid managing a separate database, keep your relational data and vectors co-located for efficient joins, and reduce infrastructure costs. Consider a dedicated vector database only when you have more than 10 million vectors or need sub-millisecond search latency.

How do I handle conversation history that grows too large for the LLM context window?

Store all messages in PostgreSQL but only send the most recent N messages (or N tokens worth) to the LLM. Use a summarization agent to periodically compress older messages into a conversation summary stored in the agent_context table. The full history remains available for audit purposes.

What is the best way to partition conversation data as it grows?

Use PostgreSQL table partitioning by month on the messages table using the created_at column. This keeps queries on recent data fast while allowing you to archive or drop old partitions. Conversations table rarely needs partitioning since it is much smaller.

How do I handle concurrent updates to agent state?

The transition_agent_state function uses PostgreSQL's row-level locking within a transaction. For additional safety, add an optimistic concurrency check using the version column on agent_context — increment it on every update and reject updates where the version does not match.

Should I store LLM prompts and completions in the database?

Yes, but in a separate table with appropriate retention policies. Store the full prompt and completion for debugging, the model name and parameters for reproducibility, and the token counts for cost tracking. Set up automated cleanup to delete records older than your retention period (typically 30-90 days) to manage storage costs.