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_volumeWhat 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.
Related
- 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.