# APAC HubSpot Target and Sales-Owned Routing Design Status: implementation design Last updated: 2026-07-14 (Asia/Shanghai) ## 1. Objective Extend the APAC tenant signal pipeline with two ordered business rules while preserving the existing tenant selection, enrichment, scoring, and reporting semantics. 1. A tenant matched to a Company in HubSpot list `19544` is a Target Account. It stays in `apac_active_tenants`, receives the normal numeric score, and its final priority is forced to `P0`. 2. A non-Target tenant whose non-empty `sales_owner_email` belongs to the APAC Sales email list is stored in `apac_sales_owned_tenants`. It is enriched and scored normally, but Daily Summary and Weekly Metrics do not read it. 3. Every other tenant continues through `apac_active_tenants` unchanged. The active-candidate SQL remains unchanged and continues to exclude `free_trial`, `new_user`, and `closing`. Previous-day signup selection uses CST natural-day boundaries. ## 2. Rule precedence The routing order is deterministic: ```text Existing forced Sales-owned tenant ID -> apac_sales_owned_tenants, retain P0, skip enrichment and Scoring Otherwise, HubSpot Target Account match -> apac_active_tenants, normal enrichment and score, priority forced to P0 Otherwise, sales_owner_email is in APAC Sales list -> apac_sales_owned_tenants, normal enrichment and normal priority Otherwise -> apac_active_tenants, existing behavior ``` HubSpot matching must happen before the Sales-owned check. A tenant matching both rules is therefore an Active P0 tenant and is eligible for Summary. The same routing applies to active candidates and previous-day signups. The three forced Sales-owned tenant IDs are evaluated first and do not enter enrichment or Scoring. ## 3. Component responsibilities ### 3.1 Dispatcher Before selecting DevOps tenants, Dispatcher attempts to refresh the local HubSpot Target Account snapshot. Dispatcher: 1. reads every membership page for HubSpot list `19544`; 2. reads the required Company properties in batches; 3. validates that the snapshot is complete; 4. atomically replaces the previous successful snapshot; 5. records sync success or failure metadata; 6. continues with the existing DevOps tenant queries regardless of HubSpot sync success; 7. includes the latest successful HubSpot sync metadata and pipeline `run_id` in every Monitor SQS message. Dispatcher does not decide whether an individual tenant matches HubSpot. This keeps matching after Domain Intelligence, where Worker can also use `official_website`. ### 3.2 Worker Worker performs the existing enrichment first, then loads the latest successful HubSpot snapshot and routes the tenant. Worker: 1. enriches the tenant exactly as today; 2. attempts HubSpot matching in the defined order; 3. if matched, routes to `apac_active_tenants` and records the match evidence; 4. otherwise checks the normalized APAC Sales email set; 5. routes to `apac_sales_owned_tenants` or `apac_active_tenants`; 6. removes the same tenant ID from the non-selected tenant table in the same transaction, keeping the tables mutually exclusive; 7. sends Scoring a message containing the selected tenant bucket and optional priority override. ### 3.3 Scoring Scoring reads and updates the table selected by the allowlisted `tenant_bucket` value: ```text active -> apac_active_tenants sales_owned -> apac_sales_owned_tenants ``` It always calculates the normal `total_score`. For a HubSpot Target Account it then replaces only the calculated priority with `P0`. The scoring snapshot is written for both tenant buckets and records the current bucket. ### 3.4 Summary and Weekly Metrics No reporting query is expanded to the Sales-owned table. Both functions continue to read `apac_active_tenants` only. Consequently: - HubSpot Target Accounts can appear in P0 Summary results; - Sales-owned tenants never appear in Daily Summary or Weekly Metrics; - `apac_tenant_score_snapshots` may contain Sales-owned scores, but reporting does not use those rows unless explicitly changed in a future design. ## 4. HubSpot snapshot data model ### 4.1 `apac_hubspot_target_accounts` This table is the latest complete, successful mirror of list `19544`. ```text hubspot_company_id primary key membership_timestamp portal_id normalized portal_id__c account_id normalized account_id__c name account_alias account_allias__c domain website additional_domains domain_root normalized registrable domain website_domain_root normalized registrable domain additional_domain_roots JSON array hubspot_modified_at sync_id synced_at ``` All rows in the table belong to one successful `sync_id`. On a successful refresh, existing Companies are overwritten with the current HubSpot values, including overwriting old values with `NULL` when HubSpot cleared a property. Companies no longer in the list are deleted. ### 4.2 `apac_hubspot_target_sync_state` One row per HubSpot list records operational state: ```text list_id primary key last_successful_sync_id last_successful_sync_at last_successful_list_total last_successful_snapshot_hash last_attempt_at last_attempt_status success or failed last_error ``` A failed attempt updates only attempt status/error fields. It never changes the last-success fields or Target Account table. ### 4.3 `apac_sales_owned_tenants` This table has the same tenant, enrichment, score, and tracking columns as `apac_active_tenants`. It adds no separate scoring model. Keeping the schemas aligned allows the same enrichment and scoring code to operate on an allowlisted destination table. Both tenant tables record HubSpot routing evidence: ```text hubspot_target hubspot_company_id hubspot_match_method hubspot_snapshot_synced_at ``` `apac_tenant_score_snapshots` gains `tenant_bucket` so a score can be traced to the table that owned the tenant when scoring ran. ## 5. HubSpot synchronization algorithm ### 5.1 API fields Dispatcher reads only the fields needed for deterministic matching and audit: ```text hs_object_id portal_id__c account_id__c domain website hs_additional_domains name account_allias__c hs_lastmodifieddate ``` List membership, not `hs_is_target_account`, is authoritative. ### 5.2 Complete fetch and validation The full HubSpot response is collected in Lambda memory before any snapshot table write. Memberships are paginated to completion and Company properties are read in batches of at most 100. Validation requires: - list processing status is `COMPLETE`; - every membership page succeeds; - Company IDs are unique after normalization; - the unique membership count equals the API-reported total/list size; - every membership Company ID has a Company response; - no required response batch failed. Transient `429` and `5xx` responses use bounded retry with `Retry-After` when available. Every HTTP request has explicit connection/read timeout behavior. ### 5.3 Atomic refresh After successful validation, Dispatcher opens one target-database transaction: ```sql INSERT ... ON DUPLICATE KEY UPDATE ...; DELETE FROM apac_hubspot_target_accounts WHERE sync_id <> :new_sync_id; UPDATE apac_hubspot_target_sync_state SET last_successful...; COMMIT; ``` The upsert overwrites all HubSpot-controlled columns, including with `NULL`. The delete removes Companies that left the list. A database error rolls back the entire refresh, preserving the previous complete snapshot. ### 5.4 Failure fallback HubSpot sync failure is non-blocking for tenant fan-out: ```text sync fails -> keep previous Target Account rows unchanged -> record failed attempt and error -> continue selecting and enqueueing tenants -> Worker uses the last successful snapshot ``` If no successful snapshot has ever existed, HubSpot matching is unavailable for that run and Worker proceeds to the Sales-owned rule. Logs must distinguish `fresh`, `stale`, and `unavailable` snapshots and include snapshot age. No hard maximum snapshot age is imposed initially. CloudWatch logs expose age so an alarm threshold can be added after observing normal operation. ## 6. Matching algorithm Worker loads the small current snapshot into in-process lookup maps and may cache it briefly per warm Lambda container, keyed by `sync_id`. Match order: 1. exact `tenant.id == portal_id`; 2. exact non-empty `tenant.custom_id == account_id`; 3. non-personal tenant email registrable domain equals a unique HubSpot `domain`, `website`, or additional-domain root; 4. Domain Intelligence `official_website` registrable domain equals a unique HubSpot domain root. Personal/free domains such as Gmail, QQ, Outlook, and Yahoo never participate in domain matching. Ambiguous domain roots matching more than one Company do not auto-match. Company name and alias are logged for investigation but never independently force P0. Each successful match records one of: ```text portal_id account_id email_domain official_website ``` ## 7. APAC Sales-owned routing APAC Sales emails are supplied through a CloudFormation parameter and Lambda environment variable, never hard-coded. Values are comma-separated, trimmed, and lower-cased. An empty configuration means no Sales-owned routing. The rule requires both: ```text sales_owner_email is non-empty normalized sales_owner_email is an exact member of the configured set ``` No domain-wide or partial email match is allowed. ## 8. Tenant movement and idempotency The two tenant tables are mutually exclusive. After selecting a destination, Worker performs in one transaction: ```text upsert destination tenant row delete the tenant ID from the other tenant table commit enqueue Scoring message ``` Examples: - a previously Active tenant gains an APAC Sales owner and leaves the HubSpot list: it moves to Sales-owned; - a Sales-owned tenant enters the HubSpot list: it moves to Active and becomes P0; - a Sales-owned tenant loses its APAC Sales owner: it moves back to Active. Upserts and deletes make SQS retry idempotent. A Scoring-send failure retains the existing behavior: the Monitor SQS record is retried after the committed database write. When a tenant moves between buckets, Worker carries forward its score and manual tracking fields (`track_step`, `track_update_time`, and `startup_program`) before deleting the previous row. ## 9. Message contracts ### 9.1 Monitor message additions ```json { "run_id": "20260714T073000Z", "hubspot_sync_id": "20260714T073000Z-", "hubspot_snapshot_synced_at": "2026-07-14T07:30:05Z", "hubspot_snapshot_status": "fresh" } ``` If sync failed, the message references the latest successful snapshot and uses `stale`; if none exists, the ID/time are null and status is `unavailable`. ### 9.2 Scoring message additions ```json { "tenant_id": "...", "tenant_bucket": "active", "priority_override": "P0", "priority_reason": "hubspot_target_account", "hubspot_company_id": "...", "hubspot_match_method": "portal_id" } ``` Only the internal allowlisted values `active` and `sales_owned` are accepted. Only `P0` with reason `hubspot_target_account` is accepted as a new override. ## 10. Observability Logs are structured single-line JSON and do not include tokens, credentials, full tenant payloads, or unmasked customer email addresses. Dispatcher logs: - sync start, page/batch progress, validation result, duration; - success/failure, list total, sync ID, snapshot age/status; - selected Active/signup totals; - enqueue progress after every SQS batch; - final per-source and overall counts. Worker logs one start and one completion/failure event per tenant: - `run_id`, tenant ID, source type, record index/count; - enrichment stage timings and error count; - HubSpot snapshot status/age, match result/method/company ID; - Sales-owned match boolean without logging the email; - destination bucket, processing status, elapsed seconds; - scoring enqueue result. Scoring logs one start and one completion/failure event per tenant: - tenant ID and bucket; - calculated score and calculated priority; - final priority and override reason; - elapsed seconds and batch progress. These logs support CloudWatch searches by `run_id`, `tenant_id`, `sync_id`, `tenant_bucket`, and processing status. ## 11. Configuration and secrets New SAM parameters: ```text HubSpotPrivateAppToken NoEcho, default empty HubSpotTargetListID default 19544 HubSpotRequestTimeout default 30 seconds HubSpotRetryCount default 3 APACSalesEmails NoEcho, default empty ``` Dispatcher receives the HubSpot settings and target database URL. Worker receives APAC Sales emails. HubSpot credentials are never sent through SQS and are never available to Worker or Scoring. ## 12. Test plan ### Dispatcher - paginates memberships and batches Company reads; - validates membership/company counts; - upserts existing rows and deletes Companies absent from the new snapshot; - rolls back and preserves the prior snapshot on refresh failure; - records failure but continues tenant fan-out using stale metadata; - records `unavailable` when no successful snapshot exists; - leaves `free_trial` excluded from Active selection; - emits progress and final summary logs. ### Worker - HubSpot match takes precedence over APAC Sales owner; - portal/account/domain/official-website matching order; - free email domains never domain-match; - ambiguous domains never auto-match; - APAC Sales-owned tenant routes to the new table and is scored; - destination upsert and other-table delete are atomic/idempotent; - tenant transitions in both directions; - stale snapshot is still used; - unavailable snapshot falls through to Sales-owned/Active routing; - forced Sales-owned fast path writes the three allowlisted IDs directly to `apac_sales_owned_tenants` and skips enrichment and Scoring; - logs contain progress but no raw email/token. ### Scoring - allowlisted bucket selects the correct table; - Sales-owned tenant receives normal score and priority; - HubSpot Active tenant receives normal score and final P0 override; - invalid bucket/override is rejected; - score snapshot records the bucket; - existing score calculation tests remain unchanged. ### Reporting and infrastructure - Summary and Weekly SQL continue to reference only `apac_active_tenants`; - SAM parameters are wired only to required functions; - sensitive parameters are `NoEcho`; - queue-to-DLQ regression tests remain green; - run unit tests, `sam validate --lint`, and `sam build`. ## 13. Rollout 1. Apply schema additions before deploying Lambda code. 2. Deploy with HubSpot token and an empty APAC Sales list initially. 3. Invoke Dispatcher with a small tenant `limit` and inspect sync/log output. 4. Verify Target Account table count equals HubSpot list total. 5. Test known exact-ID and domain examples, including a non-member Company. 6. Inject the approved APAC Sales email set and test both routing directions. 7. Run full-volume smoke testing while schedules remain disabled. 8. Confirm both queues drain, both DLQs remain empty, and Summary excludes Sales-owned rows before considering schedule enablement.