TradeMate Data Model

All tables live in the trademate_main Postgres database (TimescaleDB + pgvector). A second database trademate_journal is used by the TradeTally fork (TM-27).

Multi-tenancy

Row-Level Security (RLS) is enabled on every user-scoped table. FastAPI middleware injects the authenticated user's ID via:

sql SELECT set_config('app.current_user', '<user_uuid>', true);

Every policy uses:

sql USING (user_id::text = current_setting('app.current_user', true))

The true flag returns NULL (not an error) when the variable is unset, so unauthenticated or service-role sessions see 0 rows by default. Alembic migrations run as the Postgres superuser (BYPASSRLS).

Extensions

Extension	Purpose
`pgcrypto`	`gen_random_uuid()` server-side UUID generation
`vector`	pgvector — embedding columns (used by TM-35 news_items)
`timescaledb`	Hypertables for OHLCV / economic / fundamentals (TM-35)

Entity Relationship Diagram

erDiagram
    user_profiles {
        string user_id PK
        tierenum tier
        string full_name
        string avatar_url
        jsonb preferences
        timestamptz created_at
        timestamptz updated_at
    }

    subscriptions {
        uuid id PK
        string user_id FK
        tierenum tier
        string dodo_customer_id
        string dodo_subscription_id
        subscriptionstatusenum status
        timestamptz current_period_start
        timestamptz current_period_end
        timestamptz cancelled_at
    }

    broker_connections {
        uuid id PK
        string user_id FK
        brokerenum broker
        text encrypted_access_token
        text encrypted_refresh_token
        string account_id
        string account_number
        bool is_paper
        bool is_active
        timestamptz token_expires_at
    }

    llm_provider_keys {
        uuid id PK
        string user_id FK
        llmproviderenum provider
        text encrypted_api_key
        bool is_active
    }

    portfolios {
        uuid id PK
        string user_id FK
        string name
        uuid broker_connection_id FK
        bool is_paper_trading
        string base_currency
    }

    risk_settings {
        uuid id PK
        uuid portfolio_id FK
        string user_id
        float max_position_size_pct
        float max_drawdown_pct
        float daily_loss_limit_pct
        float sector_concentration_pct
        int max_open_positions
    }

    strategy_configs {
        uuid id PK
        uuid portfolio_id FK
        string user_id
        strategyenum strategy
        bool is_active
        float allocation_pct
        jsonb params
    }

    watchlists {
        uuid id PK
        string user_id FK
        string name
        bool is_smart
        text[] tickers
        text[] current_tickers
        timestamptz last_refreshed_at
    }

    smart_watchlist_rules {
        uuid id PK
        uuid watchlist_id FK
        string user_id
        string field_name
        string operator
        jsonb value
        string logic
        int position
    }

    signals {
        uuid id PK
        uuid portfolio_id FK
        string user_id
        strategyenum strategy
        string ticker
        signalactionenum action
        float confidence_score
        text rationale
        jsonb signal_metadata
        timestamptz expires_at
        bool is_executed
    }

    trades {
        uuid id PK
        uuid portfolio_id FK
        string user_id
        uuid signal_id FK
        string ticker
        tradesideenum side
        float qty
        float limit_price
        float filled_price
        string order_id
        tradestatusenum status
        strategyenum strategy
        jsonb regime_snapshot
        jsonb agent_outputs
        text thesis
    }

    fills {
        uuid id PK
        uuid trade_id FK
        string user_id
        string ticker
        tradesideenum side
        float qty
        float price
        float commission
        timestamptz filled_at
        string broker_fill_id
    }

    positions {
        uuid id PK
        uuid portfolio_id FK
        string user_id
        string ticker
        float qty
        float avg_cost
        float market_value
        float unrealized_pnl
        float realized_pnl
        strategyenum strategy
        float stop_price
        float target_price
    }

    agent_runs {
        uuid id PK
        uuid portfolio_id FK
        string user_id
        string agent_name
        string trigger_event
        agentstatusenum status
        timestamptz started_at
        timestamptz completed_at
        int input_tokens
        int output_tokens
        float cost_usd
        jsonb run_metadata
    }

    agent_decisions {
        uuid id PK
        uuid agent_run_id FK
        string user_id
        string step_name
        string decision_type
        jsonb step_input
        jsonb step_output
        text reasoning
        string model_used
    }

    audit_log {
        uuid id PK
        string user_id
        uuid portfolio_id
        string action
        string resource_type
        string resource_id
        jsonb old_values
        jsonb new_values
        string ip_address
        timestamptz created_at
    }

    user_profiles ||--o{ subscriptions : "has"
    user_profiles ||--o{ broker_connections : "has"
    user_profiles ||--o{ llm_provider_keys : "has"
    user_profiles ||--o{ portfolios : "owns"
    user_profiles ||--o{ watchlists : "owns"

    portfolios ||--|| risk_settings : "has"
    portfolios ||--o{ strategy_configs : "has"
    portfolios ||--o{ signals : "generates"
    portfolios ||--o{ trades : "contains"
    portfolios ||--o{ positions : "holds"
    portfolios ||--o{ agent_runs : "triggers"

    broker_connections ||--o{ portfolios : "linked to"

    watchlists ||--o{ smart_watchlist_rules : "defined by"

    signals ||--o{ trades : "triggers"

    trades ||--o{ fills : "filled by"

    agent_runs ||--o{ agent_decisions : "records"

Tables by Domain

Identity & Auth

Table	Description
`user_profiles`	App-specific user data; `user_id` mirrors Better Auth UUID
`subscriptions`	Dodo Payments subscription state per user
`broker_connections`	Encrypted Alpaca/IBKR OAuth tokens plus durable broker account id/number and paper/live account type
`llm_provider_keys`	BYOK Pro encrypted API keys per provider

Portfolio & Risk

Table	Description
`portfolios`	Top-level grouping; each execution-enabled portfolio maps to one broker account, and each broker account can be linked to only one portfolio
`risk_settings`	Hard limits: position size, drawdown, daily loss, concentration
`strategy_configs`	Active strategies + allocation weights per portfolio; MVP strategy passports live in `params.strategy_passport`

Watchlists

Table	Description
`watchlists`	Manual (user-curated) and Smart (rule-based) watchlists
`smart_watchlist_rules`	Individual filter rules (field, operator, value, AND/OR logic)

Trading

Table	Description
`signals`	Agent-generated buy/sell/hold signals per strategy + ticker
`trades`	Orders sent to broker; linked to signal + agent context snapshot
`fills`	Individual fill records per trade (partial fills supported)
`positions`	Current open positions with stop/target tracking

Agent Operations

Table	Description
`agent_runs`	One record per LangGraph agent execution; tracks tokens + cost
`agent_decisions`	Step-by-step decision trace within an agent run

Observability

Table	Description
`audit_log`	Append-only audit trail for user + system actions

Time-Series Tables (TM-35)

The following tables are added in migration 0002 (TM-35) as TimescaleDB hypertables:

Table	Partition key	Retention
`market_data`	`time`	5 years
`economic_indicators`	`time`	10 years
`fundamentals_snapshots`	`time`	10 years
`news_items`	`published_at`	2 years

news_items includes a pgvector embedding vector(1536) column for semantic search.