
This directory contains the ClickHouse schema for ingesting Polymarket event data from Substreams.
The schema is organized into layers, with numbered prefixes indicating the dependency order:
schema.0.*)schema.0.blocks.sql - Block metadata tableschema.0.templates.sql - Template tables for transactions and logs that other tables inherit fromschema.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 eventsschema.1.negrisk_adapter.sql - NegRiskAdapter contract eventsschema.1.safe_proxy_factory.sql - SafeProxyFactory contract eventsschema.1.uma_ctf_adapter.sql - UmaCtfAdapter contract eventsschema.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 intervalschema.2.mv.state_orderbook.sql - Order book metrics aggregated by asset and time intervalschema.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)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 viewsschema.3.view.user_position.sql - User position views with PNL calculationsThe materialized views aggregate data at multiple time intervals:
USDC has 6 decimals. The views provide both raw amounts (in base units) and scaled amounts (divided by 10^6) for convenience.
substreams gui clickhouse-polymarket@v0.1.2 db_outsubstreams gui clickhouse-polymarket@v0.1.2 ctf_exchange:map_eventssubstreams gui clickhouse-polymarket@v0.1.2 uma_ctf_adapter:map_eventssubstreams gui clickhouse-polymarket@v0.1.2 negrisk_adapter:map_eventssubstreams gui clickhouse-polymarket@v0.1.2 conditional_tokens:map_eventssubstreams gui clickhouse-polymarket@v0.1.2 safe_proxy_factory:map_eventssubstreams gui clickhouse-polymarket@v0.1.2 fee_module:map_eventsmessage
Events
message
Transaction
message
Log
message
ConditionPreparation
message
ConditionResolution
message
PositionSplit
message
PositionsMerge
message
PayoutRedemption
message
Events
message
Transaction
message
Log
message
ProxyCreation
message
ProxyCreationL2
message
ChainSpecificProxyCreationL2
message
FileDescriptorSet
message
FileDescriptorProto
message
DescriptorProto
message
ExtensionRangeOptions
message
FieldDescriptorProto
message
OneofDescriptorProto
message
EnumDescriptorProto
message
EnumValueDescriptorProto
message
ServiceDescriptorProto
message
MethodDescriptorProto
message
FileOptions
message
MessageOptions
message
FieldOptions
message
OneofOptions
message
EnumOptions
message
EnumValueOptions
message
ServiceOptions
message
MethodOptions
message
UninterpretedOption
message
SourceCodeInfo
message
GeneratedCodeInfo
message
Any
message
Timestamp
message
Modules
message
Binary
message
Module
message
Package
message
NetworkParams
message
PackageMetadata
message
ModuleMetadata
message
Clock
message
BlockRef
message
Request
message
Response
message
BlockUndoSignal
message
BlockScopedData
message
SessionInit
message
InitialSnapshotComplete
message
InitialSnapshotData
message
MapModuleOutput
message
StoreModuleOutput
message
OutputDebugInfo
message
ModulesProgress
message
ProcessedBytes
message
Error
message
Job
message
Stage
message
ModuleStats
message
ExternalCallMetric
message
StoreDelta
message
BlockRange
message
Events
message
Transaction
message
Log
message
AncillaryDataUpdated
message
NewAdmin
message
QuestionEmergencyResolved
message
QuestionFlagged
message
QuestionInitialized
message
QuestionPaused
message
QuestionReset
message
QuestionResolved
message
QuestionUnpaused
message
RemovedAdmin
message
QuestionUnflagged
message
Events
message
Transaction
message
Log
message
FeeRefunded
message
FeeWithdrawn
message
NewAdmin
message
RemovedAdmin
message
DeployRequest
message
Parameter
message
DeployResponse
message
UpdateRequest
message
UpdateResponse
message
InfoRequest
message
InfoResponse
message
SinkProgress
message
PackageInfo
message
ListRequest
message
ListResponse
message
DeploymentWithStatus
message
RemoveRequest
message
RemoveResponse
message
PauseRequest
message
PauseResponse
message
StopRequest
message
StopResponse
message
ResumeRequest
message
ResumeResponse
enum
DeploymentStatus
message
DatabaseChanges
message
TableChange
message
CompositePrimaryKey
message
Field
message
Service
message
DBTConfig
message
HasuraFrontend
message
PostgraphileFrontend
message
PGWebFrontend
message
RESTFrontend
message
Events
message
Transaction
message
Log
message
FeeCharged
message
NewAdmin
message
NewOperator
message
OrderCancelled
message
OrderFilled
message
OrdersMatched
message
ProxyFactoryUpdated
message
RemovedAdmin
message
RemovedOperator
message
SafeFactoryUpdated
message
TokenRegistered
message
StoreToken
message
TradingPaused
message
TradingUnpaused
message
Events
message
Transaction
message
Log
message
MarketPrepared
message
NewAdmin
message
OutcomeReported
message
PayoutRedemption
message
PositionSplit
message
PositionsConverted
message
PositionsMerge
message
QuestionPrepared
message
RemovedAdmin