Database Schema Documentation for Analytics¶

Last Updated: 16 February 2026

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.

Important: Multi-Account Support¶

Critical Change: The system now supports multiple trading accounts per trader. This fundamentally changes how data should be queried and filtered.

Key Principles¶

account is the Primary Identifier - Always use account column for filtering trading data, not trader_id.
trader_id is Unreliable in Data Tables - Accounts can be reassigned between traders via the admin UI. When this happens:
Historical records (trades, fills, etc.) retain the OLD trader_id
The trader_platforms table reflects the CURRENT ownership
Therefore, trader_id in data tables may be stale/incorrect
Source of Truth: trader_platforms - To determine which accounts belong to a trader, ALWAYS query trader_platforms table. Never trust trader_id in trades, fills, or other data tables.
group_id Limitations - Similar to trader_id, group_id is written at record creation time and may not reflect current group membership.

Query Pattern Change¶

OLD (Deprecated):

SELECT * FROM trades WHERE trader_id = 123;  -- UNRELIABLE!

NEW (Correct):

-- Step 1: Get accounts for trader from source of truth
SELECT platform_account FROM trader_platforms WHERE trader_id = 123;

-- Step 2: Query data by account
SELECT * FROM trades WHERE account IN ('ACC001', 'ACC002');

Future Deprecation Notice¶

The trader_id and group_id columns in data tables (trades, fills, continuous aggregates, etc.) are kept for backwards compatibility but may be removed in future versions. New code should use account for filtering.

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)
stellar_trader_name	Text	Stellar platform trader identifier (e.g., 'RHODZIC1') - used for matching Stellar fills
description	Text	Optional notes about the trader
date_joined	DateTime	When the trader joined the system
is_archived	Boolean	Archive flag (NOT NULL, default: false)
offices	Text	Office location (nullable)
floor	Integer	Floor number (nullable)
stage	Integer	Stage identifier (nullable)
style	Text	Trading/style label (nullable)

Notes: - stellar_trader_name is only populated for traders who trade on the Stellar platform - TT-only traders will have NULL for stellar_trader_name

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.

Column	Type	Description
id (PK)	Integer	Unique platform identifier
name	Text	Platform name

Current Platforms: | ID | Name | Description | |----|------|-------------| | 1 | TT | Trading Technologies - fills fetched via TT API | | 2 | Stellar | Stellar platform - fills arrive via FIX protocol into raw_fills_fix |

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

3. trader_platforms¶

Junction table linking traders to their platform accounts. This is the source of truth for account ownership.

Important: This table is the ONLY reliable source for determining which accounts belong to which trader. One trader can have multiple accounts across different platforms.

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	Canonical account identifier (e.g., 'LJ4AX007', 'BPC_TRADER1')
platform_trader_id	Text	Platform-specific trader ID
account_special_aliases	Text[]	Array of special account aliases for Stellar matching (e.g., ['AXIA', 'GHFinancial'])
is_sim_account	Boolean	Whether this is a simulation account
opt_out	Boolean	Whether this account opted out of comparative analytics (account-level opt-out)

Unique Constraint: (trader_id, platform_id, platform_account)

Multi-Account Support: - One trader can have multiple trader_platforms entries (multiple accounts) - platform_account is the canonical account name used in fills.account and trades.account - For Stellar: accounts may arrive with different aliases that resolve to the same canonical account

Stellar Account Alias Matching: - account_special_aliases: Special aliases that don't contain the canonical account name (e.g., 'AXIA') - Matching logic: An incoming fill matches if the account contains platform_account OR is in account_special_aliases - Example: (RHODZIC1, 'AXIA') → matches trader with platform_account='LJ4AX007' and account_special_aliases=['AXIA']

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
is_archived	Boolean	Archive flag (NOT NULL, default: false)

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	⚠️ Written at creation time - may be stale if account reassigned. Use `account` for filtering.
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	⚠️ Written at creation time - may not reflect current group membership
account	Text	Primary identifier for filtering - canonical account name from `trader_platforms.platform_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 (e.g., 'Outright')
is_sim_account	Boolean	Simulation account flag
trade_ids	JSON	Associated trade IDs (max 2)
created_at	DateTime	When record was created

Warning: Do NOT filter by trader_id - accounts can be reassigned between traders. Always filter by account column instead.

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	⚠️ Written at creation time - may be stale if account reassigned. Use `account` for filtering.
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	⚠️ Written at creation time - may not reflect current group membership
account	Text	Primary identifier for filtering - canonical account name from `trader_platforms.platform_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

Warning: Do NOT filter by trader_id - accounts can be reassigned between traders. Always filter by account column instead.

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	⚠️ Written at creation time - may be stale. Use `account` for filtering.
account	Text	Primary identifier for filtering - canonical account name
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

Warning: Do NOT filter by trader_id - always filter by account instead.

Indexes: - ix_intraday_pnl_trader_datetime on (trader_id, datetime DESC) - idx_intraday_profit_loss_trader_account_datetime on (trader_id, account, datetime) - 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 account and product.

Column	Type	Description
id (PK)	Integer	Unique record identifier
trader_id (FK)	Integer	⚠️ Kept for backwards compatibility only - may be stale
account	Text	Primary identifier for filtering - canonical account name
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

Warning: Do NOT filter by trader_id - always filter by account instead.

Unique Constraint: (account, product_id, date) - Note: trader_id is NOT part of uniqueness

Indexes: - ix_daily_pnl_trader_date on (trader_id, date DESC) - idx_intraday_daily_profit_loss_account_date on (account, date) - 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

Stellar Integration Tables¶

These tables support the Stellar platform integration, which processes fills arriving via FIX protocol.

13a. products_stellar¶

Maps Stellar product symbols to the main products table.

Column	Type	Description
id (PK)	Integer	Unique mapping identifier
symbol	Text	Stellar product symbol
exchange	Text	Stellar exchange code (e.g., 'XCME', 'XEUR')
currency	Text	Trading currency
product_id (FK)	Integer	References `products.id` (NULL if unmapped)
created_at	DateTime	When mapping was created
updated_at	DateTime	Last update time

Unique Constraint: (symbol, exchange)

Exchange Mapping: Stellar uses different exchange codes than TT: | Stellar Code | Maps To | |--------------|---------| | XCME, XCBT, XNYM, XCEC | CME | | XCBF | CFE | | XEUR | EUREX | | XMOD | MX | | IFLL | ICE_L | | IFEU, IFUS, IFLX | ICE | | XSFE | ASX | | XHKG | HKEX | | XOSE | JPX | | XSIM | SGX | | XMON, XMAT, XEUE | EURONEXT | | XMRV | MEFF |

Notes: - Products with NULL product_id require manual mapping - Auto-mapping is attempted when new products are encountered

13b. raw_fills_fix¶

Raw fills from Stellar platform arriving via FIX protocol. Processed into fills table.

Column	Type	Description
id (PK)	Integer	Unique record identifier
exec_id	Text	FIX execution ID
platform	Text	Platform identifier (e.g., 'I_STELLAR')
trader	Text	Stellar trader name (e.g., 'RHODZIC1') - used for lookup
account	Text	Exchange account alias (may vary per exchange)
symbol	Text	Product symbol
exchange	Text	Exchange code
maturity_month_year	Text	Contract expiry
side	Integer	1=Buy, 2=Sell
price	Numeric	Execution price
quantity	Integer	Fill quantity
timestamp	DateTime	Execution timestamp
currency	Text	Trading currency
processed	Boolean	Whether fill has been processed into `fills` table
processed_at	DateTime	When fill was processed
created_at	DateTime	When record arrived

Processing Flow: 1. FIX messages arrive and are stored in raw_fills_fix 2. Processing DAG matches (trader, account) to trader_platforms using: - traders.stellar_trader_name for trader lookup - Pattern matching: if account contains platform_account - Special aliases: if account is in account_special_aliases 3. Product mapped via products_stellar 4. Processed fill inserted into fills with canonical platform_account 5. processed flag set to true

Unprocessed Fills: - Fills with unknown (trader, account) combinations stay processed=false - Fills with unmapped products (NULL product_id) stay processed=false - Reported in daily health checks for manual resolution

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. They aggregate data from the trades table.

Important: These aggregates include both trader_id and account columns. Always filter by account, not trader_id.

📊 daily_product_profit¶

Daily aggregated profit by account and product.

Column	Type	Description
group_id	Integer	⚠️ Written at creation time - may not reflect current membership
trader_id	Integer	⚠️ Written at creation time - may be stale. Use `account` for filtering.
account	Text	Primary identifier for filtering - canonical account name
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 Index: (group_id, trader_id, account, symbol, day)

📊 weekly_product_profit¶

Weekly aggregated profit by account and product.

Column	Type	Description
group_id	Integer	⚠️ Written at creation time - may not reflect current membership
trader_id	Integer	⚠️ Written at creation time - may be stale. Use `account` for filtering.
account	Text	Primary identifier for filtering - canonical account name
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 Index: (group_id, trader_id, account, symbol, week)

📊 monthly_product_profit¶

Monthly aggregated profit by account and product.

Column	Type	Description
group_id	Integer	⚠️ Written at creation time - may not reflect current membership
trader_id	Integer	⚠️ Written at creation time - may be stale. Use `account` for filtering.
account	Text	Primary identifier for filtering - canonical account name
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 Index: (group_id, trader_id, account, symbol, month)

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.
⚠️ Multi-Account Support - Critical:
Filter by account, NOT by trader_id - trader_id is unreliable and may be stale
One trader can have multiple accounts
To find accounts for a trader, query trader_platforms table
The trader_id and group_id columns in data tables are kept for backwards compatibility but should not be used for filtering
Platform Support:
Platform ID 1 = TT (Trading Technologies)
Platform ID 2 = Stellar (fills arrive via FIX protocol)
Stellar fills are first stored in raw_fills_fix then processed into fills