# PQL write-back — who does what, per region

**Scope: all three regions.** This file lived in `latam/` until 2026-08-26, which wrongly implied
LATAM owned the topic; the comparison is region-agnostic, so it belongs here. It is also not the
place to look up how any single region *operates* — for APAC that is
[`../apac/apac-operating-mechanism-2026-08-26.md`](../apac/apac-operating-mechanism-2026-08-26.md).

Written 2026-08-26. Numbers are from live queries against the three regional databases and from
reading `pingcap-support` source on that date, not from this repo's own docs — several of which are
stale on this exact topic (see the correction at the bottom).

## There are TWO different write-backs. Don't conflate them.

| | **A. Score tier → CRM** | **B. Follow-up state → DB** |
|---|---|---|
| What | model writes `P0/P1/P2` out to a CRM property | a human records that outreach happened |
| Field | HubSpot Contact `apac_pql_priority_tier` | `<region>_active_tenants.track_step` |
| Who writes | the Scoring Lambda, automatically | a person, manually |
| Purpose | lets Ops trigger CRM workflows / lists | connected-guard + release-to-P1 + PQL→SQL measurement |

**A is automated and live in APAC. B — as a column in our database — is manual and effectively dead
everywhere.** The question "我们跟进了要写回" is about **B**.

⚠️ **A dead `track_step` is not the same as untracked follow-up.** APAC's follow-up *is* tracked —
in **NexusCRM** (SDR edits Lead Status by hand; it is the system of record) and surfaced through the
Feishu Bitable queue. What is missing is the *return path into the PQL dataset*, which is what makes
follow-up invisible to scoring and unmeasurable in SQL. Read every `0.0%` below as "not in our
database", never as "nobody tracked it" — and never as "we cannot read it": NexusCRM reads work
today through the read-only OAuth app, running as the signed-in user, no grant required
(the connection details are held outside this repo).

## Per-region status

### A. Score tier → HubSpot

| Region | Status |
|---|---|
| APAC | **Live in production since 2026-07-21, token configured.** Scoring PATCHes `apac_pql_priority_tier` on the HubSpot contact matched by normalized email, after the local score transaction commits. `404` → non-retryable `contact_not_found`; `429`/`5xx`/network → retried. Only Active tenants write back; sales-owned tenants are scored and snapshotted but log `sales_owned_skipped`. SQS `MaximumConcurrency: 2` bounds request rate. Source: `apac_lambda_tasks/HANDOFF.md` §8 + `APAC-Tenants-Scoring-Task/scoring_function.py:182`. |
| EMEA | **Built — same mechanism, property `emea_pql_priority_tier`.** `emea_lambda_tasks/scoring/scoring_function.py` PATCHes the email-matched Contact after the score transaction commits, never creates one. Its README says to deploy it with *"the same private-app token used by the APAC scoring Lambda"* — so this is **one shared HubSpot private app**, not one per region. Whether EMEA's deployment actually has the token set is not stated in the repo and I could not confirm it; an empty token silently records `disabled` and leaves local scoring running. |
| LATAM | **Not built.** `latam_lambda_tasks/template.yaml` and `scoring_function.py` contain no HubSpot reference at all — the fork dropped it. This is the one real gap of the three. |

### B. Follow-up state → `track_step`

| Region | Populated | Last write | Mechanism |
|---|---|---|---|
| EMEA | **13.4%** — 282 at `1`, 34 at `2`, 2,366 at `0` | **2026-08-06** (20 days stale) | manual |
| APAC | **0.0%** | never | none *in the DB* — tracked in NexusCRM Lead Status instead (manual, SDR-owned) |
| LATAM | **0.0%** (all 221 rows) | never | none |

APAC's mechanism, in detail: SDRs record follow-up as **NexusCRM Lead Status, edited by hand**,
plus a Feishu Bitable queue ("PLG Digest History") whose 状态/SDR Owner fields they edit in place;
the daily-digest agent reads that back to dedupe already-worked cards. None of it returns to
`apac_active_tenants`, and the APAC analyst credential is SELECT-only, so nobody on the GTM side can
write the column even manually. Full chain:
[`../apac/apac-operating-mechanism-2026-08-26.md`](../apac/apac-operating-mechanism-2026-08-26.md) §1.

EMEA's mechanism is step 5 of [`../emea/emea-emails-operating-pack/how-kerry-uses-gtm-skills.md`](../emea/emea-emails-operating-pack/how-kerry-uses-gtm-skills.md):
*"发出 → HubSpot / sheet 记 track_step=email"*. A person sets `1` after sending, `2` when a channel is
accepted. It works while someone does it, and stops when they don't.

## Values (EMEA's definition — canon, don't redefine per region)

| `track_step` | label | meaning |
|---|---|---|
| 0 / NULL | N/A | no manual follow-up state |
| 1 | email | outreach sent / follow-up started |
| 2 | connected | **a human comms channel is established** |

`2` is **not** a product or database connection — historically a Slack invite accepted. For LATAM
that means Discord / WhatsApp / Slack accepted, **not** a reply to the first email.

**NULL trap:** `track_step <> 2` evaluates to NULL (not TRUE) on NULL input, silently dropping those
tenants from every queue. Always `COALESCE(track_step, 0) <> 2`. LATAM's column is
`NOT NULL DEFAULT '0'`, so it is safe today — keep the COALESCE form anyway.

## Who holds which credential (this is the actual blocker for B)

| Holder | Region | Account | Can write? |
|---|---|---|---|
| GTM / analysis | EMEA | `24PhqKzMs8ahE6U.root` | **yes — full cluster, incl. LATAM's DB** ⚠️ over-privileged, see [`../latam/latam-data-source.md`](../latam/latam-data-source.md) |
| GTM / analysis | APAC | `c5P1VZxiR27eA4p.readonly_user` | no — `GRANT SELECT ON regional_support.*` only |
| GTM / analysis | LATAM | `24PhqKzMs8ahE6U.latam_readonly` | no — `GRANT SELECT ON latam_regional_support.*` only |
| Lambda pipeline | APAC, LATAM | `TargetDatabaseURL` — NoEcho stack parameter, held by engineering (Yujie) | yes |

So for APAC and LATAM the write path exists but belongs to the Lambda's credential. **Ops cannot
write `track_step` in either region today.** EMEA can only because it was handed cluster root, which
is the wrong fix, not a precedent to copy.

**Concrete ask to engineering:** a scoped ops account per region with
`UPDATE (track_step, track_update_time) ON <region>_active_tenants` and nothing else — or a small
endpoint/bot action that does the same, so no human needs DB credentials at all.

## Both CRMs are load-bearing — this is not an either/or

**HubSpot cannot be dropped in favour of NexusCRM.** Ops triggers the outbound emails from HubSpot
workflows / dynamic lists filtered on `apac_pql_priority_tier`, so the tier write-back (A) is what
makes the email machine run — see
[`../apac/bot-to-hubspot-writeback/bot-to-hubspot-writeback.md`](../apac/bot-to-hubspot-writeback/bot-to-hubspot-writeback.md).
NexusCRM is the system of record for *follow-up state and ownership*. Two systems, two jobs:

| | NexusCRM | HubSpot |
|---|---|---|
| Role | system of record: owner, Lead Status, opportunity | marketing execution: target list + email workflows |
| PQL reads | owner/opportunity via the BigQuery mirror (`nexuscrm_final.*`) | Target list `19544` → forces P0 |
| PQL writes | ✗ nothing | ✓ `apac_pql_priority_tier` on the matched Contact |
| Human writes | SDR edits Lead Status by hand | Ops builds/triggers the email workflows |

The still-open gap is the *third* leg: whether the email/track status HubSpot produces after a send
comes back anywhere. The `APAC PQL Writeback` key was scoped for it ("write email/track status after
Ops sends") but nothing in the pipeline reads it, so send-and-reply outcomes are not joined to PQL
today.

## Remaining open question for LATAM

**Which CRM does LATAM wire first?** APAC's shipped write-back targets HubSpot. But APAC's owner resolution
already reads NexusCRM (`apac_effective_sales_owner_sync_state`, rule version
`active_account_then_open_opportunity_v2`, last success 2026-08-26), and EMEA's engineering handoff
lists *"no NexusCRM dependency"* as an explicit non-goal. Settle the system of record for PQL
follow-up state before wiring LATAM.

## Recommended for LATAM now

221 tenants, 1 P0, 15 P1 (2026-08-26) — spreadsheet-sized. Don't block outreach on an integration:

1. Jasper records outreach as he sends (tenant_id, date, channel, outcome).
2. Weekly batch-apply to `track_step` — needs the scoped ops account above.
3. Automate when the daily P0 count makes step 2 painful, and build against whichever CRM wins.

---

## Correction

An earlier version of this file said APAC's write-back was "designed only — no code", citing
[`../apac/execution/README.md`](../apac/execution/README.md) ("Only the read direction is live").
**That is stale.** That note was written 2026-07-21; the write-back deployed to production the same
day and the token is configured. APAC's Scoring Lambda has been writing tier to HubSpot for a month.
The `apac/execution/README.md` line should be updated.