pinax-network / substreams-raw-blocks

Substreams Raw Blocks
MIT License
1 stars 2 forks source link

Replace `token_balances` with `account_activity` table #19

Closed zolting closed 3 weeks ago

zolting commented 3 weeks ago

This PR proposes replacing the token_balances table with the account_activity table. The key changes and rationale are as follows:

  1. Purpose: The token_balances table was originally intended to represent the pre_token_balances and post_token_balances fields from the transactions table.

  2. Redundancy: Upon review, it was determined that the account_activity table already serves this purpose, covering both balances and token balances.

  3. Data Structure: The pre_token_balances and post_token_balances fields in the transactions table have a complex type: array(row(account varchar, mint varchar, owner varchar, amount decimal(38,18))).

  4. Alternative Approach: Instead of using complex types, we could represent this data as a string in the transactions table:

    CREATE TABLE IF NOT EXISTS transactions (
     -- other fields ....
     pre_token_balances  String,
     post_token_balances String,
     -- other fields ...
    )

    The string format could be: (account1, mint1, owner1, amount1), (account2, mint2, owner2, amount2)

  5. Evaluation: While this approach aligns with Dune's implementation, there's no clear advantage to including these fields in the transactions table. The same information can be more easily accessed through the account_activity table.

Dune query fetching post_token_balances and pre_token_balances in transactions table

Dune query fetching the same info via account_activity table