# IVT aggregation bound — view, not post-process

**Ticket** TW-314
**Spec ref** §5.3 item 4 — "Convert IVT aggregation into a bounded SQL view or an explicitly documented post-processing step in the catalog entry"
**Spec ref** §5.3 non-goal — "Unbounded click-level IVT exports"
**Decision** Bounded SQL view
**Status** Proposed — needs DBA sign-off on §6 divergences

---

## 1. The choice the spec offers

Either the IVT aggregation becomes a bounded SQL view, **or** it stays a post-processing step
explicitly documented in the catalog entry. Both are permitted. This records which was taken and
why, so the reasoning survives the ticket.

**Chosen: bounded SQL view.**

## 2. What the aggregation looks like today

`AffiliateModel::getAffClearTrustStats` (lines 1540–1972):

1. For each day, build a per-shard `SELECT` over `Shorty.click_YYYYMMDD` — one for inbound status
   buckets, one for fraud buckets keyed on the raw `errors` string (lines 1605–1623).
2. `UNION ALL` the per-day parts and re-aggregate (lines 1651–1658).
3. Same for `amzn_incoming_blacklist_requests_YYYYMMDD` (lines 1629–1647).
4. **In PHP**, run every distinct `errors` string through `parseClickErrorLevelAndReason` — a regex
   splitting `"Level one - Trojan"` into level `1` and reason `"Trojan"` (lines 1984–2015).
5. Roll up into levels 0–5, compute percentages, attach an affiliate breakdown (lines 1865–1964).

Step 4 is the crux. The level/reason split — the thing that makes IVT data legible — exists **only
in PHP**. That is what made this a decision rather than a formality.

## 3. Why the view wins

**It makes the forbidden thing impossible rather than merely disallowed.**

Spec §5.3 forbids unbounded click-level IVT exports. If the view returned raw `errors` strings for
post-processing, it would need rows at or near click grain, and "bounded" would rest on the tool
remembering to aggregate. One future tool that forgets, one `max_rows` raised for a legitimate
reason, and a click-level export is one call away.

With the split in SQL, `Shorty.lucos_ro_ivt_fraud_YYYYMMDD` is grouped by
`(affiliate_name, adv_id, ivt_level, ivt_reason)`. **There is no click-grain row to select.** The
bound is a property of the granted object, not of the code reading it.

**Secondary reasons.**

- *The RO user is granted SELECT on the approved `lucos_ro_*` views only.* Whatever the view does not compute cannot be
  recovered by a second query.
- *Auditability.* A DBA reviewing the DDL sees the whole bound in one place, in the language they
  review in.
- *Cost.* Aggregation happens next to the data. A 31-day window transfers thousands of rows instead
  of tens of millions.
- *Consistency.* Every reader gets the same level/reason semantics. A regex living in one PHP class
  is guaranteed to be reimplemented slightly differently the second time.

## 4. Why the post-process alternative was rejected

Keeping the split in TypeScript would preserve the PHP semantics exactly, including en-dash and
em-dash handling the portable SQL cannot do (§6 D1). That is a real advantage and the reason this
was a decision rather than an obvious call.

It loses on the point that matters: the bound would live in application code, one refactor from
being lost, guarding a rule the spec states as a hard non-goal. Semantic fidelity on an edge case is
worth less than a structural guarantee on the core constraint.

If [OD-3](CAKE-OPEN-DECISIONS.md) confirms MySQL 8 everywhere, the `REGEXP_REPLACE` variant closes
the fidelity gap and the trade-off disappears.

## 5. What is still post-processed, and why that is fine

The **aggregation** is bounded in SQL. Three things remain in `connectors/cake/postback-ivt.ts`,
none of which read data:

| Step | Why not in SQL |
| --- | --- |
| Percentage arithmetic (`fraud / inbound`) | Pure arithmetic over already-aggregated numbers. Nothing to bound |
| Level 0–5 scaffold, including empty levels | Presentation. The PHP does this too (line 1915) |
| Affiliate-id ↔ name reconciliation between `click_*` and `amzn_*` | Cross-instance ([OD-1](CAKE-OPEN-DECISIONS.md)) |

Declared in `catalogs/queries/get_ivt_report.json` under `post_processing` — which is what §5.3
item 4 asks for in the alternative branch, so the catalog entry is explicit either way.

## 6. Deliberate divergences from the PHP

Each is a place where the SQL does not reproduce `parseClickErrorLevelAndReason` exactly. All are
believed improvements or immaterial, and all need a DBA eye on real `errors` values.

**D1 — Non-ASCII dash separators.** The PHP regex accepts `-`, `–` (en), `—` (em). The portable SQL
strips only a leading ASCII `-`, so `"Level one – Trojan"` yields reason `"– Trojan"` here versus
`"Trojan"` in PHP. Level is unaffected. Resolved by the MySQL 8 variant.
**Action:** `SELECT DISTINCT errors FROM click_… WHERE errors REGEXP '[–—]'` to confirm whether these
occur at all.

**D2 — `"Level one"` with no reason.** PHP's `(.+)$` requires a character after the level token, so a
bare `"Level one"` fails the regex and falls to `[0, "Level one"]`. The SQL assigns level `1` with
reason `"Level one"`. The SQL reading is arguably more correct; the divergence is recorded rather
than silently taken.

**D3 — Both denominators exposed.** `$denominatorPolicy` (line 1831) is a hardcoded local set to
`'status_bucket_total'`, with an unreachable `else`. `lucos_ro_ivt_inbound_*` publishes both
`valid + dropped + fraud` **and** `COUNT(1)`; they differ whenever a row has a status outside
`{0,1}`. The tool defaults to the PHP behaviour and names its choice in the response
(`denominator_policy`), so a percentage is never unattributable.

**D4 — `adv_id` retained in the grain.** The PHP applies the advertiser filter inside each per-shard
`SELECT`. The view groups by `adv_id` instead, so one view serves both filtered and unfiltered
queries. Sums are identical; row count per shard is higher.

**D5 — Amazon special cases stay in config.** Advertiser `20378` → country `us`, `21610` → `gb`
(lines 1582–1588). Hardcoded ids belong in config, not a view — a new special case should be a
reviewed config diff, not a DDL change. They live in
`catalogs/queries/get_ivt_report.json` under `amzn_advertiser_country_map`.

## 7. What a reviewer should check

- [ ] `SHOW COLUMNS FROM Shorty.lucos_ro_ivt_fraud_YYYYMMDD` exposes no click-grain column
      (no `id`, `click_ip`, `timedate`, `rpc`)
- [ ] `SELECT COUNT(*)` on the view is in the hundreds, not millions
- [ ] Spot-check `ivt_level` / `ivt_reason` against the cake UI for the same day and publisher
- [ ] D1 — run the `REGEXP '[–—]'` probe and confirm the result is empty
- [ ] Total fraud clicks from the view match `getAffClearTrustStats` for the same window