git.iko.soy/crush
on 94de6bc help sign in
crush/internal/db/migrations/20250424200609_initial.sql
98 lines 3.0 kB history raw
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);
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;
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);
36CREATE INDEX IF NOT EXISTS idx_files_session_id ON files (session_id);
37CREATE INDEX IF NOT EXISTS idx_files_path ON files (path);
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;
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);
59CREATE INDEX IF NOT EXISTS idx_messages_session_id ON messages (session_id);
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;
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;
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;
84-- +goose StatementEnd
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;
92DROP TRIGGER IF EXISTS update_session_message_count_on_delete;
93DROP TRIGGER IF EXISTS update_session_message_count_on_insert;
95DROP TABLE IF EXISTS sessions;
96DROP TABLE IF EXISTS messages;
97DROP TABLE IF EXISTS files;
98-- +goose StatementEnd