# Spike: the live TidbStore SQL path (E1, E2, E3)

Verified 2026-07-29 against a live TiDB cluster. Everything previously established about aggregate
correctness had run through the JavaScript scan path, so TidbStore's own SQL had never executed
against a database. Running it found two bugs no mock had caught.

Cost: zero. No cluster created. Reused the existing chainscope Starter (`mcp-testing`) in scratch
databases that were dropped afterward, with a temporary `role_admin` SQL user that was deleted. The
demo databases (`pokedex`, `world_facts`) were untouched and no other project was accessed.

Method note: the probe deliberately injected a pool with **no** `timezone` and **no** big-number
options, which is the hazard scenario, so anything that passed did so because of the SQL rather than
because of client configuration.

## E1: timezone round trip. PASSED after the fix

`ts` is a naive `DATETIME(3)`, so nothing in the value says which zone it is in, and correctness
previously depended on the client carrying `timezone: 'Z'`. The fix casts `ts` to CHAR in every
SELECT so the driver never performs a Date conversion.

Verified on the hostile pool:

```
stored     : 2026-03-15T23:59:59.999Z | 2026-03-16T00:00:00.000Z
read back  : 2026-03-15T23:59:59.999Z | 2026-03-16T00:00:00.000Z
byte-identical: true
daily buckets : ["2026-03-15", "2026-03-16"]   (still two distinct UTC days)
[from, to) excludes the boundary event: true
```

This is the assertion a mock cannot make. A silent shift of hours would have moved events across the
UTC day boundary and corrupted every daily and weekly bucket plus both ends of the range filter, with
no error anywhere.

## BUG 1 (found live): the keyset cursor lost precision on AUTO_RANDOM ids

The first run streamed **one row too many**. Isolating it with 2500 rows that all share a single `ts`
(so ordering depends entirely on `id`) made it unmistakable:

```
id typeof          : number
id as number       : 288230376151712500
id as CHAR (exact) : 288230376151712495
PRECISION LOST     : true
rows in table      : 2500
rows scan() saw    : 3000
```

`AUTO_RANDOM` produces BIGINTs beyond the exact integer range of a JS number, so reading `id` as a
number rounds it. A rounded cursor re-yields rows (inflating every count) and, when it rounds the
other way, **silently skips rows**, which would quietly remove them from every aggregate. Fixed by
casting `id` to CHAR and binding the exact text back, so the comparison against the BIGINT column is
exact. Re-verified:

```
rows in table: 2500 | scan saw: 2500 | EXACT: true
```

The mock had passed because it returned string ids. It now asserts the CAST explicitly.

## BUG 2 (found live): `rolledUp` reported false while the rollup had happened

`enforceRetention` reported `rolledUp: false` for TidbStore, because TidbStore has no `appendRollup`:
it rolls up in SQL inside its own `prune`, in one transaction. The flag exists so a caller can tell
whether the counts were preserved before the detail was deleted, so reporting false there was a lie.
Fixed, and now `removed: 2502, rolledUp: true`.

## BUG 3 (found live, latent): the DATE column round-trips badly too

`telemetry_daily.day` came back as a JS Date at LOCAL midnight, serializing as
`2026-01-05T08:00:00.000Z` and `2026-03-15T07:00:00.000Z`. Note the offsets differ (8 vs 7 hours)
because of daylight saving, which is the same silent-corruption class as E1 and would have shifted a
day's counts onto the wrong day. Nothing read that column yet, so it was latent. Fixed by casting
`day` to CHAR in the new `TidbStore.readRollup`, which also gives the hosted store parity with the
local one after pruning.

```
rollup day values: ["2026-03-15", "2026-03-16", "2026-06-01"]
plain YYYY-MM-DD : true
```

## E2: unbounded read. FIXED, mechanism verified live

`read()` was `SELECT *` with no LIMIT, and every aggregate went through it, so a 30-day window would
have pulled the whole table onto the heap of the 512 MB machine that also serves requests.

- Aggregation now folds over a streaming `scan()`, so memory is proportional to the number of groups
  rather than the number of rows. A test uses a store whose `read()` throws, proving no aggregate
  falls back to materializing.
- `TidbStore.scan` pages by keyset (never OFFSET, which re-scans skipped rows and turns a long range
  into quadratic work). Verified live streaming 2500 rows across pages exactly.
- `read()` is bounded and REFUSES past the limit rather than truncating, because a silently partial
  read would make every aggregate quietly wrong.

## E3: the never-executed retention SQL, including the DELETE. PASSED

With the scheduler wired, the first real execution of this SQL would otherwise have been a DELETE
running unattended 24 hours after deploy. Verified live instead:

```
enforceRetention: { removed: 2502, rolledUp: true }
events 2502 -> 0
telemetry_daily: [ 2026-03-15 calls 1, 2026-03-16 calls 1, 2026-06-01 calls 2500 ]
rollup written BEFORE delete: true
```

So the positional `GROUP BY DATE(ts), 2, tool`, the `SUM(outcome = 'error')` coercion, and the
`ON DUPLICATE KEY UPDATE` all parse and behave, the rollup precedes the delete, and the counts
survive the detail.

## Cleanup verified

Both scratch databases dropped (`SHOW DATABASES` then listed only `pokedex` and `world_facts`), the
temporary SQL user deleted (user list back to `root` only), probe scripts removed, and no cluster
created in any project.