1-- Sessions
 2CREATE TABLE IF NOT EXISTS sessions (
 3    id TEXT PRIMARY KEY,
 4    title TEXT NOT NULL,
 5    message_count INTEGER NOT NULL DEFAULT 0 CHECK (message_count >= 0),
 6    prompt_tokens  INTEGER NOT NULL DEFAULT 0 CHECK (prompt_tokens >= 0),
 7    completion_tokens  INTEGER NOT NULL DEFAULT 0 CHECK (completion_tokens>= 0),
 8    cost REAL NOT NULL DEFAULT 0.0 CHECK (cost >= 0.0),
 9    updated_at INTEGER NOT NULL,  -- Unix timestamp in milliseconds
10    created_at INTEGER NOT NULL   -- Unix timestamp in milliseconds
11);
12
13CREATE TRIGGER IF NOT EXISTS update_sessions_updated_at
14AFTER UPDATE ON sessions
15BEGIN
16UPDATE sessions SET updated_at = strftime('%s', 'now')
17WHERE id = new.id;
18END;
19
20-- Messages
21CREATE TABLE IF NOT EXISTS messages (
22    id TEXT PRIMARY KEY,
23    session_id TEXT NOT NULL,
24    message_data TEXT NOT NULL,  -- JSON string of message content
25    created_at INTEGER NOT NULL,  -- Unix timestamp in milliseconds
26    updated_at INTEGER NOT NULL,  -- Unix timestamp in milliseconds
27    FOREIGN KEY (session_id) REFERENCES sessions (id) ON DELETE CASCADE
28);
29
30CREATE INDEX IF NOT EXISTS idx_messages_session_id ON messages (session_id);
31
32CREATE TRIGGER IF NOT EXISTS update_messages_updated_at
33AFTER UPDATE ON messages
34BEGIN
35UPDATE messages SET updated_at = strftime('%s', 'now')
36WHERE id = new.id;
37END;
38
39CREATE TRIGGER IF NOT EXISTS update_session_message_count_on_insert
40AFTER INSERT ON messages
41BEGIN
42UPDATE sessions SET
43    message_count = message_count + 1
44WHERE id = new.session_id;
45END;
46
47CREATE TRIGGER IF NOT EXISTS update_session_message_count_on_delete
48AFTER DELETE ON messages
49BEGIN
50UPDATE sessions SET
51    message_count = message_count - 1
52WHERE id = old.session_id;
53END;