7 files changed, 178 insertions, 0 deletions
diff --git a/storage/migrations/001_init.up.sql b/storage/migrations/001_init.up.sql
index 8980ccf..09bb5e6 100644
--- a/storage/migrations/001_init.up.sql
+++ b/storage/migrations/001_init.up.sql
@@ -2,6 +2,7 @@ CREATE TABLE IF NOT EXISTS chats (
     id INTEGER PRIMARY KEY AUTOINCREMENT,
     name TEXT NOT NULL,
     msgs TEXT NOT NULL,
+    agent TEXT NOT NULL DEFAULT 'assistant',
     created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
     updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
 );
diff --git a/storage/migrations/002_add_vector.down.sql b/storage/migrations/002_add_vector.down.sql
new file mode 100644
index 0000000..a257b11
--- /dev/null
+++ b/storage/migrations/002_add_vector.down.sql
@@ -0,0 +1,34 @@
+-- Drop vector storage tables
+DROP INDEX IF EXISTS idx_embeddings_384_filename;
+DROP INDEX IF EXISTS idx_embeddings_768_filename;
+DROP INDEX IF EXISTS idx_embeddings_1024_filename;
+DROP INDEX IF EXISTS idx_embeddings_1536_filename;
+DROP INDEX IF EXISTS idx_embeddings_2048_filename;
+DROP INDEX IF EXISTS idx_embeddings_3072_filename;
+DROP INDEX IF EXISTS idx_embeddings_4096_filename;
+DROP INDEX IF EXISTS idx_embeddings_5120_filename;
+DROP INDEX IF EXISTS idx_embeddings_384_slug;
+DROP INDEX IF EXISTS idx_embeddings_768_slug;
+DROP INDEX IF EXISTS idx_embeddings_1024_slug;
+DROP INDEX IF EXISTS idx_embeddings_1536_slug;
+DROP INDEX IF EXISTS idx_embeddings_2048_slug;
+DROP INDEX IF EXISTS idx_embeddings_3072_slug;
+DROP INDEX IF EXISTS idx_embeddings_4096_slug;
+DROP INDEX IF EXISTS idx_embeddings_5120_slug;
+DROP INDEX IF EXISTS idx_embeddings_384_created_at;
+DROP INDEX IF EXISTS idx_embeddings_768_created_at;
+DROP INDEX IF EXISTS idx_embeddings_1024_created_at;
+DROP INDEX IF EXISTS idx_embeddings_1536_created_at;
+DROP INDEX IF EXISTS idx_embeddings_2048_created_at;
+DROP INDEX IF EXISTS idx_embeddings_3072_created_at;
+DROP INDEX IF EXISTS idx_embeddings_4096_created_at;
+DROP INDEX IF EXISTS idx_embeddings_5120_created_at;
+
+DROP TABLE IF EXISTS embeddings_384;
+DROP TABLE IF EXISTS embeddings_768;
+DROP TABLE IF EXISTS embeddings_1024;
+DROP TABLE IF EXISTS embeddings_1536;
+DROP TABLE IF EXISTS embeddings_2048;
+DROP TABLE IF EXISTS embeddings_3072;
+DROP TABLE IF EXISTS embeddings_4096;
+DROP TABLE IF EXISTS embeddings_5120;
+\ No newline at end of file
diff --git a/storage/migrations/002_add_vector.up.sql b/storage/migrations/002_add_vector.up.sql
new file mode 100644
index 0000000..baf703d
--- /dev/null
+++ b/storage/migrations/002_add_vector.up.sql
@@ -0,0 +1,98 @@
+-- Create tables for vector storage (replacing vec0 plugin usage)
+CREATE TABLE IF NOT EXISTS embeddings_384 (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    embeddings BLOB NOT NULL,
+    slug TEXT NOT NULL,
+    raw_text TEXT NOT NULL,
+    filename TEXT NOT NULL DEFAULT '',
+    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+);
+
+CREATE TABLE IF NOT EXISTS embeddings_768 (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    embeddings BLOB NOT NULL,
+    slug TEXT NOT NULL,
+    raw_text TEXT NOT NULL,
+    filename TEXT NOT NULL DEFAULT '',
+    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+);
+
+CREATE TABLE IF NOT EXISTS embeddings_1024 (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    embeddings BLOB NOT NULL,
+    slug TEXT NOT NULL,
+    raw_text TEXT NOT NULL,
+    filename TEXT NOT NULL DEFAULT '',
+    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+);
+
+CREATE TABLE IF NOT EXISTS embeddings_1536 (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    embeddings BLOB NOT NULL,
+    slug TEXT NOT NULL,
+    raw_text TEXT NOT NULL,
+    filename TEXT NOT NULL DEFAULT '',
+    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+);
+
+CREATE TABLE IF NOT EXISTS embeddings_2048 (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    embeddings BLOB NOT NULL,
+    slug TEXT NOT NULL,
+    raw_text TEXT NOT NULL,
+    filename TEXT NOT NULL DEFAULT '',
+    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+);
+
+CREATE TABLE IF NOT EXISTS embeddings_3072 (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    embeddings BLOB NOT NULL,
+    slug TEXT NOT NULL,
+    raw_text TEXT NOT NULL,
+    filename TEXT NOT NULL DEFAULT '',
+    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+);
+
+CREATE TABLE IF NOT EXISTS embeddings_4096 (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    embeddings BLOB NOT NULL,
+    slug TEXT NOT NULL,
+    raw_text TEXT NOT NULL,
+    filename TEXT NOT NULL DEFAULT '',
+    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+);
+
+CREATE TABLE IF NOT EXISTS embeddings_5120 (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    embeddings BLOB NOT NULL,
+    slug TEXT NOT NULL,
+    raw_text TEXT NOT NULL,
+    filename TEXT NOT NULL DEFAULT '',
+    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+);
+
+-- Indexes for better performance
+CREATE INDEX IF NOT EXISTS idx_embeddings_384_filename ON embeddings_384(filename);
+CREATE INDEX IF NOT EXISTS idx_embeddings_768_filename ON embeddings_768(filename);
+CREATE INDEX IF NOT EXISTS idx_embeddings_1024_filename ON embeddings_1024(filename);
+CREATE INDEX IF NOT EXISTS idx_embeddings_1536_filename ON embeddings_1536(filename);
+CREATE INDEX IF NOT EXISTS idx_embeddings_2048_filename ON embeddings_2048(filename);
+CREATE INDEX IF NOT EXISTS idx_embeddings_3072_filename ON embeddings_3072(filename);
+CREATE INDEX IF NOT EXISTS idx_embeddings_4096_filename ON embeddings_4096(filename);
+CREATE INDEX IF NOT EXISTS idx_embeddings_5120_filename ON embeddings_5120(filename);
+CREATE INDEX IF NOT EXISTS idx_embeddings_384_slug ON embeddings_384(slug);
+CREATE INDEX IF NOT EXISTS idx_embeddings_768_slug ON embeddings_768(slug);
+CREATE INDEX IF NOT EXISTS idx_embeddings_1024_slug ON embeddings_1024(slug);
+CREATE INDEX IF NOT EXISTS idx_embeddings_1536_slug ON embeddings_1536(slug);
+CREATE INDEX IF NOT EXISTS idx_embeddings_2048_slug ON embeddings_2048(slug);
+CREATE INDEX IF NOT EXISTS idx_embeddings_3072_slug ON embeddings_3072(slug);
+CREATE INDEX IF NOT EXISTS idx_embeddings_4096_slug ON embeddings_4096(slug);
+CREATE INDEX IF NOT EXISTS idx_embeddings_5120_slug ON embeddings_5120(slug);
+CREATE INDEX IF NOT EXISTS idx_embeddings_384_created_at ON embeddings_384(created_at);
+CREATE INDEX IF NOT EXISTS idx_embeddings_768_created_at ON embeddings_768(created_at);
+CREATE INDEX IF NOT EXISTS idx_embeddings_1024_created_at ON embeddings_1024(created_at);
+CREATE INDEX IF NOT EXISTS idx_embeddings_1536_created_at ON embeddings_1536(created_at);
+CREATE INDEX IF NOT EXISTS idx_embeddings_2048_created_at ON embeddings_2048(created_at);
+CREATE INDEX IF NOT EXISTS idx_embeddings_3072_created_at ON embeddings_3072(created_at);
+CREATE INDEX IF NOT EXISTS idx_embeddings_4096_created_at ON embeddings_4096(created_at);
+CREATE INDEX IF NOT EXISTS idx_embeddings_5120_created_at ON embeddings_5120(created_at);
diff --git a/storage/migrations/003_add_fts.down.sql b/storage/migrations/003_add_fts.down.sql
new file mode 100644
index 0000000..e565fd5
--- /dev/null
+++ b/storage/migrations/003_add_fts.down.sql
@@ -0,0 +1,2 @@
+-- Drop FTS5 virtual table
+DROP TABLE IF EXISTS fts_embeddings;
+\ No newline at end of file
diff --git a/storage/migrations/003_add_fts.up.sql b/storage/migrations/003_add_fts.up.sql
new file mode 100644
index 0000000..114586a
--- /dev/null
+++ b/storage/migrations/003_add_fts.up.sql
@@ -0,0 +1,15 @@
+-- Create FTS5 virtual table for full-text search
+CREATE VIRTUAL TABLE IF NOT EXISTS fts_embeddings USING fts5(
+    slug UNINDEXED,
+    raw_text,
+    filename UNINDEXED,
+    embedding_size UNINDEXED,
+    tokenize='porter unicode61'  -- Use porter stemmer and unicode61 tokenizer
+);
+
+-- Create triggers to maintain FTS table when embeddings are inserted/deleted
+-- Note: We'll handle inserts/deletes programmatically for simplicity
+-- but triggers could be added here if needed.
+
+-- Indexes for performance (FTS5 manages its own indexes)
+-- No additional indexes needed for FTS5 virtual table.
+\ No newline at end of file
diff --git a/storage/migrations/004_populate_fts.down.sql b/storage/migrations/004_populate_fts.down.sql
new file mode 100644
index 0000000..2b5c756
--- /dev/null
+++ b/storage/migrations/004_populate_fts.down.sql
@@ -0,0 +1,2 @@
+-- Clear FTS table (optional)
+DELETE FROM fts_embeddings;
+\ No newline at end of file
diff --git a/storage/migrations/004_populate_fts.up.sql b/storage/migrations/004_populate_fts.up.sql
new file mode 100644
index 0000000..1d1b16a
--- /dev/null
+++ b/storage/migrations/004_populate_fts.up.sql
@@ -0,0 +1,26 @@
+-- Populate FTS table with existing embeddings
+DELETE FROM fts_embeddings;
+
+INSERT INTO fts_embeddings (slug, raw_text, filename, embedding_size)
+SELECT slug, raw_text, filename, 384 FROM embeddings_384;
+
+INSERT INTO fts_embeddings (slug, raw_text, filename, embedding_size)
+SELECT slug, raw_text, filename, 768 FROM embeddings_768;
+
+INSERT INTO fts_embeddings (slug, raw_text, filename, embedding_size)
+SELECT slug, raw_text, filename, 1024 FROM embeddings_1024;
+
+INSERT INTO fts_embeddings (slug, raw_text, filename, embedding_size)
+SELECT slug, raw_text, filename, 1536 FROM embeddings_1536;
+
+INSERT INTO fts_embeddings (slug, raw_text, filename, embedding_size)
+SELECT slug, raw_text, filename, 2048 FROM embeddings_2048;
+
+INSERT INTO fts_embeddings (slug, raw_text, filename, embedding_size)
+SELECT slug, raw_text, filename, 3072 FROM embeddings_3072;
+
+INSERT INTO fts_embeddings (slug, raw_text, filename, embedding_size)
+SELECT slug, raw_text, filename, 4096 FROM embeddings_4096;
+
+INSERT INTO fts_embeddings (slug, raw_text, filename, embedding_size)
+SELECT slug, raw_text, filename, 5120 FROM embeddings_5120;
+\ No newline at end of file