Test Wordrow

begin; -- Wordrows is an isolated editorial publishing path. Existing reviewed project -- publication functions remain unchanged. Draft content is never publicly read. create table public.writing_drafts ( project_id uuid primary key references public.projects(id) on delete cascade, owner_id uuid not null references public.profiles(user_id) on delete restrict, content jsonb not null, revision integer not null default 1 check (revision > 0), updated_at timestamptz not null default now() ); alter table public.writing_drafts enable row level security; create policy "Authors read their writing drafts" on public.writing_drafts for select to authenticated using (owner_id = auth.uid() and public.is_active_member()); revoke all on public.writing_drafts from public, anon, authenticated; grant select on public.writing_drafts to authenticated;

create function private.writing_text(value jsonb, maximum integer) returns boolean language sql immutable set search_path = '' as $$ select coalesce(jsonb_typeof(value) = 'string' and length(value #>> '{}') <= maximum, false) $$; create function private.writing_url(value text, kind text default 'image') returns boolean language plpgsql immutable set search_path = '' as $$ begin if value = '' then return true; end if; if value is null or length(value) > 2048 or value !~ '^https://[a-zA-Z0-9.-]+(:443)?(/[^[:space:]\\]*)?$' then return false; end if; if kind = 'model' then return value ~ '^https://my\.spline\.design/[a-zA-Z0-9_-]+/?$' or value ~ '^https://(www\.)?sketchfab\.com/models/[a-f0-9]{32}/embed/?$'; elsif kind = 'video' then return value ~ '^https://(www\.)?(youtube\.com|youtube-nocookie\.com)/embed/[a-zA-Z0-9_-]{11}$' or value ~ '^https://(www\.)?youtube\.com/watch\?v=[a-zA-Z0-9_-]{11}(&[^[:space:]]*)?$' or value ~ '^https://youtu\.be/[a-zA-Z0-9_-]{11}$' or value ~ '^https://(www\.)?vimeo\.com/[0-9]+/?$' or value ~ '^https://player\.vimeo\.com/video/[0-9]+/?$'; end if; return true; end; $$; create function private.validate_writing(document jsonb, ready boolean) returns void language plpgsql set search_path = '' as $$ declare chapter jsonb; panel jsonb; begin if document is null or jsonb_typeof(document) <> 'object' or (document->'version') is distinct from '1'::jsonb or coalesce(document->>'kind','') not in ('article','book','magazine','lyrics') or not private.writing_text(document->'title',160) or not private.writing_text(document->'summary',4000) or jsonb_typeof(document->'chapters') is distinct from 'array' or jsonb_typeof(document->'panels') is distinct from 'array' then raise exception 'Invalid writing content' using errcode='22023'; end if; if (select count(*) from jsonb_object_keys(document)) <> 6 then raise exception 'Unexpected writing fields' using errcode='22023'; end if; if jsonb_array_length(document->'chapters') <> 4 or jsonb_array_length(document->'panels') <> 2 or (ready and document->>'title' !~ '[^[:space:]]') then raise exception 'Writing requires a title, four chapters and two panels' using errcode='22023'; end if; for chapter in select value from jsonb_array_elements(document->'chapters') loop if jsonb_typeof(chapter) is distinct from 'object' then raise exception 'Invalid chapter' using errcode='22023'; end if; if (select count(*) from jsonb_object_keys(chapter)) <> 4 or not private.writing_text(chapter->'title',160) or not private.writing_text(chapter->'body',50000) or not private.writing_text(chapter->'imageUrl',2048) or not private.writing_text(chapter->'imageAlt',500) or not private.writing_url(chapter->>'imageUrl') or (ready and (chapter->>'title' !~ '[^[:space:]]' or chapter->>'body' !~ '[^[:space:]]')) then raise exception 'Each chapter needs valid text and an optional HTTPS image' using errcode='22023'; end if; end loop; for panel in select value from jsonb_array_elements(document->'panels') loop if jsonb_typeof(panel) is distinct from 'object' then raise exception 'Invalid supporting panel' using errcode='22023'; end if; if (select count(*) from jsonb_object_keys(panel)) <> 4 or coalesce(panel->>'kind','') not in ('model','image','video','sources') or not private.writing_text(panel->'title',160) or not private.writing_text(panel->'body',10000) or not private.writing_text(panel->'url',2048) or not private.writing_url(panel->>'url',panel->>'kind') then raise exception 'Invalid supporting panel or unsupported embed URL' using errcode='22023'; end if; end loop; end; $$;

create function public.save_writing_draft(document jsonb, target uuid default null, expected_revision integer default 0, publish_now boolean default false) returns jsonb language plpgsql security definer set search_path = '' as $$ declare actor uuid; draft public.writing_drafts; writing_project_id uuid; release public.publications; next_revision integer; begin actor := private.require_member(); if publish_now is null or expected_revision is null then raise exception 'Invalid writing request' using errcode='22023'; end if; if publish_now and not public.has_member_role(array['admin'::public.member_role,'editor'::public.member_role]) then raise exception 'Editor membership required to publish writing' using errcode='42501'; end if; perform private.validate_writing(document,publish_now); if target is null then if expected_revision <> 0 then raise exception 'Stale writing revision' using errcode='40001'; end if; writing_project_id := public.create_project(coalesce(nullif(btrim(document->>'title'),''),'Untitled article'),document->>'summary'); insert into public.writing_drafts(project_id,owner_id,content) values(writing_project_id,actor,document) returning * into draft; next_revision := 1; else -- Always lock the project first, matching the established workflow lock order. perform 1 from public.projects where id=target for update; select * into draft from public.writing_drafts where writing_drafts.project_id=target for update; if not found or draft.owner_id <> actor then raise exception 'Writing author required' using errcode='42501'; end if; if draft.revision <> expected_revision then raise exception 'Stale writing revision' using errcode='40001'; end if; writing_project_id := target; next_revision := draft.revision+1; update public.writing_drafts set content=document,revision=next_revision,updated_at=now() where writing_drafts.project_id=target; end if; -- Draft metadata stays private; published readers use publications.snapshot only. update public.projects set title=coalesce(nullif(btrim(document->>'title'),''),'Untitled article'),summary=document->>'summary',updated_at=now() where id=writing_project_id; select * into release from public.publications where publications.project_id=writing_project_id; if publish_now then if release.id is not null and release.snapshot->>'division' is distinct from 'wordrows' then raise exception 'Reviewed publications cannot be replaced by writing' using errcode='42501'; end if; insert into public.publications(project_id,slug,title,excerpt,published_by,snapshot,is_public) values(writing_project_id,'writing-'||writing_project_id::text,btrim(document->>'title'),document->>'summary',actor, jsonb_build_object('division','wordrows','writing',document,'revision',next_revision),true) on conflict on constraint publications_project_id_key do update set title=excluded.title,excerpt=excluded.excerpt, snapshot=excluded.snapshot,is_public=true,updated_at=now() returning * into release; insert into public.publication_credits(publication_id,user_id,credit_label) values(release.id,actor,'author') on conflict do nothing; update public.projects set stage='ship',published_at=coalesce(published_at,now()) where id=writing_project_id; end if; return jsonb_build_object('projectId',writing_project_id,'revision',next_revision,'publicationSlug',release.slug); end; $$; create function public.unpublish_writing(target uuid, expected_revision integer) returns jsonb language plpgsql security definer set search_path = '' as $$ declare actor uuid; draft public.writing_drafts; release public.publications; begin actor := private.require_member(); if not public.has_member_role(array['admin'::public.member_role,'editor'::public.member_role]) then raise exception 'Editor membership required to unpublish writing' using errcode='42501'; end if; perform 1 from public.projects where id=target for update; select * into draft from public.writing_drafts where project_id=target for update; if not found or draft.owner_id <> actor then raise exception 'Writing author required' using errcode='42501'; end if; if expected_revision is null or draft.revision <> expected_revision then raise exception 'Stale writing revision' using errcode='40001'; end if; update public.publications set is_public=false where project_id=target and snapshot->>'division'='wordrows' returning * into release; update public.writing_drafts set revision=revision+1,updated_at=now() where project_id=target; update public.projects set stage='make',is_public=false where id=target; return jsonb_build_object('projectId',target,'revision',draft.revision+1,'publicationSlug',release.slug); end; $$; revoke execute on function private.writing_text(jsonb,integer),private.writing_url(text,text),private.validate_writing(jsonb,boolean) from public,anon,authenticated; revoke execute on function public.save_writing_draft(jsonb,uuid,integer,boolean),public.unpublish_writing(uuid,integer) from public,anon,authenticated; grant execute on function public.save_writing_draft(jsonb,uuid,integer,boolean),public.unpublish_writing(uuid,integer) to authenticated;

-- A writing project must never be released through a generic artifact snapshot. -- This closes cross-workflow entry points without relaxing review requirements. create function private.guard_writing_publication() returns trigger language plpgsql security definer set search_path = '' as $$ begin if exists (select 1 from public.writing_drafts where project_id=new.project_id) then if new.snapshot->>'division' is distinct from 'wordrows' then raise exception 'Use the writing publication action for Wordrows' using errcode='22023'; end if; perform private.validate_writing(new.snapshot->'writing',true); end if; return new; end; $$; create trigger writing_publication_guard before insert or update on public.publications for each row execute function private.guard_writing_publication(); revoke execute on function private.guard_writing_publication() from public,anon,authenticated;

insert into supabase_migrations.schema_migrations(version,name,statements) values ('202609120001','wordrows_writing',array[$migration$-- Wordrows is an isolated editorial publishing path. Existing reviewed project -- publication functions remain unchanged. Draft content is never publicly read. create table public.writing_drafts ( project_id uuid primary key references public.projects(id) on delete cascade, owner_id uuid not null references public.profiles(user_id) on delete restrict, content jsonb not null, revision integer not null default 1 check (revision > 0), updated_at timestamptz not null default now() ); alter table public.writing_drafts enable row level security; create policy "Authors read their writing drafts" on public.writing_drafts for select to authenticated using (owner_id = auth.uid() and public.is_active_member()); revoke all on public.writing_drafts from public, anon, authenticated; grant select on public.writing_drafts to authenticated;

create function private.writing_text(value jsonb, maximum integer) returns boolean language sql immutable set search_path = '' as $$ select coalesce(jsonb_typeof(value) = 'string' and length(value #>> '{}') <= maximum, false) $$; create function private.writing_url(value text, kind text default 'image') returns boolean language plpgsql immutable set search_path = '' as $$ begin if value = '' then return true; end if; if value is null or length(value) > 2048 or value !~ '^https://[a-zA-Z0-9.-]+(:443)?(/[^[:space:]\\]*)?$' then return false; end if; if kind = 'model' then return value ~ '^https://my\.spline\.design/[a-zA-Z0-9_-]+/?$' or value ~ '^https://(www\.)?sketchfab\.com/models/[a-f0-9]{32}/embed/?$'; elsif kind = 'video' then return value ~ '^https://(www\.)?(youtube\.com|youtube-nocookie\.com)/embed/[a-zA-Z0-9_-]{11}$' or value ~ '^https://(www\.)?youtube\.com/watch\?v=[a-zA-Z0-9_-]{11}(&[^[:space:]]*)?$' or value ~ '^https://youtu\.be/[a-zA-Z0-9_-]{11}$' or value ~ '^https://(www\.)?vimeo\.com/[0-9]+/?$' or value ~ '^https://player\.vimeo\.com/video/[0-9]+/?$'; end if; return true; end; $$; create function private.validate_writing(document jsonb, ready boolean) returns void language plpgsql set search_path = '' as $$ declare chapter jsonb; panel jsonb; begin if document is null or jsonb_typeof(document) <> 'object' or (document->'version') is distinct from '1'::jsonb or coalesce(document->>'kind','') not in ('article','book','magazine','lyrics') or not private.writing_text(document->'title',160) or not private.writing_text(document->'summary',4000) or jsonb_typeof(document->'chapters') is distinct from 'array' or jsonb_typeof(document->'panels') is distinct from 'array' then raise exception 'Invalid writing content' using errcode='22023'; end if; if (select count(*) from jsonb_object_keys(document)) <> 6 then raise exception 'Unexpected writing fields' using errcode='22023'; end if; if jsonb_array_length(document->'chapters') <> 4 or jsonb_array_length(document->'panels') <> 2 or (ready and document->>'title' !~ '[^[:space:]]') then raise exception 'Writing requires a title, four chapters and two panels' using errcode='22023'; end if; for chapter in select value from jsonb_array_elements(document->'chapters') loop if jsonb_typeof(chapter) is distinct from 'object' then raise exception 'Invalid chapter' using errcode='22023'; end if; if (select count(*) from jsonb_object_keys(chapter)) <> 4 or not private.writing_text(chapter->'title',160) or not private.writing_text(chapter->'body',50000) or not private.writing_text(chapter->'imageUrl',2048) or not private.writing_text(chapter->'imageAlt',500) or not private.writing_url(chapter->>'imageUrl') or (ready and (chapter->>'title' !~ '[^[:space:]]' or chapter->>'body' !~ '[^[:space:]]')) then raise exception 'Each chapter needs valid text and an optional HTTPS image' using errcode='22023'; end if; end loop; for panel in select value from jsonb_array_elements(document->'panels') loop if jsonb_typeof(panel) is distinct from 'object' then raise exception 'Invalid supporting panel' using errcode='22023'; end if; if (select count(*) from jsonb_object_keys(panel)) <> 4 or coalesce(panel->>'kind','') not in ('model','image','video','sources') or not private.writing_text(panel->'title',160) or not private.writing_text(panel->'body',10000) or not private.writing_text(panel->'url',2048) or not private.writing_url(panel->>'url',panel->>'kind') then raise exception 'Invalid supporting panel or unsupported embed URL' using errcode='22023'; end if; end loop; end; $$;

create function public.save_writing_draft(document jsonb, target uuid default null, expected_revision integer default 0, publish_now boolean default false) returns jsonb language plpgsql security definer set search_path = '' as $$ declare actor uuid; draft public.writing_drafts; writing_project_id uuid; release public.publications; next_revision integer; begin actor := private.require_member(); if publish_now is null or expected_revision is null then raise exception 'Invalid writing request' using errcode='22023'; end if; if publish_now and not public.has_member_role(array['admin'::public.member_role,'editor'::public.member_role]) then raise exception 'Editor membership required to publish writing' using errcode='42501'; end if; perform private.validate_writing(document,publish_now); if target is null then if expected_revision <> 0 then raise exception 'Stale writing revision' using errcode='40001'; end if; writing_project_id := public.create_project(coalesce(nullif(btrim(document->>'title'),''),'Untitled article'),document->>'summary'); insert into public.writing_drafts(project_id,owner_id,content) values(writing_project_id,actor,document) returning * into draft; next_revision := 1; else -- Always lock the project first, matching the established workflow lock order. perform 1 from public.projects where id=target for update; select * into draft from public.writing_drafts where writing_drafts.project_id=target for update; if not found or draft.owner_id <> actor then raise exception 'Writing author required' using errcode='42501'; end if; if draft.revision <> expected_revision then raise exception 'Stale writing revision' using errcode='40001'; end if; writing_project_id := target; next_revision := draft.revision+1; update public.writing_drafts set content=document,revision=next_revision,updated_at=now() where writing_drafts.project_id=target; end if; -- Draft metadata stays private; published readers use publications.snapshot only. update public.projects set title=coalesce(nullif(btrim(document->>'title'),''),'Untitled article'),summary=document->>'summary',updated_at=now() where id=writing_project_id; select * into release from public.publications where publications.project_id=writing_project_id; if publish_now then if release.id is not null and release.snapshot->>'division' is distinct from 'wordrows' then raise exception 'Reviewed publications cannot be replaced by writing' using errcode='42501'; end if; insert into public.publications(project_id,slug,title,excerpt,published_by,snapshot,is_public) values(writing_project_id,'writing-'||writing_project_id::text,btrim(document->>'title'),document->>'summary',actor, jsonb_build_object('division','wordrows','writing',document,'revision',next_revision),true) on conflict on constraint publications_project_id_key do update set title=excluded.title,excerpt=excluded.excerpt, snapshot=excluded.snapshot,is_public=true,updated_at=now() returning * into release; insert into public.publication_credits(publication_id,user_id,credit_label) values(release.id,actor,'author') on conflict do nothing; update public.projects set stage='ship',published_at=coalesce(published_at,now()) where id=writing_project_id; end if; return jsonb_build_object('projectId',writing_project_id,'revision',next_revision,'publicationSlug',release.slug); end; $$; create function public.unpublish_writing(target uuid, expected_revision integer) returns jsonb language plpgsql security definer set search_path = '' as $$ declare actor uuid; draft public.writing_drafts; release public.publications; begin actor := private.require_member(); if not public.has_member_role(array['admin'::public.member_role,'editor'::public.member_role]) then raise exception 'Editor membership required to unpublish writing' using errcode='42501'; end if; perform 1 from public.projects where id=target for update; select * into draft from public.writing_drafts where project_id=target for update; if not found or draft.owner_id <> actor then raise exception 'Writing author required' using errcode='42501'; end if; if expected_revision is null or draft.revision <> expected_revision then raise exception 'Stale writing revision' using errcode='40001'; end if; update public.publications set is_public=false where project_id=target and snapshot->>'division'='wordrows' returning * into release; update public.writing_drafts set revision=revision+1,updated_at=now() where project_id=target; update public.projects set stage='make',is_public=false where id=target; return jsonb_build_object('projectId',target,'revision',draft.revision+1,'publicationSlug',release.slug); end; $$; revoke execute on function private.writing_text(jsonb,integer),private.writing_url(text,text),private.validate_writing(jsonb,boolean) from public,anon,authenticated; revoke execute on function public.save_writing_draft(jsonb,uuid,integer,boolean),public.unpublish_writing(uuid,integer) from public,anon,authenticated; grant execute on function public.save_writing_draft(jsonb,uuid,integer,boolean),public.unpublish_writing(uuid,integer) to authenticated;

-- A writing project must never be released through a generic artifact snapshot. -- This closes cross-workflow entry points without relaxing review requirements. create function private.guard_writing_publication() returns trigger language plpgsql security definer set search_path = '' as $$ begin if exists (select 1 from public.writing_drafts where project_id=new.project_id) then if new.snapshot->>'division' is distinct from 'wordrows' then raise exception 'Use the writing publication action for Wordrows' using errcode='22023'; end if; perform private.validate_writing(new.snapshot->'writing',true); end if; return new; end; $$; create trigger writing_publication_guard before insert or update on public.publications for each row execute function private.guard_writing_publication(); revoke execute on function private.guard_writing_publication() from public,anon,authenticated; $migration$]); commit;

begin; -- Wordrows is an isolated editorial publishing path. Existing reviewed project -- publication functions remain unchanged. Draft content is never publicly read. create table public.writing_drafts ( project_id uuid primary key references public.projects(id) on delete cascade, owner_id uuid not null references public.profiles(user_id) on delete restrict, content jsonb not null, revision integer not null default 1 check (revision > 0), updated_at timestamptz not null default now() ); alter table public.writing_drafts enable row level security; create policy "Authors read their writing drafts" on public.writing_drafts for select to authenticated using (owner_id = auth.uid() and public.is_active_member()); revoke all on public.writing_drafts from public, anon, authenticated; grant select on public.writing_drafts to authenticated;

create function private.writing_text(value jsonb, maximum integer) returns boolean language sql immutable set search_path = '' as $$ select coalesce(jsonb_typeof(value) = 'string' and length(value #>> '{}') <= maximum, false) $$; create function private.writing_url(value text, kind text default 'image') returns boolean language plpgsql immutable set search_path = '' as $$ begin if value = '' then return true; end if; if value is null or length(value) > 2048 or value !~ '^https://[a-zA-Z0-9.-]+(:443)?(/[^[:space:]\\]*)?$' then return false; end if; if kind = 'model' then return value ~ '^https://my\.spline\.design/[a-zA-Z0-9_-]+/?$' or value ~ '^https://(www\.)?sketchfab\.com/models/[a-f0-9]{32}/embed/?$'; elsif kind = 'video' then return value ~ '^https://(www\.)?(youtube\.com|youtube-nocookie\.com)/embed/[a-zA-Z0-9_-]{11}$' or value ~ '^https://(www\.)?youtube\.com/watch\?v=[a-zA-Z0-9_-]{11}(&[^[:space:]]*)?$' or value ~ '^https://youtu\.be/[a-zA-Z0-9_-]{11}$' or value ~ '^https://(www\.)?vimeo\.com/[0-9]+/?$' or value ~ '^https://player\.vimeo\.com/video/[0-9]+/?$'; end if; return true; end; $$; create function private.validate_writing(document jsonb, ready boolean) returns void language plpgsql set search_path = '' as $$ declare chapter jsonb; panel jsonb; begin if document is null or jsonb_typeof(document) <> 'object' or (document->'version') is distinct from '1'::jsonb or coalesce(document->>'kind','') not in ('article','book','magazine','lyrics') or not private.writing_text(document->'title',160) or not private.writing_text(document->'summary',4000) or jsonb_typeof(document->'chapters') is distinct from 'array' or jsonb_typeof(document->'panels') is distinct from 'array' then raise exception 'Invalid writing content' using errcode='22023'; end if; if (select count(*) from jsonb_object_keys(document)) <> 6 then raise exception 'Unexpected writing fields' using errcode='22023'; end if; if jsonb_array_length(document->'chapters') <> 4 or jsonb_array_length(document->'panels') <> 2 or (ready and document->>'title' !~ '[^[:space:]]') then raise exception 'Writing requires a title, four chapters and two panels' using errcode='22023'; end if; for chapter in select value from jsonb_array_elements(document->'chapters') loop if jsonb_typeof(chapter) is distinct from 'object' then raise exception 'Invalid chapter' using errcode='22023'; end if; if (select count(*) from jsonb_object_keys(chapter)) <> 4 or not private.writing_text(chapter->'title',160) or not private.writing_text(chapter->'body',50000) or not private.writing_text(chapter->'imageUrl',2048) or not private.writing_text(chapter->'imageAlt',500) or not private.writing_url(chapter->>'imageUrl') or (ready and (chapter->>'title' !~ '[^[:space:]]' or chapter->>'body' !~ '[^[:space:]]')) then raise exception 'Each chapter needs valid text and an optional HTTPS image' using errcode='22023'; end if; end loop; for panel in select value from jsonb_array_elements(document->'panels') loop if jsonb_typeof(panel) is distinct from 'object' then raise exception 'Invalid supporting panel' using errcode='22023'; end if; if (select count(*) from jsonb_object_keys(panel)) <> 4 or coalesce(panel->>'kind','') not in ('model','image','video','sources') or not private.writing_text(panel->'title',160) or not private.writing_text(panel->'body',10000) or not private.writing_text(panel->'url',2048) or not private.writing_url(panel->>'url',panel->>'kind') then raise exception 'Invalid supporting panel or unsupported embed URL' using errcode='22023'; end if; end loop; end; $$;

create function public.save_writing_draft(document jsonb, target uuid default null, expected_revision integer default 0, publish_now boolean default false) returns jsonb language plpgsql security definer set search_path = '' as $$ declare actor uuid; draft public.writing_drafts; writing_project_id uuid; release public.publications; next_revision integer; begin actor := private.require_member(); if publish_now is null or expected_revision is null then raise exception 'Invalid writing request' using errcode='22023'; end if; if publish_now and not public.has_member_role(array['admin'::public.member_role,'editor'::public.member_role]) then raise exception 'Editor membership required to publish writing' using errcode='42501'; end if; perform private.validate_writing(document,publish_now); if target is null then if expected_revision <> 0 then raise exception 'Stale writing revision' using errcode='40001'; end if; writing_project_id := public.create_project(coalesce(nullif(btrim(document->>'title'),''),'Untitled article'),document->>'summary'); insert into public.writing_drafts(project_id,owner_id,content) values(writing_project_id,actor,document) returning * into draft; next_revision := 1; else -- Always lock the project first, matching the established workflow lock order. perform 1 from public.projects where id=target for update; select * into draft from public.writing_drafts where writing_drafts.project_id=target for update; if not found or draft.owner_id <> actor then raise exception 'Writing author required' using errcode='42501'; end if; if draft.revision <> expected_revision then raise exception 'Stale writing revision' using errcode='40001'; end if; writing_project_id := target; next_revision := draft.revision+1; update public.writing_drafts set content=document,revision=next_revision,updated_at=now() where writing_drafts.project_id=target; end if; -- Draft metadata stays private; published readers use publications.snapshot only. update public.projects set title=coalesce(nullif(btrim(document->>'title'),''),'Untitled article'),summary=document->>'summary',updated_at=now() where id=writing_project_id; select * into release from public.publications where publications.project_id=writing_project_id; if publish_now then if release.id is not null and release.snapshot->>'division' is distinct from 'wordrows' then raise exception 'Reviewed publications cannot be replaced by writing' using errcode='42501'; end if; insert into public.publications(project_id,slug,title,excerpt,published_by,snapshot,is_public) values(writing_project_id,'writing-'||writing_project_id::text,btrim(document->>'title'),document->>'summary',actor, jsonb_build_object('division','wordrows','writing',document,'revision',next_revision),true) on conflict on constraint publications_project_id_key do update set title=excluded.title,excerpt=excluded.excerpt, snapshot=excluded.snapshot,is_public=true,updated_at=now() returning * into release; insert into public.publication_credits(publication_id,user_id,credit_label) values(release.id,actor,'author') on conflict do nothing; update public.projects set stage='ship',published_at=coalesce(published_at,now()) where id=writing_project_id; end if; return jsonb_build_object('projectId',writing_project_id,'revision',next_revision,'publicationSlug',release.slug); end; $$; create function public.unpublish_writing(target uuid, expected_revision integer) returns jsonb language plpgsql security definer set search_path = '' as $$ declare actor uuid; draft public.writing_drafts; release public.publications; begin actor := private.require_member(); if not public.has_member_role(array['admin'::public.member_role,'editor'::public.member_role]) then raise exception 'Editor membership required to unpublish writing' using errcode='42501'; end if; perform 1 from public.projects where id=target for update; select * into draft from public.writing_drafts where project_id=target for update; if not found or draft.owner_id <> actor then raise exception 'Writing author required' using errcode='42501'; end if; if expected_revision is null or draft.revision <> expected_revision then raise exception 'Stale writing revision' using errcode='40001'; end if; update public.publications set is_public=false where project_id=target and snapshot->>'division'='wordrows' returning * into release; update public.writing_drafts set revision=revision+1,updated_at=now() where project_id=target; update public.projects set stage='make',is_public=false where id=target; return jsonb_build_object('projectId',target,'revision',draft.revision+1,'publicationSlug',release.slug); end; $$; revoke execute on function private.writing_text(jsonb,integer),private.writing_url(text,text),private.validate_writing(jsonb,boolean) from public,anon,authenticated; revoke execute on function public.save_writing_draft(jsonb,uuid,integer,boolean),public.unpublish_writing(uuid,integer) from public,anon,authenticated; grant execute on function public.save_writing_draft(jsonb,uuid,integer,boolean),public.unpublish_writing(uuid,integer) to authenticated;

-- A writing project must never be released through a generic artifact snapshot. -- This closes cross-workflow entry points without relaxing review requirements. create function private.guard_writing_publication() returns trigger language plpgsql security definer set search_path = '' as $$ begin if exists (select 1 from public.writing_drafts where project_id=new.project_id) then if new.snapshot->>'division' is distinct from 'wordrows' then raise exception 'Use the writing publication action for Wordrows' using errcode='22023'; end if; perform private.validate_writing(new.snapshot->'writing',true); end if; return new; end; $$; create trigger writing_publication_guard before insert or update on public.publications for each row execute function private.guard_writing_publication(); revoke execute on function private.guard_writing_publication() from public,anon,authenticated;

insert into supabase_migrations.schema_migrations(version,name,statements) values ('202609120001','wordrows_writing',array[$migration$-- Wordrows is an isolated editorial publishing path. Existing reviewed project -- publication functions remain unchanged. Draft content is never publicly read. create table public.writing_drafts ( project_id uuid primary key references public.projects(id) on delete cascade, owner_id uuid not null references public.profiles(user_id) on delete restrict, content jsonb not null, revision integer not null default 1 check (revision > 0), updated_at timestamptz not null default now() ); alter table public.writing_drafts enable row level security; create policy "Authors read their writing drafts" on public.writing_drafts for select to authenticated using (owner_id = auth.uid() and public.is_active_member()); revoke all on public.writing_drafts from public, anon, authenticated; grant select on public.writing_drafts to authenticated;

create function private.writing_text(value jsonb, maximum integer) returns boolean language sql immutable set search_path = '' as $$ select coalesce(jsonb_typeof(value) = 'string' and length(value #>> '{}') <= maximum, false) $$; create function private.writing_url(value text, kind text default 'image') returns boolean language plpgsql immutable set search_path = '' as $$ begin if value = '' then return true; end if; if value is null or length(value) > 2048 or value !~ '^https://[a-zA-Z0-9.-]+(:443)?(/[^[:space:]\\]*)?$' then return false; end if; if kind = 'model' then return value ~ '^https://my\.spline\.design/[a-zA-Z0-9_-]+/?$' or value ~ '^https://(www\.)?sketchfab\.com/models/[a-f0-9]{32}/embed/?$'; elsif kind = 'video' then return value ~ '^https://(www\.)?(youtube\.com|youtube-nocookie\.com)/embed/[a-zA-Z0-9_-]{11}$' or value ~ '^https://(www\.)?youtube\.com/watch\?v=[a-zA-Z0-9_-]{11}(&[^[:space:]]*)?$' or value ~ '^https://youtu\.be/[a-zA-Z0-9_-]{11}$' or value ~ '^https://(www\.)?vimeo\.com/[0-9]+/?$' or value ~ '^https://player\.vimeo\.com/video/[0-9]+/?$'; end if; return true; end; $$; create function private.validate_writing(document jsonb, ready boolean) returns void language plpgsql set search_path = '' as $$ declare chapter jsonb; panel jsonb; begin if document is null or jsonb_typeof(document) <> 'object' or (document->'version') is distinct from '1'::jsonb or coalesce(document->>'kind','') not in ('article','book','magazine','lyrics') or not private.writing_text(document->'title',160) or not private.writing_text(document->'summary',4000) or jsonb_typeof(document->'chapters') is distinct from 'array' or jsonb_typeof(document->'panels') is distinct from 'array' then raise exception 'Invalid writing content' using errcode='22023'; end if; if (select count(*) from jsonb_object_keys(document)) <> 6 then raise exception 'Unexpected writing fields' using errcode='22023'; end if; if jsonb_array_length(document->'chapters') <> 4 or jsonb_array_length(document->'panels') <> 2 or (ready and document->>'title' !~ '[^[:space:]]') then raise exception 'Writing requires a title, four chapters and two panels' using errcode='22023'; end if; for chapter in select value from jsonb_array_elements(document->'chapters') loop if jsonb_typeof(chapter) is distinct from 'object' then raise exception 'Invalid chapter' using errcode='22023'; end if; if (select count(*) from jsonb_object_keys(chapter)) <> 4 or not private.writing_text(chapter->'title',160) or not private.writing_text(chapter->'body',50000) or not private.writing_text(chapter->'imageUrl',2048) or not private.writing_text(chapter->'imageAlt',500) or not private.writing_url(chapter->>'imageUrl') or (ready and (chapter->>'title' !~ '[^[:space:]]' or chapter->>'body' !~ '[^[:space:]]')) then raise exception 'Each chapter needs valid text and an optional HTTPS image' using errcode='22023'; end if; end loop; for panel in select value from jsonb_array_elements(document->'panels') loop if jsonb_typeof(panel) is distinct from 'object' then raise exception 'Invalid supporting panel' using errcode='22023'; end if; if (select count(*) from jsonb_object_keys(panel)) <> 4 or coalesce(panel->>'kind','') not in ('model','image','video','sources') or not private.writing_text(panel->'title',160) or not private.writing_text(panel->'body',10000) or not private.writing_text(panel->'url',2048) or not private.writing_url(panel->>'url',panel->>'kind') then raise exception 'Invalid supporting panel or unsupported embed URL' using errcode='22023'; end if; end loop; end; $$;

create function public.save_writing_draft(document jsonb, target uuid default null, expected_revision integer default 0, publish_now boolean default false) returns jsonb language plpgsql security definer set search_path = '' as $$ declare actor uuid; draft public.writing_drafts; writing_project_id uuid; release public.publications; next_revision integer; begin actor := private.require_member(); if publish_now is null or expected_revision is null then raise exception 'Invalid writing request' using errcode='22023'; end if; if publish_now and not public.has_member_role(array['admin'::public.member_role,'editor'::public.member_role]) then raise exception 'Editor membership required to publish writing' using errcode='42501'; end if; perform private.validate_writing(document,publish_now); if target is null then if expected_revision <> 0 then raise exception 'Stale writing revision' using errcode='40001'; end if; writing_project_id := public.create_project(coalesce(nullif(btrim(document->>'title'),''),'Untitled article'),document->>'summary'); insert into public.writing_drafts(project_id,owner_id,content) values(writing_project_id,actor,document) returning * into draft; next_revision := 1; else -- Always lock the project first, matching the established workflow lock order. perform 1 from public.projects where id=target for update; select * into draft from public.writing_drafts where writing_drafts.project_id=target for update; if not found or draft.owner_id <> actor then raise exception 'Writing author required' using errcode='42501'; end if; if draft.revision <> expected_revision then raise exception 'Stale writing revision' using errcode='40001'; end if; writing_project_id := target; next_revision := draft.revision+1; update public.writing_drafts set content=document,revision=next_revision,updated_at=now() where writing_drafts.project_id=target; end if; -- Draft metadata stays private; published readers use publications.snapshot only. update public.projects set title=coalesce(nullif(btrim(document->>'title'),''),'Untitled article'),summary=document->>'summary',updated_at=now() where id=writing_project_id; select * into release from public.publications where publications.project_id=writing_project_id; if publish_now then if release.id is not null and release.snapshot->>'division' is distinct from 'wordrows' then raise exception 'Reviewed publications cannot be replaced by writing' using errcode='42501'; end if; insert into public.publications(project_id,slug,title,excerpt,published_by,snapshot,is_public) values(writing_project_id,'writing-'||writing_project_id::text,btrim(document->>'title'),document->>'summary',actor, jsonb_build_object('division','wordrows','writing',document,'revision',next_revision),true) on conflict on constraint publications_project_id_key do update set title=excluded.title,excerpt=excluded.excerpt, snapshot=excluded.snapshot,is_public=true,updated_at=now() returning * into release; insert into public.publication_credits(publication_id,user_id,credit_label) values(release.id,actor,'author') on conflict do nothing; update public.projects set stage='ship',published_at=coalesce(published_at,now()) where id=writing_project_id; end if; return jsonb_build_object('projectId',writing_project_id,'revision',next_revision,'publicationSlug',release.slug); end; $$; create function public.unpublish_writing(target uuid, expected_revision integer) returns jsonb language plpgsql security definer set search_path = '' as $$ declare actor uuid; draft public.writing_drafts; release public.publications; begin actor := private.require_member(); if not public.has_member_role(array['admin'::public.member_role,'editor'::public.member_role]) then raise exception 'Editor membership required to unpublish writing' using errcode='42501'; end if; perform 1 from public.projects where id=target for update; select * into draft from public.writing_drafts where project_id=target for update; if not found or draft.owner_id <> actor then raise exception 'Writing author required' using errcode='42501'; end if; if expected_revision is null or draft.revision <> expected_revision then raise exception 'Stale writing revision' using errcode='40001'; end if; update public.publications set is_public=false where project_id=target and snapshot->>'division'='wordrows' returning * into release; update public.writing_drafts set revision=revision+1,updated_at=now() where project_id=target; update public.projects set stage='make',is_public=false where id=target; return jsonb_build_object('projectId',target,'revision',draft.revision+1,'publicationSlug',release.slug); end; $$; revoke execute on function private.writing_text(jsonb,integer),private.writing_url(text,text),private.validate_writing(jsonb,boolean) from public,anon,authenticated; revoke execute on function public.save_writing_draft(jsonb,uuid,integer,boolean),public.unpublish_writing(uuid,integer) from public,anon,authenticated; grant execute on function public.save_writing_draft(jsonb,uuid,integer,boolean),public.unpublish_writing(uuid,integer) to authenticated;

-- A writing project must never be released through a generic artifact snapshot. -- This closes cross-workflow entry points without relaxing review requirements. create function private.guard_writing_publication() returns trigger language plpgsql security definer set search_path = '' as $$ begin if exists (select 1 from public.writing_drafts where project_id=new.project_id) then if new.snapshot->>'division' is distinct from 'wordrows' then raise exception 'Use the writing publication action for Wordrows' using errcode='22023'; end if; perform private.validate_writing(new.snapshot->'writing',true); end if; return new; end; $$; create trigger writing_publication_guard before insert or update on public.publications for each row execute function private.guard_writing_publication(); revoke execute on function private.guard_writing_publication() from public,anon,authenticated; $migration$]); commit;

begin; -- Wordrows is an isolated editorial publishing path. Existing reviewed project -- publication functions remain unchanged. Draft content is never publicly read. create table public.writing_drafts ( project_id uuid primary key references public.projects(id) on delete cascade, owner_id uuid not null references public.profiles(user_id) on delete restrict, content jsonb not null, revision integer not null default 1 check (revision > 0), updated_at timestamptz not null default now() ); alter table public.writing_drafts enable row level security; create policy "Authors read their writing drafts" on public.writing_drafts for select to authenticated using (owner_id = auth.uid() and public.is_active_member()); revoke all on public.writing_drafts from public, anon, authenticated; grant select on public.writing_drafts to authenticated;

create function private.writing_text(value jsonb, maximum integer) returns boolean language sql immutable set search_path = '' as $$ select coalesce(jsonb_typeof(value) = 'string' and length(value #>> '{}') <= maximum, false) $$; create function private.writing_url(value text, kind text default 'image') returns boolean language plpgsql immutable set search_path = '' as $$ begin if value = '' then return true; end if; if value is null or length(value) > 2048 or value !~ '^https://[a-zA-Z0-9.-]+(:443)?(/[^[:space:]\\]*)?$' then return false; end if; if kind = 'model' then return value ~ '^https://my\.spline\.design/[a-zA-Z0-9_-]+/?$' or value ~ '^https://(www\.)?sketchfab\.com/models/[a-f0-9]{32}/embed/?$'; elsif kind = 'video' then return value ~ '^https://(www\.)?(youtube\.com|youtube-nocookie\.com)/embed/[a-zA-Z0-9_-]{11}$' or value ~ '^https://(www\.)?youtube\.com/watch\?v=[a-zA-Z0-9_-]{11}(&[^[:space:]]*)?$' or value ~ '^https://youtu\.be/[a-zA-Z0-9_-]{11}$' or value ~ '^https://(www\.)?vimeo\.com/[0-9]+/?$' or value ~ '^https://player\.vimeo\.com/video/[0-9]+/?$'; end if; return true; end; $$; create function private.validate_writing(document jsonb, ready boolean) returns void language plpgsql set search_path = '' as $$ declare chapter jsonb; panel jsonb; begin if document is null or jsonb_typeof(document) <> 'object' or (document->'version') is distinct from '1'::jsonb or coalesce(document->>'kind','') not in ('article','book','magazine','lyrics') or not private.writing_text(document->'title',160) or not private.writing_text(document->'summary',4000) or jsonb_typeof(document->'chapters') is distinct from 'array' or jsonb_typeof(document->'panels') is distinct from 'array' then raise exception 'Invalid writing content' using errcode='22023'; end if; if (select count(*) from jsonb_object_keys(document)) <> 6 then raise exception 'Unexpected writing fields' using errcode='22023'; end if; if jsonb_array_length(document->'chapters') <> 4 or jsonb_array_length(document->'panels') <> 2 or (ready and document->>'title' !~ '[^[:space:]]') then raise exception 'Writing requires a title, four chapters and two panels' using errcode='22023'; end if; for chapter in select value from jsonb_array_elements(document->'chapters') loop if jsonb_typeof(chapter) is distinct from 'object' then raise exception 'Invalid chapter' using errcode='22023'; end if; if (select count(*) from jsonb_object_keys(chapter)) <> 4 or not private.writing_text(chapter->'title',160) or not private.writing_text(chapter->'body',50000) or not private.writing_text(chapter->'imageUrl',2048) or not private.writing_text(chapter->'imageAlt',500) or not private.writing_url(chapter->>'imageUrl') or (ready and (chapter->>'title' !~ '[^[:space:]]' or chapter->>'body' !~ '[^[:space:]]')) then raise exception 'Each chapter needs valid text and an optional HTTPS image' using errcode='22023'; end if; end loop; for panel in select value from jsonb_array_elements(document->'panels') loop if jsonb_typeof(panel) is distinct from 'object' then raise exception 'Invalid supporting panel' using errcode='22023'; end if; if (select count(*) from jsonb_object_keys(panel)) <> 4 or coalesce(panel->>'kind','') not in ('model','image','video','sources') or not private.writing_text(panel->'title',160) or not private.writing_text(panel->'body',10000) or not private.writing_text(panel->'url',2048) or not private.writing_url(panel->>'url',panel->>'kind') then raise exception 'Invalid supporting panel or unsupported embed URL' using errcode='22023'; end if; end loop; end; $$;

create function public.save_writing_draft(document jsonb, target uuid default null, expected_revision integer default 0, publish_now boolean default false) returns jsonb language plpgsql security definer set search_path = '' as $$ declare actor uuid; draft public.writing_drafts; writing_project_id uuid; release public.publications; next_revision integer; begin actor := private.require_member(); if publish_now is null or expected_revision is null then raise exception 'Invalid writing request' using errcode='22023'; end if; if publish_now and not public.has_member_role(array['admin'::public.member_role,'editor'::public.member_role]) then raise exception 'Editor membership required to publish writing' using errcode='42501'; end if; perform private.validate_writing(document,publish_now); if target is null then if expected_revision <> 0 then raise exception 'Stale writing revision' using errcode='40001'; end if; writing_project_id := public.create_project(coalesce(nullif(btrim(document->>'title'),''),'Untitled article'),document->>'summary'); insert into public.writing_drafts(project_id,owner_id,content) values(writing_project_id,actor,document) returning * into draft; next_revision := 1; else -- Always lock the project first, matching the established workflow lock order. perform 1 from public.projects where id=target for update; select * into draft from public.writing_drafts where writing_drafts.project_id=target for update; if not found or draft.owner_id <> actor then raise exception 'Writing author required' using errcode='42501'; end if; if draft.revision <> expected_revision then raise exception 'Stale writing revision' using errcode='40001'; end if; writing_project_id := target; next_revision := draft.revision+1; update public.writing_drafts set content=document,revision=next_revision,updated_at=now() where writing_drafts.project_id=target; end if; -- Draft metadata stays private; published readers use publications.snapshot only. update public.projects set title=coalesce(nullif(btrim(document->>'title'),''),'Untitled article'),summary=document->>'summary',updated_at=now() where id=writing_project_id; select * into release from public.publications where publications.project_id=writing_project_id; if publish_now then if release.id is not null and release.snapshot->>'division' is distinct from 'wordrows' then raise exception 'Reviewed publications cannot be replaced by writing' using errcode='42501'; end if; insert into public.publications(project_id,slug,title,excerpt,published_by,snapshot,is_public) values(writing_project_id,'writing-'||writing_project_id::text,btrim(document->>'title'),document->>'summary',actor, jsonb_build_object('division','wordrows','writing',document,'revision',next_revision),true) on conflict on constraint publications_project_id_key do update set title=excluded.title,excerpt=excluded.excerpt, snapshot=excluded.snapshot,is_public=true,updated_at=now() returning * into release; insert into public.publication_credits(publication_id,user_id,credit_label) values(release.id,actor,'author') on conflict do nothing; update public.projects set stage='ship',published_at=coalesce(published_at,now()) where id=writing_project_id; end if; return jsonb_build_object('projectId',writing_project_id,'revision',next_revision,'publicationSlug',release.slug); end; $$; create function public.unpublish_writing(target uuid, expected_revision integer) returns jsonb language plpgsql security definer set search_path = '' as $$ declare actor uuid; draft public.writing_drafts; release public.publications; begin actor := private.require_member(); if not public.has_member_role(array['admin'::public.member_role,'editor'::public.member_role]) then raise exception 'Editor membership required to unpublish writing' using errcode='42501'; end if; perform 1 from public.projects where id=target for update; select * into draft from public.writing_drafts where project_id=target for update; if not found or draft.owner_id <> actor then raise exception 'Writing author required' using errcode='42501'; end if; if expected_revision is null or draft.revision <> expected_revision then raise exception 'Stale writing revision' using errcode='40001'; end if; update public.publications set is_public=false where project_id=target and snapshot->>'division'='wordrows' returning * into release; update public.writing_drafts set revision=revision+1,updated_at=now() where project_id=target; update public.projects set stage='make',is_public=false where id=target; return jsonb_build_object('projectId',target,'revision',draft.revision+1,'publicationSlug',release.slug); end; $$; revoke execute on function private.writing_text(jsonb,integer),private.writing_url(text,text),private.validate_writing(jsonb,boolean) from public,anon,authenticated; revoke execute on function public.save_writing_draft(jsonb,uuid,integer,boolean),public.unpublish_writing(uuid,integer) from public,anon,authenticated; grant execute on function public.save_writing_draft(jsonb,uuid,integer,boolean),public.unpublish_writing(uuid,integer) to authenticated;

-- A writing project must never be released through a generic artifact snapshot. -- This closes cross-workflow entry points without relaxing review requirements. create function private.guard_writing_publication() returns trigger language plpgsql security definer set search_path = '' as $$ begin if exists (select 1 from public.writing_drafts where project_id=new.project_id) then if new.snapshot->>'division' is distinct from 'wordrows' then raise exception 'Use the writing publication action for Wordrows' using errcode='22023'; end if; perform private.validate_writing(new.snapshot->'writing',true); end if; return new; end; $$; create trigger writing_publication_guard before insert or update on public.publications for each row execute function private.guard_writing_publication(); revoke execute on function private.guard_writing_publication() from public,anon,authenticated;

insert into supabase_migrations.schema_migrations(version,name,statements) values ('202609120001','wordrows_writing',array[$migration$-- Wordrows is an isolated editorial publishing path. Existing reviewed project -- publication functions remain unchanged. Draft content is never publicly read. create table public.writing_drafts ( project_id uuid primary key references public.projects(id) on delete cascade, owner_id uuid not null references public.profiles(user_id) on delete restrict, content jsonb not null, revision integer not null default 1 check (revision > 0), updated_at timestamptz not null default now() ); alter table public.writing_drafts enable row level security; create policy "Authors read their writing drafts" on public.writing_drafts for select to authenticated using (owner_id = auth.uid() and public.is_active_member()); revoke all on public.writing_drafts from public, anon, authenticated; grant select on public.writing_drafts to authenticated;

create function private.writing_text(value jsonb, maximum integer) returns boolean language sql immutable set search_path = '' as $$ select coalesce(jsonb_typeof(value) = 'string' and length(value #>> '{}') <= maximum, false) $$; create function private.writing_url(value text, kind text default 'image') returns boolean language plpgsql immutable set search_path = '' as $$ begin if value = '' then return true; end if; if value is null or length(value) > 2048 or value !~ '^https://[a-zA-Z0-9.-]+(:443)?(/[^[:space:]\\]*)?$' then return false; end if; if kind = 'model' then return value ~ '^https://my\.spline\.design/[a-zA-Z0-9_-]+/?$' or value ~ '^https://(www\.)?sketchfab\.com/models/[a-f0-9]{32}/embed/?$'; elsif kind = 'video' then return value ~ '^https://(www\.)?(youtube\.com|youtube-nocookie\.com)/embed/[a-zA-Z0-9_-]{11}$' or value ~ '^https://(www\.)?youtube\.com/watch\?v=[a-zA-Z0-9_-]{11}(&[^[:space:]]*)?$' or value ~ '^https://youtu\.be/[a-zA-Z0-9_-]{11}$' or value ~ '^https://(www\.)?vimeo\.com/[0-9]+/?$' or value ~ '^https://player\.vimeo\.com/video/[0-9]+/?$'; end if; return true; end; $$; create function private.validate_writing(document jsonb, ready boolean) returns void language plpgsql set search_path = '' as $$ declare chapter jsonb; panel jsonb; begin if document is null or jsonb_typeof(document) <> 'object' or (document->'version') is distinct from '1'::jsonb or coalesce(document->>'kind','') not in ('article','book','magazine','lyrics') or not private.writing_text(document->'title',160) or not private.writing_text(document->'summary',4000) or jsonb_typeof(document->'chapters') is distinct from 'array' or jsonb_typeof(document->'panels') is distinct from 'array' then raise exception 'Invalid writing content' using errcode='22023'; end if; if (select count(*) from jsonb_object_keys(document)) <> 6 then raise exception 'Unexpected writing fields' using errcode='22023'; end if; if jsonb_array_length(document->'chapters') <> 4 or jsonb_array_length(document->'panels') <> 2 or (ready and document->>'title' !~ '[^[:space:]]') then raise exception 'Writing requires a title, four chapters and two panels' using errcode='22023'; end if; for chapter in select value from jsonb_array_elements(document->'chapters') loop if jsonb_typeof(chapter) is distinct from 'object' then raise exception 'Invalid chapter' using errcode='22023'; end if; if (select count(*) from jsonb_object_keys(chapter)) <> 4 or not private.writing_text(chapter->'title',160) or not private.writing_text(chapter->'body',50000) or not private.writing_text(chapter->'imageUrl',2048) or not private.writing_text(chapter->'imageAlt',500) or not private.writing_url(chapter->>'imageUrl') or (ready and (chapter->>'title' !~ '[^[:space:]]' or chapter->>'body' !~ '[^[:space:]]')) then raise exception 'Each chapter needs valid text and an optional HTTPS image' using errcode='22023'; end if; end loop; for panel in select value from jsonb_array_elements(document->'panels') loop if jsonb_typeof(panel) is distinct from 'object' then raise exception 'Invalid supporting panel' using errcode='22023'; end if; if (select count(*) from jsonb_object_keys(panel)) <> 4 or coalesce(panel->>'kind','') not in ('model','image','video','sources') or not private.writing_text(panel->'title',160) or not private.writing_text(panel->'body',10000) or not private.writing_text(panel->'url',2048) or not private.writing_url(panel->>'url',panel->>'kind') then raise exception 'Invalid supporting panel or unsupported embed URL' using errcode='22023'; end if; end loop; end; $$;

create function public.save_writing_draft(document jsonb, target uuid default null, expected_revision integer default 0, publish_now boolean default false) returns jsonb language plpgsql security definer set search_path = '' as $$ declare actor uuid; draft public.writing_drafts; writing_project_id uuid; release public.publications; next_revision integer; begin actor := private.require_member(); if publish_now is null or expected_revision is null then raise exception 'Invalid writing request' using errcode='22023'; end if; if publish_now and not public.has_member_role(array['admin'::public.member_role,'editor'::public.member_role]) then raise exception 'Editor membership required to publish writing' using errcode='42501'; end if; perform private.validate_writing(document,publish_now); if target is null then if expected_revision <> 0 then raise exception 'Stale writing revision' using errcode='40001'; end if; writing_project_id := public.create_project(coalesce(nullif(btrim(document->>'title'),''),'Untitled article'),document->>'summary'); insert into public.writing_drafts(project_id,owner_id,content) values(writing_project_id,actor,document) returning * into draft; next_revision := 1; else -- Always lock the project first, matching the established workflow lock order. perform 1 from public.projects where id=target for update; select * into draft from public.writing_drafts where writing_drafts.project_id=target for update; if not found or draft.owner_id <> actor then raise exception 'Writing author required' using errcode='42501'; end if; if draft.revision <> expected_revision then raise exception 'Stale writing revision' using errcode='40001'; end if; writing_project_id := target; next_revision := draft.revision+1; update public.writing_drafts set content=document,revision=next_revision,updated_at=now() where writing_drafts.project_id=target; end if; -- Draft metadata stays private; published readers use publications.snapshot only. update public.projects set title=coalesce(nullif(btrim(document->>'title'),''),'Untitled article'),summary=document->>'summary',updated_at=now() where id=writing_project_id; select * into release from public.publications where publications.project_id=writing_project_id; if publish_now then if release.id is not null and release.snapshot->>'division' is distinct from 'wordrows' then raise exception 'Reviewed publications cannot be replaced by writing' using errcode='42501'; end if; insert into public.publications(project_id,slug,title,excerpt,published_by,snapshot,is_public) values(writing_project_id,'writing-'||writing_project_id::text,btrim(document->>'title'),document->>'summary',actor, jsonb_build_object('division','wordrows','writing',document,'revision',next_revision),true) on conflict on constraint publications_project_id_key do update set title=excluded.title,excerpt=excluded.excerpt, snapshot=excluded.snapshot,is_public=true,updated_at=now() returning * into release; insert into public.publication_credits(publication_id,user_id,credit_label) values(release.id,actor,'author') on conflict do nothing; update public.projects set stage='ship',published_at=coalesce(published_at,now()) where id=writing_project_id; end if; return jsonb_build_object('projectId',writing_project_id,'revision',next_revision,'publicationSlug',release.slug); end; $$; create function public.unpublish_writing(target uuid, expected_revision integer) returns jsonb language plpgsql security definer set search_path = '' as $$ declare actor uuid; draft public.writing_drafts; release public.publications; begin actor := private.require_member(); if not public.has_member_role(array['admin'::public.member_role,'editor'::public.member_role]) then raise exception 'Editor membership required to unpublish writing' using errcode='42501'; end if; perform 1 from public.projects where id=target for update; select * into draft from public.writing_drafts where project_id=target for update; if not found or draft.owner_id <> actor then raise exception 'Writing author required' using errcode='42501'; end if; if expected_revision is null or draft.revision <> expected_revision then raise exception 'Stale writing revision' using errcode='40001'; end if; update public.publications set is_public=false where project_id=target and snapshot->>'division'='wordrows' returning * into release; update public.writing_drafts set revision=revision+1,updated_at=now() where project_id=target; update public.projects set stage='make',is_public=false where id=target; return jsonb_build_object('projectId',target,'revision',draft.revision+1,'publicationSlug',release.slug); end; $$; revoke execute on function private.writing_text(jsonb,integer),private.writing_url(text,text),private.validate_writing(jsonb,boolean) from public,anon,authenticated; revoke execute on function public.save_writing_draft(jsonb,uuid,integer,boolean),public.unpublish_writing(uuid,integer) from public,anon,authenticated; grant execute on function public.save_writing_draft(jsonb,uuid,integer,boolean),public.unpublish_writing(uuid,integer) to authenticated;

-- A writing project must never be released through a generic artifact snapshot. -- This closes cross-workflow entry points without relaxing review requirements. create function private.guard_writing_publication() returns trigger language plpgsql security definer set search_path = '' as $$ begin if exists (select 1 from public.writing_drafts where project_id=new.project_id) then if new.snapshot->>'division' is distinct from 'wordrows' then raise exception 'Use the writing publication action for Wordrows' using errcode='22023'; end if; perform private.validate_writing(new.snapshot->'writing',true); end if; return new; end; $$; create trigger writing_publication_guard before insert or update on public.publications for each row execute function private.guard_writing_publication(); revoke execute on function private.guard_writing_publication() from public,anon,authenticated; $migration$]); commit;

begin; -- Wordrows is an isolated editorial publishing path. Existing reviewed project -- publication functions remain unchanged. Draft content is never publicly read. create table public.writing_drafts ( project_id uuid primary key references public.projects(id) on delete cascade, owner_id uuid not null references public.profiles(user_id) on delete restrict, content jsonb not null, revision integer not null default 1 check (revision > 0), updated_at timestamptz not null default now() ); alter table public.writing_drafts enable row level security; create policy "Authors read their writing drafts" on public.writing_drafts for select to authenticated using (owner_id = auth.uid() and public.is_active_member()); revoke all on public.writing_drafts from public, anon, authenticated; grant select on public.writing_drafts to authenticated;

create function private.writing_text(value jsonb, maximum integer) returns boolean language sql immutable set search_path = '' as $$ select coalesce(jsonb_typeof(value) = 'string' and length(value #>> '{}') <= maximum, false) $$; create function private.writing_url(value text, kind text default 'image') returns boolean language plpgsql immutable set search_path = '' as $$ begin if value = '' then return true; end if; if value is null or length(value) > 2048 or value !~ '^https://[a-zA-Z0-9.-]+(:443)?(/[^[:space:]\\]*)?$' then return false; end if; if kind = 'model' then return value ~ '^https://my\.spline\.design/[a-zA-Z0-9_-]+/?$' or value ~ '^https://(www\.)?sketchfab\.com/models/[a-f0-9]{32}/embed/?$'; elsif kind = 'video' then return value ~ '^https://(www\.)?(youtube\.com|youtube-nocookie\.com)/embed/[a-zA-Z0-9_-]{11}$' or value ~ '^https://(www\.)?youtube\.com/watch\?v=[a-zA-Z0-9_-]{11}(&[^[:space:]]*)?$' or value ~ '^https://youtu\.be/[a-zA-Z0-9_-]{11}$' or value ~ '^https://(www\.)?vimeo\.com/[0-9]+/?$' or value ~ '^https://player\.vimeo\.com/video/[0-9]+/?$'; end if; return true; end; $$; create function private.validate_writing(document jsonb, ready boolean) returns void language plpgsql set search_path = '' as $$ declare chapter jsonb; panel jsonb; begin if document is null or jsonb_typeof(document) <> 'object' or (document->'version') is distinct from '1'::jsonb or coalesce(document->>'kind','') not in ('article','book','magazine','lyrics') or not private.writing_text(document->'title',160) or not private.writing_text(document->'summary',4000) or jsonb_typeof(document->'chapters') is distinct from 'array' or jsonb_typeof(document->'panels') is distinct from 'array' then raise exception 'Invalid writing content' using errcode='22023'; end if; if (select count(*) from jsonb_object_keys(document)) <> 6 then raise exception 'Unexpected writing fields' using errcode='22023'; end if; if jsonb_array_length(document->'chapters') <> 4 or jsonb_array_length(document->'panels') <> 2 or (ready and document->>'title' !~ '[^[:space:]]') then raise exception 'Writing requires a title, four chapters and two panels' using errcode='22023'; end if; for chapter in select value from jsonb_array_elements(document->'chapters') loop if jsonb_typeof(chapter) is distinct from 'object' then raise exception 'Invalid chapter' using errcode='22023'; end if; if (select count(*) from jsonb_object_keys(chapter)) <> 4 or not private.writing_text(chapter->'title',160) or not private.writing_text(chapter->'body',50000) or not private.writing_text(chapter->'imageUrl',2048) or not private.writing_text(chapter->'imageAlt',500) or not private.writing_url(chapter->>'imageUrl') or (ready and (chapter->>'title' !~ '[^[:space:]]' or chapter->>'body' !~ '[^[:space:]]')) then raise exception 'Each chapter needs valid text and an optional HTTPS image' using errcode='22023'; end if; end loop; for panel in select value from jsonb_array_elements(document->'panels') loop if jsonb_typeof(panel) is distinct from 'object' then raise exception 'Invalid supporting panel' using errcode='22023'; end if; if (select count(*) from jsonb_object_keys(panel)) <> 4 or coalesce(panel->>'kind','') not in ('model','image','video','sources') or not private.writing_text(panel->'title',160) or not private.writing_text(panel->'body',10000) or not private.writing_text(panel->'url',2048) or not private.writing_url(panel->>'url',panel->>'kind') then raise exception 'Invalid supporting panel or unsupported embed URL' using errcode='22023'; end if; end loop; end; $$;

create function public.save_writing_draft(document jsonb, target uuid default null, expected_revision integer default 0, publish_now boolean default false) returns jsonb language plpgsql security definer set search_path = '' as $$ declare actor uuid; draft public.writing_drafts; writing_project_id uuid; release public.publications; next_revision integer; begin actor := private.require_member(); if publish_now is null or expected_revision is null then raise exception 'Invalid writing request' using errcode='22023'; end if; if publish_now and not public.has_member_role(array['admin'::public.member_role,'editor'::public.member_role]) then raise exception 'Editor membership required to publish writing' using errcode='42501'; end if; perform private.validate_writing(document,publish_now); if target is null then if expected_revision <> 0 then raise exception 'Stale writing revision' using errcode='40001'; end if; writing_project_id := public.create_project(coalesce(nullif(btrim(document->>'title'),''),'Untitled article'),document->>'summary'); insert into public.writing_drafts(project_id,owner_id,content) values(writing_project_id,actor,document) returning * into draft; next_revision := 1; else -- Always lock the project first, matching the established workflow lock order. perform 1 from public.projects where id=target for update; select * into draft from public.writing_drafts where writing_drafts.project_id=target for update; if not found or draft.owner_id <> actor then raise exception 'Writing author required' using errcode='42501'; end if; if draft.revision <> expected_revision then raise exception 'Stale writing revision' using errcode='40001'; end if; writing_project_id := target; next_revision := draft.revision+1; update public.writing_drafts set content=document,revision=next_revision,updated_at=now() where writing_drafts.project_id=target; end if; -- Draft metadata stays private; published readers use publications.snapshot only. update public.projects set title=coalesce(nullif(btrim(document->>'title'),''),'Untitled article'),summary=document->>'summary',updated_at=now() where id=writing_project_id; select * into release from public.publications where publications.project_id=writing_project_id; if publish_now then if release.id is not null and release.snapshot->>'division' is distinct from 'wordrows' then raise exception 'Reviewed publications cannot be replaced by writing' using errcode='42501'; end if; insert into public.publications(project_id,slug,title,excerpt,published_by,snapshot,is_public) values(writing_project_id,'writing-'||writing_project_id::text,btrim(document->>'title'),document->>'summary',actor, jsonb_build_object('division','wordrows','writing',document,'revision',next_revision),true) on conflict on constraint publications_project_id_key do update set title=excluded.title,excerpt=excluded.excerpt, snapshot=excluded.snapshot,is_public=true,updated_at=now() returning * into release; insert into public.publication_credits(publication_id,user_id,credit_label) values(release.id,actor,'author') on conflict do nothing; update public.projects set stage='ship',published_at=coalesce(published_at,now()) where id=writing_project_id; end if; return jsonb_build_object('projectId',writing_project_id,'revision',next_revision,'publicationSlug',release.slug); end; $$; create function public.unpublish_writing(target uuid, expected_revision integer) returns jsonb language plpgsql security definer set search_path = '' as $$ declare actor uuid; draft public.writing_drafts; release public.publications; begin actor := private.require_member(); if not public.has_member_role(array['admin'::public.member_role,'editor'::public.member_role]) then raise exception 'Editor membership required to unpublish writing' using errcode='42501'; end if; perform 1 from public.projects where id=target for update; select * into draft from public.writing_drafts where project_id=target for update; if not found or draft.owner_id <> actor then raise exception 'Writing author required' using errcode='42501'; end if; if expected_revision is null or draft.revision <> expected_revision then raise exception 'Stale writing revision' using errcode='40001'; end if; update public.publications set is_public=false where project_id=target and snapshot->>'division'='wordrows' returning * into release; update public.writing_drafts set revision=revision+1,updated_at=now() where project_id=target; update public.projects set stage='make',is_public=false where id=target; return jsonb_build_object('projectId',target,'revision',draft.revision+1,'publicationSlug',release.slug); end; $$; revoke execute on function private.writing_text(jsonb,integer),private.writing_url(text,text),private.validate_writing(jsonb,boolean) from public,anon,authenticated; revoke execute on function public.save_writing_draft(jsonb,uuid,integer,boolean),public.unpublish_writing(uuid,integer) from public,anon,authenticated; grant execute on function public.save_writing_draft(jsonb,uuid,integer,boolean),public.unpublish_writing(uuid,integer) to authenticated;

-- A writing project must never be released through a generic artifact snapshot. -- This closes cross-workflow entry points without relaxing review requirements. create function private.guard_writing_publication() returns trigger language plpgsql security definer set search_path = '' as $$ begin if exists (select 1 from public.writing_drafts where project_id=new.project_id) then if new.snapshot->>'division' is distinct from 'wordrows' then raise exception 'Use the writing publication action for Wordrows' using errcode='22023'; end if; perform private.validate_writing(new.snapshot->'writing',true); end if; return new; end; $$; create trigger writing_publication_guard before insert or update on public.publications for each row execute function private.guard_writing_publication(); revoke execute on function private.guard_writing_publication() from public,anon,authenticated;

insert into supabase_migrations.schema_migrations(version,name,statements) values ('202609120001','wordrows_writing',array[$migration$-- Wordrows is an isolated editorial publishing path. Existing reviewed project -- publication functions remain unchanged. Draft content is never publicly read. create table public.writing_drafts ( project_id uuid primary key references public.projects(id) on delete cascade, owner_id uuid not null references public.profiles(user_id) on delete restrict, content jsonb not null, revision integer not null default 1 check (revision > 0), updated_at timestamptz not null default now() ); alter table public.writing_drafts enable row level security; create policy "Authors read their writing drafts" on public.writing_drafts for select to authenticated using (owner_id = auth.uid() and public.is_active_member()); revoke all on public.writing_drafts from public, anon, authenticated; grant select on public.writing_drafts to authenticated;

create function private.writing_text(value jsonb, maximum integer) returns boolean language sql immutable set search_path = '' as $$ select coalesce(jsonb_typeof(value) = 'string' and length(value #>> '{}') <= maximum, false) $$; create function private.writing_url(value text, kind text default 'image') returns boolean language plpgsql immutable set search_path = '' as $$ begin if value = '' then return true; end if; if value is null or length(value) > 2048 or value !~ '^https://[a-zA-Z0-9.-]+(:443)?(/[^[:space:]\\]*)?$' then return false; end if; if kind = 'model' then return value ~ '^https://my\.spline\.design/[a-zA-Z0-9_-]+/?$' or value ~ '^https://(www\.)?sketchfab\.com/models/[a-f0-9]{32}/embed/?$'; elsif kind = 'video' then return value ~ '^https://(www\.)?(youtube\.com|youtube-nocookie\.com)/embed/[a-zA-Z0-9_-]{11}$' or value ~ '^https://(www\.)?youtube\.com/watch\?v=[a-zA-Z0-9_-]{11}(&[^[:space:]]*)?$' or value ~ '^https://youtu\.be/[a-zA-Z0-9_-]{11}$' or value ~ '^https://(www\.)?vimeo\.com/[0-9]+/?$' or value ~ '^https://player\.vimeo\.com/video/[0-9]+/?$'; end if; return true; end; $$; create function private.validate_writing(document jsonb, ready boolean) returns void language plpgsql set search_path = '' as $$ declare chapter jsonb; panel jsonb; begin if document is null or jsonb_typeof(document) <> 'object' or (document->'version') is distinct from '1'::jsonb or coalesce(document->>'kind','') not in ('article','book','magazine','lyrics') or not private.writing_text(document->'title',160) or not private.writing_text(document->'summary',4000) or jsonb_typeof(document->'chapters') is distinct from 'array' or jsonb_typeof(document->'panels') is distinct from 'array' then raise exception 'Invalid writing content' using errcode='22023'; end if; if (select count(*) from jsonb_object_keys(document)) <> 6 then raise exception 'Unexpected writing fields' using errcode='22023'; end if; if jsonb_array_length(document->'chapters') <> 4 or jsonb_array_length(document->'panels') <> 2 or (ready and document->>'title' !~ '[^[:space:]]') then raise exception 'Writing requires a title, four chapters and two panels' using errcode='22023'; end if; for chapter in select value from jsonb_array_elements(document->'chapters') loop if jsonb_typeof(chapter) is distinct from 'object' then raise exception 'Invalid chapter' using errcode='22023'; end if; if (select count(*) from jsonb_object_keys(chapter)) <> 4 or not private.writing_text(chapter->'title',160) or not private.writing_text(chapter->'body',50000) or not private.writing_text(chapter->'imageUrl',2048) or not private.writing_text(chapter->'imageAlt',500) or not private.writing_url(chapter->>'imageUrl') or (ready and (chapter->>'title' !~ '[^[:space:]]' or chapter->>'body' !~ '[^[:space:]]')) then raise exception 'Each chapter needs valid text and an optional HTTPS image' using errcode='22023'; end if; end loop; for panel in select value from jsonb_array_elements(document->'panels') loop if jsonb_typeof(panel) is distinct from 'object' then raise exception 'Invalid supporting panel' using errcode='22023'; end if; if (select count(*) from jsonb_object_keys(panel)) <> 4 or coalesce(panel->>'kind','') not in ('model','image','video','sources') or not private.writing_text(panel->'title',160) or not private.writing_text(panel->'body',10000) or not private.writing_text(panel->'url',2048) or not private.writing_url(panel->>'url',panel->>'kind') then raise exception 'Invalid supporting panel or unsupported embed URL' using errcode='22023'; end if; end loop; end; $$;

create function public.save_writing_draft(document jsonb, target uuid default null, expected_revision integer default 0, publish_now boolean default false) returns jsonb language plpgsql security definer set search_path = '' as $$ declare actor uuid; draft public.writing_drafts; writing_project_id uuid; release public.publications; next_revision integer; begin actor := private.require_member(); if publish_now is null or expected_revision is null then raise exception 'Invalid writing request' using errcode='22023'; end if; if publish_now and not public.has_member_role(array['admin'::public.member_role,'editor'::public.member_role]) then raise exception 'Editor membership required to publish writing' using errcode='42501'; end if; perform private.validate_writing(document,publish_now); if target is null then if expected_revision <> 0 then raise exception 'Stale writing revision' using errcode='40001'; end if; writing_project_id := public.create_project(coalesce(nullif(btrim(document->>'title'),''),'Untitled article'),document->>'summary'); insert into public.writing_drafts(project_id,owner_id,content) values(writing_project_id,actor,document) returning * into draft; next_revision := 1; else -- Always lock the project first, matching the established workflow lock order. perform 1 from public.projects where id=target for update; select * into draft from public.writing_drafts where writing_drafts.project_id=target for update; if not found or draft.owner_id <> actor then raise exception 'Writing author required' using errcode='42501'; end if; if draft.revision <> expected_revision then raise exception 'Stale writing revision' using errcode='40001'; end if; writing_project_id := target; next_revision := draft.revision+1; update public.writing_drafts set content=document,revision=next_revision,updated_at=now() where writing_drafts.project_id=target; end if; -- Draft metadata stays private; published readers use publications.snapshot only. update public.projects set title=coalesce(nullif(btrim(document->>'title'),''),'Untitled article'),summary=document->>'summary',updated_at=now() where id=writing_project_id; select * into release from public.publications where publications.project_id=writing_project_id; if publish_now then if release.id is not null and release.snapshot->>'division' is distinct from 'wordrows' then raise exception 'Reviewed publications cannot be replaced by writing' using errcode='42501'; end if; insert into public.publications(project_id,slug,title,excerpt,published_by,snapshot,is_public) values(writing_project_id,'writing-'||writing_project_id::text,btrim(document->>'title'),document->>'summary',actor, jsonb_build_object('division','wordrows','writing',document,'revision',next_revision),true) on conflict on constraint publications_project_id_key do update set title=excluded.title,excerpt=excluded.excerpt, snapshot=excluded.snapshot,is_public=true,updated_at=now() returning * into release; insert into public.publication_credits(publication_id,user_id,credit_label) values(release.id,actor,'author') on conflict do nothing; update public.projects set stage='ship',published_at=coalesce(published_at,now()) where id=writing_project_id; end if; return jsonb_build_object('projectId',writing_project_id,'revision',next_revision,'publicationSlug',release.slug); end; $$; create function public.unpublish_writing(target uuid, expected_revision integer) returns jsonb language plpgsql security definer set search_path = '' as $$ declare actor uuid; draft public.writing_drafts; release public.publications; begin actor := private.require_member(); if not public.has_member_role(array['admin'::public.member_role,'editor'::public.member_role]) then raise exception 'Editor membership required to unpublish writing' using errcode='42501'; end if; perform 1 from public.projects where id=target for update; select * into draft from public.writing_drafts where project_id=target for update; if not found or draft.owner_id <> actor then raise exception 'Writing author required' using errcode='42501'; end if; if expected_revision is null or draft.revision <> expected_revision then raise exception 'Stale writing revision' using errcode='40001'; end if; update public.publications set is_public=false where project_id=target and snapshot->>'division'='wordrows' returning * into release; update public.writing_drafts set revision=revision+1,updated_at=now() where project_id=target; update public.projects set stage='make',is_public=false where id=target; return jsonb_build_object('projectId',target,'revision',draft.revision+1,'publicationSlug',release.slug); end; $$; revoke execute on function private.writing_text(jsonb,integer),private.writing_url(text,text),private.validate_writing(jsonb,boolean) from public,anon,authenticated; revoke execute on function public.save_writing_draft(jsonb,uuid,integer,boolean),public.unpublish_writing(uuid,integer) from public,anon,authenticated; grant execute on function public.save_writing_draft(jsonb,uuid,integer,boolean),public.unpublish_writing(uuid,integer) to authenticated;

-- A writing project must never be released through a generic artifact snapshot. -- This closes cross-workflow entry points without relaxing review requirements. create function private.guard_writing_publication() returns trigger language plpgsql security definer set search_path = '' as $$ begin if exists (select 1 from public.writing_drafts where project_id=new.project_id) then if new.snapshot->>'division' is distinct from 'wordrows' then raise exception 'Use the writing publication action for Wordrows' using errcode='22023'; end if; perform private.validate_writing(new.snapshot->'writing',true); end if; return new; end; $$; create trigger writing_publication_guard before insert or update on public.publications for each row execute function private.guard_writing_publication(); revoke execute on function private.guard_writing_publication() from public,anon,authenticated; $migration$]); commit;