Native Balances

CREATE TABLE recipe_native_balances.fact_native_balances
(
    vendor_event_id   String,
    ingested_at       DateTime64(3),
    chain_id          UInt32,
    block_hash        String,
    block_number      UInt64,
    transaction_index UInt32,                 -- informational only (not unique per native transfer)
    native_seq        UInt32,                 -- 1-based in-block ordinal; recency tiebreaker
    event_ts          DateTime64(3),
    wallet_address    String,
    balance           String,                 -- absolute post-transfer native balance, raw uint256 (wei)
    leg               LowCardinality(String), -- from | to
    sign              Int8
)
ENGINE = ReplicatedCollapsingMergeTree(
    '/clickhouse/tables/{database}/fact_native_balances', '{replica}', sign)
PARTITION BY (chain_id, toYYYYMM(event_ts))
ORDER BY (chain_id, wallet_address, block_number, native_seq, vendor_event_id, leg);

-- Observations (sink target).
CREATE TABLE public.native_balance_observations (
  position           BIGINT  NOT NULL,        -- block-level backfill cursor
  transaction_index  BIGINT  NOT NULL,        -- informational only
  native_seq         BIGINT  NOT NULL,        -- 1-based in-block ordinal; recency tiebreaker
  block_number       BIGINT  NOT NULL,
  block_timestamp    BIGINT  NOT NULL,        -- unix seconds
  wallet_address     TEXT    NOT NULL,
  balance            NUMERIC(76, 0) NOT NULL, -- absolute native balance (wei)
  leg                TEXT    NOT NULL,        -- from | to
  vendor_event_id    TEXT    NOT NULL
);

-- Recency index for the DISTINCT ON; leads with the DISTINCT ON key so
-- REFRESH MATERIALIZED VIEW CONCURRENTLY avoids a sort.
CREATE INDEX nbo_wallet_recency_idx
  ON public.native_balance_observations
  (wallet_address, block_number DESC, native_seq DESC);

-- Current-balance materialized view: latest observation per wallet.
CREATE MATERIALIZED VIEW public.native_balances AS
SELECT DISTINCT ON (wallet_address)
  wallet_address, balance, block_number, native_seq, transaction_index, block_timestamp
FROM public.native_balance_observations
ORDER BY wallet_address, block_number DESC, native_seq DESC;

CREATE UNIQUE INDEX native_balances_pk ON public.native_balances (wallet_address);

-- Active (non-zero) native balances — this recipe's primary access path.
CREATE INDEX native_balances_active_idx
  ON public.native_balances (wallet_address) WHERE balance > 0;
-- Cleanup index for zeroed wallets.
CREATE INDEX native_balances_zero_cleanup_idx
  ON public.native_balances (wallet_address) WHERE balance = 0;