[SPELL DESIGN] <something>.tvl

[0xRobin]

Current State of Balances:

We currently employ two models for managing balances:

1. tokens.balances: This table is fully materialized and contains a balance record for each timestamp where a balance update was detected. However, due to the sparse nature of the data, it can be challenging to work with effectively.

2. tokens.balances_daily: This model enhances the sparse raw data in tokens.balances by forward-filling, providing a balance for each day, even when the balance hasn't changed. Additionally, both models have views that enrich the data with token metadata (which will be disregarded for the current discussion).

However, these models are not optimal for complex queries, such as aggregating balances of known Uniswap pools or calculating the average balance over time of a defi lending project.

Goal:

The aim of <something>.TVL (name to be determined) is to create a fully materialized, dense balances table for a specific subset of address <> token pairs. This subset will allow for more flexible querying, as the compute-intensive forward-fill logic won't need to be rerun for each query.

Schema:

The proposed schema aligns with the main balances schema, with the primary axis being day. It includes aggregation level columns (category, project, version) and the primary balance columns (address, token, balance).

Column	Description
day	Timestamp
category
project
version
address
token_address
balance
token info	(symbol, standard, etc.)
price info	(balance_usd)

Spellbook Setup:

The setup involves:

• Curating a <address, token> pair list for each project of interest, preferably through querying decoded contracts or deployment events. Manual curation is an option if automated methods are not feasible.
• Designing a tvl_base macro to execute the forward-fill logic incrementally, taking inputs from the base daily balances table and the address <> token pair list.
• Creating a view that performs a union over all projects and enriches token and price information.

For a visual representation of the proposed setup, refer to the schema image below:

Usage:
get current uniswap TVL:

select sum(balance_usd)
from something.tvl
where day = date_trunc('day',now())
and project = 'uniswap'

compare historical bridge balances

select day, project, sum(balance_usd)
from something.tvl
where category = 'bridge'
group by day, project

Naming:

We'll need to think a bit more about naming and purpose. Suggestions welcome.
Some options:

• projects.tvl
• dex.liquidity
• projects.balances
• protocols.tvl
• ...

considerations:

1. 'TVL' (total value locked) has more connotation than just balances. Not every token balance can be considered TVL, not every TVL can be reduced to a token balance. Doing TVL properly will take more then just getting the right balances of the right tokens. Balances (without the broader connotation from tvl) might be more suited for the initial mvp of this spell.
2. Using an abbreviation as the table name is not ideal.
3. 'liquidity' is only a correct term when thinking about dex balances, and does not apply to other defi categories.
4. 'projects' hasn't been used as a schema before, so extra attention should be given here to the naming and spellbook setup.

Each option has its implications, and careful consideration should be given to aligning the chosen name with the intended purpose and broader context of the project. I think my current preference would got to projects.balances, the projects schema can be expanded upon in the future with spells for daily active users, volume, retention, ...

[andrewhong5297]

catching up here - in this case, for a given uniswap pair it would have two rows in uniswap.tvl_base? also balances is already forward filled so no need to ffill again on downstream models?

[0xRobin]

in this case, for a given uniswap pair it would have two rows in uniswap.tvl_base?

Correct, the base model would have a row for each address and token combo. Other models can be build on top that aggregate the balances based on address, project, version,..

also balances is already forward filled so no need to ffill again on downstream models

balances is only forward filled in balances.daily where the forward fill happens in a view which makes it not suitable for more advanced queries (It'll time out). balances.daily_agg is materialized but does not contain fully filled data

[mendesfabio]

heys guys, very interesting discussion! did you have any progress on it in the past weeks? one suggestion I have for the schema name is protocols which is well-adopted by bridges, decentralized exchanges, lending, etc. Some examples:

A reliable, open-source, permissionless protocol. Build on Balancer to create new innovative types of pools and financial dApps.

The Aave Protocol is decentralised non-custodial liquidity protocol where users can participate as suppliers, borrowers or liquidators

LI.FI is a multi-chain liquidity aggregation protocol that supports any-2-any swaps by aggregating bridges and DEX aggregators across +20 networks.

I also agree liquidity isn't the best term and I do regret creating balancer.liquidity a few years ago 😅 TBH if I had the chance to change it today would do to balancer.tvl since I particularly don't see the problem with the abbreviation, it's short to write, and it's probably the most-known term/metric in crypto.

[0xRobin]

thanks, added your suggestion to the list!
From Dune's side there's currently no plan to start on the implementation, but anyone from the community is free to start working on it!