islandflow/docs/clickhouse-reset-runbook.md

ClickHouse Reset Runbook

This runbook is for deliberately wiping durable market-data history from ClickHouse in local development or on the VPS. It is destructive. Do not run these commands from application startup, deployment hooks, or unattended scripts.

When To Use

Use this only when an operator has decided that existing option, equity, flow, and derived-event history should be discarded and rebuilt from fresh ingest.

Before running a reset:

• Confirm the target environment: local Docker or VPS Docker.
• Confirm there is no active analysis depending on the existing history.
• Take a backup if the data may be needed later.
• Stop ingest and API services so new writes do not race the reset.

Local Docker Reset

From the repository root:

bun run dev:infra
docker compose exec clickhouse clickhouse-client --query "SHOW TABLES"
docker compose exec clickhouse clickhouse-client --query "TRUNCATE TABLE IF EXISTS option_prints"
docker compose exec clickhouse clickhouse-client --query "TRUNCATE TABLE IF EXISTS option_nbbo"
docker compose exec clickhouse clickhouse-client --query "TRUNCATE TABLE IF EXISTS equity_prints"
docker compose exec clickhouse clickhouse-client --query "TRUNCATE TABLE IF EXISTS equity_quotes"
docker compose exec clickhouse clickhouse-client --query "TRUNCATE TABLE IF EXISTS equity_print_joins"
docker compose exec clickhouse clickhouse-client --query "TRUNCATE TABLE IF EXISTS flow_packets"
docker compose exec clickhouse clickhouse-client --query "TRUNCATE TABLE IF EXISTS smart_money_events"
docker compose exec clickhouse clickhouse-client --query "TRUNCATE TABLE IF EXISTS classifier_hits"
docker compose exec clickhouse clickhouse-client --query "TRUNCATE TABLE IF EXISTS alerts"
docker compose exec clickhouse clickhouse-client --query "TRUNCATE TABLE IF EXISTS inferred_dark_events"

If the local compose project uses deployment/docker/docker-compose.yml, run the same commands with docker compose -f deployment/docker/docker-compose.yml exec clickhouse ....

VPS Docker Reset

On the VPS, first identify the active compose project and ClickHouse service:

docker ps --format "table {{.Names}}\t{{.Image}}\t{{.Status}}"
docker compose -f deployment/docker/docker-compose.yml ps

Then stop writers and run the same TRUNCATE TABLE IF EXISTS ... commands against the active ClickHouse container. Prefer docker compose exec clickhouse clickhouse-client --query "<query>" when the compose project is healthy; otherwise use docker exec <clickhouse-container> clickhouse-client --query "<query>".

Verification

After the reset:

docker compose exec clickhouse clickhouse-client --query "SELECT count() FROM option_prints"
docker compose exec clickhouse clickhouse-client --query "SELECT count() FROM flow_packets"

Restart ingest/API services through the normal dev or deployment path. The options tape should repopulate its 100-row hot head from new signal prints, and older rows should appear only after the scroll gate asks /history/options for ClickHouse-backed history.

One-Time Cleanup: Remove Non-Signal Option Prints

If OPTIONS_PERSIST_SIGNAL_ONLY=true is enabled, historical rows with signal_pass = 0 can be removed once to align stored history with the new ingestion policy:

docker compose exec clickhouse clickhouse-client --query "ALTER TABLE option_prints DELETE WHERE signal_pass = 0"

For deployment/docker/docker-compose.yml, run the same command with docker compose -f deployment/docker/docker-compose.yml exec clickhouse ....

Important notes:

• ClickHouse ALTER ... DELETE is asynchronous; deleted rows may still appear until the mutation is applied.
• You can monitor mutation progress:

docker compose exec clickhouse clickhouse-client --query "SELECT command, is_done, latest_fail_reason FROM system.mutations WHERE table = 'option_prints' ORDER BY create_time DESC LIMIT 5"

• After mutation completion, verify row counts:

docker compose exec clickhouse clickhouse-client --query "SELECT count() AS remaining_non_signal FROM option_prints WHERE signal_pass = 0"

• Optional compaction (larger datasets): run OPTIMIZE TABLE option_prints FINAL during a maintenance window.