1-- Add 'gitinfo' 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', 'gitinfo')),
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    display_data TEXT, -- JSON data for display purposes
16    FOREIGN KEY (conversation_id) REFERENCES conversations(conversation_id) ON DELETE CASCADE
17);
18
19-- Step 2: Copy data from old table to new table
20INSERT INTO messages_new (message_id, conversation_id, sequence_id, type, llm_data, user_data, usage_data, created_at, display_data)
21SELECT message_id, conversation_id, sequence_id, type, llm_data, user_data, usage_data, created_at, display_data FROM messages;
22
23-- Step 3: Drop the old table
24DROP TABLE messages;
25
26-- Step 4: Rename the new table
27ALTER TABLE messages_new RENAME TO messages;
28
29-- Step 5: Recreate indexes
30CREATE INDEX idx_messages_conversation_id ON messages(conversation_id);
31CREATE INDEX idx_messages_conversation_sequence ON messages(conversation_id, sequence_id);
32CREATE INDEX idx_messages_type ON messages(type);