Crypto cost-realism labels investigation and ADR pre-lock

VERDICT — Crypto Cost-Realism Labels

Plain English: A backtest that assumes you buy at the bar's close price and sell later at close price is lying twice — you can't trade instantly (the price moves against you in the seconds before your order fills = slippage), and Binance takes a fee on every trade. The forex pipeline already records, for every bar, the worst price a real market order would have filled at within 3 seconds of bar close (plus the exit leg 3s later, plus benchmark prices). Crypto records none of it. This work closes that gap for the crypto table, adapted for the fact that Binance spot trades have no bid/ask — so we assume the worst trade print in the forward window was our fill.

These are labels (they see 3–6 seconds into the future), never features. A feature that could see the future would look spectacular and poison every model trained on it — the exact leakage class this team was burned by before.

The 5 locked decisions (evidence-backed, not Terry's to relitigate)

L1 — Separate label table, not inline columns

opendeviationbar_cache.open_deviation_bar_labels, mirroring the parent: ENGINE = ReplacingMergeTree(computed_at), PARTITION BY (symbol, threshold_decimal_bps). Why forced: the parent table is ReplacingMergeTree(computed_at); any inline label written post-hoc gets wiped the next time the sidecar/kintsugi re-inserts a fresher row version with NULL labels. Inline is only safe if every writer computes labels at insert time — which the streaming engine cannot do (it lacks the forward window at bar close). A separate table = zero new writers on open_deviation_bars → satisfies writer-ownership #280 by construction. (Round-1 guards + Round-2 bar-identity.)

L2 — Post-hoc backfill over the Parquet tick cache, sidecar path intentionally not involved

The Tier-1 Parquet tick cache is a sufficient forward-window substrate: zero interior missing days for all 16 symbols, spanning first-bar→last-bar. This directly answers HANDOFF hard-req 3's "document why the sidecar path is intentionally not involved": labels need the forward window the streaming engine doesn't have at bar close. Backfill runner = a stripped fork of repair_direct_parquet.py's scan skeleton; its --no-service-stop refusal does NOT transfer (that gate exists for bar-table partition races; a separate-table writer has none). (Round-2 tick-cache + oracle-and-backfill.)

L3 — Key on first_agg_trade_id; labels survive repairs as dangling/missing, never silently wrong

Bar construction is a pure deterministic function of (anchor boundary, immutable aggTrade stream, threshold). Under both sidecar clean-slate DELETE+refill and every kintsugi repair that commits (they abort unless the recomputed tail rejoins at the exact TID), bars re-appear at bit-identical keys. The only staleness mode is a dangling label (key deleted by a repair that rewrote a corrupted range) or a missing one (new key) — never a silently-wrong label. Mitigation: the primary read joins from bars (bars LEFT JOIN labels) so unlabeled bars read as NULL and dangling labels are simply not selected; the JOIN also matches the stored last_agg_trade_id/close_time_us consistency columns, so a same-key-but-reshaped bar reads NULL rather than a stale label; a periodic anti-join/consistency sweep re-labels missing keys (the pattern kintsugi's residual sweeper already uses). first_agg_trade_id is the structural choice: it's the ORDER BY / dedup key, strictly monotone under Stathera, and the key every production DELETE already scopes on. (Round-2 bar-identity: uniqueness verified collision-free on 4 partitions / ~25.4M bars.)

L4 — Window membership is TID-anchored, not pure-timestamp

tick.agg_trade_id > bar.last_agg_trade_id AND normalize_us(tick.timestamp) < bar.close_time_us + H·1e6. Why forced: 100% of pre-2025 bars have ms-derived close times (µs value ≡ Binance ms ×1000), so many distinct aggTrades share one timestamp and a pure-timestamp boundary is ill-defined. TID-anchoring excludes the breach trade deterministically (by TID, not time) and is immune to the ms/µs mix. Empty window → NaN → NULL, never 0, never a copied close (HANDOFF §3.3) — and this is not an edge case: at H=1s, 18–24% of illiquid-symbol bars have no in-window trade (8.9–13.3% at 3s). (Round-2 tick-cache §3–4.)

L5 — Fee rides a ClickHouse DEFAULT-expression column with the constant pinned in the COMMENT

Forex precedent: derived cost columns are Nullable(Float64) DEFAULT <pure expr over raw label columns> with the formula + provenance in the COMMENT ("replay-safe"). Crypto: label_fwd_{H}s_roundtrip_cost_bps as a DEFAULT expression embedding the pinned VIP5 + 25%-BNB taker constant, with value + retrieval date + Binance schedule URL in the COMMENT — swappable via ALTER MODIFY COLUMN DEFAULT without touching Rust, and it keeps the countIf(isNotNull()) coverage-audit surface a COMMENT-only constant would lose. (Round-1 ch-schema + writer; Round-2 oracle.)

The 4 decisions — resolved (Terry may override any; see OPEN-QUESTIONS-FOR-TERRY.md)

1. Horizon set → 3s. Corrected after reading the consumer directly: gate05/cost_from_ch.py is parameterized over {1,3,5,10}s and defaults to 3s (it reads the forex table; crypto has no label consumer yet). The handoff also says 3s — no conflict. 1s lives only in a secondary benchmark run (BENCHMARK_2026-04-29.md), not the gate. Ship 3s; schema stays horizon-parameterized so 1s/5s/10s is a cheap add-later backfill. (An earlier draft framed this as a blocking 3s-vs-1s conflict — retracted.)
2. Steady-state labeling → backfill-only now + a follow-up issue for the recurring top-up job.
3. mql5 probe twin → deferred to a later, separate mql5 PR (mql5/findings/forex_shared_data/orthogonality_probe.py targets the crypto table).
4. Acceptance → "100% minus per-symbol-quantified empty-window NULLs" (forex's ±5%-vs-reference precedent), not literal 100%.

Sequencing: ADR does not lock until Terry answers. Per the handoff, ping after reading (done, msg 7458) and again before the ADR locks.