Spice.xyz — Announcing Bitcoin support

Spice.xyz now supports Bitcoin!

Bitcoin blocks, transactions, inputs and outputs can now be queried with SQL over high-performance Apache Arrow APIs. With Spice.xyz’s new multi-chain architecture, queries can span cross-chain and cross-dataset for ecosystem wide insights. This architecture also paves the way to add more chains with Solana and Polygon on our roadmap.

For example, how about tracking wrapped tokens, like WBTC? You can now compare how much BTC was sent to the WBTC DAO custodian addresses versus how much WBTC was minted on the Ethereum network — all within the same query. Let’s take a look at the first half of this year.

The chart above shows the number of WBTC minted on Ethereum is well within the inflows to the WBTC custodian addresses. That’s reassuring. But what happened around March? It turns out February 28th marks when the WBTC DAO consolidated many of their older addresses which was reported at the time. If we zoom in you can see this in more detail.

Let’s look at what the SQL query might look like:

WITH wbtc_minted AS (
    SELECT SUM(CAST("value" AS NUMERIC)) / 1e8 as wbtc_minted,
           date_trunc('day', TO_TIMESTAMP(block_timestamp)) as block_timestamp
    FROM eth.token_transfers_erc20
    WHERE token_address ='0x2260fac5e5542a773aa44fbcfedf7c193bc2c599'
      AND from_address ='0x0000000000000000000000000000000000000000'
      AND block_timestamp BETWEEN UNIX_TIMESTAMP('2022-01-01 00:00:00') AND UNIX_TIMESTAMP('2022-06-30 23:59:59')
    GROUP BY block_timestamp
), wbtc_custodian_addresses_received AS (
    SELECT SUM(CAST("value" AS NUMERIC)) / 1e8 as custodian_btc_received,
           date_trunc('day', TO_TIMESTAMP(block_timestamp)) as block_timestamp
    FROM btc.transaction_outputs
    WHERE block_timestamp BETWEEN UNIX_TIMESTAMP('2022-01-01 00:00:00') AND UNIX_TIMESTAMP('2022-06-30 23:59:59')
      AND addresses[0] IN (SELECT * FROM btc.wbtc_custodian_addresses)
    GROUP BY block_timestamp
), wbtc_custodian_addresses_sent AS (
    SELECT SUM(CAST("value" AS NUMERIC)) / 1e8 as custodian_btc_sent,
           date_trunc('day', TO_TIMESTAMP(block_timestamp)) as block_timestamp
    FROM btc.transaction_inputs
    WHERE block_timestamp BETWEEN UNIX_TIMESTAMP('2022-01-01 00:00:00') AND UNIX_TIMESTAMP('2022-06-30 23:59:59')
      AND addresses[0] IN (SELECT * FROM btc.wbtc_custodian_addresses)
    GROUP BY block_timestamp
), joined_tables AS (
    SELECT COALESCE(custodian_btc_received, 0) as custodian_btc_received,
           COALESCE(custodian_btc_sent, 0) as custodian_btc_sent,
           COALESCE(wbtc_minted, 0) as wbtc_minted,
           COALESCE(wm.block_timestamp, wcr.block_timestamp, wcs.block_timestamp) as block_timestamp
    FROM wbtc_minted wm
    FULL OUTER JOIN wbtc_custodian_addresses_received wcr ON wm.block_timestamp=wcr.block_timestamp
    FULL OUTER JOIN wbtc_custodian_addresses_sent wcs ON COALESCE(wm.block_timestamp, wcr.block_timestamp)=wcs.block_timestamp
)
SELECT custodian_btc_received,
       custodian_btc_sent,
       wbtc_minted,
       custodian_btc_received - custodian_btc_sent - wbtc_minted AS wbtc_reserve_change,
       block_timestamp
FROM joined_tables
ORDER BY block_timestamp ASC;

It might look like a lot at first, but it’s pretty simple when you break it down. We’re creating three temporary tables wbtc_minted, wbtc_custodian_addresses_received, and wbtc_custodian_addresses_sent and joining them into joined_tables for the final result.

Bitcoin support in Spice.xyz marks an important step to provide data and AI infrastructure for the web3 ecosystem and the first of more chains to come in our multi-chain journey.

Thanks for reading our July update. You can read detailed release notes at docs.spice.xyz. This is just the start. We’re growing the team, supporting even more chains, like Polygon and Solana, and working to make it even easier to leverage web3 data and AI in your applications.

Learn More and Contribute

Even for advanced developers, building intelligent apps that leverage AI is still way too hard. Our mission is to make this as easy as creating a modern web page.

If that excites you, join us!

Try out Spice.xyz, get in touch on Discord, or reach out on Twitter.

Luke