Polymarket P&L Substreams

Overview

Comprehensive Substreams package for tracking Polymarket P&L with SQL sink support for persistent state accumulation. Tracks all trading activity from CTF Exchange and Neg Risk Exchange contracts across both CLOB v1 and CLOB v2.

CLOB v2 ready (since v1.2.0)

Polymarket migrated to CLOB v2 at the 2026-04-28 ~11:00 UTC cutover. v2 ships fresh Exchange contracts at new addresses, a redesigned OrderFilled event, a different fee model, and a new collateral wrapper (pUSD). This package indexes both contract generations side-by-side so a single P&L pipeline spans the migration.

What changed in CLOB v2

How this package handles it

1. Contract dispatch. map_order_fills watches all four Exchange addresses (v1 + v2). When a log lands on a v2 contract, it routes to the v2 decoder; v1 contracts still use the original v1 decoder. Both produce the same internal OrderFilledEvent shape, so the rest of the pipeline (stores, P&L math, SQL sink) sees one homogeneous stream.
2. v2 → v1 field mapping. The v2 event emits one tokenId with an explicit side. We synthesize the legacy maker_asset_id / taker_asset_id pair from (side, tokenId) using the same convention as v1 (collateral-side gets "0", the conditional-token side gets the token id). This keeps your existing WHERE side = 'buy' queries and the cost-basis store working with zero changes.
3. New columns surfaced. v2's builder and metadata are decoded but currently land only in the in-memory event (not yet in the SQL trades table — see Migration to richer v2 schema below for an opt-in extension).
4. Source-of-truth tagging. The exchange column tags every row with which contract emitted it: ctf / neg_risk (v1) or ctf_v2 / neg_risk_v2. So you can filter, partition, or audit by generation.
5. Fee column semantics. v2 fees are taker-only and protocol-determined; they appear in the same fee field but represent a single realized taker fee rather than maker+taker fees.
6. No schema migration required. Existing trades, user_pnl, user_positions, markets, daily_stats tables work unchanged — v2 fills slot in as additional rows with the new exchange values.

What stays the same

• Same chain (Polygon), same Firehose source, same RPC.
• Same map_order_fills → stores → analytics → db_out DAG.
• Same SQL sink configuration, same leaderboard / whale views.
• Same start block for historical backfill (33,605,403); v2 fills activate naturally at the v2 deploy block (84,902,353).

Migration to richer v2 schema

If you want to query v2-specific fields like builder (for builder attribution analytics) or metadata, you can add columns and surface them in db_out:

ALTER TABLE trades
  ADD COLUMN exchange_version VARCHAR(4) DEFAULT 'v1',
  ADD COLUMN builder VARCHAR(66),
  ADD COLUMN metadata VARCHAR(66);
CREATE INDEX idx_trades_builder ON trades(builder) WHERE builder IS NOT NULL;

Then add the matching .set("builder", ...) / .set("metadata", ...) calls in db_out and republish. This is opt-in — the package as shipped doesn't require it.

Key Features

Quick Start

Stream Data (No Database)

# Install CLI
brew install streamingfast/tap/substreams

# Authenticate
substreams auth

# Stream order fills
substreams run https://spkg.io/PaulieB14/polymarket-pnl-v1.2.0.spkg \
  map_order_fills \
  -e polygon.substreams.pinax.network:443 \
  -s 65000000 -t +1000

# Stream user P&L
substreams run https://spkg.io/PaulieB14/polymarket-pnl-v1.2.0.spkg \
  map_user_pnl \
  -e polygon.substreams.pinax.network:443 \
  -s 65000000 -t +1000

Sink to PostgreSQL (Required for P&L)

Important: P&L requires accumulated state. Use the SQL sink for accurate calculations.

# Install sink
brew install streamingfast/tap/substreams-sink-sql

# Create database
createdb polymarket_pnl

# Setup schema
substreams-sink-sql setup \
  "psql://localhost:5432/polymarket_pnl?sslmode=disable" \
  https://spkg.io/PaulieB14/polymarket-pnl-v1.2.0.spkg

# Run sink (start from beginning for full history)
substreams-sink-sql run \
  "psql://localhost:5432/polymarket_pnl?sslmode=disable" \
  https://spkg.io/PaulieB14/polymarket-pnl-v1.2.0.spkg \
  -e polygon.substreams.pinax.network:443

Query Your Data

-- Top traders by P&L
SELECT * FROM leaderboard_pnl LIMIT 20;

-- Whale trades with trader stats
SELECT * FROM whale_trades;

-- User positions
SELECT * FROM user_positions
WHERE user_address = '0x...' AND quantity > 0;

-- Daily stats
SELECT date, total_volume, total_trades
FROM daily_stats ORDER BY date DESC;

Architecture

                    Polygon Blockchain
                           │
                           ▼
              ┌─────────────────────────┐
              │     Firehose Blocks     │
              └─────────────────────────┘
                           │
         ┌─────────────────┼─────────────────┐
         ▼                 ▼                 ▼
┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐
│ map_order_fills │ │map_token_transf │ │map_usdc_transf  │
│  (CTF + NegRisk)│ │    (ERC1155)    │ │    (USDC)       │
└─────────────────┘ └─────────────────┘ └─────────────────┘
         │                 │                   │
         └─────────────────┼───────────────────┘
                           ▼
              ┌─────────────────────────┐
              │    STORES (State)       │
              │  • user_positions       │
              │  • user_cost_basis      │
              │  • user_realized_pnl    │
              │  • latest_prices        │
              └─────────────────────────┘
                           │
                           ▼
              ┌─────────────────────────┐
              │      map_user_pnl       │
              │   (Computed Analytics)  │
              └─────────────────────────┘
                           │
                           ▼
              ┌─────────────────────────┐
              │         db_out          │
              │      (SQL Sink)         │
              └─────────────────────────┘
                           │
                           ▼
              ┌─────────────────────────┐
              │       PostgreSQL        │
              └─────────────────────────┘

Modules

Layer 1: Event Extraction

Layer 2: State Stores

Layer 3: Analytics

Layer 4: Sink

Database Schema

Tables

Views

Contract Addresses

CLOB v1 (legacy — historical fills only after the 2026-04-28 cutover)

CLOB v2 (deployed 2026-03-31 by Polymarket Deployer 1, cutover 2026-04-28)

Other (unchanged)

Build from Source

# Clone
git clone https://github.com/PaulieB14/Polymarket-P-L-Substreams
cd Polymarket-P-L-Substreams

# Build
substreams build

# Test
substreams run substreams.yaml map_order_fills \
  -e polygon.substreams.pinax.network:443 \
  -s 65000000 -t +100

# Package & publish
substreams pack substreams.yaml -o polymarket-pnl-v1.2.0.spkg
substreams publish polymarket-pnl-v1.2.0.spkg

Why SQL Sink?

P&L calculation requires state accumulation over time:

Related

• polymarket-orderbook-substreams - Order flow & trader leaderboards

License

MIT