File size: 3,483 Bytes
61d7017 c421c84 e92517a c421c84 e92517a c421c84 e92517a 61d7017 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 | -- 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;
|