supabase/rpc/set_review_completed.sql

-- Atomic review completion (Postgres RPC, applied OUTSIDE the table migrations).
--
-- Kept in supabase/rpc/ rather than supabase/migrations/ so the migrations
-- folder stays a single compact-schema snapshot (see
-- tests/test_extensible_schema.py). Apply with:
--     python scripts/apply_migration.py supabase/rpc/set_review_completed.sql
--
-- Replaces the previous two-step set_completed (upsert reviews, then PATCH
-- submissions.status) which was non-atomic: if the second step failed, a valid
-- review row existed but the submission stayed 'processing' (or was wrongly
-- marked 'failed' by the caller's except path). This function performs both
-- writes inside one transaction, so they either both land or neither does.
--
-- Called from backend/storage/supabase_store.py::set_completed via PostgREST RPC
-- (POST /rest/v1/rpc/set_review_completed).

-- Drop the prior signatures first: Postgres cannot rename/retype an input
-- parameter via CREATE OR REPLACE. Both historical signatures are dropped so
-- re-applying is clean (p_secret_key->p_access_key, and the p_scores removal).
drop function if exists public.set_review_completed(text, jsonb, jsonb, numeric, text);
drop function if exists public.set_review_completed(text, jsonb, numeric, text);

create or replace function public.set_review_completed(
    p_access_key    text,
    p_review        jsonb,
    p_overall_score numeric,
    p_summary       text
)
returns void
language plpgsql
as $$
declare
    -- Inherit the real column type so this works whether submissions.id is
    -- bigint or uuid.
    v_submission_id public.submissions.id%type;
begin
    select id
    into v_submission_id
    from public.submissions
    where access_key = p_access_key;

    if v_submission_id is null then
        raise exception 'submission not found for access_key %', p_access_key
            using errcode = 'no_data_found';
    end if;

    insert into public.reviews (
        submission_id, review_json, overall_score, summary, metadata, created_at
    )
    values (
        v_submission_id, p_review, p_overall_score, p_summary, '{}'::jsonb, now()
    )
    on conflict (submission_id) do update set
        review_json   = excluded.review_json,
        overall_score = excluded.overall_score,
        summary       = excluded.summary,
        created_at    = excluded.created_at;

    update public.submissions
    set status       = 'completed',
        completed_at = now(),
        error        = null
    where id = v_submission_id;
end;
$$;

-- The backend authenticates with the service_role key.
grant execute on function public.set_review_completed(text, jsonb, numeric, text)
    to service_role;

-- Ask PostgREST to refresh its schema cache so the new RPC is exposed immediately.
notify pgrst, 'reload schema';