Example EVM NFT transaction

Covalent HQ has a database of Ethereum and EVM chain transactions and models for many segments. One of their models is called reports.nft_sales_v2. Below is one example from that table.

Covalent field Dotlake field value
date date 2023-05-26 00:20:18
marketplace ?? tofunft
chain_name chain astar_mainnet
block_height number 3647569
tx_hash ?? F0D98EE1CD637CE48C507BB9DA55555412AC3B6C7D682F30D18D0B4C43F10BBB
maker topic1 0435C3947FA770986CCE38B3ED854F92CC83631C
taker topic2 4550CC81DA8EBF802FEB2CAFC028E2314D020F00
token_id hexToDec(topic3) 4501
collection_address address 8B5D62F396CA3C6CF19803234685E693733F9779
collection_name ?? AstarCats
ticker_symbol derived ASTR
nft_token_price args$.transaction.eip1559.value 105
nft_token_price_usd derived 5.262012808228590
gas_usd_price derived 0.00002431556072919350
contract_address ?? EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE

Our goal here is to try to replicate this data based on our Dotlake dataset.

from datetime import datetime
import pandas as pd
import pandas_gbq
import datetime
pd.set_option('display.max_columns', None)
pd.options.display.max_colwidth = 100
pd.set_option('display.width', 120)

from google.auth import _helpers
import google.auth.compute_engine

from google.cloud import bigquery
project_id = "parity-data-infra-evaluation"
project_vm_sa_email = f"vm-shiny-dev-sa@{project_id}.iam.gserviceaccount.com"
client = bigquery.Client(project=project_id, credentials=google.auth.compute_engine.Credentials(service_account_email=project_vm_sa_email))
pandas_gbq.context.project = project_id
pandas_gbq.context.credentials = google.auth.compute_engine.Credentials(service_account_email=project_vm_sa_email)

out =  pandas_gbq.read_gbq("""WITH nft AS (
  SELECT TIMESTAMP_MILLIS(timestamp) as time, chain, number as block_number, ex.hash
    , JSON_EXTRACT_SCALAR(args, '$.transaction.eip1559.value') as nft_token_price
  , CASE WHEN JSON_EXTRACT(ev.data, '$[0].topics[3]') <>'"0x0000000000000000000000000000000000000000000000000000000000000000"' 
    THEN metrics_substrate_etl.hexToDec(SUBSTR(JSON_EXTRACT_SCALAR(ev.data, '$[0].topics[3]'), 3, 64))
    ELSE null
    END AS token_id
    , CASE 
    WHEN JSON_EXTRACT(ev.data, '$[0].topics[3]') = '"0x0000000000000000000000000000000000000000000000000000000000000000"' THEN 'ERC-20'
    WHEN JSON_EXTRACT(ev.data, '$[0].topics[1]') = '"0x0000000000000000000000000000000000000000000000000000000000000000"' THEN 'NFT mint'
    WHEN JSON_EXTRACT(ev.data, '$[0].topics[2]') = '"0x0000000000000000000000000000000000000000000000000000000000000000"' THEN 'NFT burn'
    WHEN JSON_EXTRACT_SCALAR(args, '$.transaction.eip1559.value')='0' THEN 'NFT transfer'
    ELSE 'NFT trade'
    END AS tx_type
    , JSON_EXTRACT_SCALAR(ev.data, '$[0].address') AS collection_address
    , CONCAT('0x', SUBSTR(JSON_EXTRACT_SCALAR(ev.data, '$[0].topics[1]'), 27)) AS maker
    , CONCAT('0x', SUBSTR(JSON_EXTRACT_SCALAR(ev.data, '$[0].topics[2]'), 27)) AS taker
    , JSON_EXTRACT(ev.data, '$[0].topics[0]') AS topic0

    FROM `parity-data-infra-evaluation.dotlake_partition.blocks_polkadot_astar` a,
      UNNEST(extrinsics) AS ex, UNNEST(ex.events) as ev
    WHERE block_date='2023-05-26'
    --and JSON_EXTRACT(ev.data, '$[0].topics[1]') <> '"0x0000000000000000000000000000000000000000000000000000000000000000"'
    --and JSON_EXTRACT(ev.data, '$[0].topics[2]') <> '"0x0000000000000000000000000000000000000000000000000000000000000000"'
    and JSON_EXTRACT(ev.data, '$[0].topics[3]') <> '"0x0000000000000000000000000000000000000000000000000000000000000000"'
    and JSON_EXTRACT(ev.data, '$[0].topics[0]') = '"0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef"'
    order by time
)
select * from nft where tx_type = 'NFT trade' limit 3;
""")
## 
Downloading:   0%|          |
Downloading: 100%|##########|
print(out)
##                               time  chain block_number  \
## 0 2023-05-26 00:20:18.560000+00:00  astar      3647569   
## 1 2023-05-26 01:04:12.301000+00:00  astar      3647785   
## 2 2023-05-26 01:07:48.339000+00:00  astar      3647803   
## 
##                                                                  hash         nft_token_price token_id    tx_type  \
## 0  0x0c8ca2b9ce0b34b2ce467f7cd10aa9533197e96ee92f160f5da17feafefedf2e   105000000000000000000     4501  NFT trade   
## 1  0x2276022b8baa05522e34dc40c50b0a9f76e7e58e67712d5d22f60abfaaf8815b  2090000000000000000000     6162  NFT trade   
## 2  0x01d79b0e4398118ee636dfca7d652261b1632e50bea87d67e44e4b670b203756   370000000000000000000     2843  NFT trade   
## 
##                            collection_address                                       maker  \
## 0  0x8b5d62f396ca3c6cf19803234685e693733f9779  0x0435c3947fa770986cce38b3ed854f92cc83631c   
## 1  0xd59fc6bfd9732ab19b03664a45dc29b8421bda9a  0xc5f3dd507ce1b9be6adfb89331d48800383b736d   
## 2  0x1c8f73a3d0b23033e214b708b7fbc6f68fefe21a  0x9579d2aa365040f5a3c333bcd38f53ea37a54710   
## 
##                                         taker                                                                topic0  
## 0  0x4550cc81da8ebf802feb2cafc028e2314d020f00  "0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef"  
## 1  0xabf593026b4e20eb524e13203da7cc65810fce1a  "0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef"  
## 2  0xbad8e76fcaa157936e944f99574e720831fb8473  "0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef"

The above query gives us most of the important information we need.

One item I can’t figure out is the EVM transaction hash. It seems like that would be easy to find, but I have not been able to find it yet. However, I am not sure what we would need that for anyways.

A second item I can’t figure out is the contract address (not to be confused with the collection address, which we do have). Subscan has a contracts dashboard that shows all the EVM contracts on a parachain. It would be nice if we could figure out how to replicate this. I can’t find any storage function that would give me that information. If we knew the contract details we could probably use those to get details about the NFT collection such as the collection name, collection symbol, collection size.

Finally, how do we determine which marketplace the NFT was sold on? In this example it was TofuNFT.

There are also a few outstanding questions that we should come to consensus on.

  1. Currently I am assuming that if the maker / from address is null the transaction type is a mint and if the taker / to address is null the transacion type is a burn, but I am not sure if there are any other possible reasons for a null address.

  2. Currently I am working under the assumption that if the price is zero (or null) the transaction type is a transfer, whereas if the price is non-zero, the transaction type is a trade. I don’t know if this assumption holds in all cases.

  3. The Covaent NFT reports dataset only shows transaction types of trade (where the price is non-zero). Do we want to limit out dataset to that as well? I thought it might be interesting to show mint, burn, and transfer transactions as well.