Architecture Report — 2026-04-17

On-Chain Analytics
Architecture &
Intelligence Layers

CryptoPrism.io — 4-layer intelligence stack delivering Glassnode-quality on-chain signals across 21 chains via BigQuery public datasets at 5% of the cost

Intelligence Layers

Chains Supported

DB Tables

$0.22

Daily Cost

CryptoPrism.io | cryptoprism-onchain repo | 14 metrics | 34 BigQuery datasets

Chapter 01

The 4 Intelligence Layers

CryptoPrism's on-chain analytics system is a 4-layer intelligence stack. Each layer builds on the one below, adding progressively more insight and monetization potential. Raw data at the bottom; actionable trading signals at the top.

Core Thesis

Competitors either charge $799/month (Glassnode) or give you raw data without intelligence layers (free explorers). CryptoPrism delivers Layer 3-4 intelligence at $29/month using BigQuery public datasets — 80% of the value at 5% of the cost.

Raw Daily Data

12 metrics per chain per day from BigQuery → PostgreSQL

Free (limited)

Daily metrics extracted from 34 BigQuery public blockchain datasets and stored in the onchain_daily_metrics table. Refreshed daily at 06:00 UTC via Cloud Scheduler. Currently 2,207+ rows across 14 chains.

Metric	Description	Unit
active_addresses	Unique addresses transacting per day	Count
whale_txs	Transactions above threshold (>100 BTC, >500 ETH)	Count
tx_volume	Total transaction volume	Native + USD
nvt	Network Value to Transactions ratio	Ratio
mvrv	Market Value to Realized Value ratio	Ratio
cdd	Coin Days Destroyed	Float
supply_distribution	Holder cohort breakdown	JSON
exchange_flow	Inflow/outflow to exchange addresses	Native + USD
gas_trends	Average gas price (EVM chains)	Gwei
token_transfers	Top ERC-20/SPL token transfer volumes	Count + USD
dex_volume	DEX swap counts and volume	Count + USD
staking_flows	Beacon chain deposits, validator activity	ETH + Count

Example: {"chain": "bitcoin", "metric": "active_addresses", "metric_date": "2026-04-16", "value": 641257, "signal": "neutral"}

Trend Metrics (WoW / MoM / QoQ / YoY)

Percentage-change comparisons across standardized time windows

Free (direction)

Computed entirely from Layer 1 data using SQL window functions. No additional BigQuery queries required. Transforms raw numbers into directional intelligence — the "hook" for free-tier users.

Window	Computation	Use Case
WoW	Current 7d avg vs previous 7d avg	Short-term momentum
MoM	Current 30d avg vs previous 30d avg	Monthly trend
QoQ	Current 90d avg vs previous 90d avg	Quarterly shift
YoY	Current 365d avg vs previous 365d avg	Structural change

Example: "BTC active addresses up 12.4% MoM, down 3.1% WoW"
Free users see: trend direction + percentage (no raw numbers)
Pro users see: full data including raw averages

Cross-Chain Correlation

Capital rotation, ecosystem divergence, whale synchronization

Pro

Comparative analytics across multiple chains that surface market-wide intelligence. Computed by normalizing metrics to Z-scores and comparing across chains. This is the "so what" layer.

Signal Categories

Signal Type	What It Detects	Example
Capital Rotation	Bridge flow shifts between chains	"ETH→Arbitrum bridge flow +45% WoW"
Ecosystem Divergence	Normalized growth rate differences	"SOL active addrs 2x vs 6mo ago, ETH flat"
Whale Sync	Simultaneous whale activity across chains	"BTC+ETH whale txs spiking simultaneously"
Dominance Shift	Active address share changes over time	"Tron share: 48% (up from 38% QoQ)"

Computation: Normalize metrics to Z-scores (std devs from 90d mean) → compare across chains → flag when divergence > 2.0 sigma

Divergence Signals (Price vs On-Chain)

Actionable trading intelligence — the $799/month killer

Premium

The highest-value layer. Detects when on-chain fundamentals diverge from price action. Requires on-chain data (L1) + price data (CoinGecko) + historical context (4-year backfill). Feeds directly into Saarthi AI companion responses.

Signal Types

Signal	Trigger	Interpretation
Price-Activity Divergence	Price up but activity down (or vice versa)	Rally on thin participation = bearish
Whale Accumulation	Exchange outflow rising, price flat	Silent accumulation = bullish
MVRV Zone Transition	Moving between accumulation/bull/euphoria	Historical precedent comparison
NVT Divergence	Network usage declining vs market cap	Overvaluation risk signal

Example: "BTC price +15.2% MoM but active addresses -8.1%. The rally is thinning out. Last time we saw this divergence magnitude (Sep 2025), price corrected 18% over 3 weeks."

Layer Summary

Layer	What	Computation	User Tier	Value
L1	12 metrics/chain/day	BigQuery → PostgreSQL	Free	Baseline data
L2	WoW/MoM/QoQ/YoY	SQL window functions	Free	"Which way?"
L3	Cross-chain signals	Normalized Z-scores	Pro	"So what?"
L4	Price vs on-chain	Multi-source correlation	Premium	"What do I do?"

Chapter 02

BigQuery Schema Reference

Complete schema documentation for all BigQuery public blockchain datasets used by the on-chain pipeline. Organized by chain type with critical query optimization notes.

UTXO Chains (BTC, LTC)

crypto_bitcoin.transactions / crypto_litecoin.transactions

Community-maintained datasets. Same schema for both chains.

Column	Type	Description
hash	STRING	Transaction hash
block_timestamp	TIMESTAMP	Block mining time
block_timestamp_month	DATE	PARTITION KEY Must filter on this
inputs	RECORD[]	Transaction inputs (spent_output_key, value)
outputs	RECORD[]	Transaction outputs (addresses[], value)
output_value	FLOAT	Total output value (satoshis/litoshis)
fee	FLOAT	Transaction fee
is_coinbase	BOOLEAN	Miner reward transaction flag

Critical: Partition Pruning

BTC table is 281 GB. Without block_timestamp_month filter, a 7-day query scans entire table ($1.76). With partition filter: 1.8 GB scanned. Always filter on block_timestamp_month first.

Google-Managed EVM Chains

Standardized goog_blockchain_* schema. Covers: ETH, Polygon, Arbitrum, Optimism, Avalanche, Tron, Cronos, Fantom.

goog_blockchain_*.transactions

Column	Type	Description
block_timestamp	TIMESTAMP	PARTITION KEY (DAY)
from_address	STRING	Sender address
to_address	STRING	Recipient address
value	STRUCT	STRUCT! Use `.bignumeric_value`
gas_price	STRUCT	STRUCT! Use `.bignumeric_value`
gas / gas_used	INTEGER	Gas limit / actual consumption
status	INTEGER	1 = success, 0 = reverted

Critical: STRUCT Values

value and gas_price are STRUCT types: {string_value, bignumeric_value}. Must access value.bignumeric_value / 1e18 for ETH amounts. Using value directly causes errors.

goog_blockchain_*.token_transfers

Column	Type	Description
address	STRING	Token contract address
from_address	STRING	Sender
to_address	STRING	Recipient
quantity	BIGNUMERIC	Raw transfer amount (need decimals)
event_type	STRING	'ERC-20', 'ERC-721', 'ERC-1155'

Custom Schema Chains

Solana — crypto_solana_mainnet_us

Transactions table: 786 TB. Use Token Transfers instead ($0.04/day vs $1.19/day)

Table	Key Columns	Partition	Cost/Day
Transactions	signature, fee, status, accounts[], compute_units_consumed	block_timestamp (DAY)	$1.19
Token Transfers	source, destination, authority, value, decimals, mint	block_timestamp (DAY)	$0.04

Sui — crypto_sui_mainnet_us

WARNING: timestamp_ms is INT64 (milliseconds), not TIMESTAMP. Must convert with TIMESTAMP_MILLIS()

Table	Key Columns	Gotcha
TRANSACTION	timestamp_ms, sender, total_gas_cost, execution_success	INT64 timestamp!
transaction_values_view	transaction_digest, value	Join for whale detection

NEAR — crypto_near_mainnet_us

WARNING: Partition column is block_date (DATE type), not block_timestamp

Table	Key Columns	Gotcha
transactions	signer_account_id, receiver_account_id, receipt_conversion_tokens_burnt	DATE partition!
ft_events	contract_account_id, affected_account_id, delta_amount, cause	Fungible token events
ft_balances_daily	epoch_date, account_id, liquid	Supply distribution

Aptos & MultiversX

Chain	Table	Key Columns	Notes
Aptos	transactions	sender, gas_used, gas_unit_price, tx_type, success	Filter tx_type='user_transaction'
MultiversX	operations	sender, receiver, feeNum, valueNum, status	Values already in EGLD (decimal)

Chapter 03

PostgreSQL Schema (8 Tables)

All tables hosted on GCP Cloud SQL (dbcp instance). Schema designed for fast time-series queries with composite unique constraints preventing duplicate inserts.

Tables

2,207+

Daily Metric Rows

Chains Active

Exchange Labels

#	Table	Purpose	Unique Key	Rows
1	onchain_daily_metrics	Core daily metrics per chain	`(chain, metric, metric_date)`	2,207+
2	onchain_whale_txs	Individual whale transaction records	`(tx_hash)`	~7,000+
3	onchain_exchange_flow	Daily exchange inflow/outflow	`(chain, flow_date)`	~200+
4	onchain_supply_distribution	Holder cohort snapshots	`(chain, snapshot_date, cohort)`	~500+
5	onchain_utxo_metrics	MVRV, SOPR, NUPL, CDD (BTC/LTC)	`(chain, metric, metric_date)`	~400+
6	exchange_address_labels	Known exchange wallet addresses	`(chain, address)`	39
7	onchain_scores	Composite on-chain scores (0-100)	`(chain, token_id, score_date)`	~150+
8	onchain_pipeline_runs	Pipeline execution metadata	`(auto-increment)`	~300+

Key Table Schemas

onchain_daily_metrics

Column	Type	Description
chain	VARCHAR(20)	Chain identifier
metric	VARCHAR(50)	Metric name
metric_date	DATE	Measurement date
value	FLOAT	Value in native units
value_usd	FLOAT	Value in USD
signal	VARCHAR(20)	bullish / bearish / neutral

onchain_scores

CryptoScore on-chain component: 4 equal-weight sub-scores

Sub-Score	Weight	Inputs
network_activity	25%	active_addresses, tx_count, tx_volume
whale_behavior	25%	whale_txs, exchange_flow, large_holder_changes
holder_conviction	25%	supply_distribution, cdd, exchange_reserve
usage_growth	25%	WoW/MoM trends on all metrics

onchain_pipeline_runs

Operational metadata for monitoring and cost tracking

Pipeline	Frequency	Duration	Avg Cost
daily_metrics	24h at 06:00 UTC	2-5 min/chain	$0.01-1.76
whale_scan	24h at 06:15 UTC	1-3 min/chain	$0.005-0.50
exchange_flow	24h at 06:30 UTC	2-4 min/chain	$0.01-0.80
supply_snapshot	Weekly (Sun 06:00)	5-10 min/chain	$0.05-2.00
utxo_advanced	24h at 07:00 UTC	3-8 min (BTC/LTC)	$0.50-1.80
score_compute	24h at 08:00 UTC	30s/chain	$0 (local SQL)

Chapter 04

Metric Coverage Matrix

21 chains x 14 metrics. Green = live and producing data. Amber = schema ready, pending backfill or Analytics Hub enrollment. Gray = not applicable to chain type.

Chain	Active Addr	Whale Tx	Tx Vol	NVT	MVRV	CDD	Supply Dist	Exch Flow	Gas	Token Xfer	DEX Vol	Staking	Score	Trends
Bitcoin	●	●	●	●	●	●	●	●	○	○	○	○	●	●
Ethereum	●	●	●	●	○	○	●	●	●	●	●	●	●	●
Tron	●	●	●	●	○	○	●	●	●	●	●	○	●	●
Polygon	●	●	●	●	○	○	●	●	●	●	●	○	●	●
Litecoin	●	●	●	●	●	●	●	●	○	○	○	○	●	●
Optimism	●	●	●	●	○	○	●	●	●	●	●	○	●	●
Solana	●	●	●	●	○	○	●	●	●	●	●	●	●	●
Arbitrum	●	●	●	●	○	○	●	●	●	●	●	○	●	●
Avalanche	●	●	●	●	○	○	●	●	●	●	●	○	●	●
Sui	●	●	●	●	○	○	●	●	●	●	●	●	●	●
NEAR	●	●	●	●	○	○	●	●	●	●	●	○	●	●
Aptos	●	●	●	●	○	○	●	●	●	●	●	●	●	●
MultiversX	●	●	●	●	○	○	●	●	●	●	○	●	●	●
Cronos	●	●	●	●	○	○	●	●	●	●	●	○	●	●
Fantom	●	●	●	●	○	○	●	●	●	●	●	○	●	●
Dogecoin	●	●	●	●	○	○	●	●	○	○	○	○	●	●

● Live ● Schema ready / pending backfill ● Stale dataset ○ Not applicable to chain type

Chapter 05

Cost Analysis

Daily operational cost breakdown. Cloud Run chains use BigQuery directly; Solana uses GitHub Actions for cost optimization.

$0.18

Cloud Run / Day

All chains except SOL

$0.04

Solana / Day

Via GitHub Actions

$0.22

Total / Day

$6.60/month ongoing

Daily Cost by Chain

Chain	Method	Data/Query	Daily Cost	Monthly Cost
Bitcoin	Cloud Run + BigQuery	~1.8 GB	$0.08	$2.40
Litecoin	Cloud Run + BigQuery	~1.2 GB	$0.05	$1.50
Ethereum	Cloud Run + BigQuery	~0.3 GB	$0.01	$0.30
Polygon	Cloud Run + BigQuery	~0.2 GB	$0.007	$0.21
Tron	Cloud Run + BigQuery	~0.3 GB	$0.008	$0.24
Optimism	Cloud Run + BigQuery	~0.05 GB	$0.002	$0.06
Other EVM (6)	Cloud Run + BigQuery	~0.3 GB total	$0.03	$0.90
Sui/NEAR/Aptos/MX	Cloud Run + BigQuery	~0.1 GB total	$0.01	$0.30
Solana	GitHub Actions	Token Transfers (~8 GB)	$0.04	$1.20
TOTAL			$0.22	$6.60

Budget Projection (12 Months)

Category	Cost	Notes
90-Day Backfill	$67.07	One-time (BTC $45.93, LTC $17.62, others $3.52)
Monthly Operations	$6.60/mo	All 21 chains, daily refresh
12-Month Ops Total	$79.20	$6.60 x 12 months
Cloud Run	$15-25/mo	API service hosting
Cloud SQL	$30-40/mo	PostgreSQL instance
Redis	$10-15/mo	Memorystore 1GB
12-MONTH TOTAL	~$850-1,000	Covered by $1,000 GCP credits

Budget Summary

Entire on-chain analytics infrastructure costs $0 cash for the first 12-18 months. $1,000 GCP credits cover everything: BigQuery queries, Cloud Run, Cloud SQL, Redis. After credits expire, monthly cost is approximately $77-85/month — fundable from first 3 Pro subscribers at $29/month.

Chapter 06

Architecture Diagram

End-to-end data flow from BigQuery public datasets through to the frontend dashboard.

Data Sources

BigQuery

34 blockchain datasets

CoinGecko

Price + market cap

DeFiLlama

TVL + DEX volume

Binance/Bybit

Derivatives data

▼

Pipeline Layer

Cloud Scheduler

12 cron jobs

▶

Cloud Run Pipeline

FastAPI + BigQuery client

▶

GitHub Actions

Solana (cost opt)

▼

Storage Layer

PostgreSQL

Cloud SQL (8 tables)

▶

Redis

Memorystore 1GB cache

▼

API Layer

FastAPI

17 REST endpoints

▼

Frontend

Next.js 15

app.cryptoprism.io

Saarthi AI

Claude API context

Alerts

Telegram + Email

Data Flow Summary

Pipeline Sequence (Daily at 06:00 UTC)

1. Cloud Scheduler triggers Cloud Run pipeline endpoint
2. Pipeline queries BigQuery for previous day's data (partition-pruned)
3. Results written to PostgreSQL (8 tables, UPSERT on unique constraints)
4. Computed metrics (trends, scores) calculated via SQL
5. Results cached in Redis (1h TTL for trends, 24h for daily metrics)
6. API serves cached data; cache miss triggers DB query
7. Saarthi AI reads context endpoint for on-chain intelligence
8. Divergence signals (L4) trigger alerts via Telegram + Email

Key Design Decisions

Decision	Choice	Rationale
Hosting	Firebase + Cloud Run	Vercel Hobby plan prohibits commercial use
Derivatives	Binance + Bybit direct	Same data as CoinGlass, $0 cost
AI Model	Claude API (Haiku/Sonnet)	Direct API, not Agent SDK — Saarthi is workflow
Solana Pipeline	GitHub Actions	Token Transfers: $0.04/day vs $1.19 Transactions
Auth	Firebase Auth	Google OAuth + email link, zero cost
Cache	Memorystore Redis 1GB	Sub-50ms response time for API

End of Report

CryptoPrism On-Chain Analytics

Architecture & Intelligence Layers Report

Generated: 2026-04-17 | 4 Intelligence Layers | 21 Chains
8 PostgreSQL Tables | 34 BigQuery Datasets | $0.22/day operational cost

CryptoPrism.io

On-Chain AnalyticsArchitecture &Intelligence Layers

The 4 Intelligence Layers

Signal Categories

Signal Types

Layer Summary

BigQuery Schema Reference

UTXO Chains (BTC, LTC)

Google-Managed EVM Chains

Custom Schema Chains

PostgreSQL Schema (8 Tables)

Key Table Schemas

Metric Coverage Matrix

Cost Analysis

Daily Cost by Chain

Budget Projection (12 Months)

Architecture Diagram

Data Flow Summary

Key Design Decisions

CryptoPrism On-Chain Analytics

On-Chain Analytics
Architecture &
Intelligence Layers