Levandor

crypto_shortterm.observations — window_id is NOT asset-unique

6 min read

window_id in crypto_shortterm is a bare 5-minute-window Unix epoch shared by every asset lane at the same wall-clock instant. Any query that joins to observations (or outcomes) on window_id alone silently matches all assets; you MUST add an asset predicate. emitted_signals has no asset column at all — asset identity lives only in source, via an irregular mapping.

The missing-asset-predicate trap (read this first)

window_id does not identify a market. It is open_ts().timestamp() — same integer for btc, eth, sol, xrp, doge, bnb (and btc15/eth15 at 15-min boundaries) in the same window.

  • Joining observations on window_id alone matches every asset lane. Combined with ORDER BY abs(<time delta>) LIMIT 1, the query returns a near-arbitrary asset’s row (whichever lane happened to write an observation closest to your timestamp).
  • Fix: every join/subquery against observations or outcomes MUST carry AND o.asset = <asset>. The reference tool depthprofile does this correctly — crates/analysis/src/bin/depthprofile.rs:72 → WHERE o.asset = $1.
  • emitted_signals has no asset column — you cannot join it to observations by asset directly. Derive the asset label from source with an explicit CASE (see below), never split_part(source,'_',1).

1. observations / outcomes are keyed by (asset, window_id, …)

migrations/0007_multi_asset.sql added asset and re-keyed the multi-asset tables:

ALTER TABLE crypto_shortterm.observations ADD COLUMN IF NOT EXISTS asset text NOT NULL DEFAULT 'btc';
ALTER TABLE crypto_shortterm.observations ADD PRIMARY KEY (asset, window_id, as_of);  -- :3
ALTER TABLE crypto_shortterm.outcomes     ADD PRIMARY KEY (asset, window_id);          -- :7
ALTER TABLE crypto_shortterm.emitted_signals ADD PRIMARY KEY (source, window_id);      -- :14

window_id alone identifies nothing. The rule applies to outcomes too (also (asset, window_id)), not just observations — crypto-shortterm-pnl-attribution-corrections-2026-07-03 hit exactly this on outcomes (a window_id-only join collapsed all assets → coin-flip labels).

2. window_id carries zero asset information

Constructed as the window open time, bare Unix epoch string, at crates/observe/src/lib.rs:165:

window_id: format!("{}", self.window.open_ts().timestamp()),

No symbol, no lane, no prefix. At a given wall-clock instant, all live lanes emit identical window_id values. (5-min lanes share the 300s boundary; btc15/eth15 additionally coincide on the 900s boundaries.)

3. emitted_signals has NO asset column — asset lives in source, irregularly

emitted_signals PK is (source, window_id); asset identity is encoded only in source. (Historical note: 0003_emitted_signals.sql:12 originally keyed it on window_id alone — PRIMARY KEY (window_id) — before 0007 widened it. It also carries a side column (Up/Down), but no asset.)

The source → asset-label mapping is not a clean prefix. From crates/ingest/src/asset.rs:

Asset (enum)labelsource_upsource_down
Btcbtccrypto_5m_algo_upcrypto_5m_algo_down
Ethetheth_5m_algo_upeth_5m_algo_down
Solsolsol_5m_algo_upsol_5m_algo_down
Xrpxrpxrp_5m_algo_upxrp_5m_algo_down
Dogedogedoge_5m_algo_updoge_5m_algo_down
Btc15btc15btc_15m_algo_upbtc_15m_algo_down
Eth15eth15eth_15m_algo_upeth_15m_algo_down

Two independent irregularities defeat split_part(source,'_',1):

  • BTC’s source prefix is crypto, not btc → split_part yields crypto (wrong; should be btc).
  • btc15/eth15 labels ≠ their source prefixes → split_part('btc_15m_algo_up','_',1) yields btc (wrong; should be btc15); same for eth_15m_algo_up → eth vs eth15.

So split_part is wrong for btc, btc15, and eth15 — it only accidentally works for the plain 5m alts. This supersedes the earlier heuristic recorded in crypto-shortterm-pnl-attribution-corrections-2026-07-03 (crypto_5m_algo_*→btc else split_part(source,'_',1)), which handled the BTC-crypto prefix but predates the btc15/eth15 emit lanes and mislabels them. Use an explicit CASE that maps the full source string to the label:

CASE source
  WHEN 'crypto_5m_algo_up'  THEN 'btc'   WHEN 'crypto_5m_algo_down'  THEN 'btc'
  WHEN 'eth_5m_algo_up'     THEN 'eth'   WHEN 'eth_5m_algo_down'     THEN 'eth'
  WHEN 'sol_5m_algo_up'     THEN 'sol'   WHEN 'sol_5m_algo_down'     THEN 'sol'
  WHEN 'xrp_5m_algo_up'     THEN 'xrp'   WHEN 'xrp_5m_algo_down'     THEN 'xrp'
  WHEN 'doge_5m_algo_up'    THEN 'doge'  WHEN 'doge_5m_algo_down'    THEN 'doge'
  WHEN 'btc_15m_algo_up'    THEN 'btc15' WHEN 'btc_15m_algo_down'    THEN 'btc15'
  WHEN 'eth_15m_algo_up'    THEN 'eth15' WHEN 'eth_15m_algo_down'    THEN 'eth15'
END

4. Real-world impact — the 2026-07-09 depth_guardrail bug

scripts/depth_guardrail.sh (commit 326bc2f, E3) omitted the asset predicate. Its touch CTE picks the touch depth at each fire like this:

-- BUGGY: fires has no asset; subquery is asset-blind
(SELECT CASE WHEN f.side='Up' THEN o.best_ask_sz ELSE o.down_best_ask_sz END
 FROM crypto_shortterm.observations o
 WHERE o.window_id = f.window_id                         -- no o.asset = ...
   AND o.as_of BETWEEN f.emit_ts - interval '2 seconds'
                   AND f.emit_ts + interval '2 seconds'
 ORDER BY abs(extract(epoch FROM o.as_of - f.emit_ts)) LIMIT 1) ask_sz

Because fires (from emitted_signals) has no asset and the subquery filters on window_id only, it returns the temporally-nearest observation across all lanes — usually a thin alt. Reported median_touch=33 (vs BTC’s known in-regime median ~658) tripped alert=TRUE and drove a spec rewrite to rev 2.1.

Diagnostic signature for agents

If a per-asset decomposition collapses to a suspiciously narrow band (here 26–38) with BTC indistinguishable from thin alts that are known to differ by an order of magnitude → suspect a missing asset predicate. Grouping the outer query by the fire’s asset (derived from source) does not fix it: the asset-blind subquery still selects the same near-arbitrary row regardless of how you group outside it. The only fix is constraining the subquery itself. Add the derived label in fires and filter the subquery:

WITH fires AS (
  SELECT s.window_id, s.emit_ts, s.side,
    CASE s.source WHEN 'crypto_5m_algo_up' THEN 'btc' /* …full CASE… */ END AS asset
  FROM crypto_shortterm.emitted_signals s WHERE …
) …
  WHERE o.asset = f.asset AND o.window_id = f.window_id AND o.as_of BETWEEN …

5. Adjacent facts (verified)

Related gotcha — per-directive size is gone

emitted_signals.size was dropped in migrations/0005_emitted_signals_v3.sql (DROP COLUMN IF EXISTS size — same migration also drops token_id). Per-directive size is not recoverable from that table. (The guardrail sizes clips from pmv2_wallets.size_scale, not from emitted_signals.)

Non-gotcha — time comparisons are TZ-safe

emit_ts (0003_emitted_signals.sql:3) and as_of (0001_phase0.sql) are both timestamptz. Comparisons like BETWEEN / extract(epoch FROM o.as_of - f.emit_ts) are instant-based and immune to the repo’s known session-TZ=+02 hazard (see spec-grounding-pass context). No AT TIME ZONE juggling needed for these two.

Checklist before shipping any observations/outcomes query

  • Every observations/outcomes join or subquery carries AND <alias>.asset = <asset>.
  • Asset comes from an explicit CASE on emitted_signals.source, never split_part.
  • Sanity-check per-asset output spreads out (BTC touch depth ≫ thin alts). Uniformity across assets = red flag.

Graph View

Backlinks