supabase_knowledge_setup.sql

-- SQL Script to setup Knowledge Base in Supabase

-- 1. Enable pgvector if not already enabled
create extension if not exists vector;

-- 2. Create roadmap_knowledge table
create table if not exists public.roadmap_knowledge (
  id uuid primary key default gen_random_uuid(),
  topic_title text not null,
  content text not null, -- The JSON representation or text explanation of the topic/phase
  embedding vector(768), -- Using Gemini text-embedding-004 which has 768 dimensions
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- 3. Create index for fast vector search
create index if not exists roadmap_knowledge_embedding_idx 
on public.roadmap_knowledge 
using ivfflat (embedding vector_cosine_ops)
with (lists = 100);

-- 4. Create RPC function for similarity search
create or replace function match_knowledge (
  query_embedding vector(768),
  match_threshold float,
  match_count int
)
returns table (
  id uuid,
  topic_title text,
  content text,
  similarity float
)
language sql stable
as $$
  select
    roadmap_knowledge.id,
    roadmap_knowledge.topic_title,
    roadmap_knowledge.content,
    1 - (roadmap_knowledge.embedding <=> query_embedding) as similarity
  from roadmap_knowledge
  where 1 - (roadmap_knowledge.embedding <=> query_embedding) > match_threshold
  order by roadmap_knowledge.embedding <=> query_embedding
  limit match_count;
$$;