> ## Documentation Index
> Fetch the complete documentation index at: https://docs.probalytics.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Tables & Schemas

> ClickHouse table schemas and columns

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](/docs/datasets/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](#outcomes-structure)) |
| `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:

```sql theme={null}
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:**

```sql theme={null}
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 `{}` |

<Warning>
  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.
</Warning>

### Outcome Structure

The `outcome` column is a named tuple:

```sql theme={null}
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:**

```sql theme={null}
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](/docs/datasets/fills#field-derivation-by-product) 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.

<Note>
  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.
</Note>

### 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](/docs/datasets/orderbook).

### Bid/Ask Structure

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

```sql theme={null}
Tuple(
    price Decimal64(6), -- Price level
    size Decimal64(6)   -- Total size at this price
)
```

**Access fields by name:**

```sql theme={null}
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

```sql theme={null}
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:**

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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.

```sql theme={null}
-- 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).

```sql theme={null}
-- 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:

```sql theme={null}
-- String comparison
WHERE platform = 'POLYMARKET'
```

### Arrays

Use array functions for filtering and extraction:

```sql theme={null}
-- 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:

```sql theme={null}
SELECT
    outcome.id,
    outcome.platform_id,
    outcome.name,
    outcome.index
FROM fills
```


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.