Skip to content

Supabase Setup

Supabase serves two roles for the backoffice:

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

Authentication Flow

Email OTP Login

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 Access tables / RLS U->>FE: Enter email, click "Continue with email" FE->>SB: signInWithOtp(email, shouldCreateUser: true) alt User already in auth.users SB->>Mail: Send six-digit OTP Mail->>U: Deliver OTP else New user SB->>Hook: Validate non-empty email alt Email present Hook-->>SB: allow SB->>SB: Create auth account SB->>Mail: Send six-digit OTP Mail->>U: Deliver OTP else Email missing Hook-->>SB: reject (403) SB-->>FE: error end end FE->>U: Show OTP entry (always, prevents enumeration) U->>FE: Enter six-digit code FE->>SB: verifyOtp(email, token, type: email) SB-->>FE: Authenticated session FE->>DB: Load backoffice profile under RLS alt Existing member DB-->>FE: Authorized domains FE-->>U: Open backoffice else Self-serve user DB-->>FE: No backoffice profile FE-->>U: Open onboarding wizard end

Key Design Decisions

  • shouldCreateUser: true — supports both pre-provisioned members and new self-serve onboarding users
  • before_user_created hook — rejects malformed signup events without an email; it is not an authorization boundary
  • backoffice_users + RLS — an Auth account alone grants no client data; existing members receive their assigned domains and other users remain in onboarding
  • Error suppression — frontend always shows the OTP entry regardless of send success/failure, preventing email enumeration
  • Code-only template — the Magic Link / OTP template contains {{ .Token }} and no verification URL, so email security scanners cannot consume the token

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, email OTP, 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.
  • Magic Link email template: copy supabase/templates/email_otp.html into Supabase Dashboard → Authentication → Email Templates → Magic Link. Hosted projects do not read this template from supabase/config.toml.
  • 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 (email OTP 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 a new account is created. It requires a non-empty email, then allows account creation. Authorization remains in backoffice_users and RLS; unprovisioned users can only reach self-serve onboarding.

Migration

Created by 20260226163802_add_before_user_created_hook.sql and relaxed for self-serve onboarding by 20260702102821_onboarding_self_serve.sql:

  • 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. This keeps hosted Auth behavior aligned with the versioned self-serve signup validation. Client-data authorization does not depend on the hook.

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

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 with an email not in backoffice_users → OTP appears in Inbucket and verification routes to onboarding
  2. Try with admin@admin.com → OTP appears in Inbucket and verification opens the provisioned backoffice