-- Metadata chunks table for RAG indexing
CREATE TABLE IF NOT EXISTS metadata_chunks (
id TEXT PRIMARY KEY,
org_id TEXT NOT NULL,
type TEXT NOT NULL,
name TEXT NOT NULL,
content TEXT NOT NULL,
symbols TEXT[] NOT NULL,
refs JSONB,
path TEXT,
raw JSONB,
embedding VECTOR(384), -- For all-MiniLM-L6-v2
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Vector similarity index (cosine distance)
CREATE INDEX IF NOT EXISTS idx_metadata_chunks_embedding
ON metadata_chunks USING ivfflat (embedding vector_cosine_ops);
-- Full-text search index for content
CREATE INDEX IF NOT EXISTS idx_metadata_chunks_content
ON metadata_chunks USING gin(to_tsvector('english', content));
-- Symbol array index for exact symbol matching
CREATE INDEX IF NOT EXISTS idx_metadata_chunks_symbols
ON metadata_chunks USING gin(symbols);
-- Indexes for common filtering
CREATE INDEX IF NOT EXISTS idx_metadata_chunks_org_type
ON metadata_chunks (org_id, type);
CREATE INDEX IF NOT EXISTS idx_metadata_chunks_name
ON metadata_chunks (name);
-- Trigram index for fuzzy text matching
CREATE INDEX IF NOT EXISTS idx_metadata_chunks_content_trgm
ON metadata_chunks USING gin(content gin_trgm_ops);
