CRM Architecture & Data Model Design

CRM Architecture & Data Model Design

Date: 2026-04-08

Background

MBR is entering Phase A→B (pre-launch). The business now has a working Rate Intelligence Engine, a deployed Benefit Snapshot widget, and an active daily pipeline. The next phase introduces real users: Tier 1 (free rate intelligence), Tier 2 (Guaranteed Benefit, $10/month), and Tier 3 (Concierge / 1-Click Transfer, premium).

A CRM and data architecture decision made wrong at Phase B creates painful migration at Phase C when WealthCare 50 Alliance corporate partners come on board. This task produces a definitive architecture decision and SPEC before any code is written.

This is an architectural design task — output is documentation, not code. Use Opus 4.6 with extended thinking if available.

MBR Context (read before designing)

• D:\FSS\KB\MBR\02_Strategy\Executive-Summary.md — business overview, 3 tiers, revenue model
• D:\FSS\KB\MBR\02_Strategy\Filter+Focus.md — all 73 rocks with priority ranking
• D:\FSS\KB\MBR\_WorkingOn\Projects\MBR-Business-Dev\PLAN.md — Phase A→B→C→Z roadmap
• D:\FSS\KB\MBR\_WorkingOn\Projects\rate-scanner\STATUS.md — what’s live and running
• /home/ta/projects/mbr/README.md — backend architecture overview and module map
• Existing code: /home/ta/projects/mbr/ (rate-scanner, hassle-engine scaffold, notifications scaffold, etc.)

Key Business Constraints

• Solo operator (Talbot) — no hired staff at Phase A/B; automation over manual processes
• Canada-only (regulatory, CDIC, provincial restrictions)
• Referral model only — not licensed financial advice
• QuickBooks Online for accounting
• No client data storage at Phase A for web widget (static, no auth) — but Tier 2/3 requires user records
• 50% of all profits pledged to cancer research (Cancer50Pledge) — this must flow through the data model

Tasks

1. Entity Model Design

Design the complete entity model for Phase B launch. For each entity, specify:

• Fields (name, type, required/optional)
• Relationships to other entities
• Which phase each entity is needed (A→B, B→Next, Phase C)
• Privacy sensitivity (PII, financial data, public)

Minimum entities to model:

• User — Tier (1/2/3), email, signup date, Hassle Threshold config, subscription status
• RateAlert — which alert was triggered, user threshold vs. market rate, triggered/ignored/acted-on, date
• Transfer — KYC stage (1-4), institution from/to, account type, amount, status, completion date, referral fee generated
• KYC — progressive trust architecture Stages 1-4 per the Staged KYC Trust Architecture (Filter+Focus #23): Stage 1 (form template only, no data stored), Stage 2 (name/email), Stage 3 (institution + account type), Stage 4 (full KYC)
• RevenueEvent — type (subscription, performance fee, institutional referral, broker referral), amount, 50% pledge amount, routing to CanadaHelps
• WealthCare50Partner — type (Corporate/Charity/Association), org name, contact, employee count, CSR dashboard data, Cancer Impact Grant status
• Cancer50Pledger — org name, pledge %, contact, Founding Pledger status (certificate number #1-50), public wall visibility
• FinancialSnapshot — per-user cumulative delta (Net Worth Thermometer), Financial IQ Score history, last updated
• Subscription — tier, billing date, payment method, refund eligibility (Guaranteed Benefit money-back guarantee logic)

2. CRM Tool Selection

Evaluate and recommend ONE of the following for Phase A→B through Phase C scale:

Option	Pros	Cons
Airtable	Fast setup, API-driven, visual, cheap	Not a real CRM; may hit limits at WealthCare 50 scale
HubSpot (Free tier)	Real CRM, pipelines, automation	Complex, overkill for Phase A→B solo ops
Notion + Zapier	Flexible, familiar, cheap	Weak automation, not scalable as data grows
Custom SQLite extension	Extends existing mbr/rate-scanner.db pattern, no new tool cost, full control	Manual build, no UI for Talbot to browse
Supabase	Postgres-backed, real API, row-level security, good for Phase C	Some setup overhead

Recommendation criteria:

• Works for solo operator with zero manual process
• API-driven (can be written to programmatically as users sign up, alerts trigger, transfers complete)
• Can scale to WealthCare 50 Alliance (B2B partner records, employee lists, CSR dashboards)
• Integrates with QuickBooks Online (or can route revenue events there)
• Does not require a human to maintain it daily

Provide a ranked recommendation with rationale. State clearly: “For Phase A→B, use X. Plan to migrate to Y at Phase C when WealthCare 50 partner count exceeds Z.”

3. Data Flow Diagrams

Produce ASCII or Mermaid diagrams for these flows:

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

Web form → ? → User record created → Hassle Threshold Engine configured → Subscription record

Flow B — Rate Alert → Transfer → Revenue

rate_changes (SQLite) → hassle-engine → Alert fired → User acts → Transfer initiated
→ KYC Stage 1-4 → Transfer completed → Referral fee received
→ RevenueEvent record → 50% to CanadaHelps → QB Online entry
→ Net Worth Thermometer updated → Annual Return on Better email queued

Flow C — WealthCare 50 Corporate Partner Onboarding

HR contact signs up → WealthCare50Partner record → Welcome Bomb donation triggered
→ Cancer Impact Grant calculated → CSR Dashboard populated → Employee list imported
→ Employee Financial Wellness Score calculated

Flow D — Tier 2 Refund (Guaranteed Benefit)

12-month anniversary → No alerts triggered check → Refund eligibility confirmed
→ Refund issued OR redirected to Cancer donation (Refund-to-Tribute option)
→ QB Online entry

4. Integration Architecture

For each external system, specify: direction, trigger, data transferred, mechanism (API/webhook/Zapier/manual).

• Stripe — subscription billing, performance fee collection, Split-Cart 50% routing
• CanadaHelps — automatic 50% of each revenue event routed as a donation; CRA receipt generated
• QuickBooks Online — every RevenueEvent becomes a QB transaction; refunds; monthly reconciliation
• SendGrid or Resend — rate alert emails, subscription confirmation, annual Return on Better email
• DocuSign — Transfer packet assembly and signing (Phase C — Concierge only)
• Google Sheets or Airtable — pre-CRM fallback for Tier 2 waitlist captures before CRM is live
• Cancer50Pledge.ca — pledge records created, public wall updated, Founding Pledger certificates issued

For each, answer: can this be automated end-to-end, or does it require manual steps? If manual, flag it.

5. Staged KYC Trust Architecture Data Model

Per Filter+Focus.md item #23: “4-stage progressive trust ladder for 1-Click Transfer.”

Design the data model for each stage. What is stored, where, with what consent, and what triggers advancement to the next stage:

• Stage 1: Form template only — no user data stored. User downloads pre-filled PDF. Nothing in DB.
• Stage 2: User provides name + email. Minimal record created. No financial data yet.
• Stage 3: User provides institution name + account type. Financial profile begins. Threshold Engine activated.
• Stage 4: Full KYC — SIN, DOB, address, account number. Concierge tier unlocked.

Specify: consent mechanism at each stage, data retention policy, what happens if user withdraws consent, PIPEDA compliance considerations.

Output Required

Produce the following files:

1. /mnt/d/FSS/KB/MBR/_WorkingOn/Projects/CRM/SPEC.md — complete entity model, tool recommendation, KYC data model, PIPEDA notes
2. /mnt/d/FSS/KB/MBR/_WorkingOn/Projects/CRM/DATA-FLOWS.md — all 4 data flow diagrams with integration touchpoints
3. Update /mnt/d/FSS/KB/MBR/_WorkingOn/Projects/MBR-Business-Dev/TASKS.md — add CRM project to active work

Create the Projects/CRM/ folder if it doesn’t exist.

Important Constraints

• Do not recommend Salesforce, Zoho, or enterprise CRM — solo operator, zero budget for Phase A→B tools
• Do not over-engineer for Phase Z requirements — Phase B must be launchable in days, not months
• PIPEDA compliance is non-negotiable for financial data — flag any design that could create exposure
• The data model must accommodate the 50% Cancer pledge routing on every revenue event without manual intervention

---

Claude Response — 2026-04-08

Summary:

• Read MBR context (Executive Summary, task requirements, current TASKS.md, vault CLAUDE.md)
• Created Projects/CRM/SPEC.md — complete architecture decision, entity model, PIPEDA posture, KYC staged trust model, Phase C growth path
• Created Projects/CRM/DATA-FLOWS.md — all 4 Mermaid flows (signup, alert→transfer→revenue, WC50 partner onboarding, Tier 2 refund)
• Updated Projects/MBR-Business-Dev/TASKS.md — added CRM project to Phase A→B Technical section, top priority
• Task already tracked in Tasks/_active.md

Architectural decision (one-liner): For Phase A→B, use Supabase (Postgres + Auth + RLS, ca-central-1 for PIPEDA). Never migrate off it — layer HubSpot Free on top at Phase C when WealthCare 50 Alliance has 10+ live partner conversations. The 50% Cancer pledge is enforced at the database trigger level on revenue_events so it cannot be bypassed in app code. rate-scanner.db stays local as the rate intelligence SSoT; Supabase holds user/CRM/financial data.

Key design calls to highlight:

1. 50% pledge is a DB constraint, not a convention. enforce_50_pct_pledge() trigger on revenue_events. Cannot be bypassed by any app code. Refund-to-Tribute is the only documented exception.
2. Progressive KYC = PIPEDA by design. Stage 1 stores zero data. Each subsequent stage has its own consent record, timestamp, and withdrawal path. SIN is never collected until Tier 3 Concierge is actually live — no “collect now, use later.”
3. Supabase + native QBO Stripe Sync eliminates custom accounting integration. Talbot never manually enters a transaction.
4. Hybrid DB by design: rate-scanner.db (local SQLite, public data) stays; Supabase (cloud, PII) is a different concern. Don’t conflate.
5. No Airtable, no Notion, no HubSpot (yet). Airtable fails PIPEDA (US residency, no RLS). Notion+Zapier fails automation reliability. HubSpot Free is overkill for Phase A→B but the right add-on (not replacement) at Phase C for B2B pipeline UX only.

Open questions flagged in SPEC §8:

• CanadaHelps programmatic donation API — does it exist? (Blocks full end-to-end pledge automation. Fallback: weekly manual batch.)
• Cancer50Pledge.ca: shared Supabase project with separate schema, or separate project?
• Stage 4 encryption key custody: Supabase Vault vs. external KMS?
• Supabase region: ca-central-1 on Free tier possible, or pay Pro $25/mo for residency?
• QBO Stripe Sync Canadian availability — confirm, or use CSV fallback.

Next Steps for Talbot:

• Review Projects/CRM/SPEC.md end-to-end. Is the Supabase recommendation correct, or would you prefer a different path? 1.
• Resolve the 5 open questions in SPEC §8 (CanadaHelps API, Cancer50Pledge schema, key custody, Supabase region, QBO Stripe Sync) 1.
• Authorize provisioning of the Supabase project (ca-central-1) so Phase A→B launch isn’t blocked on infrastructure 1.
• Confirm the DB-trigger enforcement of the 50% Cancer pledge is acceptable (it means the business logic lives in Postgres, not just app code — this is a feature, but worth a conscious call) 1.
• Decide whether to run /task-complete now (CRM architecture design task itself is done) or keep open as a parking lot for the open questions above 1.

Talbot Response

---