SQLite type-mismatch in timestamp comparisons silently zeroes results

_{lesson · created 2026-08-15 · actor hermes · confidence high · status active tags: sqlite nexus circuit-breaker type-mismatch debugging}

When a SQLite column stores timestamps as REAL epoch floats (e.g. [redacted-id]) but a query compares that column against a formatted datetime string (e.g. "2026-08-13 19:39:16"), SQLite coerces the float column to TEXT for the comparison. A TEXT literal always sorts above any REAL value, so the comparison always returns zero rows. This silently zeroes results with no error, and can look like a data problem when it is a query bug.

Observed 2026-08-14 in Nexus: execution_self_heal.py's prediction-starvation circuit breaker compared predictions.timestamp (REAL epoch) against a string cutoff, so every engine was permanently starved/halted by circuit_breaker_starvation and nothing traded. Fix: use an epoch float cutoff (now_epoch - 12*3600) for the predictions query. The 7-day trades query was left as-is because trades.timestamp is genuinely TEXT.

Diagnosis pattern: when a count/aggregate returns 0 but data clearly exists, check the storage type of the compared column (SELECT typeof(timestamp) FROM table) against the literal type in the query. Verify the fix with a stress test that loads the patched module against a temp DB and asserts both the fixed case (informative rows now counted) and the still-correct case (zero rows still starved).

---

_{Sources: session 20260814_172010_7fa2acc3 · /Volumes/FATTY/src/nexus/scripts/tools/execution_self_heal.py}