From e7258e38aeb46281fda474b8b7fcc3eee35edd9f Mon Sep 17 00:00:00 2001 From: Kujtim Hoxha Date: Sun, 23 Mar 2025 22:25:31 +0100 Subject: initial agent setup --- internal/db/migrations/000001_initial.down.sql | 6 +++- internal/db/migrations/000001_initial.up.sql | 40 ++++++++++++++++++++++++-- 2 files changed, 43 insertions(+), 3 deletions(-) (limited to 'internal/db/migrations') diff --git a/internal/db/migrations/000001_initial.down.sql b/internal/db/migrations/000001_initial.down.sql index 7324ddb6a..4f2712d86 100644 --- a/internal/db/migrations/000001_initial.down.sql +++ b/internal/db/migrations/000001_initial.down.sql @@ -1,4 +1,8 @@ --- sqlfluff:dialect:sqlite DROP TRIGGER IF EXISTS update_sessions_updated_at; +DROP TRIGGER IF EXISTS update_messages_updated_at; + +DROP TRIGGER IF EXISTS update_session_message_count_on_delete; +DROP TRIGGER IF EXISTS update_session_message_count_on_insert; DROP TABLE IF EXISTS sessions; +DROP TABLE IF EXISTS messages; diff --git a/internal/db/migrations/000001_initial.up.sql b/internal/db/migrations/000001_initial.up.sql index 115b6145b..b87592bb5 100644 --- a/internal/db/migrations/000001_initial.up.sql +++ b/internal/db/migrations/000001_initial.up.sql @@ -1,9 +1,10 @@ --- sqlfluff:dialect:sqlite +-- Sessions CREATE TABLE IF NOT EXISTS sessions ( id TEXT PRIMARY KEY, title TEXT NOT NULL, message_count INTEGER NOT NULL DEFAULT 0 CHECK (message_count >= 0), - tokens INTEGER NOT NULL DEFAULT 0 CHECK (tokens >= 0), + prompt_tokens INTEGER NOT NULL DEFAULT 0 CHECK (prompt_tokens >= 0), + completion_tokens INTEGER NOT NULL DEFAULT 0 CHECK (completion_tokens>= 0), cost REAL NOT NULL DEFAULT 0.0 CHECK (cost >= 0.0), updated_at INTEGER NOT NULL, -- Unix timestamp in milliseconds created_at INTEGER NOT NULL -- Unix timestamp in milliseconds @@ -15,3 +16,38 @@ BEGIN UPDATE sessions SET updated_at = strftime('%s', 'now') WHERE id = new.id; END; + +-- Messages +CREATE TABLE IF NOT EXISTS messages ( + id TEXT PRIMARY KEY, + session_id TEXT NOT NULL, + message_data TEXT NOT NULL, -- JSON string of message content + created_at INTEGER NOT NULL, -- Unix timestamp in milliseconds + updated_at INTEGER NOT NULL, -- Unix timestamp in milliseconds + FOREIGN KEY (session_id) REFERENCES sessions (id) ON DELETE CASCADE +); + +CREATE INDEX IF NOT EXISTS idx_messages_session_id ON messages (session_id); + +CREATE TRIGGER IF NOT EXISTS update_messages_updated_at +AFTER UPDATE ON messages +BEGIN +UPDATE messages SET updated_at = strftime('%s', 'now') +WHERE id = new.id; +END; + +CREATE TRIGGER IF NOT EXISTS update_session_message_count_on_insert +AFTER INSERT ON messages +BEGIN +UPDATE sessions SET + message_count = message_count + 1 +WHERE id = new.session_id; +END; + +CREATE TRIGGER IF NOT EXISTS update_session_message_count_on_delete +AFTER DELETE ON messages +BEGIN +UPDATE sessions SET + message_count = message_count - 1 +WHERE id = old.session_id; +END; -- cgit v1.2.3