SermonWise PostHog Funnel — Live Verification

What happened

Founder ran the full 5-step click-through against https://sermonwise.ai at 2026-05-11 12:43–12:53 UTC. Used coupon SWVERIFY100 (100% off, one-shot, max_redemptions=1). All 5 customer-facing steps completed end-to-end. Dashboard showed "Welcome to SermonWise Pro! You now have 15 sermon and homily starters per month" — good UX, confirms PR #381 (poll ?upgraded=1 to bridge webhook race) + PR #382 (reset usage on upgrade) are working.

The test user is intentionally retained so the founder can exercise Pro-tier derivative features (small group guide, children's sermon, social media posts). Cleanup deferred per memory/feedback_test_user_reuse_for_derivatives.md.

Event-by-event ledger (queried live from PostHog)

#	Event	Fired?	Time (UTC)	distinct_id	Properties
1	signup_form_submitted	✅	12:43:52	f49e5acd-…	app_source='sermon_starter', tradition='methodist'
2	signup_email_confirmed	❌ MISSING	—	—	—
3	first_app_visit	✅	12:45:24	f49e5acd-…	app_source='sermon_starter'
4	first_sermon_generated	✅	12:48:32	f49e5acd-…	lens_name='Wesleyan'
5	upgrade_clicked	✅	12:52:35	f49e5acd-…	product='sermon_pro', billing='monthly'
6	upgrade_completed	✅	12:53:08	f49e5acd-… (server-side)	product='sermon_pro', billing='monthly'

Identity stitching worked perfectly. posthog.identify() ran at 12:43:52 right after signup_form_submitted. Every subsequent event uses the Supabase user UID as distinct_id. No anonymous→identified split, no orphan events.

Tradition→lens chain end-to-end: form submitted tradition='methodist' → handle_new_user() trigger mapped to theological_lens_id=5 → wizard pre-populated with Wesleyan lens → first_sermon_generated captured lens_name='Wesleyan'. ✅

Stripe webhook (the part that actually charges money)

3 events delivered to stripe_webhook_inbox within 1 second of checkout, all status='succeeded' on first attempt:

Stripe event ID	Type	stripe_created	succeeded_at	Latency
evt_1TVtKMFaoK5IPzNoLer61Muu	checkout.session.completed	12:52:54	12:53:08	14s
evt_1TVtKMFaoK5IPzNoNsqSvcBn	customer.subscription.created	12:52:53	12:53:07	14s
evt_1TVtKNFaoK5IPzNoa23KUtPl	invoice.payment_succeeded	12:52:53	12:53:08	15s

Zero last_error. Zero retries. The webhook inbox pattern is doing exactly what it was designed for. The 2 open P0 churchId=undefined errors from 2026-05-08/09 are not SermonWise — verified by this run completing cleanly without producing a fresh one. Those P0s belong to CWA Pro Website signups (premium_churches flow) and need separate triage.

Database state after test

profiles for f49e5acd-78ad-455e-b5e0-51ba912ea12d:
  email                  : john+testuser@churchwiseai.com
  email_confirmed_at     : 2026-05-11 12:44:39 UTC
  subscription_tier      : sermon_pro ✅
  stripe_customer_id     : cus_UUt60f66TkPteP ✅
  first_app_visit_at     : 2026-05-11 12:45:24 UTC ✅ (server-side gate worked)
  app_source             : churchwiseai ❌ should be sermon_starter

sermons:
  count: 1, last_at: 2026-05-11 12:48:31 UTC ✅

user_app_subscriptions (slug='sermonwise'):
  no row — verify if expected or if quota tracking should populate here

---

2 real bugs surfaced

Bug #1 — handle_new_user() trigger does not persist app_source

Symptom: profiles.app_source = 'churchwiseai' on a confirmed SermonWise signup.

Root cause: public.handle_new_user() (the on_auth_user_created trigger on auth.users) inserts into profiles but the INSERT column list omits app_source entirely:

INSERT INTO public.profiles (
  id, email, full_name, church_name, denomination,
  theological_lens_id, role, user_role, account_type, avatar_url
) VALUES (...)

So profiles.app_source falls back to the column default ('churchwiseai') regardless of what's in auth.users.raw_user_meta_data->>'app_source'. The SermonWise signup page correctly sets appSource='sermon_starter' in SignupForm.tsx, and the PostHog signup_form_submitted event correctly captures app_source='sermon_starter' (client-side, before trigger fires). But the persisted DB record is wrong.

Blast radius:

• All SermonWise signups (since app_source instrumentation shipped 2026-05-07) appear as app_source='churchwiseai' in the DB.
• Any downstream analytics that count signups from the DB by app_source (founder readiness tab, MailerLite drip selectors, segment-by-source reports) sees the wrong tag.
• PostHog event properties are correct — events are tagged sermon_starter. So PostHog funnel metrics are intact.

Fix: Add app_source to the trigger's INSERT, pulling from NEW.raw_user_meta_data->>'app_source' with 'churchwiseai' as fallback. Backfill the test user (+ any other SermonWise signups in the last 4 days). Migration goes in churchwiseai-web/migrations/. Should pair with a backfill UPDATE for any post-2026-05-07 profile rows where raw_user_meta_data->>'app_source' = 'sermon_starter'. Then verify on a fresh signup.

Bug #2 — signup_email_confirmed event never fired

Symptom: The 2nd of 6 funnel events did not appear in PostHog despite the founder clicking the email link and landing on /sermons/app (verified by first_app_visit firing 2 seconds later).

Root cause hypotheses (need diagnosis):

1. auth/callback/route.ts:233 only appends ?confirmed=1 when dest.startsWith('/sermons/'). If resolveRedirect() returned a non-/sermons/ dest first (e.g. /) and the user got there via a second hop, the flag was stripped.
2. The useEffect([confirmed]) at sermons/app/page.tsx:102 fires on mount only if searchParams.get('confirmed') === '1'. If Next.js client-side navigation replaced the URL after PKCE exchange (common for OAuth callbacks), the flag could be gone before the effect runs.
3. PostHog client initialization timing — if posthog?.capture() was a no-op because the SDK hadn't loaded yet at first render.

Blast radius:

• Funnel step "email confirmed" is invisible. You can't measure email-confirmation drop-off (the gap between signup_form_submitted and first_app_visit).
• The first_app_visit event already proves "user landed and started using the app," which is the more important signal. So this is degraded measurement, not broken UX.

Fix: Either (a) move the event to server-side firing inside auth/callback/route.ts after successful PKCE exchange (using capturePostHogServer() like upgrade_completed does — guaranteed delivery, no client race), or (b) instrument browser-side and find the redirect-chain issue. Server-side is the cleaner pattern given the precedent set by first_sermon_generated and upgrade_completed. Add a regression test in the existing sermonwise-posthog-funnel.contract.spec.ts.

---

What this means for "ready for paid spend"

Cleared for soft paid testing ($20–50/day): YES.

• All 5 business-critical events fire with correct properties.
• Conversion event (upgrade_completed) is 100% reliable — server-side via webhook, deduplicated.
• Stripe pipeline is clean.

Cleared for scale paid spend ($300+/wk per Phase 4 plan): AFTER:

1. Fix Bug #1 (handle_new_user app_source persistence) + backfill — so DB-side analytics by source is accurate.
2. Diagnose Bug #2 (signup_email_confirmed missing) — so you can measure email-confirmation drop-off.
3. Accumulate 7 days of clean funnel data after the fixes ship.

Bug #1 is a quick fix (one trigger update + backfill). Bug #2 is best fixed by moving to server-side capture pattern (15 minutes of code, same pattern already used by upgrade_completed). Both could ship in one PR by end of week.

Coupon state

• sw-funnel-verify-2026-05-11 — redeemed 1/1, valid: false going forward. Self-burned. No leak risk.

Registry updates

knowledge/tests/registry.yaml entry sermonwise-posthog-funnel:

• last_run: 2026-05-11T12:53:08Z
• founder_verified: 2026-05-11
• findings: block added with the 4 observations above

Founder action items (from this verification)

1. Ship Bug #1 fix — add app_source to handle_new_user() INSERT + backfill recent rows. Single migration. ~15 min.
2. Ship Bug #2 fix — move signup_email_confirmed to server-side auth/callback capture, like upgrade_completed. ~15 min.
3. Re-test — run one more click-through with a fresh test alias (john+verify@churchwiseai.com) to confirm both bugs are fixed. Use coupon SWVERIFY100's replacement (I'll create one when you're ready).
4. Then kick off the calibrated paid test from Phase 4.
5. When you're done exercising Pro features (small group, children's sermon, social), say "clean up test user" and I'll cancel the Stripe sub + delete the auth user/profile/sermons/PostHog Person.

Files referenced

• Original audit: knowledge/archive/sermonwise-2026-05-07/01-readiness-audit.md
• Launch plan: knowledge/narrative/sermonwise-launch-action-plan.md
• Re-score: knowledge/readiness/reports/sermonwise-20260511-v9.md
• Test registry: knowledge/tests/registry.yaml (entry sermonwise-posthog-funnel)
• PostHog query: posthog-funnel-query.json (project 297257, HogQL)
• Stripe coupon: sw-funnel-verify-2026-05-11 (live mode, burned)