mike/backend/migrations/20260625_01_workflow_metadata.sql

-- Migration date: 2026-06-25

-- Custom workflow metadata fields and workflow overview read model. System
-- workflow versions remain generated from the repository metadata and are not
-- stored on user workflow rows.

drop function if exists public.get_workflows_overview(text, text, text);

alter table public.workflows
  drop column if exists author,
  drop column if exists category,
  drop column if exists is_system,
  add column if not exists language text default 'English',
  add column if not exists jurisdictions text[] default array['General']::text[];

alter table public.workflows
  alter column language set default 'English',
  alter column practice set default 'General Transactions',
  alter column jurisdictions set default array['General']::text[];

update public.workflows
set
  language = coalesce(nullif(trim(language), ''), 'English'),
  practice = coalesce(nullif(trim(practice), ''), 'General Transactions'),
  jurisdictions = coalesce(jurisdictions, array['General']::text[])
where user_id is not null;

create or replace function public.get_workflows_overview(
  p_user_id text,
  p_user_email text default null,
  p_type text default null
)
returns table (
  id uuid,
  user_id text,
  title text,
  type text,
  prompt_md text,
  columns_config jsonb,
  language text,
  practice text,
  jurisdictions text[],
  is_system boolean,
  created_at timestamptz,
  allow_edit boolean,
  is_owner boolean,
  shared_by_name text
)
language sql
stable
as $$
  with owned as (
    select
      w.id,
      w.user_id::text as user_id,
      w.title,
      w.type,
      w.prompt_md,
      w.columns_config,
      w.language,
      w.practice,
      w.jurisdictions,
      false as is_system,
      w.created_at,
      true as allow_edit,
      true as is_owner,
      null::text as shared_by_name,
      0 as sort_bucket
    from public.workflows w
    where w.user_id::text = p_user_id
      and (p_type is null or w.type = p_type)
  ),
  shared as (
    select
      w.id,
      w.user_id::text as user_id,
      w.title,
      w.type,
      w.prompt_md,
      w.columns_config,
      w.language,
      w.practice,
      w.jurisdictions,
      false as is_system,
      w.created_at,
      ws.allow_edit,
      false as is_owner,
      nullif(trim(up.display_name), '') as shared_by_name,
      1 as sort_bucket
    from public.workflow_shares ws
    join public.workflows w
      on w.id = ws.workflow_id
    left join public.user_profiles up
      on up.user_id::text = ws.shared_by_user_id::text
    where lower(ws.shared_with_email) = lower(coalesce(p_user_email, ''))
      and (p_type is null or w.type = p_type)
  ),
  visible_workflows as (
    select * from owned
    union all
    select * from shared
  )
  select
    vw.id,
    vw.user_id,
    vw.title,
    vw.type,
    vw.prompt_md,
    vw.columns_config,
    vw.language,
    vw.practice,
    vw.jurisdictions,
    vw.is_system,
    vw.created_at,
    vw.allow_edit,
    vw.is_owner,
    vw.shared_by_name
  from visible_workflows vw
  order by vw.sort_bucket asc, vw.created_at desc;
$$;