User Management

Overview

Supasheet provides a complete user management system on top of Supabase Auth. Sign-up creates a profile row automatically, profile updates flow between auth.users and supasheet.users, and admin operations (create / invite / update / delete) are wired through dedicated Edge Functions that call the Supabase Admin API.

The supasheet.users Table

Every user in Supasheet has a profile row in supasheet.users. This is the canonical user table — always reference it instead of auth.users from your application tables.

Columns (defined in supabase/migrations/20250523000814_users.sql):

Automatic Profile Creation

Two database triggers keep supasheet.users in sync with Supabase Auth:

• on_auth_user_created — fires after INSERT on auth.users and creates the matching supasheet.users row. name comes from raw_user_meta_data->>'name' (populated by OAuth providers) when present, falling back to the email local-part, or an empty string if there's no email either. picture_url comes from raw_user_meta_data->>'avatar_url' when present — there's no fallback for it, it's simply left null otherwise.
• on_auth_user_updated — propagates email changes from auth.users to supasheet.users.

The trigger function protect_user_fields() blocks direct updates to id and email, ensuring those fields stay authoritative on the auth side.

Referencing Users in Your Tables

Always foreign-key to supasheet.users(id):

CREATE TABLE tasks (
    id      UUID PRIMARY KEY DEFAULT extensions.uuid_generate_v4(),
    title   TEXT NOT NULL,
    user_id UUID REFERENCES supasheet.users(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT now()
);

In RLS policies compare user_id against auth.uid() directly:

create policy tasks_select on tasks
    for select to authenticated
    using (user_id = auth.uid());

Account UI

Each signed-in user sees /account in the sidebar, with the following pages:

The user dropdown in the header also exposes notifications and theme switching.

Admin User Management

The Core → Users section is visible only to the x-admin role — every tab (Overview, Edit, Security, Danger Zone) individually gates itself with useHasRole("x-admin") on the client and the route's beforeLoad guard throws a notFound() for anyone else. There's no per-capability grant to configure here (no supasheet.users:invite, :ban, :generate_link, and so on) — admin user management is entirely x-admin-or-nothing:

Note this is a separate check from the RLS policies on the table itself: supasheet.users only has policies scoped to pg_has_role(current_user, 'x-admin', 'member') for select/insert/update/delete (plus every user can read/update their own row via the JOIN edge in auth.uid() = id-style checks) — there's no RLS path for a non-x-admin role to insert or delete other users' rows at all. Every admin capability above works only because it's routed through a service-role Edge Function that independently re-checks x-admin membership server-side, not because the client happens to hold a table grant.

Admin operations are not run as the signed-in user — they go through Deno Edge Functions in supabase/functions/, each gated by requireRole(authHeader, "x-admin") (a call to supasheet.has_role('x-admin') over the caller's own token, independent of and in addition to the client-side UI check):

• admin-create-user — Create a user with email + password
• admin-invite-user — Send a magic invite link
• admin-list-users / admin-get-user — Listing and detail
• admin-update-user — Edit profile / email
• admin-delete-user — Tear down user account and auth row
• admin-generate-link — Generate password recovery / email confirmation links
• admin-set-user-role — Assign a native Postgres role to a user (see below)

These functions use the service role key and run server-side, so the publishable key alone cannot escalate privileges.

Assigning Roles

There's no separate Core section for role assignment or for permissions — both supasheet.user_roles and supasheet.role_permissions are gone. A user has exactly one role, stored on auth.users.raw_app_meta_data->>'role', and it's changed from the same Security tab used for ban/generate-link, via a role picker that calls admin-set-user-role:

// supabase/functions/admin-set-user-role/index.ts
const KNOWN_ROLES = ["x-admin", "admin", "user"] as const
// ...
const denied = await requireRole(req.headers.get("Authorization"), "x-admin")
if (denied) return denied

await adminClient.auth.admin.updateUserById(userId, { app_metadata: { role } })

A user can never change their own role through this path — the function explicitly rejects callerId === userId. Assigning a role fires a notification to that user (trg_user_role_changed_notify, a trigger on auth.users watching for raw_app_meta_data changes).

Granting what a role can do, on the other hand, is a pure SQL/migration concern now — there's no runtime UI for it, because there's no permissions table left to edit. Add or change a role's access with GRANT/REVOKE in a migration, exactly as shown in Authorization.

Profile Pictures

Profile pictures live in the uploads storage bucket, under the auth/<uid>/ path — there is no dedicated avatar bucket. A storage policy scoped to that path lets a user read and write only their own auth/<uid>/... folder, bypassing the normal schema.table:action permission check that governs the rest of uploads. See Storage for the full bucket/permission model.

Custom User Data

The public_data JSONB column is yours to use. Common patterns:

-- Update preferences
UPDATE supasheet.users
SET public_data = public_data || jsonb_build_object('theme', 'dark', 'locale', 'en-GB')
WHERE id = auth.uid();

It is also a great place to attach domain-specific user attributes (e.g. employee ID, department) without altering the schema.

Next Steps

• Authorization — Three roles, permissions, and RLS patterns
• Notifications — Deliver notifications to specific users or roles
• Audit Logs — Track every change made by every user