Lesson 6.4: AI Agent Memory Architecture

Short-Term Memory (Conversation History)

-- Store conversation messages
CREATE TABLE conversations (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    started_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE messages (
    id BIGSERIAL PRIMARY KEY,
    conversation_id BIGINT NOT NULL REFERENCES conversations(id),
    role TEXT NOT NULL,  -- 'user', 'assistant', 'system'
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX messages_conversation_idx ON messages(conversation_id, created_at);

-- Retrieve recent conversation
SELECT role, content
FROM messages
WHERE conversation_id = 456
ORDER BY created_at DESC
LIMIT 20;  -- Last 10 exchanges

Long-Term Memory (RAG + Vector DB)

-- Agent's knowledge base
CREATE TABLE agent_knowledge (
    id BIGSERIAL PRIMARY KEY,
    agent_id TEXT NOT NULL,
    source TEXT,  -- 'conversation', 'document', 'web'
    content TEXT NOT NULL,
    embedding VECTOR(1536),
    metadata JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX knowledge_embedding_idx ON agent_knowledge
USING hnsw (embedding vector_cosine_ops);

-- Retrieve relevant memories
SELECT content, metadata
FROM agent_knowledge
WHERE agent_id = 'assistant-1'
    AND embedding <=> query_embedding < 0.75
ORDER BY embedding <=> query_embedding
LIMIT 5;

Entity Memory (User Preferences, Facts)

-- Structured facts the agent learned
CREATE TABLE agent_entities (
    id BIGSERIAL PRIMARY KEY,
    agent_id TEXT NOT NULL,
    user_id BIGINT NOT NULL,
    entity_type TEXT,  -- 'preference', 'fact', 'goal'
    key TEXT NOT NULL,
    value JSONB,
    confidence FLOAT DEFAULT 1.0,
    last_mentioned_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(agent_id, user_id, key)
);

-- Example entities:
-- {entity_type: 'preference', key: 'communication_style', value: {'tone': 'casual', 'detail_level': 'concise'}}
-- {entity_type: 'fact', key: 'job_title', value: 'Senior ML Engineer'}
-- {entity_type: 'goal', key: 'current_project', value: 'Build RAG system'}

-- Retrieve user context
SELECT key, value FROM agent_entities
WHERE agent_id = 'assistant-1' AND user_id = 123
ORDER BY last_mentioned_at DESC;

Key Takeaways

• AI agents need three types of memory: short-term, long-term, and entity memory
• Short-term memory stores recent conversation history (messages table)
• Long-term memory uses RAG with vector search for semantic knowledge retrieval
• Entity memory stores structured facts, preferences, and goals about users
• Conversation tables track messages with roles (user, assistant, system)
• Agent knowledge uses embeddings for semantic similarity search across past interactions
• JSONB for entities allows flexible storage of varied user information
• Complete agent memory combines all three types for contextual, personalized responses