1-- Messages table
 2-- Each message is part of a conversation and can be from user, agent, or tool
 3CREATE TABLE messages (
 4    message_id TEXT PRIMARY KEY,
 5    conversation_id TEXT NOT NULL,
 6    type TEXT NOT NULL CHECK (type IN ('user', 'agent', 'tool', 'system')),
 7    llm_data TEXT, -- JSON data sent to/from LLM
 8    user_data TEXT, -- JSON data for UI display
 9    usage_data TEXT, -- JSON data about token usage, etc.
10    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
11    FOREIGN KEY (conversation_id) REFERENCES conversations(conversation_id) ON DELETE CASCADE
12);
13
14-- Index on conversation_id for efficient message retrieval
15CREATE INDEX idx_messages_conversation_id ON messages(conversation_id);
16-- Index on conversation_id and created_at for chronological ordering
17CREATE INDEX idx_messages_conversation_created ON messages(conversation_id, created_at);
18-- Index on type for filtering by message type
19CREATE INDEX idx_messages_type ON messages(type);