Index Blockchain Data Efficiently

This skill builds high-performance, scalable systems for extracting, transforming, and querying blockchain data. Core principles are ETL pipelines robust to chain reorganizations and missing data, event-driven processing using blockchain events and logs as the primary data source, incremental processing of only new blocks, and idempotent indexing operations that tolerate retries and reprocessing. It provides a block-processor interface with an Ethereum implementation that indexes each block's transactions inside a database transaction and handles reorgs by rolling back and re-indexing from the affected block.

Event processing covers indexing smart-contract events (ERC-20 Transfer and Approval as the worked example) by mapping event topic signatures to handlers, decoding the log data, and inserting structured records. Database schema guidance provides an optimized relational schema for blocks, transactions, and token transfers with indexes on the columns actually queried (contract address, from/to address, block number). Real-time synchronization uses a WebSocket subscription to new block headers, detecting reorgs by comparing the stored block hash against the newly received header. Performance optimization covers batch processing with staggered concurrent requests (to avoid rate limiting) and bulk database inserts that ignore conflicts on duplicate keys. Query and API design provides a GraphQL schema exposing blocks, transactions, and token transfers as typed, filterable resources.

async getHealthStatus(): Promise<HealthStatus> {
  const latestBlock = await this.web3.eth.getBlockNumber();
  const indexedBlock = await this.getLastProcessedBlock();
  const lag = latestBlock - indexedBlock;
  
  return {
    status: lag > 100 ? 'unhealthy' : 'healthy',
    latestBlock,
    indexedBlock,
    lag,
    isRealTimeSync: lag < 5
  };
}

Use this skill when building or hardening a blockchain indexer - handling block processing and reorgs, indexing smart-contract events, designing a query-optimized database schema, adding real-time WebSocket sync, or setting up health monitoring and metrics.

It is not a fit for smart contract development or on-chain logic itself - the guidance is scoped to off-chain extraction, transformation, and querying of already-emitted blockchain data, not to writing or auditing the contracts producing it.

Inputs are an RPC/WebSocket provider for the target blockchain network and the contract events or transaction types you need indexed. The schema output is specific about data types, not just table names: block and transaction hashes are stored as fixed-length CHAR(66)/CHAR(42) fields, token values as DECIMAL(78,0) to hold uint256 precision without rounding, and a unique constraint on (transaction_hash, log_index) prevents the same event from being indexed twice. The GraphQL output supports first/skip pagination and filtering token transfers by contract address or from/to address directly in the query.

Backend engineers building blockchain data infrastructure who need concrete, production-ready patterns for reorg-safe indexing, event decoding, and real-time sync - following the seven named best practices: connection pooling, circuit breakers for RPC calls, data validation before insertion, tested backup and recovery procedures, RPC rate-limit respect with exponential backoff, comprehensive lag and error-rate monitoring, and graceful shutdown to avoid data corruption on restart.