Custom trigger on auth.identities

-- 2. Create the trigger function
CREATE OR REPLACE FUNCTION private.handle_sso_user_company_link()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''
AS $$
DECLARE
  v_company_id bigint;
  v_sso_provider_id uuid;
BEGIN
  -- Only process SSO identities (provider format: 'sso:<uuid>')
  IF NEW.provider LIKE 'sso:%' THEN
    -- Extract the provider UUID from 'sso:<uuid>'
    v_sso_provider_id := substring(NEW.provider FROM 5)::uuid;
    
    -- Find the matching company via company_settings
    SELECT cs.company_id INTO v_company_id
    FROM private.company_settings cs
    WHERE cs.sso_provider_id = v_sso_provider_id;
    
    -- Link user to company if found
    IF v_company_id IS NOT NULL THEN
      -- Insert into company_users (UNIQUE constraint on user_id ensures one company per user)
      INSERT INTO private.company_users (company_id, user_id)
      VALUES (v_company_id, NEW.user_id)
      ON CONFLICT (user_id) DO NOTHING;
      
      -- Set user role to 'tenant' in app_metadata (no ui_permissions by default)
      UPDATE auth.users
      SET raw_app_meta_data = 
        COALESCE(raw_app_meta_data, '{}'::jsonb) || 
        jsonb_build_object('role', 'tenant')
      WHERE id = NEW.user_id;
    END IF;
  END IF;
  
  RETURN NEW;
END;
$$;


-- 3. Create the trigger on auth.identities
CREATE TRIGGER on_sso_identity_created
  AFTER INSERT ON auth.identities
  FOR EACH ROW
  EXECUTE FUNCTION private.handle_sso_user_company_link();