Fix: migration use of vec0; rag cleanup

1 files changed, 23 insertions, 9 deletions

diff --git a/storage/migrations/002_add_vector.up.sql b/storage/migrations/002_add_vector.up.sql
index 2ac4621..6e164ce 100644
--- a/storage/migrations/002_add_vector.up.sql
+++ b/storage/migrations/002_add_vector.up.sql
@@ -1,12 +1,26 @@
---CREATE VIRTUAL TABLE IF NOT EXISTS embeddings_5120 USING vec0(
---    embedding FLOAT[5120],
---    slug TEXT NOT NULL,
---    raw_text TEXT PRIMARY KEY,
---);
+-- 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 VIRTUAL TABLE IF NOT EXISTS embeddings_384 USING vec0(
-    embedding FLOAT[384],
+CREATE TABLE IF NOT EXISTS embeddings_5120 (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    embeddings BLOB NOT NULL,
     slug TEXT NOT NULL,
-    raw_text TEXT PRIMARY KEY,
-    filename TEXT NOT NULL DEFAULT ''
+    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_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_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_5120_created_at ON embeddings_5120(created_at);