# cake (audit.ad) RO views — TW-314

**Audience:** DBA, Security, Platform
**Ticket:** [TW-314](https://admedia-jira.atlassian.net/browse/TW-314) · consumers [TW-316](https://admedia-jira.atlassian.net/browse/TW-316) / [TW-317](https://admedia-jira.atlassian.net/browse/TW-317)
**Companion:** [MNGT-RO-VIEWS.md](./MNGT-RO-VIEWS.md) (TW-309) — same pattern, different source system

Engineering design pack for the DBA: **static read-only views plus four day-sharded view
families**, so `business-mcp.lucos.com` can implement the cake reporting tools without
base-table `SELECT`.

| Item | Value |
| --- | --- |
| View naming | `<schema>.lucos_ro_*` (matching TW-309) |
| Schemas | `whale`, `Admin`, `Shorty` — possibly three separate instances, see [OD-1](./CAKE-OPEN-DECISIONS.md) |
| RO grants | `SELECT` on these **views only** — not base tables / full schema |
| Staging first | Create + smoke in staging; prod later |
| No PHP auth changes | Connectors talk to DB views only |
| DDL | [`catalogs/queries/cake-ro-views-staging.sql`](../catalogs/queries/cake-ro-views-staging.sql) · [`cake-ro-shard-views-staging.sql`](../catalogs/queries/cake-ro-shard-views-staging.sql) |
| Registry | [`catalogs/views/cake-ro-views.json`](../catalogs/views/cake-ro-views.json) |

---

## Why cake differs from MNGT

Three structural differences, all of which drive design decisions elsewhere in this repo:

1. **cake spans three schemas**, and cake's own PHP merges results in application code because they
   are different connections. A view cannot join across instances, so the advertiser report's merge
   stays in the connector.
2. **Some cake tables are day-sharded** — `publisher_postback_20260811`, `click_20260811`. A view has
   a fixed `FROM`, so a view per day is created nightly and the connector unions a bounded window.
3. **Every cake tool is date-ranged.** MNGT tools are single-entity lookups; cake needs window caps.

---

## View inventory

### Static (created once)

| View | Schema | Tool | Derived from |
| --- | --- | --- | --- |
| `lucos_ro_advertiser_stats_daily` | whale | `get_advertiser_report`, `list_advertisers_high_revenue_concentration`, `list_affiliates_on_multiple_advertisers`, `list_publishers_by_active_campaigns`, `list_campaigns_without_traffic` | `advertiser_stats_report` |
| `lucos_ro_publisher_earnings_daily` | whale | `get_advertiser_report`, `list_affiliates_on_multiple_advertisers`, `list_publishers_by_active_campaigns` | `advertisers_for_aff_stats_report` |
| `lucos_ro_affiliate_stats_daily` | whale | `get_advertiser_report` AM/BD (`group_by=account_manager` / `account_sales_manager`) | `affiliate_stats_report` |
| `lucos_ro_adv_campaign_performance_daily` | Admin | `get_advertiser_report`, `list_advertisers_high_revenue_concentration` | `adv_cmp_performance` |
| `lucos_ro_advertiser_goals` | Admin | `get_advertiser_report` | `adops_adv_headers` |
| `lucos_ro_advertiser_categories` | Admin | `get_advertiser_report` | `advertiser_categories(_mapping)` |
| `lucos_ro_affiliate_cpc_summary_daily` | Admin\* | `get_affiliate_report`, `list_publishers_traffic_to_inactive_campaigns`, `list_advertisers_low_traffic_concentration`, `get_campaign_affiliate_report` | `aff_cpc_summary` ⋈ dims |
| `lucos_ro_affiliate_directory` | Admin | `get_ivt_report` | `AffiliateIDs` ⋈ `Affiliate` |
| `lucos_ro_affiliate_offers` | Admin | `list_publisher_offers` | `AffiliateIDs` ⋈ `affiliate_offers_mappings` ⋈ `publisher_advertiser_offers_from_accounts` |
| `lucos_ro_campaign_directory` | Admin | `get_postback_report`, `list_publishers_by_active_campaigns` (needs `status`), `list_publishers_traffic_to_inactive_campaigns`, `list_campaigns_without_traffic`, `list_advertisers_low_traffic_concentration`, `list_advertisers_high_revenue_concentration`, `get_campaign_affiliate_report` | `campaign` ⋈ `Advertiser_info` |
| `lucos_ro_hidden_channel_publishers` | Admin | `get_postback_report` | channel maps (exclusion list) |

\* instance depends on [OD-2](./CAKE-OPEN-DECISIONS.md).

### Day-sharded (created nightly)

| View family | Schema | Base table | Optional |
| --- | --- | --- | --- |
| `lucos_ro_postback_YYYYMMDD` | Shorty | `publisher_postback_YYYYMMDD` | no |
| `lucos_ro_ivt_inbound_YYYYMMDD` | Shorty | `click_YYYYMMDD` | no |
| `lucos_ro_ivt_fraud_YYYYMMDD` | Shorty | `click_YYYYMMDD` | no |
| `lucos_ro_ivt_amzn_YYYYMMDD` | Shorty | `amzn_incoming_blacklist_requests_YYYYMMDD` | **yes** — only exists on days Amazon traffic was blacklisted |

`lucos_ro_postback_*` is the **only** view in the set at row grain, which is why its tool carries the
tightest window (7 days) and row cap (2 000).

---

## Columns that are easy to misread

The views rename or annotate each of these; this table is the record of why.

| View.column | Base | Meaning |
| --- | --- | --- |
| `…stats_daily.estimated_conversions` | `conversions` | **Platform-estimated**, not the advertiser's own count — that is `advertiser_conversions`, a different table on a different instance |
| `…advertiser_goals.goal_type_flag` | `adops_adv_headers.cpa` | A **type flag**, not a CPA value. `1` = CPA goal, else ROAS |
| `…performance_daily.conversion_value` | `conversions × aov` | **Unrounded on purpose** — the PHP rounds once after summing |
| `…ivt_inbound_*.inbound_clicks` | `COUNT(1)` | The *unused* denominator; `valid + dropped + fraud` is what the UI uses |
| `…ivt_fraud_*.affiliate_name` | `click_*.affiliate` | A **name** |
| `…ivt_amzn_*.affiliate_id` | `amzn_*.affiliate` | An **id**. Same base column name as above, different type |
| `…postback_*.ip_prefix` | `ip` | Truncated to /24 or /48 |
| `…postback_*.postback_url_base` | `postback_url` | Query string severed at the `?` |
| `lucos_ro_campaign_directory.status` | `campaign.status` | Current lifecycle char (TW-333 additive). MNGT UI labels `A` as Active; same field as `lucos_ro_campaign_budget.status`. Full map unconfirmed. Existing postback SELECTs do not return it. |
| `lucos_ro_affiliate_offers.mapping_status` | `affiliate_offers_mappings.status` | Assignment: `0` pending, `1` approved, `2` disapproved, `3` inactive. Not campaign directory `A`. |
| `lucos_ro_affiliate_offers.offer_status` | `publisher_advertiser_offers_from_accounts.status` | Catalog lifecycle string (tool default `live`). |

## Type mismatches the views normalise

The 2026-08-12 harvests show the same logical id stored differently on each instance:

| Column | Type | Note |
| --- | --- | --- |
| `whale.advertiser_stats_report.affiliate_id` | `int` | |
| `Admin.adv_cmp_performance.aff_id` | **`varchar(255)`** | same id, different type |
| `Shorty.publisher_postback_*.publisher_id` | **`varchar(255)`** | numeric ids held as text |
| `Shorty.publisher_postback_*.campaign_id` | **`varchar(255)`** | |

Two consequences, both handled:

**1. The advertiser-report merge.** `CAST(acp.aff_id AS SIGNED)` in
`lucos_ro_adv_campaign_performance_daily` means both sides of the cross-instance merge arrive as
integers, so the join key matches.

This is a **deliberate divergence from the PHP.** `getAdvertiserStatsOptimized` builds its map key by
concatenating raw values — `$row['advertiser_id'] . '_' . $row['aff_id']` — against a whale side that
is already an int. If `aff_id` ever holds `"01001"`, the PHP produces `19880_01001` and looks it up
as `19880_1001`, silently finding nothing and reporting zero conversions. The CAST removes that
class of bug. Same numbers in the normal case, correct numbers in the edge case.

**2. Postback filters bind strings.** `publisher_id` and `campaign_id` are varchar, so
`connectors/cake/postback-ivt.ts` binds `String(...)`. Binding a number would make MySQL coerce the
column rather than the literal, dropping the index and turning a point lookup into a full scan of a
row-grain sharded table. Hidden-publisher ids come from Admin as real ints and are converted before
crossing to Shorty.

## Fields never exposed by any view

| Field | Source | Why |
| --- | --- | --- |
| `rev_share` | `Affiliate_info` | Commercially sensitive. Consumed inside the earnings view, never selected |
| `aov` | `adv_cmp_performance` | Advertiser-confidential. Only the product survives |
| `postback_url` (raw) | `publisher_postback_*` | Carries tokens and secrets |
| `ip` (full) | `publisher_postback_*` | Personal data |
| `click_ip`, `timedate`, `rpc`, `id` | `click_*` | Click grain — exposing any would make an unbounded IVT export reachable |
| `offer_custom_link` | `affiliate_offers_mappings` | Publisher custom URLs |
| `is_emailed` | `affiliate_offers_mappings` | PII |
| `terms`, `description`, traffic-source blobs | `publisher_advertiser_offers_from_accounts` | Unbounded text not needed to list assigned offers |

---

## RO grants (acceptance shape)

```sql
-- names illustrative — DBA chooses final username
GRANT SELECT ON whale.lucos_ro_advertiser_stats_daily         TO 'lucos_cake_ro'@'%';
GRANT SELECT ON whale.lucos_ro_publisher_earnings_daily       TO 'lucos_cake_ro'@'%';
GRANT SELECT ON Admin.lucos_ro_adv_campaign_performance_daily TO 'lucos_cake_ro'@'%';
GRANT SELECT ON Admin.lucos_ro_advertiser_goals               TO 'lucos_cake_ro'@'%';
GRANT SELECT ON Admin.lucos_ro_advertiser_categories          TO 'lucos_cake_ro'@'%';
GRANT SELECT ON Admin.lucos_ro_affiliate_cpc_summary_daily    TO 'lucos_cake_ro'@'%';
GRANT SELECT ON Admin.lucos_ro_affiliate_directory            TO 'lucos_cake_ro'@'%';
GRANT SELECT ON Admin.lucos_ro_campaign_directory             TO 'lucos_cake_ro'@'%';
GRANT SELECT ON Admin.lucos_ro_hidden_channel_publishers      TO 'lucos_cake_ro'@'%';
GRANT SELECT ON Admin.lucos_ro_affiliate_offers               TO 'lucos_cake_ro'@'%';
-- Explicitly NO grants on advertiser_stats_report, adv_cmp_performance,
-- aff_cpc_summary, click_*, publisher_postback_*, Affiliate_info, campaign,
-- affiliate_offers_mappings, publisher_advertiser_offers_from_accounts, …
```

### Grants for sharded views — OPEN QUESTION FOR THE DBA

A new view appears **every night**, and MySQL `GRANT` does not accept wildcards in the table part of
an object name. So unlike the static views, the sharded families cannot be covered by a fixed list.

| Option | Trade-off |
| --- | --- |
| **A.** Nightly job issues the `GRANT` alongside each `CREATE VIEW` | Simple, but the job account then needs `GRANT OPTION` — a meaningfully larger privilege |
| **B.** Put only the sharded views in a dedicated schema (e.g. `lucos_ro_cake`) and grant `SELECT` on that schema once | One static grant, no elevated job privileges. Costs a small deviation from the `<schema>.lucos_ro_*` convention |
| **C.** `GRANT SELECT ON Shorty.*` | **Rejected** — exposes every base table, defeating the entire design |

**Recommendation: B — and the AdCenter connector is the precedent.**

TW-309 offers no guidance here because MNGT has only six static views: created once, granted once.
But **TW-302 solved the same shape of problem**, because `campaigns/{id}` is an unbounded set of
paths that cannot be enumerated either. `connectors/adcenter/routes.ts` handles it with a *pattern*
allowlist rather than a list:

```ts
const allowed = [
  /^campaigns\/[A-Za-z0-9_-]+$/,
  /^creatives\/[A-Za-z0-9_-]+$/,
  /^reports\/summary\/(daily|monthly|countries|os|browsers|sources|domains|keywords)$/,
];
```

…checked twice: once by the path builders on the way in, and again by
`assertAllowlistedAdCenterPath` immediately before the fetch — "a second hard gate".

The important part is how AdCenter splits authorization from restriction:

| Layer | AdCenter | cake under option B |
| --- | --- | --- |
| Credential | one level-1 key **per advertiser** — coarse, stable, knows nothing about routes | one `SELECT` grant on the shard schema — coarse, stable |
| Restriction | regex allowlist in code | `assertObjectsAllowed` + the `lucos_ro_postback_*` wildcard, plus the `^[0-9]{8}$` suffix assertion |

So option B is not a deviation — it is the same coarse-credential / fine-code-allowlist split the
AdCenter connector already uses, and cake's application layer is already built that way.

Option **A** would make cake the only component in the system whose credential changes nightly, and
it requires `GRANT OPTION` on the job account — a larger privilege than anything else here holds.
It remains acceptable if the DBA prefers everything to stay inside `Shorty`.

Worth asking DevOps alongside this: **what privileges does the existing `aireadonly` user hold?**
It is used by the TW-297 harvest across all five databases, so a broadly-scoped read-only user may
already be the accepted norm — in which case B is uncontroversial.

---

## MySQL version — 5.x confirmed

DevOps confirmed **MySQL 5.x**, so the shipped portable `CASE` / `LIKE` / `SUBSTRING` parse is the
correct one and the MySQL 8 variant below is **not applicable**. Kept for reference only, in case an
instance is upgraded later.

Two consequences of 5.x worth checking at bring-up:

- **Statement timeout.** `max_execution_time` needs 5.7.8+; MariaDB uses `max_statement_time` in
  seconds; ≤ 5.6 has neither. The connector tries both and reports `timeoutSetting: 'none'` if
  unavailable. Confirm the exact version — see [OD-3](./CAKE-OPEN-DECISIONS.md).
- **`ONLY_FULL_GROUP_BY`** is on by default from 5.7. Every aggregated view here groups by all its
  non-aggregate columns, so it should pass, but it is worth confirming on first apply rather than
  assuming.
- **`group_concat_max_len`** defaults to 1024 bytes. `lucos_ro_advertiser_categories` uses
  `GROUP_CONCAT`; an advertiser with an unusually long category list would be **silently truncated**.
  Low risk, but silent truncation in an evidence tool is worth a glance at the largest real value.

### MySQL 8 alternative (not applicable today)

Shorter, and it also handles en-dash / em-dash separators which the portable form does not
([D1](./CAKE-IVT-AGGREGATION-BOUND.md)):

```sql
TRIM(REGEXP_REPLACE(
  c.errors,
  '(?i)^Level[[:space:]]*(zero|one|two|three|four|five|[0-9]+)[[:space:]]*([-–—][[:space:]]*)?',
  '')) AS ivt_reason
```

## Fallback if `aff_cpc_summary` is on Shorty

cake's own comment at `AffiliateReportModel.php:724` says *"may be in Shorty or Admin DB"*, and the
PHP probes both at runtime. A view cannot. If the DBA confirms Shorty, the earnings view cannot join
the Admin dimensions, and `get_affiliate_report` must compute earnings in the connector instead —
with `rev_share` staying on the deny list so it never reaches a response. See
[OD-2](./CAKE-OPEN-DECISIONS.md).

---

## Acceptance

- [ ] All static views create cleanly on staging; `SHOW CREATE VIEW` matches the reviewed DDL
- [ ] `sp_lucos_ro_create_cake_shard_views` is idempotent and skips missing base tables
- [ ] `sp_lucos_ro_prune_cake_shard_views(45)` drops only views older than 45 days
- [ ] Staging views queryable as the RO user
- [ ] RO user cannot write, and cannot read non-granted tables (expect `ERROR 1142`)
- [ ] `SHOW COLUMNS FROM Shorty.lucos_ro_postback_YYYYMMDD` shows no raw `postback_url` and no full `ip`
- [ ] `SHOW COLUMNS FROM Shorty.lucos_ro_ivt_fraud_YYYYMMDD` shows no click-grain column
- [ ] PII columns absent from all view definitions
- [ ] Totals reconcile against `getAdvertiserStatsOptimized` and `getAffClearTrustStats` for one known window
- [ ] Sharded-grant approach chosen (A / B above) and applied