Analytics & Dashboards

Turn indexed Solana data into product and business insights with materialized views, metrics layers, and dashboard tools.

Recipe

Quick-reference recipe card - copy-paste ready.

CREATE MATERIALIZED VIEW daily_volume AS
SELECT date_trunc('day', ts) AS day, SUM(amount_usd) AS volume
FROM swaps
GROUP BY 1;
 
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_volume;

When to reach for this:

Executive dashboards for TVL, volume, and active users.
Operator monitoring of program usage spikes.
Growth experiments measuring conversion after on-chain actions.
Fraud/risk dashboards on abnormal flow patterns.

Working Example

-- Core metrics tables populated by indexer
SELECT
  program_id,
  COUNT(DISTINCT user_wallet) AS dau,
  SUM(fee_lamports) / 1e9 AS fees_sol
FROM events
WHERE slot >= (SELECT max(slot) - 216000 FROM events) -- ~24h rough window
GROUP BY program_id;

Connect Metabase, Grafana, or Hex to read-only replica:

# Grafana postgres datasource -> dashboard panels on daily_volume

What this demonstrates:

Aggregations run in warehouse/Postgres, not in mobile apps via RPC.
Slot or block_time columns enable time-window metrics.
Materialized views refresh on schedule for fast dashboard loads.

Deep Dive

How It Works

Indexer normalizes chain events into fact tables (swaps, mints, votes).
Dimensions tables hold mint decimals, symbols, and program metadata.
BI tools query replica DB with read-only credentials.
Metric definitions documented to prevent "two truths" across teams.

Metric Examples

Metric	Definition	Pitfall
DAU	Distinct signers per day	Includes bots
Volume	Sum trade notional USD	Needs price oracle at time
Retention	Wallets active week N	Cohort key is wallet
Revenue	Protocol fees in SOL	Decimal conversion

bash Notes

# Nightly refresh job (cron)
psql $DATABASE_URL -c "REFRESH MATERIALIZED VIEW CONCURRENTLY daily_volume;"

Gotchas

Querying chain via RPC for charts - slow and rate limited. Fix: index first, dashboard second.
USD volume without historical price - misleading charts. Fix: store oracle price at block time.
Bot traffic inflates DAU - vanity metrics. Fix: heuristics filters or labeled cohorts.
Realtime expectations on batch ETL - dashboards lag minutes. Fix: SLAs on refresh job monitoring.
PII in wallet dashboards - privacy policies. Fix: aggregate public keys only; governance on exports.

Alternatives

Alternative	Use When	Don't Use When
Dune/Flipside community	Public protocol stats	Private unreleased program
ClickHouse real-time	Billions of rows	Small MVP
Notebook SQL	Ad-hoc research	Customer-facing dashboards
Provider analytics UI	Quick launch	Custom metric definitions

FAQs

Postgres vs warehouse?

Start Postgres; migrate to BigQuery/ClickHouse when queries exceed minute-scale on replica.

How fresh should data be?

Product-dependent - 1-5 min lag common for batch refresh; sub-minute needs stream aggregations.

Can @solana/kit power dashboards?

No - dashboards read your database API, not JSON-RPC directly.

What to monitor operationally?

Indexer lag slots, refresh duration, row count growth, API p95 latency.

How to define TVL?

Document token price source and which accounts count as locked - ambiguous across protocols.

Compressed NFT metrics?

Use DAS-indexed tables or provider exports for cNFT mint counts.

Access control?

Read-only DB roles per dashboard; no production write creds in BI tools.

Testing metric SQL?

Snapshot tests on fixed fixture dataset with expected aggregates.

Seasonality spikes?

Pre-aggregate before mint events; scale refresh frequency temporarily.

Link to indexer build?

See Building an Indexer for upstream pipeline.

Building an Indexer - data source
Indexing Basics - why not RPC
RPC Best Practices - avoid RPC analytics
Indexing Best Practices - pipeline hygiene

Stack versions: This page was written for Agave 4.1.1, Solana CLI 3.0.10, Anchor 0.32.1, anchor-lang 0.32.1, Rust 1.91.1, @solana/kit 7.0.0, Surfpool 0.12.0, and LiteSVM 0.6.x.

Recipe

Quick-reference recipe card - copy-paste ready.

CREATE MATERIALIZED VIEW daily_volume AS
SELECT date_trunc('day', ts) AS day, SUM(amount_usd) AS volume
FROM swaps
GROUP BY 1;
 
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_volume;

When to reach for this:

Executive dashboards for TVL, volume, and active users.
Operator monitoring of program usage spikes.
Growth experiments measuring conversion after on-chain actions.
Fraud/risk dashboards on abnormal flow patterns.

Working Example

-- Core metrics tables populated by indexer
SELECT
  program_id,
  COUNT(DISTINCT user_wallet) AS dau,
  SUM(fee_lamports) / 1e9 AS fees_sol
FROM events
WHERE slot >= (SELECT max(slot) - 216000 FROM events) -- ~24h rough window
GROUP BY program_id;

Connect Metabase, Grafana, or Hex to read-only replica:

# Grafana postgres datasource -> dashboard panels on daily_volume

What this demonstrates:

Aggregations run in warehouse/Postgres, not in mobile apps via RPC.
Slot or block_time columns enable time-window metrics.
Materialized views refresh on schedule for fast dashboard loads.

Deep Dive

How It Works

Indexer normalizes chain events into fact tables (swaps, mints, votes).
Dimensions tables hold mint decimals, symbols, and program metadata.
BI tools query replica DB with read-only credentials.
Metric definitions documented to prevent "two truths" across teams.

Metric Examples

Metric	Definition	Pitfall
DAU	Distinct signers per day	Includes bots
Volume	Sum trade notional USD	Needs price oracle at time
Retention	Wallets active week N	Cohort key is wallet
Revenue	Protocol fees in SOL	Decimal conversion

bash Notes

# Nightly refresh job (cron)
psql $DATABASE_URL -c "REFRESH MATERIALIZED VIEW CONCURRENTLY daily_volume;"

Gotchas

Querying chain via RPC for charts - slow and rate limited. Fix: index first, dashboard second.
USD volume without historical price - misleading charts. Fix: store oracle price at block time.
Bot traffic inflates DAU - vanity metrics. Fix: heuristics filters or labeled cohorts.
Realtime expectations on batch ETL - dashboards lag minutes. Fix: SLAs on refresh job monitoring.
PII in wallet dashboards - privacy policies. Fix: aggregate public keys only; governance on exports.

Alternatives

Alternative	Use When	Don't Use When
Dune/Flipside community	Public protocol stats	Private unreleased program
ClickHouse real-time	Billions of rows	Small MVP
Notebook SQL	Ad-hoc research	Customer-facing dashboards
Provider analytics UI	Quick launch	Custom metric definitions

FAQs

Postgres vs warehouse?

Start Postgres; migrate to BigQuery/ClickHouse when queries exceed minute-scale on replica.

How fresh should data be?

Product-dependent - 1-5 min lag common for batch refresh; sub-minute needs stream aggregations.

Can @solana/kit power dashboards?

No - dashboards read your database API, not JSON-RPC directly.

What to monitor operationally?

Indexer lag slots, refresh duration, row count growth, API p95 latency.

How to define TVL?

Document token price source and which accounts count as locked - ambiguous across protocols.

Compressed NFT metrics?

Use DAS-indexed tables or provider exports for cNFT mint counts.

Access control?

Read-only DB roles per dashboard; no production write creds in BI tools.

Testing metric SQL?

Snapshot tests on fixed fixture dataset with expected aggregates.

Seasonality spikes?

Pre-aggregate before mint events; scale refresh frequency temporarily.

Link to indexer build?

See Building an Indexer for upstream pipeline.

Building an Indexer - data source
Indexing Basics - why not RPC
RPC Best Practices - avoid RPC analytics
Indexing Best Practices - pipeline hygiene

Stack versions: This page was written for Agave 4.1.1, Solana CLI 3.0.10, Anchor 0.32.1, anchor-lang 0.32.1, Rust 1.91.1, @solana/kit 7.0.0, Surfpool 0.12.0, and LiteSVM 0.6.x.

Analytics & Dashboards

Search Documentation

Analytics & Dashboards