Idempotent Indexing: Building Reorg-Safe Data Pipelines.

1. The Reality of Reorganizations

A chain reorganization (reorg) occurs when two miners find a block at the same time, splitting the network. Eventually, one chain becomes longer, and nodes on the shorter chain must discard their recent blocks and adopt the longer one.

If your block explorer backend has already saved the transactions from the discarded (orphaned) block, you now have fake balances, fake transactions, and broken foreign keys. You must design your system to roll back flawlessly.

2. The Idempotent Schema Design

"Idempotence" means that applying the same operation multiple times yields the same result as applying it once. When your Go worker parses a block and inserts it into PostgreSQL, it should never crash if the data is already there.

// We use standard UPSERT mechanics in Postgres to achieve this.

INSERT INTO transactions (txid, block_hash, amount, time) VALUES ($1, $2, $3, $4) ON CONFLICT (txid) DO UPDATE SET block_hash = EXCLUDED.block_hash, time = EXCLUDED.time;

Notice that we UPDATE the `block_hash` on conflict. Why? Because during a reorg, a transaction might be removed from an orphaned block and immediately included in the next valid block. Its `txid` stays the same, but its `block_hash` changes. Idempotent upserts handle this shift automatically.

3. The Ingestion Loop Architecture in Go

A naive ingestion loop just requests `getBlock(height+1)`. A robust ingestion loop verifies the cryptographic link between blocks.

// Go pseudo-code for the master synchronization loop func syncNextBlock(db *Database, rpc *RPCClient) error { latestSavedBlock := db.GetHighestBlock() // Fetch the block at height + 1 nextBlockData := rpc.GetBlockByHeight(latestSavedBlock.Height + 1) // THE CRITICAL CHECK if nextBlockData.PreviousHash != latestSavedBlock.Hash { // A reorg has occurred! The chain split happened at or before latestSavedBlock. log.Warn("Reorg detected at height", latestSavedBlock.Height) return handleReorg(db, rpc, latestSavedBlock) } // Safe to process return processBlock(db, nextBlockData) }

4. Executing the Rollback

When `handleReorg` is triggered, you must prune the database backwards until you find the common ancestor between your database and the node's current reality.

1. Delete all UTXOs (Unspent Transaction Outputs) created by the orphaned block.
2. Restore any UTXOs that were "spent" by the orphaned block (flip their status back to unspent).
3. Delete the transactions associated with the orphaned block.
4. Delete the orphaned block record itself.

// This MUST be wrapped in a single SQL Transaction. If the rollback fails halfway, your database is permanently out of sync.

BEGIN; DELETE FROM utxos WHERE block_hash = 'orphan_hash'; UPDATE utxos SET spent = false WHERE spent_in_block = 'orphan_hash'; DELETE FROM transactions WHERE block_hash = 'orphan_hash'; DELETE FROM blocks WHERE hash = 'orphan_hash'; COMMIT;

5. Satoshi-Precision and Type Safety

Never use `float64` or `DECIMAL` types for crypto amounts in Go. You will encounter floating-point inaccuracies that cause balances to be off by fractions of a satoshi.

Always process amounts using Go's `uint64` (for Bitcoin-like chains) or the `math/big.Int` package (for Ethereum-like chains). In PostgreSQL, store these values as `BIGINT` or `NUMERIC(78, 0)`.

6. Conclusion

Reorg-safe indexing is the foundation of enterprise-grade blockchain infrastructure. By combining UPSERT logic, cryptographic parent-child verification, and atomic SQL rollbacks, your data pipeline will survive deep chain reorganizations and maintain 100% data integrity without human intervention.