46 lines
1.4 KiB
PL/PgSQL
46 lines
1.4 KiB
PL/PgSQL
-- Modifying the trigger to fire when email is confirmed, as per mandatory login requirements
|
|
CREATE OR REPLACE FUNCTION public.handle_new_user()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER SET search_path = public
|
|
AS $$
|
|
DECLARE
|
|
user_count int;
|
|
username_val text;
|
|
BEGIN
|
|
SELECT COUNT(*) INTO user_count FROM public.profiles;
|
|
|
|
-- Extract username from email or metadata
|
|
username_val := COALESCE(
|
|
NEW.raw_user_meta_data->>'username',
|
|
SPLIT_PART(NEW.email, '@', 1)
|
|
);
|
|
|
|
-- Insert profile synced with fields collected at signup.
|
|
-- Initial user becomes admin if roles were intended (based on user_role enum if it exists, otherwise we just insert)
|
|
-- For this specific app, we use a simple text role or separate roles table if needed.
|
|
-- Looking at existing migrations, profiles doesn't have a 'role' column yet in migration 1.
|
|
-- Let's check if 'user_role' type exists.
|
|
|
|
INSERT INTO public.profiles (id, username, full_name)
|
|
VALUES (
|
|
NEW.id,
|
|
username_val,
|
|
COALESCE(NEW.raw_user_meta_data->>'full_name', username_val)
|
|
);
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
-- Drop old trigger
|
|
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
|
|
|
|
-- Create new trigger with correct timing
|
|
DROP TRIGGER IF EXISTS on_auth_user_confirmed ON auth.users;
|
|
CREATE TRIGGER on_auth_user_confirmed
|
|
AFTER UPDATE ON auth.users
|
|
FOR EACH ROW
|
|
WHEN (OLD.confirmed_at IS NULL AND NEW.confirmed_at IS NOT NULL)
|
|
EXECUTE FUNCTION public.handle_new_user();
|