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