1-- Conversations table
 2-- Each conversation represents a single chat session with the AI agent
 3
 4-- Create migrations tracking table
 5CREATE TABLE migrations (
 6    migration_number INTEGER PRIMARY KEY,
 7    migration_name TEXT NOT NULL,
 8    executed_at DATETIME DEFAULT CURRENT_TIMESTAMP
 9);
10
11CREATE TABLE conversations (
12    conversation_id TEXT PRIMARY KEY,
13    slug TEXT, -- human-readable identifier, can be null initially
14    user_initiated BOOLEAN NOT NULL DEFAULT TRUE, -- FALSE for subagent/tool conversations
15    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
16    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
17);
18
19-- Partial unique index on slug (only for non-NULL values) for uniqueness and faster lookups
20CREATE UNIQUE INDEX idx_conversations_slug_unique ON conversations(slug) WHERE slug IS NOT NULL;
21-- Index on updated_at for ordering by recent activity
22CREATE INDEX idx_conversations_updated_at ON conversations(updated_at DESC);