Skip to main content

​
Question it answers

“Give me every NFT marketplace trade — for collection 0x…, for a single (collection, token_id), or for wallet 0x… as seller or buyer.”
A single sync serves all three access paths. It mirrors the on-chain subset of Moralis GET /nft/{address}/trades (by collection), GET /nft/{address}/{token_id}/trades (single token), and GET /wallets/{address}/nfts/trades (by wallet). It is an on-chain trade log: no off-chain enrichment — no USD price, no reliable marketplace human-name, no address labels.

​
What you get

One row per traded NFT — a bundle/batch fill that moves several NFTs lands one row per (trade, token_id), each carrying the bundle’s per-item average price:
ColumnDescription
token_addressNFT collection address
token_idToken ID traded (uint256, stored as text)
seller_addressThe NFT sender on the trade
buyer_addressThe NFT recipient on the trade
amountERC-1155 quantity (1 for ERC-721)
priceRaw price in price_token_address units; for bundles, the per-item average
price_token_addressPayment token; '' means native (ETH)
marketplace_addressThe marketplace contract — the canonical join key for naming
marketplaceMarketplace human name when decoded ('' otherwise)
tx_hash, block_number, log_index, transaction_indexOn-chain ordering and provenance
event_tsBlock time
To collapse a bundle back to one logical trade, GROUP BY (tx_hash, log_index) and groupArray(token_id).

​
Source

The recipe consumes the per-block tradeTransactions array, which is fully flattened — each element is one item (an NFT transfer, a token transfer, or an internal tx) for one participant in one trade. A single sale is spread across many rows. The transform re-joins those parts into one trade: it identifies the buyer (a participant who received an NFT and paid), reads the seller and collection off the received-NFT row, derives the price from the buyer’s native or token payment, and emits one row per received NFT.

​
Destination

DestinationTableRead pattern
ClickHouse (first-class)fact_nft_tradesPrefix scan on (chain_id, token_address, token_id, block_number); bloom_filter skip-indexes on seller_address / buyer_address; read with FINAL
Postgresnft_tradesIndexes on (token_address, block_number DESC), (token_address, token_id, block_number DESC), and seller/buyer
MySQLnft_tradesSame indexes (without DESC on the key)
ClickHouse uses the collapsing log-table pattern (see the recipes overview) — every trade row in a block shares the block’s hash, so a per-block reorg corrects all of that block’s trades together. The fact table’s sort key is collection-first, so by-collection is a prefix scan and by-(collection, token_id) is a tighter prefix; by-wallet is served by the bloom-filter indexes. Read canonical state with FINAL or a sign-aware aggregate, never a bare WHERE sign = 1.

​
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). token_id and price are raw uint256 values that exceed numeric precision, so they’re stored as text / wide decimals. 
CREATE TABLE recipe_nft_trades.fact_nft_trades
(
    vendor_event_id     String,
    ingested_at         DateTime64(3),
    chain_id            UInt32,
    block_hash          String,
    block_number        UInt64,
    event_ts            DateTime64(3),
    token_address       String,        -- NFT collection address
    token_id            String,        -- uint256 big decimal, NEVER numeric
    seller_address      String,
    buyer_address       String,
    amount              String,        -- ERC-1155 quantity (1 for ERC-721)
    price               String,        -- raw, in price_token units
    price_token_address String,        -- '' ⇒ native (ETH)
    marketplace         String,        -- human name when decoded ('' otherwise)
    marketplace_address 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_seller seller_address TYPE bloom_filter(0.01) GRANULARITY 4,
    INDEX bf_buyer  buyer_address  TYPE bloom_filter(0.01) GRANULARITY 4
)
ENGINE = ReplicatedCollapsingMergeTree(
    '/clickhouse/tables/{database}/fact_nft_trades', '{replica}', sign)
PARTITION BY (chain_id, toYYYYMM(event_ts))
ORDER BY (chain_id, token_address, token_id, block_number, vendor_event_id);
The sign column drives reorg collapsing — read with FINAL or sum(sign). A single-node setup can use CollapsingMergeTree(sign) without the replication path. The seller/buyer bloom-filter indexes ship enabled because by-wallet is a first-class access path here.
CREATE TABLE public.nft_trades (
  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,    -- NFT collection address
  token_id             TEXT        NOT NULL,    -- uint256 big decimal, NEVER numeric
  seller_address       TEXT        NOT NULL,
  buyer_address        TEXT        NOT NULL,
  amount               NUMERIC(76, 0)  NOT NULL,  -- ERC-1155 quantity (1 for ERC-721)
  price                NUMERIC(76, 0)  NOT NULL,  -- raw, in price_token units
  price_token_address  TEXT        NOT NULL,    -- '' ⇒ native (ETH)
  marketplace          TEXT        NOT NULL,    -- human name when decoded ('' otherwise)
  marketplace_address  TEXT        NOT NULL
);

-- By-collection access (the recipe's primary purpose).
CREATE INDEX IF NOT EXISTS nft_trades_token_block_idx
  ON public.nft_trades (token_address, block_number DESC);
-- By-token-id access (single NFT sale history).
CREATE INDEX IF NOT EXISTS nft_trades_token_id_block_idx
  ON public.nft_trades (token_address, token_id, block_number DESC);
-- By-wallet access (either side).
CREATE INDEX IF NOT EXISTS nft_trades_seller_block_idx
  ON public.nft_trades (seller_address, block_number DESC);
CREATE INDEX IF NOT EXISTS nft_trades_buyer_block_idx
  ON public.nft_trades (buyer_address, block_number DESC);
-- Block-range helper.
CREATE INDEX IF NOT EXISTS nft_trades_block_idx
  ON public.nft_trades (block_number);
MySQL is the same shape with DECIMAL(76,0) for amount / price and the equivalent keys. position is the block-level cursor used during backfill.

​
Example reads

All trades of an NFT collection, newest first (ClickHouse): 
SELECT block_number, token_id, seller_address, buyer_address,
       price, price_token_address, marketplace_address, tx_hash
FROM recipe_nft_trades.fact_nft_trades FINAL
WHERE chain_id = 1
  AND token_address = lower('0xBC4CA0EdA7647A8aB7C2061c2E118A18a936f13D')
ORDER BY block_number DESC
LIMIT 50;
Sale history of a single token (collection + token_id): 
SELECT block_number, seller_address, buyer_address, price, price_token_address, tx_hash
FROM recipe_nft_trades.fact_nft_trades FINAL
WHERE chain_id = 1
  AND token_address = lower('0xBC4C...')
  AND token_id = '1234'
ORDER BY block_number DESC
LIMIT 50;
All trades involving a wallet (as seller or buyer), bloom-pruned: 
SELECT block_number, token_address, token_id, seller_address, buyer_address, price
FROM recipe_nft_trades.fact_nft_trades FINAL
WHERE chain_id = 1
  AND (seller_address = lower('0x...') OR buyer_address = lower('0x...'))
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 share one block. Array-expanded trade rows can only carry a composite unique, so run realtime/hybrid on ClickHouse (its log table corrects reorgs per-block). The Postgres / MySQL configs are intended for historical backfill.

​
Multichain

The recipe is chain-parametrized — point it at any supported EVM chain or Solana. Solana marketplace trades (Magic Eden, Tensor, and others) populate the same tradeTransactions shape. Solana assigns the same log_index to multiple events in one instruction, so the trade’s identity is widened with (seller, buyer, token_address, token_id) to keep rows distinct; the trade log it produces is identical in shape.

​
Fidelity gaps

The recipe lands the columns the trade array carries. Fields a Moralis NFT trades endpoint surfaces that have no reliable on-chain source here are omitted:
  • USD price. price is the raw amount in price_token_address units (native ETH when price_token_address = ''). Converting to USD needs a same-block Token Prices join or an external price feed — out of scope for this on-chain trade log.
  • Marketplace human name. The marketplace field is frequently empty/unreliable on-chain and is landed as-is ('' when absent). marketplace_address is always present — map it to a human name with an off-chain marketplace directory.
  • Address labels (seller_address_label, buyer_address_label) — off-chain entity tags, not on-chain data.
  • Collection metadata (token_name, token_symbol) — from the NFT contract or a metadata indexer, not the trade event (see NFT Collection Metadata).
  • Smallest-total price-token quirk. When a buyer pays in tokens across multiple token addresses, the price token is chosen as the smallest summed total. This is preserved for parity with the production transformer; it is a likely upstream quirk. Native-paid trades — the common case — are unaffected.

NFT Transfers

Every NFT movement (mints, transfers, burns) — the untraded counterpart to this trade log.

NFT Marketplace

The use case this trade feed powers.