Lesson 7.5: Conversation Memory System

Complete Memory Architecture

-- Conversations
CREATE TABLE conversations (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    title TEXT,
    started_at TIMESTAMPTZ DEFAULT NOW(),
    last_message_at TIMESTAMPTZ DEFAULT NOW(),
    message_count INT DEFAULT 0
);

CREATE INDEX conversations_user_recent_idx ON conversations(user_id, last_message_at DESC);

-- Messages
CREATE TABLE messages (
    id BIGSERIAL PRIMARY KEY,
    conversation_id BIGINT NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
    role TEXT NOT NULL,  -- 'system', 'user', 'assistant', 'tool'
    content TEXT NOT NULL,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX messages_conversation_time_idx ON messages(conversation_id, created_at DESC);

-- Trigger: Update conversation stats
CREATE OR REPLACE FUNCTION update_conversation_stats()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE conversations SET
        last_message_at = NEW.created_at,
        message_count = message_count + 1
    WHERE id = NEW.conversation_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER message_inserted
AFTER INSERT ON messages
FOR EACH ROW EXECUTE FUNCTION update_conversation_stats();

-- Long-term entity memory
CREATE TABLE agent_entities (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    entity_type TEXT NOT NULL,  -- 'preference', 'fact', 'goal'
    key TEXT NOT NULL,
    value JSONB NOT NULL,
    confidence FLOAT DEFAULT 1.0,
    first_mentioned_at TIMESTAMPTZ DEFAULT NOW(),
    last_mentioned_at TIMESTAMPTZ DEFAULT NOW(),
    mention_count INT DEFAULT 1,
    UNIQUE(user_id, entity_type, key)
);

CREATE INDEX agent_entities_user_type_idx ON agent_entities(user_id, entity_type, last_mentioned_at DESC);

Key Takeaways

• Three-table design: conversations, messages, agent_entities
• Conversations table tracks metadata (title, timestamps, message count)
• Messages table stores all conversation history with roles
• Triggers automatically update conversation stats on new messages
• Agent entities store structured facts learned about users
• UNIQUE constraint prevents duplicate entity keys per user
• Confidence scores track certainty of learned facts
• Mention tracking records when entities were first/last mentioned
• CASCADE deletes clean up messages when conversations are deleted