How to Track Auth Events in Supabase

1create table public.audit_log (
2  id bigint generated always as identity primary key,
3  user_id uuid not null,
4  event_type text not null,
5  metadata jsonb default '{}',
6  created_at timestamptz default now()
7);
8
9alter table public.audit_log enable row level security;
10
11-- Users can read their own audit events
12create policy "Users read own audit logs"
13on public.audit_log for select to authenticated
14using ((select auth.uid()) = user_id);
15
16-- Create an index for fast lookups by user and time
17create index idx_audit_log_user_id on public.audit_log (user_id, created_at desc);

1create or replace function public.log_auth_event()
2returns trigger
3language plpgsql
4security definer set search_path = ''
5as $$
6declare
7  event_name text;
8  event_meta jsonb;
9begin
10  if TG_OP = 'INSERT' then
11    event_name := 'USER_SIGNUP';
12    event_meta := jsonb_build_object(
13      'email', new.email,
14      'provider', new.raw_app_meta_data ->> 'provider',
15      'email_confirmed', new.email_confirmed_at is not null
16    );
17  elsif TG_OP = 'UPDATE' then
18    if old.last_sign_in_at is distinct from new.last_sign_in_at then
19      event_name := 'USER_SIGN_IN';
20    elsif old.email_confirmed_at is null and new.email_confirmed_at is not null then
21      event_name := 'EMAIL_CONFIRMED';
22    elsif old.encrypted_password is distinct from new.encrypted_password then
23      event_name := 'PASSWORD_CHANGED';
24    else
25      event_name := 'USER_UPDATED';
26    end if;
27    event_meta := jsonb_build_object(
28      'email', new.email,
29      'provider', new.raw_app_meta_data ->> 'provider',
30      'last_sign_in', new.last_sign_in_at
31    );
32  end if;
33
34  insert into public.audit_log (user_id, event_type, metadata)
35  values (new.id, event_name, event_meta);
36
37  return new;
38end;
39$$;

1create trigger on_auth_event
2  after insert or update on auth.users
3  for each row execute function public.log_auth_event();

1import { createClient } from '@supabase/supabase-js'
2
3const supabase = createClient(
4  process.env.NEXT_PUBLIC_SUPABASE_URL!,
5  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
6)
7
8// Listen for auth state changes
9const { data: { subscription } } = supabase.auth.onAuthStateChange(
10  async (event, session) => {
11    console.log('Auth event:', event)
12
13    // Track client-side events via Edge Function
14    if (event === 'SIGNED_OUT' || event === 'TOKEN_REFRESHED') {
15      await supabase.functions.invoke('log-auth-event', {
16        body: {
17          event_type: event,
18          user_id: session?.user?.id ?? null,
19          timestamp: new Date().toISOString()
20        }
21      })
22    }
23  }
24)
25
26// Clean up on unmount
27// subscription.unsubscribe()

1-- Signups per day for the last 30 days
2select
3  date_trunc('day', created_at) as day,
4  count(*) as signups
5from public.audit_log
6where event_type = 'USER_SIGNUP'
7  and created_at > now() - interval '30 days'
8group by day
9order by day desc;
10
11-- Users who signed up but never confirmed email
12select user_id, metadata ->> 'email' as email, created_at
13from public.audit_log
14where event_type = 'USER_SIGNUP'
15  and (metadata ->> 'email_confirmed')::boolean = false
16  and user_id not in (
17    select user_id from public.audit_log where event_type = 'EMAIL_CONFIRMED'
18  );
19
20-- Most recent events for a specific user (from JS client)
21const { data } = await supabase
22  .from('audit_log')
23  .select('event_type, metadata, created_at')
24  .order('created_at', { ascending: false })
25  .limit(20)