ims/supabase/migrations/20260713000001_gemini_embeddings.sql

-- Migrate embeddings from Voyage AI (1024-dim) to Gemini text-embedding-004 (768-dim)

-- Drop dependent objects first
drop index if exists incidents_embedding_idx;
drop function if exists match_incidents;

-- Replace column (dimension change requires drop+add)
alter table incidents drop column if exists embedding;
alter table incidents add column embedding vector(768);

-- Recreate index
create index incidents_embedding_idx
  on incidents using ivfflat (embedding vector_cosine_ops)
  with (lists = 10);

-- Recreate similarity function at 768-dim
create or replace function match_incidents(
  query_embedding vector(768),
  exclude_id      uuid,
  match_count     int default 5
)
returns table (
  id            uuid,
  reference_no  text,
  incident_type text,
  description   text,
  severity      int,
  similarity    float
)
language sql
security definer
as $$
  select
    i.id,
    i.reference_no,
    i.incident_type,
    i.description,
    i.severity,
    1 - (i.embedding <=> query_embedding) as similarity
  from incidents i
  where i.id != exclude_id
    and i.embedding is not null
  order by i.embedding <=> query_embedding
  limit match_count;
$$;

-- Register Gemini key slot, remove Voyage slot
insert into app_settings (key, value) values ('GOOGLE_AI_API_KEY', '') on conflict (key) do nothing;
delete from app_settings where key = 'VOYAGE_API_KEY';