indexer-reorg-idempotent

Make an indexer idempotent by using a unique event identity key such as (txHash, logIndex) and writing with upserts instead of plain inserts. Replaying the same events twice must produce identical database state, which you verify with a replay test.

Handle reorgs by reverting all projections derived from orphaned blocks, either through framework-managed rollback combined with idempotent upserts or through explicit confirmed-depth tables. Never assume finality at the head block.

Use (txHash, logIndex) as the primary event identity key since it uniquely identifies every log. Fall back to (blockNumber, logIndex) only when the transaction hash is unavailable, which is rare.

Duplicates appear when handlers use INSERT without unique constraints, so replayed events are written again. Add a unique constraint on the event identity key and switch to upserts so a unique violation never crashes the indexer.

Separate tiers when your UI needs fast feedback but final results must be stable. Serve optimistic data for UX that may change on reorg, and only use confirmed data after N confirmations for final rankings and payout history.