SQL Workbench

Curated queries for forecast-vs-actual analysis. Per-query filters update the SQL inline; copy and run against your local PostgreSQL.

Connection

Host: localhost

Port: 5432

Database: kalshi

User: william

Sync runs at 2:15am, 10:30am, 9pm, 11:30pm ET. Scans table rolls last 60 days (extended May 5 from 7); settlements + fills are full history.

Quick schema reference

settlementsone per (city, date, market_type, strategy, entry_timing)

city, date, market_type

'high' | 'low'

strategy, entry_timing

'active' | 'forecast-no' / 'today' | 'nextday'

forecast_high, actual_high

REAL — both in °F

forecast_error

REAL — actual − forecast (positive = actual warmer than forecast)

forecast_bracket, winning_bracket

VARCHAR — labels like '88-89'

source

'kalshi' | 'nws-obs' — which path the actual_high came from

total_cost, total_payout, total_profit

REAL — $ amounts

scansone per (city, run_id, market_type) — rolling last 60 days

city, scan_code, market_type

scan_code = 'NYC-NEXTDAY' / 'NYC-TODAYNO' / etc.

date, run_id, timestamp

date = target market date

forecast_temp, forecast_high, forecast_low

REAL

is_next_day, entry_timing

BOOLEAN; 'today' | 'nextday'

pricing_source, effective_cap, edge_cap

'edge' | 'parity' | 'blended'

forecast_edge

'top' | 'bottom' | NULL

no_ask, signal

BUY_NO | HOLD

afd_factor, afd_tier, has_trough_or_front

tier = responsive/neutral/inverted

ensemble_spread, ensemble_diff

REAL — model disagreement (°F)

depth_top, depth_3c, depth_5c

orderbook depth at NO ask tiers

historical_conviction90-180 day backfill, RCC-ACIS actuals

city, date, market_type

forecast_high, actual_high, forecast_error, abs_error, bracket_miss

RCC-ACIS-derived actuals

afd_factor, afd_score, afd_keywords, afd_detail

ensemble_spread, ensemble_mean, ensemble_diff

wx_today_afd_factor, wx_today_afd_tier

live snapshots

kalshi_fillsauthoritative fill ledger

fill_id, ticker, domain

domain = 'weather' | 'mlb' | 'nba' | 'crypto'

side, action, quantity, price_usd, fee_usd

city, game_date, sports_market_type, line, team

settlement_status, result, realized_pnl_usd

matched_strategy

'active' / 'forecast-no-nextday' / 'forecast-no-today' / 'manual'