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)

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

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

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

/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:

Snapshot each rollup table (ALTER TABLE <t>_pre ATTACH PARTITION ... FROM <t> — hardlinks, near-free).
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).
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).
ALTER TABLE <live> REPLACE PARTITION <p> FROM <staging> per partition.

The 2026-08-11 full-table execution (scripts, logs, verification record) is on #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

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

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:

Re-issued DELETE on tables where the mutation is already running merges into a single pending mutation (same predicate = same effect).
Re-issued DELETE on tables that errored out last time is just a fresh attempt.
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:

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.

Start

Operating

API

API reference

Runbooks

Bulk replay

Runbook: `bulk-replay` — drain pending corporate_actions backlog

Prerequisites

Dry-run (always first)

Preflight (verify blast radius)

Live run (start small)

Scale up

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

Mutation timeout

Failure handling

Verification after drain

Interrupting a run

What if the DELETE phase aborts partway through (C2)

ReplacingMergeTree freshness (M7)

Expected phase durations (500-row plan)

If the rollup aggregates look wrong post-drain

​Runbook: bulk-replay — drain pending corporate_actions backlog

​Prerequisites

​Dry-run (always first)

​Preflight (verify blast radius)

​Live run (start small)

​Scale up

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

​Mutation timeout

​Failure handling

​Verification after drain

​Interrupting a run

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

​ReplacingMergeTree freshness (M7)

​Expected phase durations (500-row plan)

​If the rollup aggregates look wrong post-drain

Runbook: `bulk-replay` — drain pending corporate_actions backlog

Prerequisites

Dry-run (always first)

Preflight (verify blast radius)

Live run (start small)

Scale up

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

Mutation timeout

Failure handling

Verification after drain

Interrupting a run

What if the DELETE phase aborts partway through (C2)

ReplacingMergeTree freshness (M7)

Expected phase durations (500-row plan)

If the rollup aggregates look wrong post-drain