Token Prices

CREATE TABLE recipe_token_price_history.fact_token_price_update
(
    vendor_event_id     String,
    ingested_at         DateTime64(3),
    chain_id            UInt32,
    block_hash          String,
    block_number        UInt64,
    event_ts            DateTime64(3),
    token_address       String,
    pair_address        String,
    protocol            LowCardinality(String),
    usd_price           String,                   -- toString(usdPrice), cast at read time
    native_price        String,                   -- toString(nativePrice)
    sign                Int8
)
ENGINE = ReplicatedCollapsingMergeTree(
    '/clickhouse/tables/{database}/fact_token_price_update', '{replica}', sign)
PARTITION BY (chain_id, toYYYYMM(event_ts))
ORDER BY (chain_id, token_address, event_ts, vendor_event_id);

-- OPTIONAL (carry-forward valuation): the freshest USD mark per token, O(1).
-- Refreshes every 30-60s, so a quiet token carries its last on-chain mark
-- forward without a new price event. argMax over FINAL/sign=1 is reorg-correct.
CREATE DICTIONARY recipe_token_price_history.latest_token_price_dict
(
    chain_id        UInt32,
    token_address   String,
    usd_price       String,
    block_number    UInt64,
    event_ts        DateTime64(3)
)
PRIMARY KEY chain_id, token_address
SOURCE(CLICKHOUSE(
    QUERY $$
        SELECT chain_id, token_address,
               argMax(usd_price, (block_number, event_ts)) AS usd_price,
               max(block_number) AS block_number,
               max(event_ts)     AS event_ts
        FROM recipe_token_price_history.fact_token_price_update FINAL
        WHERE sign = 1
        GROUP BY chain_id, token_address
    $$
))
LAYOUT(COMPLEX_KEY_HASHED())
LIFETIME(MIN 30 MAX 60);

CREATE TABLE public.token_price_updates (
  position           BIGINT      NOT NULL,
  log_index          BIGINT,
  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,
  pair_address       TEXT        NOT NULL,
  protocol           TEXT        NOT NULL,
  usd_price          NUMERIC(38, 18),
  native_price       NUMERIC(38, 18)
);

-- By-token price history (the recipe's primary purpose).
CREATE INDEX ON public.token_price_updates (token_address, block_number DESC);
-- Block-range helper.
CREATE INDEX ON public.token_price_updates (block_number);