Package Logo
clickhouse_polymarket
clickhouse_polymarket@v0.1.2
Total Downloads
5
Published
2 hours ago
Network
polygon polygon
Publisher
Pinax

Readme

Clickhouse Polymarket

This directory contains the ClickHouse schema for ingesting Polymarket event data from Substreams.

Schema Structure

The schema is organized into layers, with numbered prefixes indicating the dependency order:

Layer 0: Foundation (schema.0.*)

  • schema.0.blocks.sql - Block metadata table
  • schema.0.templates.sql - Template tables for transactions and logs that other tables inherit from

Layer 1: Event Tables (schema.1.*)

Event-specific tables that extend the template tables:

  • schema.1.conditional_tokens.sql - ConditionalTokens contract events (ConditionPreparation, ConditionResolution, PositionSplit, PositionsMerge, PayoutRedemption)
  • schema.1.ctf_exchange.sql - CTFExchange contract events (OrderFilled, OrdersMatched, FeeCharged, etc.)
  • schema.1.fee_module.sql - FeeModule contract events
  • schema.1.negrisk_adapter.sql - NegRiskAdapter contract events
  • schema.1.safe_proxy_factory.sql - SafeProxyFactory contract events
  • schema.1.uma_ctf_adapter.sql - UmaCtfAdapter contract events

Layer 2: Materialized Views (schema.2.mv.*)

AggregatingMergeTree tables with materialized views for real-time aggregation:

  • schema.2.mv.state_open_interest.sql - Open interest aggregated by condition and time interval
  • schema.2.mv.state_orderbook.sql - Order book metrics aggregated by asset and time interval
  • schema.2.mv.state_user_condition_position.sql - User positions by condition (from splits/merges/redemptions)
  • schema.2.mv.state_user_position.sql - User positions by token (from exchange trades)

Layer 3: Views (schema.3.view.*)

Convenience views that query the aggregated state tables:

  • schema.3.view.open_interest.sql - Open interest views (per-condition and global)
  • schema.3.view.orderbook.sql - Order book views (per-asset and global)
  • schema.3.view.user_condition_position.sql - User condition position views
  • schema.3.view.user_position.sql - User position views with PNL calculations

Time Intervals

The materialized views aggregate data at multiple time intervals:

  • 1 minute (1m)
  • 5 minutes (5m)
  • 10 minutes (10m)
  • 30 minutes (30m)
  • 1 hour (60m)
  • 4 hours (240m)
  • 1 day (1440m)
  • 1 week (10080m)

USDC Decimals

USDC has 6 decimals. The views provide both raw amounts (in base units) and scaled amounts (divided by 10^6) for convenience.

Documentation

Modules

Maps icon
Maps

map
ctf_exchange:map_events

33202d26f7b7aa284fd1b6808478291eb93a981f
map ctf_exchange:map_events (
paramsstring
)  -> ctf_exchange.v1.Events
Default param : evt_addr:0x4bfb41d5b3570defd03c39a9a4d8de6bd8b8982e || evt_addr:0xc5d563a36ae78145c45a50134d48a1215220f80a
substreams gui clickhouse-polymarket@v0.1.2 ctf_exchange:map_events

map
uma_ctf_adapter:map_events

ab4e958195c8e3c418ef15d83ddafc6f19f95cbb
map uma_ctf_adapter:map_events (
paramsstring
)  -> uma_ctf_adapter.v1.Events
Default param : evt_addr:0x2f5e3684cb1f318ec51b00edba38d79ac2c0aa9d || evt_addr:0x6a9d222616c90fca5754cd1333cfd9b7fb6a4f74
substreams gui clickhouse-polymarket@v0.1.2 uma_ctf_adapter:map_events

map
negrisk_adapter:map_events

d60899942a914823ff30a860b0fe3a9c8f6f93d0
map negrisk_adapter:map_events (
paramsstring
)  -> negrisk_adapter.v1.Events
Default param : evt_addr:0xd91e80cf2e7be2e162c6513ced06f1dd0da35296 || evt_addr:0xf16a3bdffb7b882e3236243e901f6c5953e2ee0d
substreams gui clickhouse-polymarket@v0.1.2 negrisk_adapter:map_events

map
conditional_tokens:map_events

a6f435c82e49ed0bd48eeaf3e3340308634fa813
map conditional_tokens:map_events (
paramsstring
)  -> conditional_tokens.v1.Events
Default param : evt_addr:0x4d97dcd97ec945f40cf65f87097ace5ea0476045
substreams gui clickhouse-polymarket@v0.1.2 conditional_tokens:map_events

map
safe_proxy_factory:map_events

cc82d7882f2e7313db6896b0226d5e5f52cd2207
map safe_proxy_factory:map_events (
paramsstring
)  -> safe_proxy_factory.v1.Events
Default param : evt_addr:0xaacfeea03eb1561c4e67d661e40682bd20e3541b
substreams gui clickhouse-polymarket@v0.1.2 safe_proxy_factory:map_events

map
fee_module:map_events

6b8e712cf987db59f1669ab226fe5a7564ed380f
map fee_module:map_events (
paramsstring
)  -> fee_module.v1.Events
Default param : evt_addr:0xe3f18acc55091e2c48d883fc8c8413319d4ab7b0 || evt_addr:0x56c79347e95530c01a2fc76e732f9566da16e113 || evt_addr:0xb768891e3130f6df18214ac804d4db76c2c37730 || evt_addr:0x78769d50be1763ed1ca0d5e878d93f05aabff29e
substreams gui clickhouse-polymarket@v0.1.2 fee_module:map_events
Protobuf

Protobuf Docs Explorer

safe_proxy_factory.v1
uma_ctf_adapter.v1
fee_module.v1
ctf_exchange.v1
negrisk_adapter.v1
conditional_tokens.v1