T17 · Indexers

A Real-World Problem

Your indexer has run for two weeks without a red dashboard indicator: no crashes, errors, missing blocks, or latency above three seconds.

One morning a user reports receiving a “Received 500” notification last night, but the funds are not in their wallet.

You find the database row. It has a transaction hash, block number, amount, and timestamp. You paste its transaction hash into a block explorer:

“Transaction not found.”

You check the block number. A block exists at that height, but its contents differ entirely from the block in your database. It has no such transaction.

The indexer neither crashed nor skipped a block. It faithfully recorded a history that later ceased to exist and notified a user based on it.

T1 warned that this can happen. T16 had you store block_hash. Now put it to work.

The problem is sharp: the chain’s recent history can be rewritten, but your database does not rewrite itself. What connects the two safely?

Thought Experiment

A noticeboard stands across a river. You copy it through binoculars. Its rule: the newest notices can be torn down and replaced at any time, while older notices underneath stay fixed.

There are three ways to copy it.

First: remember only “which notice did I reach?” Your notebook says “copied through 1000”; next time, start at 1001.

If notices 996–1000 were replaced, you never know. Five notices that never existed remain among thousands of real ones in your book, with no marker to identify them.

Second: record each notice’s fingerprint and the fingerprint it claims for the previous notice. Your notebook contains a linked chain. Before copying notice 1001, compare its claimed predecessor with your fingerprint for 1000.

A mismatch means history changed. You can determine that from your own side of the river immediately, without asking anyone’s opinion.

Third: copy only notices buried far below the newest ones. You will rarely be wrong, but you will always lag reality.

The second approach hides another question: you know 1001 fails to connect, but not how many notices were replaced. Perhaps only 1000, perhaps everything after 996.

The solution is to compare backward: check notebook 1000 against the current noticeboard 1000, then 999, then 998, until the first match. That matching notice is the fork point; everything after it is dirty.

This thought experiment is the chapter’s full skeleton. The rest turns it into reliable code.

Your Decision

Choose a reorg strategy for your indexer.

What Happens

Compare the strategies:

The second column matters: all except the first depend on detecting reorgs. Without detection, even elegant rollback code never runs. That is why T16 stored block hashes.

First conclusion:

A reorg is normal chain behavior, not an exceptional outage. Make it an ordinary indexer code path.

Second, and more important for the opening complaint:

The hard part of a reorg is not deleting data. It is undoing effects that have already escaped your database.

Notifications, emails, and payments have no delete statement. Reorg handling has two parts: inside the database, precisely delete and recompute; outside it, the only defense is not acting before blocks are deep enough.

The two parts use different mechanisms. Mixing them produced the opening notification.

Build a Model

The Main Loop

Start with readable pseudocode for the whole indexer:

loop:
  head = rpc.blockNumber()
  cp   = db.readCheckpoint(chainId)          # Height + block hash

  if not isStillOnChain(cp):                 # 1. Before advancing, check that our position is still canonical
      rollbackTo(findForkPoint(cp))          #    One transaction
      continue

  from = cp.number + 1                       # 2. Range for this pass
  to   = min(cp.number + batchSize, head)
  if from > to: sleep(pollInterval); continue

  for n in from..to:                         # 3. One transaction per block
      block = rpc.getBlockHeader(n)
      if block.parentHash != lastHash: break # Head changed during fetch; recheck on next pass
      logs = rpc.getLogs(n, n, filters)
      db.transaction:
          insertBlock(block)
          insertLogs(logs)                   # Idempotent through primary key
          enqueueOutbox(logs, n)             # Store side effects; do not send them yet
          writeCheckpoint(n, block.hash)     # In the same transaction as data
      lastHash = block.hash

Five decisions are embedded here. Consider them in turn.

Detect Reorgs in Two Places

First, while advancing: the next block’s parentHash must equal the checkpoint’s last_block_hash. A mismatch signals a reorg.

This catches most cases but has a blind spot: a deeper earlier reorg after you have already passed that point, or an RPC service switching between nodes with different head lag, can leave a superficially continuous parentHash.

Second, periodically rescan: fetch hashes of the last D indexed blocks from blocks and compare each against the current onchain block at that height. Make D a multiple of the deepest reorg you have observed.

The scan is cheap and catches the cases the first check misses, which are precisely the hardest to diagnose.

Find the Fork Point by Walking Backward

findForkPoint(cp):
    n = cp.number
    while n > cp.number - maxRollbackDepth:
        onchain = rpc.getBlockHeader(n).hash
        local   = db.blockHash(chainId, n)
        if local is null:  return n        # Missing locally, so treat this as the boundary
        if onchain == local: return n      # First matching height = fork point
        n = n - 1
    raise DeepReorgError(n)

Three key details:

The fork point is the first matching height, not the first mismatching one. That word decides whether deletion uses > or >=. An off-by-one error leaves a dirty block or removes a good one. Neither necessarily throws an error.

Set a maximum rollback depth. Without it, a faulty RPC connection to another network or a half-synced node could make the indexer walk back to genesis and delete everything. That failure mode has happened in real systems.

A deeper mismatch requires human attention. Stop and alert rather than deleting on your own. This is one of the few indexer conditions that should wake someone up.

Linear or binary search? Use linear search. Most reorgs are only a few blocks deep. A few extra RPC calls buy simple, auditable boundary logic. Binary search is premature here.

Roll Back in One Transaction

Use the three tables from T16 and add an outbox for pending effects:

begin;

-- 0. Capture affected holders before deletion; afterward their identities are lost.
create temp table touched_holders on commit drop as
select distinct holder from (
  select from_addr as holder from transfer_events
   where chain_id = $1 and block_number > $fork
  union
  select to_addr   as holder from transfer_events
   where chain_id = $1 and block_number > $fork
) x;

-- 1. Delete fact and block rows above the fork point.
delete from transfer_events where chain_id = $1 and block_number > $fork;
delete from blocks           where chain_id = $1 and block_number > $fork;

-- 2. Delete effects not yet sent.
delete from outbox
 where chain_id = $1 and block_number > $fork and delivered_at is null;

-- 3. Recalculate derived balances for affected holders.
delete from token_balances b
 where b.chain_id = $1
   and exists (select 1 from touched_holders t where t.holder = b.holder);

insert into token_balances (chain_id, contract, holder, balance, updated_at_block)
select ... from transfer_events
 where chain_id = $1 and to_addr in (select holder from touched_holders)
    or chain_id = $1 and from_addr in (select holder from touched_holders)
group by chain_id, contract, holder;

-- 4. Move the checkpoint back to the fork point.
update indexer_checkpoints
   set last_block_number = $fork, last_block_hash = $forkHash
 where chain_id = $1;

commit;

All five steps belong in one transaction. Crash after any one step outside an atomic transaction and the database may contradict itself without an obvious error.

Step 0 is easy to put in the wrong place: collect affected addresses before deleting their event rows. Do it afterward and the recomputation set is empty, leaving derived balances stale.

Two Checkpoint Ordering Mistakes

Remember this rule above all others:

Mistake one: commit checkpoint before writing data
  Crash between steps → checkpoint says “done,” but data was never written
  → permanent missing data with no automatic detector

Mistake two: write data, then commit checkpoint in another transaction
  Crash between steps → data exists, checkpoint did not move
  → restart redoes the range; idempotent insertion blocks duplicates → harmless

Correct: commit data and checkpoint in one transaction

If one transaction is impossible, update the checkpoint last. Repeating work is safer than silently skipping it.

This turns a crash from a correctness problem into a performance problem. It depends on T16 and its on conflict do nothing: idempotency is the foundation of resuming, not an optional optimization.

Backfill and Head Tracking: Same Code, Different Ranges

Historical backfill and live tracking share processing logic. They differ in who determines the range. Live tracking advances from checkpoint plus one serially and checks reorgs each pass. Backfill takes ranges from a work table, can run shards in parallel, and need not worry about stable historical blocks reorganizing.

Parallel backfill has a trap: progress cannot be one number.

Suppose five shards cover 1–100, 101–200, 201–300, 301–400, and 401–500. Shards 1, 2, and 4 finish while shard 3 fails. If progress is the highest completed height, it displays 400. The gap of 100 blocks disappears from monitoring and may never be filled.

Use a range table with a row for each work unit, status, and retry count:

create table backfill_ranges (
  chain_id   integer not null,
  from_block bigint  not null,
  to_block   bigint  not null,
  status     text    not null,          -- pending / running / done / failed
  attempts   integer not null default 0,
  primary key (chain_id, from_block)
);

Then backfill is complete when no row has a status other than done. One SQL query answers that more reliably than “how far did we get?”

Side Effects: Wait Until a Safe Height

The opening notification went wrong here.

Do not perform irreversible actions directly inside the block-processing transaction. Split them into two steps:

create table outbox (
  id            bigserial   primary key,
  chain_id      integer     not null,
  block_number  bigint      not null,   -- Used to decide whether the event is deep enough
  event_key     text        not null,   -- Onchain coordinate used by senders for deduplication
  payload       jsonb       not null,
  delivered_at  timestamptz,
  unique (chain_id, event_key)
);

First, save “what to send” in the same database transaction as the event, but do not send yet. If the transaction fails, the outbox row vanishes. If a reorg removes the block, the pending row is deleted with it.

Second, a separate dispatcher sends only eligible rows:

select * from outbox
 where chain_id = $1
   and delivered_at is null
   and block_number <= $safeHeight        -- Only events deep enough
 order by block_number, id
 limit 500;

safeHeight is where confirmation depth takes effect. An event does not leave your database before it is buried deeply enough.

That closes the issue from T1: a notification for a nonexistent transfer remains unsent in the outbox until a reorg removes it with delete.

Confirmation depth is not one global constant. Configure it by business risk:

Different actions on the same chain should use different depths. Put them in configuration rather than scattering constants through code.

Acceptance: An Automatable Standard

This chapter has a strict testable standard:

Replay from any height and obtain exactly the same final result, byte for byte.

Use the checksum query at the end of T16. Record the checksum, delete data above a chosen height, move the checkpoint back, replay, and calculate again. The values must match.

That turns “is my indexer correct?” into an automated yes/no test. If it cannot pass, its replay or reorg logic is wrong even if no user has yet noticed.

What It Is Called

Hands-on

Check 1: replay the same blocks any number of times; checksum stays fixed
Check 2: simulate a reorg, roll back and replay; checksum returns to its prior value
Check 3: kill the process 20 times while indexing; after each restart the checksum agrees

- One transaction per block
- Ingest events with on conflict do nothing
- Commit checkpoint with data in the same transaction
- Save block headers in blocks, including parent_hash

-- Choose height N, delete everything after it, then move checkpoint back.
delete from transfer_events where chain_id = $1 and block_number > $N;
delete from blocks           where chain_id = $1 and block_number > $N;
update indexer_checkpoints set last_block_number = $N,
       last_block_hash = (select block_hash from blocks
                          where chain_id = $1 and block_number = $N)
 where chain_id = $1;

-- Alter one byte of a block hash, simulating a block that is no longer canonical.
update blocks
   set block_hash = decode('00' || encode(block_hash, 'hex'), 'hex')
 where chain_id = $1 and block_number = $N - 3;

- Does the checksum match the control run?
- Does checkpoint height match the maximum height in blocks?
- Does checkpoint ever say it reached N while N's data is absent?

AI Lab

Step one:
I have a fact table keyed by chain_id + block_number + log_index,
a block table storing block_hash and parent_hash, and a checkpoint table with height
and hash. Write complete reorg detection and rollback logic, including fork-point search.
Provide pseudocode and SQL.

Step two:
My database contains blocks 1000 through 1010. A reorg replaces 1006–1010 with five
new blocks; 1005 and earlier remain. Under your logic, explain in order:
1. Where will processing block 1011 detect the problem?
2. Which heights does findForkPoint compare, and what does it return?
3. Write the deletion condition and say exactly which heights it removes.
4. What changes if I use >= instead of >, and what are the consequences?

Step three:
Before the reorg, my system sent notifications, emails, and rewards for events in
1006–1010. They cannot simply be deleted. Design a way to prevent this, and explain
when your design could still fail.

Real-World Case

Change One Variable

Takeaway Questions

Chapter Check