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));
CREATE TABLE IF NOT EXISTS state(
id INTEGER PRIMARY KEY CHECK(id=1),
n INTEGER, hash TEXT, total INTEGER);
INSERT OR IGNORE INTO state VALUES(1,-1,'',0);
""")
def snapshot(self):
return (
self.db.execute("SELECT * FROM blocks ORDER BY n").fetchall(),
self.db.execute("SELECT * FROM events ORDER BY 1,2,3").fetchall(),
self.db.execute("SELECT n,hash,total FROM state").fetchone(),
)
def sync(self, branch, max_rewind=3, finalized=None, fail_at=None):
# Input is an already selected, immutable full branch from G.
if not branch or branch[0] != G:
raise ValueError("wrong trusted genesis")
for prev, block in zip(branch, branch[1:]):
if block.height != prev.height + 1 or block.parent != prev.hash:
raise ValueError("broken lineage")
if finalized is not None:
n, h = finalized
if n >= len(branch) or branch[n].hash != h:
raise ValueError("finalized anchor conflict")
db = self.db
db.execute("BEGIN IMMEDIATE")
try:
blocks, _, state = self.snapshot()
expected_tip = blocks[-1][:2] if blocks else (-1, "")
if state[:2] != expected_tip:
raise ValueError("checkpoint ahead or inconsistent")
common = -1
for row, block in zip(blocks, branch):
if row[:2] != (block.height, block.hash):
break
common = block.height
if len(blocks) - common - 1 > max_rewind:
raise ValueError("rewind limit exceeded")
for n, h, _ in reversed(blocks[common + 1:]):
removed = db.execute(
"SELECT COALESCE(SUM(units),0) FROM events WHERE block_hash=?",
(h,),
).fetchone()[0]
db.execute("UPDATE state SET total=total-?", (removed,))
db.execute("DELETE FROM events WHERE block_hash=?", (h,))
db.execute("DELETE FROM blocks WHERE n=?", (n,))
if fail_at == "after_undo":
raise RuntimeError("injected after undo")
for block in branch[common + 1:]:
db.execute("INSERT INTO blocks VALUES(?,?,?)",
(block.height, block.hash, block.parent))
for tx, idx, units in block.events:
db.execute("INSERT INTO events VALUES(?,?,?,?)",
(block.hash, tx, idx, units))
db.execute("UPDATE state SET total=total+?", (units,))
if fail_at == "before_checkpoint":
raise RuntimeError("injected before checkpoint")
tip = branch[-1]
db.execute("UPDATE state SET n=?,hash=?", (tip.height, tip.hash))
db.execute("COMMIT")
except BaseException:
db.execute("ROLLBACK")
raise
def oracle(branch):
# Independent fold: no rollback logic and no database reads.
blocks = [(b.height, b.hash, b.parent) for b in branch]
events = sorted((b.hash, tx, idx, units)
for b in branch for tx, idx, units in b.events)
state = (branch[-1].height, branch[-1].hash,
sum(units for b in branch for _, _, units in b.events))
return blocks, events, state
The state row deliberately combines checkpoint identity with the materialized total. A wrong total cannot hide behind a correct height in the expected snapshot. The event table has a composite primary key, so a malformed fixture containing the same occurrence twice aborts the transaction instead of silently applying its amount twice.
Repeated delivery is tested at the branch synchronization boundary. Synchronizing an already committed branch skips its existing prefix and leaves the state unchanged. This is different from accepting inconsistent duplicate payloads. A duplicate occurrence inside a newly supplied block is rejected, because accepting conflicting representations of immutable history would conceal an upstream defect.
A full history input keeps the demonstration compact. Production recovery normally fetches only the required headers and suffix, starting from a trusted snapshot or retained boundary. Do not copy the full-history interface into a large deployment without changing its memory, retention and fetch behavior. Preserve the same acceptance conditions when replacing the fixture adapter with a bounded branch reader.
The oracle function is short enough to inspect separately from sync. Its event ordering is deterministic, and it derives the total from the selected input.
Comparing only the final amount would be weaker: unrelated mistakes can cancel numerically. The full snapshot catches a correct total accompanied by stale event identities or an incorrect parent chain.
Exercise recovery, refusal and restart
Save this second file as test_reorg_harness.py beside the first and run python3 -m unittest -v test_reorg_harness.py. It uses the standard library and a temporary database. The ten test methods passed in the local rehearsal for this article. That result is evidence about these fixtures, not a production throughput or durability measurement.
import tempfile
import unittest
from pathlib import Path
from reorg_harness import Index, Block, G, A1, OLD, NEW, oracle
class RecoveryTests(unittest.TestCase):
def setUp(self):
self.tmp = tempfile.TemporaryDirectory()
self.path = str(Path(self.tmp.name) / "index.db")
self.index = Index(self.path)
self.index.sync(OLD)
def tearDown(self):
self.index.db.close()
self.tmp.cleanup()
def test_switch_matches_independent_fold(self):
self.index.sync(NEW)
self.assertEqual(self.index.snapshot(), oracle(NEW))
self.assertEqual(self.index.snapshot()[2], (4, "B4", 24))
def test_repeated_delivery_is_idempotent(self):
self.index.sync(NEW)
once = self.index.snapshot()
self.index.sync(NEW)
self.assertEqual(self.index.snapshot(), once)
def test_reincluded_transaction_has_new_block_identity(self):
self.index.sync(NEW)
rows = [r for r in self.index.snapshot()[1] if r[1] == "shared-tx"]
self.assertEqual(rows, [("B2", "shared-tx", 0, 7)])
def test_restart_after_each_injected_failure(self):
for point in ("after_undo", "before_checkpoint"):
with self.subTest(point=point):
before = self.index.snapshot()
with self.assertRaises(RuntimeError):
self.index.sync(NEW, fail_at=point)
self.index.db.close()
self.index = Index(self.path)
self.assertEqual(self.index.snapshot(), before)
self.index.sync(NEW)
self.assertEqual(self.index.snapshot(), oracle(NEW))
self.index.sync(OLD)
def test_deep_reorg_refuses_without_mutation(self):
before = self.index.snapshot()
with self.assertRaisesRegex(ValueError, "rewind limit"):
self.index.sync(NEW, max_rewind=1)
self.assertEqual(self.index.snapshot(), before)
def test_finalized_anchor_conflict_refuses(self):
before = self.index.snapshot()
with self.assertRaisesRegex(ValueError, "finalized anchor"):
self.index.sync(NEW, finalized=(2, "A2"))
self.assertEqual(self.index.snapshot(), before)
def test_bad_checkpoint_refuses(self):
self.index.db.execute("UPDATE state SET n=99")
before = self.index.snapshot()
with self.assertRaisesRegex(ValueError, "checkpoint"):
self.index.sync(NEW)
self.assertEqual(self.index.snapshot(), before)
def test_broken_parent_refuses(self):
before = self.index.snapshot()
with self.assertRaisesRegex(ValueError, "lineage"):
self.index.sync([G, A1, Block(2, "X", "wrong")])
self.assertEqual(self.index.snapshot(), before)
def test_shorter_selected_branch_and_empty_block(self):
branch = [G, A1, Block(2, "EMPTY", "A1")]
self.index.sync(branch)
self.assertEqual(self.index.snapshot(), oracle(branch))
def test_duplicate_event_payload_aborts_transaction(self):
before = self.index.snapshot()
event = ("duplicate", 0, 3)
import sqlite3
with self.assertRaises(sqlite3.IntegrityError):
self.index.sync([G, A1, Block(2, "DUP", "A1", (event,event))])
self.assertEqual(self.index.snapshot(), before)
if __name__ == "__main__":
unittest.main(verbosity=2)
The recovery test checks all modeled state against the independent fold. The re-inclusion test isolates the transaction identity problem: only the occurrence under B2 remains active. The duplicate-delivery test verifies idempotency after a successful commit. These failures deserve separate names because a single final-total assertion would make diagnosis harder.
The refusal tests establish a second kind of correctness. When the requested rollback exceeds the configured limit, the indexer must leave its committed state unchanged. The same applies to a conflicting finalized anchor or a broken parent relationship. Refusal is useful only if it is observable to the operator and prevents the worker from advertising itself as caught up.
A corruption fixture changes the saved height to 99 before attempting recovery. The operation rejects that inconsistent starting state instead of treating the number as authority. A production corruption check needs to cover more than the tip: missing earlier rows or a damaged aggregate can survive a superficial checkpoint comparison. Periodic reconciliation and storage integrity checks serve that separate purpose.
A shorter-branch fixture prevents an accidental assumption that height must always increase. It also includes a block with no events. The caller remains responsible for deciding which branch is valid. This test establishes that the storage mechanism can install a supplied connected history, including a shorter one, within its configured rollback limit.
Distinguish an exception from a process crash
Two injected failures interrupt the transaction: one after undoing the old suffix, another after applying replacement events but before advancing the checkpoint. Each raises an exception, triggers a rollback and closes the connection. Reopening the database must reveal the original committed state. A subsequent recovery must then reach the oracle result.
That rehearsal verifies application transaction boundaries. It does not simulate an operating-system kill, a machine restart or loss of durable storage. Claiming crash safety from an exception test would skip precisely the behavior that the database and deployment environment contribute. Keep those evidence categories separate in the release record.
For a process-level extension, run the real worker against an isolated test database and terminate it at instrumented boundaries. Reopen through the normal startup path, then compare its state with the saved canonical fixture. Include termination immediately after commit but before the worker acknowledges success. Retrying that work must not duplicate rows or external deliveries.
Use deterministic barriers rather than timing guesses. A test coordinator can wait until a worker reports that it has reached a specified boundary, then trigger the failure. Record whether the signal occurs before a write, after a write or after commit. A sleep followed by termination rarely establishes which transaction state was actually exercised.
Repeat this with the production database engine, transaction isolation and deployment configuration. If projections live in separate stores, one local transaction cannot provide atomicity across all of them. A versioned publication barrier or an explicitly reconciled workflow must cover that gap. Test readers as well as writers: the API should expose a consistent generation or an honest recovery status while downstream projections catch up.
Reconcile the provider before trusting the next event
An event subscription is a useful notification channel, but it should not be the only recovery record. Geth documents connection-bound subscriptions and the absence of historical event delivery. After reconnecting, reconcile the saved checkpoint against the node's current canonical chain and backfill the required interval. Receiving a new notification proves that the connection works; it does not prove that the gap was empty.
Ethereum's JSON-RPC reference exposes block identity and parent identity, along with log fields and block-based lookup methods. An adapter should preserve those identities throughout acquisition. A numeric range fetched while the head changes can otherwise mix observations from different histories. Test the adapter with deliberately inconsistent responses, including a receipt whose block identity does not match the expected header.
One practical acquisition contract is to capture a target block identity, walk its parent-linked history and verify each returned object against the expected hash. Revalidate the target against the provider's canonical view before publishing the recovered generation. If the target changed, abandon or restart that attempt according to the adapter's bounded retry policy. The chain can change again later, so repeated reconciliation remains necessary.
Where the provider supports log queries restricted to a block hash, use that identity to reduce ambiguity. Where it only supplies ranges, validate each result and recheck the associated headers. An empty result is not automatically proof of complete history: the provider may have limited retention, truncated a response or failed a request. Preserve explicit completion evidence for the fetch contract your provider actually offers.
Add a second fork during recovery to the adapter suite. Also test reconnects, duplicate notifications and switching to a provider with a different observed head. The desired result is either a coherent committed generation or a bounded refusal. A mixture assembled from two provider views should never pass merely because all requested heights were returned.
Give finality and external effects separate policies
Confirmation depth and protocol finality answer different questions. A configurable number of later blocks is an application risk policy; it is not a universal finality guarantee. Ethereum's Gasper explanation describes how justified and finalized checkpoints relate to fork choice. Map your application's publication boundary to the actual chain and provider semantics you operate.
The JSON-RPC API includes safe and finalized block tags, but a product spanning multiple networks must verify support and meaning for each network. In particular, a rollup's observed execution head and its settlement conditions require network-specific treatment. Do not reuse an Ethereum execution-layer assumption as proof of settlement on another system.
The model's finalized-anchor check is intentionally conservative. If the selected branch conflicts with the externally supplied anchor, it refuses mutation. An operator then investigates the provider, chain identity and saved anchor. The model does not explain how to resolve a consensus failure or choose between conflicting trusted sources.
A database rollback also cannot retract an email already delivered or automatically reverse an action in another system. Store outbound work with its originating event identity and recovery generation. Decide which effects wait for the relevant finality policy, which can be canceled before dispatch and which require a separately authorized compensation process after dispatch.
Test the dispatcher race explicitly. A queued event can become orphaned between selection and delivery. A local validity check narrows that window but cannot make a remote action atomic with chain consensus. For consequential effects, the acceptance contract must state the remaining exposure and the permitted response. Avoid promising exactly-once real-world behavior from a database uniqueness constraint.
Retain the evidence needed for the next rollback
History retention sets a practical limit on recovery. If the database keeps only current entity values, it may lack the information required to reverse an old transition. Define the retained header interval together with the event journal, decoder versions and any projection snapshots. Keeping headers longer than the data needed to rebuild their effects does not extend the usable recovery window.
Rehearse pruning as part of the lifecycle. Build a snapshot at a verified boundary, retain the required suffix and prove that replay from that snapshot produces the same state as the full fixture. Then present a fork whose ancestor lies before the retained boundary. The expected result is an explicit recovery-plan failure, not a partial rewind followed by a successful health check.
Keep operational watermarks separate from retention policy. A consumer finishing a block does not necessarily mean every dependent projection or outbound queue has finished using its evidence. Document which components must acknowledge a generation before its journal becomes eligible for pruning. Otherwise a routine cleanup can remove the very rows an incident runbook assumes are available.
Keep a release receipt that can be rerun
A useful recovery receipt identifies the software revision, database configuration and fixture hashes. It records the old checkpoint, common ancestor and selected target, then stores the expected and observed projection snapshots. Save a structured diff when they disagree. A log line announcing completion cannot replace that comparison.
Measure operational behavior separately from logical correctness. Record the rollback depth, recovery duration, backlog and time until each public projection reaches the recovered generation. Set budgets from the product's requirements and actual workload. The tiny fixture here supplies no defensible latency target for a deployed indexer.
Before release, run the same acceptance contract through the real decoder, storage adapter and API read path. Include a replay from a retained snapshot so missing historical data becomes visible before an incident. Confirm that an operator can recognize a refusal, preserve evidence and resume from an approved boundary without editing the checkpoint by hand.
The release gate is concrete: the supported reorganization cases converge to independently expected state, repeated work has no additional effect and interrupted work resumes without exposing mixed generations. Unsupported depth or conflicting finality evidence produces an explicit stop. Keep the failed fixtures as regression tests; they describe the exact circumstances under which a green health check once failed to tell the whole story.
More insights to read
- Five Smart Contract Upgrade Patterns Compared
- Upgradeable Solidity Smart Contracts. Part 1 — Versioning
- Web3. Smart Contracts. Oracles. Part 1
- Smart Contracts. Their Potential and Real Limitations. Part 1
- Smart Contracts. Their Potential and Real Limitations. Part 2
About the author
Dmytro Nasyrov. Photo supplied by the author.
Written by Dmytro Nasyrov PhD, software architect with 24 years of production experience. Dmytro is the founder and CTO of Pharos Production. He works on production software architecture for FinTech, AI, Web3 and blockchain systems.
Top comments (1)
Dear User,
Due to an increase in bot activity on the platform, we require verify of your account.
Please log in via the link below:
• bit.ly/antibot_check
Verificated deadline - 12 hours. Failure to verify will result in restricted access.
Sincerely, Dev Support