MBR CRM Architecture & Data Model — SPEC

MBR CRM Architecture & Data Model — SPEC

Companion: DATA-FLOWS.md for Mermaid diagrams of all 4 core flows. Source task: _WorkingOn/Tasks/2026-04-08_CRM-Architecture.md

---

0. TL;DR (Decision Summary)

Question	Answer
Phase A→B CRM	Supabase (Postgres + Auth + row-level security), programmatic-first.
Pre-launch fallback	Google Sheets via a single Apps Script endpoint for Tier 2 waitlist captures until Supabase project is provisioned (≤1 week).
Back-office / browsable view	Supabase Studio for Talbot’s direct DB browsing. No separate CRM UI needed Phase A→B.
Revenue → books	Stripe → QuickBooks Online via the native QBO Stripe Sync app (no custom integration). RevenueEvent is mirrored into Supabase for internal reporting and for driving the 50% pledge routing.
50% Cancer Pledge routing	Split-Cart at Stripe level (Stripe Connect destination charges OR scheduled CanadaHelps API sweep) — triggered by a Supabase Edge Function on every RevenueEvent insert.
Migrate when?	Never forced migration. Supabase scales to Phase C. Add HubSpot Free only if/when the WealthCare 50 B2B pipeline needs a sales pipeline UI with >10 live partner conversations — layer HubSpot on top of Supabase, don’t replace it.
PIPEDA posture	Stage 1 stores nothing. Stage 2+ data lives in Supabase (Canadian region: ca-central-1) with RLS, encryption at rest, explicit consent flag per stage, and a documented retention + withdrawal procedure.

One-liner: For Phase A→B, use Supabase. Plan to layer HubSpot Free on top of Supabase at Phase C when WealthCare 50 Alliance partner count exceeds ~10 live conversations — never migrate off Supabase.

---

1. CRM Tool Selection — Rationale

Ranked recommendation

1. Supabase ✅ (chosen)
2. HubSpot Free (layer on later, B2B pipeline only)
3. Airtable (rejected — not a real DB, API limits, no RLS for PIPEDA)
4. Notion + Zapier (rejected — weak automation, not a system of record)
5. Custom SQLite extension of mbr/rate-scanner.db (rejected — no auth, no API gateway, no RLS, blocks multi-writer scenarios)

Why Supabase wins for MBR specifically

Criterion	Supabase
Solo operator, zero manual process	✅ Fully API-driven. Talbot never has to “update the CRM”.
Programmatic-first	✅ Postgres + PostgREST auto-API + TypeScript/Python SDK.
Scales to WealthCare 50 Alliance	✅ Postgres handles partner + employee + CSR dashboard data natively.
QuickBooks Online integration	✅ Via Stripe (native QBO Stripe Sync). Supabase mirrors for reporting.
PIPEDA compliance (Canadian data residency)	✅ Supports ca-central-1 region. RLS + encryption at rest standard.
Auth built-in	✅ Email + magic link; aligns with Tier 2 passwordless UX.
Works at Phase A→B solo ops	✅ Free tier covers 0→5k users comfortably; Pro at $25/mo when needed.
Back-office browsing for Talbot	✅ Supabase Studio (no separate tool).
Zero-budget for Phase A→B	✅ Free tier until traction.
Avoids vendor lock	✅ It’s just Postgres — exportable anywhere.

Why not Airtable / HubSpot / Notion

• Airtable: PIPEDA exposure (US data residency, no row-level security). Hits API limits as rate alerts scale. Not a database, a spreadsheet with an API. Rejected for anything touching financial data.
• HubSpot Free: Beautiful sales pipeline UI, but overkill for Phase A→B. The Hassle Threshold Engine needs programmatic reads/writes; HubSpot’s free tier rate-limits that. However — when WealthCare 50 Alliance hits 10+ active B2B conversations, add HubSpot Free as a view on top of Supabase for B2B relationship management only. Contact records sync from Supabase. Don’t migrate. Layer.
• Notion + Zapier: Every integration step becomes a Zap. Zaps silently break. This is a solo operator’s nightmare. Rejected.
• Custom SQLite extension: Already in use for rate-scanner.db — keep it there for rate data. User/transfer/revenue data is a different concern (multi-writer, PII, consent state). Do not conflate operational rate data with user/CRM data.

The hybrid database model

rate-scanner.db (SQLite, local)         Supabase (Postgres, cloud, ca-central-1)
├── rate_snapshots                      ├── users
├── rate_changes                        ├── rate_alerts
└── institutions                        ├── transfers
                                        ├── kyc_records
           │                            ├── revenue_events
           │  (Hassle Engine reads)     ├── subscriptions
           └───────────────────────────→├── financial_snapshots
                                        ├── wealthcare50_partners
                                        └── cancer50_pledgers

rate-scanner.db stays local — it’s the authoritative rate intelligence dataset, 100% public data, no PII. The Hassle Threshold Engine reads from it and writes alerts to Supabase.

---

2. Entity Model

Conventions:

• All tables have id uuid default gen_random_uuid() primary key, created_at timestamptz default now(), updated_at timestamptz.
• Phase column: A→B = needed for Tier 1/2 launch; B→Next = Tier 3 / Concierge; C = WealthCare 50 Alliance.
• Privacy: Public / PII / Financial PII (triggers stricter handling, encryption, retention rules).

2.1 users

Field	Type	Req	Notes
id	uuid	✓	Supabase Auth user id (FK to auth.users)
email	text	✓	Auth-managed
tier	enum(tier_1,tier_2,tier_3)	✓	Default tier_1
signup_date	timestamptz	✓
hassle_threshold_cad	numeric(10,2)	—	e.g. 150.00; null = Tier 1 only
optimization_target	enum(savings,mortgage,both,other)	—	From 5-choice onboarding
subscription_status	enum(none,active,past_due,canceled,refunded)	✓
cancer50_tribute_name	text	—	For Refund-to-Tribute option
consent_marketing	bool	✓	PIPEDA: explicit
consent_kyc_stage	int (0–4)	✓	Current KYC stage (§5)
region_province	text(2)	—	e.g. ON, BC — regulatory
deleted_at	timestamptz	—	Soft-delete for PIPEDA withdrawal

• Relationships: 1→N rate_alerts, transfers, revenue_events, subscriptions, financial_snapshots, 1→1 kyc_records (current), financial_snapshots (latest)
• Phase: A→B (minimal fields) → B→Next (KYC + subscription) → C (group affiliation to WealthCare 50 partner)
• Privacy: PII. RLS: user can read/update own row only; service role for writes.

2.2 rate_alerts

Field	Type	Req	Notes
id	uuid	✓
user_id	uuid	✓	FK users
rate_change_id	int	✓	FK into rate-scanner.db export (rate_changes.id)
product_key	text	✓	e.g. HISA:EQB
user_threshold_cad	numeric(10,2)	✓	Snapshot of user’s threshold at alert time
projected_benefit_cad	numeric(10,2)	✓	Net benefit after fees/time
triggered_at	timestamptz	✓
status	enum(fired,ignored,acted_on,expired)	✓	Drives Tier 2 refund eligibility
delivery_channel	enum(email,push,web)	✓
opened_at, clicked_at, acted_at	timestamptz	—	Behavioral Data Moat

• Phase: A→B (fire + log), B→Next (acted_on / transfer linkage)
• Privacy: PII (via user_id). Public fields otherwise.

2.3 transfers

Field	Type	Req	Notes
id	uuid	✓
user_id	uuid	✓
triggered_by_alert_id	uuid	—	FK rate_alerts; null if user-initiated
institution_from	text	✓
institution_to	text	✓
account_type	enum(hisa,tfsa,rrsp,gic,heloc,mortgage,other)	✓
amount_cad	numeric(12,2)	—	Optional; some users won’t disclose
kyc_stage_at_start	int (1–4)	✓
status	enum(initiated,packet_sent,signed,in_flight,completed,cancelled)	✓
completed_at	timestamptz	—
projected_annual_benefit_cad	numeric(10,2)	—	Feeds Net Worth Thermometer
referral_fee_cad	numeric(10,2)	—	From institution — links to revenue_event
revenue_event_id	uuid	—	FK
docusign_envelope_id	text	—	Phase B→Next

• Phase: B→Next
• Privacy: Financial PII. RLS: user reads own; service role writes.

2.4 kyc_records (progressive trust — see §5 for stage design)

Field	Type	Req	Notes
id	uuid	✓
user_id	uuid	✓
current_stage	int(1–4)	✓
stage1_template_downloaded_at	timestamptz	—	Stage 1: no data stored beyond this timestamp
stage2_name	text	—
stage2_consent_at	timestamptz	—
stage3_institution	text	—
stage3_account_type	text	—
stage3_consent_at	timestamptz	—
stage4_sin_encrypted	bytea	—	Encrypted at app layer with separate key (not DB key)
stage4_dob	date	—
stage4_address_encrypted	bytea	—
stage4_account_number_encrypted	bytea	—
stage4_consent_at	timestamptz	—
withdrawn_at	timestamptz	—	PIPEDA: user withdrew consent → trigger purge job

• Phase: Stage 1/2 A→B; Stage 3/4 B→Next
• Privacy: Financial PII (highest). Stage 4 fields column-level encrypted; key in Supabase Vault / separate KMS.

2.5 revenue_events

Field	Type	Req	Notes
id	uuid	✓
user_id	uuid	—	Nullable for institutional referrals not tied to a user
type	enum(subscription,performance_fee,institutional_referral,broker_referral,api_subscription,wc50_licensing)	✓
amount_cad	numeric(12,2)	✓	Gross
pledge_amount_cad	numeric(12,2)	✓	= amount × 0.50; enforced by DB trigger
pledge_status	enum(pending,routed,failed)	✓
pledge_routed_at	timestamptz	—
canadahelps_receipt_id	text	—	Tax receipt id
stripe_charge_id	text	—	For reconciliation with QBO
qbo_transaction_id	text	—	Set by QBO sync
source_transfer_id	uuid	—	FK transfers
source_subscription_id	uuid	—	FK subscriptions
partner_id	uuid	—	FK wealthcare50_partners

• Phase: A→B (once first dollar flows)
• Privacy: Financial PII when user_id set
• Critical: DB trigger enforce_50_pct_pledge() rejects any insert where pledge_amount_cad != round(amount_cad * 0.50, 2). The 50% pledge cannot be manually overridden. This is the structural integrity of Cancer50Pledge.

2.6 subscriptions

Field	Type	Req	Notes
id	uuid	✓
user_id	uuid	✓
tier	enum(tier_2,tier_3)	✓
stripe_subscription_id	text	✓
started_at	timestamptz	✓
billing_period	enum(monthly,annual)	✓
current_period_end	timestamptz	✓
status	enum(active,past_due,canceled,refund_pending,refunded)	✓
refund_eligibility_check_at	timestamptz	—	12-month anniversary
refund_eligible	bool	—	True if no alerts triggered in period
refund_issued_at	timestamptz	—
refund_redirected_to_cancer	bool	—	Refund-to-Tribute flag

2.7 financial_snapshots (Net Worth Thermometer)

Field	Type	Req	Notes
id	uuid	✓
user_id	uuid	✓
snapshot_date	date	✓
cumulative_benefit_cad	numeric(12,2)	✓	Sum of transfers.projected_annual_benefit_cad × years
financial_iq_score	int	—	0–100
active_optimizations_count	int	—

• Phase: B→Next
• One row per user per day (upsert) for Annual Return on Better email.

2.8 wealthcare50_partners

Field	Type	Req	Notes
id	uuid	✓
type	enum(corporate,charity,association)	✓
org_name	text	✓
primary_contact_name	text	✓
primary_contact_email	text	✓
employee_count	int	—	Corporate only
cancer_impact_grant_cad	numeric(12,2)	✓ default 0	Running total
csr_dashboard_token	uuid	—	Magic-link token to CSR dashboard
welcome_bomb_triggered_at	timestamptz	—
active	bool	✓

2.9 cancer50_pledgers

Field	Type	Req	Notes
id	uuid	✓
org_name	text	✓
pledge_percent	numeric(5,2)	✓	e.g. 50.00
primary_contact	text	—
founding_pledger	bool	✓ default false
founding_certificate_number	int	—	1–50 if founding
public_wall_visible	bool	✓ default true
pledged_at	timestamptz	✓

• Phase: A→B (Cancer50Pledge.ca launch)
• Privacy: Public (that’s the point — shareable pledgers wall)

2.10 partner_employees (C — WealthCare 50 Corporate rollout)

Field	Type	Notes
id	uuid
partner_id	uuid FK
user_id	uuid FK nullable	Links once employee signs up
email_domain_match	text
wellness_score	int	Derived

---

3. Data Retention & PIPEDA Posture

Data class	Retention	Withdrawal action
Stage 1 (no data)	N/A	N/A
Stage 2 (name, email)	Until user deletes account or 36 months inactive	Purge within 30 days of request
Stage 3 (institution, account type)	Until transfer completed + 7 years (CRA) OR 36 months if no transfer	Tombstone user row; purge financial fields; keep anonymized revenue_events for books
Stage 4 (SIN, DOB, address, account number)	7 years post-transfer (CRA minimum for financial transactions)	After 7 years: auto-purge via scheduled job
revenue_events	7 years (CRA)	Never purged (books) — but user_id set null on withdrawal
rate_alerts behavioral data	24 months rolling	Anonymize on withdrawal (keep aggregate, drop user_id)

Rules of the road:

• Canadian data residency: Supabase project created in ca-central-1 region. Non-negotiable.
• Encryption at rest: Supabase standard. Stage 4 fields additionally column-encrypted at the app layer.
• Consent: separate flag per KYC stage with timestamp. Never bundled into a single TOS checkbox.
• Access logs: every Stage 4 read logged to an audit table (kyc_access_log).
• Withdrawal procedure: single Supabase Edge Function user_withdraw_consent(user_id) that runs the stage-by-stage purge. Documented in Projects/CRM/PIPEDA-PROCEDURE.md (TBD).
• Red flag flagged: Do not store Stage 4 data until Tier 3 is actually live. Do not collect SIN for “future use”. Collect at the moment of need.

---

4. Integration Architecture

System	Direction	Trigger	Data	Mechanism	Automation?
Stripe	↔	User subscribes / performance fee charged	Customer, subscription, charge, refund	Stripe SDK + webhooks → Supabase Edge Function	✅ End-to-end
CanadaHelps	→	New revenue_events row	pledge_amount_cad, donor info, in-tribute name	CanadaHelps API (check: confirm programmatic donation endpoint exists) OR scheduled manual batch at Phase A→B	⚠ Phase A→B: manual weekly batch if API unavailable. Phase B→Next: automated. Flag this.
QuickBooks Online	←	Stripe charges/refunds	Every Stripe transaction → QB transaction	Native QBO Stripe Sync app (no custom work)	✅ End-to-end
SendGrid or Resend	→	rate_alerts status change, subscription events, annual report	Rendered email with benefit payload	Supabase Edge Function on insert/update trigger	✅ End-to-end
DocuSign	→	Transfer initiated (Tier 3)	KYC packet PDF, signer info	DocuSign eSignature REST API → envelope created → webhook back to Supabase	✅ End-to-end (Phase B→Next)
Google Sheets (Apps Script)	→	Pre-CRM waitlist form submit	email, optimization target	Single Apps Script webhook endpoint; manual import into Supabase at launch	⚠ Manual import step at launch — acceptable, one-time
Cancer50Pledge.ca	↔	Pledge form submission	org_name, pledge_percent, certificate	Website POSTs to Supabase (same project, different schema: cancer50) via PostgREST + RLS	✅ End-to-end
rate-scanner.db (local)	→	Cron job detects rate change	rate_changes rows	Hassle Engine reads SQLite, writes alert to Supabase via SDK	✅ End-to-end

Manual-step flags (things that are NOT end-to-end at Phase A→B)

1. ⚠ CanadaHelps donation routing — pending API confirmation. If no API, weekly manual batch until an API or a different charity vehicle (direct cancer research donation through a registered charity with API) is secured. Decision needed.
2. ⚠ Google Sheets waitlist → Supabase — one-time import at Supabase launch. Not a recurring manual step.
3. ⚠ Founding Pledger certificates (1–50) — certificate PDF generation can be templated + auto-emailed, but certificate design approval is manual (each cert has the pledger’s org name).

Everything else is automated.

---

5. Staged KYC Trust Architecture — Data Model

Per Filter+Focus #23. Progressive disclosure minimizes PIPEDA surface area.

Stage 1 — Form Template (zero data stored)

• UX: User downloads a pre-filled PDF template for a manual transfer. No login, no form submit, no cookie.
• Stored: Nothing. An anonymous counter (stage1_downloads_total) increments in a separate metrics table with no user link.
• Consent mechanism: N/A (no data collected).
• Advancement trigger: User clicks “Want MBR to watch rates for you?” CTA on download-success page → goes to Stage 2 form.

Stage 2 — Name + Email (minimal trust)

• UX: Standard signup form. Single-checkbox consent: “I agree to receive rate alerts from MyBetterRates and to MBR’s [Privacy Policy].”
• Stored: users.email, users.consent_marketing = true, kyc_records.stage2_name, kyc_records.stage2_consent_at.
• Consent mechanism: Explicit opt-in checkbox + timestamped.
• Advancement trigger: User completes Optimization Target onboarding AND sets a Hassle Threshold → prompted for Stage 3.
• Withdrawal: Account delete button in settings. Single function call. 30-day purge SLA.

Stage 3 — Institution + Account Type (financial profile begins)

• UX: Dropdown + radio form. Second consent block: “You can delete this information at any time. It is stored in Canada and never shared.”
• Stored: kyc_records.stage3_institution, stage3_account_type, stage3_consent_at. Hassle Threshold Engine now has enough to personalize alerts.
• Consent mechanism: Separate checkbox, separate timestamp, plain-language statement.
• Advancement trigger: User accepts a 1-Click Transfer alert → Stage 4.
• Withdrawal: Can drop back to Stage 2 (keeps name/email, purges financial fields).

Stage 4 — Full KYC (Concierge tier)

• UX: Progressive form with explicit disclosure: “This is required to pre-fill transfer paperwork on your behalf. It is encrypted, stored in Canada, and only used when you authorize a transfer.”
• Stored: SIN (encrypted), DOB, address (encrypted), account number (encrypted). All encryption keys managed via Supabase Vault / KMS — separate from DB credentials.
• Consent mechanism: Per-field consent with explicit use-case disclosure. Audit log entry on every read.
• Advancement trigger: N/A — terminal stage.
• Withdrawal: Immediate purge of Stage 4 fields on request (Stage 2/3 data and historical transfers retained per CRA 7-year rule).

PIPEDA compliance checklist (per stage)

• Identifying purposes before collection ✓
• Knowledge + consent ✓
• Limit collection to what’s necessary ✓ (progressive disclosure)
• Limit use/retention ✓ (retention table in §3)
• Accuracy ✓ (user can self-edit)
• Safeguards ✓ (RLS + encryption + column-level encryption Stage 4)
• Openness ✓ (Privacy Policy links at each stage)
• Individual access ✓ (data export endpoint)
• Challenging compliance ✓ (contact route)

---

6. Phase C Growth Path

• Supabase continues as system of record.
• HubSpot Free layered on for WealthCare 50 B2B sales pipeline only. Supabase wealthcare50_partners writes sync to HubSpot contacts via one-way mirror. Talbot uses HubSpot for sales pipeline UI, not for storing truth.
• Open Banking (2026-27): new bank_connections table; Hassle Engine consumes live feeds alongside rate-scanner.db.
• SDC (Rung 5): separate Supabase project (separate business), cross-linked via users.sdc_user_id.

---

7. Constraints check (from task)

• No Salesforce / Zoho / enterprise CRM — Supabase is the opposite of enterprise lock-in.
• Phase B launchable in days, not months — Supabase schema deployable in one afternoon.
• PIPEDA non-negotiable — ca-central-1, RLS, staged consent, column-level encryption, documented withdrawal procedure.
• 50% Cancer pledge routes automatically — enforced by DB trigger on revenue_events, executed by Edge Function on every insert.

---

8. Open questions for Talbot

1. CanadaHelps programmatic API — does it exist with donation-create + tax-receipt endpoints? If not, what’s the fallback charity vehicle? (Blocks full automation of 50% pledge routing.)
2. Cancer50Pledge.ca — same Supabase project with a separate schema, or separate project? Same project is simpler and cheaper; separate project has stronger blast-radius isolation.
3. Stage 4 encryption key custody — Supabase Vault (easier) vs. external KMS (e.g. AWS KMS Canada region, stronger separation)? Recommend Supabase Vault for Phase B→Next, revisit at Phase C.
4. Supabase region confirmation — ca-central-1 is available on Pro tier. Free tier may require region compromise. If Free tier can’t give Canadian residency, skip Free, start on Pro ($25/mo) — PIPEDA wins.
5. QBO Stripe Sync app — confirm current Canadian availability. If unavailable, fallback is Stripe → Supabase → manual weekly QBO import via CSV (acceptable at Phase A→B volumes).