43 lines
1.1 KiB
PL/PgSQL
43 lines
1.1 KiB
PL/PgSQL
-- Enable pgvector
|
|
create extension if not exists vector;
|
|
|
|
-- Add embedding column to incidents (nullable — populated async after creation)
|
|
alter table incidents
|
|
add column if not exists embedding vector(1024);
|
|
|
|
-- IVFFlat index for cosine similarity (10 lists sufficient for <10k rows)
|
|
create index if not exists incidents_embedding_idx
|
|
on incidents using ivfflat (embedding vector_cosine_ops)
|
|
with (lists = 10);
|
|
|
|
-- Similarity search — returns top-N most similar incidents (cosine)
|
|
create or replace function match_incidents(
|
|
query_embedding vector(1024),
|
|
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;
|
|
$$;
|