ТЗ: Корпоративный RAG-ассистент по базе знаний на Supabase pgvector и Hybrid Search

-- migrations/20261017_rag_hybrid_search.sql
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE IF NOT EXISTS document_chunks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  document_id UUID NOT NULL,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  metadata JSONB DEFAULT '{}'::jsonb,
  embedding vector(1536),
  fts tsvector GENERATED ALWAYS AS (to_tsvector('russian', title || ' ' || content)) STORED,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- HNSW индекс для мгновенного векторного поиска
CREATE INDEX IF NOT EXISTS idx_document_chunks_embedding_hnsw 
ON document_chunks USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Индекс GIN для полнотекстового поиска по ключевым словам
CREATE INDEX IF NOT EXISTS idx_document_chunks_fts 
ON document_chunks USING gin (fts);

-- Функция гибридного поиска (Reciprocal Rank Fusion)
CREATE OR REPLACE FUNCTION match_documents_hybrid(
  query_text TEXT,
  query_embedding vector(1536),
  match_count INT DEFAULT 20,
  rrf_k INT DEFAULT 60
)
RETURNS TABLE (
  id UUID,
  title TEXT,
  content TEXT,
  metadata JSONB,
  similarity FLOAT
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  WITH full_text AS (
    SELECT 
      c.id,
      ROW_NUMBER() OVER (ORDER BY ts_rank_cd(c.fts, websearch_to_tsquery('russian', query_text)) DESC) AS rank_ix
    FROM document_chunks c
    WHERE c.fts @@ websearch_to_tsquery('russian', query_text)
    LIMIT match_count
  ),
  semantic AS (
    SELECT 
      c.id,
      ROW_NUMBER() OVER (ORDER BY c.embedding <=> query_embedding) AS rank_ix
    FROM document_chunks c
    ORDER BY c.embedding <=> query_embedding
    LIMIT match_count
  )
  SELECT 
    c.id,
    c.title,
    c.content,
    c.metadata,
    COALESCE(1.0 / (rrf_k + ft.rank_ix), 0.0) + COALESCE(1.0 / (rrf_k + sem.rank_ix), 0.0) AS similarity
  FROM full_text ft
  FULL OUTER JOIN semantic sem ON ft.id = sem.id
  JOIN document_chunks c ON c.id = COALESCE(ft.id, sem.id)
  ORDER BY similarity DESC
  LIMIT match_count;
END;
$$;