Verify an Indexer Can Recover from a Chain Reorganization

An indexer can reach the latest block and still serve data from an abandoned chain. Its checkpoint advances, its health endpoint stays green and its token balance includes a transfer that no longer exists in canonical history. Recovery needs a stronger acceptance test: after replacing a branch, every indexed result must match an independent rebuild of the selected canonical history. This tutorial turns that requirement into an executable rehearsal. A small Python and SQLite model indexes signed amounts, replaces an orphaned suffix and checks its state against a separate pure replay function. The fixture finishes at block B4 with a total of 24 . Repeating the same input changes nothing. Injecting an exception during recovery leaves the previously committed state intact. The example is deliberately bounded. It uses synthetic block identifiers and complete, already selected branches. It does not implement Ethereum consensus, validate block hashes or connect to a node. The database represents one chain and one aggregate. Those limits make the assertions easy to inspect before adapting them to a production indexer with multiple projections and concurrent readers. The acceptance condition is useful beyond this model. A wallet requires correct ownership and balances; an analytics product requires correct event history and aggregates. Decide which results users depend on before choosing the recovery mechanism. Define the state that must recover A checkpoint containing only a height cannot identify a chain. Two competing blocks can occupy the same position. Record the block number together with its hash, then retain enough parent relationships to establish which stored blocks belong to the replacement branch. Treat the checkpoint as a claim about fully committed application state, not merely the most recent RPC response. For an Ethereum log pipeline, distinguish an observed event from the transaction that produced it. A useful occurrence key contains the chain identity, block hash, transaction hash and log index. The chain identity can live in the database namespace for a strictly single-chain deployment. A transaction hash alone cannot distinguish its appearances across competing histories. The Geth documentation makes the delivery consequence explicit: “a subscription can emit logs for the same transaction multiple times.” Geth documentation, Real-time Events That statement concerns notification behavior, not a promise that every disconnected consumer will receive every correction. Design storage so repeated observations cannot duplicate an occurrence, while a transaction appearing in a different block can be represented accurately. Its execution context may have changed; do not carry its old derived output into the replacement block without decoding the new receipt. The blockchain software engineering scope at Pharos Production includes applications whose product behavior depends on off-chain data. A reorganization therefore belongs in the application acceptance criteria: the frontend can display a wrong balance even when every smart contract executed correctly. This tutorial supplies a concrete database exercise for that boundary; it does not claim a measured customer outcome. Write the recovery contract in terms of visible state. Canonical block membership, active event rows, materialized aggregates and the checkpoint must agree at a committed boundary. If historical orphan records are retained for audit, mark them explicitly and exclude them from current product queries. Deleting them is only one storage policy. Also specify what a reader can observe during recovery. A single database transaction can present an old committed state followed by a new committed state, subject to the database's isolation behavior. An asynchronous projection pipeline needs a visible generation or watermark instead. Otherwise an API can combine the new event table with yesterday's aggregate and return a result that belongs to neither branch. Build a fork with a result you can calculate Use a fixture small enough to audit without trusting the implementation. The shared prefix is G → A1 . The old suffix is A2 → A3 ; the replacement suffix is B2 → B3 → B4 . The caller selects the replacement branch. The model never decides that a branch wins merely because it has more blocks. | Block | Parent | Event | Signed units | Role | |---|---|---|---|---| | G | none | none | 0 | Trusted fixture origin | | A1 | G | deposit | 10 | Shared prefix | | A2 | A1 | shared-tx | 7 | Old occurrence | | A3 | A2 | orphan-tx | -2 | Orphan-only event | | B2 | A1 | shared-tx | 7 | Replacement occurrence | | B3 | B2 | credit | 11 | New event | | B4 | B3 | debit | -4 | Replacement tip | The old total is 10 + 7 - 2 = 15 . Undoing its suffix returns the aggregate to 10 . Applying the replacement produces 10 + 7 + 11 - 4 = 24 . Keeping the old negative event would leave an incorrect result. Keeping both occurrences of the shared transaction would also fail, even if the checkpoint looked correct. These amounts describe a synthetic signed counter. They are not an ERC-20 balance implementation: there are no addresses, decimals, fees or contract-specific event semantics. Use integer quantities in the model so arithmetic noise cannot obscure a branch identity defect. A real token indexer must define how its decoder maps each event into the relevant accounts and units. Make the oracle independent of the repair path. The example folds the chosen branch directly into the expected block rows, event rows and total. It never reads the database or calls the rollback code. This catches an indexer that agrees with its own checkpoint while retaining wrong rows. It does not validate a faulty decoder shared by both paths; production replay needs separate golden receipt fixtures for that risk. An empty block matters too. It advances chain continuity without changing the aggregate. An indexer that checkpoints only blocks containing matching logs has discarded evidence it needs when locating a common ancestor. Keep headers or equivalent ancestry records for the entire retained recovery interval. Make replacement and checkpoint advancement atomic The replacement operation first checks that its input is a connected branch from the trusted origin. Each block must extend the previous hash and increment the height by one. It then compares stored block identities with the candidate branch until their shared prefix ends. Everything after that point is subject to replacement. Bound the amount of history the automatic path may undo. A rollback window is an operational limit, not a claim that deeper reorganizations are impossible. If recovery needs records outside the retained window, stop the normal writer and preserve evidence. Continuing from a guessed ancestor can make a damaged projection look current. Inside one transaction, remove the losing suffix in reverse block order, reverse its aggregate contributions and append the replacement suffix in forward order. Advance the checkpoint only after the new rows and aggregate updates succeed. Committing those changes together is what makes the checkpoint meaningful. Writing it last without a shared transaction is insufficient when earlier writes can persist independently. Inverse arithmetic works for this additive projection. It does not automatically work for every business object. Reversing a maximum value, an ownership transition with side effects or an order-dependent state machine may require before-images, versioned entities or a replay from a saved boundary. Test the actual projection algorithm rather than assuming every update has a safe subtraction. The code uses explicit SQL transaction commands and disables Python's implicit transaction opening with isolation_level=None . See the Python sqlite3 transaction documentation for the connection behavior. This choice keeps the demonstrated boundary visible. It is not a database configuration recommendation for every deployment. There is one writer in this fixture. A production worker pool needs a fence that prevents an older worker from committing after a newer recovery generation takes ownership. A database lock can serialize writers, but lock acquisition alone does not prove that the worker's fetched branch is still acceptable. Validate the generation or expected checkpoint at the commit boundary. Run the storage model Save the following as reorg_harness.py . Block hashes are readable fixture labels; the input contract assumes immutable content for each label. There is no remote I/O inside the transaction. The finalized argument, when supplied, represents an externally verified anchor that the selected branch must contain. The dApp delivery process described by Pharos Production includes indexer strategy during discovery and indexer deployment during production readiness. The dApp development and indexer delivery process provides a place to assign this rehearsal to a release owner. Passing the local example establishes only the behavior demonstrated below; the deployed storage and RPC adapter still need their own evidence. import sqlite3 from dataclasses import dataclass @dataclass(frozen=True) class Block: height: int hash: str parent: str events: tuple = () # (transaction hash, log index, signed units) G = Block(0, "G", "") A1 = Block(1, "A1", "G", (("deposit", 0, 10),)) A2 = Block(2, "A2", "A1", (("shared-tx", 0, 7),)) A3 = Block(3, "A3", "A2", (("orphan-tx", 0, -2),)) B2 = Block(2, "B2", "A1", (("shared-tx", 0, 7),)) B3 = Block(3, "B3", "B2", (("credit", 0, 11),)) B4 = Block(4, "B4", "B3", (("debit", 0, -4),)) OLD = [G, A1, A2, A3] NEW = [G, A1, B2, B3, B4] class Index: def init(self, path): self.db = sqlite3.connect(path, isolation_level=None) self.db.executescript(""" CREATE TABLE IF NOT EXISTS blocks( n INTEGER PRIMARY KEY, hash TEXT UNIQUE, parent TEXT); CREATE TABLE IF NOT EXISTS events( block_hash TEXT, tx TEXT, idx INTEGER, units INTEGER, PRIMARY KEY(block_hash, tx, idx)); CREAT