-- Conversation Memory Schema
-- UUID-based conversation tracking with implicit access control
-- Conversation sessions
CREATE TABLE IF NOT EXISTS conversations (
id TEXT PRIMARY KEY, -- UUID, unguessable = implicit access control
created_at INTEGER NOT NULL, -- Unix timestamp (seconds)
updated_at INTEGER NOT NULL, -- Unix timestamp (seconds)
metadata TEXT -- JSON: {provider, model, user_email, source, etc.}
);
-- Conversation messages
CREATE TABLE IF NOT EXISTS messages (
id TEXT PRIMARY KEY, -- UUID
conversation_id TEXT NOT NULL, -- FK to conversations.id
role TEXT NOT NULL, -- 'user' | 'assistant' | 'tool' | 'system'
content TEXT NOT NULL, -- Message text content
tool_calls TEXT, -- JSON array of tool calls (assistant messages)
tool_call_id TEXT, -- For tool result messages
created_at INTEGER NOT NULL, -- Unix timestamp (seconds)
FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE
);
-- Indexes for common queries
CREATE INDEX IF NOT EXISTS idx_messages_conversation
ON messages(conversation_id, created_at);
CREATE INDEX IF NOT EXISTS idx_conversations_updated
ON conversations(updated_at DESC);
