Enterprise Vector RAG Architecture: Sub-Second Semantic Search, Chunking & Zero-Hallucination Retrieval

CREATE OR REPLACE FUNCTION hybrid_document_search(
  query_text TEXT,
  query_embedding vector(1536),
  match_count INT DEFAULT 10,
  rrf_k INT DEFAULT 60
)
RETURNS TABLE (
  chunk_id UUID,
  document_title TEXT,
  chunk_content TEXT,
  hybrid_score FLOAT
) LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY
  WITH semantic_search AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> query_embedding) as rank
    FROM document_chunks
    ORDER BY embedding <=> query_embedding
    LIMIT match_count * 2
  ),
  keyword_search AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY ts_rank_cd(to_tsvector('english', content), plainto_tsquery('english', query_text)) DESC) as rank
    FROM document_chunks
    WHERE to_tsvector('english', content) @@ plainto_tsquery('english', query_text)
    LIMIT match_count * 2
  )
  SELECT
    c.id as chunk_id,
    c.document_title,
    c.content as chunk_content,
    COALESCE(1.0 / (rrf_k + s.rank), 0.0) +
    COALESCE(1.0 / (rrf_k + k.rank), 0.0) AS hybrid_score
  FROM document_chunks c
  LEFT JOIN semantic_search s ON c.id = s.id
  LEFT JOIN keyword_search k ON c.id = k.id
  WHERE s.id IS NOT NULL OR k.id IS NOT NULL
  ORDER BY hybrid_score DESC
  LIMIT match_count;
END;
$$;