Database Schema

LostChurn runs on a SpacetimeDB module — a distributed, transactional database with built-in reducers (server-side functions). The module contains 74 tables, 44 views, 26 reducers, and 5 procedures that power the entire payment recovery pipeline.

Entity Relationship Diagram

The diagram below shows the 10 core tables and their foreign-key relationships:

Retry Pipeline Flow

Once a webhook is received, LostChurn runs the following orchestration:

Key Design Decisions

Decline Code Taxonomy

The decline_code table contains 316 decline codes across 18 payment processors (PSPs). Each code carries:

• category — broad failure type (e.g., insufficient_funds, card_expired)
• subcategory — fine-grained reason for routing decisions
• retryable — boolean flag that gates whether a retry_attempt is ever created

Enrollment-Based Campaign Tracking

Rather than hardcoding retry logic per PSP, LostChurn uses a campaign → enrollment model. Each payment_event is enrolled into a merchant-configured campaign with multiple stages. The enrollment.stage field tracks progress through retry + dunning sequences.

Email Suppression

The email_suppression table enforces hard bounce suppression. Any email that hard-bounces via the Resend webhook is permanently suppressed to protect deliverability scores and comply with Gmail/Yahoo 2024 bulk sender requirements.

Webhook Delivery Queue

The webhook_delivery table acts as an outbox for merchant-configured webhooks. A cron reducer scans for status = 'queued' rows and fires them, providing at-least-once delivery semantics.