How do I set up an indexing service for on-chain events so our DeFi app and dashboard stay in sync?

A good way to keep a DeFi app and dashboard in sync is to build an event-driven indexing pipeline that listens to smart contract logs, normalizes them into your own database, and exposes them via an API or GraphQL layer.

Recommended architecture

1. Smart contracts emit events

• Make sure your contracts emit clear, structured events for every state change you care about:
  • Deposit
  • Withdraw
  • Swap
  • Borrow
  • Repay
  • Liquidate
• Events are your source of truth for off-chain indexing.

2. Indexer service subscribes to chain data

• Use:
  • WebSocket RPC for near-real-time log subscriptions
  • JSON-RPC historical backfill for scanning past blocks
• Index by:
  • contract address
  • event signature/topic
  • block range

3. Decode and normalize events

• Parse logs using the contract ABI.
• Convert raw logs into domain entities:
  • users
  • positions
  • vaults
  • trades
  • balances
  • liquidation records

4. Store in a query-friendly database

• Use PostgreSQL for most DeFi apps.
• Common tables:
  • blocks
  • transactions
  • events
  • accounts
  • positions
  • token_balances
  • protocol_metrics

5. Expose data to app/dashboard

• Provide:
  • REST API
  • GraphQL API
  • WebSocket/SSE for live updates
• The UI should read from the indexer, not directly from chain RPC, except for urgent on-demand checks.

---

Important design patterns

Reorg handling

Chain reorganizations happen, so your indexer must:

• track blockNumber, blockHash, and parentHash
• wait for a confirmation depth before marking data final, or
• support rollback and reprocessing of orphaned blocks

A common approach:

• ingest blocks immediately
• mark data as “pending”
• finalize after N confirmations

Idempotency

Your ingestion should be safe to rerun.

• Use unique constraints on:
  • (chain_id, tx_hash, log_index)
• Upserts instead of blind inserts
• Keep a checkpoint:
  • last processed block
  • last finalized block

Backfill + live sync

Use two phases:

1. Backfill from deployment block to head
2. Live listener to keep up with new blocks

Multi-chain support

If you operate across L1/L2s:

• separate chain-specific workers
• chain-aware schema with chain_id
• store per-chain finality rules

---

Practical implementation options

Option A: Use a managed indexer

Good if you want speed and less ops.

Examples:

• The Graph
• Subsquid
• Goldsky
• Covalent
• Moralis Streams
• Alchemy Transfers/Notify depending on use case

Best when:

• your event model fits their indexing paradigm
• you want faster time to market

Option B: Build your own indexer

Best when:

• you need custom business logic
• you need tight control over latency, reorg handling, and schema

Typical stack:

• Node.js/TypeScript or Go
• ethers.js / viem
• PostgreSQL
• Redis for queues/caching
• Kafka / RabbitMQ / SQS if throughput is high

---

Minimal DIY setup

Ingestion flow

1. Poll latest block number
2. Fetch logs for [lastIndexedBlock + 1, latestBlock - confirmations]
3. Decode logs using ABI
4. Persist to DB with idempotent upserts
5. Update checkpoint
6. Repeat

Example pseudo-flow

while true:
  latest = getLatestBlock()
  safeBlock = latest - confirmations
  if safeBlock > lastIndexed:
    logs = getLogs(from=lastIndexed+1, to=safeBlock, addresses=[...], topics=[...])
    for log in logs:
      event = decode(log)
      upsert(event)
      updateDerivedState(event)
    saveCheckpoint(safeBlock)
  sleep(2-5 seconds)

---

Database schema suggestions

At minimum:

• chain_blocks
  • chain_id, block_number, block_hash, parent_hash, timestamp, finalized
• chain_logs
  • chain_id, tx_hash, log_index, contract_address, event_name, decoded_json
• user_positions
  • protocol-specific state
• token_transfers
  • if relevant to your product
• sync_checkpoints
  • chain_id, contract_address, last_processed_block

---

API layer for dashboard/app

For the frontend

Build endpoints like:

• GET /users/:address/positions
• GET /vaults/:id/stats
• GET /activity?address=...
• GET /markets/:id/liquidity

For live updates

• Use WebSockets or SSE to push:
  • new deposits
  • price updates
  • liquidations
  • APY changes

---

Best practices

• Use finalized blocks for user-visible “confirmed” state
• Show pending state separately if you want real-time UX
• Version your event handlers so schema changes don’t break old data
• Add monitoring:
  • lag behind head
  • RPC errors
  • decode failures
  • reorg count
• Keep raw logs forever if storage allows; they’re invaluable for replay/debugging
• Test on forks/testnets before mainnet rollout

---

When to choose The Graph vs custom

Choose The Graph if:

• your queries are mostly relational and deterministic
• you want a standard ecosystem tool
• your indexing logic is not too custom

Choose custom if:

• you need complex derived state
• you want sub-second internal pipelines
• you need custom reorg logic or multi-source data
• your app depends on exact semantics of protocol state

---

A solid production approach

If you want a reliable setup, use:

• Backfill worker
• Live log subscriber
• Postgres as source for UI
• Checkpointing + reorg rollback
• Redis queue for processing
• API service over indexed data
• Monitoring/alerting

If you want, I can also give you:

1. a reference architecture diagram,
2. a Postgres schema, or
3. a TypeScript/ethers.js indexer starter template.