Migration from Clerk to Supabase Auth

Feature Owner: Platform / Auth Team (migration owner: clydetims)

Developers: Clyde Ador, Christian Denzon, Patrick Babala

Module: Authentication & Identity — Clerk → Supabase Auth

Date: 2026-10-05

Branch set covered: migration/phase0-1-DB-Provisioning (#812), migration/phase1-DB-Provisioning (#820), migration/phase2-Server-Auth-Layer-Swap (#821), migration/phase3-Admin-Management-Ops (#822), migration/phase4-client-ui-swap/chris (#823), migration/phase4-client-UI-swap-sign-up-access-revoke-page (#824), migration/phase4-forgot-password (#825), migration/phase5-RLS-Sweep (#827), migration/phase6-decommission-clerk/chris (#826)

EXECUTIVE SUMMARY

What is this feature?

Replacement of Clerk as the identity provider with Supabase Auth, delivered as nine

merged pull requests across six phases between 2026-09-09 and 2026-09-11. Clerk's

hosted session model, clerkMiddleware, clerkClient admin API, and Svix webhooks

are removed entirely. Supabase Auth (auth.users, GoTrue) becomes the single source

of identity, with the existing app_users.clerk_id TEXT columns retained but

rewritten in place to hold Supabase auth UUIDs rather than user_2... Clerk strings.

The migration deliberately did not introduce a new user_id column. clerk_id

was kept as the join key across every table so the change stayed inside the auth

layer instead of rippling through every query in the codebase.

Why does it matter?

Clerk and Supabase were two separate paid identity systems operating on the same

users, which meant duplicated per-seat cost and no single enforcement point at the

database. The RLS sweep in Phase 5 is the load-bearing part: it rewrote six

inconsistent identity conventions across ~20 tables to one canonical

auth.uid() form. Once that landed, tenant isolation had a database-level backstop

behind the service-role client rather than depending on every route remembering to

check ownership.

What's the MVP scope?

Included:

• A reversible identity backfill (preflight-app-users.ts → backfill-supabase-auth.ts → send-recovery-links.ts) with an auth_migration_map audit table as the rollback path

• Replacement of the two Clerk webhooks with auth.users database triggers

• Server-side auth swap across 48 files: middleware, authenticate.ts, supabase-server.ts, syncUser.ts, and ~40 API routes

• Admin API replacements for clerkClient operations (invite, delete, reauth, sync, enrollment)

• Client UI swap: sign-in, sign-up, sign-out, user menu, OAuth callback, access-revoked, forgot-password OTP

• Idempotent RLS sweep across the public schema

• Clerk package, config, and test-mock removal

Excluded: renaming clerk_id columns, adding a first-class user_id UUID column,

multi-tenant Clerk→Supabase coexistence, password migration for existing Clerk users

(they set new passwords via recovery links instead), and any change to application

business logic or API contracts.

  ┌──────────────────────────────────────┐
  │          Phase 0    Kickoff          │
  │ Clerk is the live identity provider  │
  └──────────────────────────────────────┘
├─│
│  ▼
  ┌──────────────────────────────────────┐
  │                 #812                 │
  │ Phase 0+1   Dependencies and scripts │
  └──────────────────────────────────────┘
├─│
│  ▼
  ┌──────────────────────────────────────┐
  │                 #820                 │
  │ Phase 1    Triggers, webhooks deleted │
  └──────────────────────────────────────┘
├─│
│  ▼
  ┌──────────────────────────────────────┐
  │                 #821                 │
  │  Phase 2    Server auth layer swap   │
  └──────────────────────────────────────┘
├─│
│  ▼
  ┌──────────────────────────────────────┐
  │                 #822                 │
  │ Phase 3    Admin and management ops  │
  └──────────────────────────────────────┘
├─│
│  ▼
  ┌──────────────────────────────────────┐
  │                 #823                 │
  │      Phase 4    Client UI swap       │
  └──────────────────────────────────────┘
├─│
│  ▼
  ┌──────────────────────────────────────┐
  │                 #824                 │
  │  Phase 4    Sign-up, access-revoked  │
  └──────────────────────────────────────┘
├─│
│  ▼
  ┌──────────────────────────────────────┐
  │                 #825                 │
  │  Phase 4    Forgot password via OTP  │
  └──────────────────────────────────────┘
├─│
│  ▼
  ┌──────────────────────────────────────┐
  │                 #827                 │
  │   Phase 5    RLS auth.uid() sweep    │
  └──────────────────────────────────────┘
├─│
│  ▼
  ┌──────────────────────────────────────┐
  │                 #826                 │
  │    Phase 6    Decommission Clerk     │
  └──────────────────────────────────────┘
│
├─│
│  ▼
  ┌──────────────────────────────────────┐
  │         Cutover    Complete          │
  │     Zero Clerk code paths remain     │
  └──────────────────────────────────────┘

USER PAIN POINT & SOLUTION

Current State (Without Feature)

Two identity providers in one application. Users authenticate with Clerk and are

mirrored into app_users by Svix webhooks (/api/clerk/user-created,

/api/clerk/user-updated). Server routes call auth() or currentUser() from

@clerk/nextjs/server; admin routes call clerkClient. Meanwhile Supabase was

already the primary database, so a Clerk outage and a Supabase outage were two

independent availability risks, and the RLS policies had no reliable way to

resolve "who is calling" because Clerk IDs are opaque strings that Postgres cannot

validate against auth.uid().

Pain Point

Emotional: Support tickets for users locked out mid-migration, with no way to

tell whether a login failure is a Clerk session problem or a Supabase row problem.

Functional: Profile provisioning depended on webhook delivery. The migration

plan explicitly records that deleting the webhooks before cutover silently drops

the welcome email for direct sign-ups and profile sync for existing users, because

syncUserIfNotExists only provisions new rows.

Business Impact: Duplicate per-seat identity cost, a webhook-dependent signup

path with no retry, and RLS policies carrying six mutually inconsistent

identity-resolution conventions.

Future State (With Feature)

Supabase Auth is the only identity provider. User creation and profile mirroring

happen inside the database via on_auth_user_created and on_auth_user_updated

triggers on auth.users, so there is no network hop and no webhook delivery to

lose. RLS resolves the caller canonically through auth.uid(), and every

auth_migration_map row preserves the old Clerk ID so the overwrite is reversible.

Marketing Hook

"One identity layer, enforced in the database — not two providers and a webhook."

4D FRAMEWORK MAPPING

Diagnose

A preflight pass (preflight-app-users.ts) plus an FK introspection RPC

(get_fk_on_update_action) identified two classes of breakage before any write:

Clerk users with no matching app_users row, and FK columns that referenced

app_users.clerk_id without ON UPDATE CASCADE. The second class mattered because

the backfill rewrites clerk_id in place, and any FK set to NO ACTION would

abort mid-run.

Design

The migration was sequenced so that no phase depended on a later one being

reverted. Database provisioning and the backfill script landed first while Clerk was

still live; the server swap came next; the RLS sweep was deliberately sequenced

after cutover because it is "functionally latent today (service-role writes), so

it is sequenced after cutover rather than blocking it" per the plan's own risk note.

Clerk decommission came last.

Develop

clerk_id was kept as the physical column name everywhere. Internally-typed UUID

columns were resolved in policies with

col = (SELECT id FROM app_users WHERE clerk_id = auth.uid()::text), while

*_clerk_id and TEXT creator_id columns resolved with col = auth.uid()::text.

Keeping the name meant no query rewrites outside the auth layer.

Deliver

Nine merged PRs, five SQL migrations, three operational scripts, one new shared

admin helper module, one new test helper, and 424 new test lines. The measurable

output is that @clerk/nextjs no longer appears in package.json and no Clerk code

path resolves at runtime.

USER FLOWS

Entry Point

• Operator: npx tsx lib/supabase/scripts/preflight-app-users.ts, then backfill-supabase-auth.ts --dry-run, then --yes

• User: /sign-in, /sign-up, /forgot-password, /access-revoked

• OAuth return: /auth/callback

• Post-auth gate: /redirect-check

Success Criteria

• Every pre-existing Clerk user can sign in with a password they set via recovery link

• app_users.clerk_id holds the matching auth.users.id for 100% of rows

• auth_migration_map has exactly one row per migrated user

• No RLS policy in public still matches auth.jwt(), request.jwt.claims, or auth.uid()::uuid

Main Flow (Happy Path)

Operator cutover (the point-of-no-return sequence)

1. Run preflight and read every warning; abort if any Clerk user lacks an app_users row

2. Run backfill-supabase-auth.ts --dry-run and reconcile the planned row count

3. Run backfill-supabase-auth.ts --yes: create confirmed auth users with random passwords, rewrite child columns first, then app_users.clerk_id last, persisting auth_migration_map before the overwrite

4. Run send-recovery-links.ts so every migrated user sets a new password

5. Smoke-test auth.admin.createUser on the target DB and confirm the trigger seeded the app_users row and LEARNER role

6. Merge Phase 6, then remove Clerk environment variables

Password sign-in (/sign-in)

1. Client zod-validates email and password, then POST /api/auth/login-rate-limit

2. On 429, surface the rate-limit message and stop

3. supabase.auth.signInWithPassword(...)

4. Record the failure server-side, then stamp sessionStorage and redirect after 500 ms to /redirect-check

OAuth sign-in (/auth/callback)

1. Google returns to /auth/callback?code=...&next=/redirect-check

2. sanitizeNextPath validates next as a same-origin absolute path

3. supabase.auth.exchangeCodeForSession(code)

4. Redirect to the sanitized path; any failure redirects to /sign-in?error=oauth

Password recovery (/forgot-password)

1. supabase.auth.resetPasswordForEmail(email, { redirectTo })

2. User enters the 6-digit code; supabase.auth.verifyOtp({ email, token, type: "recovery" })

3. supabase.auth.updateUser({ password })

4. Success state, then redirect to /redirect-check after 3 s

New registration (/sign-up)

1. zod-validates first/last name, email, password, and confirmation

2. supabase.auth.signUp(...) with full_name / first_name / last_name in user metadata and emailRedirectTo

3. If no session was returned, show the verification-pending state; otherwise show the signed-in state

4. Alternative path: signInWithOAuth({ provider: "google" })

Edge Cases

Decision Points

IF the backfill dry-run row count does not match the preflight count THEN abort and reconcile.

ELSE IF a Clerk user has no app_users row THEN stop; the trigger will provision it only for new auth users, so a missing profile needs manual handling before the overwrite.

ELSE IF sanitizeNextPath cannot prove next is same-origin THEN redirect to /redirect-check.

ELSE IF the OAuth code exchange fails THEN redirect to /sign-in?error=oauth.

ELSE proceed with the redirect.

INFORMATION ARCHITECTURE

Primary Information (Always Visible)

• Authenticated identity state (signed-in/out, name, avatar) in the header and user menu

• Sign-in, sign-up, and recovery form errors as inline field or form-level messages

• The access-revoked suspension notice

Secondary Information

• Verification-pending state after sign-up

• OTP code entry step, resend cooldown timer, and password strength requirements

• Rate-limit messaging on repeated failed sign-ins

Tertiary Information (Hidden Until Needed)

• The next redirect target, carried in the OAuth URL

• sessionStorage handoff key used to hand a post-sign-in redirect to /redirect-check

• user_metadata.app_user_id, the backfill/trigger marker

Actions

Primary CTA

"Sign in"

Secondary Actions

"Create an account", "Continue with Google", "Forgot password", "Sign out", "Resend code"

WIREFRAMES

Key Screens

Sign in (/sign-in)

Purpose: Authenticate an existing user by password or Google OAuth.

Components: email field, password field, inline field errors, form-level error banner, rate-limit banner, Google button, loading state.

┌──────────────────────────────────────────────┐
│                                              │
│              Welcome back                    │
│                                              │
│   Email                                      │
│   ┌────────────────────────────────────────┐  │
│   │ you@example.com                       │  │
│   └────────────────────────────────────────┘  │
│   Please enter a valid email address         │
│                                              │
│   Password                                   │
│   ┌────────────────────────────────────────┐  │
│   │ ••••••••                               │  │
│   └────────────────────────────────────────┘  │
│                                              │
│   ┌────────────────────────────────────────┐  │
│   │              Sign in                   │  │
│   └────────────────────────────────────────┘  │
│   Too many failed login attempts.            │
│                                              │
│   ────────────  or  ────────────             │
│   ┌────────────────────────────────────────┐  │
│   │      Continue with Google              │  │
│   └────────────────────────────────────────┘  │
│                                              │
│   Don't have an account? Create one          │
│   Forgot your password?                      │
└──────────────────────────────────────────────┘

Forgot password (/forgot-password)

Purpose: Request a recovery code, verify it, then set a new password.

Components: email step, OTP code step with resend cooldown, new-password step, success state.

┌──────────────────────────────────────────────┐
│  STEP 1 of 3        ● ○ ○                    │
│                                              │
│        Forgot your password?                 │
│                                              │
│   Email                                      │
│   ┌────────────────────────────────────────┐  │
│   │ you@example.com                       │  │
│   └────────────────────────────────────────┘  │
│                                              │
│   ┌────────────────────────────────────────┐  │
│   │        Send reset code                 │  │
│   └────────────────────────────────────────┘  │
├──────────────────────────────────────────────┤
│  STEP 2 of 3        ● ● ○                    │
│                                              │
│        Enter the 6-digit code                │
│        sent to you@example.com               │
│                                              │
│   ┌────────────────────────────────────────┐  │
│   │  1 2 3 4 5 6                           │  │
│   └────────────────────────────────────────┘  │
│   The reset code has expired or is invalid.  │
│                                              │
│   Resend code (00:45)                        │
├──────────────────────────────────────────────┤
│  STEP 3 of 3        ● ● ●                    │
│                                              │
│        Choose a new password                  │
│                                              │
│   New password        [show/hide]            │
│   Confirm password      [show/hide]          │
│   Passwords do not match                     │
│                                              │
│   ┌────────────────────────────────────────┐  │
│   │        Update password                 │  │
│   └────────────────────────────────────────┘  │
├──────────────────────────────────────────────┤
│  SUCCESS                                     │
│  Your password has been updated.             │
│  Redirecting you to WyzQuests...             │
└──────────────────────────────────────────────┘

Modal / Detail Views

No new modals. The user menu (UserButton) is a dropdown anchored to the header

avatar and carries sign-out; SignInButton and SignOutButton are self-contained

header actions.

Empty State

/redirect-check renders nothing user-visible while it resolves the effective role

and redirects; it is a routing interstitial, not a page with content.

Loading State

Sign-in and sign-up buttons show a spinner and disable while the auth call is in

flight. SessionProvider starts from a server-provided user and a isLoaded

flag, so first paint is not blocked on a client round-trip.

Error State

Errors are inline, never alert(). getSupabaseAuthErrorMessage normalizes GoTrue

error codes to human text, and each flow holds both per-field errors and a

form-level message.

Annotations

• Sign-up is link-based, not OTP: signUp returns a session when email

confirmation is disabled and no session (verification state) when it is enabled.

verifyOtp is used only by password recovery.

• The pre-Supabase Clerk sign-in page hard-coded forceRedirectUrl. There was

never a postSignInUrl value in this codebase — the Supabase replacement is the

500 ms sessionStorage handoff to /redirect-check.

WIREFLOWS

Primary password sign-in flow:

/sign-in
   |
   v
[zod validate] --[invalid]--> [inline field errors]
   |
   v
[POST /api/auth/login-rate-limit]
   |
   +--[429]--------------------> [rate-limit banner, stop]
   |
   v
[supabase.auth.signInWithPassword]
   |
   +--[error]--> [record failure via login-rate-limit]
   |             [getSupabaseAuthErrorMessage]
   |             [form-level error]
   |
   v
[stamp sessionStorage, wait 500 ms]
   |
   v
/redirect-check --[no role]--> /access-revoked
   |
   v
[role dashboard]

Secondary OAuth flow:

/sign-in or /sign-up
   |
   v
[signInWithOAuth provider=google]
   |  redirectTo = <origin>/auth/callback?next=/redirect-check
   v
Google
   |
   v
/auth/callback?code=...&next=...
   |
   +--[no code]------------------> /sign-in?error=oauth
   |
   v
[sanitizeNextPath(next, origin)]
   |  reject non-absolute / // / control chars /
   |  backslash / cross-origin -> /redirect-check
   v
[exchangeCodeForSession(code)]
   |
   +--[error]--------------------> /sign-in?error=oauth
   |
   v
[redirect to safeNext]

Tertiary operator backfill flow:

┌──────────────────────────────────────────────────────────────────┐
│                 Step 1   Preflight and FK audit                  │
│                    --dry-run only, no writes                     │
│          abort if any Clerk user lacks an app_users row          │
└──────────────────────────────────────────────────────────────────┘
├───│
│   ▼
┌──────────────────────────────────────────────────────────────────┐
│                  Step 2   Dry-run the backfill                   │
│               backfill-supabase-auth.ts --dry-run                │
│          review the planned row count before any write           │
└──────────────────────────────────────────────────────────────────┘
├───│
│   ▼
┌──────────────────────────────────────────────────────────────────┐
│                  Step 3   Execute the backfill                   │
│                 backfill-supabase-auth.ts --yes                  │
│       creates confirmed users, records auth_migration_map        │
└──────────────────────────────────────────────────────────────────┘
├───│
│   ▼
┌──────────────────────────────────────────────────────────────────┐
│                   Step 4   Send recovery links                   │
│                      send-recovery-links.ts                      │
│             every migrated user sets a new password              │
└──────────────────────────────────────────────────────────────────┘
├───│
│   ▼
┌──────────────────────────────────────────────────────────────────┐
│                 Step 5   Verify RLS enforcement                  │
│                       check-rls-enabled.ts                       │
│        must pass before Phase 6 removes the Clerk package        │
└──────────────────────────────────────────────────────────────────┘
├───│
│   ▼
┌──────────────────────────────────────────────────────────────────┐
│                      Step 6   Decommission                       │
│                  merge Phase 6, drop Clerk env                   │
│                    no Clerk code path remains                    │
└──────────────────────────────────────────────────────────────────┘

PROTOTYPE

Figma Prototype Link: Not specified. The client work in #823–#825 was a

component-level swap against the existing design system; no new Figma frames were

produced for the Clerk-specific surfaces that were removed

(ClerkProviderWrapper.tsx was deleted).

Other design references: components/auth/SignInButton.tsx,

components/auth/SignOutButton.tsx, components/auth/UserButton.tsx, and the

/access-revoked page introduced in #824.

How to Test (Manual)

1. Sign out, then sign in with a migrated account using the password set through a recovery link.

2. Confirm Google OAuth from /sign-in lands on the role dashboard and not on an off-origin URL.

3. Exercise next injection: request /auth/callback?code=...&next=//evil.com and confirm the redirect target is /redirect-check.

4. Run the full forgot-password three-step flow, then resubmit a valid code after the recovery session has expired and confirm expired-code guidance appears.

5. Sign up with a new email and confirm the verification-pending state, then suspend the account and confirm /access-revoked is enforced.

BACKEND SCHEMA

Database Tables

auth.users (Supabase-managed)

Purpose: Canonical identity store after cutover. Not created by these migrations.

Source: Supabase platform

-- Provisioned by the backfill and by auth.users triggers.
-- createUser is called with EmailConfirm: true and a random password, so no
-- user is ever left in an unconfirmed state by the migration itself.

app_users

Purpose: Application profile and role store. id stays the internal UUID; clerk_id is rewritten in place to the auth UUID.

Source: pre-existing (created out-of-band, not in supabase/migrations/)

-- Existing columns reused without rename:
--   id                  UUID PRIMARY KEY      -- internal, never rewritten
--   clerk_id            TEXT                  -- now holds auth.users.id
--   email               TEXT UNIQUE           -- app_users_email_key
--   name                TEXT
--   role                platform_role         -- seeded LEARNER by trigger
--   profile_image_url   TEXT
--   agency_id           UUID

auth_migration_map

Purpose: Durable audit and rollback record for the clerk_id overwrite — the safety net for the point-of-no-return operation.

Source: supabase/migrations/20260908_create_auth_migration_map.sql (#812)

CREATE TABLE IF NOT EXISTS public.auth_migration_map (
    -- Legacy Clerk user ID (e.g. "user_2abc..."). Primary key so the backfill
    -- can upsert on it and stay idempotent.
    old_clerk_id TEXT PRIMARY KEY,
 
    -- New Supabase auth.users.id (UUID) written into the clerk_id columns.
    new_auth_id  UUID NOT NULL,
 
    -- Internal app_users.id (UUID) of the matching profile row.
    app_user_id  UUID NOT NULL,
 
    -- Denormalized email for easier reconciliation/audit.
    email        TEXT,
 
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
CREATE INDEX IF NOT EXISTS idx_auth_migration_map_new_auth_id
    ON public.auth_migration_map (new_auth_id);
 
CREATE INDEX IF NOT EXISTS idx_auth_migration_map_app_user_id
    ON public.auth_migration_map (app_user_id);
 
-- Service-role-only table: block all direct client access.
ALTER TABLE public.auth_migration_map ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY "No direct client access to auth_migration_map"
    ON public.auth_migration_map
    FOR ALL
    USING (false)
    WITH CHECK (false);

Relationships:

• app_users.clerk_id → auth.users.id — one-to-one, both TEXT, no declared FK

• agencies.owner_clerk_id and agency_members.user_clerk_id → app_users.clerk_id — also not declared FKs

• user_presence_logs.clerk_id → app_users.clerk_id — the one real FK, converted to ON UPDATE CASCADE ON DELETE CASCADE by the Phase 0/1 migration

• gemini_cache.created_by and gemini_pricing.updated_by → auth.users.id — direct FKs, backfilled via auth_migration_map in the Phase 5 sweep

Identity model after cutover:

┌──────────────────────────────────────┐
│              auth.users              │
│      Supabase Auth UUID, native      │
└──────────────────────────────────────┘
                    │  auth.getUser() / getClaims()
                    ▼
┌──────────────────────────────────────┐
│     sb-<project-ref>-auth-token      │
│ session cookie, set by @supabase/ssr │
└──────────────────────────────────────┘
                    │  one-to-one, both TEXT
                    ▼
┌──────────────────────────────────────┐
│          app_users.clerk_id          │
│ rewritten in place to the auth UUID  │
└──────────────────────────────────────┘
┌──────────────────────────────────────┐
│             app_users.id             │
│    internal UUID, never rewritten    │
└──────────────────────────────────────┘
                    │
                    ├──────────┴──────────┘
┌──────────────────────────────────────┐
│          auth_migration_map          │
│     old_clerk_id -> new_auth_id      │
└──────────────────────────────────────┘

Indexes

Constraints

auth_migration_map: old_clerk_id is PRIMARY KEY (upsert target); new_auth_id

and app_user_id are NOT NULL; RLS forced with a deny-all policy so only the

service-role client can write.

app_users: relies on app_users_email_key UNIQUE on email; the trigger's

insert-conflict-adoption path and the email-conflict branch in

sync_app_user_profile both depend on that constraint. Because app_users

predates supabase/migrations/, the trigger migration is not self-contained and

will fail on a fresh supabase db reset.

user_presence_logs: FK to app_users.clerk_id upgraded to `ON UPDATE CASCADE

ON DELETE CASCADEso the in-placeclerk_id` rewrite propagates.

RLS / Database Security

The Phase 5 sweep rewrote every non-canonical policy to one of two forms:

• Auth-UUID columns (*_clerk_id, login_attempts.clerk_id, TEXT

  idea_validations / quest_generations / adventure_generations creator_id):

  col = auth.uid()::text

• Internal-UUID columns (user_id, to_user_id, app_user_id, creator_id

  UUID, added_by, manager_id, folder_permissions.user_id,

  asset_metadata.creator_id):

  col = (SELECT id FROM app_users WHERE clerk_id = auth.uid()::text)

• Admin checks: user_has_role(id, 'ADMIN') / user_has_any_role(...)

  resolved via clerk_id = auth.uid()::text

Helper Functions

Triggers

RPC / Stored Procedures

API ENDPOINTS

No endpoint URLs, methods, or response shapes changed. The migration is

implementation-internal to every route.

GET /auth/callback

Purpose: OAuth code-for-session exchange, then same-origin redirect.

Auth: Public. No session required — the session is created by this exchange.

Query Params: code (required), next (optional, sanitized)

Path Params: None

Request Body: None

302 Location: /redirect-check

Error Responses: Missing code, exchange failure, or unexpected throw → 302

to /sign-in?error=oauth. Unsafe next → 302 to /redirect-check.

POST /api/auth/login-rate-limit

Purpose: Pre-flight rate-limit check before signInWithPassword, and failure recording after an error. Carried over from the pre-migration app; wired into the Supabase sign-in flow in #823.

Auth: Public (unauthenticated, keyed by submitted email)

Request Body: { "email": string }

{ "message": "Too many failed login attempts. Please try again later." }

Error Responses: 429 surfaces the message in the rate-limit banner.

GET /api/admin/sync-users

Purpose: Bulk admin sync, rewritten in #822 off clerkClient.

Auth: ADMIN (platform role)

Request Body: None

Response: Per-user sync results

Error Responses: Non-admin → 403; Supabase errors surfaced per row

Routes rewritten in #821 (48 files): app/admin/page.tsx,

app/reviewer/dashboard/page.tsx, app/reviewer/queue/page.tsx,

app/api/admin/agencies/*, app/api/admin/change-role,

app/api/admin/delete-user, app/api/admin/invite-user, app/api/admin/reauth,

app/api/admin/suspend-user, app/api/agency/* (7 routes),

app/api/ai/* (6 routes), app/api/creator/enrollment/*,

app/api/folder/search, app/api/get-role, app/api/learner/* (6 routes),

app/api/user/available-roles, app/api/user/heartbeat,

app/api/user/switch-role, plus lib/api/admin/* server actions and

lib/ai/token-enforcement.service.ts.

DATA REQUIREMENTS

Frontend Needs

// hooks/auth/SessionProvider.tsx
type SessionState = {
  user: AuthUser | null;
  isLoaded: boolean;
};
 
// lib/syncUser.ts — the only shape the auth layer consumes internally
export interface SupabaseAuthUserPayload {
  id: string;      // auth.users.id UUID, replaces the Clerk user id
  email: string;
  name: string;    // from user_metadata.full_name
  image: string | null;
}
 
// lib/auth/supabase-server.ts
export type SupabaseClientResult =
  | { success: true; supabase: SupabaseClient }
  | { success: false; error: { message: string; status: 401 | 500 } };
 
// lib/schemas/password-reset.schema.ts
export const passwordField = /* shared by sign-up, reset, and confirm */;

Required fields: id and email on the auth payload — authenticateUser()

throws AuthenticationError("Missing required user fields") without both.

API Calls Frontend Will Make

Caching Strategy

Client-side: The browser Supabase client is a module-level singleton

(getSupabaseBrowserClient()), so auth state is shared across components and is

not re-fetched per component. SessionProvider hydrates from a server-provided

user, so first paint needs no client round-trip.

Refetch: SessionProvider re-reads the session on mount and on

onAuthStateChange; useAuth reads from that context.

Invalidation: signOut() clears the session; no manual cache purge is needed

because the singleton holds no query cache.

Debouncing: Resend-cooldown countdown (secondsUntilResend) gates the resend

button; there is no input debounce on the OTP field.

Deduplication: deriveNameFromEmail is shared between invite-user and

creator-enrollment so both emit identical user_metadata.

Stale-data handling: authenticateUser uses a 5-minute TTL userIdCache;

authenticateUserWithRole uses a 30-second roleCache. Both are keyed by the

auth UUID and bypassed on checkCircuitBreaker().

Server-side caching: createSupabaseServerClient is created per request.

createUserScopedSupabaseClient returns an anon-key client bound to the session

cookie, so RLS — not a route-level filter — is what bounds user-facing reads.

PERFORMANCE CONSIDERATIONS

Database Optimization

Every RLS predicate on an internal-UUID column is now a correlated subselect

((SELECT id FROM app_users WHERE clerk_id = auth.uid()::text)) rather than a

direct auth.uid() comparison. That is one extra index lookup on app_users per

row-evaluated policy check, which is why the app_users.clerk_id index is on the

hot path. The alternative — duplicating the auth UUID into every table — was

rejected in the plan as the more invasive option.

Caching Strategy

userIdCache (5 min TTL) and roleCache (30 s TTL) absorb the repeated

app_users lookups that authenticate* would otherwise make per request.

API Response Time

Performance targets: No new latency budget was set. The auth swap moved session

resolution from Clerk's hosted edge to a same-request Postgres call.

Expected response time: auth.getUser() adds one round-trip per request in

middleware and again in authenticateUser*, mitigated by the TTL caches above.

Known bottlenecks:

• Middleware calls supabase.auth.getUser() on every request, including

  static and public ones, because the client must be constructed before the route

  check.

• findAuthUserByEmail paginates listUsers at 1000 users/page because the

  installed auth-js has no getUserByEmail. At scale this is a full table scan

  of auth.users on the server.

• The RLS subselect is evaluated per row rather than once per query.

Potential future optimizations:

• Replace the middleware getUser() with a JWT/claims read that needs no network hop.

• Cache findAuthUserByEmail results, or add an email→auth-id index the admin path can hit.

• Hoist the app_users subselect in hot RLS policies into a STABLE function.

SECURITY & AUTHORIZATION

Access Matrix

Auth surfaces only. Ownership and role rules inside quest/agency tables were not

changed by this migration.

Notes: The last row is absolute, not role-gated — the table has a deny-all RLS

policy and is only reachable with the service-role key.

Authorization Logic

Authentication: Supabase Auth sessions in sb-<project-ref>-auth-token cookies,

set by @supabase/ssr and refreshed in middleware's setAll. Server helpers all

resolve the caller through getCurrentSupabaseUser() (which calls auth.getUser(),

verifying the JWT against the auth server rather than trusting the cookie).

Role checks: authenticateUserWithRole() / authenticateAgencyAdmin() /

authenticateAgencyMember() / authenticateAgencyManager() / authenticateUserWithAgency()

are unchanged in signature — only the identity source swapped from auth() to

getCurrentSupabaseUser(). RESTRICTED_AGENCY_ADMIN_ROLES and

RESTRICTED_AGENCY_MEMBER_ROLES are unchanged.

Permission checks: user_has_role(id, 'ADMIN') and user_has_any_role(...)

resolve the caller via clerk_id = auth.uid()::text inside the database.

Resource ownership: verifyContentOwnership(content_id, content_type, creator_id)

and verifyAgencyResourceOwnership(context, resourceCreatorId) are unchanged and

still run before service-role queries, per the repo's hard rule.

Tenant/agency scoping: is_agency_member / is_agency_manager now resolve the

caller from auth.uid() instead of raw JWT claims.

Server-side authorization: Unchanged in shape. The service-role client still

bypasses RLS, so the route-level ownership checks remain the primary tenant

boundary; the Phase 5 sweep is defense in depth behind it.

Frontend permission visibility: UserButton / SignOutButton render from

SessionProvider state. Route middleware returns 401 JSON for unauthenticated

/api requests.

Data Validation

Validation library: zod (v4) — signUpSchema, signInSchema, and the shared

passwordField in lib/schemas/password-reset.schema.ts.

Input limits: Email 1–255 chars; first/last name trimmed, 1–50 chars.

Required fields: Sign-up requires firstName, lastName, email, password,

confirmPassword. refine enforces password === confirmPassword with the error

on confirmPassword.

UUID validation: UUID-shaped ids are regex-checked in the backfill script;

runtime route params are validated by the existing route schemas.

Enum validation: PlatformRole via platformRoleEnum; view_as hierarchy via

VIEW_AS_HIERARCHY.

File validation: Unchanged.

Server-side validation: Both client and server run the same zod schemas. Errors

are collected into a Record<string, string> keyed by issue.path[0], first error

per field wins.

Cross-tenant validation: Unchanged, and still mandatory — service-role queries

targeting user-supplied ids must be preceded by an ownership or agency-membership

check.

ERROR HANDLING

Transaction/rollback: The backfill is not wrapped in a single transaction —

it is intentionally ordered and restartable. It writes child columns first, then

app_users.clerk_id last, and only after auth_migration_map has been persisted,

so a crash mid-run leaves the map complete enough to finish or reverse. Each SQL

migration is individually idempotent (CREATE TABLE IF NOT EXISTS,

CREATE OR REPLACE FUNCTION, DROP TRIGGER IF EXISTS before recreate,

DROP POLICY IF EXISTS before create) so any phase can be re-applied.

The documented rollback path is auth_migration_map: it holds the old Clerk ID for

every rewritten row. Note that rollback restores identifiers, not credentials —

users who set passwords during the migration would need a new recovery link.

TESTING CHECKLIST

Happy Path

[ ] Migrated user signs in with the password set via recovery link

[ ] New user signs up and receives the verification-pending state

[ ] Google OAuth round-trip returns to /redirect-check

[ ] Full three-step forgot-password flow completes and redirects after 3 s

[ ] Trigger provisions an app_users row with a LEARNER role for a newly created auth user

[ ] sync_app_user_profile mirrors an email change into app_users

[ ] Backfill dry-run reports a count matching preflight, then --yes completes

[ ] Admin invite, delete, reauth, sync, and enrollment routes work off auth.admin

Edge Cases

[ ] next=//evil.com and next=/\evil.com both fall back to /redirect-check

[ ] OAuth callback with no code redirects to /sign-in?error=oauth

[ ] Sign-up with an existing confirmed email shows the already-registered message

[ ] verifyOtp succeeds, session expires, resubmit shows expired-code guidance

[ ] findAuthUserByEmail matches case-insensitively and returns null past the last page

[ ] Email local-part 12345 yields "User" metadata, not empty strings

[ ] Trigger fires on a backfill-created user and does not duplicate the profile

[ ] auth_migration_map cannot be read through the anon or authenticated client

[ ] RLS verification query finds no remaining auth.jwt() / request.jwt.claims / auth.uid()::uuid policy in public

Coverage gaps to close (not covered by the nine PRs):

[ ] sanitizeNextPath in app/auth/callback/route.ts has no unit test despite being the primary open-redirect defense

[ ] SessionProvider, useAuth, useUser, and useSupabase have no tests

[ ] lib/supabase/admin.ts (deriveNameFromEmail, isEmailExistsError, findAuthUserByEmail) has no tests

[ ] lib/auth/sign-up.ts (parseSignUpForm, submitSignUpForm, isAlreadyRegistered) has no tests

[ ] The backfill script has no automated test

The only new tests shipped in the migration were

tests/pages/ForgotPasswordPage.test.tsx (208 lines, #825) and

tests/unit/password-reset.schema.test.ts (216 lines, #825). #826 migrated eight

existing suites off Clerk mocks and added tests/helpers/supabaseAuthMock.ts.

OPEN QUESTIONS

For Frontend

Should sanitizeNextPath be extracted into a shared util and unit-tested? It is the

only open-redirect defense and currently has no test coverage.

Should the 500 ms sessionStorage handoff be replaced with a query parameter or a

server-side cookie? A fixed delay is fragile on slow connections.

Should /access-revoked show the suspension reason and duration? It currently shows

only a generic suspension notice.

For Backend

How long should auth_migration_map be retained before it is archived or dropped?

It currently blocks all client access permanently and holds every user's old Clerk

ID in plaintext.

Should clerk_id be renamed now that it holds an auth UUID? The name is actively

misleading and the rename cost has not been re-evaluated since cutover.

Should quest_comments INSERT regain a role gate at the database layer, or is

API-layer-only enforcement accepted as the permanent state? The Phase 5 migration

documents this as an intentional relaxation.

Is the RLS verification RAISE WARNING audit sufficient as the sweep's completion

criterion? The migration itself notes it only scans public and pattern-matches

LIKE, so it "is a useful signal that the sweep is complete, not proof."

Should the user_presence_logs cascade also be applied to the undeclared

agencies.owner_clerk_id and agency_members.user_clerk_id "foreign keys"? They

have no FK constraint, so nothing cascades for them.

SUCCESS METRICS

Zero Clerk code paths resolve at runtime: @clerk/nextjs absent from

package.json, no app/api/clerk/* routes, no .clerk/ config directory.

100% of app_users.clerk_id values match an auth.users.id, with a 1:1 row count

in auth_migration_map.

Sign-in success rate for migrated users at or above the pre-migration Clerk

baseline after the recovery-link window closes.

Zero RLS policies in public matching auth.jwt(), request.jwt.claims, or

auth.uid()::uuid.

Every service-role query targeting user-supplied ids still preceded by an

ownership or agency-membership check (repo hard rule preserved through the swap).

DEPENDENCIES

This Feature Depends On

Existing features: app_users profile and role model; the six-platform-role

system (OWNER/ADMIN/CREATOR/REVIEWER/LEARNER and view-as hierarchy);

login rate limiting; agency membership and ownership checks; idle timeout.

Database tables: app_users, agencies, agency_members,

user_presence_logs, notifications, folders/project_folders,

folder_permissions, quest_comments, comment_deletion_log,

learner_* tables, token_usage*, ai_usage_tracking, gemini_config,

gemini_cache, gemini_pricing, idea_validations, quest_generations,

adventure_generations, asset_metadata, page_visits,

quest_gamifications, global_achievements.

APIs: Supabase Auth (GoTrue) — signUp, signInWithPassword,

signInWithOAuth, exchangeCodeForSession, resetPasswordForEmail,

verifyOtp, updateUser, getSession, getUser, and auth.admin.*

(createUser, listUsers, deleteUser, updateUserById).

Services: Supabase Auth, Supabase Postgres + RLS, Google OAuth provider,

Resend/email delivery for the Reset Password and Set Password templates.

Authentication: Supabase session cookies (sb-<project-ref>-auth-token);

wyzquests_view_as_role remains the application-managed view-as cookie.

Migrations:

20260908_create_auth_migration_map.sql,

20260909_get_fk_on_update_action_rpc.sql,

20260909_presence_logs_fk_on_update_cascade.sql,

20260909_create_auth_users_triggers.sql,

20260911_supabase_rls_auth_uid.sql

Libraries: @supabase/supabase-js, @supabase/ssr, zod. Removed:

@clerk/nextjs, svix.

These Features Depend On This

Every authenticated route and component — lib/auth/authenticate.ts and

lib/auth/supabase-server.ts are imported by ~48 route modules plus the admin

server actions. Any regression in session resolution surfaces application-wide.

TIMELINE & OWNERSHIP

Owner: Platform / Auth Team (migration commits authored by clydetims)

Implementation timeline: Nine PRs merged over three days:

Completion date: 2026-09-11

Follow-up work:

• Add unit tests for sanitizeNextPath, lib/supabase/admin.ts, and

  lib/auth/sign-up.ts (currently zero coverage on the new auth code).

• Decide the retention policy for auth_migration_map.

• Re-evaluate renaming clerk_id now that it holds an auth UUID.

• Add declared FKs with ON UPDATE CASCADE for agencies.owner_clerk_id and

  agency_members.user_clerk_id.

• Review whether quest_comments INSERT should regain a database-level role gate.

Blocking environment prerequisites:

• NEXT_PUBLIC_SUPABASE_URL, NEXT_PUBLIC_SUPABASE_ANON_KEY,

  SUPABASE_SERVICE_ROLE_KEY, and app-origin env vars configured.

• Supabase Reset Password email template must include {{ .Token }} (the

  6-digit code), not just the confirmation link, or the OTP form has nothing to

  verify. Configured in the dashboard for the hosted project and in

  supabase/config.toml + supabase/templates/ for local dev.

• Google OAuth client registered and its callback pointed at /auth/callback.

• Cutover gate: Phase 1 bundled trigger creation with webhook deletion in one

  commit. Before the production cutover, record evidence that the migration was

  applied and the auth.admin.createUser smoke test passed (an app_users row

  with a seeded LEARNER role) on the target DB. Deleting the webhooks early, while

  Clerk is still the active IdP, silently drops the welcome email for direct

  sign-ups and profile sync for existing users.

• The trigger migration assumes app_users and app_users_email_key already

  exist. It is not self-contained and will fail on a fresh supabase db reset or

  in CI — confirm preflight output on the target DB before applying.

• Note: scripts/check-rls-enabled.ts and its CI workflow

  (.github/workflows/rls-check.yml) were not part of these nine PRs; they

  were added later in aec04f1f (2026-09-22) as post-migration hardening.

Document Version

1.0 - Initial Clerk → Supabase Auth migration technical document covering PRs

#812, #820–#827 across Phases 0–6 - 2026-10-05