-- V1-2.3 M6 `sheet_verification_jobs`: the worker RPCs of the server verdict. -- docs/development/V1-2_ANATOMICAL_SHEETS.md §6.4 (job, RPCs, processing), -- §6.5 (failures), §12.3 (M6). Service role only: no page reaches them. -- -- The round row (M5 `anatomical_sheet_reviews`) is the job, with the avatar -- lease pattern of 20261013100000_files_foundation.sql (lease token, -- `locked_at`, a fixed lease, attempts below a limit), plus a progressive -- delay for technical retries only (owner decision 2026-11-01): a failure -- reported by the worker sets `not_before`, the claim skips the round until -- then, so no worker sleeps and any worker honours it: -- * claim_sheet_verifications: rounds `verifying` with no live lease; -- * get_sheet_verification_input: the frozen document, the base, the rows of -- the drawings the document references; -- * complete_sheet_verification: the verdict, checked for its binding and -- its structure; `stale` or `malformed` write nothing; -- * fail_sheet_verification: a technical failure, from a closed list; -- transient codes retry after a delay up to the attempt limit, the -- definitive codes (`sheet_worker_final_codes`) close the round at once. -- The verdict itself is the core's (`compute_verdict`, crates/core); SQL -- checks only that it answers this round and covers this document. -- =========================================================================== -- 1. Constants -- =========================================================================== -- The earliest time a technical retry may be claimed (null = now). Hidden: -- the M5 grant to `authenticated` is column-level and does not list it. alter table public.anatomical_sheet_reviews add column not_before timestamptz; comment on column public.anatomical_sheet_reviews.not_before is 'Earliest time a technical retry may be claimed; null = now. Set by fail_sheet_verification, cleared by the claim. Service role only.'; -- Attempts before a round closes `verification_failed` / `unavailable` -- (§6.5 « five, proposal, tuned in V1-2.3 »). create or replace function private.sheet_verification_attempt_limit() returns integer language sql immutable set search_path = '' as $$ select 5 $$; -- Wait before the next claim after a technical failure at attempt n: 2 s, 5 s, -- 10 s, then 20 s (attempt 1 itself is claimed at once). Validation and -- integrity refusals are verdicts, never retried. create or replace function private.sheet_verification_retry_delay(attempts integer) returns interval language sql immutable set search_path = '' as $$ select case when attempts <= 1 then interval '2 seconds' when attempts = 2 then interval '5 seconds' when attempts = 3 then interval '10 seconds' else interval '20 seconds' end $$; -- A lease older than this is dead: the worker crashed or lost the database. -- The child's wall-clock deadline is far below it (crates/worker limits). create or replace function private.sheet_verification_lease() returns interval language sql immutable set search_path = '' as $$ select interval '10 minutes' $$; -- The largest verdict text accepted (the at-caps fixture measures far below). create or replace function private.sheet_verification_max_verdict_bytes() returns integer language sql immutable set search_path = '' as $$ select 4194304 $$; -- The core's failure codes (crates/core/src/domain/sheet/verdict.rs `codes`). create or replace function private.sheet_verdict_codes() returns text[] language sql immutable set search_path = '' as $$ select array[ 'verification.integrity.document', 'verification.integrity.base', 'verification.validation.rules', 'verification.validation.no_geometry', 'verification.validation.caps', 'verification.integrity.drawing_missing', 'verification.integrity.size_mismatch', 'verification.integrity.object_conflict', 'verification.integrity.svg_rejected', 'verification.integrity.svg_removal', 'verification.integrity.svg_not_canonical', 'verification.validation.out_of_frame', 'verification.validation.marker_too_small', 'verification.validation.marker_interior_too_small', 'verification.integrity.fingerprint' ] $$; -- The worker's technical failure codes (§6.5 transient, timeout, resource, -- interruption rows; crates/worker `verify_job`). The worker maps the cause -- to a code; the retry policy of each code is `sheet_worker_final_codes`. create or replace function private.sheet_worker_codes() returns text[] language sql immutable set search_path = '' as $$ select array[ 'verification.worker.unavailable', 'verification.worker.timeout', 'verification.worker.resource_limit', 'verification.worker.lease_expired', 'verification.worker.verdict_refused' ] $$; -- The codes whose cause is fixed by the frozen input or the contract caps -- (resource_limit) or by a verdict the SQL refuses (verdict_refused): a retry -- would give the same answer, so the round closes at the first occurrence. -- The others (timeout, unavailable, lease_expired) are transient. create or replace function private.sheet_worker_final_codes() returns text[] language sql immutable set search_path = '' as $$ select array['verification.worker.resource_limit', 'verification.worker.verdict_refused'] $$; -- The service role only (the worker's key); anything else is refused. create or replace function private.require_service_role() returns void language plpgsql stable set search_path = '' as $$ begin if auth.role() is distinct from 'service_role' then raise exception 'rights.capability' using errcode = '42501'; end if; end; $$; -- =========================================================================== -- 2. Group keys a verdict must carry (§6.3, §6.4) -- =========================================================================== -- `general`, `view:` per view, `zone:` per finding zone: the core's -- `keys::group_keys` (crates/core/src/domain/sheet/review.rs). create or replace function private.sheet_document_group_keys(p_doc jsonb) returns text[] language sql immutable set search_path = '' as $$ select array['general'] || coalesce((select array_agg(distinct 'view:' || (v ->> 'id')) from jsonb_array_elements(case jsonb_typeof(p_doc -> 'views') when 'array' then p_doc -> 'views' else '[]'::jsonb end) v where jsonb_typeof(v -> 'id') = 'string'), '{}') || coalesce((select array_agg(distinct 'zone:' || (z ->> 'id')) from jsonb_array_elements(case jsonb_typeof(p_doc -> 'finding_zones') when 'array' then p_doc -> 'finding_zones' else '[]'::jsonb end) z where jsonb_typeof(z -> 'id') = 'string'), '{}') $$; -- The base of a round's version: the published source it derives from (§6.2). create or replace function private.sheet_review_base(p_version_id uuid) returns jsonb language sql stable security definer set search_path = '' as $$ select s.content from public.anatomical_sheet_versions v join public.anatomical_sheet_versions s on s.id = v.source_version_id where v.id = p_version_id $$; -- A passed verdict's structure (§6.4): its groups are exactly the document's -- keys and the base's removed ones, each with a digest; its drawings exactly -- the document's asset ids with the rows' sha256, each `ok`; a fingerprint. create or replace function private.passed_verdict_fits(p_verdict jsonb, p_sheet_id uuid, p_doc jsonb, p_base jsonb) returns boolean language plpgsql stable security definer set search_path = '' as $$ declare v_expected text[]; v_keys text[]; v_drawings jsonb; begin if p_verdict -> 'failure_code' is distinct from 'null'::jsonb or p_verdict -> 'failures' is distinct from '[]'::jsonb or coalesce(p_verdict ->> 'fingerprint', '') !~ '^[0-9a-f]{64}$' or jsonb_typeof(p_verdict -> 'groups') is distinct from 'array' or jsonb_typeof(p_verdict -> 'drawings') is distinct from 'array' then return false; end if; if exists ( select 1 from jsonb_array_elements(p_verdict -> 'groups') g where jsonb_typeof(g) <> 'object' or jsonb_typeof(g -> 'key') is distinct from 'string' or coalesce(g ->> 'digest', '') !~ '^[0-9a-f]{64}$' or jsonb_typeof(g -> 'coverable') is distinct from 'boolean' ) then return false; end if; select array_agg(distinct k order by k) into v_expected from unnest(private.sheet_document_group_keys(p_doc) || coalesce(private.sheet_document_group_keys(p_base), '{}')) k; select array_agg(g ->> 'key' order by g ->> 'key') into v_keys from jsonb_array_elements(p_verdict -> 'groups') g; -- Same set, no duplicate key. if v_keys is distinct from v_expected then return false; end if; v_drawings := p_verdict -> 'drawings'; if exists ( select 1 from jsonb_array_elements(v_drawings) d where jsonb_typeof(d) <> 'object' or d ->> 'result' is distinct from 'ok' or jsonb_typeof(d -> 'asset_id') is distinct from 'string' or jsonb_typeof(d -> 'sha256') is distinct from 'string' ) then return false; end if; return ( select coalesce(array_agg(lower(d ->> 'asset_id') || '/' || (d ->> 'sha256') order by 1), '{}') from jsonb_array_elements(v_drawings) d ) = ( select coalesce(array_agg(a.id::text || '/' || a.sha256 order by 1), '{}') from public.anatomical_sheet_assets a where a.sheet_id = p_sheet_id and a.id::text = any (private.sheet_document_asset_ids(p_doc)) ) and jsonb_array_length(v_drawings) = cardinality(private.sheet_document_asset_ids(p_doc)); end; $$; -- =========================================================================== -- 3. Worker RPCs -- =========================================================================== -- Claim up to `max_jobs` rounds (1 to 10). A dead lease counts as an attempt -- already spent (§6.5 interruption); at the limit the round closes -- `verification_failed` / `unavailable` with `lease_expired`. create or replace function public.claim_sheet_verifications(max_jobs integer) returns table ( review_id uuid, version_id uuid, sheet_id uuid, revision integer, lease_token uuid, attempts integer ) language plpgsql volatile security definer set search_path = '' as $$ #variable_conflict use_variable declare v_limit integer := least(greatest(coalesce(max_jobs, 1), 1), 10); v_closed record; begin perform private.require_service_role(); for v_closed in update public.anatomical_sheet_reviews r set status = 'verification_failed', failure_outcome = 'unavailable', failure_code = 'verification.worker.lease_expired', lease_token = null, locked_at = null where r.status = 'verifying' and r.locked_at is not null and r.locked_at < now() - private.sheet_verification_lease() and r.attempts >= private.sheet_verification_attempt_limit() returning r.id, r.attempts loop perform private.log_event(null, 'anatomical_sheet_review.verification_failed', 'anatomical_sheet_review', v_closed.id, null, null, jsonb_build_object('outcome', 'unavailable', 'code', 'verification.worker.lease_expired', 'attempts', v_closed.attempts)); end loop; return query with picked as ( select r.id from public.anatomical_sheet_reviews r where r.status = 'verifying' and (r.locked_at is null or r.locked_at < now() - private.sheet_verification_lease()) and r.attempts < private.sheet_verification_attempt_limit() and (r.not_before is null or r.not_before <= now()) order by r.submitted_at limit v_limit for update skip locked ), leased as ( update public.anatomical_sheet_reviews r set lease_token = gen_random_uuid(), locked_at = now(), attempts = r.attempts + 1, not_before = null, -- A dead lease left no code: record the interruption it was. failure_code = case when r.locked_at is not null then 'verification.worker.lease_expired' else r.failure_code end from picked where r.id = picked.id returning r.id, r.version_id, r.revision, r.lease_token, r.attempts ) select l.id, l.version_id, v.sheet_id, l.revision, l.lease_token, l.attempts from leased l join public.anatomical_sheet_versions v on v.id = l.version_id; end; $$; -- The input of a leased round: the document at the round's revision (frozen -- while the round is `verifying`), the base, the drawing rows the document -- references among those registered for the sheet. Null for a stale lease. create or replace function public.get_sheet_verification_input(review_id uuid, lease_token uuid) returns jsonb language plpgsql stable security definer set search_path = '' as $$ #variable_conflict use_variable declare v_review public.anatomical_sheet_reviews%rowtype; v_version public.anatomical_sheet_versions%rowtype; begin perform private.require_service_role(); select r.* into v_review from public.anatomical_sheet_reviews r where r.id = review_id; if v_review.id is null or v_review.status <> 'verifying' or lease_token is null or v_review.lease_token is distinct from lease_token then return null; end if; select v.* into v_version from public.anatomical_sheet_versions v where v.id = v_review.version_id; -- The draft is frozen while its round is open; a revision past the round's -- is a stale round (never reached: the freeze trigger refuses the save). if v_version.revision <> v_review.revision then return null; end if; return jsonb_build_object( 'review_id', v_review.id, 'version_id', v_version.id, 'sheet_id', v_version.sheet_id, 'revision', v_review.revision, 'document', v_version.content::text, 'base_document', private.sheet_review_base(v_version.id)::text, 'drawings', coalesce(( select jsonb_agg(jsonb_build_object( 'asset_id', a.id, 'sha256', a.sha256, 'byte_size', a.byte_size, 'bucket', 'anatomical-sheet-assets', 'object_key', a.sha256 || '.svg', 'uploaded', a.uploaded_at is not null) order by a.id) from public.anatomical_sheet_assets a where a.sheet_id = v_version.sheet_id and a.id::text = any (private.sheet_document_asset_ids(v_version.content)) ), '[]'::jsonb) ); end; $$; -- Record the verdict of a leased round. Answers `passed` or `failed` once -- written; `stale` (not verifying, another token, another round) or -- `malformed` (wrong shape, unknown code, groups or drawings that do not fit -- the document) write nothing. create or replace function public.complete_sheet_verification(review_id uuid, lease_token uuid, verdict jsonb) returns text language plpgsql volatile security definer set search_path = '' as $$ #variable_conflict use_variable declare v_review public.anatomical_sheet_reviews%rowtype; v_version public.anatomical_sheet_versions%rowtype; v_outcome text; v_code text; begin perform private.require_service_role(); select r.* into v_review from public.anatomical_sheet_reviews r where r.id = review_id for update; if v_review.id is null or v_review.status <> 'verifying' or lease_token is null or v_review.lease_token is distinct from lease_token then return 'stale'; end if; select v.* into v_version from public.anatomical_sheet_versions v where v.id = v_review.version_id; if jsonb_typeof(verdict) is distinct from 'object' or octet_length(verdict::text) > private.sheet_verification_max_verdict_bytes() or verdict -> 'format' is distinct from '1'::jsonb or jsonb_typeof(verdict -> 'review_id') is distinct from 'string' or jsonb_typeof(verdict -> 'version_id') is distinct from 'string' or jsonb_typeof(verdict -> 'sheet_id') is distinct from 'string' or jsonb_typeof(verdict -> 'revision') is distinct from 'number' or jsonb_typeof(verdict -> 'verified_with') is distinct from 'string' or char_length(verdict ->> 'verified_with') > 100 then return 'malformed'; end if; -- A verdict naming another round or revision answers a round that no longer -- exists as such (§6.5 stale result). if lower(verdict ->> 'review_id') <> v_review.id::text or lower(verdict ->> 'version_id') <> v_version.id::text or lower(verdict ->> 'sheet_id') <> v_version.sheet_id::text or (verdict -> 'revision') <> to_jsonb(v_review.revision) then return 'stale'; end if; v_outcome := verdict ->> 'outcome'; if v_outcome = 'passed' then if not private.passed_verdict_fits(verdict, v_version.sheet_id, v_version.content, private.sheet_review_base(v_version.id)) then return 'malformed'; end if; update public.anatomical_sheet_reviews r set status = 'open', verdict = complete_sheet_verification.verdict, verified_at = now(), verified_with = verdict ->> 'verified_with', failure_outcome = null, failure_code = null, lease_token = null, locked_at = null where r.id = v_review.id; perform private.log_event(null, 'anatomical_sheet_review.verification_passed', 'anatomical_sheet_review', v_review.id, null, null, jsonb_build_object('sheet_id', v_version.sheet_id, 'version_id', v_version.id, 'revision', v_review.revision, 'verified_with', verdict ->> 'verified_with')); return 'passed'; end if; if v_outcome is distinct from 'failed' then return 'malformed'; end if; -- A failed verdict may carry no group or drawing (an unreadable document): -- its codes are the core's closed list, the first one recorded. v_code := verdict ->> 'failure_code'; if v_code is null or not (v_code = any (private.sheet_verdict_codes())) or jsonb_typeof(verdict -> 'failures') is distinct from 'array' or jsonb_array_length(verdict -> 'failures') = 0 or exists (select 1 from jsonb_array_elements(verdict -> 'failures') f where jsonb_typeof(f) <> 'string' or not ((f #>> '{}') = any (private.sheet_verdict_codes()))) or verdict -> 'failures' -> 0 <> to_jsonb(v_code) then return 'malformed'; end if; update public.anatomical_sheet_reviews r set status = 'verification_failed', verdict = complete_sheet_verification.verdict, verified_at = now(), verified_with = verdict ->> 'verified_with', failure_outcome = 'failed', failure_code = v_code, lease_token = null, locked_at = null where r.id = v_review.id; perform private.log_event(null, 'anatomical_sheet_review.verification_failed', 'anatomical_sheet_review', v_review.id, null, null, jsonb_build_object('sheet_id', v_version.sheet_id, 'version_id', v_version.id, 'revision', v_review.revision, 'outcome', 'failed', 'codes', verdict -> 'failures')); return 'failed'; end; $$; -- A technical failure of a leased round (§6.5): a transient code leaves the -- round `verifying` and claimable after its retry delay, its code recorded, -- until the attempt limit closes it `verification_failed` / `unavailable`; a -- definitive code closes it at once. Answers `retry`, `unavailable` or -- `stale` (nothing written). create or replace function public.fail_sheet_verification(review_id uuid, lease_token uuid, code text) returns text language plpgsql volatile security definer set search_path = '' as $$ #variable_conflict use_variable declare v_review public.anatomical_sheet_reviews%rowtype; v_final boolean; v_definitive boolean; begin perform private.require_service_role(); -- `lease_expired` is the claim's own record of a dead lease, never a worker report. if code is null or not (code = any (private.sheet_worker_codes())) or code = 'verification.worker.lease_expired' then raise exception 'invalid_input' using errcode = 'P0001'; end if; select r.* into v_review from public.anatomical_sheet_reviews r where r.id = review_id for update; if v_review.id is null or v_review.status <> 'verifying' or lease_token is null or v_review.lease_token is distinct from lease_token then return 'stale'; end if; v_definitive := code = any (private.sheet_worker_final_codes()); v_final := v_definitive or v_review.attempts >= private.sheet_verification_attempt_limit(); update public.anatomical_sheet_reviews r set failure_code = code, lease_token = null, locked_at = null, not_before = case when v_final then null else now() + private.sheet_verification_retry_delay(v_review.attempts) end, status = case when v_final then 'verification_failed'::public.sheet_round_status else r.status end, failure_outcome = case when v_final then 'unavailable'::public.sheet_verification_outcome else null end where r.id = v_review.id; perform private.log_event(null, case when v_final then 'anatomical_sheet_review.verification_failed' else 'anatomical_sheet_review.verification_attempt_failed' end, 'anatomical_sheet_review', v_review.id, null, null, jsonb_build_object('code', code, 'attempts', v_review.attempts, 'outcome', case when v_final then 'unavailable' end, 'closed_by', case when not v_final then null when v_definitive then 'definitive' else 'attempt_limit' end, 'retry_delay_seconds', case when not v_final then extract(epoch from private.sheet_verification_retry_delay(v_review.attempts))::integer end)); return case when v_final then 'unavailable' else 'retry' end; end; $$; -- =========================================================================== -- 4. Privileges: the service role only -- =========================================================================== revoke all on function private.sheet_verification_attempt_limit() from public; revoke all on function private.sheet_verification_retry_delay(integer) from public; revoke all on function private.sheet_verification_lease() from public; revoke all on function private.sheet_verification_max_verdict_bytes() from public; revoke all on function private.sheet_verdict_codes() from public; revoke all on function private.sheet_worker_codes() from public; revoke all on function private.sheet_worker_final_codes() from public; revoke all on function private.require_service_role() from public; revoke all on function private.sheet_document_group_keys(jsonb) from public; revoke all on function private.sheet_review_base(uuid) from public; revoke all on function private.passed_verdict_fits(jsonb, uuid, jsonb, jsonb) from public; revoke all on function public.claim_sheet_verifications(integer) from public, anon, authenticated; revoke all on function public.get_sheet_verification_input(uuid, uuid) from public, anon, authenticated; revoke all on function public.complete_sheet_verification(uuid, uuid, jsonb) from public, anon, authenticated; revoke all on function public.fail_sheet_verification(uuid, uuid, text) from public, anon, authenticated; grant execute on function public.claim_sheet_verifications(integer) to service_role; grant execute on function public.get_sheet_verification_input(uuid, uuid) to service_role; grant execute on function public.complete_sheet_verification(uuid, uuid, jsonb) to service_role; grant execute on function public.fail_sheet_verification(uuid, uuid, text) to service_role;