acmcc/supabase/migrations/20260423222738_f071a711-e68a-4c75-af0c-134d7e605c9f.sql

-- Trigger function: call notify-staff-new-form edge function via pg_net
CREATE OR REPLACE FUNCTION public.notify_staff_on_form_inbox()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path TO 'public'
AS $$
DECLARE
  v_url text;
  v_service_key text;
BEGIN
  -- Read project URL + service role key from vault if available, otherwise fall back to current_setting
  BEGIN
    SELECT decrypted_secret INTO v_service_key
    FROM vault.decrypted_secrets
    WHERE name = 'email_queue_service_role_key'
    LIMIT 1;
  EXCEPTION WHEN OTHERS THEN
    v_service_key := NULL;
  END;

  v_url := 'https://uxatwcforzoyuejvbdyw.supabase.co/functions/v1/notify-staff-new-form';

  IF v_service_key IS NULL THEN
    RAISE WARNING 'notify_staff_on_form_inbox: no service role key in vault, skipping HTTP call for inbox %', NEW.id;
    RETURN NEW;
  END IF;

  PERFORM net.http_post(
    url := v_url,
    headers := jsonb_build_object(
      'Content-Type', 'application/json',
      'Authorization', 'Bearer ' || v_service_key
    ),
    body := jsonb_build_object('inbox_id', NEW.id)
  );

  RETURN NEW;
EXCEPTION WHEN OTHERS THEN
  RAISE WARNING 'notify_staff_on_form_inbox failed for inbox %: %', NEW.id, SQLERRM;
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_notify_staff_on_form_inbox ON public.form_inbox;
CREATE TRIGGER trg_notify_staff_on_form_inbox
AFTER INSERT ON public.form_inbox
FOR EACH ROW
EXECUTE FUNCTION public.notify_staff_on_form_inbox();