Tables & Schemas

Complete reference for all Probalytics database tables. All tables use a ReplacingMergeTree with indexed_at as the version column. Rows are identified by each table’s ORDER BY key; when identical keys are inserted more than once, the greatest indexed_at wins during background merges.

markets

Primary table containing prediction market metadata from all supported platforms. Availability: All ClickHouse tiers, including all retained market history during trials. See Markets for the distinction between account access and source coverage.

Schema

Column	Type	Description
`id`	`UUID`	Probalytics unique market identifier
`platform`	`Enum('POLYMARKET', 'KALSHI')`	Source platform
`platform_id`	`String`	Platform’s native market ID
`slug`	`String`	URL-friendly market identifier
`url`	`String`	Direct link to market on platform
`title`	`String`	Market question/title
`description`	`String`	Full market description text
`category`	`LowCardinality(String)`	Market category (e.g., “Politics”, “Sports”)
`tags`	`Array(LowCardinality(String))`	Associated tags
`market_type`	`Enum('BINARY', 'MULTIPLE', 'SCALAR', 'PARLAY', 'PERPETUAL')`	Type of market
`outcomes`	`Array(Tuple(...))`	Possible outcomes (see Outcomes)
`created_at`	`DateTime64(6)`	Source-provided creation/reference time; some perpetual products use a fixed product epoch
`opened_at`	`Nullable(DateTime64(6))`	When trading opened
`closes_at`	`Nullable(DateTime64(6))`	When trading closes
`resolves_at`	`Nullable(DateTime64(6))`	Expected resolution time
`end_date`	`Nullable(DateTime64(6))`	Market end date
`reset_at`	`Nullable(DateTime64(6))`	Last reset timestamp
`status`	`Enum('PENDING', 'ACTIVE', 'PAUSED', 'CLOSED', 'RESOLVED')`	Current market state

Resolution Fields

Column	Type	Description
`resolution_type`	`Nullable(Enum('STANDARD', 'SPLIT', 'VOID'))`	How market was resolved
`resolution_winning_outcome_id`	`Nullable(UUID)`	ID of winning outcome
`resolution_outcome_payouts`	`Array(Tuple(outcome_id UUID, payout Decimal128(18)))`	Payout per outcome
`resolution_resolved_by`	`Nullable(String)`	Resolver identifier
`resolution_resolved_at`	`Nullable(DateTime64(6))`	Resolution timestamp
`resolution_source_block_number`	`Nullable(UInt64)`	Resolution block number when supplied; API-derived resolutions can contain `0` or no meaningful chain provenance
`resolution_source_tx_hash`	`Nullable(String)`	Resolution transaction hash when supplied; API-derived resolutions can contain an empty/default value

Indexing Fields

Column	Type	Description
`source_block_number`	`UInt64`	Source block number when supplied; `0` means unavailable for current API-derived market rows
`source_tx_hash`	`FixedString(66)`	Source transaction hash when supplied; empty/default means unavailable
`indexed_at`	`DateTime64(9)`	When Probalytics indexed this record

Outcomes Structure

The outcomes column is an array of named tuples:

Array(Tuple(
    id UUID,            -- Probalytics outcome ID
    platform_id String, -- Platform's native outcome ID
    name String,        -- Outcome name (e.g., "Yes", "No", "Trump")
    index UInt8         -- Position in the outcomes list
))

Access fields by name:

SELECT
    id,
    title,
    outcome.id as outcome_id,
    outcome.name as outcome_name
FROM markets
ARRAY JOIN outcomes as outcome
WHERE platform = 'POLYMARKET'
LIMIT 5;

Market Types

Type	Description
`BINARY`	Two outcomes: Yes/No
`MULTIPLE`	Multiple mutually exclusive outcomes
`SCALAR`	Numeric range market
`PARLAY`	Combined multi-leg market
`PERPETUAL`	Perpetual or margin instrument; prices and quantities use instrument units rather than probability units

Market Statuses

Status	Description
`PENDING`	Market created but not yet open for trading
`ACTIVE`	Open for trading
`PAUSED`	Trading temporarily suspended
`CLOSED`	Trading ended, awaiting resolution
`RESOLVED`	Final outcome determined

fills

Normalized execution records from supported platform and product feeds. Participant identifiers are populated only when the source exposes them. Availability: All retained fill history on paid ClickHouse tiers; trials normally receive the last 7 days. This is an access entitlement, not a guarantee of complete venue history.

Schema

Column	Type	Description
`id`	`UUID`	Probalytics unique fill identifier
`market_id`	`UUID`	Reference to `markets.id`
`market_platform_id`	`String`	Platform’s native market ID
`platform`	`Enum('POLYMARKET', 'KALSHI')`	Source platform
`platform_id`	`String`	Platform’s native fill/trade ID
`outcome`	`Tuple(id UUID, platform_id String, name String, index UInt8)`	The outcome traded
`size`	`Decimal128(18)`	Traded quantity: contracts for prediction markets, venue instrument units for perpetual products
`price`	`Decimal128(18)`	Price per contract in platform-native units
`normalized_price`	`Decimal128(18)`	Binary: execution price expressed relative to outcome index 0, normally 0–1. Perpetual: instrument price, not a probability
`taker_side`	`Enum('BUY', 'SELL')`	Source-specific normalized taker side; semantics differ by platform and product
`taker_cash_flow`	`Decimal128(18)`	Stored gross/notional amount for the taker; currently not signed by BUY/SELL direction
`maker_cash_flow`	`Decimal128(18)`	Stored gross/notional amount for the maker; currently not signed by direction
`taker_id`	`Nullable(String)`	Taker identifier (wallet address or platform user ID)
`maker_id`	`Nullable(String)`	Maker identifier (wallet address or platform user ID)
`fee`	`Decimal128(18)`	Fee amount; `0` can mean fee data was unavailable rather than no fee was charged
`timestamp`	`DateTime64(6)`	When the fill was executed (microsecond precision)

Indexing Fields

Column	Type	Description
`source_block_number`	`Nullable(UInt64)`	Blockchain block number (null for non-chain fills)
`source_tx_hash`	`Nullable(String)`	Transaction hash (null for non-chain fills)
`source_log_index`	`Nullable(UInt32)`	Log index within the transaction
`indexed_at`	`DateTime64(9)`	When Probalytics indexed this record
`metadata`	`JSON`	Source- or product-specific metadata; defaults to `{}`

Do not interpret normalized_price without joining market_id to markets and checking market_type. Do not calculate directional P&L by summing the current cash-flow columns: their stored values are not signed by trade direction.

Outcome Structure

The outcome column is a named tuple:

Tuple(
    id UUID,            -- Probalytics outcome ID
    platform_id String, -- Platform's native outcome ID
    name String,        -- Outcome name
    index UInt8         -- Position index
)

Access fields by name:

SELECT
    outcome.id as outcome_id,
    outcome.platform_id as platform_outcome_id,
    outcome.name as outcome_name,
    outcome.index as outcome_index,
    price,
    size
FROM fills
LIMIT 5;

Taker Side

taker_side is not directly comparable across every source. In particular, current Kalshi binary fills are stored as BUY; the source side selects the bought Yes/No outcome. Polymarket binary fills use BUY/SELL relative to exchanging quote currency and outcome tokens. See Fills before aggregating by this field.

orderbook_snapshots

Orderbook depth snapshots per market outcome, written when the book state changes. Each row captures the full bid/ask ladder at a point in time. Availability: Orderbook and Custom ClickHouse tiers. Paid credentials receive all retained history; trials normally receive the last 7 days.

Polymarket data is available from November 20, 2025, with high-quality, high-frequency snapshots beginning February 17, 2026; earlier data may have lower capture frequency and gaps. Kalshi orderbook data is available from May 8, 2026. Coverage dates describe observed dataset availability, not a guarantee that every transition is contiguous; use the quality fields below.

Schema

Column	Type	Description
`market_id`	`UUID`	Reference to `markets.id`
`market_platform_id`	`String`	Platform’s native market ID
`platform`	`Enum('POLYMARKET', 'KALSHI')`	Source platform
`outcome`	`Tuple(id UUID, platform_id String, name String, index UInt8)`	The outcome this snapshot is for
`bids`	`Array(Tuple(price Decimal64(6), size Decimal64(6)))`	Bid levels sorted by price descending
`asks`	`Array(Tuple(price Decimal64(6), size Decimal64(6)))`	Ask levels sorted by price ascending
`timestamp`	`DateTime64(9)`	Exchange event time represented by the book
`indexed_at`	`DateTime64(9)`	When Probalytics materialized the row
`hash`	`UInt64`	Internal non-cryptographic content identity and sorting-key tiebreaker
`state`	`Enum8('VERIFIED', 'INTERMEDIATE')`	Whether the orderbook was reconstructed without gaps or is an earlier retained state from that update sequence
`continuity`	`Enum8('CONTIGUOUS', 'RESET')`	Whether the row connects to the preceding published row or is a fresh starting point
`path_index`	`UInt32`	Zero-based order of rows within an emitted path; not a global sequence number

For complete-book semantics, row ordering, quality fields, historical defaults, and venue-specific reconstruction, see Orderbook Snapshots.

Bid/Ask Structure

Each level in the bids and asks arrays is a named tuple:

Tuple(
    price Decimal64(6), -- Price level
    size Decimal64(6)   -- Total size at this price
)

Access fields by name:

SELECT
    market_platform_id,
    outcome.name as outcome_name,
    bids[1].price as best_bid_price,
    bids[1].size as best_bid_size,
    asks[1].price as best_ask_price,
    asks[1].size as best_ask_size,
    timestamp,
    continuity,
    path_index
FROM orderbook_snapshots FINAL
WHERE market_id = 'your-market-uuid-here'
  AND state = 'VERIFIED'
ORDER BY timestamp DESC, path_index DESC, indexed_at DESC
LIMIT 10;

Computed Fields

BBO, spread, and mid-price are not stored — compute them at query time:

best_bid = bids[1].price (first level in descending bids)
best_ask = asks[1].price (first level in ascending asks)
spread = asks[1].price - bids[1].price
mid_price = (bids[1].price + asks[1].price) / 2

Example: Bid-Ask Spread Over Time

SELECT
    toStartOfMinute(timestamp) as minute,
    outcome.name as outcome,
    avg(asks[1].price - bids[1].price) as avg_spread,
    avg(bids[1].size + asks[1].size) as avg_top_of_book_size
FROM orderbook_snapshots
WHERE market_id = 'your-market-uuid-here'
  AND timestamp >= now() - INTERVAL 1 HOUR
  AND state = 'VERIFIED'
  AND length(bids) > 0
  AND length(asks) > 0
GROUP BY minute, outcome
ORDER BY minute ASC;

Table Relationships

From	To	Description
`fills.market_id`	`markets.id`	Links fill to its market
`fills.market_platform_id`	`markets.platform_id`	Platform-native market reference
`fills.outcome`	`markets.outcomes[]`	Traded outcome matches one in the market’s outcomes array
`orderbook_snapshots.market_id`	`markets.id`	Links snapshot to its market
`orderbook_snapshots.outcome`	`markets.outcomes[]`	Snapshot outcome matches one in the market’s outcomes array

Join example:

SELECT
    m.title,
    f.price,
    f.size,
    f.outcome.name
FROM fills f
JOIN markets m ON f.market_id = m.id
WHERE m.platform = 'POLYMARKET'
LIMIT 10;

Ordering & Partitioning

Understanding table ordering helps write efficient queries.

markets

ORDER BY (platform, id)

Queries filtering by platform and id align with the sorting key. A created_at filter is still useful for reducing the requested range, but it is not part of the current sort key.

fills

ORDER BY (platform, market_id, timestamp, id)
PARTITION BY toYYYYMM(timestamp)

Queries filtering by platform + market_id are fastest
Monthly partitions on timestamp allow efficient date range pruning

orderbook_snapshots

ORDER BY (market_id, outcome.id, timestamp, hash, state, continuity, path_index)
PARTITION BY toYYYYMMDD(timestamp)

Queries filtering by market_id + outcome.id are fastest
Daily partitions on timestamp allow efficient date range pruning
Preserve path_index when ordering states that share a timestamp

Data Types Reference

DateTime64(6) / DateTime64(9)

Microsecond (6) or nanosecond (9) stored precision timestamps in UTC. Precision indicates storage scale, not guaranteed source-clock accuracy.

-- Filter by date range
WHERE timestamp >= '2024-01-01' AND timestamp < '2024-02-01'

-- Extract components
SELECT toYear(timestamp), toMonth(timestamp), toDayOfWeek(timestamp)

Decimal128(18) / Decimal64(6)

High-precision decimals for financial values. Decimal128(18) has 18 decimal places (used for fill prices/sizes). Decimal64(6) has 6 decimal places (used for orderbook levels).

-- Restrict probability-price analysis to non-perpetual products
SELECT avg(f.normalized_price)
FROM fills f
JOIN markets m ON f.market_id = m.id
WHERE m.market_type != 'PERPETUAL'

-- Safe arithmetic
SELECT sum(size), avg(price)

Enum Types

Use enum values as strings so queries remain readable and do not depend on internal numeric assignments:

-- String comparison
WHERE platform = 'POLYMARKET'

Arrays

Use array functions for filtering and extraction:

-- Check if array contains value
WHERE has(tags, 'politics')

-- Expand array to rows using ARRAY JOIN
SELECT outcome.name
FROM markets
ARRAY JOIN outcomes as outcome

-- Array length
WHERE length(outcomes) > 2

-- Access by index (1-based)
SELECT bids[1].price as best_bid FROM orderbook_snapshots

Named Tuples

Access tuple fields directly by name:

SELECT
    outcome.id,
    outcome.platform_id,
    outcome.name,
    outcome.index
FROM fills

Getting Started

Datasets

Access Methods

REST API

Reference

Support

markets

Schema

Resolution Fields

Indexing Fields

Outcomes Structure

Market Types

Market Statuses

fills

Schema

Indexing Fields

Outcome Structure

Taker Side

orderbook_snapshots

Schema

Bid/Ask Structure

Computed Fields

Example: Bid-Ask Spread Over Time

Table Relationships

Ordering & Partitioning

markets

fills

orderbook_snapshots

Data Types Reference

DateTime64(6) / DateTime64(9)

Decimal128(18) / Decimal64(6)

Enum Types

Arrays

Named Tuples

​markets

​Schema

​Resolution Fields

​Indexing Fields

​Outcomes Structure

​Market Types

​Market Statuses

​fills

​Schema

​Indexing Fields

​Outcome Structure

​Taker Side

​orderbook_snapshots

​Schema

​Bid/Ask Structure

​Computed Fields

​Example: Bid-Ask Spread Over Time

​Table Relationships

​Ordering & Partitioning

​markets

​fills

​orderbook_snapshots

​Data Types Reference

​DateTime64(6) / DateTime64(9)

​Decimal128(18) / Decimal64(6)

​Enum Types

​Arrays

​Named Tuples

markets

Schema

Resolution Fields

Indexing Fields

Outcomes Structure

Market Types

Market Statuses

fills

Schema

Indexing Fields

Outcome Structure

Taker Side

orderbook_snapshots

Schema

Bid/Ask Structure

Computed Fields

Example: Bid-Ask Spread Over Time

Table Relationships

Ordering & Partitioning

markets

fills

orderbook_snapshots

Data Types Reference

DateTime64(6) / DateTime64(9)

Decimal128(18) / Decimal64(6)

Enum Types

Arrays

Named Tuples