Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.moralis.com/llms.txt

Use this file to discover all available pages before exploring further.

​
Question it answers

“Give me every DEX trade on pool/pair 0x…, newest first — each row is one trade with both token legs, the side, the protocol, and a USD notional.”
Same trade projection as Swaps by Token, but keyed by the pool/pair address with one row per trade (no per-token-side unpivot). pool_address is the pair address for direct pool fills and the aggregator address for aggregator-routed trades.

​
What you get

One row per trade, keyed by pool_address:
ColumnDescription
pool_addressPair address (pool fills) or aggregator address (aggregator-routed trades)
token0_address, token1_addressThe pair’s two tokens
amount0, amount1Raw token-unit deltas for each leg (uint256 as text)
sidebuy · sell — derived from the base token’s pool-balance delta
source_kindpool_fill (direct pool) · aggregator (aggregator-routed)
price_usdBought-leg (token1) USD price; NULL with no in-block price update
notional_usdtoken1Amount / 10^token1Decimals × price_usd; NULL if unpriced
protocolDEX/protocol identifier
fee_amount, fee_token, fee_usdTrade fee — pool fills only (NULL on aggregator rows)
trader_addressThe trading wallet
block_number, event_ts, tx_hash, vendor_event_idOn-chain ordering and identity

​
Source

The transform reads two per-block trade arrays and projects them into one feed: tokenSwaps (direct pool fills, source_kind='pool_fill', pool_address = pairAddress) · aggregateTokenSwaps (aggregator-routed trades, source_kind='aggregator', pool_address = aggregatorAddress) The two branches are disjoint by construction — together they give every fill exactly once. USD values are computed inline from the same block’s tokenPriceUpdates (the chronologically-last update wins on duplicate keys) — no separate price join at read time.

​
Destination

DestinationTableBy-pair access
ClickHouse (first-class)fact_swaps_by_pairPrefix scan on (chain_id, pool_address, event_ts, …); read with FINAL or sum(sign)
PostgresswapsIndex lead (pool_address, block_number DESC)
MySQLswapsIndex lead (pool_address, block_number)
ClickHouse uses the collapsing log-table pattern (see the recipes overview) so chain reorganizations self-correct — the +1/−1 reorg pair for a trade shares an identical key and collapses cleanly. The fact table’s sort key is pool-first, so a pair’s full trade feed is a contiguous range read.

​
Full schema

The complete read table this recipe produces — one row per trade. Keep the columns you need and drop the rest (see Schema & flexibility). Raw uint256 amounts are stored as text in ClickHouse (they exceed numeric precision); USD columns are nullable so an unpriced token leaves them empty. 
CREATE TABLE recipe_swaps_by_pair.fact_swaps_by_pair
(
    vendor_event_id     String,
    ingested_at         DateTime64(3),
    chain_id            UInt32,
    block_hash          String,
    block_number        UInt64,
    event_ts            DateTime64(3),
    pool_address        String,        -- pair (pool_fill) or aggregator
    token0_address      String,
    token1_address      String,
    amount0             String,        -- raw uint256
    amount1             String,        -- raw uint256
    trader_address      String,
    price_usd           Nullable(String),
    notional_usd        Nullable(String),
    side                LowCardinality(String),   -- buy | sell
    source_kind         LowCardinality(String),   -- aggregator | pool_fill
    protocol            LowCardinality(String),
    fee_amount          Nullable(String),
    fee_token           Nullable(String),
    fee_usd             Nullable(String),
    sign                Int8
)
ENGINE = ReplicatedCollapsingMergeTree(
    '/clickhouse/tables/{database}/fact_swaps_by_pair', '{replica}', sign)
PARTITION BY (chain_id, toYYYYMM(event_ts))
ORDER BY (chain_id, pool_address, event_ts, 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.
CREATE TABLE public.swaps (
  position           BIGINT      NOT NULL,
  log_index          BIGINT,                 -- NULL for aggregator rows
  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,
  trader_address     TEXT        NOT NULL,
  pool_address       TEXT        NOT NULL,    -- pair (pool_fill) or aggregator
  token0_address     TEXT        NOT NULL,
  token1_address     TEXT        NOT NULL,
  amount0            NUMERIC(76, 0)  NOT NULL,
  amount1            NUMERIC(76, 0)  NOT NULL,
  price_usd          NUMERIC(38, 18),
  notional_usd       NUMERIC(38, 18),
  side               TEXT        NOT NULL,
  source_kind        TEXT        NOT NULL,    -- aggregator | pool_fill
  protocol           TEXT        NOT NULL,
  fee_amount         NUMERIC(76, 0),
  fee_token          TEXT,
  fee_usd            NUMERIC(38, 18)
);

-- By-pair access (the recipe's primary purpose).
CREATE INDEX IF NOT EXISTS swaps_pool_block_idx
  ON public.swaps (pool_address, block_number DESC);
-- Token + trader + block-range helpers.
CREATE INDEX IF NOT EXISTS swaps_token0_block_idx
  ON public.swaps (token0_address, block_number DESC);
CREATE INDEX IF NOT EXISTS swaps_token1_block_idx
  ON public.swaps (token1_address, block_number DESC);
CREATE INDEX IF NOT EXISTS swaps_trader_block_idx
  ON public.swaps (trader_address, block_number DESC);
CREATE INDEX IF NOT EXISTS swaps_block_idx
  ON public.swaps (block_number);
MySQL is the same shape with DECIMAL(38,18) for the USD columns. Amounts use NUMERIC(76,0) so large raw uint256 balances never overflow. position is the block-level cursor used during backfill.

​
Example reads

All trades on a pool/pair, newest first (ClickHouse): 
SELECT event_ts, side, source_kind, token0_address, token1_address,
       amount0, amount1, notional_usd, protocol
FROM recipe_swaps_by_pair.fact_swaps_by_pair FINAL
WHERE chain_id = 1
  AND pool_address = lower('0x88e6A0c2dDD26FEEb64F039a2c41296FcB3f5640')
ORDER BY event_ts DESC
LIMIT 50;
24h USD volume per pool (sign-aware, cheaper than FINAL): 
SELECT pool_address,
       sumIf(toFloat64OrZero(notional_usd), sign =  1)
     - sumIf(toFloat64OrZero(notional_usd), sign = -1) AS volume_usd
FROM recipe_swaps_by_pair.fact_swaps_by_pair
WHERE chain_id = 1 AND event_ts >= now() - INTERVAL 1 DAY
GROUP BY pool_address
ORDER BY volume_usd DESC
LIMIT 25;
Postgres / MySQL: 
SELECT block_number, side, source_kind, token0_address, token1_address,
       amount0, amount1, notional_usd, protocol
FROM swaps
WHERE pool_address = lower('0x88e6...')
ORDER BY block_number DESC
LIMIT 50;

​
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 trades per block), so array-expanded trade rows can only carry a composite unique. Run realtime/hybrid on ClickHouse (the log table corrects reorgs per-block via the collapsing companion table); the Postgres / MySQL configs are intended for historical backfill.

​
Multichain

The recipe is chain-parametrized — point it at any supported EVM chain or Solana. On Solana, the same logIndex can be assigned to multiple events in one instruction, so for production the pool-fill event identity is widened with (token0_address, token1_address, token0_amount) to keep rows distinct; the trade feed it produces is identical in shape.

​
Fidelity gaps

  • price_usd / notional_usd / fee_usd are NULL when the relevant token had no in-block tokenPriceUpdate (illiquid or brand-new tokens). The recipe enriches only from same-block price updates — there is no cross-block carry-forward.
  • fee_* is populated for pool fills only. Aggregator-routed trades carry no per-fill fee, so fee_amount / fee_token / fee_usd are NULL on source_kind='aggregator' rows.
  • One trade can appear under two pool_address values when it is observed both as a direct pool fill (the pair address) and as part of an aggregator route (the aggregator address). These are distinct events with distinct vendor_event_ids by design — the two source arrays are disjoint per event.

Swaps by Token

The same trade projection, keyed by token with one row per token side.

Pair OHLCV

Roll a pair’s trades into candlesticks for charting.

Trading & Charting

The use case this per-pair trade feed powers.

Token Analytics

Pool-level volume and trade flow for token analytics.