​

Runbook / Investigation: regime-replay corrected backfill stuck at 2026-06-12

Every code claim below cites a file:line read from origin/main (worktree .worktrees/data-runs-detail, at/after merge commit 734ce62f). Names that could NOT be confirmed from code are marked # TODO confirm on prod. HISTORICAL SNAPSHOT — READ §10 FOR THE CURRENT STATE. §§1–9 describe the incident AS FOUND, and the fix PR that added §10 rewrote regime-replay-backfill.yml (nightly cron, optional inputs, pinned monotonic computed_at, freshness gate) and touched main.go comments and regime-parity-check.sh. Workflow line-number citations and present-tense claims in §§4–7 (“no schedule trigger”, “default to: is 2026-06-12”, “no SLA row”, input defaults, “final step”) refer to the PRE-FIX file at 734ce62f and are intentionally left as the incident record — do not “correct” them, and do not operate from §7’s original dispatch recipe; the current procedure is §10.

​

1. Symptom

Backtest failed — regime labels stale: cover through 2026-06-12 but backtest date_to=2026-07-16; re-run the regime-replay corrected backfill

• Affected surface: every v2 / cross-sectional backtest whose window ends after 2026-06-12. Windows ending on/before 2026-06-12 pass.
• Blocks: the newly-shipped Builder → rich-backtest flow (app#424) and any recent-window backtest.
• Nature: a backend data-freshness problem — the regime-label coverage has not advanced past 2026-06-12. Independent of frontend routing.

​

2. What the coverage check does (the error source)

• Reads the label coverage with a single query (line 54–55):
  SELECT max(date) AS max_date FROM daily_regime_labels
  
  → table daily_regime_labels, column date.
• Empty/epoch guard (line 64–68): if max_date is None or <= 1970-01-02 raises StaleRegimeLabelsError("… daily_regime_labels is empty …").
• The failing comparison (line 70–74):
  if date_to > max_date:
      raise StaleRegimeLabelsError(
          f"regime labels stale: cover through {max_date} but backtest "
          f"date_to={date_to}; re-run the regime-replay corrected backfill")
  
  Hard-fail, no tolerance / grace window. Any date_to strictly greater than max(date) aborts the run.
• Caller: api/app/services/backtester/executor.py:198–200 invokes it with the split-narrowed end date (effective_end) at run start. Rationale in the docstring (regime_tagger.py:24–32): without the guard, trades past the last label are silently tagged "UNKNOWN" by tag_trades_with_regime (regime_tagger.py:167–173), masking a coverage gap as a benign regime.

​

3. What feeds daily_regime_labels (the dependency chain)

v2 backtest (executor.py:200)
  └─ assert_regime_labels_cover → SELECT max(date) FROM daily_regime_labels   (regime_tagger.py:54)

daily_regime_labels           ← ReplacingMergeTree ORDER BY date              (clickhouse/041_daily_regime_labels.sql:3-13)
  └─ populated ONLY by MV  daily_regime_labels_mv                             (clickhouse/053_daily_regime_labels_projection.sql:49-61)
        SELECT … FROM regime_archive WHERE variant = 'corrected'             (053:60-61)

regime_archive                 ← ReplacingMergeTree(computed_at)              (clickhouse/052_regime_archive.sql:6-24)
  ORDER BY (logic_version, variant, date); variant Enum 'emitted'|'corrected'
  ├─ variant='corrected'  written ONLY by cmd/regime-replay                   (cmd/regime-replay/main.go:141-147, literal "corrected" @142)
  │      inputs = BuildRegimeInputs:
  │        • SPY daily OHLC aggregated from market_data_1min                  (regime_input_builder.go:41-46)
  │        • VIX series from Postgres market_breadth                          (regime_input_builder.go:163-165)
  └─ variant='emitted'    written by live pattern-detector scheduler          (scheduler.go:753 → WriteEmittedArchive, regime_archive_writer.go:238)
         once per trading session (regime_archive_writer.go:216-286)

​

4. What the regime-replay corrected backfill is + how it’s triggered

• Rebuilds a clean regime buffer from epoch and folds collectors.Step() oldest→newest over [from, to], writing one corrected row per session (main.go:120-149). Pure/deterministic fold (main.go:6-16).
• Idempotent: regime_archive is ReplacingMergeTree(computed_at); a given (--from, --to, --run-id) is byte-reproducible (main.go:12-16, resolveComputedAt main.go:163-172). Re-running the SAME run-id dedups to itself; a NEW run-id supersedes.
• Requires --from, --to, --run-id (main.go:59-64); reads CLICKHOUSE_URL (SPY bars + regime_archive) and RELATIONAL_DB_URL (market_breadth VIX) (main.go:88-115).

• on: workflow_dispatch ONLY — lines 16–17. There is NO schedule: / cron trigger (confirmed: grep schedule|cron returns only workflow_dispatch). Nothing runs this automatically.
• Default to: input is '2026-06-12' — line 26. Default from: is '2021-04-22' (line 22); default run_id 'sp1-corrected-backfill' (line 30).
• It cross-compiles regime-replay, scps it to compute, and runs it via SSH reading /opt/sequency/.env (lines 69–107). This is the canonical, NOT-manual-SSH path (workflow header lines 6–9; CLAUDE.md deploy-safety).
• deploy-worker.yml:616-617 only builds the regime-replay binary on deploy — it does not run the backfill.

The default to: '2026-06-12' (regime-replay-backfill.yml:26) matches the stale coverage date in the error exactly. That is the smoking gun.

​

5. Grounded hypotheses for why it’s stuck at 2026-06-12 (ranked)

1. (Most likely) The backfill is manual-only and was never re-run past its initial window. It has no cron (regime-replay-backfill.yml:16-17); the last dispatch used the default --to 2026-06-12 (line 26). The live emitted variant keeps advancing daily but is filtered out of the projection (053:60-61), so daily_regime_labels froze at the last manual backfill. Evidence: workflow is workflow_dispatch-only; default to == the stale date; only main.go:142 writes corrected.
2. A re-run was dispatched but left to at the 2026-06-12 default (or an earlier date), re-writing the same window with no forward progress. Same symptom, same evidence line (:26). Distinguish from #1 only via Actions run history.
3. A re-run was dispatched with a later --to but failed on compute. BuildRegimeInputs hard-errors if inputs are missing over the window (main.go:121-124); a failed job leaves coverage unchanged. Most plausible underlying cause is #4. Rule out via the Actions run log + the job’s own post-run regime_archive summary (workflow lines 103-106).
4. (Upstream) An input needed by the fold is itself stale past 2026-06-12, so even a correctly-dispatched re-run can’t extend coverage:
   • market_breadth VIX in Postgres (regime_input_builder.go:163-165) — if VIX rows stop at 2026-06-12, the fold can’t produce later sessions.
   • SPY daily bars from market_data_1min (regime_input_builder.go:41-46) — if SPY 1-min ingestion lapsed. Less likely (SPY bars are core), but check. If either is stale, the root cause is upstream and the backfill re-run is blocked until it’s refreshed.
5. (Least likely) The daily_regime_labels_mv stopped propagating new corrected inserts. The MV only fires on inserts to regime_archive (053:44-61); if no corrected insert has happened since 2026-06-12 (hypotheses 1–4), there is simply nothing for it to project — this is a consequence, not an independent cause. Only suspect a broken MV if regime_archive has corrected rows past 2026-06-12 but daily_regime_labels does not.

​

6. RUNBOOK — diagnose on prod (READ-ONLY)

curl -s 'http://10.0.0.2:8123/?database=sequency' --data-binary \
  "SELECT max(date) FROM daily_regime_labels"
# Expect: 2026-06-12 (matches 'cover through …' in the error)

curl -s 'http://10.0.0.2:8123/?database=sequency' --data-binary \
  "SELECT variant, count(), min(date), max(date)
   FROM regime_archive GROUP BY variant ORDER BY variant FORMAT TSV"
# Diagnostic:
#   corrected max(date) == 2026-06-12  → backfill never re-run (hyp 1/2)  [MOST LIKELY]
#   emitted   max(date) ≈ today        → live engine fine; only 'corrected' is stale
#   corrected > daily_regime_labels max → MV propagation issue (hyp 5)

gh run list -R sequencyhq/sequency-data --workflow "Regime Replay Backfill" --limit 10
# Inspect the newest run's inputs (from/to/run_id) and conclusion.

ssh [email protected] \
  "sudo journalctl -u pattern-detector --since '48 hours ago' --no-pager | grep -i 'regime archive'"
# systemd unit: pattern-detector.service (systemd/pattern-detector.service).
# A same-session no-op is normal (regime_archive_writer.go:216-234).

# VIX (Postgres market_breadth) — the relational input to the fold:
PGPASSWORD=sequency psql -h 10.0.0.2 -U sequency -d sequency -c \
  "SELECT max(time) FROM market_breadth WHERE vix_level IS NOT NULL"
# SPY daily bars (ClickHouse market_data_1min):
curl -s 'http://10.0.0.2:8123/?database=sequency' --data-binary \
  "SELECT max(toDate(time)) FROM market_data_1min WHERE symbol='SPY'"
# Both should be >= the date you want the backfill to reach; if either stops at
# ~2026-06-12, fix that FIRST — the backfill re-run depends on it.

​

7. Fix path (pipeline, NOT manual SSH) — ⚠️ SUPERSEDED: use the §10 procedure

1. Preconditions: confirm §6.E shows market_breadth VIX and SPY bars are fresh through your target to. If not, resolve that upstream first.
2. Dispatch the backfill: GitHub → Actions → “Regime Replay Backfill” → Run workflow, with:
   • from = 2021-04-22 (default; full byte-reproducible history — safe, ReplacingMergeTree dedups). Or an incremental 2026-06-01 to extend — the rewritten workflow rejects non-dry partial-history runs (the percentile warm-up of a short window diverges from the full fold; §10).
   • to = today (or the latest completed trading session) — this is the value the pre-fix default 2026-06-12 failed to advance.
   • run_id = a NEW unique id, e.g. sp1-corrected-backfill-2026-07-15. A new id gets a fresh computed_at and supersedes prior corrections — WRONG, this was part of the bug (§10.3): the hash-derived stamp is deterministic but NOT monotonic, so a new run id was never guaranteed to supersede. The rewritten workflow always pins a strictly-increasing --computed-at, making the LATEST run supersede regardless of run_id.
   • Optionally set dry_run: true first to print the fold summary with no writes (main.go:131-153). Keep logic_version unchanged (sp1-v1) — a NEW logic_version makes prior backtests non-comparable (workflow lines 11–14; 053 KNOWN LIMITATION 16-23).
3. The workflow self-verifies: the run step prints per-variant count/min/max of regime_archive (pre-fix lines 103–106); post-fix the real verification is the hard freshness/MV-integrity gate step after it.
4. Confirm the fix (READ-ONLY):
   • SELECT max(date) FROM daily_regime_labels ≈ today (§6.A). The MV (053:49-61) propagates the new corrected rows automatically.
   • Re-run a v2 backtest with a recent date_to — it should pass assert_regime_labels_cover and complete.

​

8. Open questions (need prod / GitHub access to answer)

• When was the backfill last dispatched, with what from/to/run_id, and did it succeed? (§6.C gh run list.) Distinguishes hypotheses 1 vs 2 vs 3.
• Is market_breadth VIX fresh past 2026-06-12? (§6.E.) If not, the root cause is upstream VIX ingestion, not the backfill itself. # TODO confirm on prod
• What writes market_breadth VIX, and on what schedule? Not resolved from the pattern-detector code read; likely the daily-daemon or a VIX backfill (cmd/backfill-vix is built in deploy-worker.yml:618-619). # TODO confirm on prod
• Is the live pattern-detector emit still advancing emitted? (§6.B/D.) Confirms the SPY input pipeline is healthy so a re-run will have data.
• Did a second corrected logic_version ever get written? If so the 053 single-version assumption (16-23) is violated and the projection could carry the wrong variant on a date. # TODO confirm on prod

​

9. Single most-likely root cause (one line)

​

10. Resolution (2026-07-15)

• Run history (§6.C): the workflow has run exactly twice ever — both on 2026-06-16, both workflow_dispatch, both success. Never dispatched again; no failed runs exist. The backfill never failed — it was never re-run. (Answers §8 Q1; rules out hypotheses 2/3.)
• Prod substrate (§6.A/B): daily_regime_labels max(date) = 2026-06-12. regime_archive: corrected = 1,243 rows, 2021-07-01→2026-06-12, all with a single hash-derived computed_at of 2021-08-16 (run id sp1-corrected-backfill); emitted advancing through 2026-07-15. Substrate gap, not MV propagation — hypothesis 5 ruled out.
• Upstream healthy (§6.E; rules out hypothesis 4): market_breadth VIX max(time) = 2026-07-15 20:14 UTC; SPY market_data_1min max day = 2026-07-15. (Answers §8 Q2/Q4.)
• Single corrected logic_version (sp1-v1) / single run id — the 053 single-version assumption holds (§8 Q5).

1. Nightly schedule: cron, 06:00 UTC Tue–Sat — the structural fix (regime-replay-backfill.yml). One run per completed trading session: after the prior session’s after-hours bars complete (the SPY daily aggregation is deliberately full-day, parity-locked to the live path — regime_input_builder.go header) and before the 07:00 UTC regime-parity-nightly gate, which then validates the fresh corrected rows.
2. Inputs are now optional, resolved and validated in one step — a schedule run or an all-empty dispatch resolves to from=2021-04-22, to=<current ET date>, run_id=sp1-corrected-nightly-<to>. The hardcoded to: '2026-06-12' default footgun is gone. All values (including dry_run and run_number) are charset-validated before the first GITHUB_ENV write — raw dispatch inputs can carry newlines, which would otherwise smuggle unvalidated variable assignments into later steps’ ssh command lines. Non-dry runs must keep from=2021-04-22: a shorter window’s percentile warm-up (~207 trading days of pad) diverges from the full fold near thresholds, and mixing the two provenances in one table is silent inconsistency.
3. --computed-at pinned to <UTC run date>T06:00:00Z + github.run_number seconds — resolveComputedAt (main.go) derives the ReplacingMergeTree version stamp from hash(run_id), which is deterministic but not monotonic: a newer run id was never guaranteed to supersede an older one (that false claim in main.go’s comments is corrected in this PR). The pinned stamp is strictly increasing across workflow runs, so the LATEST run always supersedes — nightlies, catch-ups, and corrective re-runs after upstream data fixes all behave identically, with no same-day-collision or earlier-to traps. Every pinned stamp also exceeds the original backfill’s 2021-08-16 hash stamp. Re-folds of unchanged history are byte-identical, so overlap supersedes are content-neutral.
4. Hard post-run gate, session-based, on BOTH tables — the job fails when corrected max(date) is more than 1 SPY trading session behind (so holiday clusters cannot false-alarm), or when daily_regime_labels max(date) != corrected max(date) (a broken 053 projection MV — §5 hypothesis 5 — now fails loudly instead of freezing silently). BuildRegimeInputs only WARNs on stale inputs (regimeStalenessTolDays), so this workflow gate is the loud failure that was missing. Failures fire PROBE_ALERT_WEBHOOK, mirroring regime-parity-nightly.yml.
5. Independent staleness backstop in the parity gate — scripts/regime-parity-check.sh gains FATAL check [4c]: more than STALE_TOL_SESSIONS (default 3) SPY sessions beyond corrected max(date) fails the 07:00 UTC parity job. This monitors the producer from OUTSIDE it: if the backfill cron silently stops firing (GitHub drops/disables schedules), its in-job gate never runs — the parity job still pages.
6. docs/DATA_FRESHNESS_SLAS.md gains the previously-missing regime-labels row.