-- 007_review_reset.sql - the store review account starts clean after every sign-out (2026-11-03). -- -- The account the app stores' reviewers use (006: app_metadata.review_account = true) is shared: -- Apple's reviewer, Google's, and the owner testing. When it signs out, the app calls this, and the -- account's folders, recordings, transcripts, tasks, audio file rows and activity go, then a few -- made-up demo notes come back, so the next person opens the same small Library. The audio BYTES -- are removed by the app first, through the Storage API (as delete_my_account, 005). -- -- Only that account can run it, signed in itself: never another account (42501), never a connected -- app, never signed out, and it takes no argument. Nothing about anyone else is read or changed. -- -- Safe on a live database: adds one function and replaces schema_version(). Nothing moves. -- -- ROLLBACK: -- drop function public.reset_review_account(); (and schema_version() back to 6) create or replace function public.reset_review_account() returns int language plpgsql security definer set search_path = '' as $$ declare me uuid := (select auth.uid()); day date := (now() at time zone 'America/Toronto')::date; r1 uuid := gen_random_uuid(); r2 uuid := gen_random_uuid(); r3 uuid := gen_random_uuid(); begin if me is null then raise exception 'Sign in first.' using errcode = '42501'; end if; if coalesce((select auth.jwt()) ->> 'client_id', '') <> '' then raise exception 'A connected app can''t reset an account.' using errcode = '42501'; end if; if coalesce((select auth.jwt()) -> 'app_metadata' ->> 'review_account', '') <> 'true' then raise exception 'Only the review account resets.' using errcode = '42501'; end if; delete from public.activity where owner_id = me; delete from public.action_items where owner_id = me; delete from public.transcripts where owner_id = me; delete from public.audio_files where owner_id = me; delete from public.recordings where owner_id = me; delete from public.folders where owner_id = me; -- Made-up demo notes (no audio): a wrist thought, a short team meeting, a shopping list. insert into public.recordings (id, owner_id, title, source, recorded_at, local_date, duration_ms, language, template, status, summary, engine) values (r1, me, 'Idea: weekly digest email', 'watch', now() - interval '2 hours', day, 38000, 'en', 'voice_note', 'ready', '{"template": "voice_note", "engine": "gemini", "overview": "An idea for a weekly email that sums up what customers asked for.", "blocks": [{"heading": "Note", "items": ["Send customers a short Friday email with the three most asked-for features", "Try it for a month, then look at the open rate"]}]}', 'gemini'), (r2, me, 'Team check-in: launch week', 'device', now() - interval '1 day', day - 1, 412000, 'en', 'meeting', 'ready', '{"template": "meeting", "engine": "gemini", "overview": "Sam and Alex went over launch week: the store listing is ready, support email needs an auto-reply, and the demo video moves to Thursday.", "blocks": [{"heading": "Decisions", "items": ["Launch stays on Monday", "Demo video moves to Thursday"]}, {"heading": "Open questions", "items": ["Who answers support email over the weekend?"]}]}', 'gemini'), (r3, me, 'Groceries for the weekend', 'device', now() - interval '3 days', day - 3, 21000, 'en', 'voice_note', 'ready', '{"template": "voice_note", "engine": "gemini", "overview": "Shopping list for Saturday.", "blocks": [{"heading": "Note", "items": ["Milk, eggs, bread", "Coffee beans", "Something for Sunday dinner"]}]}', 'gemini'); insert into public.transcripts (id, owner_id, recording_id, language, origin, segments) values (gen_random_uuid(), me, r1, 'en', 'gemini', '[{"speaker": null, "start_ms": 0, "end_ms": 38000, "text": "Idea for later: a short Friday email to customers with the three features they asked for most. Try it for a month and check the open rate."}]'), (gen_random_uuid(), me, r2, 'en', 'gemini', '[{"speaker": "Speaker 1", "start_ms": 0, "end_ms": 9000, "text": "Okay, launch week. The store listing is ready."}, {"speaker": "Speaker 2", "start_ms": 9000, "end_ms": 18000, "text": "Good. We still need an auto-reply on the support email."}, {"speaker": "Speaker 1", "start_ms": 18000, "end_ms": 27000, "text": "Let''s keep launch on Monday and move the demo video to Thursday."}, {"speaker": "Speaker 2", "start_ms": 27000, "end_ms": 36000, "text": "Agreed. Who answers support over the weekend?"}]'), (gen_random_uuid(), me, r3, 'en', 'gemini', '[{"speaker": null, "start_ms": 0, "end_ms": 21000, "text": "For Saturday: milk, eggs, bread, coffee beans, and something for Sunday dinner."}]'); insert into public.action_items (id, owner_id, recording_id, label, type, status, due_date, context, origin) values (gen_random_uuid(), me, r1, 'Draft the first Friday digest email', 'draft_email', 'open', day + 4, 'Three most asked-for features', 'extracted'), (gen_random_uuid(), me, r2, 'Set up an auto-reply on the support email', 'create_reminder', 'open', day + 1, null, 'extracted'), (gen_random_uuid(), me, r2, 'Send Alex the new demo video date', 'send_message', 'open', null, 'Moved to Thursday', 'extracted'), (gen_random_uuid(), me, r3, 'Buy groceries', 'create_reminder', 'open', day + 2, 'Milk, eggs, bread, coffee beans', 'extracted'); return 3; end $$; revoke all on function public.reset_review_account() from public, anon; grant execute on function public.reset_review_account() to authenticated; create or replace function public.schema_version() returns int language sql immutable set search_path = '' as $$ select 7 $$; -- After applying: verify, don't assume. -- select public.schema_version(); -- expect 7 -- select proname, prosecdef from pg_proc where proname = 'reset_review_account'; -- expect true -- select grantee, privilege_type from information_schema.routine_privileges -- where routine_name = 'reset_review_account'; -- authenticated (and the owner), never anon