blindtest/schema.sql

-- Chosun blind test schema (Supabase / Postgres)
-- Run this once in your Supabase project SQL editor.

create table if not exists articles (
    id bigserial primary key,
    source_text text not null,
    created_at timestamptz default now()
);

create table if not exists pipeline_runs (
    id bigserial primary key,
    article_id bigint references articles(id) on delete cascade,
    pipeline_key text not null,
    prompt_key text not null,
    model text not null,
    output text not null,
    processing_time_s real,
    run_at timestamptz default now()
);

create index if not exists pipeline_runs_article_idx on pipeline_runs (article_id);

create table if not exists tasks (
    id bigserial primary key,
    article_id bigint references articles(id) on delete cascade,
    run_a_id bigint references pipeline_runs(id),
    run_b_id bigint references pipeline_runs(id),
    created_at timestamptz default now()
);

create table if not exists votes (
    id bigserial primary key,
    task_id bigint references tasks(id) on delete cascade,
    choice text not null check (choice in ('A','B','tie','both_bad')),
    comment text,
    voted_at timestamptz default now()
);

create index if not exists votes_task_idx on votes (task_id);

-- Single-output rating table (current UI: Good / Not Bad / Critical).
create table if not exists ratings (
    id bigserial primary key,
    pipeline_run_id bigint references pipeline_runs(id) on delete cascade,
    rating text not null check (rating in ('good','not_bad','critical')),
    comment text,
    rated_at timestamptz default now()
);

create index if not exists ratings_run_idx on ratings (pipeline_run_id);

-- Allow anon key (used by the Gradio app) to insert/select.
-- RLS is enabled by default on public tables; without policies, anon writes silently fail.
alter table articles       enable row level security;
alter table pipeline_runs  enable row level security;
alter table tasks          enable row level security;
alter table votes          enable row level security;
alter table ratings        enable row level security;

create policy "anon insert articles"      on articles      for insert to anon with check (true);
create policy "anon select articles"      on articles      for select to anon using (true);
create policy "anon insert pipeline_runs" on pipeline_runs for insert to anon with check (true);
create policy "anon select pipeline_runs" on pipeline_runs for select to anon using (true);
create policy "anon insert tasks"         on tasks         for insert to anon with check (true);
create policy "anon select tasks"         on tasks         for select to anon using (true);
create policy "anon insert votes"         on votes         for insert to anon with check (true);
create policy "anon select votes"         on votes         for select to anon using (true);
create policy "anon insert ratings"       on ratings       for insert to anon with check (true);
create policy "anon select ratings"       on ratings       for select to anon using (true);

-- Aggregation view: winner is identified by pipeline_key (un-blinded).
create or replace view vote_summary as
select
    pr_chosen.pipeline_key as winner_pipeline,
    count(*) as wins
from votes v
join tasks t on t.id = v.task_id
join pipeline_runs pr_chosen on pr_chosen.id = case
    when v.choice = 'A' then t.run_a_id
    when v.choice = 'B' then t.run_b_id
    else null
end
where v.choice in ('A','B')
group by pr_chosen.pipeline_key;