-- Canonical document schema_version 2 (ANATOMICAL_SHEET_MODEL.md ยง2, Step 4c): -- shared `references` registry cited by `reference_ids`, `review_status` -- on findings and annotations, optional `count_range` on vertebral regions. -- No version 1 document was ever published (the only row is the official -- equine draft, regenerated by its seed), so version 1 is no longer stored. alter table public.anatomical_sheet_versions drop constraint anatomical_sheet_versions_schema_known; alter table public.anatomical_sheet_versions add constraint anatomical_sheet_versions_schema_known check (document_schema_version = 2); -- Shape: the references registry is one more id-bearing array. create or replace function private.sheet_document_has_shape(p_doc jsonb) returns boolean language sql immutable set search_path = '' as $$ select jsonb_typeof(p_doc) = 'object' and jsonb_typeof(coalesce(p_doc -> 'views', '[]'::jsonb)) = 'array' and jsonb_typeof(coalesce(p_doc -> 'annotations', '[]'::jsonb)) = 'array' and jsonb_typeof(coalesce(p_doc -> 'categories', '[]'::jsonb)) = 'array' and jsonb_typeof(coalesce(p_doc -> 'finding_zones', '[]'::jsonb)) = 'array' and jsonb_typeof(coalesce(p_doc -> 'references', '[]'::jsonb)) = 'array' and jsonb_typeof(coalesce(p_doc -> 'vertebral_config' -> 'regions', '[]'::jsonb)) = 'array'; $$; -- Uniqueness of the id-bearing collections, references included. create or replace function private.sheet_document_duplicate_issues(p_doc jsonb) returns text[] language plpgsql immutable set search_path = '' as $$ declare v_issues text[] := '{}'; r record; begin for r in select * from (values ('views', 'id', 'view.id.duplicate'), ('annotations', 'id', 'annotation.id.duplicate'), ('categories', 'id', 'category.id.duplicate'), ('finding_zones', 'id', 'finding_zone.id.duplicate'), ('references', 'id', 'reference.id.duplicate') ) as t(collection, key, code) loop if exists ( select 1 from jsonb_array_elements(coalesce(p_doc -> r.collection, '[]'::jsonb)) e group by e ->> r.key having count(*) > 1 ) then v_issues := private.append_issue(v_issues, r.code); end if; end loop; if exists ( select 1 from jsonb_array_elements(coalesce(p_doc -> 'vertebral_config' -> 'regions', '[]'::jsonb)) g group by g ->> 'code' having count(*) > 1 ) then v_issues := private.append_issue(v_issues, 'region.code.duplicate'); end if; return v_issues; end; $$; -- Citations point at declared references (every depth of the finding tree, -- categories included); the usual vertebra count sits inside its range. create or replace function private.sheet_document_reference_issues(p_doc jsonb) returns text[] language plpgsql immutable set search_path = '' as $$ declare v_issues text[] := '{}'; begin if exists ( select 1 from jsonb_path_query(coalesce(p_doc -> 'finding_zones', '[]'::jsonb), '$[*].categories[*].**.reference_ids[*]') cited where not exists ( select 1 from jsonb_array_elements(coalesce(p_doc -> 'references', '[]'::jsonb)) ref where ref -> 'id' = cited ) ) then v_issues := private.append_issue(v_issues, 'finding.reference.missing'); end if; if exists ( select 1 from jsonb_array_elements(coalesce(p_doc -> 'vertebral_config' -> 'regions', '[]'::jsonb)) g where jsonb_typeof(g -> 'count_range') = 'object' and not ( (g -> 'count_range' ->> 'min')::numeric <= (g ->> 'count')::numeric and (g ->> 'count')::numeric <= (g -> 'count_range' ->> 'max')::numeric ) ) then v_issues := private.append_issue(v_issues, 'region.count_range.invalid'); end if; -- Zod refuses non-http(s) reference urls at parse; a document sent straight -- to the RPC must meet the same bar before anything renders it as a link. if exists ( select 1 from jsonb_array_elements(coalesce(p_doc -> 'references', '[]'::jsonb)) ref where jsonb_typeof(ref -> 'url') = 'string' and (ref ->> 'url') !~ '^https?://' ) then v_issues := private.append_issue(v_issues, 'reference.url.scheme'); end if; return v_issues; end; $$; revoke all on function private.sheet_document_reference_issues(jsonb) from public; create or replace function private.sheet_document_issues(p_doc jsonb) returns text[] language sql immutable set search_path = '' as $$ select case when not private.sheet_document_has_shape(p_doc) then array['document.shape'] else private.sheet_document_view_issues(p_doc) || private.sheet_document_duplicate_issues(p_doc) || private.sheet_document_annotation_issues(p_doc) || private.sheet_document_reference_issues(p_doc) end; $$;