Build CRM · high-level scoping document

"The participants of the flip360 business eg a financial planner, an accountant, a mortage broker and so on have a 'telephone book' of clients (a client database) that they can park onto the flip360 business as contactable by other flip360 professional service providers with an opt in/opt out filter for would you like to be connected with. But each of this business providers dont want another business provider being able to direct access their telephone book. Do you follow this data protection legislation and the business offering that matt wants to set up under CRM that feeds the flip360 referral/commission engine?"

"The workstream lead Corrina wants to be able to warm up a database that has opted in and be able to have full utilisation of the CRM."

-- EXTEND existing contacts table (no breaking changes)
ALTER TABLE contacts ADD COLUMN owner_provider_id INTEGER REFERENCES providers(id);
ALTER TABLE contacts ADD COLUMN owner_visibility TEXT DEFAULT 'PRIVATE';
  -- 'PRIVATE' (default, owner-only) | 'DISCOVERABLE' (consent granted)
ALTER TABLE contacts ADD COLUMN discoverable_categories TEXT;  -- JSON array
ALTER TABLE contacts ADD COLUMN collection_purpose TEXT;
ALTER TABLE contacts ADD COLUMN collection_source TEXT;
ALTER TABLE contacts ADD COLUMN collected_at DATETIME;
ALTER TABLE contacts ADD COLUMN consent_version TEXT;
ALTER TABLE contacts ADD COLUMN lifecycle_stage TEXT DEFAULT 'cold';
  -- 'cold' | 'warming' | 'engaged' | 'active' | 'lapsed'
ALTER TABLE contacts ADD COLUMN self_service_token TEXT UNIQUE;

-- NEW · providers (the tenants)
CREATE TABLE providers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  business_name TEXT NOT NULL,
  professional_category TEXT NOT NULL,  -- 'financial-planner' | 'broker' | ...
  afsl_acl_number TEXT,
  pi_insurance_cert_url TEXT,
  kyc_completed_at DATETIME,
  provider_agreement_execution_id INTEGER REFERENCES agreement_executions(id),
  webauthn_credential_id TEXT,
  status TEXT DEFAULT 'PENDING',  -- 'PENDING' | 'ACTIVE' | 'SUSPENDED'
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- NEW · consent_grants (the consent ledger, hash-anchored)
CREATE TABLE consent_grants (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  contact_id INTEGER NOT NULL REFERENCES contacts(id),
  owner_provider_id INTEGER NOT NULL REFERENCES providers(id),
  granted_at DATETIME NOT NULL,
  consent_version TEXT NOT NULL,
  categories TEXT NOT NULL,  -- JSON array
  marketing_opt_in INTEGER DEFAULT 0,  -- Spam Act separation
  introduction_opt_in INTEGER DEFAULT 0,
  ip_address TEXT,
  user_agent TEXT,
  withdrawn_at DATETIME,
  payload_hash TEXT NOT NULL  -- sha-256
);

-- NEW · disclosure_log (APP 6 + APP 11.2 audit)
CREATE TABLE disclosure_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  query_provider_id INTEGER NOT NULL REFERENCES providers(id),
  target_contact_id INTEGER NOT NULL REFERENCES contacts(id),
  query_type TEXT NOT NULL,  -- 'discovery-count' | 'intro-request' | 'intro-made'
  occurred_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  request_id TEXT  -- correlates with introductions.id where applicable
);

-- NEW · introductions (feeds commission engine)
CREATE TABLE introductions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  introducer_provider_id INTEGER NOT NULL REFERENCES providers(id),
  target_provider_id INTEGER NOT NULL REFERENCES providers(id),
  contact_id INTEGER NOT NULL REFERENCES contacts(id),
  status TEXT NOT NULL,  -- 'REQUESTED' | 'APPROVED' | 'DECLINED' | 'MADE' | 'CONVERTED'
  governing_agreement_execution_id INTEGER REFERENCES agreement_executions(id),
  commission_rule_id INTEGER REFERENCES commission_rules(id),
  requested_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  converted_at DATETIME
);

-- NEW · campaigns (Corrina's warm-up)
CREATE TABLE campaigns (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  owner_provider_id INTEGER NOT NULL REFERENCES providers(id),
  name TEXT NOT NULL,
  segment_definition TEXT NOT NULL,  -- JSON query spec
  status TEXT DEFAULT 'DRAFT',
  marketing_consent_gate INTEGER DEFAULT 1,  -- hard requirement
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- NEW · campaign_sends (per-recipient event log)
CREATE TABLE campaign_sends (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  campaign_id INTEGER NOT NULL REFERENCES campaigns(id),
  contact_id INTEGER NOT NULL REFERENCES contacts(id),
  sent_at DATETIME,
  opened_at DATETIME,
  clicked_at DATETIME,
  unsubscribed_at DATETIME,
  converted_at DATETIME
);

-- INDICES for tenant isolation enforcement
CREATE INDEX idx_contacts_owner ON contacts(owner_provider_id);
CREATE INDEX idx_consent_grants_contact ON consent_grants(contact_id);
CREATE INDEX idx_disclosure_log_query ON disclosure_log(query_provider_id, occurred_at);
CREATE INDEX idx_introductions_status ON introductions(status);

// src/middleware/tenantScope.ts (proposed)
export const tenantScope: MiddlewareHandler = async (c, next) => {
  const session = await getSession(c)
  if (!session?.providerId) return c.json({ error: 'unauthenticated' }, 401)
  c.set('providerId', session.providerId)
  // EVERY downstream SELECT on contacts MUST include owner_provider_id = ?
  // Linter enforces; PR review enforces; runtime assertion enforces.
  await next()
}

 Recommendation to Steerco #2

Option A (Owner-private first, federate later) · with Pine Lawyers engagement initiated this week so the federated layer can ship in fortnight 2-3 without slip.

This is the same-breed-as-IPO-comps play: ship the foundation now, prove tenant isolation under real load, layer the federated trust fabric on a hardened base. Air BnB shipped accommodation listings before they shipped Experiences. Afterpay shipped pay-in-four before they shipped business-tier features. Same discipline.