Data Architecture & Database Schema

Version: 1.7 Last Updated: May 10, 2026 (Dome → Polymarket migration, Findings #287/#288/#289) Database: PostgreSQL 16 + TimescaleDB (Apache edition — see §7 for retention/compression limitations) Size: ~32 GB (was 9.3 GB at v1.5; rapid growth driven by hypertables — see §6 for retention deferral note)

This document provides a complete reference for the database schema, data sources, collection services, and data integrity systems.

🔴 CRITICAL — May 10, 2026 Dome→Polymarket migration (Findings #287/#288/#289):

Dome was acquired by Polymarket; all Dome APIs reached end-of-life on April 28, 2026. The body of this doc was written when api.domeapi.io was an active data source — read all references to "Dome API" as historical context. Current data sources:

Old Dome endpoint	New Polymarket replacement
/polymarket/orders (historical trades)	data-api.polymarket.com/trades?market=<cid> — 200 req/10s = 20 q/s sustained (20× faster than Dome was)
/polymarket/market-price/{token} historical	clob.polymarket.com/prices-history?market=<token>&startTs=... — 1,000 req/10s
/polymarket/orderbooks (historical orderbook)	No replacement — live capture only via polymarket-collector.service + polymarket-websocket.service (WSS market channel). Decision deferred for Phase 3 simulator review.
/polymarket/candlesticks	clob/prices-history (price-only {t,p}, no OHLCV); candlesticks table is empty / no readers, kept as schema placeholder
/polymarket/activity, /wallet/pnl/*	data-api/activity, derive from data-api/positions + /closed-positions

Decommissioned services (Tier A #287): polymarket-orderbook-backfill, polymarket-prices-backfill, polymarket-dome-prices-backfill. State files archived. Live data continues via polymarket-collector + polymarket-live-trades + polymarket-websocket.

Migrated services (Tier B #288): backfill_trades.py now hits data-api.polymarket.com/trades with smart Option A logic preserved (T1 delta, T2 smart re-fetch, T3 earnings-only, circuit breaker). 20× faster — cold bootstrap ~3h → ~8min, weekly warm delta ~18min → ~1-2min.

Deleted files (Tier C #289): dome_client.py, main_collector.py, backfill_earnings_trades.py, backfill_candlesticks.py, backfill_prices.py, dome_prices_backfill.py, orderbook_backfill_service.py, prices_backfill_service.py.

Audit context (Apr 27, 2026): this doc went through a comprehensive review during the Database Integrity Audit (Finding #250 in docs/FINDINGS.md, full report in docs/DATABASE_AUDIT_2026_04_27.md). The audit found 8 tables present in production but missing from this doc's §3, several stale row-count claims, and a phantom gap_tracking table reference. v1.6 reflects fix per Phase 6.

Operational integrity reference: for live integrity status see docs/DATA_INTEGRITY.md §2 (the new integrity monitor in src/data_tracker/integrity_monitor.py, replacing the v2/v3 watchdogs).

Table of Contents

1. Overview
2. Data Sources
3. Database Schema
4. Collection Services
5. Backfill Sources
6. Indexing Strategy
7. Data Integrity
8. Dashboard Integration

1. Overview

System Architecture

┌─────────────────────────────────────────────────────────────────────────────┐
│                           DATA COLLECTION SYSTEM                            │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                             │
│  ┌─────────────────┐   ┌─────────────────┐   ┌─────────────────┐           │
│  │   POLYMARKET    │   │   PM DATA-API   │   │   IBKR          │           │
│  │   APIs          │   │   (Dome EOL'd)  │   │   (Trading)     │           │
│  │                 │   │                 │   │                 │           │
│  │  • Gamma API    │   │  • Trades       │   │  • Executions   │           │
│  │  • CLOB API     │   │  • Prices       │   │  • Account      │           │
│  │  • WebSocket    │   │  • Markets      │   │  • Positions    │           │
│  │  • Data API     │   │                 │   │                 │           │
│  └────────┬────────┘   └────────┬────────┘   └────────┬────────┘           │
│           │                     │                     │                     │
│           ▼                     ▼                     ▼                     │
│  ┌─────────────────────────────────────────────────────────────────────┐   │
│  │                      COLLECTION SERVICES                             │   │
│  │                                                                      │   │
│  │  ┌────────────────┐  ┌────────────────┐  ┌────────────────┐         │   │
│  │  │ Continuous     │  │ WebSocket      │  │ Trades         │         │   │
│  │  │ Collector      │  │ Collector      │  │ Backfill       │         │   │
│  │  │ (1-2 min)      │  │ (real-time)    │  │ (historical)   │         │   │
│  │  └────────────────┘  └────────────────┘  └────────────────┘         │   │
│  │                                                                      │   │
│  │  ┌────────────────┐  ┌────────────────┐  ┌────────────────┐         │   │
│  │  │ Prices         │  │ Earnings       │  │ IBKR           │         │   │
│  │  │ Backfill       │  │ Sync           │  │ Trader         │         │   │
│  │  │ (historical)   │  │ (scheduled)    │  │ (live)         │         │   │
│  │  └────────────────┘  └────────────────┘  └────────────────┘         │   │
│  │                                                                      │   │
│  └─────────────────────────────────────────────────────────────────────┘   │
│                                    │                                        │
│                                    ▼                                        │
│  ┌─────────────────────────────────────────────────────────────────────┐   │
│  │                    POSTGRESQL + TIMESCALEDB                          │   │
│  │  ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────────┐   │   │
│  │  │ markets │ │ trades  │ │orderbook│ │ prices  │ │trade_audit  │   │   │
│  │  │ 4,725   │ │ 2.4M    │ │snapshots│ │snapshots│ │_log         │   │   │
│  │  │         │ │         │ │ 2.5M    │ │ 302K    │ │ 117+ rows   │   │   │
│  │  └─────────┘ └─────────┘ └─────────┘ └─────────┘ └─────────────┘   │   │
│  └─────────────────────────────────────────────────────────────────────┘   │
│                                                                             │
└─────────────────────────────────────────────────────────────────────────────┘

Key Metrics (Apr 27, 2026 — refreshed for Phase 6)

Tables present but previously undocumented (added in v1.6)

The Apr 27 audit (docs/DATABASE_AUDIT_2026_04_27.md §8.1) found these tables in prod that v1.5 of this doc did not cover:

• skipped_signals — written by src/earnings/signal_generator.py; read by outcome_resolver for counterfactual P&L.
• collection_jobs — generic job-result log; written by reconciler etc.
• collection_stats — periodic collection counters.
• ibkr_executions — raw IBKR fills, written by trade_reconciler.
• margin_ledger, margin_events — leverage / financing tracking.
• exit_execution_analysis — slippage attribution per exit.
• fractional_eligibility_cache — IBKR fractional-share eligibility cache.
• candlesticks — empty hypertable, currently unused.

Phase 6 doc refresh adds these to the §3 schema.

Tables removed from doc (v1.5 referenced; do not exist in prod)

• gap_tracking — phantom reference. The actual table was data_gaps (now read-only legacy; see DATA_INTEGRITY.md §2 for the replacement integrity_status).

2. Data Sources

2.1 Polymarket APIs

Key API Insights

Polymarket data-api (data-api.polymarket.com) is the primary source for: - ALL market trades via /trades?market=<cid> or /trades?asset_id=<token> (not just your own) - Historical trade data with full execution details - Transaction hashes (transactionHash), wallet addresses (proxyWallet), timestamps - This is the best source for trade data collection

Migration note (Findings #287/#288/#289, May 10, 2026): This used to be called "Dome API" because Dome was the original vendor before Polymarket acquired them. All Dome APIs (api.domeapi.io) reached end-of-life on April 28, 2026. All consumers now hit the new Polymarket data-api directly. No API key required for public endpoints.

Note: continuous_collector.py disables authenticated CLOB trade polling by default. Live trades come from Polymarket data-api /trades?asset_id= via live_trades_collector.py (formerly mis-named "Dome" in v1.0; cosmetic rename in #289 — same service, same endpoint, just clarified naming).

WebSocket provides: - price_change events: Best bid/ask updates when prices change - book events: Orderbook snapshots on subscription - last_trade_price: Included in book events - Note: WebSocket does NOT provide actual trade events with transaction details

CLOB API authenticated endpoints only return YOUR trades (where you are maker/taker), not all market trades.

Gamma API provides: - Market metadata (liquidity, volume, open interest) - Price snapshots with 24h/1w/1m changes - Best bid/ask prices

2.2 Third-Party APIs

2.3 IBKR Data

2.4 Data Source Matrix by Table

3. Database Schema

3.1 Core Tables

markets (4,725 rows)

Primary market registry from Polymarket.

orderbook_snapshots (2.5M+ rows) - TimescaleDB Hypertable

Real-time orderbook state snapshots.

⚠️ GOTCHA — DO NOT use spread_yes / spread_no for cost modeling on low-volume markets (Finding #299, 2026-05-17). On sparsely-traded earnings markets, the only resting orders are stub auto-MM quotes from PM's market-maker at $0.01 / $0.99 boundaries. The collector captures whatever's at the top of book — and on low-volume books that's the stub quote, not the price a real trader would execute against. Average spread_no reported as $0.98 ($0.01 bid / $0.99 ask) on H1b-eligible markets where actual realized spread is 7-12¢. The prob_24h_before value in earnings_markets comes from a different price-discovery layer (CLOB last-trade or reference price), so the spread fields and prob_*_before values are NOT consistent.

Use the src/utils/orderbook_spread.py helpers instead (Finding #299, 2026-05-17 evening — RESOLVED). Three public functions: - realistic_spread_from_trades(engine, condition_id, start=, end=, outcome=) — single-market, single-side - realistic_spread_yes_no_from_trades(engine, condition_id, start=, end=) — both sides via single GROUP BY - aggregate_realistic_spread(engine, condition_ids, start=, end=, outcome=) — cohort-level median/p25/p75/mean

All compute max(trade.price) - min(trade.price) from the trades hypertable for the given window — i.e., the spread you'd actually pay against passing flow. Use a tight window (e.g., 24h pre-ER) for cost-modeling estimates. See pm_earnings_arb-001 Phase 1.5 #1 audit for the canonical methodology.

price_snapshots (302K+ rows) - TimescaleDB Hypertable

Simplified price history. This is the source of conviction data (not markets table).

⚠️ IMPORTANT: Conviction (beat probability) comes from price_snapshots.yes_price, NOT markets table. The markets table contains volume data but no yes_price column. | volume_24h | NUMERIC | 24h volume | | liquidity | NUMERIC | Total liquidity | | open_interest | NUMERIC | Open interest | | price_change_1h | NUMERIC | 1h price change | | price_change_24h | NUMERIC | 24h price change | | best_bid | NUMERIC | Best bid | | best_ask | NUMERIC | Best ask |

equity_bars_1m (NEW Apr 26, 2026 — Phase 2 of Strategy Analysis Revamp) — TimescaleDB Hypertable

Polygon 1-minute equity aggregates cache for symbols with upcoming Polymarket earnings markets. Eliminates per-simulator-run Polygon round-trips and is the load-bearing infrastructure for Phase 3 unified simulator (per docs/STRATEGY_ANALYSIS_ARCHITECTURE.md §0.10 + Finding #240).

Hypertable: 7-day chunks. Retention: 2 years (manual via collector --prune-older-than 730 since prod is on TimescaleDB Apache edition). Population: scripts/equity_bars_collector.py (daily timer polymarket-equity-bars.timer). Cache helper: src/earnings/equity_bar_cache.py:fetch_minute_bars_cached().

Migration: src/data_tracker/migrations/025_equity_bars_1m.sql.

trades (2.4M+ rows) - TimescaleDB Hypertable

Individual trade records.

⚠️ GOTCHA — Pre-2024 trades NOT available (Finding #301, 2026-05-17). The Polymarket data-api/trades endpoint and Polygon-class providers enforce a hard 5-year max-window. Pre-2024 PM trades are inaccessible via standard endpoints; contiguous coverage in our hypertable starts mid-2024. Multi-quarter / multi-year backtest scope is bounded by this limit. Going forward this is solved by retention policy (we accumulate trades as they happen); retroactive backfill of pre-2024 history would require either a higher-tier vendor plan or PM archive S3 dumps (none currently public). Constrains pm_earnings_arb walk-forward (METHODOLOGY.md §3) and any future research that needs a long-window holdout.

trade_dedupe (new) - Global trade ID uniqueness

Lightweight table to enforce global trade_id uniqueness across all time buckets. TimescaleDB hypertables require time in unique indexes, so this table prevents duplicates under concurrent collectors and restarts. Used by both live trades and trades backfill to guarantee idempotency.

3.2 Earnings Trading Tables

earnings_markets (414 rows)

Polymarket earnings markets linked to stock earnings.

conviction_history (390+ rows - populated Jan 27, 2026)

Hourly conviction snapshots for ALL earnings markets with BMO/AMC separation.

Key Indexes: - idx_conviction_history_symbol_time - (symbol, recorded_at DESC) - idx_conviction_history_entry_time - (is_entry_time, symbol)

Use Cases: - Analyze Polymarket reliability at different time horizons - Refine entry timing (is 24h before more accurate than 1h before?) - Track conviction evolution for each earnings event - Compare BMO vs AMC conviction patterns

trading_positions (historical record and crash recovery)

Trading position history. Note (Phase 4B+4C): IBKR is the source of truth for live position state. This table serves as the historical audit trail and crash recovery source. The position_state column tracks the lifecycle: pending/open/closing/closed/failed.

Post-fill durability (#455): if Postgres rejects the entry write after IBKR fills, the trader atomically stores the complete trading_positions + trade_audit_log payload in /var/lib/polymarket/db-write-spool/position_open. The payload hydrates the in-memory position, exposure and earnings monitor before replay; both entry paths stay closed until it is ACKed. Migration 039 adds a shared unique entry_persist_key and entry-exec indexes to both tables; transaction advisory locks + ON CONFLICT cover concurrent replay and a crash after commit. Exit execution is persisted first and failed closes use position_close/. Raw executions, reconciliation logs and live trades use their own kinds; quarantine and artifact:db_write_spool make poison/stuck records visible. Per-kind capacity reserves trader fills from collector backlogs; transient failures back off indefinitely, while only genuine poison quarantines. Committed-close and stopout-before-restart evidence clear idempotently. IBKR remains source of truth for live shares and stops.

trade_audit_log (117+ live rows)

Comprehensive trade recording with 111+ columns.

Key Sections: - Order Identification: trade_id, symbol, direction, timing - Entry Details: entry_time, entry_price, entry_slippage, entry_commission - Exit Details: exit_time, exit_price, exit_slippage, exit_commission - Position Sizing: shares, shares_fractional, is_fractional, position_value, position_size_pct, capital_at_entry - Risk Management: stop_price, stop_distance_pct, stop_triggered - Performance: realized_pnl, realized_pnl_pct, gross_pnl, total_commission, net_pnl_after_financing. realized_pnl is price P&L (gross of commissions), gross_pnl = realized_pnl (#332), net_pnl_after_financing = realized_pnl − the trade's margin_ledger financing; the trading_positions twin carries the same exit_price / realized_pnl. Every close and correction writer derives them together via src/earnings/pnl_fields.py; trade_reconciler flags rows where they disagree (Finding #477). - Signal Context: conviction_at_entry, conviction_source, yes_price_at_entry - Market Conditions: vix_at_entry, sp500_at_entry, market_session - IBKR Data: ibkr_account_id, ibkr_perm_id, entry_fill_details (JSONB), ibkr_client_id (Migration 013) - Strategy Context: portfolio_state_at_entry (JSONB), risk_parameters (JSONB) - Outcome Data: actual_eps, consensus_eps, pm_resolution_price - External Trade Detection (Migration 013): ibkr_client_id, is_external_trade

polymarket_opportunity_log (Migration 021, Finding #221)

Research-grade append-only log: each evaluation of an earnings/Polymarket candidate on the signal path (skip_stage, skip_reason, conviction, volumes, depth, mcap, priority). Includes scheduled entry/exit and stop_loss_pct aligned with live BMO/AMC rules when computable; counterfactual_* columns are filled by offline jobs (scripts/compute_counterfactual_pnl.py). Does not replace skipped_signals (volume-threshold subset) or trade_audit_log (executions).

external_trades (Migration 013)

Detected trades from non-bot clientIds.

position_reconciliation_log (Migration 013)

Audit log of reconciliation runs.

3.3 System Tables

data_gaps (804 rows)

Tracks detected data gaps for backfill.

backfill_status (various)

Tracks backfill job progress.

audit_events (126 rows)

System event log.

4. Collection Services

4.1 Active Services

4.1.1 Outcome Resolver (Finding #227)

Module: src/data_tracker/collectors/outcome_resolver.py. Run by polymarket-resolver.timer daily at 06:00 ET. Performs four ordered passes (each idempotent via WHERE x IS NULL):

1. earnings_markets — fills actual_beat via a 4-tier source cascade and pulls stock_price_* from Polygon 1m aggs (with stock_return_* derived arithmetically). Source priority for actual_beat:
2. EPS surprise — eps_actual >= eps_estimate (most direct signal). EPS sources tried in order: Yahoo earnings_history → Finnhub /stock/earnings → Polygon /vX/reference/financials (Stocks Starter unlocks this; diluted EPS preferred over basic; ±120-day window then closest filing within 30 days of target).
3. Post-event Polymarket yes_price — latest snapshot from price_snapshots >12h after earnings_date. Markets converge to 0.000/1.000 within hours of resolution, so this is essentially ground truth. Threshold >=0.95 → BEAT, <=0.05 → MISS.
4. Pre-event prob cascade — falls through prob_30min_before → prob_1h_before → prob_6h_before → prob_12h_before → prob_24h_before (most recent non-NULL). Same 0.95/0.05 thresholds.
5. Strict 0.99/0.01 thresholds available via _pm_resolution_actual_beat(..., strict=True) for callers that want maximum precision (not used by the resolver itself). revenue_actual populated from Polygon's revenues field as a side effect of Tier-1 step 3.
6. skipped_signals — counterfactual P&L using counterfactual_pricing.simulate_minute_path (Polygon 1m bars, stop on bar low/high). Sets actual_outcome ∈ {win, loss, stop} and would_have_won.
7. polymarket_opportunity_log — invokes run_counterfactual_for_row (fixed $10K notional). Replaces operator-only scripts/compute_counterfactual_pnl.py for the timer-driven path.
8. trade_audit_log.prediction_correct — single SQL UPDATE joining to earnings_markets.actual_beat (now populated by Pass 1).

Empirical calibration (Apr 2026, n=159 resolved-EPS rows): Polymarket yes_price >= 0.95 → BEAT at 93.75% accuracy (n=112); <=0.05 → MISS at 93.6% (n=47). Justifies the 0.95/0.05 default thresholds.

Coverage: actual_beat populated on ~95-99.8% of past earnings_markets rows (Finding #321; was 524 / 715 = 73% as of Apr 22, 2026). The small remaining gap is markets where neither Yahoo/Finnhub/Polygon EPS exists AND no Polymarket conviction snapshot was ever recorded (mostly pre-Jan-2026 condition_ids that predated the conviction collector).

Manual run / one-shot historical backfill: - Daily incremental: python3 -m src.data_tracker.collectors.outcome_resolver --earnings-limit 200 --skipped-limit 200 --opportunity-limit 200 - Full historical: python3 scripts/backfill_learning_loop.py --polygon-sleep 0.6 (no date floor; loops until exhausted) - Regime/VIX backfill on trade_audit_log: python3 scripts/backfill_regime_for_audit_log.py

Required env: DATABASE_URL, POLYGON_KEY (Polygon Stocks Starter $29/mo recommended for 100 req/min), FINNHUB_API_KEY (optional).

4.1.2 Finding-A Simulator (Apr 25, 2026 — Finding #228)

Module: scripts/analyze_finding_a_full.py. Read-only counterfactual replay of v3 Finding A (block stops outside RTH) and the alternative stop-loss rules (B/D/G/F) on every closed trade in trade_audit_log using Polygon 1m bars. Builds on the same counterfactual_pricing.py primitives the resolver uses.

Output: scripts/finding_a_full.csv (51 rows × 41 cols at the Apr 25 baseline) + stdout aggregate. The CSV is loaded by scripts/strategy_performance_analysis.py:analyze_finding_a_simulator() and rendered in the dashboard 🔬 Finding-A Simulator section + 🎯 Action Queue tab.

Why it exists: every counterfactual stop-loss rule needs a TRUE end-to-end simulator (memory.md §K — "true-simulator rule"). Proxies (e.g. Rule B as a proxy for Finding A) and observational tables (e.g. "stops fire pre-market AND pre-market trades have negative P&L") conflate selection with causation. The simulator distinguishes FA_soft (software-only gate; hardware STP-LMT keeps outsideRth=True per Finding #179 — INERT for P&L) from FA_hard (hardware also gated; the version that actually changes outcomes), models gap-open slippage at RTH re-arm, models earnings_detector cap on wrong-prediction trades, detects halt-suspect cases, and runs the 4-check bias gauntlet per rule.

Re-run procedure (before any new stop-loss rule proposal):

cd [hidden] && source .env && export DATABASE_URL POLYGON_KEY PYTHONPATH=[hidden]
venv/bin/python3 scripts/analyze_finding_a_full.py --polygon-sleep 0.05
venv/bin/python3 scripts/strategy_performance_analysis.py

Canonical narrative: docs/FINDINGS.md Finding #228. Forward plan + ranked queue: docs/STRATEGY_PLAYBOOK.md.

4.2 Collection Architecture (v2.0)

┌─────────────────────────────────────────────────────────────────────────────────┐
│                        DATA COLLECTION ARCHITECTURE v2.0                         │
├─────────────────────────────────────────────────────────────────────────────────┤
│                                                                                  │
│  ┌─────────────────────────────────────────────────────────────────────────────┐│
│  │                            LIVE DATA COLLECTION                             ││
│  │                                                                             ││
│  │  ┌───────────────────┐  ┌───────────────────┐  ┌───────────────────┐       ││
│  │  │ Live Trades       │  │ Continuous        │  │ WebSocket         │       ││
│  │  │ Collector         │  │ Collector         │  │ Collector         │       ││
│  │  │ (PM data-api)     │  │ (CLOB+Gamma)      │  │ (PM WSS market)   │       ││
│  │  │                   │  │                   │  │                   │       ││
│  │  │ ✓ ALL Trades      │  │ ✓ Full Orderbook  │  │ ✓ Price Changes   │       ││
│  │  │ ✓ Tx Details      │  │ ✓ Market Metadata │  │ ✓ Best Bid/Ask    │       ││
│  │  │ ✓ Wallet Addrs    │  │ ✓ Volume/OI       │  │ ✓ Sub-second      │       ││
│  │  │                   │  │ ✓ Price Snapshots │  │                   │       ││
│  │  │ Poll: 10-30s      │  │ Poll: 1-2s        │  │ Push: real-time   │       ││
│  │  └─────────┬─────────┘  └─────────┬─────────┘  └─────────┬─────────┘       ││
│  │            │                      │                      │                  ││
│  │            └──────────────────────┼──────────────────────┘                  ││
│  │                                   │                                         ││
│  └───────────────────────────────────┼─────────────────────────────────────────┘│
│                                      │                                          │
│  ┌───────────────────────────────────┼─────────────────────────────────────────┐│
│  │                         HISTORICAL BACKFILL                                 ││
│  │                                   │                                         ││
│  │  ┌───────────────────┐  ┌────────┴────────┐  ┌───────────────────┐         ││
│  │  │ Trades Backfill   │  │ Prices Backfill │  │ Orderbook Backfill│         ││
│  │  │ (PM data-api)     │  │ DECOMMISSIONED  │  │ DECOMMISSIONED    │         ││
│  │  │ #288 — was Dome   │  │ Finding #287/   │  │ Finding #287 —    │         ││
│  │  │ /orders, EOL      │  │ #289 (May 10)   │  │ no PM replacement │         ││
│  │  │ Apr 28, 2026.     │  │ Live capture    │  │ for historical OB.│         ││
│  │  │ Weekly Sun 06ET.  │  │ via PM-collector│  │ Live capture only.│         ││
│  │  └───────────────────┘  └─────────────────┘  └───────────────────┘         ││
│  │                                                                             ││
│  └─────────────────────────────────────────────────────────────────────────────┘│
│                                      │                                          │
│                                      ▼                                          │
│              ┌───────────────────────────────────────────────┐                  │
│              │         POSTGRESQL + TIMESCALEDB              │                  │
│              │                                               │                  │
│              │  trades │ orderbook_snapshots │ price_snapshots │                │
│              │  2.4M+  │      2.5M+          │      302K+      │                │
│              └───────────────────────────────────────────────┘                  │
│                                                                                  │
└─────────────────────────────────────────────────────────────────────────────────┘

4.3 Data Source Matrix

4.4 Why Polymarket data-api /trades for Live Trades?

(Section header was "Why Dome for Live Trades?" — renamed Finding #289 since the data source is now the Polymarket-owned data-api.polymarket.com rather than the deprecated Dome vendor.)

The Polymarket WebSocket market channel provides price_change events, NOT actual trade events:

// WebSocket price_change event - NO trade details
{
  "event_type": "price_change",
  "price_changes": [
    {"asset_id": "...", "price": "0.75", "size": "100", "side": "BUY", "best_bid": "0.74", "best_ask": "0.76"}
  ]
}

The Polymarket data-api /trades endpoint provides complete trade data:

// data-api.polymarket.com/trades?market=<cid>&limit=N — FULL trade details
{
  "proxyWallet": "0x1234...",
  "side": "BUY",
  "size": 448.32,
  "price": 0.5421,
  "timestamp": 1767777667,
  "transactionHash": "0xabc...",
  "conditionId": "0xdef...",
  "asset": "<token_id_int>",
  "outcome": "Yes",
  "title": "...",
  "slug": "..."
}

4.3 Service Commands

# Check all services
systemctl list-units 'polymarket*'

# Service operations
sudo systemctl status polymarket-collector
sudo systemctl restart polymarket-collector
sudo journalctl -u polymarket-collector -f

# Check integrity-monitor logs (watchdog DELETED — Finding #290)
tail -f /var/log/polymarket/integrity_monitor.log

5. Backfill Sources

5.1 Backfill Matrix

5.2 Backfill Priority

1. Trades (Polymarket data-api) - Most critical, full history available, ~8min cold bootstrap on free tier (20× faster than Dome was per Finding #288)
2. ~~Orderbooks~~ - No historical backfill post-Dome-EOL; live capture only (Finding #287). Phase 3 simulator may need a Dune/on-chain alternative — deferred.
3. ~~Prices~~ - Decommissioned dedicated backfill; live capture sufficient (Finding #289)
4. Markets - Auto-sync via polymarket-data-backfill.timer, low priority

5.3 Gap Recovery Process

Gap Detected → integrity_monitor freshness rule → AlertManager →
  → Telegram alert via AlertCategory.DATA_INTEGRITY
  → If trades: weekly Sun 06:00 ET timer auto-recovers via Polymarket data-api
  → If prices: live capture continuous; no manual recovery needed
  → If orderbooks: live capture continuous; historical gaps unrecoverable

6. Indexing Strategy

6.1 Index Summary

6.2 Key Indexes

-- Time-series queries (most common)
CREATE INDEX idx_orderbook_condition_time ON orderbook_snapshots(condition_id, time DESC);
CREATE INDEX idx_price_condition_time ON price_snapshots(condition_id, time DESC);
CREATE INDEX idx_trades_condition_time ON trades(condition_id, time DESC);

-- Market lookups
CREATE UNIQUE INDEX markets_condition_id_key ON markets(condition_id);
CREATE INDEX idx_markets_status ON markets(status);

-- Earnings trading
CREATE INDEX idx_earnings_date ON earnings_markets(earnings_date);
CREATE INDEX idx_earnings_ticker ON earnings_markets(ticker);

-- Trade analysis
CREATE INDEX idx_trade_audit_symbol_time ON trade_audit_log(symbol, entry_time);
CREATE INDEX idx_trade_audit_status ON trade_audit_log(status);

-- Gap tracking
CREATE INDEX idx_data_gaps_status ON data_gaps(status);

6.3 TimescaleDB Chunking

Tables orderbook_snapshots, price_snapshots, and trades use TimescaleDB hypertables with automatic chunking by time (typically 1 week per chunk).

7. Data Integrity

7.1 Integrity Monitor (replaces v2/v3 watchdogs — Apr 27, 2026, Finding #258)

The legacy polymarket-watchdog.service (v2) and polymarket-watchdog-v3.service were both decommissioned by the Database Integrity Audit Phase 3. The current canonical integrity layer is src/data_tracker/integrity_monitor.py running under polymarket-integrity-monitor.service with a 5-min internal loop.

~51 declarative rules across 6 categories (current count incl. the 4 archive:orderbook_* rules of #383, Aug 21 2026; was 37 verified live via curl /api/integrity May 10, 2026 post-#291 ship; was 36 before Finding #291 added artifact:trades_backfill_state; was 34 before Finding #269 added systemd_timer + systemd_last_run for polymarket-trades-backfill; was 28 before Apr 27 evening when Finding #268 added 6 systemd_last_run:* rules):

• freshness (5): MAX(time) checks per core hypertable (e.g. price_snapshots ≤ 10 min, markets ≤ 60 min).
• null_rate (5): NULL % on tracked-quality columns scoped to post-fix-deploy windows. min_rows floor avoids alerting on small batches.
• systemd (19): 5 always-on services (systemd:* — including integrity-monitor self-check), 7 timer-fire freshness (systemd_timer:*, includes polymarket-trades-backfill.timer from Finding #269), 7 batch-service exit-result (systemd_last_run:*).
• artifact (2): ibkr_snapshot.json (5 min), strategy_analysis_report.json (14 d).
• storage (4): hypertable size vs warn/fail thresholds — safety net while Timescale retention/compression is deferred (operator Q2).
• meta (1): monitor self-heartbeat.

State is written to integrity_status table (migration 027) and atomic-mirrored to /var/log/polymarket/integrity_status.json for the dashboard's /api/integrity endpoint. HIGH/CRITICAL transitions trigger Telegram via AlertCategory.DATA_INTEGRITY. Full architecture: docs/DATA_INTEGRITY.md §2.

7.2 Global Trade Dedupe

To prevent duplicate trades under concurrent collectors or restart races: - trade_dedupe enforces global uniqueness on trade_id - Live trades insert into trade_dedupe first; only new IDs proceed into trades - Finding #455: a failed transaction stores the full raw trade in the durable DB-write spool and does not mark the ID seen. Replay uses the same trade_dedupe transaction and marks it seen only after commit.

7.3 Legacy data_gaps table (read-only since Apr 27, 2026)

The pre-audit data_gaps table is preserved for historical reference but no longer written to. All 804 rows at the time of Phase 0 baseline were tombstones (status='deleted', gap_type='incorrect_source'). Replaced by the declarative rules above. Old gap-type taxonomy retained here for archival context only:

7.4 Data Freshness Thresholds (current — enforced by freshness:* rules)

8. Dashboard Integration

8.1 Data Coverage View

The dashboard displays: - Category-level coverage percentages — in /api/db-stats (category_gaps, data_availability), not on the page. Since Finding #445 this counts markets with rows in the last 7 days (DASHBOARD_RECENT_DAYS); *_start/*_end are the first and last row inside that window; data_availability.window_days and computed_at say so. The page's Per-Market Data Availability table counts the last 7 days (MARKET_AVAILABILITY_DAYS), one grouped COUNT per hypertable over the 25 displayed markets, cached 6 h + single-flight + 15-min failure backoff like coverage (market_availability_computed_at = age). - Timeline visualizations for each data type - Gap indicators (red = missing, green = available) - Real-time service status

8.2 API Endpoints (data + integrity surface)

GET /api/db-stats              - Database statistics
GET /api/dependencies          - External service status
GET /api/data-health           - Data freshness metrics
GET /api/integrity             - Live integrity-monitor snapshot (Phase 4 — Finding #259)
                                  Reads /var/log/polymarket/integrity_status.json (atomic-written
                                  every 5 min); falls back to direct DB read when stale.
                                  Returns: { summary: {total, ok, warn, fail, unknown}, checks: [...] }
GET /api/architecture-findings - Renders docs/FINDINGS.md for the Testing Findings tab

8.3 Cache Strategy

Dashboard data is cached to reduce database load: - db_stats: recomputed by the background loop (DashboardCache, 30 s sleep after each pass); the category coverage inside it is recomputed at most every 6 h (DATA_AVAILABILITY_TTL_SEC, Finding #445), single-flight (a concurrent caller gets the last good value), and keeps serving the last good value if a refresh fails, with no retry for 15 min (DATA_AVAILABILITY_RETRY_SEC) - Deep refresh every 2 hours for market cap data - Real-time data bypasses cache

DB discipline (Finding #445, extends #373): every dashboard read of price_snapshots / trades / orderbook_snapshots carries a time > NOW() - INTERVAL … bound (7 days, including the per-market counts, whose :since is a parameter). Hypertable row counts are approximate_row_count(), never COUNT(*). The hypertable "last updated" is the global latest write, read from the newest non-empty chunk (up to 8 newest chunk ranges from timescaledb_information.chunks, then MAX(time) WHERE time >= :since AND time < :until per chunk as a separate statement — never a subquery, which plans every chunk). Dashboard sessions carry statement_timeout = 60 s and idle_in_transaction_session_timeout = 60 s. The stats pass, the earnings calendar (Gamma) and the positions view (yfinance) run one short transaction per statement (_PerStatementReads), so no transaction outlives one query and no connection is held across HTTP. Tripwire: tests/unit/test_finding_445_dashboard_bounded_sql.py.

Appendix: Quick Reference

Table Row Counts (Apr 27, 2026 — refreshed for Phase 6)

State Files

Service Logs