← Back

Project deep dive

CABC Email Router

A production Stripe webhook processor and email routing system built for a real booster club — not a demo, not a tutorial. Designed, architected, and shipped to handle real financial transactions and automate purchase fulfillment notifications end to end.

Next.jsTypeScriptReactSupabasePostgreSQLResendStripeVercel
Admin access is passwordless — magic link protected

Event pipeline

Stripe Checkout

checkout.session.completed

Webhook Handler

record → idempotency guard

Route Match

product_id → recipient config

Email Dispatch

per-recipient + attempt log

Admin Dashboard

fulfillment tracking + CSV export

Overview

The Compass Athletic Booster Club needed a way to automatically route Stripe purchase notifications to the right people. A parent buys a spirit pack, a corporate sponsor submits a contribution, a student registers for an event — each of those needs to land in a different inbox, with the right context, immediately. No manual forwarding. No checking dashboards.

CABC Email Router sits between Stripe and the people who need to act. It ingests webhook events, normalizes messy customer data from a dozen possible Stripe sources, matches each purchase to a configured route, and dispatches templated emails to the right recipients — then tracks every attempt and surfaces everything in an admin dashboard built for non-technical operators.

The system handles real money and real people. That meant idempotency, auditability, and operational observability weren't afterthoughts — they were requirements from the start.

Core Features

What it actually does

Configurable email routing

Routes map Stripe product and price IDs to recipient email lists, subject templates, and branded message content. Adding a new product or reassigning who gets notified is a form submission in the admin — no code changes, no redeployment.

Stripe data normalization

Customer data arrives from Stripe fragmented across checkout custom fields, the customer object, customer_details, collected_information, and billing details on the payment intent. The system resolves the correct name, email, phone, business name, student name, and organization across all of them — using fuzzy label matching to handle variations like "customer name," "full name," and "buyer name" across different form designs.

Webhook idempotency

Events are recorded and processed in two separate phases. A unique constraint on stripe_event_id catches duplicates at the database layer before any processing begins. Purchase notifications deduplicate independently on stripe_checkout_session_id + stripe_price_id, so retries and replays never double-send.

Per-recipient attempt tracking

Every email send is recorded individually — recipient, subject, Resend message ID, status, and any error. A notification is only marked "sent" if every recipient succeeds. One failure marks the whole notification "failed" with enough detail to know exactly which address had issues.

Fulfillment workflow

Notifications carry two independent status tracks: delivery status (pending → sent / failed) and fulfillment status (new → in progress → fulfilled / blocked). Admins update fulfillment manually with notes after the purchase has been acted on.

Purchase reporting and CSV export

Report pages aggregate purchases by product with revenue totals, filter by date range and fulfillment status, and export filtered datasets to CSV. The same filter object drives both the query and the export URL — no duplicated logic.

Health monitoring

A health check page verifies all six required environment variables, surfaces the last successfully processed webhook event, and shows the last failed event with its error — enough to diagnose configuration problems without touching logs.

Magic link authentication

Admin access requires a verified email address in the admin_users table. Sign-in is passwordless — an email with a sign-in link, no credentials to manage. Supabase Auth handles the OTP flow with a route callback that supports both PKCE code exchange and token hash verification, covering first-time and returning users correctly.

Technical Highlights

Under the hood

Architecture

  • —Stripe webhook → two-phase record/process pipeline → route match → per-recipient email dispatch → attempt logging → fulfillment tracking
  • —Dual-status model separating delivery outcome from business fulfillment state
  • —Service client / user client separation in Supabase: service role for backend writes, anon key for auth-dependent operations
  • —revalidatePath cache invalidation on all admin mutations for fresh server-rendered data

Backend

  • —Single expanded Stripe API call per event: customer, payment_intent.latest_charge, and line_items.data.price.product resolved in one request
  • —Fuzzy custom field matching normalizes keys across any form design variation
  • —Customer name deduplication: if resolved name equals business name, returns null rather than repeating data
  • —Database-layer duplicate detection via Postgres error code 23505 — no pre-query needed
  • —Resend sender normalization handles accidental env var prefix duplication and quoted values

Admin UI

  • —Server Components throughout with server actions for mutations — no client-side data fetching
  • —Zod schema validation on every server action before any database write
  • —Raw Stripe event JSON viewer on notification detail pages for debugging
  • —Route editor with expandable inline forms — no page navigation required to manage configuration

Auth

  • —Supabase magic link with PKCE flow: code verifier stored in server action cookies for proper exchange at callback
  • —Callback route handles both ?code= (returning users) and ?token_hash=&type= (first-time confirmation) — two different URL formats Supabase sends depending on whether the user exists
  • —requireAdmin() enforces email allow-list check on every protected route after session verification

Engineering Challenges

Where it got interesting

01

Extracting clean customer data from Stripe's fragmented model

Stripe surfaces customer information in at least ten places across a checkout session, and the right value for "customer name" depends on which fields the merchant configured and which ones the customer filled in. The normalization layer resolves a deterministic priority order across custom fields (with fuzzy label matching), the Stripe customer object, session customer_details, collected_information, and billing details — handling deleted objects, polymorphic fields, and the edge case where a sole proprietor's customer name and business name are identical.

02

Webhook idempotency without pessimistic locking

Stripe can and does send duplicate events. The solution uses two layers: a unique constraint on stripe_event_id catches duplicate webhook deliveries immediately, and a separate unique constraint on session_id + price_id catches cases where the same purchase appears through different events. Both are enforced at the database layer — no pre-query, no race condition.

03

The two-path auth callback

Supabase sends different URL formats for first-time versus returning users: ?code= for users who already exist, ?token_hash=&type= for new accounts going through email confirmation. The original callback only handled one path. Discovering the discrepancy required reading Supabase audit logs to compare user_recovery_requested versus user_confirmation_requested events, then updating the callback to branch on whichever parameter is present.

04

PKCE verifier storage in a server action

signInWithOtp generates a PKCE code verifier that needs to be stored in a cookie so the callback can exchange the code. Called from a Next.js server action using a client with a no-op setAll, the verifier was silently lost — causing "no auth token" at the callback for any user who requested a new link. The fix required creating the Supabase client inline in the action with a setAll implementation that actually writes to the cookie store, which is valid in server actions even though it isn't in Server Components.

← Back to portfolio