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-- Files
22CREATE TABLE IF NOT EXISTS files (
23    id TEXT PRIMARY KEY,
24    session_id TEXT NOT NULL,
25    path TEXT NOT NULL,
26    content TEXT NOT NULL,
27    version TEXT NOT NULL,
28    created_at INTEGER NOT NULL,  -- Unix timestamp in milliseconds
29    updated_at INTEGER NOT NULL,  -- Unix timestamp in milliseconds
30    FOREIGN KEY (session_id) REFERENCES sessions (id) ON DELETE CASCADE
31);
32
33CREATE INDEX IF NOT EXISTS idx_files_session_id ON files (session_id);
34CREATE INDEX IF NOT EXISTS idx_files_path ON files (path);
35
36CREATE TRIGGER IF NOT EXISTS update_files_updated_at
37AFTER UPDATE ON files
38BEGIN
39UPDATE files SET updated_at = strftime('%s', 'now')
40WHERE id = new.id;
41END;
42
43-- Messages
44CREATE TABLE IF NOT EXISTS messages (
45    id TEXT PRIMARY KEY,
46    session_id TEXT NOT NULL,
47    role TEXT NOT NULL,
48    parts TEXT NOT NULL default '[]',
49    model TEXT,
50    created_at INTEGER NOT NULL,  -- Unix timestamp in milliseconds
51    updated_at INTEGER NOT NULL,  -- Unix timestamp in milliseconds
52    finished_at INTEGER,  -- Unix timestamp in milliseconds
53    FOREIGN KEY (session_id) REFERENCES sessions (id) ON DELETE CASCADE
54);
55
56CREATE INDEX IF NOT EXISTS idx_messages_session_id ON messages (session_id);
57
58CREATE TRIGGER IF NOT EXISTS update_messages_updated_at
59AFTER UPDATE ON messages
60BEGIN
61UPDATE messages SET updated_at = strftime('%s', 'now')
62WHERE id = new.id;
63END;
64
65CREATE TRIGGER IF NOT EXISTS update_session_message_count_on_insert
66AFTER INSERT ON messages
67BEGIN
68UPDATE sessions SET
69    message_count = message_count + 1
70WHERE id = new.session_id;
71END;
72
73CREATE TRIGGER IF NOT EXISTS update_session_message_count_on_delete
74AFTER DELETE ON messages
75BEGIN
76UPDATE sessions SET
77    message_count = message_count - 1
78WHERE id = old.session_id;
79END;