NFT Collection Metadata

CREATE TABLE recipe_nft_collection_metadata.fact_nft_collection_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,
    contract_type       LowCardinality(String),   -- ERC721 | ERC1155 | NFT
    sign                Int8
)
ENGINE = ReplicatedCollapsingMergeTree(
    '/clickhouse/tables/{database}/fact_nft_collection_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): one row per NFT-contract deploy.
CREATE TABLE public.nft_collection_metadata_observations (
  position           BIGINT          NOT NULL,    -- block-level cursor used during backfill
  transaction_index  BIGINT,
  block_number       BIGINT          NOT NULL,
  block_timestamp    BIGINT          NOT NULL,    -- unix seconds
  tx_hash            VARCHAR(66)     NOT NULL,
  vendor_event_id    TEXT            NOT NULL,
  token_address      VARCHAR(66)     NOT NULL,
  deployer_address   VARCHAR(66)     NOT NULL,
  name               TEXT            NOT NULL,
  symbol             TEXT            NOT NULL,
  contract_type      VARCHAR(16)     NOT NULL     -- ERC721 | ERC1155 | NFT
);

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

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

-- Required by REFRESH MATERIALIZED VIEW CONCURRENTLY (one row per collection).
CREATE UNIQUE INDEX IF NOT EXISTS nft_collection_metadata_pk
  ON public.nft_collection_metadata (token_address);

-- Lookup helpers.
CREATE INDEX IF NOT EXISTS nft_collection_metadata_symbol_idx
  ON public.nft_collection_metadata (symbol);
CREATE INDEX IF NOT EXISTS nft_collection_metadata_type_idx
  ON public.nft_collection_metadata (contract_type);