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
التالي · الدرس 44 من 45
Analytics Dashboard Queries
قراءة 1 دقيقة
أنهيت 43 من 45 درسًا

اسأل Byte عن هذا الدرس
محادثة خاصة حول هذا الدرس. قرأ Byte الدرس كاملًا، ويجيب خلال ثوانٍ، ويُظهر القسم الذي تأتي منه كل إجابة. يمكنك السؤال بأي لغة.
الأسئلة والأجوبة
أسئلة عامة حول هذا الدرس. يجيب AI Tutor فورًا بلغتك، ويراجع فريق FreeAcademy الإجابات، ويمكن لكل المتعلمين قراءتها.

