​

Integration Readiness Guide

​

Purpose

​

When to run this

• Before onboarding a CRM integration
• During release readiness checks for integration features
• During integration incident triage

​

Prerequisites

• Access to architecture docs and integration contracts
• BigQuery/query access to integration tables
• Knowledge of target provider and org scope

​

Inputs

• Target provider(s): Salesforce, HubSpot, Zoho, Zenoti, custom
• Tenant/org scope and expected event path (webhook or polling)
• Current Phase status and integration schema references

​

Procedure

​

1) Determine current readiness state

• ✅ Multi-tenant isolation (tenant_id enforced everywhere)
• ✅ Org scoping (org_id with JWT/header support)
• ✅ Config plane (agent profiles, hierarchies, rate plans, assignments)
• ✅ Outbox table foundation (integration_events_outbox)
• ✅ API client security hardening (protected headers, error normalization)
• ✅ Decimal safety policy (NUMERIC/string, no float math)

• 🔲 CRM adapter implementations (Salesforce, HubSpot, Zoho, Zenoti, custom)
• 🔲 Webhook infrastructure (incoming/outgoing)
• 🔲 Pub/Sub workers (event processing)
• 🔲 Secret Manager integration (credential storage)
• 🔲 Connection management UI (OAuth flows, API keys)

​

2) Validate universal adapter pattern coverage

​

Webhook Path (Preferred - Real-Time)

1. External CRM sends webhook to our platform
2. Webhook Handler validates signature and extracts payload
3. Event Processor transforms CRM event to internal format
4. Outbox Writer appends event to integration_events_outbox
5. Pub/Sub Publisher publishes event to topic (fast path)
6. Commission Calculator processes event and updates commission data

​

Polling Path (Fallback)

1. Polling Worker (Cloud Scheduler cadence) queries external CRM API
2. Change Detection compares current state with last known state
3. Event Generator creates events for detected changes
4. Outbox Writer appends events to integration_events_outbox
5. Pub/Sub Publisher publishes events to topic
6. Commission Calculator processes events

​

3) Validate org scoping contract

​

Authority Hierarchy

1. JWT org_id is Authoritative: If JWT contains org_id, it takes precedence over any X-Org-Id header. Org-scoped users cannot override their org context.
2. Platform Admin Mode: If JWT has no org_id (platform admin), X-Org-Id header is required for org-scoped operations. Requests without X-Org-Id return 400 Bad Request.
3. NULL = Platform-Level: In database schemas, org_id = NULL means platform-level (visible to all orgs within tenant). This is distinct from “no org” (which requires X-Org-Id header for platform admins).

​

Enforcement Rules

• List Operations: Filter by org_id (WHERE clause includes org_id match or NULL for platform-level)
• Get-by-ID Operations: Verify org_id matches (return 404 if cross-org, not 403 to avoid existence leaks)
• Mutations: Enforce org_id scope (prevent cross-org writes)
• Org-Scoped Users: Cannot access data outside their JWT org_id (even with X-Org-Id header)

​

Implementation

• Backend: require_org_id() dependency extracts org_id from JWT or X-Org-Id header
• Frontend: useOrgScope() hook manages org selection for platform admins
• Database: All config tables include org_id STRING column (NULL = platform-level)

​

4) Validate credential storage model

​

Where Credentials Live

• OAuth tokens (access tokens, refresh tokens)
• API keys (for external CRM APIs)
• Webhook secrets (for signature validation)

• Secret reference IDs (e.g., secret_ref: "projects/xxx/secrets/salesforce-oauth-token")
• Never store actual credentials in BigQuery

​

Why This Pattern?

1. Security: Secret Manager provides encryption at rest, access control, audit logging
2. Rotation: Secrets can be rotated without updating BigQuery records
3. Compliance: Meets audit requirements for credential storage
4. Isolation: Per-tenant secret isolation via IAM

​

Example Schema

CREATE TABLE integration_connections (
  tenant_id STRING NOT NULL,
  connection_id STRING NOT NULL,
  provider STRING NOT NULL,  -- "salesforce" | "hubspot" | "zoho" | "zenoti" | "custom"
  secret_ref STRING NOT NULL,  -- Secret Manager reference (NOT the actual secret)
  status STRING NOT NULL,  -- "active" | "inactive" | "error"
  last_sync_at TIMESTAMP,
  created_at TIMESTAMP NOT NULL
);

​

5) Validate JSON and decimal rules

​

JSON Storage

payload_json STRING NOT NULL  -- JSON-encoded event payload

​

Decimal Safety (Critical)

• ✅ "split_pct": "33.333333" (string)
• ✅ "pepm_rate": "12.50" (string)
• ❌ "split_pct": 33.333333 (number - forbidden)

​

6) Validate operational constraints and outbox model

​

BigQuery Limitations

• Do NOT build high-frequency polling loops against BigQuery
• Use Pub/Sub for real-time event delivery (fast path)
• Use outbox table for audit trail and retry/replay (safety net)
• Worker cadence should be low-frequency (Cloud Scheduler: minutes/hours, not seconds)

​

Outbox Pattern

• Fast Path: Pub/Sub for real-time delivery
• Safety Net: Outbox table for retry/replay
• Worker: Phase 4 via Pub/Sub + Cloud Scheduler cadence

​

7) Review provider-specific adapter examples

​

Salesforce

1. OAuth flow stores tokens in Secret Manager
2. Webhook path: Salesforce sends webhook → our handler → outbox → Pub/Sub
3. Polling path: Worker queries Salesforce API → change detection → outbox → Pub/Sub

• account.created, account.updated, account.deleted
• contact.created, contact.updated
• opportunity.created, opportunity.won, opportunity.lost

​

HubSpot

1. OAuth flow stores tokens in Secret Manager
2. Webhook path: HubSpot sends webhook → our handler → outbox → Pub/Sub
3. Polling path: Worker queries HubSpot API → change detection → outbox → Pub/Sub

• contact.created, contact.updated, contact.deleted
• company.created, company.updated
• deal.created, deal.updated, deal.won

​

Zoho

1. OAuth flow stores tokens in Secret Manager
2. Webhook path: Zoho sends webhook → our handler → outbox → Pub/Sub
3. Polling path: Worker queries Zoho API → change detection → outbox → Pub/Sub

• contact.created, contact.updated
• account.created, account.updated
• deal.created, deal.updated

​

Zenoti

1. API key stored in Secret Manager
2. Webhook path: Zenoti sends webhook → our handler → outbox → Pub/Sub
3. Polling path: Worker queries Zenoti API → change detection → outbox → Pub/Sub

• appointment.created, appointment.updated, appointment.completed
• member.created, member.updated
• service.created, service.updated

​

Custom CRM (Champion Health)

1. Credentials stored in Secret Manager (API key or OAuth)
2. Webhook path: Custom CRM sends webhook → our handler → outbox → Pub/Sub
3. Polling path: Worker queries custom API → change detection → outbox → Pub/Sub

​

8) Validate event payload contracts

​

Event Payload Structure

{
  "event_type": "agent_profile.created",
  "entity_type": "agent_profile",
  "entity_id": "profile-uuid-123",
  "tenant_id": "creative_benefit_strategies",
  "org_id": "org-456",
  "payload": {
    "profile_id": "profile-uuid-123",
    "agent_id": "721995",
    "first_name": "Robin",
    "last_name": "Bundy",
    "email": "robin@example.com"
  },
  "metadata": {
    "trace_id": "trace-uuid-789",
    "source": "admin_ui",
    "actor": "admin@example.com",
    "timestamp": "2025-12-01T10:00:00Z"
  }
}

​

Decimal Values in Payloads

{
  "payload": {
    "pepm_rate": "12.50",  // ✅ String
    "split_pct": "33.333333",  // ✅ String
    "commission_amount": "1234.56"  // ✅ String
  }
}

​

9) Confirm Phase 4 roadmap

1. Connection Management: OAuth flows, API key management UI
2. Webhook Infrastructure: Incoming/outgoing webhook handlers
3. Pub/Sub Workers: Event processing workers (Cloud Scheduler cadence)
4. Adapters: Provider-specific adapters (Salesforce, HubSpot, Zoho, Zenoti, custom)
5. Monitoring: Integration health checks, error alerting
6. Testing: Integration test suite for each provider

​

10) Confirm schema references

​

Phase 3.x Integration Readiness Schemas

• Pydantic Schema: api/schemas/transaction_event.py
• BigQuery DDL: integration/bigquery/sql/tables/transaction_events.sql
• Validation: api/utils/decimal_strings.py
• Tests: api/tests/test_decimal_strings.py, api/tests/test_transaction_event.py

• BigQuery DDL: integration/bigquery/sql/tables/identity_external_id_map.sql
• Purpose: Maps external CRM identifiers (Salesforce contact ID, HubSpot deal ID, etc.) to internal entity identifiers (agent_id, business_id, etc.)

• JSON Schema: docs/no_code_rules.schema.json
• Documentation: docs/NO_CODE_RULES.md
• Validation: api/utils/rules_schema_validate.py
• Purpose: Strict, versioned schema for no-code commission rule definitions

• BigQuery DDL: integration/bigquery/sql/tables/integration_events_outbox.sql
• Purpose: Append-only audit trail for CRM integration events

• Hook: dashboard/src/hooks/useEmbedMode.ts
• Documentation: docs/EMBED_MODE.md
• Purpose: Minimal UI mode for Salesforce/HubSpot Canvas-type integrations

​

Verification

• Phase 3.x readiness primitives are present and documented
• Remaining Phase 4 items are explicitly tracked
• Secret handling, scoping, and decimal rules align with architecture contracts

​

Failure modes & fixes

1. Credentials stored in BigQuery directly
   • Move to Secret Manager and keep only secret_ref in data tables.
2. Org scoping ambiguity or bypass risk
   • Enforce JWT-first authority with require_org_id() and route-level checks.
3. Decimal precision drift in payloads
   • Enforce string decimals in JSON payloads; avoid float math paths.
4. High-frequency polling design
   • Shift to webhook/PubSub fast path and scheduled low-frequency polling.

​

Artifacts produced

• Integration readiness decision (ready foundations vs pending adapters)
• Gap list for Phase 4 implementation
• Contract/evidence references for security and data correctness

​

Related docs

• Architect_Context.md - Full architecture documentation
• integration/bigquery/sql/tables/integration_events_outbox.sql - Outbox table DDL
• ADR-001 through ADR-005 in Architect_Context.md - Architectural decisions