# cake staging validation — what the live database actually said **Date:** 2026-08-13 · **Tickets:** [TW-314](https://admedia-jira.atlassian.net/browse/TW-314)–[TW-317](https://admedia-jira.atlassian.net/browse/TW-317) **Access used:** phpMyAdmin (`phpmyadmin.admedia.com`) against Whale Dev and OCI Dev Server Until this date every cake design decision rested on the schema harvest, which reads `INFORMATION_SCHEMA` only. All thirteen views have now been created on the real staging databases and read back. This file records what that changed, because several findings **contradict** what other docs in this folder still assert. --- ## 1. All thirteen views exist and return rows | Schema | Views | Verified | | --- | --- | --- | | `whale` | 2 | ✅ rows returned | | `Admin` | 7 | ✅ rows returned | | `Shorty` | 4 (for `20260812`, Amazon `20260803`) | ✅ 3 with data; Amazon table is empty on dev | Two results worth keeping: **The IVT denominators agree.** On `lucos_ro_ivt_inbound_20260812`, `valid + dropped + fraud` equals `inbound_clicks` in every row (2+541+53=596, 1+770+2=773, 0+2866+17=2883). The two competing denominators — one live in the cake UI, one in an unreachable `else` branch — coincide on this day, which means no row carried a status outside `{0,1}`. Publishing both was right, and now we know their real relationship instead of guessing. **The IVT level/reason port is correct.** This was the riskiest SQL in the set: a PHP regex reimplemented as `CASE`/`LIKE`/`SUBSTRING`. Real output: ``` 0 Blacklist IP 3 High Risk Score - Automation Tool - Spoof - Bot Behaviour 4 AI Threats Detection - Brightdata data-center IP ranges ``` Levels extracted as integers, and no leftover `Level four -` prefix on the reason. A broken port would show either the prefix or an empty reason. **Postback redaction works structurally.** `postback_url_base` = `https://tracker.trustdeals.com/postback` (39 chars) while `postback_url_length` = 130 and `query_param_count` = 5. Ninety-one characters of query string — the click ids and signing tokens — never left the database. Both IP branches were exercised: `82.13.240.0/24` and `2a02:c7c:aebd::/48`. --- ## 2. Corrections to other docs ### OD-3 was half wrong — the servers are on different versions | Server | Instance | Version | `sql_mode` | `event_scheduler` | | --- | --- | --- | --- | --- | | Whale Dev | `whale` | **Percona 8.0.41** | `ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,…` | ON | | OCI Dev | `Admin` | **5.7.44** | *(empty)* | OFF | | OCI Dev | `Shorty` | **5.7.44** | *(empty)* | OFF | DevOps answered "MySQL 5.x", which is true of two servers out of three. Consequences: - **The portable SQL was the right call and must stay.** `Shorty` is 5.7, so the MySQL 8 `REGEXP_REPLACE` form in [CAKE-RO-VIEWS.md](./CAKE-RO-VIEWS.md) is still unusable, even though whale could run it. - **`max_execution_time` exists on both versions** (5.7.8+), so the first branch of the fallback chain in `connectors/cake/executor.ts` succeeds everywhere. The "what if it is 5.6" scenario is closed. - **`ONLY_FULL_GROUP_BY` is on where we do not aggregate, and off where we do.** Every aggregating view is on Admin or Shorty (empty `sql_mode`); the two whale views are plain column selects. This is luck rather than design — worth re-checking before prod, where `sql_mode` may differ. ### `event_scheduler` is OFF on the one server that needs it The nightly shard-view job runs on **Shorty**, where the scheduler is off. It is on for whale, which has no job. So the ready-made `CREATE EVENT` block cannot be used as-is — either someone enables the scheduler on the OCI Dev Server, or the job runs from an external cron. ### OD-1: dev topology differs from prod `Admin` and `Shorty` are **the same server** on dev (both `server=5`, "OCI Dev Server"); `whale` is separate. Production has all three apart (`.7`, `.2`, `.10`). **Do not let this leak into the design.** A cross-schema join between Admin and Shorty would pass every test on dev and fail in production. The connector keeps the merge in application code for exactly this reason, and it must stay that way. ### OD-4: `click_` is now measured, and it rules out fixed-name rolling views | Table | Rows | Size | | --- | --- | --- | | `click_20260812` | 245,966 | 563 MB | | `click_20260811` | 220,270 | 490 MB | | `click_20260810` | 260,877 | 545 MB | ≈250k rows and ≈500 MB **per day**, on dev. The "fixed view names, rolling content" option would make every IVT query read the whole window, so a one-day question would scan ~7.7M rows / ~15 GB instead of one day. Measured cost of aggregating a single day: **0.35 s**. Thirty-one days is therefore ~11 s for a question that should take a third of a second. **Recommendation stands at option B** — a dedicated schema for the sharded views, granted once. --- ## 3. One real bug, found only by running it ``` ERROR 1271 (HY000): Illegal mix of collations for operation 'case' ``` `publisher_postback_*.ip` is `varchar(255) latin1_swedish_ci`, but `INET_NTOA()` returns the connection charset. Two branches of the `ip_prefix` `CASE` were latin1 and one was utf8, so MySQL refused the whole view. Fixed by wrapping every branch in `CONVERT(… USING utf8)`, in both the readable template and the string the nightly procedure builds. **The harvest could not have caught this.** It records column types, not collations. Had the file gone to the DBA untested, it would have failed on their first run and looked like broken code rather than one charset mismatch. --- ## 4. The database contains things that are not clean text Two findings from reading real rows, neither visible in a schema catalog. ### HTML entities in display strings `Admin.advertiser_categories.name` stores `Career & Employment`. cake's PHP renders that into a web page where it displays correctly. A tool answering a model is not a web page — left alone, ChatGPT receives the escaped form and repeats it verbatim. Handled by `decodeEntities` in `connectors/cake/shared.ts`, applied to the display-name fields (`adv_name`, `affiliate_name`, `campaign_name`, `category`). Single-pass by design, and unrecognised entities are left exactly as stored. ### Penetration-test payloads stored as affiliate names `Admin.AffiliateIDs.Affiliate` contains, among real publisher names: ``` 107039 !(()&&!|*|*| 107047 ";print(md5(acunetix_wvs_security_test));$a=" 107037 $(nslookup 6jpWmovN) ``` Someone ran Acunetix against a cake signup form and the probe strings were saved as records. **Why this matters here.** `affiliate_name` is a field our tools return, so those strings travel into the model's context. Today they are harmless noise, but the same column would carry `Ignore your previous instructions and …` just as readily. This is evidence — not speculation — that the field holds unsanitised attacker-controlled input. **Deliberately not stripped.** `decodeEntities` normalises escaping; it does not launder content. Silently rewriting stored values would make the tool misreport what the database contains, which is worse for an evidence tool than surfacing ugly data. The mitigation belongs at the boundary that presents tool output to the model, and it applies to every connector, not just cake. --- ## 5. What is still not proven | Gap | Why | | --- | --- | | `get_ivt_report` Amazon path | All eight `amzn_incoming_blacklist_requests_*` tables are empty on dev (`COUNT(*)` = 0) | | Cross-instance advertiser merge | whale rows sampled at 2024, Admin `adv_cmp_performance` at 2022 — the two sides of the merge may not overlap on dev. See [OD-8](./CAKE-OPEN-DECISIONS.md) on synthetic fixtures | | End-to-end through the MCP server | Blocked on configuration, see below | --- ## 5a. The nightly job is installed and running on staging Shorty | Object | State | | --- | --- | | `sp_lucos_ro_create_cake_shard_views(p_date)` | installed | | `sp_lucos_ro_prune_cake_shard_views(p_keep_days)` | installed | | `ev_lucos_ro_refresh_cake_shard_views` | **ENABLED**, every 1 day, starts 2026-08-14 00:15 | | `event_scheduler` | switched ON (was OFF) | | Shard views | 28, backfilled 2026-07-31 → 2026-08-13 | **The procedure was proven, not assumed.** Called for `2026-08-11` — a day no view had been made for by hand — it created `lucos_ro_ivt_fraud_20260811` and `lucos_ro_ivt_inbound_20260811` and correctly created *neither* `lucos_ro_postback_20260811` nor `lucos_ro_ivt_amzn_20260811`, because those base tables do not exist for that day. Skipping a missing day rather than erroring is the designed behaviour, now demonstrated. The backfill shows the same thing at scale: 28 views across 14 days, with nothing at all for 2026-08-06 → 09 where the `click_` tables are absent. That gap is what the connector reports as `shards_missing`, so the staging data now exercises the partial-answer path instead of a suspiciously clean range. **Two caveats for whoever owns the server.** `SET GLOBAL event_scheduler = ON` changes the *running* server only. Unless `event_scheduler=ON` is added to `my.cnf`, the nightly job stops silently at the next MySQL restart — exactly the quiet failure the `shards_missing` reporting exists to make visible, but better prevented. The job creates views for **today and tomorrow**. That is a hedge: we still do not know what time a new day's `click_` table appears. If it is created lazily on first click rather than at midnight, a 00:15 run would find nothing, and without the second call that day would be skipped permanently. --- ## 6. The current blocker is one line of configuration `dev-mcp.lucos.com` is deployed and healthy, and exposes **all four cake tools** — confirmed via `tools/list`. Calling one returns: ``` Tool 'get_affiliate_report' failed: Missing CAKE_ADMIN_RO_DSN_STAGING. ``` So the code path, the registry entry and the credential guard all work; the deployed process simply has no DSNs. It needs `CAKE_ADMIN_RO_DSN_STAGING`, `CAKE_WHALE_RO_DSN_STAGING` and `CAKE_SHORTY_RO_DSN_STAGING`, then a reload. **And the read-only login still does not exist.** Neither dev account carries `WITH GRANT OPTION` (`dev@192.168.%` has `ALL PRIVILEGES ON *.*` but cannot grant; `poongkabilan@192.168.30.106` likewise). So the views could be created but `lucos_cake_ro` could not be. Creating it needs someone with grant rights — see [OD-9](./CAKE-OPEN-DECISIONS.md).