Our dashboard: https://dune.xyz/3stepcap/QiDao-Vaults
Our Dune page: https://dune.xyz/3stepcap
Need some data engineering or visualization work for your project? š @0x3step
Qidao received a grant from the Polygon Foundation to perform Data Engineering and Dashboard Construction using the Dune Analytics platform. Three Step was selected to perform the work and delivered the completed result within 1 week of starting the project.
The first step in our process was to understand the data generated by the Qidao smart contracts for different transaction types.
The transaction types are:
Additionally the data for Qidao is separated into 5 event tables for each event type. For example, for the Borrow MAI event there are the tables:
crosschainQiStablecoin_evt_BorrowToken
erc20QiStablecoinwbtc_evt_BorrowToken
QiStablecoin_evt_BorrowToken
CrosschainQiStablecoinV2_evt_BorrowToken
erc20QiStablecoin_evt_BorrowToken
After performing a review of all of the raw data we decided to create a view for each event type, and create a uniform table schema so that each event type view had the same exact structure. That way, we can combine all the event tables into one view that aggregates all of the event types.
The schema we designed looks like:
| Column Name | Column Type | Column Description |
|---|---|---|
| vaultID | numeric | A Vault represents a userās borrow with a specific contract, a vault is an NFT on chain. |
| contract_address | bytea | This is the Qidao contract. Each contract can be used for one collateral asset. |
| evt_tx_hash | bytea | blockchain hash |
| evt_block_time | timestamptz | timestamp of the block |
| evt_block_number | numeric | number of the block |
| evt_index | int8 | evt_index |
| address_one | bytea | if the transaction has an address involved, we added it to this field |
| address_two | bytea | if the transaction has a second address involved, we added it to this field |
| address_one_type | text | This is the role of address_one in the transaction. For example in a transfer, this is āfromā, meaning the address that a vault was transferred out of. |
| address_two_type | text | This is the role of address_two in the transaction. For example in a transfer, this is ātoā, meaning the address that a vault was transferred into. |
| amount_mai | float8 | the amount of MAI in the transaction, positive value for borrows and negative values to pay back. |
| amount_collateral | numeric | the amount of MAI in the transaction, positive value for depositing and negative values to borrowing. |
| closing_fee | numeric | When a user pays back MAI they pay a fee. |
| approved_bool | bool | T/F for approval transactions |
| TokenId | numeric | TokenId from events (if there was a TokenId, we also added its value to the vaultID field since the meaning is the same) |
| transaction_type | text | name of the event, i.e. deposit collateral, approval, etc. |
| interaction_type | text | erc20,base,crosschain,etc |
Now that we have one table with a uniform schema for each transaction, things are much easier to work with. Next we want to create a table that has useful information about users and their interactions with Qidao using Vaults.
We therefore created a table with the following structure:
| Column Name | Column Type | Column Description |
|---|---|---|
| date | date | date based on "evt_block_timeā of txs |
| collateral_token_symbol | text | symbol of collateral asset |
| collateral_token_contract | bytea | token contract of collateral asset |
| change_in_collateral_amount | float8 | sum of āamount_collateralā field for transactions with a block |
| cumulative_collateral_amount | float8 | running total of ārunning total of ācumulative_amount_maiā |
| collateral_amount_usd | float8 | ācumulative_amount_maiā * ācollateral_priceā |
| change_in_mai_amount | float8 | sum of āamount_maiā field for transactions with a block |
| cumulative_amount_mai | float8 | running total of ācumulative_amount_maiā |
| collateral_price | float8 | AVG price for the collateral token for that day based on the prices.āusdā table. For some tokens (i.e. camWBTC a price was missing, in which case we substituted the closest comparable coin, i.e. WBTC). |
| collateral_ratio | float8 | (collateral_amount_usd/cumulative_amount_mai) * 100 |