SermonWise auth: signup, email confirmation, OAuth

Why this doc exists

On 2026-05-11 we hit a 6-PR debugging odyssey because the assumed architecture (Supabase PKCE email confirmation → /auth/callback → exchangeCodeForSession → land on /sermons/app) silently broke for any user who clicked the email link from a different browser, profile, or device than where they originated the signup. The PKCE code_verifier cookie was missing in that case, and exchangeCodeForSession failed with the classic Supabase error "PKCE code verifier not found in storage." Users ended up on /sermons/login?confirmed=1 instead of the dashboard, having to manually re-authenticate.

This doc captures the final architecture so the next agent doesn't go through the same archaeology.

High-level architecture (post-FA-106)

┌─── Password signup (sermonwise.ai/sermons/signup) ──────────────────────┐
│                                                                          │
│  SignupForm.tsx                                                          │
│    → supabase.auth.signUp({                                              │
│        email, password,                                                  │
│        options: {                                                        │
│          emailRedirectTo:                                                │
│            "https://sermonwise.ai/auth/callback?next=/sermons/app        │
│             &flow=signup",                                               │
│          data: { app_source: "sermon_starter", ... }                     │
│        }                                                                 │
│      })                                                                  │
│    → Supabase creates user, sends confirmation email                     │
│                                                                          │
│  Confirmation email template (Supabase Dashboard → Auth → Email          │
│  Templates → "Confirm signup"):                                          │
│                                                                          │
│      <a href="{{ .RedirectTo }}&token_hash={{ .TokenHash }}              │
│                &type=email">Confirm your email</a>                       │
│                                                                          │
│  ────────────────────────────────────────────────────────────────       │
│  CRITICAL: do NOT use {{ .ConfirmationURL }}                            │
│                                                                          │
│  {{ .ConfirmationURL }} routes through                                   │
│  auth.churchwiseai.com/auth/v1/verify, which forwards back to           │
│  /auth/callback with a PKCE `?code=...` query param. That code           │
│  exchange requires the original browser's `code_verifier` cookie.       │
│  Cross-profile / cross-browser / cross-device users don't have          │
│  that cookie, and the flow silently falls through to                     │
│  /sermons/login?confirmed=1 — forcing manual sign-in.                   │
│                                                                          │
│  The {{ .TokenHash }} variant uses Supabase's verifyOtp endpoint         │
│  which is purely token-based and does NOT need any browser-stored        │
│  state. Works cross-profile, cross-browser, cross-device.               │
│  ────────────────────────────────────────────────────────────────       │
│                                                                          │
│  User clicks link →                                                      │
│    sermonwise.ai/auth/callback?next=/sermons/app&flow=signup             │
│      &token_hash=pkce_XXX&type=email                                     │
│                                                                          │
│  /auth/callback/route.ts (Node Lambda)                                   │
│    → sees `token_hash` query param (line ~205)                           │
│    → redirects to /auth/confirm?token_hash=...&type=email&next=...       │
│                                                                          │
│  /auth/confirm/page.tsx ('use client')                                   │
│    → supabase.auth.verifyOtp({ token_hash, type: 'email' })              │
│    → no code_verifier cookie needed                                      │
│    → session established                                                 │
│    → window.location.href = next (=/sermons/app)                         │
│                                                                          │
│  /sermons/app (Next.js page)                                             │
│    → posthog.capture('first_app_visit') gated by                         │
│      POST /api/sermons/log-first-visit (server-atomic)                   │
│    → ALSO fires signup_email_confirmed (server-side) as the              │
│      canonical PostHog event for the funnel — see below                  │
└──────────────────────────────────────────────────────────────────────────┘

┌─── Google OAuth signin (any page with "Continue with Google") ──────────┐
│                                                                          │
│  SignupForm.tsx / SigninForm.tsx                                         │
│    → supabase.auth.signInWithOAuth({                                     │
│        provider: 'google',                                               │
│        options: { redirectTo: "https://sermonwise.ai/auth/callback...    │
│                                ?next=/sermons/app" }                     │
│      })                                                                  │
│    → Supabase redirects browser to Google                                │
│    → Google authenticates                                                │
│    → Google 302 back to sermonwise.ai/auth/callback?code=GOOGLE_CODE     │
│                                                                          │
│  /auth/callback/route.ts                                                 │
│    → no `token_hash`, has `code`                                         │
│    → supabase.auth.exchangeCodeForSession(code)                          │
│    → For OAuth, code_verifier is set the moment we initiate              │
│      signInWithOAuth — same page session, same browser, so the           │
│      cookie is reliably present. Cross-browser is NOT a concern          │
│      because the OAuth round trip stays in the same browser.            │
│    → success → redirect to /sermons/app                                  │
└──────────────────────────────────────────────────────────────────────────┘

signup_email_confirmed PostHog event — TWO fire paths

The funnel's signup_email_confirmed event has TWO independent fire paths, deduplicated server-side by PostHog using $insert_id = "<user_id>:signup_email_confirmed":

Path A: Postgres trigger (canonical)

• Where: on_auth_user_email_confirmed trigger on auth.users AFTER UPDATE OF email_confirmed_at
• Function: public.fire_signup_email_confirmed_to_posthog() (SECURITY DEFINER)
• When: Fires the moment Supabase sets email_confirmed_at (NULL → timestamp)
• Gate: Only fires if raw_user_meta_data.app_source LIKE 'sermon%' (SermonWise signups only)
• Properties: confirmed_via='auth_trigger', plus app_source, email_confirmed_at
• HTTP: Uses public.http_post() (the http extension is installed in public, NOT extensions — this caused PR #418's fix)
• Vault: Reads POSTHOG_PUBLIC_KEY from vault.decrypted_secrets
• Fault tolerance: Vault read errors + HTTP errors are caught with EXCEPTION WHEN OTHERS and logged as warnings only. Never blocks confirmation.
• Migration: churchwiseai-web/migrations/2026-05-11-signup-email-confirmed-auth-trigger.sql

Path B: log-first-visit fallback

• Where: POST /api/sermons/log-first-visit/route.ts
• When: Fires when user lands on /sermons/app for the first time (server-atomic via first_app_visit_at NULL → timestamp gate on profiles)
• Properties: confirmed_via='log_first_visit', plus app_source, email_confirmed_at
• Timestamp backdate: Passes user.email_confirmed_at as the PostHog event timestamp (top-level timestamp field, not in properties) so funnel timing is accurate even if log-first-visit runs minutes/hours/days after the actual confirm.

Why both?

Defense in depth. The trigger is the canonical source — fires the moment email is confirmed, even if user never returns to /sermons/app. The log-first-visit fallback catches cases where:

1. The trigger doesn't fire (e.g., verifyOtp's UPDATE path may differ from exchangeCodeForSession's)
2. The trigger fires but vault/HTTP fails silently
3. User signs up via OAuth (no app_source, trigger bails by design, but they need the event)

PostHog $insert_id ensures only ONE event per user lands in the funnel regardless of which path delivered it.

Critical config: Supabase email template

The email template MUST use {{ .TokenHash }}, not {{ .ConfirmationURL }}. The default Supabase template uses {{ .ConfirmationURL }} and is the wrong choice for SSR apps with non-PKCE-friendly user populations.

Where to set it: Supabase Dashboard → Authentication → Email Templates → "Confirm signup" → Message body.

The correct template:

<h2>Confirm your signup</h2>
<p>Follow this link to confirm your user:</p>
<table width="100%" cellpadding="0" cellspacing="0" border="0">
  <tr>
    <td align="center" style="padding:0 0 24px;">
      <table cellpadding="0" cellspacing="0" border="0">
        <tr>
          <td align="center" bgcolor="#D4AF37" style="border-radius:6px;">
            <a href="{{ .RedirectTo }}&token_hash={{ .TokenHash }}&type=email"
               target="_blank"
               style="display:inline-block; padding:14px 32px; color:#ffffff; text-decoration:none; font-family:Arial,Helvetica,sans-serif; font-size:16px; font-weight:bold; border-radius:6px; line-height:1.2;">
              Confirm your email
            </a>
          </td>
        </tr>
      </table>
    </td>
  </tr>
</table>
<p>If the button doesn't work, copy and paste this URL into your browser:</p>
<p>{{ .RedirectTo }}&token_hash={{ .TokenHash }}&type=email</p>

If an agent reverts this template to {{ .ConfirmationURL }}, cross-profile signups will silently break. Always re-check this template if FA-106 symptoms reappear (users complain about having to manually sign in after email confirm).

Rules for future agents

1. DO NOT change signUp()'s emailRedirectTo to drop the flow=signup query param. The middleware in src/middleware.ts (line ~44 "Catch Supabase PKCE ?code= on any path") uses it to route fallback flows correctly.

2. DO NOT remove the token_hash handling in /auth/callback/route.ts (lines ~205–217). That's what bridges Supabase's email-template URL back to our /auth/confirm client page.

3. DO NOT switch the Supabase email template back to {{ .ConfirmationURL }}. Cross-profile signups will break silently. The pre-FA-106 behavior (user lands on /sermons/login?confirmed=1) felt like working code but was a degraded UX that founder caught.

4. DO NOT remove the app_source LIKE 'sermon%' gate from the DB trigger without thinking through other auth.users consumers (church admin, ITW, ShareWiseAI all share auth.users). Each product needs its own analytics event.

5. DO NOT bypass the $insert_id dedupe in either fire path. Without it, every magic-link login could re-fire signup_email_confirmed, polluting the funnel.

6. If confirmed_via=auth_trigger stops appearing in PostHog and only log_first_visit shows up, the DB trigger is broken — check vault has POSTHOG_PUBLIC_KEY, check public.http_post still exists, check pg_proc for the function definition.

Verification (manual, ~3 min)

Sign up with john+verifyN@churchwiseai.com from one browser/profile. Click the confirmation link from a DIFFERENT browser/profile (or a phone). You should land directly on /sermons/app signed in — no manual sign-in form.

Query verification:

SELECT
  email,
  email_confirmed_at,
  last_sign_in_at,
  EXTRACT(EPOCH FROM (last_sign_in_at - email_confirmed_at))::int AS signin_lag_seconds
FROM auth.users
WHERE email = 'john+verifyN@churchwiseai.com';

signin_lag_seconds = 0 (within a second of email_confirmed_at) means verifyOtp auto-signed-in. If it's null or much later, the flow broke.

PostHog verification:

curl -G "https://us.posthog.com/api/projects/297257/events/" \
  -H "Authorization: Bearer $POSTHOG_PERSONAL_API_KEY" \
  --data-urlencode "event=signup_email_confirmed" \
  --data-urlencode "after=YYYY-MM-DDTHH:MM:SSZ"

Look for confirmed_via property — either auth_trigger (trigger won the race) or log_first_visit (fallback). Either is acceptable; both means dedup is working.

Related references

• churchwiseai-web/migrations/2026-05-11-signup-email-confirmed-auth-trigger.sql — trigger DDL
• memory/project_supabase_email_confirm_not_callback.md — agent memory of the discovery
• memory/feedback_supabase_mcp_apply_migration_blocks_auth.md — agent memory of the MCP gotcha
• PRs #398, #409, #410, #412, #414, #417, #418 — the iteration trail
• Supabase docs: https://supabase.com/docs/guides/auth/server-side/email-based-auth-with-pkce-flow-for-ssr (note: this doc covers the PKCE flow; we deliberately chose token_hash for cross-browser robustness)