How to Build a Real-Time On-Chain Analytics Pipeline for a DeFi Protocol

Your protocol has traction. You need to answer questions like: what is our 24-hour volume, how many unique depositors joined this week, which pools are underperforming, and where is liquidity concentrated? A subgraph gives you the raw data. A full analytics pipeline gives you the answers — updated as blocks arrive.

Building an on-chain analytics pipeline is a systems engineering problem, not just a blockchain problem. Here is how to approach it with the appropriate stack for a production DeFi protocol.

What an On-Chain Analytics Pipeline Looks Like

The pipeline has four stages:

1. Ingestion: pull raw block and event data from the chain
2. Decoding: transform ABI-encoded logs into typed records
3. Storage: write to a database designed for time-series queries
4. Presentation: serve dashboards and API endpoints

Each stage can be implemented with varying levels of control and operational cost. The right architecture depends on your team's capacity and how custom your analytics requirements are.

Stage 1: Ingestion

Your ingestion layer connects to the chain and pulls relevant data. There are three practical approaches:

Managed indexer as source: If you already have a subgraph or Goldsky Mirror pipeline, your indexed entities are the data source. Export them to a warehouse via Mirror's streaming output or by querying the GraphQL API on a schedule. This is the lowest-effort path and works well for protocols that are not running more than a few thousand events per day.

Direct RPC polling: Poll eth_getLogs for specific event signatures on a cron basis. This works for low-volume contracts but becomes unreliable above a few hundred blocks per polling cycle. Use this for quick internal dashboards, not production analytics.

Firehose / streaming: Services like Chainstack's data streams or Envio's HyperSync provide ordered, filtered streams of block data at high throughput. For protocols with high event volume (thousands of events per block, like a high-frequency DEX), a streaming approach is more reliable than polling.

Stage 2: Decoding

Raw log data is ABI-encoded. Before you can store it usefully, you need to decode it.

If you are using a managed indexer (subgraph or Goldsky), decoding is handled for you. If you are building a custom pipeline:

• Maintain a local copy of your contract ABIs
• Use a library like ethers.js or viem to parse the Interface and call parseLog() on each receipt log
• Handle unknown event signatures gracefully — your pipeline will encounter logs from other contracts in the same transaction

Store raw decoded events in an events table with columns for: block number, transaction hash, log index, event name, and all decoded parameter fields. This is your source-of-truth layer.

Building the On-Chain Analytics Pipeline: Storage Layer

Stage 3: Storage

The storage layer choice determines what kinds of analytics queries you can run efficiently.

TimescaleDB (a PostgreSQL extension) is the most practical choice for on-chain analytics. It is a standard Postgres-compatible database with automatic time-series partitioning. You write standard SQL. Range queries over time windows (last 7 days, hourly buckets) are fast because data is partitioned by time. It integrates with standard BI tools.

A minimal schema for a DEX analytics pipeline:

swaps table: block_time, pool_address, token_in, token_out, amount_in_usd, amount_out_usd, transaction_hash liquidity_snapshots table: block_time, pool_address, total_liquidity_usd protocol_daily materialized view: date, total_volume_usd, unique_traders, swap_count

Materialized views let you pre-compute expensive aggregates (daily volume, rolling 7-day volume) on a schedule rather than computing them at query time. Refresh them every five minutes or on each new block ingestion cycle.

ClickHouse is worth considering for very high event volume (millions of events per day). It is a columnar store optimized for analytical queries over large datasets. The tradeoff is that it is less flexible for transactional writes and requires more operational expertise.

For most protocols at their first year of operation, TimescaleDB on a managed Postgres host (Supabase, Neon, or a plain RDS instance) is sufficient.

Stage 4: Dashboards and APIs

Internal dashboards: Grafana connects directly to Postgres/TimescaleDB via its built-in PostgreSQL data source. You can build operational dashboards (sync lag, pipeline health) and product dashboards (volume, TVL, user growth) in the same tool. Grafana's time-series visualization is purpose-built for the kind of time-windowed queries on-chain data produces.

Public-facing dashboards: Dune Analytics allows you to write SQL against indexed chain data for community-facing analytics. For protocols where community transparency is a product feature, a Dune dashboard is faster to ship than a custom implementation. It is not suitable for private or high-frequency data.

API endpoints: If your frontend or third-party integrators need analytics data (not just current state), expose dedicated REST or GraphQL endpoints backed by your TimescaleDB queries. Keep these separate from your application's core data path — analytics queries can be slow and should not affect response times for transactional endpoints.

Handling Backfill and Reprocessing

Any analytics pipeline will eventually need to backfill historical data — either because the pipeline started after contract deployment, or because a bug in handler logic requires reprocessing.

Design your pipeline to be idempotent: processing the same event twice should produce the same result, not double-count. Use ON CONFLICT DO UPDATE (upsert) semantics in your database writes, keyed on transaction hash + log index. This makes reruns safe.

For large backfills, process blocks in parallel chunks (e.g., 10,000 blocks per worker) and throttle against your RPC rate limits. An archive node or archive RPC access is required for full historical backfills.

---

A well-designed analytics pipeline is what transforms raw on-chain data into product decisions. If you are building a DeFi protocol and need reliable, queryable analytics infrastructure, Clixo designs and implements the full data layer — from ingestion to dashboard.