Launch your community platform with Supabase and Once UI in 15 minutes

CREATE OR REPLACE FUNCTION hub_new_follower_notify()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
  v_follower_username text;
  v_follower_slug text;
  v_notification_id uuid;
BEGIN
  SELECT p.username, p.slug INTO v_follower_username, v_follower_slug
  FROM profiles p WHERE p.id = NEW.follower_id;

  INSERT INTO hub_notifications(type, actor_id, resource_type, resource_id, context)
  VALUES (
    'new_follower',
    NEW.follower_id,
    'profile',
    NEW.following_id,
    jsonb_build_object(
      'follower_id', NEW.follower_id,
      'follower_username', v_follower_username,
      'follower_slug', v_follower_slug
    )
  ) RETURNING id INTO v_notification_id;

  INSERT INTO hub_notification_recipients(notification_id, user_id, delivered_at)
  SELECT v_notification_id, NEW.following_id, now()
  FROM (
    SELECT coalesce(us.allow_in_app, true) AS allow_in_app
    FROM hub_user_notification_settings us