Community

Study groups, chat, presence and leaderboards. Every rule described here is enforced by row-level security inside your own database, not by the application — so it holds even for a direct API call.

Enrolment #

Community is opt-in. A row in community_enrollments carries your consent plus the settings that govern what others can see.

The row is created by a database trigger the moment your public.users row exists, so the community tab can never load against a missing enrolment record.

# am I enrolled?
curl -s -X POST "$SUPABASE_URL/rest/v1/rpc/community_is_enrolled" \
  -H "apikey: $SUPABASE_ANON_KEY" \
  -H "Authorization: Bearer $USER_JWT" \
  -H 'Content-Type: application/json' -d '{}'

Groups #

A group is a row in groups with 27 columns — identity, exam target, subject list, visibility, join policy and a denormalised member count.

Visibility and join policy #

Two independent axes. Visibility controls who can find the group; join policy controls who can enter it.

Roles #

Two roles are in use: owner and member. Permission checks go through a single helper that takes an array, so a policy can accept several roles in one call:

create or replace function public._has_group_role(
  gid uuid, uid uuid, allowed_roles text[]
) returns boolean language sql stable security definer
set search_path = public as $$
  select exists (
    select 1 from public.group_members gm
    where gm.group_id = gid
      and gm.user_id  = uid
      and gm.role     = any(allowed_roles)
  );
$$;

Creation is atomic #

Creating a group and becoming its owner are logically one operation. If they were two writes and the second failed, you would have a group nobody could administer. Four triggers on groups close that gap:

trg_sync_member_count on group_members maintains groups.member_count, so listing groups never needs a COUNT(*) per row.

Invites #

An invite is a token in group_invites with an optional use limit and expiry. Redemption is a three-step flow so a user can see what they are joining before committing:

1. Create

   community_create_invite(p_type, p_target_id, p_days) — owner only.

2. Preview

   community_preview_invite(p_token) returns the group name, member count and inviter without joining. Also exposed as get_invite_details.

3. Redeem

   community_redeem_invite(p_token) validates expiry and use count, then adds membership and increments uses_count.

Validity is computed, not stored, so an expired invite cannot be resurrected by editing a flag:

(gi.expires_at is null or gi.expires_at > now())
and (gi.max_uses is null or gi.uses_count < gi.max_uses)

Group chat #

Messages live in group_chat_messages, published to Supabase Realtime. Reads and writes are membership-gated by RLS, so a non-member cannot fetch history even with a valid token and the correct group id.

Presence #

Presence answers "who is studying right now". The client sends a heartbeat while a session runs; user_presence holds one row per user.

-- community_heartbeat upserts on user_presence(user_id)
insert into public.user_presence (
  user_id, status, last_seen, subject_id, subject_name,
  task_id, task_title, session_started_at
) values (auth.uid(), p_state, now(), …)
on conflict (user_id) do update set
  status     = excluded.status,
  last_seen  = excluded.last_seen,
  -- a mid-session heartbeat must not reset when the session began
  session_started_at = coalesce(user_presence.session_started_at,
                                excluded.session_started_at),
  updated_at = now();

That COALESCE is the important line. Without it every heartbeat would move session_started_at forward and elapsed time would never grow.

Stale presence #

A closed tab sends no goodbye. expire_stale_presence() reconciles that:

update public.user_presence set status = 'offline'
where status != 'offline'
  and last_seen < now() - interval '2 minutes';

So a user who disappears shows as offline within two minutes, rather than appearing to study forever.

Buddies #

Buddy connections are rows in community_friends (user_id, friend_id, status, accepted_at). Requests are made by handle, not email — you never need to know someone's address to add them.

Fixed in supabase/021_fix_buddy_handle_and_overview.sql: bootstrap now writes the column and the JSONB key, and request_buddy accepts either, plus users.username — so accounts that enrolled before the fix, or never enrolled at all, stay findable. Underneath both was a third defect: user_profiles had no rows at all, because the signup trigger never ran (see Triggers). A user with no user_profiles row cannot have a handle in either location.

What you see once a request is accepted

community_get_overview returns each buddy with the fields the UI renders:

Privacy is enforced server-side, from each buddy's own community_enrollments.privacy — not by the client choosing what to display. A client-side filter is not a privacy control: by the time the client filters, the data has already left the database. stealthMode collapses live status and subject; shareTasks, shareSubjectBreakdown, shareQuestionCounts, shareExactTime and shareCurrentSubject each gate their own field.

A withheld field returns null, not [] — the UI distinguishes them: null renders “not shared”, an empty array renders “no settled study time”. Returning [] for a withheld field would tell the reader their buddy did nothing today, which is a different and wrong statement.

Start alerts and quiet hours #

You can ask to be notified when a specific buddy or group starts studying. community_start_alerts stores the subscription together with a quiet-hours window and a timezone offset, so a 3 a.m. notification is suppressed rather than delivered.

Leaderboards #

Two scopes. Group leaderboards run in Postgres; the global leaderboard is assembled by the local server.

Moderation #

community_submit_report(p_target_type, p_target_id, p_reason) writes to community_reports. Targets can be a group, a message or a user. Reports are readable only by the reporter and by service-role callers, so one user cannot enumerate another's reports.

trg_auto_add_super_admin also places the platform super admin in every new group, which is what makes moderation possible without a privileged back door into private groups.

How the rules are enforced #

Group content policies never query group_members directly, because a policy on that table querying itself recurses and Postgres aborts. Every check goes through a SECURITY DEFINER helper:

-- membership-gated read on group content
using ( public._is_group_member(group_id, (select auth.uid())) )

-- owner-only write
using ( public._has_group_role(group_id, (select auth.uid()), array['owner']) )

(select auth.uid()) rather than a bare call is deliberate: the subquery form is evaluated once per statement instead of once per row.