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

Security invariants

Public self-serve signup means the authenticated role is any stranger with a JWT, not a vetted user. Hold these whenever you touch RLS / auth:

  • Use is_super_admin() for global staff / super-admin bypass. It is the clear alias for legacy is_backoffice_admin(), which checks backoffice_users.is_admin; neither name means customer account admin. Account-admin access flows through has_domain_access(...), has_domain_data_access(...), and my_admin_client_ids().
  • No USING (true) for authenticated on a tenant table. Scope every read policy to has_domain_access(...) / has_domain_data_access(...) / is_super_admin() (global staff / super admin only). Enforced by just lint-migrations and the Supabase Migration Lint CI step (.github/workflows/pr-checks-supabase.yml); a genuinely-public table needs -- lint-allow: authenticated-using-true: <reason> on the CREATE POLICY.
  • Every SECURITY DEFINER function in a PostgREST-exposed schema must self-authorize — is_super_admin() (global staff / super admin) / has_domain_access(p_domain) at the top — or be in the explicit anon/widget allowlist. The IX-3610 grant loop grants authenticated EXECUTE to all of them, so a missing guard is a cross-tenant hole (that is exactly how get_account_for_outreach_agent leaked account data + chat transcripts). The supabase-security skill (run per-migration, above) catches these.
  • Revoke anon from every new function, by name. Supabase's default privileges grant EXECUTE directly to anon on functions created in public, so a new SECURITY DEFINER function is callable with the public anon key the moment it exists — and REVOKE ... FROM PUBLIC does not undo a direct grant. Write REVOKE ALL ON FUNCTION <name>(<args>) FROM PUBLIC, anon, authenticated; naming the function, then grant back only what genuinely needs it. A DO-loop revoke that filters on a predicate does not count unless it names the function: one looked correct in 20260828061302 and still shipped sync_site_for_domain anon-executable to production (IX-4522, after IX-3610 and IX-4443). Enforced by just lint-migrations, the CI job, and the .claude/hooks/check.sh edit hook; a genuinely anon-facing RPC needs -- lint-allow: definer-anon-execute: <reason>. 20260828154547 completes the IX-3610 guard by narrowing the default privilege itself (IX-4532). Note the shape: ALTER DEFAULT PRIVILEGES ... IN SCHEMA x REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC is a no-op — a schema-scoped entry merges with Postgres's built-in default, whose function default is EXECUTE to PUBLIC, and every role inherits PUBLIC. Only the schema-less ALTER DEFAULT PRIVILEGES FOR ROLE postgres REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC drops it; the scoped form is still needed for the per-role anon / authenticated grants Supabase adds. That is why IX-3610's guard read as correct for a year and changed nothing. The lint remains the only protection for functions created by supabase_admin, whose default ACL postgres cannot alter. Confirmed on a preview branch after 20260828154547: pg_default_acl holds postgres / global = {postgres=X/postgres} (PUBLIC gone) and postgres / public = {postgres=X,service_role=X}, and a function created by postgres in any schema comes out with no anon, no authenticated and no PUBLIC. The supabase_admin / public row still reads {postgres=X,anon=X,authenticated=X,service_role=X} — that is the hole the lint covers. Two further lint rules follow from the defaults granting nothing: a migration that DROPs one of the three widget RPCs (get_widget_config, get_active_staff_experiment, get_active_domain_experiment) must restate GRANT EXECUTE ... TO anon in the same file or the widget breaks for every visitor, and a migration that creates a non-trigger function in an exposed schema must state its EXECUTE grants — GRANT EXECUTE ON FUNCTION public.my_rpc() TO authenticated; (or anon / service_role) — or carry -- lint-allow: no-execute-grant: <reason> when service_role-only is the intent. Infer the role from the caller (backoffice UI → authenticated, widget → anon, job/worker → service_role); when the caller is unclear, ask rather than guessing — a wrong grant is either a 403 or a cross-tenant leak.
  • Resolve access by exact domain or registered alias. Access decisions resolve the exact domain (or its registered alias via canonical_domain); never root-normalize a domain to grant access — two different clients can share a registrable root (e.g. welcometothejungle.com). Root-normalization is for www canonicalization only, never a tenant boundary.
  • Keep email/password signup disabled OR enable_confirmations = true in the hosted GoTrue config. The onboarding claim gate trusts the JWT email as proof of mailbox ownership; that holds only if the email is verified (magic-link / OAuth). Password signup without confirmation would be instant domain takeover.

Choosing schemas and Data API exposure

  • Prefer a non-public schema for new tables — current direction. The goal is to move a maximum of new tables out of public for security (anything in public is on the PostgREST API surface and safe only by RLS + grants) and for better semantics (a schema per domain: analytics, config, geo, hubspot, knowledge, …). Decide by who reads the table:
  • Backend/pipeline-internal, no frontend read → put it in a dedicated unexposed schema (e.g. analytics). Unexposed = unreachable by any API caller = strongest isolation. The service still reaches Postgres only through PostgREST, so it cannot INSERT into an unexposed schema directly — write via a SECURITY DEFINER RPC in an exposed schema (revoke EXECUTE from public/anon/authenticated, service-role only). Pattern: analytics.posthog_batch_dlq written by public.record_posthog_batch_dlq (IX-4005), mirroring bulk_patch_conversation_flags.

  • Frontend/app reads it via PostgREST → it must live in an exposed schema. Backoffice-only tables go in the already-exposed backoffice schema (precedent: backoffice.notices), not public.<prefix>_* — no dashboard step either way, smaller public surface. Outside public two safety nets stop working: rls_auto_enable never fires, so write an explicit ENABLE ROW LEVEL SECURITY per table (a missed one ships an open table), and table grants to service_role are dead without GRANT USAGE ON SCHEMA <s> TO service_role. Always scope RLS (never USING (true) for authenticated on tenant data).

  • Exposing a new Postgres schema to the Data API is remote-project configuration, not a migration. config.toml [api] schemas only configures the local stack; production's exposed-schema list is set in Dashboard → Project Settings → API → Exposed schemas and can drift from config.toml (e.g. vectorco is in config.toml but not exposed on prod). Preview branches are configured by scripts/supabase_branch.py ensure: add required frontend schemas to REQUIRED_EXPOSED_SCHEMAS, and the script preserves the branch's existing PostgREST list while appending them through the Management API. A supabase.schema('x') / client.schema('x') call against an unexposed schema fails PGRST106: Invalid schema. This is exactly why an internal table belongs in an unexposed schema written via RPC (above) rather than an exposed one — smaller surface.

Auditing RLS drift and the raw PostHog exception

  • The auto-RLS event trigger (rls_auto_enable, migration 20260407152052) only fires on CREATE TABLE — it does not catch a later manual ALTER TABLE ... DISABLE ROW LEVEL SECURITY, nor a table created before 2026-04-07. "We have auto-RLS" is not a guarantee every public table is covered. Periodically run mcp__supabase__get_advisors(project_id, type: "security") (or the supabase-security skill) against the live project, not just on new migrations, to catch drift (IX-4006: posthog_events_raw carried a dead policy on an RLS-disabled table, invisible to per-migration checks since it predated them).

  • public.posthog_events_raw is ACL-only on purpose — never enable RLS on it. PostHog's direct database export writes the table with COPY FROM as the posthog_export login role, and PostgreSQL refuses COPY FROM on an RLS-enabled table for a non-owner role. Enabling RLS therefore stops ingestion outright: that is IX-4043, ~12h of paused export on 2026-07-17, caused by IX-4006 acting on the false premise that "the service-role client bypasses RLS regardless" (the writer is not the service-role client). Security on this table is grants: relacl gives only postgres + service_role (full), n8n_readonly (SELECT), posthog_export (INSERT); PUBLIC, anon and authenticated hold nothing, so PostgREST cannot reach it. Consequences for anyone auditing:

  • The Supabase advisor finding rls_disabled_in_public on this table is KNOWN-ACCEPTED. Do not "fix" it, do not file it again — note it as accepted and move on.
  • The invariant (RLS off, no inert policies, nothing granted to PUBLIC/anon/authenticated, no role outside the four above) is asserted by a DO … RAISE EXCEPTION block inside 20260827095020_ix4445_acl_only_posthog_events_raw.sql, so any environment applying migrations fails loudly if the state has drifted. supabase/tests/ix4445_posthog_events_raw_acl_only.test.sql re-checks it on demand — nothing in CI runs supabase/tests/*, so ongoing drift detection is the live advisor sweep above, not that file.
  • If the export is ever migrated off COPY FROM to plain INSERT, this constraint disappears and RLS can be reconsidered — until then, out of scope (IX-4445).