1-- Add autoincrementing sequence_id to messages table for reliable ordering
 2-- This eliminates timestamp collision issues when multiple messages are created simultaneously
 3
 4-- Create new table with sequence_id column
 5CREATE TABLE messages_new (
 6    message_id TEXT PRIMARY KEY,
 7    conversation_id TEXT NOT NULL,
 8    sequence_id INTEGER NOT NULL,
 9    type TEXT NOT NULL CHECK (type IN ('user', 'agent', 'tool', 'system')),
10    llm_data TEXT, -- JSON data sent to/from LLM
11    user_data TEXT, -- JSON data for UI display
12    usage_data TEXT, -- JSON data about token usage, etc.
13    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
14    FOREIGN KEY (conversation_id) REFERENCES conversations(conversation_id) ON DELETE CASCADE
15);
16
17-- Copy data from old table to new table with sequence_id based on created_at order
18-- Only run if the new table is empty (idempotent)
19INSERT INTO messages_new (message_id, conversation_id, sequence_id, type, llm_data, user_data, usage_data, created_at)
20SELECT 
21    message_id, 
22    conversation_id,
23    ROW_NUMBER() OVER (PARTITION BY conversation_id ORDER BY created_at, message_id) as sequence_id,
24    type, 
25    llm_data, 
26    user_data, 
27    usage_data, 
28    created_at
29FROM messages
30WHERE NOT EXISTS (SELECT 1 FROM messages_new LIMIT 1);
31
32-- Replace old table with new table (only if we have data in the new table)
33-- Check if we need to do the table swap
34DROP TABLE IF EXISTS messages_old;
35ALTER TABLE messages RENAME TO messages_old;
36ALTER TABLE messages_new RENAME TO messages;
37DROP TABLE messages_old;
38
39-- Recreate indexes with sequence_id instead of created_at for ordering
40CREATE INDEX idx_messages_conversation_id ON messages(conversation_id);
41CREATE INDEX idx_messages_conversation_sequence ON messages(conversation_id, sequence_id);
42CREATE INDEX idx_messages_type ON messages(type);