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

# Bulk replay

# Runbook: `bulk-replay` — drain pending corporate\_actions backlog

**When to run:** one-shot historical backfills where the per-symbol
worker's hours-per-replay mutation cost would be prohibitive. The
daemon's normal worker handles the 1-5 splits/month steady state; this
tool handles the backlog (initial 5-year seed, or any manual bulk
enqueue of pending rows).

## Prerequisites

* SSH access to compute server (for binary location + env).
* Source the daemon's env file: `set -a; source /opt/sequency/config/corporate-actions-daemon.env; set +a`.
* Daemon's worker goroutine is either stopped or idle — concurrent
  replays of the same rows would race. bulk-replay acquires the same
  `sequency.replay_locks` row as the daemon, so they can't both run,
  but stopping the daemon explicitly (`sudo systemctl stop
  corporate-actions-daemon`) is cleaner than racing.
* No operator is mid-run on the `replay-split` CLI for any row in the
  pending queue. `replay-split` does NOT hold the advisory lock — if
  you fence only the daemon but an operator is interactively replaying
  NVDA in another SSH session, both processes will fight for the same
  CH mutations. Coordinate out-of-band.

## Dry-run (always first)

```bash theme={null}
/opt/sequency/services/corporate-actions-daemon/bulk-replay -limit 5 -dry-run
```

Inspect:

* Row count — matches your expectation?
* Sample DELETE WHERE clause — predicate includes expected symbols?

## Preflight (verify blast radius)

```bash theme={null}
/opt/sequency/services/corporate-actions-daemon/bulk-replay -limit 5 -preflight-only
```

This runs 5 `SELECT count()` queries (one per rollup table + one against
market\_data\_1min) using the same predicate the DELETE will use. Output:

```
rows to DELETE per table: 5m=... 15m=... 1h=... daily=...
market_data_1min count (not deleted, dedupes via ReplacingMergeTree): ...
```

If any count is zero, the plan is likely mis-formed. If the total is
very large (millions), consider a smaller `-limit`.

## Live run (start small)

```bash theme={null}
# Drain the first 5 pending rows. Review behavior before scaling up.
/opt/sequency/services/corporate-actions-daemon/bulk-replay -limit 5
```

You'll be prompted `Type 'yes' to continue:` before any destructive
step. Phases logged in order:

```
--- phase=delete_rollups ---
--- phase=wait_mutations ---
--- phase=fetch ---
--- phase=insert ---
--- phase=mark_completed ---
SUCCESS: completed=N fetch_failed=M insert_failed=P
```

## Scale up

After the small batch verifies cleanly, drain larger batches:

```bash theme={null}
/opt/sequency/services/corporate-actions-daemon/bulk-replay -limit 50 -yes
/opt/sequency/services/corporate-actions-daemon/bulk-replay -limit 500 -yes
```

The `-yes` flag skips interactive confirmation for scripted runs.

## Rebuild the rollups after the 1min rewrite (mandatory closing step — #1748)

The `delete_rollups`/`insert` phases are NOT sufficient to leave the rollup
tables correct: materialized views only ever see insert blocks, so replay
inserts layer fresh aggregate states into buckets whose surviving states
still encode the pre-adjustment era (#1747 measured the result on NVDA:
double-counted `sumMerge` volumes, argMin/argMax extremes spanning both
price bases, missing buckets). No `OPTIMIZE` repairs this.

After any replay batch completes, rebuild every affected rollup partition
from the deduplicated 1-minute tape and swap it in atomically:

1. Snapshot each rollup table (`ALTER TABLE <t>_pre ATTACH PARTITION ... FROM <t>` — hardlinks, near-free).
2. Rebuild the affected partitions into a staging table with an
   `INSERT ... SELECT` that reproduces the MV's exact aggregate-state
   expressions over `market_data_1min FINAL` (states built from the deduped
   tape have unique extrema per bucket, so reads become deterministic).
3. Verify: per-partition logical bucket counts (staging vs live) plus a
   spot symbol compared field-by-field against a direct deduped recompute
   (expect 0 diffs staging-side).
4. `ALTER TABLE <live> REPLACE PARTITION <p> FROM <staging>` per partition.

The 2026-08-11 full-table execution (scripts, logs, verification record) is
on [#1748](https://github.com/sequencyhq/sequency-data/issues/1748) — reuse
`rebuild-1748-phaseA.sh`/`phaseB.sh` restricted to the affected partitions.
For a small batch, the affected set is each replayed symbol's months across
all three grains (daily partitions are NY-calendar **years**).

## Mutation timeout

Default is 4 hours. The single bulk DELETE mutation rewrites every
part in each rollup table once, regardless of how many rows match.
For the full 466-row 5-year backlog that took \~X minutes in testing
(update after first prod run).

If the timeout hits, the CLI errors out but the mutation keeps running
in CH's background. Re-running bulk-replay is safe — the still-pending
mutation will eventually finish, and the new run's wait will see it
clear.

## Failure handling

Per-symbol Alpaca fetch or INSERT failures are collected, logged, and
the corresponding `corporate_actions` row is marked
`replay_status='failed'` with the error string. The bulk run continues
for all other symbols.

To retry a failed symbol: query its `corporate_actions` row, reset
`replay_status='pending'`, and re-run bulk-replay.

## Verification after drain

```bash theme={null}
# Sample a symbol from the batch and spot-check pre/post-split bars.
curl -s 'http://10.0.0.2:8123/?database=sequency' --data-binary "
SELECT date_trunc('day', time) d, first_value(open) opn, last_value(close) cls
FROM sequency.market_data_1min
WHERE symbol = 'NVDA' AND time >= '2024-05-10' AND time < '2024-07-10'
GROUP BY d ORDER BY d
"
```

Prices should be on the same adjusted scale across the split date (no
\~10× discontinuity for a 10:1 split).

## Interrupting a run

`Ctrl-C` sends SIGINT → ctx cancels → the CLI aborts between phases.
In-flight mutations in CH keep running in the background (they're
independent of the CLI process). Re-run the CLI later; the wait phase
will see them clear.

If you need to force-kill a stuck mutation:

```sql theme={null}
KILL MUTATION WHERE database = 'sequency' AND table = 'market_data_15min' AND NOT is_done
```

## What if the DELETE phase aborts partway through (C2)

Symptom: operator sees `log.Fatalf: alter delete market_data_XYZ: …`
after one or more earlier DELETEs succeeded. State on disk:

* Some rollup tables have mutations running in background
* The failing table has NO pending mutation
* `corporate_actions` rows are still `replay_status='pending'`
* No INSERTs ran yet

**Recovery:** re-run `bulk-replay` with the same limit. The idempotency
invariants guarantee correctness:

1. Re-issued DELETE on tables where the mutation is already running
   merges into a single pending mutation (same predicate = same effect).
2. Re-issued DELETE on tables that errored out last time is just a
   fresh attempt.
3. `waitRollupMutations` observes all 4 tables clear before
   INSERT/MV phase fires.

Do NOT fix the state manually (e.g. ALTER DELETE individual tables by
hand) — you'll diverge from what `bulk-replay` expects on the next run.

## ReplacingMergeTree freshness (M7)

`market_data_1min` is ReplacingMergeTree on `(symbol, time, …)` — duplicate
inserts dedupe on merge, with the newer `updated_at` winning. **Until CH's
background merge runs**, a direct `SELECT … FROM market_data_1min` may see
2× the expected rows for the replayed window (old and new bars both
present; merge hasn't happened yet).

**Do NOT verify a replay through the rollup views** (`v_market_data_5min`,
`v_market_data_15min`, `v_market_data_1hour`, `v_market_data_daily`). This
runbook used to recommend exactly that, on the theory that their
`sumMerge`/`argMaxMerge` finalizers clean duplicates up. They do not, and the
rollups are the one place a replay's duplicates are permanent (#1747):

* The rollups are AggregatingMergeTree targets fed by materialized views on
  `market_data_1min`. An MV only sees the insert block in front of it, never
  the collapsed ReplacingMergeTree state, so a replayed minute contributes a
  SECOND aggregate state to its bucket.
* `sumMerge(volume)` then adds both — the bucket's volume is permanently
  double-counted. `maxMerge(high)` / `minMerge(low)` take the extreme across
  pre- and post-adjustment prices. `argMinMerge(open)` / `argMaxMerge(close)`
  have two candidates carrying the SAME timestamp and break the tie by
  physical part layout, so they return different values run to run.
* No `OPTIMIZE` repairs this. The contaminated states are already written;
  merging them just freezes one arbitrary answer.

Verify against `market_data_1min` with the dedup written out, which is
merge-independent and needs no `OPTIMIZE` first:

```sql theme={null}
SELECT time,
       argMax(open,   updated_at) AS open,
       argMax(high,   updated_at) AS high,
       argMax(low,    updated_at) AS low,
       argMax(close,  updated_at) AS close,
       argMax(volume, updated_at) AS volume
FROM sequency.market_data_1min
WHERE symbol = '<SYM>' AND time >= '<from>' AND time < '<to>'
GROUP BY time ORDER BY time
```

`OPTIMIZE TABLE sequency.market_data_1min PARTITION <YYYYMM> FINAL` still
collapses the duplicates physically, and is worth issuing for downstream
readers that do NOT dedup. It is expensive on large partitions, and it does
not repair the rollups.

Consumers that still read the rollup views are reading a contaminated window
until those buckets age out. The pattern/shape/lifecycle backfills no longer
do — they build every grain from the deduplicated 1min tape
(`services/historical-replay/pattern_event_store.go`).

## Expected phase durations (500-row plan)

Calibrate after first full run. Rough estimates going in:

* **delete\_rollups** (issue 4 ALTER DELETEs): seconds
* **wait\_mutations** (CH rewrites every part of 4 rollup tables once):
  hours — dominated by `market_data_15min` (\~100 parts, \~1M rows each;
  \~3-5 min/part under typical load). **This is the long pole.**
* **fetch** (500 × 5yr paginated Alpaca calls, 4 concurrent, 100 req/min):
  \~30-60 min depending on `-max-rpm`
* **insert** (sequential per-symbol JSONEachRow): \~30-60 min
* **mark\_completed** (one UPDATE, tuple-IN on PK): seconds

Plan a 4-6 hour maintenance window for the full 466-row drain.

## If the rollup aggregates look wrong post-drain

Ultimate recovery: restore from the last `clickhouse-backup` snapshot
in DO Spaces (see `memory/session-2026-04-17-18-infra-cleanup.md` for
the backup operational path, and `pgbackrest/pgbackrest.conf` for
details — though PG-side, same infra team owns restores). Schedule a
maintenance window; restoring a 40+ GB snapshot takes 30-60 min.