1-- +goose Up
 2-- +goose StatementBegin
 3-- Sessions
 4CREATE TABLE IF NOT EXISTS sessions (
 5    id TEXT PRIMARY KEY,
 6    parent_session_id TEXT,
 7    title TEXT NOT NULL,
 8    message_count INTEGER NOT NULL DEFAULT 0 CHECK (message_count >= 0),
 9    prompt_tokens  INTEGER NOT NULL DEFAULT 0 CHECK (prompt_tokens >= 0),
10    completion_tokens  INTEGER NOT NULL DEFAULT 0 CHECK (completion_tokens>= 0),
11    cost REAL NOT NULL DEFAULT 0.0 CHECK (cost >= 0.0),
12    updated_at INTEGER NOT NULL,  -- Unix timestamp in milliseconds
13    created_at INTEGER NOT NULL   -- Unix timestamp in milliseconds
14);
15
16CREATE TRIGGER IF NOT EXISTS update_sessions_updated_at
17AFTER UPDATE ON sessions
18BEGIN
19UPDATE sessions SET updated_at = strftime('%s', 'now')
20WHERE id = new.id;
21END;
22
23-- Files
24CREATE TABLE IF NOT EXISTS files (
25    id TEXT PRIMARY KEY,
26    session_id TEXT NOT NULL,
27    path TEXT NOT NULL,
28    content TEXT NOT NULL,
29    version INTEGER NOT NULL DEFAULT 0,
30    created_at INTEGER NOT NULL,  -- Unix timestamp in milliseconds
31    updated_at INTEGER NOT NULL,  -- Unix timestamp in milliseconds
32    FOREIGN KEY (session_id) REFERENCES sessions (id) ON DELETE CASCADE,
33    UNIQUE(path, session_id, version)
34);
35
36CREATE INDEX IF NOT EXISTS idx_files_session_id ON files (session_id);
37CREATE INDEX IF NOT EXISTS idx_files_path ON files (path);
38
39CREATE TRIGGER IF NOT EXISTS update_files_updated_at
40AFTER UPDATE ON files
41BEGIN
42UPDATE files SET updated_at = strftime('%s', 'now')
43WHERE id = new.id;
44END;
45
46-- Messages
47CREATE TABLE IF NOT EXISTS messages (
48    id TEXT PRIMARY KEY,
49    session_id TEXT NOT NULL,
50    role TEXT NOT NULL,
51    parts TEXT NOT NULL default '[]',
52    model TEXT,
53    created_at INTEGER NOT NULL,  -- Unix timestamp in milliseconds
54    updated_at INTEGER NOT NULL,  -- Unix timestamp in milliseconds
55    finished_at INTEGER,  -- Unix timestamp in milliseconds
56    FOREIGN KEY (session_id) REFERENCES sessions (id) ON DELETE CASCADE
57);
58
59CREATE INDEX IF NOT EXISTS idx_messages_session_id ON messages (session_id);
60
61CREATE TRIGGER IF NOT EXISTS update_messages_updated_at
62AFTER UPDATE ON messages
63BEGIN
64UPDATE messages SET updated_at = strftime('%s', 'now')
65WHERE id = new.id;
66END;
67
68CREATE TRIGGER IF NOT EXISTS update_session_message_count_on_insert
69AFTER INSERT ON messages
70BEGIN
71UPDATE sessions SET
72    message_count = message_count + 1
73WHERE id = new.session_id;
74END;
75
76CREATE TRIGGER IF NOT EXISTS update_session_message_count_on_delete
77AFTER DELETE ON messages
78BEGIN
79UPDATE sessions SET
80    message_count = message_count - 1
81WHERE id = old.session_id;
82END;
83
84-- +goose StatementEnd
85
86-- +goose Down
87-- +goose StatementBegin
88DROP TRIGGER IF EXISTS update_sessions_updated_at;
89DROP TRIGGER IF EXISTS update_messages_updated_at;
90DROP TRIGGER IF EXISTS update_files_updated_at;
91
92DROP TRIGGER IF EXISTS update_session_message_count_on_delete;
93DROP TRIGGER IF EXISTS update_session_message_count_on_insert;
94
95DROP TABLE IF EXISTS sessions;
96DROP TABLE IF EXISTS messages;
97DROP TABLE IF EXISTS files;
98-- +goose StatementEnd