supabase/rag_upgrade_migration.sql

/* ===========================================================================
   RAG upgrade migration for public.rag_user_documents
   Run this in the Supabase SQL editor (or via `supabase db` / apply_migration).

   Adds:
     - pgvector embedding column (vector(768), BAAI/bge-base-en-v1.5)
     - metadata columns: source_type, url, created_at, chunk_type
     - full-text search column + GIN index (hybrid search)
     - HNSW index on the embedding (fast ANN)
     - match_rag_documents() RPC: vector + keyword search fused with RRF,
       with a similarity threshold and metadata filters.

   NOTE: the embedding column is migrated from double precision[] to
   vector(768). Existing rows must already hold 768-length vectors (they do,
   for bge-base-en-v1.5). Any rows produced with a different-dimension model
   must be re-embedded (re-run /chunk_pdf or /chunk_url) after this migration.
   =========================================================================== */

-- pgvector is created `with schema extensions` in this project; Supabase keeps
-- `extensions` on the search_path, so `vector`, `<=>` and `vector_cosine_ops`
-- resolve unqualified.
create extension if not exists vector with schema extensions;

-- ---------------------------------------------------------------------------
-- 1) Schema changes
-- ---------------------------------------------------------------------------

-- Embedding: double precision[]  ->  vector(768)
alter table public.rag_user_documents
  alter column embedding type vector(768)
  using (embedding::text::vector(768));

-- Metadata columns
alter table public.rag_user_documents
  add column if not exists source_type text,
  add column if not exists url text,
  add column if not exists created_at timestamptz not null default now(),
  add column if not exists chunk_type text;

-- Full-text search column (generated from content)
alter table public.rag_user_documents
  add column if not exists fts tsvector
  generated always as (to_tsvector('english', coalesce(content, ''))) stored;

-- ---------------------------------------------------------------------------
-- 2) Indexes
-- ---------------------------------------------------------------------------

create index if not exists ix_rag_user_documents_embedding
  on public.rag_user_documents using hnsw (embedding vector_cosine_ops);

create index if not exists ix_rag_user_documents_fts
  on public.rag_user_documents using gin (fts);

create index if not exists ix_rag_user_documents_user_id
  on public.rag_user_documents using btree (user_id);

create index if not exists ix_rag_user_documents_source_type
  on public.rag_user_documents using btree (source_type);

create index if not exists ix_rag_user_documents_created_at
  on public.rag_user_documents using btree (created_at);

-- ---------------------------------------------------------------------------
-- 3) Hybrid retrieval RPC (vector + full-text, fused with Reciprocal Rank Fusion)
-- ---------------------------------------------------------------------------

create or replace function public.match_rag_documents(
  query_embedding    vector(768),
  query_text         text,
  p_user_id          uuid,
  match_threshold    float       default 0.3,
  match_count        int         default 50,
  filter_source_type text        default null,
  filter_url         text        default null,
  created_after      timestamptz default null,
  rrf_k              int         default 60
)
returns table (
  id          uuid,
  content     text,
  source_type text,
  url         text,
  created_at  timestamptz,
  doc_id      uuid,
  chunk_type  text,
  similarity  float,
  rrf_score   float
)
language sql
stable
security definer
set search_path = public, extensions
as $$
  with base as (
    select d.*
    from public.rag_user_documents d
    where (p_user_id is null or d.user_id = p_user_id)
      and (filter_source_type is null or d.source_type = filter_source_type)
      and (filter_url is null or d.url ilike '%' || filter_url || '%')
      and (created_after is null or d.created_at >= created_after)
  ),
  -- Semantic half: only vectors above the similarity threshold
  vector_search as (
    select
      base.id,
      1 - (base.embedding <=> query_embedding) as similarity,
      row_number() over (order by base.embedding <=> query_embedding) as rank
    from base
    where 1 - (base.embedding <=> query_embedding) >= match_threshold
    order by base.embedding <=> query_embedding
    limit match_count
  ),
  -- Keyword half: full-text matches (independent of the vector threshold)
  keyword_search as (
    select
      base.id,
      row_number() over (
        order by ts_rank_cd(base.fts, websearch_to_tsquery('english', query_text)) desc
      ) as rank
    from base
    where query_text is not null
      and query_text <> ''
      and base.fts @@ websearch_to_tsquery('english', query_text)
    order by ts_rank_cd(base.fts, websearch_to_tsquery('english', query_text)) desc
    limit match_count
  ),
  fused as (
    select
      coalesce(v.id, k.id) as id,
      coalesce(1.0 / (rrf_k + v.rank), 0.0)
        + coalesce(1.0 / (rrf_k + k.rank), 0.0) as rrf_score,
      v.similarity as similarity
    from vector_search v
    full outer join keyword_search k on v.id = k.id
  )
  select
    b.id,
    b.content,
    b.source_type,
    b.url,
    b.created_at,
    b.doc_id,
    b.chunk_type,
    -- when a row is keyword-only, recompute similarity for display
    coalesce(f.similarity, 1 - (b.embedding <=> query_embedding)) as similarity,
    f.rrf_score
  from fused f
  join public.rag_user_documents b on b.id = f.id
  order by f.rrf_score desc
  limit match_count;
$$;