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