Index logs with reorg handling
An indexer that stores a block number but not the block hash will serve data from a chain
that no longer exists after a reorg. Key every row by blockHash and roll back on mismatch.
Procedure
Persist the cursor as (block_number, block_hash), never block number alone. Use
block_hash as the primary key component for log rows:
CREATE TABLE logs (
block_number INTEGER, block_hash TEXT, log_index INTEGER,
address TEXT, topic0 TEXT, data TEXT,
PRIMARY KEY (block_hash, log_index)
);
Before appending a new block, verify it still extends the stored head.
python3 - <<'PY'
import json, subprocess
def head(n, rpc):
out = subprocess.check_output(["cast","block",str(n),"--json","--rpc-url",rpc])
b = json.loads(out); return b["hash"], b["parentHash"]
last_n, last_h = db.head() # stored (number, hash)
now_h, parent = head(last_n, RPC)
if now_h != last_h:
rewind(last_n) # hash changed -> orphaned
PY
On mismatch, walk back until the stored hash matches the canonical chain. Delete rows with
the orphaned hashes, set the cursor to the last common ancestor, and re-fetch forward.
cast block $((N)) --json --rpc-url $RPC | jq -r '.hash, .parentHash'
cast block $((N-1)) --json --rpc-url $RPC | jq -r .hash # must equal .parentHash above
Only index blocks at confirmed_depth behind the head. Mainnet: 12 blocks. Arbitrum: wait
for the L1-finalized batch. Optimism/Base: use the safe head returned by
eth_getBlockByNumber("safe", false).
Store the log's removed flag from eth_getLogs; a subsequent poll can return the same
log with removed: true instead of re-fetching ranges.
Pitfalls
- Treating
latest as final. A reorg of depth 2 is routine on mainnet and common on testnets;
anything indexed off latest will be rolled back eventually.
- Cursor stored as only a block number: after a reorg you cannot tell which hash you processed,
so you either re-index duplicates or skip blocks.
logIndex is unique only within a block and can be reused after a reorg reorders logs, so
(blockNumber, logIndex) is not a stable key — include the hash.
- Long polling gaps widen the reorg window; if you resume after downtime, re-verify the last
confirmed_depth + slack blocks, not just the head.
- Providers can serve a cached, slightly stale head; a hash mismatch that "flips back" on the
next poll usually means the RPC is behind, not that the chain reorged. Re-query a second
provider before rewinding.
Verification
cast block $N --json --rpc-url $RPC | jq -r .hash # canonical head hash
# indexer's stored hash for block N must equal that value
After indexing, re-run the same block range and diff the row counts; they must be identical,
and the indexer's head hash must match cast block <head> --json | jq -r .hash.
1---2name: index-logs-with-reorg-handling3description: Use when building or reviewing a log indexer that must survive chain reorgs without serving orphaned rows or double-counting events.4metadata:5 source: https://skillmd.com/skills/jiayaoqijia/index-logs-with-reorg-handling6---78# Index logs with reorg handling910An indexer that stores a block number but not the block hash will serve data from a chain11that no longer exists after a reorg. Key every row by `blockHash` and roll back on mismatch.1213## Procedure14151. Persist the cursor as `(block_number, block_hash)`, never block number alone. Use16 `block_hash` as the primary key component for log rows:1718 CREATE TABLE logs (19 block_number INTEGER, block_hash TEXT, log_index INTEGER,20 address TEXT, topic0 TEXT, data TEXT,21 PRIMARY KEY (block_hash, log_index)22 );23242. Before appending a new block, verify it still extends the stored head.2526 python3 - <<'PY'27 import json, subprocess28 def head(n, rpc):29 out = subprocess.check_output(["cast","block",str(n),"--json","--rpc-url",rpc])30 b = json.loads(out); return b["hash"], b["parentHash"]31 last_n, last_h = db.head() # stored (number, hash)32 now_h, parent = head(last_n, RPC)33 if now_h != last_h:34 rewind(last_n) # hash changed -> orphaned35 PY36373. On mismatch, walk back until the stored hash matches the canonical chain. Delete rows with38 the orphaned hashes, set the cursor to the last common ancestor, and re-fetch forward.3940 cast block $((N)) --json --rpc-url $RPC | jq -r '.hash, .parentHash'41 cast block $((N-1)) --json --rpc-url $RPC | jq -r .hash # must equal .parentHash above42434. Only index blocks at `confirmed_depth` behind the head. Mainnet: 12 blocks. Arbitrum: wait44 for the L1-`finalized` batch. Optimism/Base: use the `safe` head returned by45 `eth_getBlockByNumber("safe", false)`.465. Store the log's `removed` flag from `eth_getLogs`; a subsequent poll can return the same47 log with `removed: true` instead of re-fetching ranges.4849## Pitfalls5051- Treating `latest` as final. A reorg of depth 2 is routine on mainnet and common on testnets;52 anything indexed off `latest` will be rolled back eventually.53- Cursor stored as only a block number: after a reorg you cannot tell which hash you processed,54 so you either re-index duplicates or skip blocks.55- `logIndex` is unique only within a block and can be reused after a reorg reorders logs, so56 `(blockNumber, logIndex)` is not a stable key — include the hash.57- Long polling gaps widen the reorg window; if you resume after downtime, re-verify the last58 `confirmed_depth + slack` blocks, not just the head.59- Providers can serve a cached, slightly stale head; a hash mismatch that "flips back" on the60 next poll usually means the RPC is behind, not that the chain reorged. Re-query a second61 provider before rewinding.6263## Verification6465 cast block $N --json --rpc-url $RPC | jq -r .hash # canonical head hash66 # indexer's stored hash for block N must equal that value6768After indexing, re-run the same block range and diff the row counts; they must be identical,69and the indexer's head hash must match `cast block <head> --json | jq -r .hash`.