mirror of
https://github.com/pacnpal/thrilltrack-explorer.git
synced 2025-12-20 07:11:12 -05:00
79 lines
2.9 KiB
PL/PgSQL
79 lines
2.9 KiB
PL/PgSQL
-- Create function to sync moderator role changes to Novu topics
|
|
CREATE OR REPLACE FUNCTION public.sync_moderator_novu_topic()
|
|
RETURNS TRIGGER
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path = public
|
|
AS $$
|
|
DECLARE
|
|
function_url text;
|
|
anon_key text;
|
|
action_type text;
|
|
BEGIN
|
|
-- Only process moderator, admin, and superuser roles
|
|
IF (TG_OP = 'INSERT' AND NEW.role IN ('moderator', 'admin', 'superuser')) OR
|
|
(TG_OP = 'DELETE' AND OLD.role IN ('moderator', 'admin', 'superuser')) THEN
|
|
|
|
-- Build the function URL
|
|
function_url := 'https://ydvtmnrszybqnbcqbdcy.supabase.co/functions/v1/manage-moderator-topic';
|
|
|
|
-- Use the public anon key
|
|
anon_key := 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6InlkdnRtbnJzenlicW5iY3FiZGN5Iiwicm9sZSI6ImFub24iLCJpYXQiOjE3NTgzMjYzNTYsImV4cCI6MjA3MzkwMjM1Nn0.DM3oyapd_omP5ZzIlrT0H9qBsiQBxBRgw2tYuqgXKX4';
|
|
|
|
-- Determine action type
|
|
action_type := CASE
|
|
WHEN TG_OP = 'INSERT' THEN 'add'
|
|
WHEN TG_OP = 'DELETE' THEN 'remove'
|
|
END;
|
|
|
|
-- Call edge function asynchronously to add/remove from Novu topics
|
|
PERFORM net.http_post(
|
|
url := function_url,
|
|
headers := jsonb_build_object(
|
|
'Content-Type', 'application/json',
|
|
'Authorization', 'Bearer ' || anon_key,
|
|
'apikey', anon_key
|
|
),
|
|
body := jsonb_build_object(
|
|
'userId', CASE WHEN TG_OP = 'INSERT' THEN NEW.user_id ELSE OLD.user_id END,
|
|
'action', action_type
|
|
)
|
|
);
|
|
|
|
RAISE LOG 'Triggered Novu topic sync for user % with action %',
|
|
CASE WHEN TG_OP = 'INSERT' THEN NEW.user_id ELSE OLD.user_id END,
|
|
action_type;
|
|
END IF;
|
|
|
|
RETURN COALESCE(NEW, OLD);
|
|
EXCEPTION
|
|
WHEN OTHERS THEN
|
|
-- Log error but don't fail the role change
|
|
RAISE WARNING 'Failed to sync moderator to Novu topic: %', SQLERRM;
|
|
RETURN COALESCE(NEW, OLD);
|
|
END;
|
|
$$;
|
|
|
|
-- Create trigger for role insertions
|
|
DROP TRIGGER IF EXISTS sync_moderator_novu_topic_on_insert ON public.user_roles;
|
|
CREATE TRIGGER sync_moderator_novu_topic_on_insert
|
|
AFTER INSERT ON public.user_roles
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION public.sync_moderator_novu_topic();
|
|
|
|
-- Create trigger for role deletions
|
|
DROP TRIGGER IF EXISTS sync_moderator_novu_topic_on_delete ON public.user_roles;
|
|
CREATE TRIGGER sync_moderator_novu_topic_on_delete
|
|
AFTER DELETE ON public.user_roles
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION public.sync_moderator_novu_topic();
|
|
|
|
-- Add comments
|
|
COMMENT ON FUNCTION public.sync_moderator_novu_topic() IS
|
|
'Automatically adds or removes users from Novu moderation topics when moderator-level roles are granted or revoked';
|
|
|
|
COMMENT ON TRIGGER sync_moderator_novu_topic_on_insert ON public.user_roles IS
|
|
'Adds users to Novu moderation topics when they receive moderator, admin, or superuser roles';
|
|
|
|
COMMENT ON TRIGGER sync_moderator_novu_topic_on_delete ON public.user_roles IS
|
|
'Removes users from Novu moderation topics when their moderator, admin, or superuser roles are revoked'; |