Functions, Triggers & Security

Functions, Triggers & Security

Stored functions and triggers move business logic into the database — guaranteeing it runs regardless of which client sends the query.

Stored Function

-- Award XP and update profile total in one atomic call
CREATE OR REPLACE FUNCTION complete_course(
  p_user_id UUID,
  p_course_id TEXT,
  p_xp INT,
  p_quiz_score INT DEFAULT NULL
)
RETURNS JSON
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
DECLARE
  v_existing progress;
BEGIN
  -- Prevent duplicate completion
  SELECT * INTO v_existing
  FROM progress
  WHERE user_id = p_user_id AND course_id = p_course_id;

  IF FOUND THEN
    RETURN json_build_object('success', false, 'reason', 'already_completed');
  END IF;

  INSERT INTO progress (user_id, course_id, xp_earned, quiz_score)
  VALUES (p_user_id, p_course_id, p_xp, p_quiz_score);

  UPDATE profiles
  SET total_xp = total_xp + p_xp
  WHERE id = p_user_id;

  RETURN json_build_object('success', true, 'xp_earned', p_xp);
END;
$$;

-- Call from Supabase client
-- const { data } = await supabase.rpc('complete_course', { p_user_id, p_course_id, p_xp: 150 })

Trigger

-- Auto-create a profile when a user signs up
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER  -- run as owner, bypasses RLS
SET search_path = public
AS $$
BEGIN
  INSERT INTO public.profiles (id, email, display_name)
  VALUES (NEW.id, NEW.email, SPLIT_PART(NEW.email, '@', 1));
  RETURN NEW;
END;
$$;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION handle_new_user();

Roles and Grants (Supabase context)

-- anon: unauthenticated users
-- authenticated: signed-in users

GRANT SELECT ON courses TO anon;
GRANT SELECT, INSERT, UPDATE ON progress TO authenticated;
REVOKE ALL ON progress FROM anon;