Token Approvals

CREATE TABLE recipe_token_approvals.fact_token_approvals
(
    vendor_event_id   String,
    ingested_at       DateTime64(3),
    chain_id          UInt32,
    block_hash        String,
    block_number      UInt64,
    log_index         UInt32,
    event_ts          DateTime64(3),
    tx_hash           String,
    token_address     String,
    owner_address     String,
    spender_address   String,
    value             String,                 -- raw allowance; unlimited approve = uint256 max (78 digits)
    sign              Int8
)
ENGINE = ReplicatedCollapsingMergeTree(
    '/clickhouse/tables/{database}/fact_token_approvals', '{replica}', sign)
PARTITION BY (chain_id, toYYYYMM(event_ts))
ORDER BY (chain_id, owner_address, token_address, spender_address, block_number, log_index, vendor_event_id);

-- 1. Approval events (sink target) — one row per Approval log.
CREATE TABLE public.token_approval_events (
  position         BIGINT  NOT NULL,
  log_index        BIGINT  NOT NULL,
  block_number     BIGINT  NOT NULL,
  block_timestamp  BIGINT  NOT NULL,          -- unix seconds
  tx_hash          TEXT    NOT NULL,
  token_address    TEXT    NOT NULL,
  owner_address    TEXT    NOT NULL,
  spender_address  TEXT    NOT NULL,
  value            TEXT    NOT NULL,           -- allowance; max-uint = 78 digits
  vendor_event_id  TEXT    NOT NULL
);

-- Recency index leading with the DISTINCT ON keys so REFRESH avoids a sort.
CREATE INDEX tae_owner_token_spender_recency_idx
  ON public.token_approval_events
  (owner_address, token_address, spender_address, block_number DESC, log_index DESC);

-- 2. Current-allowance materialized view: latest approve per (owner, token, spender).
CREATE MATERIALIZED VIEW public.token_allowances AS
SELECT DISTINCT ON (owner_address, token_address, spender_address)
  owner_address, token_address, spender_address,
  value, tx_hash, block_number, log_index, block_timestamp
FROM public.token_approval_events
ORDER BY owner_address, token_address, spender_address, block_number DESC, log_index DESC;

CREATE UNIQUE INDEX token_allowances_pk
  ON public.token_allowances (owner_address, token_address, spender_address);

-- Primary access path: all live allowances granted by an owner.
CREATE INDEX token_allowances_by_owner_active_idx
  ON public.token_allowances (owner_address, token_address, spender_address)
  WHERE value <> '0';

-- Sibling access path: who can spend a given token on behalf of others.
CREATE INDEX token_allowances_by_spender_idx
  ON public.token_allowances (spender_address, token_address);