# Shard expansion and maximum date-range rules

**Tickets** TW-314 (documented) · TW-315 (enforced in code)
**Spec ref** §5.3 item 3 — "Document shard expansion rules and maximum date ranges for day-partitioned tables"
**Status** Proposed — needs DBA sign-off on §6 retention

---

## 1. Why this document exists

Four tables behind the cake reporting tools are **day-sharded**: the date is part of the table
name, so each day is a physically separate table.

| Base table | Instance | Grain | Read by |
| --- | --- | --- | --- |
| `publisher_postback_YYYYMMDD` | Shorty | row per postback | `get_postback_report` |
| `click_YYYYMMDD` | Shorty | row per click | `get_ivt_report` |
| `amzn_incoming_blacklist_requests_YYYYMMDD` | Shorty | row per blocked request | `get_ivt_report` |
| `cleartrust_raw_date_pubisher_YYYYMMDD` | Admin | pre-aggregated | *not used in Phase 1 — §8* |

Two consequences, both needing rules:

1. **A query over N days is a query over N tables.** Cost scales with the window, so the window
   must be capped.
2. **The table name is data.** It cannot be bound as a SQL parameter, which makes shard identifiers
   the one place in the whole tool surface where a value is interpolated into SQL text — see §5.

## 2. Terms

- **Shard** — one day's table, identified by its `YYYYMMDD` suffix.
- **Shard suffix** — the eight digits, always derived from an already-validated ISO date.
- **Window** — the inclusive `[from, to]` range the caller asked for.
- **Expansion** — turning a window into the list of shards that must be read.

---

## 3. Maximum date ranges

Enforced by `connectors/cake/date-range.ts`, sourced from `constraints` in each
`catalogs/queries/*.json`. The catalog is authoritative; the table below is the rationale.

| Tool | Sharded | Default window | **Max window** | Max rows | Timeout |
| --- | --- | --- | --- | --- | --- |
| `get_advertiser_report` | no | 31 days | **92 days** | 5 000 | 5 s |
| `get_affiliate_report` | no | 31 days | **92 days** | 5 000 | 5 s |
| `get_postback_report` | **yes** | 1 day | **7 days** | 2 000 | 15 s |
| `get_ivt_report` | **yes** | 7 days | **31 days** | 5 000 | 20 s |

**How these were chosen.**

- *Non-sharded tools get 92 days* because `advertiser_stats_report` and the pre-aggregated
  `lucos_ro_affiliate_cpc_summary_daily` are already daily rollups. One index range scan; a quarter is the
  natural business question and costs no more than a month.
- *`get_postback_report` gets 7 days* because it is the only tool returning **row-grain** data.
  Seven shards × row-grain is where a report stops being a report and becomes an export. The 1-day
  default reflects the real investigative question — "did this publisher's postbacks fire on the day
  of the discrepancy?"
- *`get_ivt_report` gets 31 days* because the shard views are pre-aggregated
  ([IVT aggregation bound](CAKE-IVT-AGGREGATION-BOUND.md)), so a shard contributes tens of rows, not
  millions. IVT is a trend question; a month is the minimum useful window.

**Additional window rules.**

1. `date_from` and `date_to` must be strict ISO `YYYY-MM-DD`. No relative strings, no `DD/MM/YYYY`,
   no epoch. See §5.
2. `from <= to`. Equal is legal and means a single day.
3. Window size is inclusive: `2026-08-01 .. 2026-08-07` is **7** days, not 6.
4. **No future dates.** `to` may not exceed today in the database's timezone.
5. A window exceeding the max is **rejected**, never silently clamped. A caller that asked for 90
   days of postbacks and received 7 without being told would draw a wrong conclusion from a
   right-looking answer.

---

## 4. Shard strategy — chosen and rejected

### Chosen: per-day views, expanded by the executor into a bounded `UNION ALL`

A nightly job (`catalogs/queries/cake-ro-shard-views-staging.sql`, TW-314) creates one view per
day per family. The executor expands the window into a `UNION ALL` over those views, capped by §3.

```sql
SELECT ... FROM Shorty.lucos_ro_postback_20260801
UNION ALL
SELECT ... FROM Shorty.lucos_ro_postback_20260802
...                                    -- at most max_window shard branches
```

Why: no new storage, no staleness, and it mirrors how the cake PHP already reads these tables
(`getAffPostBackStats` loops the date array; `getAffClearTrustStats` builds exactly this
`UNION ALL`). The RO user still touches only granted `lucos_ro_*` views, so column allowlisting and redaction hold
on every shard.

### Rejected: nightly rollup summary tables

Pre-aggregate shards into `lucos_postback_daily` / `lucos_ivt_daily` with one static view on top.
Fastest queries, simplest SQL.

Rejected because it introduces a **second source of truth** for numbers this tool exists to
corroborate. If the rollup and cake disagree, the tool is worse than useless — it manufactures a
discrepancy. It also adds storage, a backfill story, and T-1 staleness for a tool whose most common
question is about today.

Worth revisiting if `get_ivt_report` latency proves unacceptable at 31 shards.

### Rejected: one nightly-rebuilt `UNION` view

`DROP` and `CREATE` a single view each night unioning the last N days.

Rejected because the window becomes a **property of the view, not the query**. A caller asking for a
date outside the baked window gets an empty success rather than an error — exactly the silent-partial
failure §6 exists to prevent. It also makes every query pay for N days regardless of what was asked.

---

## 5. Shard identifiers must be validated, not escaped

> **Rule.** A shard suffix is interpolated into SQL text. It must match `^[0-9]{8}$` and must be
> produced by formatting an already-validated date. It is never taken from caller input, even
> indirectly.

This is the only interpolation in the tool surface — everything else is a bound parameter. The
existing PHP builds table names via `date("Ymd", strtotime($date))` where `$date` originates in a
request. `strtotime` accepts `"now"`, `"+1 day"`, `"last monday"`, and returns `false` on garbage,
which `date()` renders as `19700101`. Not exploitable today, but one refactor away from being so,
and it silently reads the wrong table.

The Lucos path:

```
caller input (string)
  → strict ISO parse, reject anything else          date-range.ts
  → range + future + ordering checks                date-range.ts
  → Date objects
  → format to YYYYMMDD                              shard-expander.ts
  → assert /^[0-9]{8}$/ on every suffix             shard-expander.ts  ← belt and braces
  → build view name, interpolate
```

The final assertion is redundant given the parse. It stays because it is the last line before a
string reaches SQL, and its cost is one regex per shard.

---

## 6. Missing shards are reported, never skipped

Shards disappear: retention drops old ones, and today's may not exist before the first write.

The cake PHP calls `_doesTableExist` and `continue`s past a missing shard
(`AffiliateReportModel::getAdvertiserClickDetails`; `getAffPostBackStats` does not check at all and
lets the query fail). For a UI that is reasonable degradation — a human sees a sparse chart and
knows something is off.

For a tool feeding a model it is not. A silently-partial answer is **worse than an error**, because
the model cannot know it is partial and will present it as complete.

Rules:

1. Before executing, probe `information_schema.TABLES` — one query, not one per shard. That catalog includes both `VIEW` (IVT/postback `lucos_ro_*`) and `BASE TABLE` (monitor `validate_click_monitor_*`). Probing `VIEWS` only makes every monitor window look empty.
2. Every absent shard is listed in the response as `shards_missing[]`, with `partial: true`.
3. Present shards are still queried — a partial answer that **declares itself partial** is useful.
4. If **every** shard is missing, the tool errors (`NoShardsAvailableError`). An empty success would
   read as "there were no postbacks", when the truth is "we cannot tell".

```jsonc
{
  "partial": true,
  "shards_expected": 7,
  "shards_read": 5,
  "shards_missing": ["2026-08-01", "2026-08-02"],
  "result_count": 143
}
```

**One deliberate exception.** `lucos_ro_ivt_amzn_*` exists only on days Amazon traffic was blacklisted, so
its absence is normal rather than a gap. It is reported as `amzn_shards_read` and does **not** set
`partial` — otherwise almost every IVT call would claim to be incomplete and the flag would stop
meaning anything.

---

## 7. Where each rule is enforced

| Rule | Enforced in |
| --- | --- |
| Strict ISO parse, ordering, no future | `connectors/cake/date-range.ts` |
| Max window per tool | `connectors/cake/date-range.ts`, values from catalog JSON |
| Suffix format assertion | `connectors/cake/shard-expander.ts` |
| Shard existence probe, `shards_missing` | `connectors/cake/shard-expander.ts` |
| Max rows | catalog `constraints.max_rows`, applied as `LIMIT` |
| Statement timeout | `SET SESSION max_execution_time` in `connectors/cake/executor.ts` |
| Object allowlist | catalog `objects`, checked by `assertObjectsAllowed` (spec §2.6 rule 5) |
| SELECT-only | `connectors/cake/executor.ts`, refused before connecting |

---

## 8. Out of scope for Phase 1

`cleartrust_raw_date_pubisher_YYYYMMDD` (the real table name misspells "publisher") is read by
`AffiliateModel::getAffClearTrustRefStats` for a **referrer** breakdown. `get_ivt_report` does not
expose referrer data in Phase 1: referrer URLs carry the same token-leakage risk as postback URLs and
would need their own redaction rules. A referrer breakdown needs a new ticket, a view on the Admin
instance, and an extension of [the URL redaction rules](CAKE-POSTBACK-URL-REDACTION-RULES.md).