# 01 · Current State & Bot Validation

> As-of date: **2026-06-23**. All counts below are live-DB snapshots and will drift. The acceptance test is **query-logic reproducibility against a frozen snapshot**, not the exact integers.

---

## 1. Two systems, one result DB

| Layer | Where | What it does |
|---|---|---|
| **Strategy / analysis / SQL drafts** | Local `Life-cycle Growth/EMEA` (this workspace) | Model docs, queue definitions, SQL drafts, GTM alignment. **Does not run in prod.** |
| **Execution layer** | GitHub `pingcap-inc/pingcap-support/emea_lambda_tasks` | The EMEA Tenants Monitor Lambdas that actually run. |
| **Shared result DB** | TiDB Cloud `regional_support` | Both read/write here. |

### GitHub Lambda layout
- **`monitor/`** — ingestion + enrichment. Reads EMEA tenants from source, enriches billing / credit card / credits / cluster count / QPS, does domain intelligence, upserts to `emea_active_tenants` and `emea_new_signup`.
- **`scoring/`** — recomputes `total_score`, `priority`, `key_lead` from the latest enriched row in `emea_active_tenants`; writes score snapshots.
- **`summary/`** — reads enriched/scored tables, generates the daily/weekly bot messages.

**This round changes nothing in GitHub directly.** We produce the planning + SQL + handoff; Yujie/dev implement.

---

## 2. Live DB baseline (2026-06-23)

| Table / metric | Value |
|---|---|
| `domain_intelligence` | ~218 rows |
| `emea_active_tenants` | 1,051 tenants |
| `emea_new_signup` | 1,018 tenants |
| `emea_tenant_events` | 2,066 events / 540 tenants / 31 event types |
| `emea_tenant_score_snapshots` | 5,807 snapshots / 1,049 tenants |
| `UpdateSpendingLimit` | 559 events / **473 tenants** (latest 2026-06-23) |
| `has_credit_card=1` | 54 active tenants |
| `track_step=2` (connected) | 23 tenants |
| paid-but-unconnected P0/P1 | 12 tenants |
| `premium_cluster_count>0` | **0 tenants** (forward-looking rule only — nothing to action today) |
| `dedicated_cluster_count>0` | 2 tenants |
| `CreateImport succeeded` | present in `emea_tenant_events` |

> **Note on the event grain:** `UpdateSpendingLimit` is 559 events across 473 tenants → any queue built on it **must dedup to distinct tenant (latest event)**, or it will show the same account multiple times. Same caution for imports.

---

## 3. The `connected` definition (read this before anything else)

`track_step=2` / `connected` is **NOT** a product/database connection. It means an **operational human connection** has been established — usually a Slack invite accepted, or a clear human comms channel exists.

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

**Engineering consequence (the NULL trap):** "unconnected" must be written NULL-safe. In MySQL/TiDB, `track_step <> 2` evaluates to `NULL` (not `TRUE`) when `track_step IS NULL`, so a tenant with no track_step would be **silently dropped** from every queue. Always write:

```sql
COALESCE(track_step, 0) <> 2      -- canonical "unconnected" predicate used in all queues
```

---

## 4. Bot validation

### 2026-06-23 daily bot
| Bot says | Live DB reproduces |
|---|---|
| New signups last day: 0 | header counter only |
| **P0: 3** | `emea_active_tenants`, `DATE(processed_at)=2026-06-23`, `source_type='active_candidate'` → **P0 = 3** ✅ |
| **P1: 26** | same filter → **P1 = 26** ✅ |
| Imports last 7d: 1 tenant / 2 events | `emea_tenant_events`, `CreateImport`/`succeeded` ✅ |

### 2026-06-22 daily bot
| Bot says | Live DB reproduces |
|---|---|
| New signups last day: 8 | header counter only |
| P0: 3 / P1: 25 | active-candidate filter ✅ |
| Imports last 7d: **4 tenants / 5 events** | dates 06-15, 06-16, 06-22 → **4 tenants / 5 events** ✅ |

> The import "last 7 days" window is **anchored on the bot run date** (rolling), not a fixed calendar week — that's the only way 06-15/06-16/06-22 all fall inside one window. Any reimplementation must keep the run-date anchor.

---

## 5. The filter bug: daily active list vs. weekly all-actionable queue

**Inferred current bot logic:**
- Daily P0/P1 list filters `emea_active_tenants` to `source_type='active_candidate'`.
- New signups are **header counters**, not merged into the daily P0/P1 list.
- Import section reads `emea_tenant_events`.

**The problem:** the **weekly all-actionable P0/P1** must **NOT** reuse the daily `source_type='active_candidate'` filter. If it does, it silently **excludes `source_type='new_signup'` P1 rows** — i.e. high-intent new signups never appear in the weekly actionable view.

> This is the **single highest-value fix** in this round. It's concrete, verifiable, and changes what operators see. It should not get buried under the new-queue work. Repro + corrected query in [04-sql-drafts.sql](04-sql-drafts.sql).