# PQL infrastructure — where the three regions actually are

**Purpose:** the factual companion to [`target-state.md`](target-state.md). That file says what the
finished system looks like; this one says where EMEA, APAC and LATAM are today, and which gap blocks
which action. It is a stock-take, not an action list.

**Method:** direct read-only queries against the three regional databases and reads of the deployed
Lambda source, most recently 2026-08-27. Not from this repo's own docs, several of which were stale on
these exact points. **Counts drift daily** — APAC moved 7,788 → 7,953 within a working day — so treat
every number as a magnitude with an as-of date, never as a constant.

**Convention:** regions are named; people are not. Where a rule or a system has an owner, say the region
or the layer.

---

## 1. Layer by layer

The pipeline is six layers, and the regions are failing at different ones — which is why a fix that works
in one region never generalises on its own.

```
collect → store → score → publish → return → measure
```

### Layer 1 · Collect

> Target guarantee: [`target-state.md`](target-state.md) §2 ① — identity resolution that surfaces ambiguity; every measured value carries an explicit coverage status.

| Signal | EMEA | APAC | LATAM | Note |
|---|---|---|---|---|
| `tenant_avg_qps` | measured for 217 | 653 | 125 | see the correction below — this is **not** a collection gap |
| `cluster_count` | full | full | full | |
| `has_credit_card` | full | full | full | |
| `entity_ai_category` | full | full | full | via `domain_intelligence`, present in all three |
| `icp_categories` | ✗ | ✗ | only region with it | populated for 9 tenants; the literal string `[]` for the rest |
| `tenant_events` (import, spending-limit) | **6,254 rows** | **0 rows** | **0 rows** | table exists in all three; only EMEA is fed |
| workload-depth signals beyond QPS (e.g. first DDL) | ✗ | ✗ | ✗ | no source anywhere — and **parked by direction**: regional operations are converging on QPS as the workload signal, so this is not a gap anyone is waiting on |
| active-user detail | frozen 2026-07-07 | frozen | frozen | upstream, owned outside this pipeline |
| Serverless import events | empty since March | — | — | upstream ETL gap |

**Correction worth carrying into any plan: the QPS "coverage" percentage does not mean what it looks
like.** Measured 2026-08-27, counting only tenants that have at least one cluster to measure:

| | tenants | with ≥1 cluster | with a cluster but no QPS reading |
|---|---|---|---|
| EMEA | 2,716 | 194 | **0** |
| APAC | 7,953 | 538 | 32 |
| LATAM | 314 | 107 | 1 |

QPS is NULL because there is no cluster to measure, not because the exporter is broken. The familiar
8% / 8.4% / 55.7% figures are the share of tenants that ever created a cluster — **a funnel fact, not an
infrastructure fact**, and it should not be budgeted as a collection project. ⚠️ One committed request
doc still frames it as a "fill rate" to be raised; that framing is wrong.

The real collection gaps are: **`tenant_events` unfed in APAC and LATAM**, and **first DDL nowhere**.

### Layer 2 · Store

> Target guarantee: [`target-state.md`](target-state.md) §2 ② — one schema; history retained; raw signals kept beside derived scores; full-population daily writes.

The structural gap, and it is uniform across all three regions.

| Table | Grain | Holds |
|---|---|---|
| `<region>_active_tenants` | one row per tenant, **upserted daily** | current state only — yesterday's value is overwritten |
| `<region>_tenant_score_snapshots` | one row per tenant per day | `total_score`, `priority` — **and no raw signal** |

Verified by `DESC` on all three: `snapshot_date, tenant_id, source_type, total_score, priority,
scored_at` (LATAM adds `icp_categories`, APAC adds `tenant_bucket`). **No `tenant_avg_qps`, no
`cluster_count`.**

Consequence: **no signal has any history.** Every rate-of-change, trend, dispersion, first-crossing and
decay feature is uncomputable — not expensive, not approximate, uncomputable. The value is collected
daily and discarded daily.

Second constraint on the same layer — snapshots are a **rotating sample, not a daily census**:

| | rows/day | population | coverage | history retained |
|---|---|---|---|---|
| EMEA | ~216 | 2,716 | ~8% | 89 days |
| APAC | ~980 | 7,953 | ~12% | 45 days |
| LATAM | ~172 | 314 | ~55% | 4 days |

So even after adding raw columns, per-tenant series would be intermittent: in APAC only ~10% of tenants
have two or more observations inside any 7-day window, and those are the already-visible high scorers.
**Both the added columns and the full-population write are needed together** — either alone leaves the
feature unbuildable. Open request covering exactly this:
[`../../latam/REQUEST_snapshot_signal_history.md`](../../latam/REQUEST_snapshot_signal_history.md) —
written, no engineering ticket filed.

### Layer 3 · Score

> Target guarantee: [`target-state.md`](target-state.md) §2 ③ — one rule set with one review path; thresholds as configuration; rate of change and decay, not only accumulation; sourcing stored, not hand-listed.

**The three regions share a model contract and no longer share an implementation.** This is the single
most expensive assumption to get wrong when planning cross-region work.

| | EMEA | APAC | LATAM |
|---|---|---|---|
| Tenants · P0 / P1 / P2 | 2,716 · 8 / 303 / 2,404 | 7,953 · 45 / 108 / 7,800 (+168 sales-owned) | 314 · 3 / 16 / 295 |
| Model | additive v0 + error gate | `Activity 40 + Fit 30 + Urgency 30` + rule overrides | APAC model, thresholds untuned |
| `error_message` → P1 hard gate | **yes**, bare, no fit filter | no | no |
| `cluster_count ≥ 50` forces P0 | ✗ | **yes** | **yes** |
| `cluster ≥ 10` + known org → P1 | ✗ | **yes** | **yes** |
| HubSpot target list forces P0 | ✗ | **yes** (list `19544`) | ✗ |
| `icp_categories` / ICP floor | ✗ | ✗ | **yes** (floor 25) |
| QPS ≥ 10 gate requires `company_ai_core` | **yes** | — | **yes** |
| `company_ai_core` tenants in region | 6 | 14 | **0** |
| Dormancy / recency gates | ✗ | only in the regional operating layer, not in the pipeline | ✗ |

Two live defects visible in that table, not curiosities:

- **LATAM's ai_core-dependent gates have never fired since deploy** — three P0 gates require a fit
  category that has zero tenants in the region. A rule written against one region cannot be assumed live
  in another; check that region's own source.
- **EMEA carries a scoring artefact**: 219 of 223 `error_message` rows are our own
  `domain intelligence failed / HTTP 429`, and that field both gates into P1 and adds +10 urgency —
  one failure counted twice. ~66% of EMEA P1 is this artefact (288 → ~99 simulated). The fix exists as a
  document, not as code.

### Layer 4 · Publish — score tier out to the marketing system

> Target guarantee: [`target-state.md`](target-state.md) §2 ④ — the channel carries lifecycle state as well as priority; repeat-delivery suppression lives in the delivery system; a configured-but-inert channel fails loudly.

This layer **already works and should not be rebuilt.** The scoring Lambda PATCHes a HubSpot Contact
property on the email-matched contact after the local transaction commits; Ops then filters dynamic
lists on that property to trigger outbound email. **That property is what makes the email machine run.**

| Region | State |
|---|---|
| APAC | **Live in production since 2026-07-21**, token configured, property `apac_pql_priority_tier`. Active tenants only; sales-owned tenants log `sales_owned_skipped` |
| EMEA | **Code built**, same mechanism, `emea_pql_priority_tier`. ⚠️ **Whether the token is set is unconfirmed — an empty token silently records `disabled` and leaves local scoring running.** Verify before relying on it |
| LATAM | **Not built.** The fork dropped every HubSpot reference from the template and the scoring function |

One shared HubSpot private app across regions, not one per region.

### Layer 5 · Return — follow-up state back to the model

> Target guarantee: [`target-state.md`](target-state.md) §2 ⑤ — state returns through signals that fill themselves; both the state and the evidence of a touch are captured; no human holds a database credential.

| Region | `track_step` populated | Last write |
|---|---|---|
| EMEA | 316 of 2,716 (11.6%) | **2026-08-06** |
| APAC | 0.0% | never |
| LATAM | 0.0% | never |

Read `0.0%` as **"not in our database"**, never as "nobody tracked it", and never as "we cannot read it".
APAC's follow-up is tracked — in CRM lead status, edited by hand, plus a regional queue table the
operating layer reads back to suppress already-worked rows. CRM is readable today through a read-only
per-user interface that needs no grant. What is missing is the return path into the PQL dataset.

Two constraints shape any fix:

- **Credentials.** In APAC and LATAM the DB write path belongs to the pipeline's own credential and the
  analyst accounts are `SELECT`-only. EMEA can write only because it holds cluster root — an
  over-privilege, not a precedent.
- **The CRM cannot supply history.** Verified 2026-08-27 through the read interface: the lead history
  object is retrievable **only** by `LeadId = <literal>` — a scan for "what changed recently" is rejected
  by the API — and the rows returned carry `Field = "__created__"` with empty old/new values. Activity
  objects (`Event`, `Task`) time out (504) on every query, filtered or not. So change history and
  activity history cannot be retrieved from CRM at all; **whatever history the model needs has to be
  captured by the pipeline at the time**.

### Layer 6 · Measure

> Target guarantee: [`target-state.md`](target-state.md) §2 ⑥ — the effect of an intervention on a tenant is provable, and delivery-channel outcomes join back to the tenant that triggered them.

Not currently possible in any region. Showing that an action moved a tenant — from "created a cluster but
never queried" to "running a workload" — requires the signal's history, which layer 2 does not keep.
Send-and-reply outcomes from the marketing system are also not joined back to PQL.

**This is the strongest argument for the layer-2 fix, and it is stronger than the scoring argument: it
blocks proving that any operational action worked.**

---

## 2. Topology and access shape

| | EMEA | APAC | LATAM |
|---|---|---|---|
| Cluster | `24PhqKzMs8ahE6U` (us-west-2) | `c5P1VZxiR27eA4p` (**ap-southeast-1**) | `24PhqKzMs8ahE6U` (us-west-2) |
| Database | `regional_support` | `regional_support` — different cluster, same name | `latam_regional_support` |
| Analyst credential | ⚠️ cluster **root** — can write and drop across every database on that cluster, LATAM's included | scoped read-only | scoped read-only |
| Engineering repo | `emea_lambda_tasks` | `apac_lambda_tasks` | `latam_lambda_tasks` |

**It is two clusters, not three.** EMEA and LATAM share one and are separated only by database name;
APAC is physically separate in another AWS region. Whether that separation is a residency or latency
requirement, or historical, is an open question and it decides whether "one database" is even available
as a target.

## 3. Schema divergence

| Concept | EMEA | APAC | LATAM |
|---|---|---|---|
| New signup | separate table `emea_new_signup` (2,668) | `source_type='new_signup'` | `source_type='new_signup'` |
| Sales ownership | not modelled | `apac_sales_owned_tenants` (168) — scored, excluded from all reporting | not modelled |
| CRM owner decision | not modelled | `apac_effective_sales_owner_snapshot` — 971 tenants (12%), owner fields only | not modelled |
| Target-account list | not modelled | `apac_hubspot_target_accounts` (286) | not modelled |
| Product events | `emea_tenant_events` **+ `_old` + `_top20`** | table exists, 0 rows | table exists, 0 rows |
| Curated leads | `emea_key_leads` (109) | not modelled | `latam_key_leads` (**0 rows**) |
| Domain enrichment | `domain_intelligence` | `domain_intelligence` | `domain_intelligence` |

Read the pattern, not the rows: **every region invented a concept the other two lack, and every region
carries a stub of something it never wired.** No region is the reference implementation, and any
cross-region query today needs three dialects of the same idea.

## 4. Rule divergence

Covered in the Layer 3 table above. The summary judgement: of the rules that exist in exactly one region,
**none has a stated regional reason** — one is a defect, one can never fire where it is deployed, and the
rest are simply where someone happened to be working. Only four things have a legitimate reason to differ
per region, and they are listed in [`target-state.md`](target-state.md) §4 — along with the declaration
a region fills in so that "our ICP is different" is expressible as values rather than as a second
implementation. Today every region expresses it as code, which is why this section exists.

## 5. The operating layer each region grew outside the pipeline

Every region has one, because the pipeline did not deliver something the humans needed. The rules those
layers invented are unversioned, unreviewed, and invisible to scoring — that is a design signal about the
pipeline, not misbehaviour by the regions.

| Region | Shape | Where its logic lives | What it compensates for |
|---|---|---|---|
| EMEA | an outreach pack plus a person recording follow-up state by hand | a runbook and a spreadsheet habit | no return path; no queue hygiene |
| APAC | a scheduled agent that **re-scores every tenant independently** and applies its own liveness gates, new-signup fast lane, exclusion list, staleness compression and follow-up de-duplication | a scheduled-task prompt, mirrored to a repo that had drifted 4 weeks out of date | no recency term upstream; no return path; queue noise |
| LATAM | a spreadsheet-first flow, deliberately, while the population is small | a recommendation document | nothing yet — the population has not forced the issue |

APAC's case is the sharpest: **two scoring engines run daily against the same table and disagree**, and
the one the humans actually act on is the unofficial one.

## 6. Gaps ranked by what each unblocks

| # | Gap | Layer | Unblocks | Region | Kind |
|---|---|---|---|---|---|
| 1 | Snapshots keep no raw signal and cover a rotating ~8–55%/day | store | every trend/decay feature **and all intervention measurement** | all | infrastructure |
| 2 | `tenant_events` unfed | collect | import / spending-limit signals, event-based decay | APAC, LATAM | infrastructure |
| 3 | No scoped write path for follow-up state | return | state returning to the dataset; PQL→pipeline measurement | APAC, LATAM | infrastructure |
| 4 | Publish token unverified | publish | email triggering at all in that region | EMEA | configuration |
| 5 | Publish path absent | publish | email triggering in that region | LATAM | configuration |
| 6 | Regional scoring implementations diverged | score | any rule intended to apply cross-region | all | logic |
| 7 | Enrichment-failure artefact inside the P1 gate | score | ~66% of that region's P1 being real | EMEA | logic |
| — | Workload-depth signals beyond QPS have no source | collect | a finer aha rung than QPS provides | all | **parked by direction** — operations are converging on QPS |

**The four categories are the same ones used in [`target-state.md`](target-state.md) §6** — *data source*,
*infrastructure*, *logic*, *configuration*. Items 4–5 are configuration and 6–7 are logic; neither needs
infrastructure work. Keeping that line visible is what stops a configuration problem being scheduled as
an infra project. Gaps 1–3 are infrastructure; where a gap needs a source that does not exist or has not
been granted, it is marked *data source* instead.

## 7. Near-term operational readiness

Four mutually exclusive lifecycle states are derivable from the current row today, with email addresses
populated for 100% of tenants in all three regions:

| State | Meaning | EMEA | APAC | LATAM |
|---|---|---|---:|---:|---:|
| cluster > 0, QPS = 0 | created a cluster, never ran a query | 105 | 302 | 57 |
| 0 < QPS < 1 | tried it, never scaled | 63 | 130 | 28 |
| QPS ≥ 1, no card | real usage, not yet paying | 15 | 41 | 2 |
| card on file, QPS = 0 | paying, not using | 34 | 119 | 42 |

*(as of 2026-08-26; QPS here is a point sample — see the invariants.)*

What acting on them needs, in order: a **second published property** carrying lifecycle state, written by
the same mechanism that already writes the priority tier (tier answers "does this deserve a person",
state answers "where is this tenant stuck" — one field cannot carry both); **verification of the EMEA
token**; a **LATAM publish path**, or an accepted manual process while the population is small; and
**repeat-delivery suppression in the marketing workflow, not in the database**, because the return layer
is too empty to de-duplicate against.

These are **state** triggers ("where you are"), not **event** triggers ("what just changed"). Every
event-shaped trigger — first crossing of a QPS threshold, sustained rise, drop to zero as a churn warning
— needs the store-layer fix first.

## 8. Open questions

**All regions** — is the cluster separation (§2) a requirement or an accident? Which system of record
holds follow-up state per region, and does that answer have to be the same everywhere? Who owns a rule
once it is shared?

**EMEA** — is the publish token actually set? Does the analyst credential stay cluster root?

**APAC** — do the two scoring engines converge, or does the operating layer stay separate with its rules
merely made visible? The unofficial engine is the one the humans act on today.

**LATAM** — at this population, is a spreadsheet still the right answer, and what population size changes
that? What should replace the ai_core-dependent gates that cannot fire here?

**Cross-cutting, not infrastructure** — a population of tenants with real repeated workload is classified
`individual` and sits unattended in P2 in every region; no field in these databases separates a real
individual from a real company on a free email domain. That is a data absence and a regional GTM decision,
not a scoring defect.

---

## 9. Related records

| Where | What |
|---|---|
| [`target-state.md`](target-state.md) | What the finished system looks like |
| [`../qps-timeseries-signal-contract.md`](../qps-timeseries-signal-contract.md) | Feature contract; Tier A computable now, Tier B blocked on the store layer |
| [`../../latam/REQUEST_snapshot_signal_history.md`](../../latam/REQUEST_snapshot_signal_history.md) | The store-layer fix — written, no ticket filed |
| [`../REQUEST_tenant_daily_qps_view.md`](../REQUEST_tenant_daily_qps_view.md) · [#18](https://github.com/pingcap-inc/pql-infrastructure/issues/18) | True 24h-average QPS rollup — the durable end state for the collect layer |
| [`../REQUEST_nexuscrm_read_access.md`](../REQUEST_nexuscrm_read_access.md) | Batch CRM mirror access, for cohort analysis and the return path |
| [`../writeback-status-by-region.md`](../writeback-status-by-region.md) | Publish and return layers in full, per region |
| [`../../emea/emea-signal-model-v0-patch-2026-08-26.sql`](../../emea/emea-signal-model-v0-patch-2026-08-26.sql) | Score-layer fixes for EMEA — document only, not implemented |
| [pingcap-support#19](https://github.com/pingcap-inc/pingcap-support/pull/19) | LATAM interim QPS/billing scoring change, open |