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