Skip to content

Supabase Setup

Supabase serves two roles for the backoffice:

  1. Auth provider — magic link and Google OAuth for client-backoffice
  2. PostgreSQL host — analytics data, config tables, RLS-secured access

Authentication Flow

sequenceDiagram participant U as User participant FE as Frontend participant SB as Supabase Auth participant Hook as before_user_created hook participant DB as backoffice_users U->>FE: Enter email, click "Send Magic Link" FE->>SB: signInWithOtp(email, shouldCreateUser: true) alt User already in auth.users SB->>U: Send magic link email else New user SB->>Hook: Check before creating account Hook->>DB: SELECT FROM backoffice_users WHERE email = ? alt Email found and active DB-->>Hook: exists Hook-->>SB: allow (empty JSON) SB->>U: Create account + send magic link email else Email not found DB-->>Hook: not found Hook-->>SB: reject (403) SB-->>FE: error end end FE->>U: "Check your inbox" (always, prevents enumeration)

Key Design Decisions

  • shouldCreateUser: true — required so pre-provisioned users (added to backoffice_users before their first login) can get their first magic link
  • before_user_created hook — gates account creation to emails in backoffice_users only, preventing transactional email waste
  • Error suppression — frontend always shows "check your inbox" regardless of success/failure, preventing email enumeration

Google OAuth

Google sign-in is also available. After OAuth, the link_current_user_to_backoffice RPC links auth.users.id to backoffice_users.user_id.

Access Control

User Provisioning Flow

  1. Admin creates entry in backoffice_users (email, display_name, is_admin)
  2. Admin assigns domains via backoffice_user_domains
  3. User visits backoffice and logs in (magic link or Google)
  4. On first login, auth account is created (hook allows it) and linked to backoffice_users
  5. RLS policies scope all data queries to the user's assigned domains

RLS Architecture

Table Policy Scope
conversations has_domain_access(site_domain) User's assigned domains
visitors has_domain_access(site_domain) User's assigned domains
accounts has_domain_access(site_domain) User's assigned domains
messages Via conversation's site_domain User's assigned domains
backoffice_users Own record or admin Self + admin
backoffice_user_domains Own assignments or admin Self + admin

Admins (is_admin = true) bypass domain scoping and see all data.

before_user_created Hook

What It Does

A PostgreSQL function (public.before_user_created_hook) that runs inside Supabase's auth flow before any new account is created. It checks if the email exists in backoffice_users with is_active = TRUE. If not, it returns a 403 and no email is sent.

Migration

Created by 20260226163802_add_before_user_created_hook.sql:

  • Grants supabase_auth_admin SELECT on backoffice_users
  • Creates the hook function
  • Restricts EXECUTE to supabase_auth_admin only

Production Setup

Required: Enable hook in Supabase dashboard

The migration deploys the function, but the hook must also be enabled in the dashboard. Without this, shouldCreateUser: true allows any email to create an account and receive a magic link.

Steps:

  1. Go to Supabase Dashboard → Authentication → Hooks
  2. Enable Before User Created
  3. Set type: Postgres function
  4. Set schema: public
  5. Set function: before_user_created_hook
  6. Save

Stale User Cleanup

If the hook was not enabled while shouldCreateUser: true was deployed, unauthorized accounts may exist in auth.users. These bypass the hook on future logins (it only fires on user creation).

Preview stale users:

SELECT au.id, au.email, au.created_at
FROM auth.users au
WHERE NOT EXISTS (
    SELECT 1 FROM public.backoffice_users bu
    WHERE bu.email = au.email
    AND bu.is_active = TRUE
);

Delete after review:

DELETE FROM auth.users au
WHERE NOT EXISTS (
    SELECT 1 FROM public.backoffice_users bu
    WHERE bu.email = au.email
    AND bu.is_active = TRUE
);

Local Development

Seed Users

supabase/seed.sql creates two users after supabase db reset:

Email Password Role
admin@admin.com admin Admin (all domains)
user@user.com user Regular user

Email Testing

Local emails are captured by Inbucket (not actually sent): http://localhost:54324

Hook Configuration

The hook is enabled locally via supabase/config.toml:

[auth.hook.before_user_created]
enabled = true
uri = "pg-functions://postgres/public/before_user_created_hook"

This matches production behavior. To test the hook locally:

  1. Try logging in with an email NOT in backoffice_users → no email in Inbucket
  2. Try with admin@admin.com → magic link appears in Inbucket