> ## Documentation Index
> Fetch the complete documentation index at: https://docs.sequency.sh/llms.txt
> Use this file to discover all available pages before exploring further.

# Regime replay backfill stale investigation

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

**Date:** 2026-07-15 · **Status:** RESOLVED 2026-07-15 — root cause confirmed on prod + run history; durable fix shipped (nightly schedule, pinned computed\_at, coverage gate — §10) · **Type:** data-freshness incident + diagnose/fix runbook
**Repo:** sequency-data · **Read alongside:** `docs/runbooks/regime-vnext-rd.md`, `docs/superpowers/plans/2026-06-14-regime-sp1-canonical-grid.md`, `.github/workflows/regime-replay-backfill.yml`

> 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

Running a strategy backtest in the v2 rich Strategy-Lab Backtest view
(`/strategy-lab/backtest`, cross-sectional engine) with `date_to` at/near today
fails with:

> **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.

**Reproduce:** POST a v2 backtest with `date_to` > 2026-06-12 (any strategy /
universe). The guard fires at run start, before any simulation
(`executor.py:200`). No prod mutation required to reproduce.

***

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

**File:** `api/app/services/backtester/regime_tagger.py`
**Function:** `assert_regime_labels_cover(ts_client, date_to)` — lines **35–74**.

* Reads the label coverage with a single query (line **54–55**):
  ```sql theme={null}
  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):**
  ```python theme={null}
  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.

So "cover through 2026-06-12" == `max(date)` currently in `daily_regime_labels`.

***

## 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)
```

**Load-bearing fact:** the projection MV filters `variant = 'corrected'`
(`053:60-61`). The **live daily emit writes `variant = 'emitted'`**
(`regime_archive_writer.go:278`, `scheduler.go:753`) and therefore **does NOT
advance `daily_regime_labels`**. Only the `corrected` variant does, and the only
producer of `corrected` rows is the `regime-replay` command (`main.go:142`).
The Python regime backfill referenced in `041`'s header comment is **retired**
(`regime_tagger.py:8-9`, `053` header) — do not resurrect it.

***

## 4. What the regime-replay `corrected` backfill is + how it's triggered

**Producer binary:** `services/pattern-detector/cmd/regime-replay/main.go`

* 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`).

**Trigger — this is the crux:** `.github/workflows/regime-replay-backfill.yml`

* **`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`, `scp`s 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.

There was **no Data-Freshness SLA row for regime labels** in
`docs/DATA_FRESHNESS_SLAS.md` (it listed options/bars/confluence/VP but not
regime) — reinforcing that no scheduled contract kept this data fresh. *(Row
added by the fix — §10.)*

***

## 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)

Run from the compute server (has vRack access to ClickHouse `10.0.0.2` and
Postgres `10.0.0.2`). None of these mutate data.

**A. Current label coverage (the exact value in the error):**

```bash theme={null}
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)
```

**B. `corrected` vs `emitted` coverage in the substrate — proves where the gap is:**

```bash theme={null}
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)
```

**C. Was the backfill ever re-run, and to what `--to`?** (needs GitHub, not prod)

```bash theme={null}
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.
```

**D. Is the LIVE emit still firing?** (rules hypothesis 4 SPY-side out)

```bash theme={null}
ssh sequency@compute.sequencyhq.com \
  "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).
```

**E. Upstream freshness (hypothesis 4):**

```bash theme={null}
# 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

Per CLAUDE.md deploy-safety and the workflow header (lines 6–9), the corrected
series is (re)advanced **only** by re-running the GitHub Actions workflow — never
by SSHing to run `regime-replay` by hand.

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.

**Systemic recommendation (implemented — see §10):** the
absence of a `schedule:` on `regime-replay-backfill.yml` is the structural cause.
Options: (a) add a nightly cron to that workflow that folds `[2021-04-22, today]`
with a per-day `run_id` (mirrors `regime-parity-nightly.yml:17-20`, which already
runs "after the daily regime emit"); or (b) a design change to let the projection
read `emitted` as a fallback where `corrected` is absent (SP2 territory — the
`053` single-logic\_version limitation, 16-23, must be handled first). Option (a)
— plus a monotonic pinned stamp, hard freshness/MV gates, and a parity-side
staleness backstop — shipped with this runbook update; see §10.

***

## 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)

The `corrected` regime substrate that feeds `daily_regime_labels` is advanced
**only** by the operator-triggered `regime-replay-backfill.yml`, which has **no
scheduled trigger** (`workflow_dispatch`-only, lines 16–17) and whose **default
`to` is `2026-06-12`** (line 26) — so it was run once through 2026-06-12 and
never re-run, freezing coverage while the live `emitted` variant (filtered out of
the projection at `053:60-61`) kept advancing unused.

***

## 10. Resolution (2026-07-15)

**Root cause confirmed = §5 hypothesis 1.** Evidence gathered on prod + GitHub:

* **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).

**Durable fix (shipped in the same PR as this runbook update):**

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.

**Catch-up procedure:** dispatch the workflow once post-merge with ALL inputs
empty (resolves exactly like a nightly run: full-history fold to the current ET
date). Then verify per §6.A that `daily_regime_labels` max(date) reaches the
last completed session, and that a recent-window v2 backtest passes
`assert_regime_labels_cover`. If a same-day row ever folds partial after-hours
data (intraday dispatch), any later run supersedes it — stamps are strictly
increasing per run.