Real-Time Crypto Data: Meet Flipside LiveQuery

At block 16,000,000 ETH was trading on mainnet for roughly $1,207. Today’s piece will show you how we turned back the clock and got to this number!

Crypto data pipeline 101

1. Users submit transactions to a designated node network (e.g., Infura the built-in default for the Metamask wallet, Alchemy, Quicknode, or their own personal node) that then transmits the transaction to other nodes peer to peer (“gossip”) until it is included in a block.

2. Blocks reach finality when enough nodes come to consensus that the block and the transactions in it are valid.

3. Transactions interact with smart contracts which emit events (Alice sent Bob 5 ETH) and have traces (internal actions, e.g., Alice’s ETH balance reduced by 5) and logs (the order of actions and explicit details).

4. Data companies monitor these processes and make a spectrum of data available:

   • pre-transaction data: the transactions being gossiped, often called the ‘mempool’.

   • transaction data: events, logs, traces all decoded and human readable.

   • post-transaction data: the “state” of the chain after a block of transactions, e.g., Alice’s balance after the transfer to Bob.

These data vary in rawness, human readability (0xf42400 → 16 million), velocity, size, etc. Different data for different purposes. If you want to pause and deep dive check out our piece: wtf-traces where we covered events, logs, and traces in our review of our MakerDAO Schema.

I’ll take my data medium rare

If you’re interested in the daily users of a blockchain, your unit of time would be ‘day’.

The Ethereum blockchain makes a block roughly every 12 seconds (i.e., 7000+ blocks a day).

At Flipside Crypto we take raw data from nodes and clean it in a variety of ways.

To add a transaction to our EZ_NFT_SALES data, we need to check NFT contract events, identify which platform the trade happened on (e.g. OpenSea), parse out total payment vs royalties vs platform fee vs gas fee paid.

We even bring in off-chain data like the project’s name. We look at the amount of events to treat bulk sales differently than single sales (we don’t want to double count transfer amounts and inflate the total purchase price).

This takes time!

We run between 15 - 60 minute lags on highly curated data (link is our data transparency dashboard). This is great for some use cases (e.g., trend analytics for an NFT collection) and not so great for others (monitoring hacks in real time).

To give users the power to merge real-time and curated data, we created LiveQuery. A structured way to bring external data into your Flipside Crypto SQL environment.

Quick(node) Registration

1. Get a generous API key for free from Quicknode where you can choose a blockchain (I will use Ethereum for this depo) and access millions of calls per month!

Quicknode will provide you an easy to copy HTTP link with your network (mine is maximum-dark-paper) and secret (letters and numbers)

2. Register your name-secret pair with your Flipside Crypto account (only YOU will be able to access this secret) using our Ephemeral key manager: “Ephit”. You’ll need to retain access to both your service name (mine: charlie-quicknode) and the secret name (eth-mainnet-url)

3. You’ll see a custom SQL query pop-up after you submit your credential. While logged into the Flipside app open a new query and copy/paste/run. It will return success: true and you’re good to go!

You now have your credentials registered to your Flipside account only. Making your query safe to share.

The Time Travel Example

This is very powerful stuff. Quicknode and other RPC providers enable all kinds of communication with nodes including submitting transactions, not just asking about them. It’s the backbone of how Web2 internet (i.e., websites) connect to web3. The Quicknode documentation is worth skimming separately.

For now, we’ll start with a basic-ish use of the eth_call option in Quicknode to read a smart contract as of a specific blockheight.

For this example of eth_call we’ll need to create an RPC request and pass our parameters (contract, function, block #) as inputs. I like to follow this process inside out (request → input → result) instead of top down (input → request → result) but both work.

1. Using the ETH-USDC 0.05% pool address 0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640

2. I want to call the slot0 function which holds something called the sqrtPriceX96, but I want it as of block 16,000,000 (Nov 18, 2022) not changing constantly.

For those curious about Uniswap v3, you can check out our full v3 explainer which breaks down liquidity, contract math, and decoding these things into human readable numbers using a real world pool.

3. I’m going to frame a CTE called create_rpc_request where I know I’ll use the contract_address and pass data detailing my request (slot0) at a block_height. This uses the livequery.utils.udf_json_rpc_call() function.

create_rpc_request as (
SELECT
  contract_address,
   livequery.utils.udf_json_rpc_call(
          'eth_call',
          [ { 'to': contract_address,
              'from': null,
              'data': data
             },
           block_height]
   ) AS rpc_request
FROM
   inputs
),

4. Since this is a simple read, I don’t need to make a full transaction with gas paid or a private key signature. A simple inputs CTE will be set above this one to bring in the contract address, formatted data, and block_height.

WITH inputs AS (
   SELECT
    LOWER('0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640')
       AS contract_address,
    '0x3850c7bd'
       AS function_sig,
    CONCAT('0x',trim(to_char(16000000,'XXXXXXXXX')))
       AS block_height,
    CONCAT(
            function_sig,
            LPAD(REPLACE('', '0x', ''), 64, 0)
           )
       AS DATA
   ),

The contract address is ETH-USDC 0.05% Uniswap v3. I took that from the analytics page of Uniswap where you can find TVL, volume, fees and other information about pools. Slot0() → 0×3850c7bd is taken from our evmsig database of function signatures. Because Ethereum uses a lot of hex codes and expects 64 length characters, we pad this signature (this function doesn’t need its own inputs otherwise we’d concatenate those in the data too) to get to the right length.

5. With the rpc request ready, let’s make our API call using our registered secret! I’ll store this in the last base CTE so that we can clean the result as the last step. livequery.live.udf_api() let’s us make a POST request to Quicknode {eth-mainnet-url} using our named secret charlie-quicknode.

Note: this only works from my account! So I am not worried about you copying this query and using up my credits.

base AS (
  SELECT
      livequery.live.udf_api(
               'POST',
              '{eth-mainnet-url}', -- {my secret URL}
               {},  -- empty header
               rpc_request,
              'charlie-quicknode' -- my secret name
            ) AS api_call
     FROM
            create_rpc_request
   )

6. We did it! The last step is to grab the api_call:data:result and make it human readable. Since this all lives within my query, I can make additional CTEs from our expansive ETH data and use this result to filter, join, and advance my analytics.

7. For completeness, this code will include the ETH-USDC 0.05% pool specific conversion method, see the v3 explainer for specific Uniswap details on converting between these values.

SELECT api_call:data:result AS result,

-- drop '0x' and break into chunks of 64 characters
regexp_substr_all(SUBSTR(result, 3, len(result)), '.{64}')
     AS segmented_result,

-- block height we used
(SELECT block_height from inputs)
     AS block_height,

-- Flipside function for hex to integer
ethereum.public.udf_hex_to_int(segmented_result[0])::integer
     AS sqrtPX96,

-- wei (18 decimals) / usdc (6 decimals) -> USDC/ETH
 POWER(sqrtPX96 / POWER(2, 96), -2)*1e12
     AS human_price
FROM

     base;

Links for more

This is definitely one of the more technical deep dives, especially if you haven’t worked with JSON or making API requests. Here’s the sample query, note you need to switch charlie-quicknode and eth-mainnet-url to your registered Quicknode secret to use their eth_call implementation.

1. Sample Query

2. Quicknode Endpoint Registration

3. Ephemeral-it

4. EVM Signature Database

5. Flipside Livequery repo w/ more snippets

Have questions? Want to meet other crypto analysts? Join our discord community & check out the show-and-tell!