Handling chain reorganizations in EVM indexers

An event indexer looks simple: read blocks in order, decode logs, write to a database. The problem is that the most recent blocks of a blockchain can still be replaced. When that happens it's called a reorganization (reorg), and a naive indexer keeps showing transfers, balances or events that no longer exist on the canonical chain.

Why reorganizations happen

In networks like Ethereum, different nodes can briefly see different versions of the chain tip. When the network converges, the losing version is discarded and its blocks become orphaned. Their transactions usually return to the mempool and may be included in another block, with a different number, a different position and sometimes a different outcome.

Since the move to proof of stake, Ethereum has an explicit notion of finality. A slot lasts 12 seconds and an epoch has 32 slots; under normal conditions a block is finalized after about two epochs, roughly 13 minutes. Before that, short reorganizations at the tip are possible. Layer-2 networks have their own rules: sequencer confirmation is fast, but finality depends on data being posted to and finalized on layer 1.

Use the confirmation levels the node already offers

Besides block numbers, the JSON-RPC API accepts the tags latest, safe and finalized. They are the most direct way to know how far it's safe to trust:

• latest: the newest block the node knows about. It can be reorganized.
• safe: a block unlikely to be reorganized under normal network conditions.
• finalized: a block finalized by the protocol. Reverting it would require a severe consensus failure.

A simple strategy is to index only up to finalized and never deal with reorgs. The cost is latency: data arrives minutes late. Many products need fresher data, so the indexer has to follow the tip and know how to undo what it wrote.

Store every block's hash

A block's number doesn't change in a reorganization. Its hash does. So every record derived from a block should store the source block's hash, and the indexer should keep the hashes of the recent blocks it processed.

CREATE TABLE indexed_blocks (
  chain_id     integer NOT NULL,
  number       bigint  NOT NULL,
  hash         bytea   NOT NULL,
  parent_hash  bytea   NOT NULL,
  PRIMARY KEY (chain_id, number)
);
 
CREATE TABLE token_transfers (
  chain_id      integer      NOT NULL,
  block_number  bigint       NOT NULL,
  block_hash    bytea        NOT NULL,
  tx_hash       bytea        NOT NULL,
  log_index     integer      NOT NULL,
  sender        bytea        NOT NULL,
  recipient     bytea        NOT NULL,
  amount        numeric(78,0) NOT NULL, -- a uint256 fits in 78 decimal digits
  PRIMARY KEY (chain_id, tx_hash, log_index)
);

The numeric(78,0) type deserves a note: uint256 values don't fit in bigint, and converting them to floating point loses precision.

Detect the reorg through parentHash

When block N arrives, compare its parentHash with the hash you stored for N - 1. If they match, the chain continues. If not, there was a reorganization, and you need to walk back until you find the common ancestor: the highest block whose stored hash matches the current canonical hash.

async function findCommonAncestor(chainId: number, head: Block): Promise<bigint> {
  let height = head.number - 1n;
  while (true) {
    const stored = await db.blockHash(chainId, height);
    if (!stored) throw new Error(`Reorg deeper than the stored window at ${height}`);
    const canonical = await rpc.getBlockByNumber(height);
    if (canonical.hash === stored) return height;
    height -= 1n;
  }
}

Keep a hash window larger than the deepest reorganization you consider plausible. If the search leaves the window, stop and alert: halting indexing is better than writing inconsistent data.

Undo and re-apply in one transaction

Once you've found the ancestor, delete everything derived from blocks above it and reprocess from there. Do both in the same database transaction so no consumer sees the intermediate state.

BEGIN;
DELETE FROM token_transfers WHERE chain_id = $1 AND block_number > $2;
DELETE FROM indexed_blocks  WHERE chain_id = $1 AND number > $2;
-- reprocess canonical blocks from $2 + 1 and insert the new records
COMMIT;

Notice the rollback deletes by block number, not by transaction. After a reorg, the same transaction can reappear in a different block with a different log_index; deleting by block guarantees no stale version survives.

Derived balances and external effects

Two traps appear once the basics work:

• Aggregates. If you maintain balances by summing transfers, rollback must undo those sums too. It's often simpler to recompute the affected aggregates from the raw records.
• Effects outside the database. Webhooks, emails and calls to other systems can't be undone with ROLLBACK. Trigger external effects only for blocks at the right confirmation depth, using an outbox so each effect is sent exactly once.

Expose confirmation to consumers

Different consumers tolerate different risks. A dashboard can display latest data; a settlement process should wait for finalized. Instead of choosing one level for everyone, expose each record's state in the API and let the consumer decide.

{ "tx_hash": "0x9f…", "amount": "2500000000000000000", "block_number": 21480211, "confirmation": "safe" }

Summary

Reorganizations aren't rare exceptions to ignore: they're part of how a blockchain normally works. Storing every block's hash, detecting divergence through parentHash, undoing and re-applying atomically and exposing confirmation levels turn a fragile indexer into a dependable data source.