schema.sql

-- Enable pgvector extension
create extension if not exists vector;

-- Stores every PR that was reviewed
create table reviews (
  id           uuid primary key default gen_random_uuid(),
  repo         text not null,
  pr_number    integer not null,
  pr_title     text,
  author       text,
  created_at   timestamptz default now()
);

-- Stores every issue found, with its embedding
create table issues (
  id           uuid primary key default gen_random_uuid(),
  review_id    uuid references reviews(id) on delete cascade,
  repo         text not null,
  pr_number    integer not null,
  file         text not null,
  line         integer,
  severity     text not null,
  type         text not null,
  message      text not null,
  suggestion   text,
  embedding    vector(1024),
  created_at   timestamptz default now()
);

-- Index for fast similarity search
create index on issues using ivfflat (embedding vector_cosine_ops)
  with (lists = 100);

-- Function: find similar past issues using cosine similarity
create or replace function find_similar_issues(
  query_embedding vector(1024),
  match_threshold float,
  match_count     int,
  filter_repo     text default null
)
returns table (
  id          uuid,
  repo        text,
  pr_number   integer,
  file        text,
  severity    text,
  type        text,
  message     text,
  suggestion  text,
  similarity  float
)
language sql stable
as $$
  select
    i.id,
    i.repo,
    i.pr_number,
    i.file,
    i.severity,
    i.type,
    i.message,
    i.suggestion,
    1 - (i.embedding <=> query_embedding) as similarity
  from issues i
  where
    (filter_repo is null or i.repo = filter_repo)
    and 1 - (i.embedding <=> query_embedding) > match_threshold
  order by i.embedding <=> query_embedding
  limit match_count;
$$;