The pipeline works. It reads events from a node, writes rows to a warehouse, and the dashboard matches the block explorer. It stays correct for three weeks. Then one morning the balances are wrong, the totals are slightly too high, and nothing in the logs looks like an error. Every job succeeded.
What happened is that the chain changed its mind, and the pipeline was built by someone who believed the chain was a log.
A chain is a process, not a log
The mental model that causes the damage is the one everyone starts with: blocks arrive in order, each one is final, so indexing is a matter of keeping up. That describes a log. It does not describe consensus.
What actually happens is that a node tells you its current best view of history. Another node may hold a different view for a while, and when the network settles, some blocks you already read are discarded and replaced by different blocks at the same heights. Transactions in the discarded blocks may reappear later, in a different order, with different results, or never reappear at all.
So the correct model is closer to change data capture against a database that occasionally rolls back a transaction you already replicated. Except the rollback is not announced as a transaction boundary, and the depth is not bounded by anything you control.
The three bugs that are always there
The watermark is a block number
The first version of every indexer stores progress as an integer and resumes from it:
# the bug, in two lines
cursor = SELECT max(block_number) FROM events
fetch_logs(from_block=cursor + 1, to_block="latest")This is correct only if height uniquely identifies a block, and height does not. After a reorg your cursor points into a history that no longer exists, and you resume past the divergence without ever noticing it. The rows from the abandoned blocks stay in the table forever, and no future run will look back far enough to find them.
The watermark has to be a block identity, and on every run you have to check that the identity still exists on the chain you are now talking to. If it does not, you walk backwards until you find a block whose hash the node still agrees with. That block is your common ancestor, and everything after it has to be undone before you go forward.
Row identity uses the block number
The natural primary key looks like (block_number, transaction_index, log_index). It is stable, unique, and wrong for the same reason. Two different blocks can occupy height 12,345,678, and a log at the same position in both is a different event about a different history.
Key on (block_hash, log_index) instead, and store block_number alongside as an attribute you can query and index but never trust as identity. The moment identity includes the hash, the rollback becomes a plain delete by hash, and dedup on re-ingest becomes free rather than a reconciliation job.
Nobody reads the removal flag
If you subscribe to logs rather than polling ranges, the node will actually tell you when a log has been orphaned: the log object carries a removed boolean. It is a genuine gift, and it gets dropped on the floor constantly, because the consumer was written against the polling shape where the field is always false and nobody wrote a branch for it.
If your event handler has no branch for a retraction, you have not written an indexer. You have written an accumulator.
Finality decides how much machinery you need
How far back you must be able to undo is not a matter of taste. It follows from the finality model of the chain you are reading, and the models differ enough that a design which is correct on one is negligent on another.
| Finality model | What the node is telling you | Rollback window you must support |
|---|---|---|
| Probabilistic | This block is very likely permanent, and gets likelier with depth | Unbounded in principle. Pick a depth from observed reorg history and accept the residual risk explicitly. |
| Checkpoint or epoch finality | Blocks up to checkpoint X are final by protocol rule | From head back to the last finalized checkpoint. Beyond it, no rollback is possible and none is needed. |
| Single-slot or instant | This block is final now | Effectively none, provided you only ever read finalized state. |
The middle row is the useful one, because it turns an open-ended engineering problem into a bounded one. If the protocol gives you a finalized marker, read it, store it, and treat everything at or below it as immutable. That single fact lets you compact history, drop the rollback path for old data, and give downstream consumers a real guarantee instead of a hope.
Two designs, and the honest cost of each
Index only finalized blocks. Lag behind the head by the finality window and you never see a reorg. The pipeline collapses back into the simple append-only shape everyone wanted in the first place. You pay in latency, and if the product needs to show pending activity, you pay again by building a second, throwaway path for unconfirmed data that is clearly labelled as unconfirmed.
Index optimistically and reconcile. Follow the head, mark rows with the block that produced them, and undo on divergence. Low latency, and considerably more machinery. The core of it is small enough to read in one screen:
# On every cycle: confirm our tip still exists, then extend.
def sync(node, db):
tip = db.tip() # (number, hash) or None
# 1. find the deepest block we still agree with
while tip and node.block_hash(tip.number) != tip.hash:
db.rollback_block(tip.hash) # delete rows keyed by this hash
tip = db.tip() # walk back one block and re-check
# 2. extend forward, one block at a time, hash-linked
start = (tip.number + 1) if tip else GENESIS
for block in node.blocks_from(start):
if tip and block.parent_hash != tip.hash:
break # divergence appeared mid-run; restart
db.apply_block(block) # rows keyed (block.hash, log_index)
tip = block
def apply_block(db, block):
# idempotent by construction: same hash means same rows
db.upsert_events([
{"block_hash": block.hash, "log_index": log.index,
"block_number": block.number, **decode(log)}
for log in block.logs
])
db.set_tip(block.number, block.hash)Three details in there are load bearing. The loop walks back by checking hashes, not by assuming a fixed depth. Extension verifies parent_hash against the stored tip, so a divergence that appears while the run is in flight is caught rather than stitched over. And apply_block is idempotent because identity contains the hash, which means a crashed run can simply be re-run.
Derived tables are where this actually hurts
Rolling back raw events is a delete. Rolling back an aggregate is not, and this is the part that gets discovered in production.
If you maintain a running balance by adding each transfer as it arrives, a retraction leaves you with no way to reverse it, because the aggregate does not remember which inputs produced it. You have two honest options, and one dishonest one.
- Recompute the aggregate from raw events for the affected range. Correct, and expensive in proportion to how much you keep in memory of the range.
- Store the deltas that produced the aggregate, keyed by block hash, so a rollback is the sum of the deltas being negated. More storage, cheap undo.
- Assume it will not matter. This is the one in most systems, and it works right up until a customer reconciles your number against a block explorer.
The same logic applies to anything you have published downstream. If a webhook fired, an email went out, or a payout was triggered off an event that later got orphaned, no amount of database rollback fixes it. Side effects belong behind the finality marker, not behind the head. That is a product rule as much as an engineering one, and it is worth writing down before someone asks why a notification was sent for a transaction that does not exist.
Reconciliation is the only real test
Unit tests will not catch a reorg bug, because the failure is a property of a history you did not simulate. The check that works is boring: periodically re-read a window of blocks from the node, recompute what your tables should contain for that window, and diff. Alert on any difference, not just on large ones.
Alongside it, three metrics tell you whether the design is holding:
- Reorg depth distribution. Not the maximum, the distribution. It tells you whether the rollback window you chose is defensible or a guess.
- Lag behind head, and lag behind finality. Two different numbers with two different meanings, and teams routinely report only the first.
- Rows rolled back per day. If this is flat zero on a probabilistic chain, your rollback path is not running, and you will find out the first time it needs to.
None of this is exotic distributed-systems work. It is ordinary care applied to a data source that is honest about being uncertain, which is more than most sources are. The chain tells you the hash of every block and the parent of every block, and it will tell you when a log was removed. The failure is not that the information is missing. The failure is building as though the information were not needed.