# How to Track Auth Events in Supabase

- Tool: Supabase
- Difficulty: Intermediate
- Time required: 15-20 min
- Compatibility: Supabase (all plans), @supabase/supabase-js v2+
- Last updated: March 2026

## TL;DR

Track auth events in Supabase by creating a database trigger on auth.users that logs signups and profile changes to a custom audit table, and by listening to onAuthStateChange on the client for real-time event tracking. The trigger fires on every INSERT and UPDATE to auth.users, capturing the event type, user ID, and timestamp. The client-side listener captures SIGNED_IN, SIGNED_OUT, TOKEN_REFRESHED, and PASSWORD_RECOVERY events.

## Tracking Authentication Events with Database Triggers and Client-Side Listeners

Supabase provides two complementary approaches to auth event tracking. Server-side database triggers on auth.users capture every signup and user update at the database level, regardless of which client initiated the action. Client-side onAuthStateChange listeners capture login, logout, token refresh, and password recovery events in real time. This tutorial shows you how to combine both methods for complete auth event visibility, using a custom audit_log table to store the history.

## Before you start

- A Supabase project with authentication enabled
- Access to the SQL Editor in the Supabase Dashboard
- @supabase/supabase-js v2 installed in your frontend project
- Basic understanding of PostgreSQL triggers and functions

## Step-by-step guide

### 1. Create the audit_log table for storing auth events

Create a table that stores every auth event with a timestamp, user ID, event type, and metadata. Use a jsonb column for metadata so you can store flexible data like IP addresses, user agents, or provider information. Enable RLS and restrict access so only the service role can insert events (via the trigger function) and individual users can only read their own events.

```
create table public.audit_log (
  id bigint generated always as identity primary key,
  user_id uuid not null,
  event_type text not null,
  metadata jsonb default '{}',
  created_at timestamptz default now()
);

alter table public.audit_log enable row level security;

-- Users can read their own audit events
create policy "Users read own audit logs"
on public.audit_log for select to authenticated
using ((select auth.uid()) = user_id);

-- Create an index for fast lookups by user and time
create index idx_audit_log_user_id on public.audit_log (user_id, created_at desc);
```

**Expected result:** The audit_log table is created with RLS enabled. Users can only read their own audit events, and no one can insert directly via the API.

### 2. Create a trigger function to log auth.users changes

Write a PL/pgSQL function that fires on INSERT and UPDATE to the auth.users table. The function checks whether it is a new user (INSERT / signup) or an existing user update (UPDATE) and logs the appropriate event type. Use security definer so the function can write to the audit_log table regardless of RLS, and set search_path to empty string for security. The function captures the user's email, provider, and last sign-in time as metadata.

```
create or replace function public.log_auth_event()
returns trigger
language plpgsql
security definer set search_path = ''
as $$
declare
  event_name text;
  event_meta jsonb;
begin
  if TG_OP = 'INSERT' then
    event_name := 'USER_SIGNUP';
    event_meta := jsonb_build_object(
      'email', new.email,
      'provider', new.raw_app_meta_data ->> 'provider',
      'email_confirmed', new.email_confirmed_at is not null
    );
  elsif TG_OP = 'UPDATE' then
    if old.last_sign_in_at is distinct from new.last_sign_in_at then
      event_name := 'USER_SIGN_IN';
    elsif old.email_confirmed_at is null and new.email_confirmed_at is not null then
      event_name := 'EMAIL_CONFIRMED';
    elsif old.encrypted_password is distinct from new.encrypted_password then
      event_name := 'PASSWORD_CHANGED';
    else
      event_name := 'USER_UPDATED';
    end if;
    event_meta := jsonb_build_object(
      'email', new.email,
      'provider', new.raw_app_meta_data ->> 'provider',
      'last_sign_in', new.last_sign_in_at
    );
  end if;

  insert into public.audit_log (user_id, event_type, metadata)
  values (new.id, event_name, event_meta);

  return new;
end;
$$;
```

**Expected result:** The trigger function is created and can detect signup, sign-in, email confirmation, password change, and generic user update events.

### 3. Attach the trigger to the auth.users table

Create a trigger that fires after every INSERT and UPDATE on auth.users. Use AFTER trigger (not BEFORE) because you want to log the event after it has been committed, not risk blocking the auth operation if the logging fails. The trigger calls the log_auth_event function for each affected row.

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

**Expected result:** Every new signup and every sign-in, email confirmation, or password change automatically creates a row in the audit_log table.

### 4. Add client-side auth event tracking with onAuthStateChange

The database trigger captures server-side events, but client-side events like SIGNED_OUT and TOKEN_REFRESHED only happen in the browser. Use onAuthStateChange to listen for these events and optionally log them to the same audit_log table via an Edge Function or a direct insert. Set up the listener early in your application lifecycle, typically in your root component or layout.

```
import { createClient } from '@supabase/supabase-js'

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
)

// Listen for auth state changes
const { data: { subscription } } = supabase.auth.onAuthStateChange(
  async (event, session) => {
    console.log('Auth event:', event)

    // Track client-side events via Edge Function
    if (event === 'SIGNED_OUT' || event === 'TOKEN_REFRESHED') {
      await supabase.functions.invoke('log-auth-event', {
        body: {
          event_type: event,
          user_id: session?.user?.id ?? null,
          timestamp: new Date().toISOString()
        }
      })
    }
  }
)

// Clean up on unmount
// subscription.unsubscribe()
```

**Expected result:** Client-side events like SIGNED_OUT and TOKEN_REFRESHED are captured and sent to the backend for logging.

### 5. Query the audit log to analyze auth event history

Once events are being tracked, query the audit_log table to analyze user behavior. You can count signups per day, identify users who never confirmed their email, find accounts with frequent password changes, or detect suspicious sign-in patterns. Use the SQL Editor or the JS client for these queries.

```
-- Signups per day for the last 30 days
select
  date_trunc('day', created_at) as day,
  count(*) as signups
from public.audit_log
where event_type = 'USER_SIGNUP'
  and created_at > now() - interval '30 days'
group by day
order by day desc;

-- Users who signed up but never confirmed email
select user_id, metadata ->> 'email' as email, created_at
from public.audit_log
where event_type = 'USER_SIGNUP'
  and (metadata ->> 'email_confirmed')::boolean = false
  and user_id not in (
    select user_id from public.audit_log where event_type = 'EMAIL_CONFIRMED'
  );

-- Most recent events for a specific user (from JS client)
const { data } = await supabase
  .from('audit_log')
  .select('event_type, metadata, created_at')
  .order('created_at', { ascending: false })
  .limit(20)
```

**Expected result:** Queries return auth event analytics such as daily signup counts, unconfirmed users, and individual user event timelines.

## Complete code example

File: `auth-event-tracking.sql`

```sql
-- Audit log table
create table public.audit_log (
  id bigint generated always as identity primary key,
  user_id uuid not null,
  event_type text not null,
  metadata jsonb default '{}',
  created_at timestamptz default now()
);

alter table public.audit_log enable row level security;

create policy "Users read own audit logs"
on public.audit_log for select to authenticated
using ((select auth.uid()) = user_id);

create index idx_audit_log_user_id on public.audit_log (user_id, created_at desc);
create index idx_audit_log_event_type on public.audit_log (event_type, created_at desc);

-- Trigger function to log auth events
create or replace function public.log_auth_event()
returns trigger
language plpgsql
security definer set search_path = ''
as $$
declare
  event_name text;
  event_meta jsonb;
begin
  if TG_OP = 'INSERT' then
    event_name := 'USER_SIGNUP';
    event_meta := jsonb_build_object(
      'email', new.email,
      'provider', new.raw_app_meta_data ->> 'provider',
      'email_confirmed', new.email_confirmed_at is not null
    );
  elsif TG_OP = 'UPDATE' then
    if old.last_sign_in_at is distinct from new.last_sign_in_at then
      event_name := 'USER_SIGN_IN';
    elsif old.email_confirmed_at is null and new.email_confirmed_at is not null then
      event_name := 'EMAIL_CONFIRMED';
    elsif old.encrypted_password is distinct from new.encrypted_password then
      event_name := 'PASSWORD_CHANGED';
    else
      event_name := 'USER_UPDATED';
    end if;
    event_meta := jsonb_build_object(
      'email', new.email,
      'provider', new.raw_app_meta_data ->> 'provider',
      'last_sign_in', new.last_sign_in_at
    );
  end if;

  insert into public.audit_log (user_id, event_type, metadata)
  values (new.id, event_name, event_meta);

  return new;
end;
$$;

-- Attach trigger to auth.users
create trigger on_auth_event
  after insert or update on auth.users
  for each row execute function public.log_auth_event();

-- Optional: retention cleanup (requires pg_cron extension)
select cron.schedule(
  'cleanup-old-audit-logs',
  '0 3 * * 0',
  $$delete from public.audit_log where created_at < now() - interval '90 days'$$
);
```

## Common mistakes

- **Using a BEFORE trigger instead of AFTER, which can block the auth operation if the logging function fails** — undefined Fix: Use AFTER INSERT OR UPDATE triggers for audit logging. This ensures the auth operation succeeds even if the logging fails.
- **Adding an INSERT RLS policy for authenticated users on the audit_log table, allowing users to insert fake events** — undefined Fix: Do not add an INSERT policy for authenticated users. The trigger function uses security definer to write events, bypassing RLS. Only add a SELECT policy for users to read their own events.
- **Not setting search_path = '' on the security definer function, creating a potential search path exploit** — undefined Fix: Always add SET search_path = '' to any function declared with SECURITY DEFINER, and use fully qualified table names (public.audit_log, not just audit_log).

## Best practices

- Use database triggers for server-side events and onAuthStateChange for client-side events to get complete coverage
- Always use security definer with set search_path = '' for trigger functions that access the auth schema
- Add indexes on user_id and created_at for fast audit log queries
- Implement a retention policy to delete old audit log entries and prevent unbounded table growth
- Store flexible metadata in a jsonb column so you can extend the tracked data without schema changes
- Do not add INSERT RLS policies on the audit_log table — let only the trigger function write to it
- Use AFTER triggers for audit logging so the primary operation is not blocked by logging failures
- Clean up onAuthStateChange subscriptions when components unmount to prevent memory leaks

## Frequently asked questions

### Does the database trigger capture every login event?

Yes. Every successful sign-in updates the last_sign_in_at column on auth.users, which fires the UPDATE trigger. The trigger function detects this change and logs a USER_SIGN_IN event.

### Can I track failed login attempts with a database trigger?

No. Failed login attempts do not modify auth.users, so the trigger does not fire. To track failed logins, use client-side error handling and send the failure event to an Edge Function or external logging service.

### Will the trigger slow down the auth process?

The impact is minimal. The trigger performs a single INSERT into the audit_log table, which takes less than a millisecond. Using an AFTER trigger ensures the auth operation completes before logging starts.

### How do I prevent the audit_log table from growing too large?

Schedule a pg_cron job to delete old records. For example: select cron.schedule('cleanup', '0 3 * * 0', 'delete from public.audit_log where created_at < now() - interval 90 days'). This runs every Sunday at 3 AM.

### Can users see other users' audit events?

No. The RLS policy on audit_log restricts SELECT to rows where user_id matches auth.uid(). Each user can only see their own events.

### What auth events does onAuthStateChange capture?

It captures INITIAL_SESSION, SIGNED_IN, SIGNED_OUT, TOKEN_REFRESHED, USER_UPDATED, and PASSWORD_RECOVERY events. These are client-side only and complement the server-side trigger.

### Can RapidDev help me build a custom auth event tracking and analytics system?

Yes. RapidDev can design and implement a complete auth event tracking pipeline including database triggers, client-side listeners, analytics dashboards, and alerting for suspicious activity.

---

Source: https://www.rapidevelopers.com/supabase-tutorial/how-to-track-auth-events-in-supabase
© RapidDev — https://www.rapidevelopers.com/supabase-tutorial/how-to-track-auth-events-in-supabase