Files
faunosteo/.claude/audits/2026-10-28-v1-2-1-repro-old-schema.sql

159 lines
11 KiB
PL/PgSQL

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