L1-17: Indexer #

1. Problem #

Build your own on-chain data indexer from scratch -- without using The Graph or Dune -- by directly listening to contract events, storing them in a local database, and providing a REST API for frontend queries. Goal: index ERC-20 Transfer events and support querying transfer records by address and token contract.

2. Why #

The Graph's hosted service may experience downtime or delays; a self-built indexer provides full control:

• Flexibility: implement custom logic that The Graph does not support (complex aggregations, cross-contract relationships)
• Real-time: directly listen to WebSocket event pushes with lower latency than The Graph's polling model
• Understand the underlying principles: building your own indexer lets you understand what The Graph is doing -- listen to events -> parse -> store -> query
• Data ownership: indexed data is stored in your own database, unaffected by third-party service availability

This is also a key skill for backend engineers transitioning to web3.

3. Solution #

Architecture Design #

Ethereum node (WebSocket)
      │
      ▼
watchEvent (viem) ──→ parse Event parameters
      │
      ▼
SQLite database ──→ store Transfer records
      │
      ▼
Express REST API ──→ /api/transfers, /api/balance/:address

Database Schema #

CREATE TABLE IF NOT EXISTS transfers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  tx_hash TEXT NOT NULL,
  block_number INTEGER NOT NULL,
  log_index INTEGER NOT NULL,
  from_address TEXT NOT NULL,
  to_address TEXT NOT NULL,
  token_address TEXT NOT NULL,
  amount TEXT NOT NULL,
  timestamp INTEGER NOT NULL,
  UNIQUE(tx_hash, log_index)
);

CREATE TABLE IF NOT EXISTS indexer_state (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL
);

Core Indexing Logic #

async function processEvents(fromBlock, toBlock) {
  for (const tokenAddress of WATCHED_TOKENS) {
    const logs = await client.getLogs({
      address: tokenAddress,
      event: ERC20_TRANSFER_EVENT,
      fromBlock: BigInt(fromBlock),
      toBlock: BigInt(toBlock),
    });

    const insert = db.prepare(`
      INSERT OR IGNORE INTO transfers
      (tx_hash, block_number, log_index, from_address, to_address,
       token_address, amount, timestamp)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `);

    const insertMany = db.transaction((logs) => {
      for (const log of logs) {
        insert.run(
          log.transactionHash,
          Number(log.blockNumber),
          log.logIndex,
          log.args.from.toLowerCase(),
          log.args.to.toLowerCase(),
          log.address.toLowerCase(),
          log.args.value.toString(),
          Math.floor(Date.now() / 1000),
        );
      }
    });
    insertMany(logs);
  }
}

Reorg Handling #

async function handleReorgs() {
  const lastBlock = await getLastIndexedBlock();
  const safeBlock = Math.max(0, lastBlock - 12);
  db.prepare('DELETE FROM transfers WHERE block_number > ?').run(safeBlock);
  await updateLastIndexedBlock(safeBlock);
}

REST API #

• GET /api/transfers?address=0x...&limit=100 -- query transfer records by address
• GET /api/transfers?token=0x...&limit=100 -- query by token contract
• GET /api/balance/:address -- query the indexed balance of an address

4. Pitfalls Encountered #

4.1 WebSocket Disconnection and Reconnection #

The WebSocket connection to an Ethereum node may drop due to network issues. Without automatic reconnection and resumption from the last checkpoint, the indexer will miss all events that occurred during the disconnection period.

4.2 Chain Reorganization (Reorg) #

Ethereum occasionally experiences chain reorganizations -- previously confirmed blocks are rolled back. Events stored in the indexer for those blocks also become invalid. The most conservative strategy is to only index data that is at least 12 blocks behind the latest block ("finalized" status).

4.3 Deduplication #

The same event may be indexed multiple times due to retries or reorg handling. A UNIQUE(tx_hash, log_index) constraint combined with INSERT OR IGNORE is the simplest deduplication strategy.

4.4 JavaScript Precision Issues with Large Numbers #

ERC-20 amount can be arbitrarily large (e.g., 10^18 wei). JavaScript's Number type cannot represent these precisely. All amount fields should be stored as strings (TEXT).

5. Why the Pitfalls Happen #

5.1 #

WebSocket differs from HTTP -- it is a long-lived connection, and network jitter can cause disconnections. viem's watchEvent attempts reconnection internally, but in a custom indexer, reconnection logic should be handled explicitly.

5.2 #

Ethereum's consensus mechanism allows reorganizations of up to approximately 64 blocks. The conservative strategy is to never index data from the latest 12 blocks, ensuring all indexed data has reached the "safe head."

5.3 #

If the event processing crashes (e.g., a database write failure), already-processed events may be partially written. Use transactions (db.transaction) to ensure atomicity.

6. How to Resolve the Pitfalls #

• Use better-sqlite3 in WAL mode (faster writes, supports concurrent reads)
• Record the last indexed block number in the indexer_state table and resume from there after a restart
• Before each indexing run, clean up data from the last 12 blocks (conservative reorg protection)
• Store all numeric fields as TEXT (to avoid precision loss)
• Wrap batch writes with db.transaction() to ensure atomicity

7. Technical Highlights #