Migration Clerk to Supabase Auth

Feature Owner: Platform / Auth Team (Migration Owner: clydetims)

Module: Authentication & Identity - Clerk to Supabase Auth

Priority: High

[Sprint/Week]: Migration Window (2026-09-09 - 2026-09-11) [Status]: Complete

Date: 2026-10-05

EXECUTIVE SUMMARY

What is this feature?

Replacement of Clerk as the identity provider with Supabase Auth, delivered across nine merged pull requests spanning six phases between 2026-09-09 and 2026-09-11. Clerk's hosted session model (clerkMiddleware), clerkClient admin API, and Svix webhooks were removed entirely. Supabase Auth (GoTrue, auth.users) is now the single source of identity. The existing app_users.clerk_id TEXT columns were retained and rewritten in-place to hold Supabase Auth UUIDs instead of Clerk strings, with an auth_migration_map audit table preserving the old to new mapping for reversibility.

Why does it matter?

Consolidated identity (eliminated two paid identity systems), a single enforcement point at the database via RLS normalized to auth.uid(), and removal of webhook-dependent provisioning (replaced by auth.users DB triggers). This reduces cost, removes webhook fragility, and provides DB-level tenant isolation backstops.

What's the MVP scope?

Included: Reversible backfill tooling (preflight-app-users.ts, backfill-supabase-auth.ts, send-recovery-links.ts) with auth_migration_map; replacement of Clerk webhooks with auth.users triggers (on_auth_user_created, on_auth_user_updated); server auth swap (~48 files: middleware, authenticate.ts, supabase-server.ts, syncUser.ts, ~40 API routes); admin API replacements for clerkClient (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 ~20 public tables; full Clerk package/config removal and test-mock cleanup.

Excluded: Renaming clerk_id columns or adding a first-class user_id UUID; Clerk to Supabase coexistence; password migration for existing Clerk users (recovery links used); changes to business logic or public API contracts.

1. USER PAIN POINT & SOLUTION

Current State (Without Feature)

Users authenticated with Clerk and were mirrored into app_users via Svix webhooks (/api/clerk/user-created, /api/clerk/user-updated). Server routes used Clerk helpers (auth(), currentUser() from @clerk/nextjs/server); admin routes used clerkClient. Supabase was the primary database while Clerk was the auth provider, creating dual availability and no reliable DB-level way to resolve caller identity against auth.uid(). RLS policies carried six inconsistent identity-resolution conventions.

Pain Point

Emotional: Support uncertainty during migration (difficult to distinguish Clerk session vs Supabase row/login failures).

Functional: Profile provisioning depended on webhook delivery. Deleting webhooks before cutover drops welcome emails for direct sign-ups and profile sync for existing users (syncUserIfNotExists only provisions new rows). No robust retry path.

Business Impact: Duplicate per-seat identity cost, webhook fragility, and inconsistent RLS enforcement increasing risk of cross-tenant access if ownership checks missed.

Future State (With Feature)

Supabase Auth is the sole identity provider. User creation/profile mirroring occurs inside the database via on_auth_user_created and on_auth_user_updated triggers on auth.users (no external webhook delivery). RLS resolves callers canonically via auth.uid(); auth_migration_map preserves old Clerk IDs for reversibility. Admin operations use Supabase Admin; client flows use Supabase Auth.

Marketing Hook

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

Marketing Hook

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

2. 4D FRAMEWORK MAPPING

Diagnose

Preflight (preflight-app-users.ts) + FK introspection RPC (get_fk_on_update_action) identified (1) Clerk users with no matching app_users row, (2) FK columns referencing app_users.clerk_id without ON UPDATE CASCADE (unsafe for in-place rewrite).

Design

Phased reversible sequence: DB provisioning + backfill first (Clerk still live), server swap next, RLS sweep sequenced after cutover (latent under service-role; not blocking), Clerk decommission last. Kept clerk_id column name everywhere to avoid broad query rewrites.

Develop

auth_migration_map persisted. Policies: UUID FKs resolved via col = (SELECT id FROM app_users WHERE clerk_id = auth.uid()::text); *_clerk_id and TEXT creator_id resolved as col = auth.uid()::text. New shared admin helper module, new test helper, Clerk mocks replaced with Supabase equivalents.

Deliver

Nine merged PRs, five SQL migrations, three operational scripts, one shared admin helper module, one new test helper, +424 test lines. @clerk/nextjs removed from package.json; no Clerk code paths remain at runtime.

3. USER FLOWS

Entry Point

Authentication entry points: /sign-in, /sign-up, forgot password, OAuth callback, access-revoked, and admin management surfaces (previously clerkClient).

Success Criteria

New users sign up/in via Supabase Auth and have app_users provisioned via DB triggers.
Existing migrated users authenticate via Supabase Auth (recovery links used to set new passwords where applicable).
Admin operations (invite/delete/reauth/sync/enrollment) succeed via Supabase Admin with role checks.
RLS restricts access using auth.uid(); cross-tenant access blocked by DB policies + route ownership checks.
No Clerk runtime dependencies remain.

Main Flow (Happy Path)

Sign Up (email/password): Submit /sign-up → Supabase Auth creates auth.users → DB trigger on_auth_user_created creates/links app_users → session established → redirect to app.
Sign In: Submit /sign-in → Supabase Auth validates → session cookie set → app loads with user-scoped client.
Forgot Password (OTP): Request reset → Supabase sends OTP/email → verification + password set via Supabase Auth flows.
OAuth: Callback handled via Supabase Auth → user provisioned via triggers → redirect.
Sign Out: Client calls Supabase sign-out → session cleared → redirect to /sign-in.
Admin Management: Server routes use Supabase Admin helpers with authenticateUserWithRole/authenticateAgencyAdmin/authenticateAgencyMember.
Access Revoked: Invalid/blocked session → routed to /access-revoked (Supabase-based flow).

Edge Cases

No data: Clerk users with no matching app_users detected by preflight-app-users.ts; handled via backfill/map.
API error: Server returns 401/403/404 via ApiResponseHelper; errors surfaced consistently.
Permission denied: RLS + route ownership/role checks enforce access; denied requests return 403.

Decision Points

IF FK references app_users.clerk_id have ON UPDATE NO ACTION → DO NOT run in-place rewrite until remediated (preflight gate).
IF user exists in Clerk but missing in app_users → RECORD in preflight and handle per backfill plan.
IF RLS policy cannot be expressed cleanly with auth.uid() → USE canonical pattern: UUID FKs via subquery to app_users.clerk_id = auth.uid()::text; TEXT *_clerk_id/creator_id use auth.uid()::text directly.
IF cutover timing → RLS sweep runs AFTER cutover (not blocking).

4. INFORMATION ARCHITECTURE

Primary Information (Always visible)

Single source of identity: auth.users (Supabase Auth, GoTrue)
Join key preserved: app_users.clerk_id (TEXT) stores Supabase Auth UUIDs post-backfill
Reversibility: auth_migration_map(old_clerk_id, new_auth_id, ...)
Canonical auth resolution: auth.uid() in RLS and server auth context
Auth boundary: Supabase Auth session cookies; user-scoped client via createUserScopedSupabaseClient() (RLS-respecting); service-role client reserved for admin/cross-tenant ops with explicit checks

Secondary Information

DB triggers: on_auth_user_created, on_auth_user_updated on auth.users (replace Clerk webhooks)
Migration scripts: preflight-app-users.ts, backfill-supabase-auth.ts, send-recovery-links.ts
Admin helpers: shared Supabase Admin helper module (replaces clerkClient)
Client auth: sign-in/sign-up, forgot-password (OTP), access-revoked, OAuth callback, user menu

Tertiary Information (Hidden until needed)

Phase PRs (#812, #820, #821, #822, #823, #824, #825, #827, #826)
5 SQL migrations, test changes (+424 lines), removed Clerk deps/config
FK introspection via get_fk_on_update_action() RPC and preflight reports

Actions

Primary CTA:

Migrate identity via validated backfill (preflight → backfill → recovery links → cutover)

Secondary Actions:

Apply RLS sweep post-cutover (idempotent)
Decommission Clerk (Phase 6) after validation

5. WIREFRAMES

[Wireframe status]

Not applicable for this auth migration. UI changes implemented in existing auth pages/components.

Key Screens:

/sign-in (Supabase Auth sign-in)
/sign-up (Supabase Auth sign-up)
/forgot-password (OTP-based reset)
/access-revoked (Supabase-based)
OAuth callback handling

Annotations:

Auth flows use Supabase Auth UI/handlers; no major layout changes.
Session management switched from Clerk to Supabase Auth.

6. WIREFLOWS

Clerk (legacy) → [Phase 0-1: Prep/Triggers/Backfill] → [Phase 2: Server swap] → [Phase 4: Client swap] → [Cutover to Supabase Auth] → [Phase 5: RLS sweep (post-cutover)] → [Phase 6: Clerk decommission]

7. PROTOTYPE

[Prototype status / link]

Not applicable. Auth flows validated via implementation and tests.

How to test:

Sign up new user via /sign-up (triggers create app_users)
Sign in with migrated user (post-backfill/recovery)
Forgot password OTP flow
Admin operations (invite/delete/sync) as agency admin
Verify RLS: user-scoped reads return only own data

8. BACKEND SCHEMA

Database Tables

Core changes:

app_users.clerk_id (TEXT): retained as join key; post-backfill stores Supabase Auth UUIDs (auth.users.id)
auth_migration_map (audit): old_clerk_id (Clerk) → new_auth_id (Supabase Auth UUID) with metadata

-- auth_migration_map (representative)
 
auth_migration_map (
  old_clerk_id TEXT PRIMARY KEY,
  new_auth_id UUID NOT NULL REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT now()
)

Indexes

auth_migration_map(new_auth_id)
app_users(clerk_id) (existing, retained)

Constraints

FK considerations: FKs referencing app_users.clerk_id must allow ON UPDATE (prefer ON UPDATE CASCADE) for safe in-place rewrite; detected via get_fk_on_update_action() in preflight.

9. API ENDPOINTS

Endpoint 1:

Various auth/admin routes (migrated) - server-side auth context switched to Supabase Auth

Purpose: Replace Clerk auth context with Supabase session; preserve ownership/role checks

Auth: Supabase Auth session (via user-scoped client or session validation)

Response 200:

Standard API responses per ApiResponseHelper

Response 401:

{ success: false, error: 'Unauthorized', status: 401 }

Endpoint 2:

Admin management routes (previously using clerkClient)

Purpose: Admin operations (invite, delete, reauth, sync, enrollment) via Supabase Admin

Auth: Requires authenticated user with appropriate role (agency admin/platform admin)

Response 200:

Success payload per operation

Response 403:

{ success: false, error: 'Forbidden', status: 403 }

Notes: Service-role usage requires explicit ownership/role checks before queries on user-supplied ids (see Section 12).

10. DATA REQUIREMENTS

Frontend Needs

Supabase Auth session state (user, session)
Access to user-scoped client for RLS-respecting requests
Auth forms state (sign-in/sign-up/forgot-password)

API Calls Frontend Will Make

Supabase Auth client calls (signIn, signUp, signOut, resetPassword, etc.)
App API calls using authenticated session

Caching Strategy

Supabase Auth session cached by client SDK
No Clerk-specific caching; follow existing app caching patterns

11. PERFORMANCE CONSIDERATIONS

Database Optimization

auth_migration_map indexed by new_auth_id
app_users.clerk_id indexed (retained)
RLS policies optimized to use canonical auth.uid() resolution patterns

Query optimization notes

UUID FK resolution uses subquery to app_users by clerk_id = auth.uid()::text (indexed lookup on app_users.clerk_id)
TEXT *_clerk_id/creator_id direct comparison to auth.uid()::text

Caching Strategy

Session validation via Supabase (client-side caching)
Avoid N+1 in policy subqueries where possible

API Response Time

Target: Parity with pre-migration (no degradation). DB triggers execute synchronously on auth.users changes (minimal overhead).

12. SECURITY & AUTHORIZATION

Who can access this feature?

Creator: [?]

Reviewer: [?]

Learner: [?]

Admin roles (Agency Admin/Platform Admin): [?] for admin operations

Authorization Logic

User-scoped reads: via createUserScopedSupabaseClient() + RLS (auth.uid())
Service-role ops: explicit checks required (verifyContentOwnership, verifyAgencyResourceOwnership, authenticateUserWithRole/authenticateAgencyAdmin/authenticateAgencyMember)
Admin operations gated by role helpers; identity verified against Supabase session

Data Validation

Supabase Auth enforces email/password/OTP validation
Server routes validate inputs; ownership checks before service-role queries on user-supplied ids
RLS enforces tenant isolation at DB level

13. ERROR HANDLING

Common Errors

401 Unauthorized: Session missing/invalid → return ApiResponseHelper.unauthorized; client may redirect to /sign-in or /access-revoked

404 Not Found: Resource not found or ownership check fails (verifyContentOwnership returns 404)

422 Validation Error: Input validation failures → 422 with field errors

500 Server Error: Configuration/session client creation failures, unexpected DB errors

14. TESTING CHECKLIST

Happy Path

☐ New user sign-up creates auth.users + app_users via trigger

☐ Existing migrated user signs in successfully

☐ Forgot password (OTP) flow completes

☐ Admin invite/delete/reauth/sync succeeds

☐ Sign-out clears session

☐ OAuth callback provisions user

Edge Cases

☐ Preflight detects users missing app_users

☐ Preflight flags FK without ON UPDATE CASCADE

☐ Access revoked path works for invalid sessions

☐ RLS blocks cross-tenant access

☐ Service-role misuse prevented by ownership checks

☐ Trigger handles user updates via on_auth_user_updated

15. OPEN QUESTIONS

For Frontend:

None blocking. Client auth flows implemented per Supabase Auth patterns.

For Backend:

Confirm RLS sweep timing applied post-cutover in all environments (Phase 5)
Verify recovery link distribution completed for all migrated users

16. OUT OF SCOPE (v1.1+)

Not building in Migration Window:

Renaming clerk_id to user_id (would ripple entire codebase)
Adding first-class user_id UUID column
Password migration for existing Clerk users
Multi-tenant Clerk → Supabase coexistence
Changes to business logic/API contracts

Why: Kept migration scoped to auth layer swap to minimize blast radius; column rename deferred.

17. SUCCESS METRICS

How will we know this feature is successful?

Zero Clerk runtime dependencies (@clerk/nextjs removed; no Clerk code paths)
100% user provisioning via auth.users DB triggers post-cutover
RLS normalized to auth.uid() across ~20 tables (Phase 5 applied)
All auth flows (sign-in/sign-up/forgot-password/OAuth/admin) passing
No increase in auth failure rates post-cutover
Admin operations parity with Clerk-based flows

18. DEPENDENCIES

This feature depends on:

Supabase Auth (GoTrue) configuration in all environments
DB migrations for triggers, auth_migration_map, RPCs (get_fk_on_update_action)
Environment variables for Supabase (anon/service role) configured correctly
Preflight/backfill/recovery scripts executed in correct order

These features depend on this:

All authenticated routes (now rely on Supabase session + RLS)
Admin management surfaces (now use Supabase Admin)
User-scoped data access (RLS enforced via auth.uid())

19. TIMELINE & OWNERSHIP

Backend: Platform/Auth Team (clydetims) - DB provisioning, triggers, server swap, admin ops, RLS sweep - Complete

Frontend: Platform Team - Client UI swap (sign-in/up, access-revoked, forgot-password, OAuth, user menu) - Complete

QA: Platform Team - Auth flows, RLS, admin ops, regression - Complete

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)

20. HANDOVER CHECKLIST (Completion Required)

☐ No Clerk runtime deps: @clerk/nextjs absent; zero Clerk imports/usages

☐ Triggers active: on_auth_user_created and on_auth_user_updated enabled on auth.users in all envs

☐ Preflight/backfill: preflight-app-users.ts report reviewed; backfill-supabase-auth.ts executed; auth_migration_map populated/audited

☐ Recovery comms: send-recovery-links.ts executed/scheduled for users needing password setup

☐ RLS sweep applied post-cutover (Phase 5 #827); policies validated

☐ Admin ops verified (invite/delete/reauth/sync/enrollment) via Supabase Admin

☐ Client auth flows verified (sign-in/sign-up/OAuth/forgot-password/OTP/sign-out/access-revoked)

☐ Server swap verified (middleware, authenticate, supabase-server, syncUser, key API routes)

☐ Monitoring in place (trigger errors, auth failure rate, RLS 403 spikes)

☐ Security review: service-role usage audited; ownership checks present on user-supplied ids

☐ Documentation updated (this handover doc)