chore: refactor db

3 files changed, 40 insertions, 40 deletions

diff --git a/internal/db/migrations/20250502063010_add_summary_to_sessions.sql b/internal/db/migrations/20250502063010_add_summary_to_sessions.sql
deleted file mode 100644
index 9cecfcedb..000000000
--- a/internal/db/migrations/20250502063010_add_summary_to_sessions.sql
+++ /dev/null
@@ -1,11 +0,0 @@
--- +goose Up
--- +goose StatementBegin
-ALTER TABLE sessions ADD COLUMN summary TEXT;
-ALTER TABLE sessions ADD COLUMN summarized_at INTEGER;
--- +goose StatementEnd
-
--- +goose Down
--- +goose StatementBegin
-ALTER TABLE sessions DROP COLUMN summarized_at;
-ALTER TABLE sessions DROP COLUMN summary;
--- +goose StatementEnd
\ No newline at end of file
diff --git a/internal/db/migrations/20250508122310_create_logs_table.sql b/internal/db/migrations/20250508122310_create_logs_table.sql
deleted file mode 100644
index f4876fbae..000000000
--- a/internal/db/migrations/20250508122310_create_logs_table.sql
+++ /dev/null
@@ -1,16 +0,0 @@
--- +goose Up
-CREATE TABLE logs (
-    id TEXT PRIMARY KEY,
-    session_id TEXT REFERENCES sessions(id) ON DELETE CASCADE,
-    timestamp INTEGER NOT NULL,
-    level TEXT NOT NULL,
-    message TEXT NOT NULL,
-    attributes TEXT,
-    created_at INTEGER NOT NULL
-);
-
-CREATE INDEX logs_session_id_idx ON logs(session_id);
-CREATE INDEX logs_timestamp_idx ON logs(timestamp);
-
--- +goose Down
-DROP TABLE logs;
\ No newline at end of file
diff --git a/internal/db/migrations/20250424200609_initial.sql b/internal/db/migrations/20250513000000_initial.sql
index 02caecf0c..ad97a4ad3 100644
--- a/internal/db/migrations/20250424200609_initial.sql
+++ b/internal/db/migrations/20250513000000_initial.sql
@@ -6,17 +6,19 @@ CREATE TABLE IF NOT EXISTS sessions (
     parent_session_id TEXT,
     title TEXT NOT NULL,
     message_count INTEGER NOT NULL DEFAULT 0 CHECK (message_count >= 0),
-    prompt_tokens  INTEGER NOT NULL DEFAULT 0 CHECK (prompt_tokens >= 0),
-    completion_tokens  INTEGER NOT NULL DEFAULT 0 CHECK (completion_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
+    summary TEXT,
+    summarized_at TEXT,
+    updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000Z', 'now')),
+    created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000Z', 'now'))
 );
 
 CREATE TRIGGER IF NOT EXISTS update_sessions_updated_at
 AFTER UPDATE ON sessions
 BEGIN
-UPDATE sessions SET updated_at = strftime('%s', 'now')
+UPDATE sessions SET updated_at = strftime('%Y-%m-%dT%H:%M:%f000Z', 'now')
 WHERE id = new.id;
 END;
 
@@ -27,8 +29,9 @@ CREATE TABLE IF NOT EXISTS files (
     path TEXT NOT NULL,
     content TEXT NOT NULL,
     version TEXT NOT NULL,
-    created_at INTEGER NOT NULL,  -- Unix timestamp in milliseconds
-    updated_at INTEGER NOT NULL,  -- Unix timestamp in milliseconds
+    is_new INTEGER DEFAULT 0,
+    created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000Z', 'now')),
+    updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000Z', 'now')),
     FOREIGN KEY (session_id) REFERENCES sessions (id) ON DELETE CASCADE,
     UNIQUE(path, session_id, version)
 );
@@ -39,7 +42,7 @@ CREATE INDEX IF NOT EXISTS idx_files_path ON files (path);
 CREATE TRIGGER IF NOT EXISTS update_files_updated_at
 AFTER UPDATE ON files
 BEGIN
-UPDATE files SET updated_at = strftime('%s', 'now')
+UPDATE files SET updated_at = strftime('%Y-%m-%dT%H:%M:%f000Z', 'now')
 WHERE id = new.id;
 END;
 
@@ -50,9 +53,9 @@ CREATE TABLE IF NOT EXISTS messages (
     role TEXT NOT NULL,
     parts TEXT NOT NULL default '[]',
     model TEXT,
-    created_at INTEGER NOT NULL,  -- Unix timestamp in milliseconds
-    updated_at INTEGER NOT NULL,  -- Unix timestamp in milliseconds
-    finished_at INTEGER,  -- Unix timestamp in milliseconds
+    created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000Z', 'now')),
+    updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000Z', 'now')),
+    finished_at TEXT,
     FOREIGN KEY (session_id) REFERENCES sessions (id) ON DELETE CASCADE
 );
 
@@ -61,7 +64,7 @@ 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')
+UPDATE messages SET updated_at = strftime('%Y-%m-%dT%H:%M:%f000Z', 'now')
 WHERE id = new.id;
 END;
 
@@ -81,6 +84,28 @@ UPDATE sessions SET
 WHERE id = old.session_id;
 END;
 
+-- Logs
+CREATE TABLE IF NOT EXISTS logs (
+    id TEXT PRIMARY KEY,
+    session_id TEXT REFERENCES sessions(id) ON DELETE CASCADE,
+    timestamp TEXT NOT NULL,
+    level TEXT NOT NULL,
+    message TEXT NOT NULL,
+    attributes TEXT,
+    created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000Z', 'now')),
+    updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000Z', 'now'))
+);
+
+CREATE INDEX logs_session_id_idx ON logs(session_id);
+CREATE INDEX logs_timestamp_idx ON logs(timestamp);
+
+CREATE TRIGGER IF NOT EXISTS update_logs_updated_at
+AFTER UPDATE ON logs
+BEGIN
+UPDATE logs SET updated_at = strftime('%Y-%m-%dT%H:%M:%f000Z', 'now')
+WHERE id = new.id;
+END;
+
 -- +goose StatementEnd
 
 -- +goose Down
@@ -88,11 +113,13 @@ END;
 DROP TRIGGER IF EXISTS update_sessions_updated_at;
 DROP TRIGGER IF EXISTS update_messages_updated_at;
 DROP TRIGGER IF EXISTS update_files_updated_at;
+DROP TRIGGER IF EXISTS update_logs_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 logs;
 DROP TABLE IF EXISTS messages;
 DROP TABLE IF EXISTS files;
+DROP TABLE IF EXISTS sessions;
 -- +goose StatementEnd