initial agent setup

2 files changed, 43 insertions, 3 deletions

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;