Portfolio Tracking

​

Who it’s for

​

The recipe bundle

​

How the pieces fit

Token Balances by Wallet ──┐
Native Balances          ──┼──►  current holdings  ◄── valued by ──  Token Prices
NFTs by Wallet           ──┘     (per wallet)

• The three balance recipes each unpivot every transfer into per-wallet (from, post-balance) and (to, post-balance) observations. The latest observation per (wallet, asset), argMax over (block_number, log_index), is the live holding. Token and NFT balances are keyed by (chain_id, wallet_address, token_address[, token_id]); native balances drop token_address and tiebreak on native_seq (native transfers carry no log_index).
• Token Prices is the connective tissue: join each fungible balance to its token’s latest mark to value the holding. The recipe ships a carry-forward latest_token_price_dict, so even quiet tokens keep their last on-chain mark: a single dictGet per token.

​

Valuing a wallet’s fungible holdings

SELECT  b.token_address,
        b.current_balance,
        dictGetOrDefault(
          'recipe_token_price_history.latest_token_price_dict', 'usd_price',
          tuple(b.chain_id, b.token_address), '0') AS usd_price
FROM (
  SELECT  chain_id,
          token_address,
          argMax(balance, (block_number, log_index)) AS current_balance
  FROM    recipe_token_balances_by_wallet.fact_balances_by_wallet FINAL
  WHERE   chain_id = 1 AND wallet_address = lower('0x…')
  GROUP BY chain_id, token_address
  HAVING  current_balance != '0' AND current_balance != ''
) AS b;

​

Notes and considerations

• Read canonical state. All three balance facts and Token Prices use the collapsing log-table pattern. Read with FINAL (then argMax) or a sign-aware aggregate, never a bare WHERE sign = 1, or unmerged reorg counter-rows will skew balances.
• Decimals. Balances are raw integer strings. Scale by 10^token_decimals before displaying or valuing; pull decimals from a Token Metadata sync.
• Chains. Token Balances and Token Prices are chain-parametrized (EVM + Solana); set the chain env vars per sync. NFTs by Wallet is EVM (ERC-721 / ERC-1155).
• Native vs token. The native asset is not an ERC-20 and is tracked by its own recipe. Don’t expect it in the token-balances table. Value it against a native-price mark, not the token-price dictionary.
• Pair with other recipes. Add Wallet History for the chronological feed behind a holding, and Token Approvals to surface outstanding allowances in a wallet’s security view.
• Run mode. Run the ClickHouse path in hybrid mode so balances backfill history and then stay current, reorg-safe, at the chain head.

​

Get started