Token Transfers

Question it answers

“Give me every ERC-20 transfer: all transfers of token 0x…, or every transfer wallet 0x… sent or received.”

One flat event log of token transfers, served two ways from a single sync: by-token (every movement of a given token) and by-wallet (every transfer a wallet was on either side of). Each row is one transfer carrying exactly one token and one amount; there’s no USD enrichment, because a transfer event carries no price.

What you get

One row per transfer, from Moralis-indexed, normalized per-block onchain data:

Column	Description
`token_address`	The transferred token’s contract
`from_address`, `to_address`	Sender and recipient
`amount`	Raw `uint256` token units (not decimal-adjusted)
`transfer_type`	Transfer kind as emitted (`erc20`, …)
`initiated_by`	Address that initiated the transfer
`block_number`, `log_index`, `transaction_index`	On-chain ordering tuple
`tx_hash`	Transaction that produced the transfer
`block_timestamp` / `event_ts`	Block time

Source

The transform reads a single per-block array, tokenTransfers, and lands one row per transfer. Fields map straight from the source struct (tokenAddress → token_address, fromAddress → from_address, toAddress → to_address, amount → amount, type → transfer_type, initiatedBy → initiated_by). There’s no price join: transfers are unpriced, so there is no amount_usd column. The per-transfer vendor_event_id is widened beyond (tx_hash, log_index) so the id stays unique on Solana, where logIndex is not row-unique within an instruction (see Multichain).

Destination

Destination	Table	Read pattern
ClickHouse (first-class)	`fact_token_transfers`	Prefix scan on `(chain_id, token_address, block_number)` for by-token; `bloom_filter` skip-indexes on `from_address` / `to_address` for by-wallet; read with `FINAL` or `sum(sign)`
Postgres	`token_transfers`	Index on `(token_address, block_number DESC)`; plus `(from_address, …)` and `(to_address, …)`
MySQL	`token_transfers`	Index on `(token_address, block_number)`; plus `(from_address, …)` and `(to_address, …)`

ClickHouse uses the collapsing log-table pattern (see the recipes overview) so chain reorganizations self-correct: the +1/−1 reorg pair for a row shares an identical key and collapses on merge. The fact table’s sort key is token-first, so all transfers of a token are a contiguous range read; by-wallet reads are accelerated by data-skipping bloom filters rather than a second sort key.

Full schema

Below is the complete read table this recipe produces. It’s a starting point: keep the columns you need and drop the rest (see Schema & flexibility). amount is stored raw (uint256 token units) because the transfer event carries no decimals; scale by 10^token_decimals at read time.

ClickHouse, fact_token_transfers

CREATE TABLE recipe_token_transfers.fact_token_transfers
(
    vendor_event_id     String,
    ingested_at         DateTime64(3),
    chain_id            UInt32,
    block_hash          String,
    block_number        UInt64,
    event_ts            DateTime64(3),
    token_address       String,
    from_address        String,
    to_address          String,
    amount              String,        -- raw uint256 token units
    transfer_type       LowCardinality(String),
    initiated_by        String,
    tx_hash             String,
    log_index           Nullable(UInt32),
    transaction_index   Nullable(Int32),
    sign                Int8,
    -- by-wallet skip indexes: prune granules that cannot contain the wallet.
    INDEX bf_from from_address TYPE bloom_filter(0.01) GRANULARITY 4,
    INDEX bf_to   to_address   TYPE bloom_filter(0.01) GRANULARITY 4
)
ENGINE = ReplicatedCollapsingMergeTree(
    '/clickhouse/tables/{database}/fact_token_transfers', '{replica}', sign)
PARTITION BY (chain_id, toYYYYMM(event_ts))
ORDER BY (chain_id, token_address, block_number, vendor_event_id);

The sign column drives reorg collapsing: read with FINAL or sum(sign), never a bare WHERE sign = 1. A single-node setup can use CollapsingMergeTree(sign) without the replication path.

Postgres, token_transfers

CREATE TABLE public.token_transfers (
  position           BIGINT      NOT NULL,
  log_index          BIGINT,
  transaction_index  BIGINT,
  block_number       BIGINT      NOT NULL,
  block_timestamp    BIGINT      NOT NULL,    -- unix seconds
  tx_hash            TEXT        NOT NULL,
  vendor_event_id    TEXT        NOT NULL,
  token_address      TEXT        NOT NULL,
  from_address       TEXT        NOT NULL,
  to_address         TEXT        NOT NULL,
  amount             NUMERIC(76, 0)  NOT NULL,   -- raw uint256 token units
  transfer_type      TEXT        NOT NULL,
  initiated_by       TEXT        NOT NULL
);

-- By-token access (the recipe's primary purpose).
CREATE INDEX IF NOT EXISTS token_transfers_token_block_idx
  ON public.token_transfers (token_address, block_number DESC);
-- By-wallet access (either side).
CREATE INDEX IF NOT EXISTS token_transfers_from_block_idx
  ON public.token_transfers (from_address, block_number DESC);
CREATE INDEX IF NOT EXISTS token_transfers_to_block_idx
  ON public.token_transfers (to_address, block_number DESC);
-- Block-range helper.
CREATE INDEX IF NOT EXISTS token_transfers_block_idx
  ON public.token_transfers (block_number);

MySQL is the same shape with the same indexes. amount uses NUMERIC(76, 0) so large raw uint256 values don’t overflow; position is the block-level cursor used during backfill.

Example reads

All transfers of a token, newest first (ClickHouse):

SELECT block_number, from_address, to_address, amount, transfer_type, tx_hash
FROM recipe_token_transfers.fact_token_transfers FINAL
WHERE chain_id = 1
  AND token_address = lower('0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48')
ORDER BY block_number DESC
LIMIT 50;

All transfers involving a wallet on either side (bloom-pruned):

SELECT block_number, token_address, from_address, to_address, amount
FROM recipe_token_transfers.fact_token_transfers FINAL
WHERE chain_id = 1
  AND (from_address = lower('0x...') OR to_address = lower('0x...'))
ORDER BY block_number DESC
LIMIT 50;

Net amount received by a wallet for one token (sign-aware, cheaper than FINAL):

SELECT
  sumIf(toFloat64OrZero(amount), to_address = lower('0xwallet') AND sign =  1)
- sumIf(toFloat64OrZero(amount), to_address = lower('0xwallet') AND sign = -1)
  AS received
FROM recipe_token_transfers.fact_token_transfers
WHERE chain_id = 1 AND token_address = lower('0xtoken');

Modes

Shipped defaults: ClickHouse hybrid (backfill → realtime), Postgres / MySQL historical (one-shot backfill). For live/reorg-safe ingestion, use ClickHouse; see the overview.

The realtime reorg path needs a single-column UNIQUE on the position column, but position is block-level (many transfers share one block), so array-expanded transfer rows can only carry a composite unique. Run realtime/hybrid on ClickHouse, where the collapsing log table corrects reorgs per-block. The Postgres / MySQL configs are intended for historical backfill.

Multichain

The recipe is chain-parametrized via the chain setting: point it at any supported EVM chain or Solana. On Solana, multiple events in one instruction can share a logIndex, so the vendor_event_id is widened with (from_address, to_address, token_address, amount) to keep rows distinct; the transfer log it produces is identical in shape.

Fidelity gaps

The recipe lands exactly what the tokenTransfers array carries. Fields a transfers endpoint might surface that have no onchain source in this array are omitted:

USD value: a transfer carries no price. Pricing requires joining the same-block price data; that’s out of scope for a plain transfer log (see Token Prices for the price-join pattern).
Token metadata (symbol, name, decimals, logo, verified/spam flags): these come from a separate token-metadata sync, not the transfer event. amount is therefore stored raw, not decimal-adjusted.
Pre/post balances: the balance surface lives in the balances recipes (Token Balances by Token / by Wallet); this transfer log stays a flat event stream.

Migrating from the REST API

This recipe replaces both ERC-20 transfer REST endpoints from one sync: the same token_transfers table serves the by-token and by-wallet reads.

Not 1:1. The REST responses inlined token_name / token_symbol / token_decimals and a pre-scaled value_decimal; this recipe’s amount is raw uint256. Pair it with Token Metadata and join on token_address to scale amounts and label tokens. token_logo, possible_spam, verified_contract, security_score, and the address *_label / *_entity fields are off-chain signals; add them from your own token/label lists or drop them.

Field mapping (both endpoints share it; calculated = derived at read time, add yourself = off-chain):

REST API field	Data Feeds	Fidelity
`from_address`, `to_address`, token contract	`token_transfers.from_address` / `to_address` / `token_address`	exact
`value` (raw)	`token_transfers.amount`	exact
`value_decimal`	`amount / 10^decimals` (decimals via Token Metadata)	calculated
`transaction_hash`	`token_transfers.tx_hash`	exact
`block_number`, `block_timestamp`, `block_hash`, `transaction_index`, `log_index`	`token_transfers.*`	exact
`token_name`, `token_symbol`, `token_decimals`	join Token Metadata	exact
`token_logo`, `possible_spam`, `verified_contract`, `security_score`	None	add yourself
`from/to_address_label`, `_entity`, `_entity_logo`	None	add yourself

Neither endpoint returned USD values (a transfer carries no price), so every mapped value is exact from the chain, and the feed adds two fields the API omitted: transfer_type and initiated_by.

`GET /erc20/{address}/transfers`: transfers by token

Every transfer of one token becomes a plain by-token read of token_transfers, the table’s primary sort, so it’s a contiguous range scan:

SELECT t.*, t.amount / power(10, m.decimals) AS value_decimal
FROM token_transfers t
LEFT JOIN token_metadata m ON m.token_address = t.token_address
WHERE t.token_address = lower('0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48')
ORDER BY t.block_number DESC, t.log_index DESC
LIMIT 100;

The endpoint’s from_date / to_date, from_block / to_block, and order params are plain WHERE / ORDER BY clauses; no fixed page size.
High-volume tokens have enormous histories: keyset-paginate on (block_number, log_index), not OFFSET.
For a token’s complete history, backfill from its deploy block (historical or hybrid mode); realtime alone only captures new transfers.

`GET /{address}/erc20/transfers`: transfers by wallet

A wallet’s transfers are the rows where it appears on either side; the recipe indexes both from_address and to_address, so it’s one query:

SELECT t.*,
       t.amount / power(10, m.decimals) AS value_decimal,
       CASE WHEN t.from_address = lower('0xwallet') THEN 'send' ELSE 'receive' END AS direction
FROM token_transfers t
LEFT JOIN token_metadata m ON m.token_address = t.token_address
WHERE (t.from_address = lower('0xwallet') OR t.to_address = lower('0xwallet'))
ORDER BY t.block_number DESC, t.log_index DESC
LIMIT 100;

In each row the token_address is the token, not the wallet; derive direction by comparing the wallet to from_address / to_address, as above.
The endpoint’s contract_addresses filter becomes AND t.token_address = ANY($tokens); block/date windows and order map the same way as by-token.
On ClickHouse the by-wallet read is served by the bloom_filter skip indexes on from_address / to_address (see Example reads) rather than a second sort key.

Token Holders

The balance roll-up these transfers feed: all non-zero holders of a token.

Accounting & Tax

Per-asset transfer ledgers for reconciliation, valued via Token Prices.

Getting Started

Access Methods

Data APIs & Migration Guides

Streams

Gotchas & Troubleshooting

Concepts

Use Cases

Token Transfers

Question it answers

What you get

Source

Destination

Full schema

Example reads

Modes

Multichain

Fidelity gaps

Migrating from the REST API

`GET /erc20/{address}/transfers`: transfers by token

`GET /{address}/erc20/transfers`: transfers by wallet

Token Holders

Accounting & Tax

​Question it answers

​What you get

​Source

​Destination

​Full schema

​Example reads

​Modes

​Multichain

​Fidelity gaps

​Migrating from the REST API

​GET /erc20/{address}/transfers: transfers by token

​GET /{address}/erc20/transfers: transfers by wallet

​Related

Token Holders

Accounting & Tax

Question it answers

What you get

Source

Destination

Full schema

Example reads

Modes

Multichain

Fidelity gaps

Migrating from the REST API

`GET /erc20/{address}/transfers`: transfers by token

`GET /{address}/erc20/transfers`: transfers by wallet

Related