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

Users with the right permissions on supasheet.users see the Core → Users section. Unlike most tables, these permission values aren't something you add yourself — they ship pre-seeded in supabase/migrations/20250523000822_roles.sql, granted by default to the x-admin role only:

Note this is a separate check from the RLS policies on the table itself: supasheet.users only has SELECT/UPDATE policies scoped to auth.uid() = id (a user reading/updating their own row) — there's no RLS path for inserting or deleting other users' rows at all. Every admin capability above works only because it's routed through a service-role Edge Function, not a direct authenticated-role table write.

Admin operations are not run as the signed-in user — they go through Deno Edge Functions in supabase/functions/:

• 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

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

Assigning Roles and Permissions

Two more Core sections round out user administration — unlike the Edge-Function-backed pages above, these are ordinary permission-gated tables over supasheet.user_roles and supasheet.role_permissions, so they read/write directly as the signed-in x-admin user (no Edge Function involved):

These six are also pre-seeded to x-admin only in 20250523000822_roles.sql, alongside the supasheet.users:* permissions above. In practice this means an x-admin never has to write the INSERT INTO supasheet.user_roles / role_permissions statements shown in Authorization by hand — the same operations are available as a UI over these two Core sections. Reach for raw SQL when seeding roles/permissions from a migration; use the UI for day-to-day changes.

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