# Engineering Request: persist raw signals in `*_tenant_score_snapshots`

**Target repo:** `pingcap-inc/pingcap-support` → `latam_lambda_tasks` (reference landing), then
`emea_lambda_tasks` / `apac_lambda_tasks` — the three schemas are isomorphic.
**Status:** ⚠️ **DRAFTED LOCALLY — NOT PUSHED.** This file lives in `pql-infrastructure/latam/` because
that is where the requirement originates; its delivery target is the engineering repo above.
**Raised by:** Growth (heidi.liu@pingcap.com) · **Date:** 2026-08-26
**Engineering owner:** Yujie
**Blocks:** every QPS time-series feature in
[`universal/qps-timeseries-signal-contract.md`](../universal/qps-timeseries-signal-contract.md)

---

## 1. Why

The PQL model tiers tenants on `tenant_avg_qps`, but **no history of that value has ever been
kept**. `*_active_tenants` is keyed on `tenant_id` and upserted daily, so the previous day's QPS is
overwritten. `*_tenant_score_snapshots` keeps a daily row, but stores only the *derived* score —
never the raw signal that produced it.

Verified 2026-08-26 by reading `schema.sql` in the engineering repo and by direct read-only queries
against all three regional databases:

```
emea_tenant_score_snapshots:  snapshot_date, tenant_id, source_type, total_score, priority, scored_at
apac_tenant_score_snapshots:  + tenant_bucket
latam_tenant_score_snapshots: + icp_categories
```

**None of the three carries `tenant_avg_qps`, `cluster_count`, or any other raw signal.**

The consequence is not "we need more data" — it is that a value we already collect every day is
discarded every day. A single-day QPS reading cannot distinguish a production batch workload from a
dormant tenant, which is the direct cause of the tier-flapping recorded in the 2026-07-30 data-source
audit (one tenant flipping P0↔P1 across five consecutive days on readings spanning three orders of
magnitude).

This request is deliberately small: it does **not** need backfill, does **not** depend on the data
team, and is roughly an order of magnitude less work than the global `tenant_daily_qps` rollup
([pql-infrastructure#18](https://github.com/pingcap-inc/pql-infrastructure/issues/18)). Accumulation starts the
day it ships; 14 days later the first slope and dispersion features become computable.

**Added 2026-08-27 — this request now has a live consumer.** A regional operating layer has shipped a
rule that detects tenants whose score is rising quickly, diffed off these snapshot rows. With the
current rotating sample, a 7-day delta is computable for only about a tenth of the population, and
those are the tenants already visible near the top of the queue — so the rule under-reports the
growth-stage population it exists to find, by construction, until the full-population write below
lands. This is no longer a hypothetical feature request.

## 2. Two changes, both required

Change A alone is not sufficient. Today the scoring lambda writes snapshots for only ~9% of tenants
per day, so even with the new columns the per-tenant series would be too sparse to differentiate.

### Change A — add raw signal columns

```sql
ALTER TABLE latam_tenant_score_snapshots
  ADD COLUMN tenant_avg_qps    DOUBLE       NULL,
  ADD COLUMN qps_source_status VARCHAR(16)  NULL,
  ADD COLUMN cluster_count     INT          NULL,
  ADD COLUMN has_credit_card   TINYINT(1)   NULL;
```

Apply the same statement to `emea_tenant_score_snapshots` and `apac_tenant_score_snapshots`.

**`qps_source_status` is load-bearing — please do not drop it as redundant.** It carries the same
three-state contract already agreed for the global rollup
([`universal/REQUEST_tenant_daily_qps_view.md`](../universal/REQUEST_tenant_daily_qps_view.md) §3.1):

| Value | Meaning | `tenant_avg_qps` |
|---|---|---|
| `ready` | The tenant was measured for this day; the value is real | a number, legitimately including `0.0` |
| `no_clusters` | Tenant had no clusters that day — genuinely zero, correctly measured | `0.0` |
| `unavailable` | Not measured (no monitoring coverage, collection failed, tenant not scanned) | `NULL` |

"We measured this tenant and it did nothing" and "we did not measure this tenant" are opposite
facts. Collapsing them into `0` would make every slope and decay feature read false declines. This
matters more here than in the current-state table, because a time series propagates the error across
every downstream window. **Write `NULL`, never `0`, when the value was not observed.**

Column population will look partial, and that is expected and correct. **It is not a collection
gap.** Re-measured 2026-08-27 counting only tenants that have a cluster to measure: of tenants with
at least one cluster, those with no QPS reading are EMEA 0 of 194, APAC 32 of 538, LATAM 1 of 107.
QPS is NULL because there is nothing running to measure, which is exactly what `qps_source_status`
is for. The familiar 8% / 8.4% / 56% figures are the share of tenants that ever created a cluster —
a funnel fact, not an infrastructure one. **There is no "raise the fill rate" workstream to schedule
here**; an earlier version of this paragraph implied otherwise.

### Change B — write a snapshot row for every tenant, every day

Current EMEA write volume, measured 2026-08-16 → 08-25: **228–261 rows/day against 2,682 tenants**,
i.e. only the subset scanned that day. Each tenant therefore appears in the series intermittently,
with irregular gaps.

Requested: the scoring lambda writes one row per tenant per day for the **full** tenant population.

If full-population write is judged too heavy, the acceptable minimum is: every tenant whose
`tenant_avg_qps IS NOT NULL`, plus every tenant currently tiered P0 or P1. This preserves the
series where features will actually be computed. Please state which of the two was implemented.

**Volume estimate** (2026-08-26 tenant counts): EMEA 2,682 + APAC 7,788 + LATAM 221 ≈ **10,691
rows/day** across all three regions, ~3.9M rows/year. Each row is narrow (one date, one id, four
small numerics, three short strings). If retention becomes a concern, 400 days is more than
sufficient; a rolling drop beyond that is fine and needs no coordination with us.

## 3. Acceptance

1. All three `*_tenant_score_snapshots` tables carry the four new columns, same names and types.
2. `qps_source_status` is populated on every written row.
3. Spot check — a tenant with no monitoring coverage shows `unavailable` + `NULL`, **not**
   `ready` + `0.0`. A tenant with clusters but no traffic shows `ready` + `0.0`.
4. Over 14 consecutive days, daily row count ≈ tenant population (Change B full), or ≈ the stated
   minimum subset, with no unexplained gaps.
5. Pick one tenant with known sustained load: its 14-day series is dense enough to compute a
   coefficient of variation and a 7-day slope.

## 4. Out of scope

- ❌ No backfill. History starts at deploy.
- ❌ No change to scoring weights, thresholds, or tier rules. This request only persists inputs.
- ❌ No new collection path for QPS — this stores whatever the pipeline already reads today.
- ❌ Not a replacement for [pql-infrastructure#18](https://github.com/pingcap-inc/pql-infrastructure/issues/18)
  (daily-average QPS rollup). That remains the correct long-term measure; this is the cheap
  intermediate that stops discarding what we already have.

## 5. Known limitation to record, not to solve here

The stored value inherits the sampling method it comes from: a one-hour Prometheus rate taken at
11:00, i.e. **one point per tenant per day**. Day-level features — trend, dispersion, range,
drop-to-zero — are unaffected. **Intra-day periodicity is not recoverable** at this sampling rate
and must not be claimed. The 2026-07-30 audit additionally flags that the metric may include
non-user traffic (IT confirmation pending), so `QPS > 0` does not yet strictly mean "user active".

---

**Cross-references**

| Where | What |
|---|---|
| [`universal/qps-timeseries-signal-contract.md`](../universal/qps-timeseries-signal-contract.md) | The features this unblocks, and the contract regions must meet |
| [`universal/REQUEST_tenant_daily_qps_view.md`](../universal/REQUEST_tenant_daily_qps_view.md) | The global daily-QPS rollup — `source_status` design inherited from its §3.1 |
| [pql-infrastructure#18](https://github.com/pingcap-inc/pql-infrastructure/issues/18) | Open ticket for that rollup |
| `tidb-lifecycle-ops` → `pql-signal-redesign/00-diagnosis.md` §5.2 | Where the need for this change was first identified |
| `tidb-lifecycle-ops` → `pql-signal-redesign/01-live-data-2026-08-26.md` §5 | Measured snapshot schema and the ~9%/day write-coverage finding behind Change B |