1-- Add 'error' to the message type check constraint
 2-- This requires dropping and recreating the messages table with the new constraint
 3-- SQLite doesn't support ALTER TABLE to modify CHECK constraints
 4
 5-- Step 1: Create a new messages table with the updated constraint
 6CREATE TABLE messages_new (
 7    message_id TEXT PRIMARY KEY,
 8    conversation_id TEXT NOT NULL,
 9    sequence_id INTEGER NOT NULL,
10    type TEXT NOT NULL CHECK (type IN ('user', 'agent', 'tool', 'system', 'error')),
11    llm_data TEXT, -- JSON data sent to/from LLM
12    user_data TEXT, -- JSON data for UI display
13    usage_data TEXT, -- JSON data about token usage, etc.
14    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
15    FOREIGN KEY (conversation_id) REFERENCES conversations(conversation_id) ON DELETE CASCADE
16);
17
18-- Step 2: Copy data from old table to new table
19INSERT INTO messages_new SELECT * FROM messages;
20
21-- Step 3: Drop the old table
22DROP TABLE messages;
23
24-- Step 4: Rename the new table
25ALTER TABLE messages_new RENAME TO messages;
26
27-- Step 5: Recreate indexes
28CREATE INDEX idx_messages_conversation_id ON messages(conversation_id);
29CREATE INDEX idx_messages_conversation_sequence ON messages(conversation_id, sequence_id);
30CREATE INDEX idx_messages_type ON messages(type);