Token Metadata

CREATE TABLE recipe_token_metadata.fact_token_metadata
(
    vendor_event_id     String,
    ingested_at         DateTime64(3),
    chain_id            UInt32,
    block_hash          String,
    block_number        UInt64,
    transaction_index   UInt32,
    event_ts            DateTime64(3),
    token_address       String,
    deployer_address    String,
    name                String,
    symbol              String,
    decimals            Int16,
    total_supply        String,                     -- raw uint256, no decimals applied
    sign                Int8
)
ENGINE = ReplicatedCollapsingMergeTree(
    '/clickhouse/tables/{database}/fact_token_metadata', '{replica}', sign)
PARTITION BY (chain_id, toYYYYMM(event_ts))
ORDER BY (chain_id, token_address, block_number, transaction_index, vendor_event_id);

-- 1. Observations (sink target) — append-only, one row per deploy.
CREATE TABLE public.token_metadata_observations (
  position           BIGINT          NOT NULL,
  transaction_index  BIGINT,
  block_number       BIGINT          NOT NULL,
  block_timestamp    BIGINT          NOT NULL,    -- unix seconds
  tx_hash            TEXT            NOT NULL,
  vendor_event_id    TEXT            NOT NULL,
  token_address      TEXT            NOT NULL,
  deployer_address   TEXT            NOT NULL,
  name               TEXT            NOT NULL,
  symbol             TEXT            NOT NULL,
  decimals           SMALLINT        NOT NULL,
  total_supply       NUMERIC(76, 0)              -- raw uint256, no decimals applied
);

-- Speeds the DISTINCT ON (latest-per-token) the materialized view computes.
CREATE INDEX IF NOT EXISTS tmo_token_recency_idx
  ON public.token_metadata_observations
  (token_address, block_number DESC, transaction_index DESC);

-- 2. Current-metadata materialized view: latest deploy per token_address.
CREATE MATERIALIZED VIEW public.token_metadata AS
SELECT DISTINCT ON (token_address)
  token_address,
  name,
  symbol,
  decimals,
  total_supply,
  block_number,
  deployer_address
FROM public.token_metadata_observations
ORDER BY token_address, block_number DESC, transaction_index DESC;

-- Required by REFRESH MATERIALIZED VIEW CONCURRENTLY (one row per token).
CREATE UNIQUE INDEX IF NOT EXISTS token_metadata_pk
  ON public.token_metadata (token_address);

-- Lookup helper.
CREATE INDEX IF NOT EXISTS token_metadata_symbol_idx
  ON public.token_metadata (symbol);