Database Schema Documentation for Analytics¶

Last Updated: 2nd December 2025

Overview¶

This document describes the database schema for the Comparative Analytics trading system. The database uses PostgreSQL with TimescaleDB extensions for efficient time-series data storage and analysis.

Core Tables¶

1. traders¶

Stores information about individual traders in the system.

Column	Type	Description
id (PK)	Integer	Unique trader identifier
name	Text	Trader's name (optional)
date_joined	DateTime	When the trader joined the system

Relationships: - Has many trader_platforms (trader accounts on different platforms) - Has many fills (individual trade executions) - Has many trades (completed trades) - Has many group_memberships (group affiliations) - Has many intraday_profit_loss records - Has many daily_profit_loss records - Has many tt_accounts_risk_limits (risk limit snapshots) - Has many custom_tags (user-defined trade tags)

2. platforms¶

Trading platforms integrated with the system (e.g., TTC).

Column	Type	Description
id (PK)	Integer	Unique platform identifier
name	Text	Platform name (e.g., 'TTC')

Relationships: - Has many trader_platforms (trader accounts) - Has many fills and trades

3. trader_platforms¶

Junction table linking traders to their platform accounts.

Column	Type	Description
id (PK)	Integer	Unique record identifier
trader_id (FK)	Integer	References `traders.id`
platform_id (FK)	Integer	References `platforms.id`
platform_account	Text	Account identifier on the platform
platform_trader_id	Text	Platform-specific trader ID
is_sim_account	Boolean	Whether this is a simulation account
opt_out	Boolean	Whether trader opted out of tracking

Unique Constraint: (trader_id, platform_id, platform_account)

4. products¶

Tradable financial products/instruments.

Column	Type	Description
id (PK)	Integer	Unique product identifier
exchange	Text	Exchange name (e.g., 'CME', 'NYSE')
symbol	Text	Product symbol (e.g., 'ES', 'NQ')
name	Text	Full product name
currency	Text	Trading currency (e.g., 'USD', 'EUR')
tick_size	Numeric	Minimum price movement
price_per_tick	Numeric	Value of one tick in currency
tt_instrument_id	Text	TT platform instrument ID
tt_product_id	Text	TT platform product ID (indexed)
color	Text	Hex color for UI visualization
created_at	DateTime	When product was added
updated_at	DateTime	Last update time

Unique Constraint: (exchange, symbol)

Indexes: - ix_products_tt_product_id on (tt_product_id)

Relationships: - Has many fills, trades, tick_data - Has one products_tickdata mapping - Has one product_color assignment - Has many tt_accounts_risk_limits (product risk limits)

5. groups¶

Groups for organizing traders (e.g., by team, strategy, or company). For now we have Group 1 Axia where all the traders chosen by Axia are and Group 2 that has all the other traders that we fetch data for using the TT api.

Column	Type	Description
id (PK)	Integer	Unique group identifier
name	Text	Group name (unique)
description	Text	Group description
created_at	DateTime	When group was created

Relationships: - Has many group_members (traders in the group) - Has many trades and fills

6. group_members¶

Junction table for trader-group relationships.

Column	Type	Description
id (PK)	Integer	Unique membership identifier
group_id (FK)	Integer	References `groups.id`
trader_id (FK)	Integer	References `traders.id`
role	Text	Role in group ('admin' or 'trader')
added_at	DateTime	When trader joined the group

Unique Constraint: (group_id, trader_id)

Trading Data Tables (TimescaleDB Hypertables)¶

7. fills ⏱️¶

Individual trade executions (TimescaleDB hypertable partitioned by timestamp).

Column	Type	Description
id	UUID	Unique fill identifier
trader_id (PK)	Integer	References `traders.id`
platform_id (PK)	Integer	References `platforms.id`
contract (PK)	Text	Contract specification
price (PK)	Numeric	Execution price
quantity (PK)	Float	Fill quantity
timestamp (PK)	DateTime	Execution timestamp
symbol	Text	Product symbol
product_id (FK)	Integer	References `products.id`
group_id (FK)	Integer	References `groups.id`
account	Text	Trading account
side	Integer	1=Buy, 2=Sell
platform_trader_id	Text	Platform-specific trader ID
exchange	Text	Exchange name
fill_type	Text	Type of fill
is_sim_account	Boolean	Simulation account flag
trade_ids	JSON	Associated trade IDs (max 2)
created_at	DateTime	When record was created

Indexes: - ix_fills_id on (id) - TimescaleDB time-based partitioning on timestamp

8. trades ⏱️¶

Completed trades (entry + exit) - TimescaleDB hypertable partitioned by open_time.

Column	Type	Description
id	UUID	Unique trade identifier
trader_id (PK)	Integer	References `traders.id`
open_time (PK)	DateTime	Trade open timestamp
product_id (PK)	Integer	References `products.id`
side (PK)	Integer	1=Long, 2=Short
contract (PK)	Text	Contract specification
platform_id (FK)	Integer	References `platforms.id`
group_id (FK)	Integer	References `groups.id`
account	Text	Trading account
close_time	DateTime	Trade close timestamp
open_price	Numeric	Entry price
close_price	Numeric	Exit price
profit	Float	Profit/Loss in USD
original_currency	Text	Original trading currency
original_currency_profit	Float	P/L in original currency
original_currency_exchange_rate	Float	Exchange rate used
total_quantity	Float	Total trade size
is_sim_account	Boolean	Simulation account flag
is_processed	Boolean	Processing status
is_intraday_pnl_calculated	Boolean	Intraday P/L calculation flag
fill_ids	JSON	Associated fill IDs
description	Text	Trade notes/description (max 5000 chars, optional)
created_at	DateTime	Record creation time
updated_at	DateTime	Last update time

Indexes: - trades_open_time_idx on (open_time DESC) - ix_trades_id on (id, open_time) - unique - ix_trades_group_profit_desc_close_time on (group_id, profit DESC, close_time)

Relationships: - Has many trade_tags (tag assignments) - Has many intraday_profit_loss records

9. intraday_profit_loss ⏱️¶

Intraday P/L tracking with OHLC pattern (TimescaleDB hypertable).

Column	Type	Description
trade_id (PK)	UUID	References `trades.id`
datetime (PK)	DateTime	Candle timestamp
trader_id (FK)	Integer	References `traders.id`
product_id (FK)	Integer	References `products.id`
product_contract	Text	Contract specification
open_price	Float	Trade open price
open_pnl	Float	Opening P/L for interval
high_pnl	Float	Highest P/L in interval
low_pnl	Float	Lowest P/L in interval
close_pnl	Float	Closing P/L for interval
interval	Enum	Time interval ('1m', '5m', '15m', '30m', '1h', '4h', '1d')
is_daily_pnl_calculated	Boolean	Daily P/L calculation flag

Indexes: - ix_intraday_pnl_trader_datetime on (trader_id, datetime DESC) - ix_intraday_pnl_product_datetime on (product_id, datetime DESC) - ix_intraday_pnl_datetime_desc on (datetime DESC) - ix_intraday_pnl_is_daily_pnl_calculated on (is_daily_pnl_calculated)

10. intraday_daily_profit_loss¶

Daily aggregated P/L per trader and product.

Column	Type	Description
id (PK)	Integer	Unique record identifier
trader_id (FK)	Integer	References `traders.id`
product_id (FK)	Integer	References `products.id` (NULL = all products)
date	Date	Trading date
open_pnl	Float	Opening P/L for the day
high_pnl	Float	Highest P/L during the day
low_pnl	Float	Lowest P/L during the day
close_pnl	Float	Closing P/L for the day
total_trades	Integer	Number of trades
created_at	DateTime	Record creation time
updated_at	DateTime	Last update time

Unique Constraint: (trader_id, product_id, date)

Indexes: - ix_daily_pnl_trader_date on (trader_id, date DESC) - ix_daily_pnl_product_date on (product_id, date DESC) - ix_daily_pnl_date_desc on (date DESC) - ix_daily_pnl_trader_product_date on (trader_id, product_id, date DESC)

Market Data Tables¶

11. tick_data ⏱️¶

Market tick data (TimescaleDB hypertable). Data has one month expiry ‼️ and are erased after one month

Column	Type	Description
tickdata_symbol (PK)	Text	Tick data symbol
datetime (PK)	DateTime	Tick timestamp
product_id (FK)	Integer	References `products.id`
product_contract	Text	Contract specification
price	Float	Tick price

Indexes: - Multiple indexes for efficient time-series queries - BRIN index for space-efficient range queries

12. products_tickdata¶

Maps products to their tick data symbols.

Column	Type	Description
id (PK)	Integer	Unique mapping identifier
product_id (FK)	Integer	References `products.id` (unique)
product_symbol	Text	Product symbol (unique)
tickdata_symbol	Text	Tick data symbol (unique)
tickdata_name	Text	Tick data name
tickdata_exchange	Text	Exchange for tick data
timezone	Text	Market timezone
update_time	Time	Daily update time
price_multiplier	Float	Price conversion multiplier

13. ohlc_daily_prices¶

Daily OHLC (Open/High/Low/Close) prices for products fetched from TickData API.

Important: Stores FRONT MONTH data only - no specific contract tracking.

Column	Type	Description
id (PK)	Integer	Unique record identifier
products_tickdata_id (FK)	Integer	References `products_tickdata.id`
product_symbol	Text	TT base symbol (e.g., "ES") - denormalized for convenience
tickdata_symbol	Text	TickData base symbol (e.g., "ES") - denormalized for convenience
trade_date	Date	Trading date (UTC, weekdays only)
open_price	Numeric	Opening price (front month)
high_price	Numeric	High price (front month)
low_price	Numeric	Low price (front month)
close_price	Numeric	Closing price (front month)
volume	Integer	Trading volume
created_at	DateTime	Record creation time
updated_at	DateTime	Last update time

Unique Constraint: (products_tickdata_id, trade_date)

Indexes: - ix_ohlc_symbol_date on (tickdata_symbol, trade_date DESC) - ix_ohlc_daily_prices_products_tickdata_id on (products_tickdata_id) - ix_ohlc_daily_prices_product_symbol on (product_symbol) - ix_ohlc_daily_prices_tickdata_symbol on (tickdata_symbol) - ix_ohlc_daily_prices_trade_date on (trade_date)

Data Source: - Fetched hourly starting at 4:30 AM UTC via DWH-fetch-ohlc-prices DAG - 2-week rolling lookback ensures automatic gap-filling - Only front month contract prices (contract rolls reflected automatically)

Usage Notes: - Prices stored without trailing zeros (Numeric type without fixed scale) - Symbols are denormalized from products_tickdata for query convenience - See docs/development/ohlc-data-usage-guide.md for usage examples

Support Tables¶

14. exchange_rates¶

Currency exchange rates for P/L calculations.

Column	Type	Description
id (PK)	Integer	Unique rate identifier
currency	Text	Currency code
base_currency	Text	Base currency (default: 'USD')
date	DateTime	Rate date
exchange_rate	Numeric	Exchange rate value
created_at	DateTime	Record creation time
updated_at	DateTime	Last update time

Unique Constraint: (currency, date)

15. calendar_events¶

Market events and announcements.

Column	Type	Description
id (PK)	Integer	Unique event identifier
display_name	Text	Event display name
refinit_code	Text	External event code (optional)
manual	Boolean	Manually created flag

16. event_days¶

Individual days when events occur.

Column	Type	Description
calendar_event_id (PK)	Integer	References `calendar_events.id`
event_day (PK)	Date	Event date
start_time	DateTime	Event start time
end_time	DateTime	Event end time

Tagging Tables¶

Trade tagging system for categorizing and annotating trades. Supports a 3-level hierarchical tag structure.

17. tags¶

Fixed (predefined) tags organized in a 2-level hierarchy.

Column	Type	Description
id (PK)	Integer	Unique tag identifier
name	Text	Tag name (max 100 chars)
parent_id (FK)	Integer	References `tags.id` (NULL for Layer 1)
created_at	DateTime	When tag was created

Tag Hierarchy: - Layer 1 (Categories): Tech, Data, Flow, CBank, News (parent_id = NULL) - Layer 2 (Subcategories): Candlesticks, Profile, DOM, Footprint, Other, etc. (parent_id references Layer 1)

Relationships: - Has many children tags (Layer 2 tags) - Has many trade_tags (tag assignments) - Has many custom_tags (user-defined tags under Layer 2)

18. custom_tags¶

User-defined tags (Layer 3) created by traders under Layer 2 "Other" tags.

Column	Type	Description
id (PK)	Integer	Unique custom tag identifier
trader_id	Integer	Trader who created the tag
parent_tag_id (FK)	Integer	References `tags.id` (must be Layer 2)
name	Text	Custom tag name (max 100 chars)
created_at	DateTime	When tag was created

Unique Constraint: (trader_id, parent_tag_id, name)

Usage Notes: - Custom tags are trader-specific (not shared between traders) - Can only be created under Layer 2 tags (typically "Other" subcategory) - Example: Tech → Other → "morning-session"

Relationships: - Belongs to a trader - Belongs to a parent tag (Layer 2) - Has many trade_tags (tag assignments)

19. trade_tags¶

Junction table linking trades to tags (both fixed and custom).

Column	Type	Description
id (PK)	Integer	Unique assignment identifier
trade_id	Text	References `trades.id` (UUID as string)
tag_id (FK)	Integer	References `tags.id` (nullable)
custom_tag_id (FK)	Integer	References `custom_tags.id` (nullable)
created_at	DateTime	When tag was assigned

Constraint: Exactly one of tag_id or custom_tag_id must be non-null.

Tag Hierarchy Rules: 1. ONE Layer 1 tag per trade - Each trade belongs to one category only 2. MULTIPLE Layer 2 tags - Under that Layer 1, multiple Layer 2 tags allowed 3. MULTIPLE custom tags - Under Layer 2 "Other" tags, multiple custom tags allowed

Relationships: - Belongs to a trade - Belongs to a tag (fixed) OR custom_tag (user-defined)

20. tt_accounts_risk_limits¶

Daily snapshots of TT account risk limits for trader-product combinations.

Purpose: Tracks the maximum position limits (maxPosLimit) assigned to traders for each product in both SIM and LIVE environments. Snapshots are taken daily at 10 PM UTC via the Trading-Fetch-TT-Account-Limits DAG.

Important: This is a daily snapshot system. Intra-day limit changes are only reflected in the next daily snapshot.

Column	Type	Description
id (PK)	Integer	Unique record identifier
trader_id (FK)	Integer	References `traders.id` (indexed)
trader_account_id	Integer	TT accountId
trader_account_name	Text	TT accountName (e.g., "BPC_DDUQUESNEY")
product_id (FK)	Integer	References `products.id` (nullable, indexed)
product_tt_id	Text	TT productId (e.g., "12331099295610969204")
product_symbol	Text	Product symbol (e.g., "ES")
exchange	Text	Exchange name
max_pos_limit	Integer	Maximum position limit
date	Date	Snapshot date (UTC)
created_at	DateTime	Record creation time
updated_at	DateTime	Last update time (for multiple snapshots same day)

Unique Constraint: (trader_id, trader_account_id, product_tt_id, date)

Indexes: - ix_tt_accounts_risk_limits_product_tt_id on (product_tt_id) - Index on (trader_id) - Index on (product_id)

Data Source: - TT REST API endpoints: - /user/{userId}/accounts - to get trader accounts - /account/{accountId}/limits - to get product limits for each account

Product Matching Strategy: 1. PRIMARY: Match by product_tt_id → products.tt_product_id 2. FALLBACK: Match by product_symbol → products.symbol if TT product ID not found 3. NO MATCH: Store with NULL product_id (can backfill later when product added)

Usage Notes: - Nullable product_id allows capturing limits even when product not yet in our database - Multiple snapshots per day UPDATE existing records (same date) - No backfilling capability - API only returns current limits - Useful for analyzing trader limit utilization and risk management

TimescaleDB Continuous Aggregates (Views)¶

These are pre-computed materialized views that automatically update:

📊 daily_product_profit¶

Daily aggregated profit by trader, group, and product.

Column	Type	Description
group_id	Integer	Group identifier
trader_id	Integer	Trader identifier
symbol	Text	Product symbol
day	Date	Trading day
total_profit	Float	Total daily profit
total_quantity	Float	Total volume traded

Update Frequency: Every 5 minutes

📊 weekly_product_profit¶

Weekly aggregated profit by trader, group, and product.

Column	Type	Description
group_id	Integer	Group identifier
trader_id	Integer	Trader identifier
symbol	Text	Product symbol
week	Date	Week start date
total_profit	Float	Total weekly profit
total_quantity	Float	Total volume traded

Update Frequency: Every 5 minutes

📊 monthly_product_profit¶

Monthly aggregated profit by trader, group, and product.

Column	Type	Description
group_id	Integer	Group identifier
trader_id	Integer	Trader identifier
symbol	Text	Product symbol
month	Date	Month start date
total_profit	Float	Total monthly profit
total_quantity	Float	Total volume traded

Update Frequency: Every 5 minutes

Entity Relationship Diagram¶

erDiagram
    TRADERS ||--o{ TRADER_PLATFORMS : "has accounts on"
    PLATFORMS ||--o{ TRADER_PLATFORMS : "has traders"
    TRADERS ||--o{ GROUP_MEMBERS : "belongs to"
    GROUPS ||--o{ GROUP_MEMBERS : "has members"

    TRADERS ||--o{ FILLS : "executes"
    PLATFORMS ||--o{ FILLS : "processes"
    PRODUCTS ||--o{ FILLS : "traded in"
    GROUPS ||--o{ FILLS : "tracks"

    TRADERS ||--o{ TRADES : "completes"
    PLATFORMS ||--o{ TRADES : "facilitates"
    PRODUCTS ||--o{ TRADES : "involves"
    GROUPS ||--o{ TRADES : "monitors"

    TRADES ||--o{ INTRADAY_PROFIT_LOSS : "generates"
    TRADERS ||--o{ INTRADAY_PROFIT_LOSS : "has"
    PRODUCTS ||--o{ INTRADAY_PROFIT_LOSS : "for"

    TRADERS ||--o{ DAILY_PROFIT_LOSS : "accumulates"
    PRODUCTS ||--o{ DAILY_PROFIT_LOSS : "per"

    PRODUCTS ||--|| PRODUCTS_TICKDATA : "maps to"
    PRODUCTS ||--o{ TICK_DATA : "has prices"
    PRODUCTS ||--o| PRODUCT_COLORS : "has color"
    PRODUCTS ||--o{ TT_ACCOUNTS_RISK_LIMITS : "has risk limits for"

    PRODUCTS_TICKDATA ||--o{ OHLC_DAILY_PRICES : "has daily prices"

    TRADERS ||--o{ TT_ACCOUNTS_RISK_LIMITS : "has risk limits"

    CALENDAR_EVENTS ||--o{ EVENT_DAYS : "occurs on"

    TAGS ||--o{ TAGS : "has children"
    TAGS ||--o{ CUSTOM_TAGS : "has custom tags"
    TAGS ||--o{ TRADE_TAGS : "assigned via"
    TRADERS ||--o{ CUSTOM_TAGS : "creates"
    CUSTOM_TAGS ||--o{ TRADE_TAGS : "assigned via"
    TRADES ||--o{ TRADE_TAGS : "has tags"

Key Points for Power BI Users¶

Primary Keys: Tables use composite primary keys for TimescaleDB optimization. Use the id field as the unique identifier when available.
Time-Series Data: Tables marked with ⏱️ are TimescaleDB hypertables optimized for time-based queries.
Continuous Aggregates: Use the pre-computed views (daily_product_profit, weekly_product_profit, monthly_product_profit) for faster dashboard performance.
Relationships:
Start with trades table for complete trade analysis
Use fills for execution-level detail
Join with traders, groups, and products for dimensional analysis
Performance Tips:
Filter by date ranges when querying time-series tables
Use the continuous aggregates for summary dashboards
The intraday_daily_profit_loss table provides pre-calculated daily P/L
For daily OHLC prices, use ohlc_daily_prices table with denormalized symbols (no joins needed)
Currency Handling: All profit values are normalized to USD. Original currency values and exchange rates are preserved in the trades table.
Simulation vs Live: Use is_sim_account flag to filter between simulation and live trading data.