NFT Trades

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 with 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:

Column	Description
`token_address`	NFT collection address
`token_id`	Token ID traded (`uint256`, stored as text)
`seller_address`	The NFT sender on the trade
`buyer_address`	The NFT recipient on the trade
`amount`	ERC-1155 quantity (`1` for ERC-721)
`price`	Raw price in `price_token_address` units; for bundles, the per-item average
`price_token_address`	Payment token; `''` means native (ETH)
`marketplace_address`	The marketplace contract, the canonical join key for naming
`marketplace`	Marketplace human name when decoded (`''` otherwise)
`tx_hash`, `block_number`, `log_index`, `transaction_index`	On-chain ordering and provenance
`event_ts`	Block 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

Destination	Table	Read pattern
ClickHouse (first-class)	`fact_nft_trades`	Prefix scan on `(chain_id, token_address, token_id, block_number)`; `bloom_filter` skip-indexes on `seller_address` / `buyer_address`; read with `FINAL`
Postgres	`nft_trades`	Indexes on `(token_address, block_number DESC)`, `(token_address, token_id, block_number DESC)`, and seller/buyer
MySQL	`nft_trades`	Same 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.

ClickHouse, fact_nft_trades

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.

Postgres, nft_trades

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, which is 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.

Getting Started

Access Methods

Data APIs & Migration Guides

Streams

Gotchas & Troubleshooting

Concepts

Use Cases

Question it answers

What you get

Source

Destination

Full schema

Example reads

Modes

Multichain

Fidelity gaps

NFT Transfers

NFT Marketplace

​Question it answers

​What you get

​Source

​Destination

​Full schema

​Example reads

​Modes

​Multichain

​Fidelity gaps

​Related

NFT Transfers

NFT Marketplace

Question it answers

What you get

Source

Destination

Full schema

Example reads

Modes

Multichain

Fidelity gaps

Related