add initial stuff

2 files changed, 21 insertions, 0 deletions

diff --git a/internal/db/migrations/000001_initial.down.sql b/internal/db/migrations/000001_initial.down.sql
new file mode 100644
index 000000000..7324ddb6a
--- /dev/null
+++ b/internal/db/migrations/000001_initial.down.sql
@@ -0,0 +1,4 @@
+-- sqlfluff:dialect:sqlite
+DROP TRIGGER IF EXISTS update_sessions_updated_at;
+
+DROP TABLE IF EXISTS sessions;
diff --git a/internal/db/migrations/000001_initial.up.sql b/internal/db/migrations/000001_initial.up.sql
new file mode 100644
index 000000000..115b6145b
--- /dev/null
+++ b/internal/db/migrations/000001_initial.up.sql
@@ -0,0 +1,17 @@
+-- sqlfluff:dialect:sqlite
+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),
+    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
+);
+
+CREATE TRIGGER IF NOT EXISTS update_sessions_updated_at
+AFTER UPDATE ON sessions
+BEGIN
+UPDATE sessions SET updated_at = strftime('%s', 'now')
+WHERE id = new.id;
+END;