is it possible to set a database trigger to run a ...
# sql
k
is it possible to set a database trigger to run a function when a user first verifies (using phone OTP or magic link)? I need to update some other values once users are verified
g
You should be able to do an Update Trigger on auth.users and have your trigger function compare old.confirmed_at and new.confirmed_at are different and then do your stuff. You should verify that confirmed_at is correct as I'm just guessing looking at the table, but pretty sure.
k
it seems like my users are being auto-confirmed so the date doesn't change on first sign in
g
I've got a test user which has not confirmed the email sent (bad address) and confirmed_at is still null.
Hmmm. You are using magic link, so a bit different. I guess.
k
so I might have found it for email I wasn't requiring email confirmation so it maybe was auto-setting it
but for phone I was requiring it and I think I had seen the confirmed_at unchanged
will recheck both again to be sure
g
Also check some of the phone fields, certainly something changes, that you can use in your update function.
k
yeah the confirmed at seemed to be due to not requiring confirmation, seems like I can check against that
thank you!
so I was able to setup this trigger, but now I can't create new users (getting a database error on updating one of my tables, im guessing because of the new trigger function)
g
Could be RLS or coding bug. Is function “security definer”. Do you have an insert trigger function to create public user table like Supabase shows in guides? Mainly cause that would have been similar.
k
yeah I have a trigger to create the user after insert, so I basically just copied that function but altered it (it is
security definer
)
g
The database log might give you more info on what is going wrong in you function
k
yeah I was able to get the user created from that log
now just gotta figure out why the function isnt actually doing what it should haha
man it doesn't seem to be consistent with whether it changes the confirmed_at for phones at least
frustrating haha sometimes it goes from null to a time when I first verify with OTP, sometimes it stays null
g
Sorry, I’ve not seen any other discussions that help with this, but could be somewhere. One could dig thru supabase/gotrue to see exact stuff it updates, but not trivial.
k
do you know if there's a way to view logs of the functions? I can't seem to get anything from
function_logs
in the logs query editor and postrges_logs doesn't seem to have them either
still can't get my trigger/function to fire/work (or atleast I can't find logs for them
g
I saw someone getting log messages out of functions, but can't find it. The database log should show any database errors. I normally debug my functions as sql in the sql editor as best I can. You are also welcome to show your function, maybe will see something.
k
Copy code
create or replace function public.activate_confirmed_user()
returns trigger as $$
begin
  if (old.confirmed_at != new.confirmed_at) then
  --update user to active
    update public.users u
    set activated = true
    where u.id = new.id;
  --update user tryggers to active
    update public.tryggers 
    set active = true
    where user_id = new.id;
    commit;
  end if;
  return new;
end;
$$ language plpgsql security definer;

create trigger on_auth_user_confirmed
  after update on auth.users
  for each row execute procedure public.activate_confirmed_user();
It seems fairly straightforward, but doesn't seem to fire (or I don't see any evidence of it firing in the logs I can find)
but also again running into inconsistent confirmed_at updating on first log-in
g
You can add
raise log 'I am here'
to see if you get in if, or at beginning to see function run.
t
is
old.confirmed_at
null? If so a hint to use
is distinct from
instead to compare
Copy code
sql
select null != 'new confirmed at', null is distinct from 'new confirmed at'