Database Schema

Complete database schema for PolySuggest, including PostgreSQL tables, TimescaleDB hypertables, and design rationale.

PostgreSQL Tables

Core Entities

positions

Tracks user holdings across markets.

CREATE TABLE positions (
  id BIGSERIAL PRIMARY KEY,
  user_id TEXT NOT NULL,
  market_id TEXT NOT NULL,
  outcome TEXT NOT NULL CHECK (outcome IN ('YES', 'NO')),
  quantity INT NOT NULL,
  entry_price FLOAT NOT NULL,
  entry_date TIMESTAMP NOT NULL,
  closed_at TIMESTAMP,
  exit_price FLOAT,
  pnl FLOAT,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_positions_user_id ON positions(user_id);
CREATE INDEX idx_positions_user_market ON positions(user_id, market_id);
CREATE INDEX idx_positions_market_id ON positions(market_id);
CREATE INDEX idx_positions_created_at ON positions(created_at DESC);

Notes:

quantity – Number of shares (always positive, outcome indicates direction)
entry_price – Fill price when position was opened
exit_price – Fill price when position was closed (NULL if open)
pnl – Realized P&L if closed_at is not NULL

orders

Limit orders and execution records.

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id TEXT NOT NULL,
  market_id TEXT NOT NULL,
  outcome TEXT NOT NULL CHECK (outcome IN ('YES', 'NO')),
  size INT NOT NULL,
  limit_price FLOAT NOT NULL,
  created_at TIMESTAMP NOT NULL,
  filled_at TIMESTAMP,
  fill_price FLOAT,
  filled_size INT DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'pending' 
    CHECK (status IN ('pending', 'filled', 'partially_filled', 'cancelled')),
  cancelled_at TIMESTAMP,
  cancel_reason TEXT,
  slippage FLOAT,
  order_type TEXT DEFAULT 'limit' CHECK (order_type IN ('limit', 'market')),
  tif TEXT DEFAULT 'gtc' CHECK (tif IN ('gtc', 'ioc', 'fok')),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_market_id ON orders(market_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

Notes:

size – Order size (shares)
filled_size – How many shares actually filled
slippage – Actual slippage vs limit price ((fill_price / limit_price) - 1)
tif – Time in force (good-til-canceled, immediate-or-cancel, fill-or-kill)

suggestions

Market suggestions generated by AI engine.

CREATE TABLE suggestions (
  id BIGSERIAL PRIMARY KEY,
  trend_topic TEXT NOT NULL,
  suggested_outcome TEXT NOT NULL CHECK (suggested_outcome IN ('YES', 'NO')),
  confidence FLOAT NOT NULL CHECK (confidence >= 0 AND confidence <= 1),
  reasoning TEXT,
  created_at TIMESTAMP NOT NULL,
  model_version TEXT,
  resolved BOOLEAN DEFAULT FALSE,
  resolved_at TIMESTAMP,
  actual_outcome TEXT CHECK (actual_outcome IS NULL OR actual_outcome IN ('YES', 'NO')),
  calibration_error FLOAT,
  model_accuracy FLOAT,
  roi FLOAT,
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_suggestions_created_at ON suggestions(created_at DESC);
CREATE INDEX idx_suggestions_confidence ON suggestions(confidence DESC);
CREATE INDEX idx_suggestions_resolved ON suggestions(resolved);

edges

Detected probability mismatches (market vs fair probability).

CREATE TABLE edges (
  id BIGSERIAL PRIMARY KEY,
  market_id TEXT NOT NULL,
  detected_at TIMESTAMP NOT NULL,
  fair_probability FLOAT NOT NULL,
  market_probability FLOAT NOT NULL,
  edge_pct FLOAT NOT NULL,
  liquidity_available FLOAT NOT NULL,
  trend_sentiment FLOAT,
  decay_score FLOAT DEFAULT 1.0,
  resolved_at TIMESTAMP,
  actual_outcome TEXT CHECK (actual_outcome IS NULL OR actual_outcome IN ('YES', 'NO')),
  pnl FLOAT,
  confidence FLOAT,
  model_name TEXT,
  credible_interval_lower FLOAT,
  credible_interval_upper FLOAT,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_edges_market_id ON edges(market_id);
CREATE INDEX idx_edges_detected_at ON edges(detected_at DESC);
CREATE INDEX idx_edges_edge_pct ON edges(ABS(edge_pct) DESC);
CREATE INDEX idx_edges_resolved ON edges(resolved_at);

TimescaleDB Hypertables

market_prices

OHLCV data for all markets.

CREATE TABLE market_prices (
  time TIMESTAMP NOT NULL,
  market_id TEXT NOT NULL,
  open FLOAT,
  high FLOAT,
  low FLOAT,
  close FLOAT,
  volume FLOAT,
  bid FLOAT,
  ask FLOAT,
  yes_price FLOAT,
  no_price FLOAT,
  spread_pct FLOAT,
  liquidity FLOAT
);

SELECT create_hypertable(
  'market_prices',
  'time',
  if_not_exists => TRUE,
  chunk_time_interval => INTERVAL '1 day'
);

CREATE INDEX idx_market_prices_market_time 
  ON market_prices (market_id, time DESC);

Database Initialization

# Create databases
createdb polysuggest
createdb timescale

# Enable TimescaleDB
psql polysuggest -c "CREATE EXTENSION timescaledb"

# Run migrations
cd polymarket-ai-market-suggestor
alembic upgrade head

See also: Architecture Guide for complete data flow.

PostgreSQL Tables​

Core Entities​

positions​

orders​

suggestions​

edges​

TimescaleDB Hypertables​

market_prices​

Database Initialization​