[BUG] solana_utils.daily_balances coerces unobserved SPL token state to zero after lamport-only final activity

[2bwfkjpr25-lab]

Related: #9350, #9829

This issue concerns a state-semantics/data-contract question that is separate from the incremental merge-key and historical duplicate problem.

Description

solana_utils.daily_balances selects the final balance-changing solana.account_activity row per (address, day):

row_number() over (
    partition by address, date_trunc('day', block_time)
    order by block_slot desc, tx_index desc
)

It then applies:

coalesce(post_token_balance, 0) as token_balance

When the final activity changes lamports only, the transaction metadata may contain no token snapshot. In that case, post_token_balance is NULL because token state was not observed in that transaction. The model currently converts that unobserved NULL into a numeric zero.

I am asking maintainers to confirm the intended contract:

1. Is this an end-of-day account-state snapshot?
2. Or is it a snapshot of the final balance-changing activity for each (address, day)?

Current behavior

Confirmed account-day:

• Address: A3DSsYZJWHiwXSQb7P2AbEoaWhpauJLU1PVdTPnzV5s9
• Day: 2026-02-22 UTC

Time (UTC)	Observation
23:59:31	Explicit wSOL observation: post_token_balance = 38.353268459
23:59:55	Final activity: explicit inbound System Program transfer of +1 lamport; token fields are NULL
daily row	Selects the 23:59:55 slot, but stores token_balance = 0 with mint/owner NULL
2026-02-23 00:28:06	A later explicit token observation has the same mint/owner and pre_token_balance = 38.353268459

The final transaction had empty pre/post token-balance metadata, so the raw NULL means “not observed in this transaction,” not “actual token amount is zero.”

A bounded Token/Token-2022 Program audit between the prior token observation and the final activity returned no rows. The later explicit observation retained the same pre-token balance, supporting continuity across EOD.

Dune reproduction:
https://dune.com/queries/8361360

Solana Explorer:
https://explorer.solana.com/tx/4Vm95iQCJ2b5w5SpEcg8otvvGeKMtU9pmDKJTv4s3cpnzkrgT5SSNNX6NDXeJTvZ7SATf2nf72QY1SfvdMPAj8rQ

Model:
https://github.com/duneanalytics/spellbook/blob/main/dbt_subprojects/solana/models/solana_utils/solana_utils_daily_balances.sql

Public documentation:
https://docs.dune.com/data-catalog/curated/balances/solana-daily-balances

Expected behavior

This depends on the intended contract:

• If this is a last-event snapshot, the token fields should remain NULL when the final event did not observe token state, optionally with an observation-status flag, rather than asserting a known zero.
• If this is an EOD state snapshot, SOL and SPL state should be resolved independently. The last lifecycle-valid token state could be carried forward with separate token observation time/source metadata, while respecting CloseAccount, reinitialization, mint/owner changes, and SyncNative.

I am not proposing that every NULL should be blindly forward-filled.

The current coercion makes it impossible for consumers to distinguish:

1. an explicitly observed zero;
2. token state not observed in the transaction;
3. token state not applicable to the account;
4. a closed/inactive token-account lifecycle.

Impacted model(s)

solana_utils.daily_balances
solana_utils.latest_balances (potential downstream impact)

At least one account-day is fully validated. A bounded screening query produced additional candidates, but they have not all been lifecycle-validated, so I am not presenting them as confirmed affected rows or as an impact estimate.

A false zero may affect token-holder filters, portfolio valuation, treasury monitoring, and downstream queries that interpret token_balance = 0 as a confirmed state.

Possible solution

The appropriate implementation depends on the confirmed contract:

• Last-event contract: preserve NULL and expose whether token state was observed.
• EOD-state contract: use independent SOL/token observation clocks, lifecycle-aware state reconstruction, and separate provenance fields.

Could maintainers confirm the intended contract before a code change is proposed?