-- V1-2.1 phase A: reproduce F1-F9, F12-F17, F19 on the OLD schema (develop, 12 migrations) -- of the throwaway sandbox. One transaction, rolled back: nothing persists. \set ON_ERROR_STOP 1 begin; set local client_min_messages = notice; insert into auth.users (id, email, email_confirmed_at) values ('a1000000-0000-0000-0000-000000000001', 'repro-owner@sandbox.local', now()), ('a1000000-0000-0000-0000-00000000000a', 'repro-alice@sandbox.local', now()), ('a1000000-0000-0000-0000-00000000000b', 'repro-bob@sandbox.local', now()), ('a1000000-0000-0000-0000-00000000000c', 'repro-unconfirmed@sandbox.local', null), ('a1000000-0000-0000-0000-00000000000d', 'repro-admin@sandbox.local', now()); select private.assign_platform_role(null, 'a1000000-0000-0000-0000-000000000001', 'owner'); select private.assign_platform_role(null, 'a1000000-0000-0000-0000-00000000000d', 'admin'); create function pg_temp.doc(p_species text, p_asset uuid) returns jsonb language sql as $$ select jsonb_build_object( 'schema_version', 2, 'species_id', p_species, 'name', jsonb_build_object('fr', 'Repro', 'en', 'Repro'), 'description', '{}'::jsonb, 'scope_disclaimer', '{}'::jsonb, 'attribution', jsonb_build_object('authors', '[]'::jsonb, 'license', null, 'notes', null), 'vertebral_config', jsonb_build_object('regions', '[]'::jsonb), 'references', '[]'::jsonb, 'categories', jsonb_build_array(jsonb_build_object('id', 'c', 'name', jsonb_build_object('fr', 'C', 'en', 'C'), 'color', null)), 'views', jsonb_build_array(jsonb_build_object('id', 'main', 'kind', 'main', 'name', jsonb_build_object('fr', 'V', 'en', 'V'), 'svg_asset_id', p_asset, 'view_box', null)), 'annotations', '[]'::jsonb, 'finding_zones', '[]'::jsonb); $$; grant execute on function pg_temp.doc(text, uuid) to authenticated, service_role; create table pg_temp.ids (key text primary key, id uuid); grant all on pg_temp.ids to authenticated, service_role; create function pg_temp.id(k text) returns uuid language sql as $$ select id from pg_temp.ids where key = k $$; grant execute on function pg_temp.id(text) to authenticated, service_role; create function pg_temp.as_user(p uuid) returns void language sql as $$ select set_config('request.jwt.claims', jsonb_build_object('sub', p, 'role', 'authenticated')::text, true); $$; grant execute on function pg_temp.as_user(uuid) to authenticated, service_role; -- F1, F5, F6, F15 ------------------------------------------------------------ set local role authenticated; select pg_temp.as_user('a1000000-0000-0000-0000-00000000000a'); insert into pg_temp.ids select 'f1', public.create_anatomical_sheet('other', 'Perso', 'perso-other', pg_temp.doc('other', null), '', 'public'); do $$ begin raise notice 'F1 observed: plain account created a personal PUBLIC sheet for species other: %', (select format('creator=%s official=%s visibility=%s species=%s', creator_user_id is not null, is_official, visibility, species_id) from public.anatomical_sheets where id = pg_temp.id('f1')); end $$; select pg_temp.as_user('a1000000-0000-0000-0000-00000000000c'); do $$ begin perform public.create_anatomical_sheet('equine', 'Unconfirmed', 'unconfirmed-sheet', pg_temp.doc('equine', null)); raise notice 'F5 observed: an account with an UNCONFIRMED email created a sheet'; exception when others then raise notice 'F5 not reproduced: %', sqlerrm; end $$; select pg_temp.as_user('a1000000-0000-0000-0000-00000000000a'); do $$ begin perform public.set_sheet_visibility(pg_temp.id('f1'), 'private'); perform public.set_sheet_visibility(pg_temp.id('f1'), 'public'); raise notice 'F6 observed: set_sheet_visibility changes visibility with no gate'; exception when others then raise notice 'F6 not reproduced: %', sqlerrm; end $$; select pg_temp.as_user('a1000000-0000-0000-0000-00000000000b'); do $$ begin raise notice 'F15 observed: another plain account reads the public personal sheet: % row(s)', (select count(*) from public.anatomical_sheets where id = pg_temp.id('f1')); end $$; -- F2, F4, F8, F9, F13: alice publishes v1 with an UNCONFIRMED drawing, archives, publishes v2 select pg_temp.as_user('a1000000-0000-0000-0000-00000000000a'); insert into pg_temp.ids select 's', public.create_anatomical_sheet('equine', 'Repro', 'repro-sheet', pg_temp.doc('equine', null), '', 'public'); insert into pg_temp.ids select 'asset', public.register_sheet_asset(pg_temp.id('s'), repeat('e', 64), 100, 'r.svg'); insert into pg_temp.ids select 'v1', id from public.anatomical_sheet_versions where sheet_id = pg_temp.id('s'); update public.anatomical_sheet_versions set content = pg_temp.doc('equine', pg_temp.id('asset')), change_note = 'direct' where id = pg_temp.id('v1'); do $$ begin raise notice 'F9 observed: editor rewrote draft content by direct UPDATE (no RPC): change_note=%', (select change_note from public.anatomical_sheet_versions where id = pg_temp.id('v1')); end $$; update public.anatomical_sheets set name = 'Renamed directly' where id = pg_temp.id('s'); do $$ begin raise notice 'F9 observed: sheet renamed by direct UPDATE: %', (select name from public.anatomical_sheets where id = pg_temp.id('s')); end $$; select public.publish_sheet_version(pg_temp.id('v1')); do $$ begin raise notice 'F4 observed: version published with a drawing never confirmed: uploaded_at null = %', (select uploaded_at is null from public.anatomical_sheet_assets where id = pg_temp.id('asset')); end $$; insert into pg_temp.ids select 'v2', public.create_sheet_draft(pg_temp.id('s'), null, 'v2'); select public.archive_anatomical_sheet(pg_temp.id('s')); do $$ begin perform public.publish_sheet_version(pg_temp.id('v2')); raise notice 'F2 observed: the draft of an ARCHIVED sheet was published'; exception when others then raise notice 'F2 not reproduced: %', sqlerrm; end $$; do $$ begin perform public.create_sheet_draft(pg_temp.id('s'), null, 'v3 on archived'); raise notice 'F2 observed: create_sheet_draft opened a draft on an ARCHIVED sheet'; exception when others then raise notice 'F2 (draft) not reproduced: %', sqlerrm; end $$; select pg_temp.as_user('a1000000-0000-0000-0000-00000000000b'); do $$ begin raise notice 'F8 observed: after archiving, another account reads % published version(s) of the sheet', (select count(*) from public.anatomical_sheet_versions where sheet_id = pg_temp.id('s') and status <> 'draft'); end $$; select pg_temp.as_user('a1000000-0000-0000-0000-00000000000a'); select public.restore_anatomical_sheet(pg_temp.id('s')); select public.deprecate_sheet_version(pg_temp.id('v1'), 'old'); do $$ begin perform public.delete_sheet_version(pg_temp.id('v1')); raise notice 'F13 observed: a DEPRECATED (once published) version was deleted physically: rows left %', (select count(*) from public.anatomical_sheet_versions where id = pg_temp.id('v1')); exception when others then raise notice 'F13 not reproduced: %', sqlerrm; end $$; -- F3: bob forks alice's public sheet; copied asset rows lack uploaded_at select pg_temp.as_user('a1000000-0000-0000-0000-00000000000b'); insert into pg_temp.ids select 'fork', public.fork_anatomical_sheet(pg_temp.id('v2'), 'Copie', 'copie-repro'); reset role; update public.anatomical_sheet_assets set uploaded_at = now() where id = pg_temp.id('asset'); do $$ begin raise notice 'F3 observed: source drawing confirmed, copied rows confirmed: %', (select string_agg((uploaded_at is not null)::text, ',') from public.anatomical_sheet_assets where sheet_id = pg_temp.id('fork')); end $$; -- F14: a platform admin, no capability, no MFA claim, creates and publishes an official sheet set local role authenticated; select pg_temp.as_user('a1000000-0000-0000-0000-00000000000d'); insert into pg_temp.ids select 'off', public.create_anatomical_sheet('equine', 'Off', 'off-repro', pg_temp.doc('equine', null), '', 'public', true); insert into pg_temp.ids select 'off-asset', public.register_sheet_asset(pg_temp.id('off'), repeat('f', 64), 100, 'o.svg'); update public.anatomical_sheet_versions set content = pg_temp.doc('equine', pg_temp.id('off-asset')) where sheet_id = pg_temp.id('off'); do $$ begin perform public.publish_sheet_version((select id from public.anatomical_sheet_versions where sheet_id = pg_temp.id('off'))); raise notice 'F14 observed: a platform admin at aal1 (claims carry no aal) published an official sheet'; exception when others then raise notice 'F14 not reproduced: %', sqlerrm; end $$; -- F19: an owner session with NO aal claim grants owner to another account that has no factor select pg_temp.as_user('a1000000-0000-0000-0000-000000000001'); do $$ begin perform public.grant_platform_role('a1000000-0000-0000-0000-00000000000b', 'owner'); raise notice 'F19 observed: grant_platform_role(owner) accepted at aal1 (claims: %)', current_setting('request.jwt.claims'); exception when others then raise notice 'F19 not reproduced: %', sqlerrm; end $$; -- F7, F12, F17: the service_role session reset role; do $$ begin raise notice 'F19 observed: the new owner holds % verified factor(s); active owner rows now %', (select count(*) from auth.mfa_factors where user_id = 'a1000000-0000-0000-0000-00000000000b' and status = 'verified'), (select count(*) from public.profile_roles where user_id = 'a1000000-0000-0000-0000-00000000000b' and role = 'owner' and revoked_at is null); end $$; insert into pg_temp.ids select 'pending', public.register_sheet_asset(pg_temp.id('off'), repeat('9', 64), 100, 'p.svg') from (select pg_temp.as_user('a1000000-0000-0000-0000-00000000000d')) x; select set_config('request.jwt.claims', '{"role":"service_role"}', true); set local role service_role; do $$ begin perform public.confirm_sheet_asset_upload(pg_temp.id('pending')); raise notice 'F7 observed: upload confirmed with no object in Storage: objects=% uploaded_at set=%', (select count(*) from storage.objects where bucket_id = 'anatomical-sheet-assets' and name = repeat('9', 64) || '.svg'), (select uploaded_at is not null from public.anatomical_sheet_assets where id = pg_temp.id('pending')); exception when others then raise notice 'F7 not reproduced: %', sqlerrm; end $$; do $$ begin update public.anatomical_sheet_versions set status = 'published', published_at = now() where sheet_id = pg_temp.id('f1') and status = 'draft'; raise notice 'F12 observed: service_role published a draft by direct UPDATE: %', (select status from public.anatomical_sheet_versions where sheet_id = pg_temp.id('f1')); exception when others then raise notice 'F12 not reproduced: %', sqlerrm; end $$; do $$ begin insert into public.profile_roles (user_id, role) values ('a1000000-0000-0000-0000-00000000000c', 'owner'); raise notice 'F17 observed: service_role inserted an owner row directly into profile_roles'; exception when others then raise notice 'F17 not reproduced: %', sqlerrm; end $$; -- F16: privileged session rewrites a drawing row's sha256 under a published version reset role; do $$ begin update public.anatomical_sheet_assets set sha256 = repeat('0', 64) where id = pg_temp.id('off-asset'); raise notice 'F16 observed: sha256 of an asset used by a published version changed: %', (select left(sha256, 8) from public.anatomical_sheet_assets where id = pg_temp.id('off-asset')); exception when others then raise notice 'F16 not reproduced: %', sqlerrm; end $$; rollback;