Supabase :

users

				
					create table public.users (
  id uuid not null,
  created_at timestamp with time zone not null default now(),
  name text null,
  online boolean null default false,
  last_seen_at timestamp with time zone null,
  constraint users_pkey primary key (id),
  constraint users_id_fkey foreign KEY (id) references auth.users (id) on delete CASCADE
) TABLESPACE pg_default;

active_subscriptions

				
					create view public.active_subscriptions as
select
  subscription.id,
  subscription.entity,
  (subscription.claims ->> 'sub'::text)::uuid as user_id,
  subscription.created_at
from
  realtime.subscription
where
  subscription.entity::text = 'users'::text;

Function & Triggers

				
					create or replace function public.handle_user_connection_change ()
returns trigger
language plpgsql
security definer
as $$
declare
  v_user_id uuid;
  sub_exists boolean;
begin
  if (tg_op = 'DELETE') then
    v_user_id := (old.claims ->> 'sub')::uuid;
  else
    v_user_id := (new.claims ->> 'sub')::uuid;
  end if;

  if v_user_id is not null then
    select exists (
      select 1
      from public.active_subscriptions
      where user_id = v_user_id
    )
    into sub_exists;

    insert into public.users (id, online, last_seen_at)
    values (v_user_id, sub_exists, now())
    on conflict (id)
    do update set
      online = excluded.online,
      last_seen_at = excluded.last_seen_at;
  end if;

  return null;
end;
$$;


create trigger on_active_subscription_change
after INSERT
or DELETE
or
update on realtime.subscription for EACH row
execute FUNCTION handle_user_connection_change ();