Webhook icon
Schedule icon
If icon
Fail icon
Queries icon
Query icon
Request icon
LoopUntil icon
SlackIncomingWebhook icon

Index ERC-20 Transfers into Postgres and Survive Chain Reorganizations

Index ERC-20 transfers into Postgres in atomic batches, detect chain reorgs and repair the data automatically.

Categories
BusinessData

Teams that need token transfers in their own database (deposit crediting, accounting, analytics, compliance) usually write a loop around eth_getLogs. It works until the chain reorganizes. Blocks near the head get replaced, the transfers in them disappear or change, and the database keeps rows that never happened on the final chain. Balances drift and nobody notices. This blueprint indexes close to the head and repairs reorgs itself. Every batch is written atomically with its cursor, so a crash never loses or duplicates data.

How it works

  1. every_minute (Schedule) or new_block (Webhook, for new-block notifications) starts a run; concurrency cancels overlapping runs.
  2. stored_window and chain_state fetch, in one JSON-RPC batch, the chain id, the head, and the current hash of each of the last reorg_window blocks this indexer stored.
  3. repair (Postgres) finds the lowest stored block whose hash changed, deletes the transfers and stored hashes from there, and moves the cursor back below it, all in one statement. Each repair is logged in indexer_reorgs.
  4. deep_check (If) handles reorgs deeper than the window, when even the oldest stored hash changed. Every stored transfer carries its block hash, so the flow re-verifies transfers up to deep_reorg_depth blocks back against the chain and rewinds to the real fork point.
  5. catch_up (LoopUntil) indexes batch after batch until the cursor reaches the head seen at the start:
    • plan builds the next eth_getLogs range and the header requests for its last reorg_window blocks;
    • fetch sends them in one batch;
    • store inserts the transfers (256-bit amounts kept exactly in NUMERIC(78,0)), records the hashes, prunes old ones, and moves the cursor, in one statement.
  6. report_reorg posts every repair: fork block, depth, transfers removed.

Error handling

  • Atomic batches. Transfers, block hashes and the cursor move together in one statement. A failure at any point leaves the cursor where it was, and the next run resumes there; inserts are idempotent on (chain_id, tx_hash, log_index).
  • A reorg during a fetch is caught. If a log's block hash differs from the header fetched in the same batch, nothing is stored and the loop stops; the next run repairs from the stored hashes.
  • JSON-RPC errors arrive with HTTP 200. rpc_ok and fetch_ok fail with the provider's message, for example a range or result-size limit, which tells you to lower batch_blocks. "The cursor did not move."
  • Clear failures. Malformed token addresses fail before any RPC call, RPC calls retry with backoff, and the errors alert names the task and cause.

What this blueprint teaches about Kestra

  • LoopUntil as a catch-up loop with a SQL planner, so a backlog of thousands of blocks is indexed in one run.
  • Letting Postgres build JSON-RPC requests (jsonb_build_object, to_hex), so Pebble only passes them along.
  • Exactly-once ingestion with data-modifying CTEs: insert, prune and cursor move in one atomic statement.
  • Self-healing state: the flow verifies its own earlier output against the source before adding more.

Prerequisites

  • An EVM JSON-RPC endpoint that serves eth_getLogs for the range you index.
  • A Postgres database (four tables and one helper function are created on the first run).
  • A Slack incoming webhook.

Secrets

  • EVM_RPC_URL: JSON-RPC endpoint.
  • POSTGRES_URL, POSTGRES_USER, POSTGRES_PASSWORD: index database.
  • SLACK_WEBHOOK_URL: Slack incoming webhook.

Inputs

  • tokens (ARRAY): token contracts to index. The default is USDC on Ethereum mainnet.
  • start_block (INT): where a new index starts.
  • batch_blocks (INT, default 500): blocks per eth_getLogs.
  • reorg_window (INT, default 64), deep_reorg_depth (INT, default 1000): how far back to check.
  • confirmations (INT, default 0): stay this far behind the head.

Outputs

  • progress (JSON): last indexed block and number of transfers stored.
  • reorg (JSON): fork block, depth and transfers removed when this run repaired a reorg.

Quick start

  1. Add the secrets, set tokens and a recent start_block, and run the flow: it indexes up to the head in batches.
  2. Query token_transfers, for example for net balances: sum(CASE WHEN to_address = $1 THEN value ELSE -value END).
  3. Point a new-block webhook at new_block for lower latency, and change its key.
  4. Use a copy of the flow per chain; tables are keyed by chain id and token set.

Plugins used

See How

New to Kestra?

Use blueprints to kickstart your first workflows.