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    model TEXT,
28    created_at INTEGER NOT NULL,  -- Unix timestamp in milliseconds
29    updated_at INTEGER NOT NULL,  -- Unix timestamp in milliseconds
30    finished_at INTEGER,  -- Unix timestamp in milliseconds
31    FOREIGN KEY (session_id) REFERENCES sessions (id) ON DELETE CASCADE
32);
33
34CREATE INDEX IF NOT EXISTS idx_messages_session_id ON messages (session_id);
35
36CREATE TRIGGER IF NOT EXISTS update_messages_updated_at
37AFTER UPDATE ON messages
38BEGIN
39UPDATE messages SET updated_at = strftime('%s', 'now')
40WHERE id = new.id;
41END;
42
43CREATE TRIGGER IF NOT EXISTS update_session_message_count_on_insert
44AFTER INSERT ON messages
45BEGIN
46UPDATE sessions SET
47    message_count = message_count + 1
48WHERE id = new.session_id;
49END;
50
51CREATE TRIGGER IF NOT EXISTS update_session_message_count_on_delete
52AFTER DELETE ON messages
53BEGIN
54UPDATE sessions SET
55    message_count = message_count - 1
56WHERE id = old.session_id;
57END;