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 Mail as Cloudflare Email SMTP 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->>Mail: Send magic link email Mail->>U: Deliver magic link 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->>SB: Create auth account SB->>Mail: Send magic link email Mail->>U: Deliver magic link 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

Auth Email Delivery

Hosted Supabase Auth sends Rose authentication emails through the Cloudflare Email SMTP service configured in Supabase Dashboard → Authentication → SMTP. This replaces Supabase's built-in email service, which is capped at 2 emails/hour and is only intended for project setup/testing.

Supabase Auth remains the source of truth for generating signup, magic-link, invite, email-change, and password-reset tokens. Cloudflare is only the delivery provider: Supabase connects to the Cloudflare SMTP endpoint and sends the rendered Auth email to the user.

Required hosted settings:

  • Custom SMTP: enabled, using the Cloudflare Email SMTP credentials.
  • Auth email rate limit: 30 emails/hour in Supabase Dashboard → Authentication → Rate Limits (rate_limit_email_sent = 30 via the Management API).
  • Secrets: SMTP password/token lives only in Supabase project settings or secret storage; never commit it to this repo.

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

For the full seeding workflow (synthetic data, copy-from-prod, materialized view refresh) see Supabase Seeding. For per-branch isolated DBs see Supabase Preview Branches.

Seed Users

supabase/seed.sql creates two users after supabase db reset or just seed-branch:

Email Password Role
admin@admin.com admin Admin (all domains)
user@user.com user Regular user (sees domains granted via backoffice_user_domains)

Email Testing

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

Local Docker Supabase does not use the hosted Cloudflare SMTP service unless [auth.email.smtp] is explicitly configured in supabase/config.toml. Keep local development on Inbucket for normal testing; use the hosted project or a preview branch when you need to verify Cloudflare SMTP delivery end-to-end.

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