Files
faunosteo/supabase/migrations/20261010100000_sheet_document_schema_v2.sql

129 lines
4.6 KiB
PL/PgSQL

-- 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;
$$;