MBR CRM — Data Flow Diagrams

All flows assume Supabase as the system of record, Stripe for payments, and the native QBO Stripe Sync for bookkeeping. See SPEC.md for entity detail.

sequenceDiagram
    autonumber
    actor U as User
    participant Web as mybetterrates.com (SvelteKit)
    participant Auth as Supabase Auth
    participant DB as Supabase Postgres
    participant HE as Hassle Threshold Engine
    participant Stripe
    participant Email as Resend

    U->>Web: Submits email + optimization target
    Web->>Auth: magic link signup
    Auth-->>U: magic link email
    U->>Auth: clicks magic link
    Auth->>DB: insert users (tier=tier_1, consent_marketing=true)
    DB->>DB: kyc_records row (stage=2, stage2_consent_at=now)
    Web->>U: Set Hassle Threshold screen
    U->>Web: enters $150 threshold
    Web->>DB: update users.hassle_threshold_cad=150, consent_kyc_stage=3
    DB->>HE: trigger → register user in engine
    U->>Web: Upgrade to Tier 2 ($10/mo)
    Web->>Stripe: create subscription
    Stripe-->>Web: subscription_id
    Stripe->>DB: webhook: subscription.created
    DB->>DB: insert subscriptions (tier=tier_2, status=active)
    DB->>Email: trigger welcome email w/ first benefit projection
    Email-->>U: "You're in. We'll only email when it matters."

Critical points:

No Stage 3 financial data captured at signup; only at first alert engagement.
Subscription creation is a separate step — Tier 1 → Tier 2 never auto-charges.
Welcome email sets expectation: silence is the feature.

Flow B — Rate Alert → Transfer → Revenue (the core loop)

flowchart TD
    A[rate-scanner.db<br/>rate_changes row] --> B[Hassle Threshold Engine<br/>cron every 6h]
    B --> C{Any user threshold<br/>exceeded?}
    C -->|No| Z[Stop]
    C -->|Yes| D[Insert rate_alerts<br/>Supabase]
    D --> E[Edge Function:<br/>send email via Resend]
    E --> F[User receives alert]
    F --> G{User acts?}
    G -->|Ignore| H[Update rate_alerts.status=ignored]
    G -->|Click| I[Web: Transfer wizard]
    I --> J{KYC stage >= 3?}
    J -->|No| K[Progressive KYC form<br/>Stage 2→3]
    J -->|Yes| L[Insert transfers<br/>status=initiated]
    K --> L
    L --> M{Tier 3?}
    M -->|No DIY| N[Generate pre-filled PDF<br/>user completes manually]
    M -->|Yes| O[DocuSign envelope<br/>1-Click path]
    N --> P[User confirms completed]
    O --> P
    P --> Q[Update transfers<br/>status=completed]
    Q --> R[Institution pays referral fee<br/>via Stripe payout or manual]
    R --> S[Insert revenue_events<br/>type=institutional_referral]
    S --> T[DB trigger: pledge_amount = 0.5 × amount]
    T --> U[Edge Function:<br/>route 50% to CanadaHelps]
    U --> V[Insert canadahelps_receipt_id]
    S --> W[Stripe → QBO Sync<br/>book the transaction]
    Q --> X[Upsert financial_snapshots<br/>cumulative_benefit += annual_benefit]
    X --> Y[Queue Annual Return<br/>on Better email]

Critical points:

The 50% pledge is enforced at the database layer — revenue_events insert trigger rejects any row where pledge_amount_cad ≠ amount × 0.50. Cannot be bypassed in app code.
If institutional referral arrives asynchronously (weeks later), the transfers row carries projected_annual_benefit_cad so the Net Worth Thermometer updates at transfer-complete time, not fee-received time.
Email sending decoupled via Edge Function — alert delivery never blocks DB writes.

Flow C — WealthCare 50 Corporate Partner Onboarding

sequenceDiagram
    autonumber
    actor HR as HR Contact
    participant LP as wc50.mybetterrates.com
    participant DB as Supabase
    participant CH as CanadaHelps
    participant Email as Resend
    participant Dash as CSR Dashboard

    HR->>LP: Fills partner signup form<br/>(org, contact, employee count)
    LP->>DB: insert wealthcare50_partners (type=corporate, active=true)
    DB->>DB: Generate csr_dashboard_token (uuid)
    DB->>CH: Trigger Welcome Bomb donation<br/>(fixed amount × employee_count tier)
    CH-->>DB: receipt_id
    DB->>DB: insert revenue_events (type=wc50_licensing or welcome_bomb)
    DB->>Email: Send welcome email to HR<br/>with CSR dashboard magic link
    Email-->>HR: "Your Cancer Impact Grant is live"
    HR->>Dash: Opens dashboard (token-authenticated)
    Dash->>DB: Reads partner_id aggregates<br/>(cancer_impact_grant_cad, wellness_score)
    Dash-->>HR: Live CSR metrics
    HR->>LP: (Optional) Uploads employee roster CSV
    LP->>DB: Insert partner_employees rows
    DB->>DB: Match on email domain → link to future users

Critical points:

CSR dashboard is a token-authenticated read-only view — no HR login/password until scale demands it.
Employee list import is optional; email-domain matching backfills links as employees sign up naturally.
Welcome Bomb triggers the partner’s first dopamine hit before any employee signs up — this is the hook.

Flow D — Tier 2 Refund (Guaranteed Benefit)

flowchart TD
    A[Daily cron: refund_eligibility_job] --> B[Query subscriptions<br/>WHERE tier=tier_2<br/>AND current_period_end<br/>is 12-month anniversary<br/>AND status=active]
    B --> C[For each candidate user:<br/>Count rate_alerts<br/>WHERE status IN acted_on,fired<br/>during period]
    C --> D{Alerts fired > 0?}
    D -->|Yes| E[subscriptions.refund_eligible=false<br/>renew or cancel normally]
    D -->|No| F[subscriptions.refund_eligible=true<br/>status=refund_pending]
    F --> G[Email: 'You're owed a refund —<br/>take it or tribute it?']
    G --> H{User choice}
    H -->|Refund| I[Stripe refund subscription amount]
    H -->|Tribute| J[Route refund amount to CanadaHelps<br/>in name of cancer50_tribute_name]
    H -->|No response 14d| I
    I --> K[Insert revenue_events<br/>type=subscription, amount=-120<br/>pledge_amount=-60 OR 0]
    J --> L[Insert revenue_events<br/>type=subscription, amount=0<br/>pledge_amount=120<br/>+ canadahelps_receipt_id]
    K --> M[Stripe → QBO Sync<br/>books the refund]
    L --> M
    M --> N[Update subscriptions.status=refunded]
    N --> O[Generate shareable Tribute graphic<br/>if Tribute path chosen]

Critical points:

Refund eligibility is daily batch, not real-time. Cron job + query is simpler and auditable.
The Refund-to-Tribute path makes a refund into a revenue_event with a negative gross but a positive pledge — the pledge trigger accommodates this specific case (refund-to-cancer is NOT 50%; it’s 100% to cancer, flagged by type=subscription + negative amount + redirected_to_cancer=true).
Trigger exception: enforce_50_pct_pledge() must allow refund_redirected_to_cancer=true rows to bypass the 50% rule. Documented as the only exception.
QBO entry must correctly show the refund as an expense, not double-book as revenue — Stripe Sync handles this natively.

Cross-flow integration summary

Touchpoint	Owned by	Used in flow	Automation
rate-scanner.db	Local cron	B	✅
Supabase Postgres	SSoT	All	✅
Stripe	Payments	A, B, D	✅ (native webhooks)
CanadaHelps API	Pledge routing	B, C, D	⚠ pending API confirmation
QBO Stripe Sync	Bookkeeping	A, B, D	✅ (native app)
Resend	Transactional email	A, B, D	✅
DocuSign	Signature packets	B (Tier 3)	✅ (Phase B→Next)
CSR Dashboard	Read-only view	C	✅

MBR CRM — Data Flow Diagrams

MBR CRM — Data Flow Diagrams

Flow A — New User Signup (Tier 1 → Tier 2 upgrade)

Flow B — Rate Alert → Transfer → Revenue (the core loop)

Flow C — WealthCare 50 Corporate Partner Onboarding

Flow D — Tier 2 Refund (Guaranteed Benefit)

Cross-flow integration summary