# APAC Tenant Signal Pipeline — Session Handoff Last updated: 2026-07-15 16:46 (Asia/Shanghai) This document is the current handoff for `apac_lambda_tasks/`. It supersedes earlier notes about Key Leads, schedules, and database migration state. ## 1. Current objective The pipeline: 1. selects APAC active tenants and previous-CST-day APAC signups from DevOps; 2. refreshes and uses HubSpot Company list `19544` as the Target Account list; 3. enriches tenants with domain, cluster/service-plan/QPS, Billing, and credit signals; 4. routes tenants to Active or Sales-owned storage; 5. calculates a normal numeric score and P0/P1/P2 priority; 6. sends deterministic daily and weekly Feishu reports. The project is managed as one AWS SAM/CloudFormation stack. Do not manually replace Lambda functions or commit credentials. ## 2. Repository and workspace - Repository: `pingcap-inc/pingcap-support` - Working directory: `apac_lambda_tasks/` - Branch: `agent/apac-routing-cst` (based on the latest `origin/main` after PR #3 merged the original pipeline branch) - Stack: `apac-tenant-monitoring` - AWS account: `552185537300` - Region: `us-west-2` - CLI profile: `552185537300_DBaaS-DevUser-Role` The worktree also contains unrelated EMEA, bot, and copied Lambda artifacts. Always run `git status --short`; never stage, revert, delete, or commit unrelated files. Only stage paths under `apac_lambda_tasks/` for this branch. Never put database passwords, HubSpot tokens, API keys, Feishu webhooks, or other secrets in this document, source, Git commits, or `samconfig.toml`. ## 3. Deployed architecture Five Python 3.12 Lambda functions are deployed: - `APAC-Tenants-Monitor-Fanout-Dispatcher-Task` - `APAC-Tenants-Monitor-Fanout-Worker-Task` - `APAC-Tenants-Scoring-Task` - `APAC-Tenants-Summary-Task` - `APAC-Weekly-Tenant-Metrics-Task` Data flow: ```text Dispatcher -> refresh latest complete HubSpot target snapshot -> select DevOps active candidates and previous-day signups -> Monitor SQS -> Worker enrichment and routing -> apac_active_tenants or apac_sales_owned_tenants -> Scoring SQS -> Scoring and daily score snapshot Summary / Weekly -> read apac_active_tenants only -> Feishu ``` Dispatcher code is split intentionally: - `dispatcher_function.py`: DevOps selection, SQS fan-out, handler orchestration; - `hubspot_sync.py`: HubSpot API, normalization, validation, transactional snapshot refresh, and stale-snapshot fallback. The stack update completed successfully on 2026-07-14 around 21:01 Asia/Shanghai. Dispatcher, Worker, Scoring, and Summary were updated; all five functions were Active after deployment. The later CST natural-day boundary changes are validated locally but have not yet been deployed. Deploy the new branch before treating CST boundaries as the production runtime behavior. On 2026-07-15, Worker was updated directly through the Lambda API with commit `2b5bc42` behavior, and its `APAC_SALES_EMAILS` environment variable was set to the user-supplied list, later expanded to 17 addresses. Summary was updated directly with commit `4b2676c` behavior to add a HubSpot Target section and show Sales owner in Target, P0, and P1 details. A SAM changeset was created but deliberately deleted without execution because it included all three EventBridge rules and could have reverted Dispatcher scheduling. Worker code/environment and Summary code now have intentional CloudFormation drift; a later stack deployment must preserve the email list and the actual scheduling state. ## 4. Actual schedule state Point-in-time AWS state re-verified on 2026-07-21: | Rule | Actual state | Actual expression | Asia/Shanghai | | --- | --- | --- | --- | | Dispatcher Daily | `ENABLED` | `cron(30 0 * * ? *)` | daily 08:30 | | Summary Daily | `ENABLED` | `cron(0 4 * * ? *)` | daily 12:00 | | Weekly Metrics | `DISABLED` | `cron(0 7 ? * MON *)` | Monday 15:00 | The user explicitly said Dispatcher `cron(30 0 * * ? *)` is authoritative. `template.yaml` defines separate schedule-state parameters, defaults Dispatcher and Summary to enabled and Weekly to disabled, and uses `cron(30 0 * * ? *)` for Dispatcher plus `cron(0 4 * * ? *)` for Summary. The production stack parameters and EventBridge rules were aligned with these values on 2026-07-21. Production Worker currently has 17 APAC Sales email entries. CloudFormation masks the NoEcho `APACSalesEmails` value as `****`, so read-only inspection cannot prove it equals the Lambda environment. Do not hardcode the list in the template; securely pass the current 17-entry value during deployment. ## 5. Database state Target database: `regional_support`. Current tables used by the pipeline: - `apac_active_tenants` - `apac_sales_owned_tenants` - `apac_hubspot_target_accounts` - `apac_hubspot_target_sync_state` - `apac_tenant_events` - `apac_tenant_score_snapshots` - `domain_intelligence` The production migration `migration_hubspot_sales_routing.sql` was applied successfully on 2026-07-14. It: - added HubSpot match fields to `apac_active_tenants`; - created `apac_sales_owned_tenants`; - created the two HubSpot snapshot/state tables; - added `tenant_bucket` to score snapshots; - removed `apac_active_tenants.key_lead`; - dropped the empty `apac_key_leads` table. Key Leads has been deliberately and completely removed from Worker, Scoring, Summary, SAM configuration, schema, tests, and production database. Do not restore it unless the user changes this decision. On 2026-07-15, a transaction moved all current non-HubSpot tenants owned by the configured APAC Sales set, including the three forced fast-path IDs, from Active to Sales-owned. Post-migration verification showed: - `apac_active_tenants`: 1,034 total; P0 33, P1 29, P2 972; - `apac_sales_owned_tenants`: 161 total; P0 26, P1 15, P2 120; - zero duplicate tenant IDs across the two tables; - zero configured, non-HubSpot Sales-owned candidates left in Active; - all 15 HubSpot Targets remained Active; - no HubSpot Target existed in Sales-owned. There is no `apac_new_signup` table. New signups are stored in one of the two tenant tables based on routing. `apac_tenant_events` has no writer in this project. Import metrics depend on an external producer. ## 6. HubSpot synchronization Dispatcher attempts the HubSpot refresh before selecting DevOps tenants. It: 1. checks list processing status; 2. paginates all list memberships; 3. batch-reads Company properties; 4. validates membership and Company counts; 5. upserts the complete snapshot and deletes Companies no longer in the list in one transaction; 6. updates sync state. If refresh fails, fan-out continues and Worker uses the most recent successful snapshot. If no successful snapshot exists, HubSpot matching is unavailable and routing falls through to the Sales-owned rule. A production smoke invocation performed only HubSpot synchronization—no tenant selection and no SQS messages. It succeeded with: - list ID: `19544`; - snapshot status: `fresh`; - Company count: `286` at that point in time; - database snapshot count: `286`; - sync-state status: `success`. The list is dynamic; do not hard-code 286 as an invariant. ## 7. Routing precedence For each non-forced tenant, Worker enriches first, then applies: ```text HubSpot target match -> apac_active_tenants -> calculate normal total_score -> force final priority to P0 otherwise sales_owner_email in configured APAC Sales set -> apac_sales_owned_tenants -> normal score and normal priority otherwise -> apac_active_tenants -> normal score and normal priority ``` HubSpot always wins over Sales ownership. Worker removes the same tenant ID from the other tenant table in the same transaction and preserves score plus manual tracking fields when moving buckets. HubSpot match order: 1. exact `tenant.id == portal_id__c`; 2. exact non-empty `tenant.custom_id == account_id__c`; 3. unique non-personal email registrable-domain match; 4. unique Domain Intelligence official-website domain match. Ambiguous domains do not auto-match. Free/personal email domains do not participate in domain matching. HubSpot evidence stored on Active rows: - `hubspot_target`; - `hubspot_company_id`; - `hubspot_match_method`; - `hubspot_snapshot_synced_at`. ## 8. APAC Sales configuration `APAC_SALES_EMAILS` is an exact, case-insensitive comma/semicolon-separated email set supplied through the `APACSalesEmails` NoEcho stack parameter. Worker currently has the 17-address APAC Sales set supplied by the user on 2026-07-15, including the later additions `xianming.dou@pingcap.com` and `lili@pingcap.com`. It was applied directly to the Lambda environment rather than the CloudFormation parameter, so a later stack deployment can overwrite it unless the same `APACSalesEmails` value is supplied. Summary and Weekly intentionally never scan `apac_sales_owned_tenants`. Scoring now supports direct priority writeback to an existing HubSpot Contact after the local score transaction commits. It matches the normalized tenant email exactly, PATCHes only `apac_pql_priority_tier`, treats `404` as a non-retryable `contact_not_found`, and retries `429`, `5xx`, and network failures. The token is supplied separately through the NoEcho `HubSpotWritebackPrivateAppToken` stack parameter. An empty token keeps local scoring active while disabling writeback. This behavior was deployed to production on 2026-07-21, and the token is configured. Only Active tenants write back to HubSpot. Sales-owned tenants are still scored and snapshotted locally but log `sales_owned_skipped`. The Scoring SQS event source uses `MaximumConcurrency: 2` to bound HubSpot request concurrency. The production mapping was verified at 2 after correcting pre-existing runtime drift during the 2026-07-21 deployment. ## 9. Tenant selection Active candidates: ```sql status = 'active' AND kind = 'external' AND sales_force_region = 'APAC' AND country NOT IN ('JP') AND plan NOT IN ('free_trial', 'new_user', 'closing') ``` There is no fallback from `sales_force_region` to `country_region` for Active. Previous CST natural-day signups (the database timestamps remain UTC, so CST midnight is converted to the corresponding UTC boundary): ```sql created_at >= previous CST day 00:00 (UTC boundary) AND created_at < current CST day 00:00 (UTC boundary) AND country_region = 'APAC' AND country NOT IN ('JP') ``` Signup selection intentionally has no status, kind, or plan filter. Scoring snapshot dates and Weekly complete-week boundaries also use CST natural days. Audit timestamps, rolling 24-hour/7-day windows, TTLs, and elapsed-time calculations remain UTC-based. The user reviewed the theoretical possibility that one tenant could enter both sets and said to leave the current behavior unchanged for now. ## 10. Worker behavior For Active candidates Worker collects: - Domain Intelligence; - complete DevOps cluster pagination and service-plan counts; - per-cluster Clinic QPS when total clusters are fewer than 10; - Billing credit-card state and active credit summary. Rules: - known free email providers classify as `individual` without web search; - non-free domains use cached Domain Intelligence, then OpenAI web search; - all DevOps cluster pages are read; - at 10+ clusters, skip per-cluster Clinic calls and store QPS as `NULL`; - a partial Clinic failure stores QPS as `NULL`, not a partial average or zero; - New Signup performs Domain Intelligence only; - enrichment failures are recorded and still sent to Scoring when possible; - scoring-send failure causes the Monitor SQS record to retry the whole idempotent upsert workflow. Forced Sales-owned fast-path tenant IDs: - `1372813089209260919` - `1372813089209300444` - `1372813089209301246` They preserve existing enrichment and P0, write directly to `apac_sales_owned_tenants`, remove the Active row, and skip Domain, cluster, Clinic, Billing, and Scoring calls. This fast path runs before HubSpot and sales-owner matching. ## 11. Scoring Scoring accepts only two buckets: ```text active -> apac_active_tenants sales_owned -> apac_sales_owned_tenants ``` It always computes the normal numeric `total_score`. Only a HubSpot Target message may carry `priority_override = P0` with reason `hubspot_target_account`; this overrides final priority, not score. `apac_tenant_score_snapshots.tenant_bucket` records which tenant table owned the row when scoring occurred. The snapshot primary key remains `(snapshot_date, tenant_id)`, so a same-day bucket move updates that day's row rather than storing two rows. The exact scoring model and priority gates live in `APAC-Tenants-Scoring-Task/scoring_function.py` and its README. ## 12. Summary and Weekly Daily Summary reads `apac_active_tenants` only. It reports: - previous-CST-day signup totals; - non-individual and AI-related signup totals; - P0 details; - P1 details; - HubSpot Target Account total and details, including match method and Sales owner; - successful import activity from `apac_tenant_events`. Key Lead sections have been removed. Weekly Metrics also reads `apac_active_tenants` only and uses the previous complete CST Sunday-to-Saturday window for signup totals. The user reviewed the observation that old Active rows may remain eligible for Weekly metrics after they stop being selected and said to leave the current behavior unchanged. No `last_active_candidate_at` field was added. ## 13. Observability and runtime verification Structured logs exist for HubSpot page/batch progress, Dispatcher enqueue progress, per-tenant Worker routing/outcome, and Scoring result. Point-in-time checks after deployment: - Monitor queue: 0 visible / 0 in flight; - Monitor DLQ: 0 / 0; - Scoring queue: 0 / 0; - Scoring DLQ: 0 / 0; - CloudWatch APAC alarms: `OK`; - recent Lambda Error count: zero; - recent throttle count: zero; - no recent structured `hubspot_sync_failed`, `tenant_enrichment_failed_recorded`, `tenant_processing_failed`, `scoring_failed`, or `scoring_tenant_missing` events were found; - Worker observed maximum duration was about 40 seconds, below its 900-second timeout. CloudWatch alarms currently have no SNS/notification action because `AlarmTopicArn` is empty. The user asked about Feishu alerts, reviewed the SNS -> notifier Lambda -> Feishu option, and said to leave it unchanged for now. ## 14. Validation completed Latest completed local checks after the CST natural-day update: ```text 45 unit tests passed Python compileall passed sam validate --lint passed sam build passed git diff --check passed ``` The final SAM deployment succeeded and the production database migration was verified afterward. ## 15. Known reviewed items — leave unchanged for now The user explicitly said the following do not need changes now: 1. theoretical Active/New Signup overlap and lightweight Signup overwrite risk; 2. Weekly metrics potentially retaining stale Active rows; 3. CloudWatch alarms not notifying a Feishu group; 4. actual Dispatcher schedule drift, with actual `cron(30 0 * * ? *)` treated as authoritative. Do not reopen or implement these without a new user request. ## 16. Next useful actions 1. Obtain the approved APAC Sales email set and update `APACSalesEmails` securely. 2. Run a limited/full Dispatcher cycle after that update and verify both tenant buckets plus both queues/DLQs. 3. Commit and push only the intended APAC changes if requested. 4. Align `template.yaml` with the authoritative Dispatcher schedule only when the user explicitly asks. ## 17. Source-of-truth files - Overall design: `HUBSPOT-SALES-ROUTING-DESIGN.md` - Operations: `README.md` - Infrastructure: `template.yaml` - Fresh schema: `schema.sql` - Existing-DB migration: `migration_hubspot_sales_routing.sql` - Dispatcher orchestration: `APAC-Tenants-Monitor-Fanout-Dispatcher-Task/dispatcher_function.py` - HubSpot sync: `APAC-Tenants-Monitor-Fanout-Dispatcher-Task/hubspot_sync.py` - Worker handler: `APAC-Tenants-Monitor-Fanout-Worker-Task/worker_function.py` - Worker enrichment/routing: `APAC-Tenants-Monitor-Fanout-Worker-Task/tenant_processor.py` - Cluster/QPS: `APAC-Tenants-Monitor-Fanout-Worker-Task/cluster_metrics.py` - Scoring: `APAC-Tenants-Scoring-Task/scoring_function.py` - Daily report: `APAC-Tenants-Summary-Task/summary_function.py` - Weekly report: `APAC-Weekly-Tenant-Metrics-Task/weekly_metrics_function.py` ## 18. Safety reminders - Never expose or commit credentials supplied in chat or Lambda environments. - Do not use `git add .` in the dirty worktree. - Do not re-create Key Leads. - Do not include Sales-owned rows in Summary or Weekly. - Do not re-include `free_trial` in Active selection. - Do not treat a HubSpot Company outside list `19544` as a Target Account. - Do not replace unknown QPS with measured zero. - Do not change actual schedules or send Feishu test messages without explicit authorization.