Spaces:
Sleeping
Sleeping
File size: 3,718 Bytes
b0b150b |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 |
-- ============================================
-- MEXAR RAG Migration Script
-- Run this in Supabase SQL Editor
-- ============================================
-- 1. Enable pgvector extension (if not already)
CREATE EXTENSION IF NOT EXISTS vector;
-- 2. Clear existing chunks (required due to dimension change)
DELETE FROM document_chunks;
-- 3. Alter embedding dimension: 384 → 1024
ALTER TABLE document_chunks
ALTER COLUMN embedding TYPE vector(1024);
-- 4. Add tsvector column for keyword search
ALTER TABLE document_chunks
ADD COLUMN IF NOT EXISTS content_tsvector TSVECTOR;
-- 5. Add chunk metadata columns
ALTER TABLE document_chunks
ADD COLUMN IF NOT EXISTS chunk_index INTEGER,
ADD COLUMN IF NOT EXISTS section_title TEXT,
ADD COLUMN IF NOT EXISTS token_count INTEGER;
-- 6. Create HNSW index for fast cosine similarity
DROP INDEX IF EXISTS chunks_embedding_idx;
DROP INDEX IF EXISTS chunks_embedding_hnsw;
CREATE INDEX chunks_embedding_hnsw
ON document_chunks USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
-- 7. Create GIN index for full-text search
CREATE INDEX IF NOT EXISTS chunks_content_gin
ON document_chunks USING GIN (content_tsvector);
-- 8. Create trigger to auto-update tsvector
CREATE OR REPLACE FUNCTION update_tsvector()
RETURNS TRIGGER AS $$
BEGIN
NEW.content_tsvector := to_tsvector('english', COALESCE(NEW.content, ''));
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS tsvector_update ON document_chunks;
CREATE TRIGGER tsvector_update
BEFORE INSERT OR UPDATE ON document_chunks
FOR EACH ROW EXECUTE FUNCTION update_tsvector();
-- 9. Add agent metadata columns for full Supabase storage
ALTER TABLE agents
ADD COLUMN IF NOT EXISTS knowledge_graph_json JSONB,
ADD COLUMN IF NOT EXISTS domain_signature JSONB,
ADD COLUMN IF NOT EXISTS prompt_analysis JSONB,
ADD COLUMN IF NOT EXISTS compilation_stats JSONB,
ADD COLUMN IF NOT EXISTS chunk_count INTEGER DEFAULT 0;
-- 10. Update existing tsvector data
UPDATE document_chunks
SET content_tsvector = to_tsvector('english', content)
WHERE content_tsvector IS NULL;
-- 11. Create hybrid search function
CREATE OR REPLACE FUNCTION hybrid_search(
query_embedding vector(1024),
query_text text,
target_agent_id integer,
match_count integer DEFAULT 20
)
RETURNS TABLE (
id integer,
content text,
source text,
semantic_rank integer,
keyword_rank integer,
rrf_score float
) AS $$
BEGIN
RETURN QUERY
WITH semantic AS (
SELECT dc.id, dc.content, dc.source,
ROW_NUMBER() OVER (ORDER BY dc.embedding <=> query_embedding)::integer as rank
FROM document_chunks dc
WHERE dc.agent_id = target_agent_id
ORDER BY dc.embedding <=> query_embedding
LIMIT match_count
),
keyword AS (
SELECT dc.id, dc.content, dc.source,
ROW_NUMBER() OVER (ORDER BY ts_rank(dc.content_tsvector, plainto_tsquery('english', query_text)) DESC)::integer as rank
FROM document_chunks dc
WHERE dc.agent_id = target_agent_id
AND dc.content_tsvector @@ plainto_tsquery('english', query_text)
LIMIT match_count
)
SELECT
COALESCE(s.id, k.id) as id,
COALESCE(s.content, k.content) as content,
COALESCE(s.source, k.source) as source,
s.rank as semantic_rank,
k.rank as keyword_rank,
(COALESCE(1.0/(60 + s.rank), 0) + COALESCE(1.0/(60 + k.rank), 0))::float as rrf_score
FROM semantic s
FULL OUTER JOIN keyword k ON s.id = k.id
ORDER BY rrf_score DESC
LIMIT match_count;
END;
$$ LANGUAGE plpgsql;
-- Done! Verify with:
-- SELECT * FROM pg_indexes WHERE tablename = 'document_chunks';
|