# Open decisions — P5 cake reporting (TW-289) Everything below is **proposed** and implemented as the default, but needs a DBA / team-lead call before TW-314 is applied to a real database. Each entry records the alternative so a different choice does not require re-deriving the analysis. --- ## OD-10 — Staging `whale` pointed at the wrong database ✅ RESOLVED **Raised 2026-08-12**, when the first whale harvest returned 13 tables that were all Lucos product tables (`lucos_users`, `lucos_subscriptions`, `lucos_chat_*`, `sessions`) rather than the cake reporting warehouse — so `get_advertiser_report` could not have run in staging. **Resolved the same day.** The DSN was corrected and whale re-harvested at 12:19: **731 tables, 9 400 columns** (from 13 / 120). Both required tables are present with every column the views bind: | Table | Columns checked | Result | | --- | --- | --- | | `advertiser_stats_report` | 13 | ✅ all present | | `advertisers_for_aff_stats_report` | 5 | ✅ all present | No code or view changes were needed — the definitions already matched the real tables. **Worth keeping.** This is the case for harvesting before wiring anything up: a wrong DSN looks exactly like broken code at bring-up time, and would have cost a day of debugging the wrong layer. --- ## OD-1 — Are `Admin`, `whale`, and `Shorty` on the same MySQL instance? ✅ RESOLVED **Answer: separate instances.** `jobs/schema_harvest/allowlist.json` records a distinct `prod_host_note` per target — Admin `192.168.30.7`, Shorty `192.168.30.2`, whale `192.168.30.10`, adcenter `.4`, keywords `.13` — confirmed from real RO access under TW-297. That matches cake's four connection groups in `html/app/Config/Database.php` and explains why `AffiliateReportModel::getAdvertiserStatsOptimized` merges whale and Admin in PHP rather than joining. **Consequence (implemented).** Views never cross an instance boundary, and the advertiser-report merge stays in the connector as a documented, bounded post-process. Three RO connections: `cake_admin_ro`, `cake_whale_ro`, `cake_shorty_ro`. **Still needed from DBA.** Staging hostnames — the notes above are prod only. **⚠️ Dev differs from prod (2026-08-13).** On dev, `Admin` and `Shorty` are the *same* server; only whale is separate. A cross-schema join would pass on dev and fail in prod, so the merge must stay in application code. See [CAKE-STAGING-VALIDATION.md](./CAKE-STAGING-VALIDATION.md) § 2. --- ## OD-2 — Which instance holds `aff_cpc_summary`? ✅ RESOLVED **Answer: `Admin`.** `catalogs/schemas/staging/admin.json` (live staging harvest, 2026-08-10) lists `Admin.aff_cpc_summary` as a `BASE TABLE`, and every column the earnings view needs is present: `date`, `adv_id`, `affiliate_id`, `camp_id`, `cpc`, `net_cpc`, `status`, `click_table_id`. **Consequence.** The primary `Admin.lucos_ro_affiliate_cpc_summary_daily` view is correct as written, `rev_share` is consumed inside the database, and **the commented fallback view is not needed** — no code change. This was the highest-risk open question; it is closed. The question existed because `getAdvertiserAffiliateClickCounts`, `getCampaignsForAdvertiserAffiliate`, and `getAdvertiserClickDetails` all probe `shortyDB` **then** `db` (Admin) and use whichever answers first, and the comment at `AffiliateReportModel.php:724` says *"aff_cpc_summary may be in Shorty or Admin DB"*. The harvest settles it for staging Admin. ### ⚠️ New question raised by the harvest The Admin schema also contains `aff_cpc_summary_04`, `_05`, `_13_jun`, `_14_jun`, `_feb12`, and `_new`. These look like manual backups or an abandoned migration — but **`aff_cpc_summary_new` is worth asking about explicitly**: if a migration is in flight, the live table may change under us. Confirm with the DBA that plain `aff_cpc_summary` is still the authoritative table. --- ## OD-3 — MySQL version ✅ RESOLVED — measured directly, and the servers differ **Superseded by [CAKE-STAGING-VALIDATION.md](./CAKE-STAGING-VALIDATION.md) § 2 (2026-08-13).** Read live from each server: whale is **Percona 8.0.41**, Admin and Shorty are **5.7.44**. DevOps' "5.x" was true of two servers out of three. `max_execution_time` exists on both versions, so the fallback chain's first branch succeeds everywhere and the 5.6 scenario below is closed. The portable parse must stay, because Shorty — where the IVT views live — is 5.7. The original analysis is kept below because it is why the fallback chain exists. **Answer from DevOps: MySQL 5.x.** So the **portable** parse already shipped is the correct one and the MySQL 8 `REGEXP_REPLACE` variant does not apply. No code change. The known limitation stands: en-dash / em-dash separators fall through to the raw-string path ([D1](CAKE-IVT-AGGREGATION-BOUND.md)) — run the `REGEXP '[–—]'` probe to confirm it never happens. ### ⚠️ Follow-up: exactly which 5.x? "5.x" is not precise enough for the statement timeout, because the mechanism changed mid-series: | Server | Variable | Unit | | --- | --- | --- | | MySQL 5.7.8+ | `max_execution_time` | milliseconds | | MariaDB | `max_statement_time` | **seconds** | | MySQL ≤ 5.6 | neither exists | — | Setting an unsupported session variable is a hard error, so an unguarded `SET SESSION` at connect time would have made **every query fail** on 5.6. `connectors/cake/executor.ts` now tries `max_execution_time`, falls back to `max_statement_time`, and if neither works logs a warning and reports `timeoutSetting: 'none'` — so an unbounded connection is visible rather than assumed. **Ask DevOps:** `SELECT VERSION();` — and whether it is MySQL or MariaDB. If the answer is ≤ 5.6, queries are bounded only by `LIMIT` and row caps, and we should decide whether that is acceptable for prod or whether a different guard is needed. **Also affects TW-310.** `connectors/sql/executor.ts` on master sets no statement timeout at all. Worth raising with the MNGT owner during connector reconciliation. --- ## OD-4 — Shard strategy **Chosen (implemented).** Per-day views created by a nightly job, expanded by the executor into a bounded `UNION ALL`. Rationale and the two rejected alternatives: [shard rules §4](CAKE-SHARD-AND-DATE-RANGE-RULES.md). **Still needs.** Agreement to run the nightly view-creation job, and the retention floor (OD-6). **⚠️ `event_scheduler` is OFF on Shorty** (measured 2026-08-13), which is the one server the job runs on. It is ON for whale, which has no job. So the ready-made `CREATE EVENT` block cannot be used as-is — either enable the scheduler on the OCI Dev Server, or drive the two `CALL`s from an external cron. **Sharded grants — recommendation now evidence-backed.** `click_*` measures ≈250k rows / ≈500 MB per day, and aggregating one day costs 0.35 s. Fixed-name rolling views would make every IVT query read the whole window (~15 GB, ~11 s), so that option is ruled out on measurement rather than instinct. **Option B** — one dedicated schema, granted once — is the recommendation. See [CAKE-STAGING-VALIDATION.md](./CAKE-STAGING-VALIDATION.md) § 2. --- ## OD-5 — IVT aggregation: bounded view **Chosen (implemented).** Bounded SQL view. Rationale and rejected alternative: [IVT aggregation bound](CAKE-IVT-AGGREGATION-BOUND.md). --- ## OD-6 — Shard retention floor **Why it matters.** `getAdvertiserClickDetails` silently `continue`s when a shard is missing. For a UI that is tolerable. For an evidence tool it is not: a silently-partial answer is worse than an error, because the model will present it as complete. **Proposed (implemented).** The executor probes `information_schema.TABLES` (covers `VIEW` and `BASE TABLE`); missing shards are reported as `shards_missing[]` with `partial: true`. If **all** shards are missing the tool errors. Monitor day shards (`Shorty.validate_click_monitor_*`) are base tables — probing `VIEWS` only made every monitor window look empty. **Needs from DBA.** How many days of `click_*`, `publisher_postback_*`, and `amzn_incoming_blacklist_requests_*` are retained, so the documented max windows do not routinely exceed what exists. The nightly job's `p_keep_days` must be ≥ the largest max window (31). ### ✅ Staging answered by the 2026-08-12 harvest `catalogs/schemas/staging/shorty.json` lists what actually exists: | Family | Strict `YYYYMMDD` shards | Oldest | Newest | Max window | Verdict | | --- | --- | --- | --- | --- | --- | | `click_` | 49 | 2026-06-01 | 2026-08-12 | 31 | ✅ 72 days of history, comfortable | | `publisher_postback_` | 13 | 2026-02-03 | 2026-08-07 | 7 | ⚠️ sparse — see below | | `amzn_incoming_blacklist_requests_` | 123 | 2026-02-27 | 2026-08-03 | 31 | ✅ | **No window change needed.** `click_` reaches back 72 days, so the 31-day IVT ceiling cannot outrun retention, and `p_keep_days = 45` on the prune procedure sits safely between the two. **These are not daily series.** 49 shards across 72 days means roughly 23 days simply have no table. That is why the connector reports `partial` with `shards_missing[]` rather than silently returning a short answer — the gaps are real, not a bug. **`publisher_postback_` is sparse in staging**: 13 shards across six months, newest 2026-08-07. The tool defaults to *today*, which in staging returns `NO_SHARDS_AVAILABLE`. That is correct behaviour — it refuses to invent data — but staging smoke tests must pass an explicit `date_range` on a date that exists, e.g. `2026-08-07`. Whether prod is equally sparse is unknown. ### Still open **Production retention.** The numbers above are staging only. Confirm prod separately before enabling the tools there, since prod could delete far more aggressively. Re-run at any time to refresh: ```bash npm run harvest:schemas -- --env staging --target shorty ``` --- ## OD-7 — IP address handling in `get_postback_report` **Why it matters.** `publisher_postback_*.ip` is a raw client IP — personal data, and the spec's redaction posture excludes addresses from tool responses. **Proposed (implemented).** Truncate to /24 (IPv4) and /48 (IPv6) **in the view**, so a full IP never leaves the database, with a second truncation in `redaction/postback-url.ts`. **Alternative.** Drop the column entirely. Cheaper to defend, but loses the ability to answer "did these postbacks all come from one source?", a common fraud question. **Needs from Security.** Sign-off that /24 is sufficient de-identification here. --- ## OD-8 — Synthetic staging fixtures Spec §5.3 item 6 requires synthetic affiliate and advertiser reporting fixtures in staging. That is **not** in TW-314–TW-317 and is not delivered here. It blocks the "staging E2E" exit criterion on TW-316 and TW-317. Flagging so it gets a ticket rather than being discovered at acceptance. Compare AdCenter, which already has synthetic advertiser `19880` and `docs/fixtures/adcenter-staging.json` from TW-301. cake needs the equivalent. --- ## OD-9 — RO DSN provisioning The four tools need three DSNs per environment, following the schema-harvest naming convention: ``` CAKE_ADMIN_RO_DSN_STAGING CAKE_ADMIN_RO_DSN_PROD CAKE_WHALE_RO_DSN_STAGING CAKE_WHALE_RO_DSN_PROD CAKE_SHORTY_RO_DSN_STAGING CAKE_SHORTY_RO_DSN_PROD ``` Prod additionally requires `RO_SQL_ALLOW_PROD=true`, mirroring `SCHEMA_HARVEST_ALLOW_PROD`. Each points at a user holding SELECT on the approved `lucos_ro_*` views and **nothing else** — created by TW-314 in this repo. Never committed; host env or vault only.