Skip to content

Database Models

The system uses PostgreSQL with SQLAlchemy ORM. All models are defined in tradingbot/utils/db.py.

Bot Model

Stores bot configuration and portfolio state.

class Bot(Base):
    name: str                    # Primary key
    description: str             # Optional description
    portfolio: dict              # JSON: {"USD": 10000, "QQQ": 5.5, ...}
    created_at: datetime
    updated_at: datetime

Portfolio Format: {"USD": cash_amount, "SYMBOL": quantity, ...}

Trade Model

Logs all trade executions.

class Trade(Base):
    id: int                      # Auto-increment primary key
    bot_name: str                # Foreign key to Bot.name
    symbol: str                  # Trading symbol
    isBuy: bool                  # True for buy, False for sell
    quantity: float              # Number of shares/units
    price: float                 # Price per unit
    timestamp: datetime           # Execution time
    profit: float                 # Profit (for sells, nullable)

HistoricData Model

Caches market data for performance.

class HistoricData(Base):
    symbol: str                  # Primary key (part of composite)
    timestamp: datetime           # Primary key (part of composite)
    open: float
    high: float
    low: float
    close: float
    volume: float

RunLog Model

Tracks bot execution history.

class RunLog(Base):
    id: int                      # Auto-increment primary key
    bot_name: str                # Foreign key to Bot.name
    start_time: datetime         # When run started
    success: bool                 # Whether run succeeded
    result: str                  # Result message (nullable)

PortfolioWorth Model

Historical portfolio valuations.

class PortfolioWorth(Base):
    bot_name: str                # Primary key (part of composite)
    date: datetime               # Primary key (part of composite)
    portfolio_worth: float        # Total value in USD
    holdings: dict                # JSON snapshot of holdings
    created_at: datetime

StockNews Model

News articles per symbol from yfinance (loaded daily with portfolio worth).

class StockNews(Base):
    id: int                      # Auto-increment primary key
    symbol: str                  # Trading symbol (indexed)
    title: str                   # Article title
    link: str                    # Article URL
    publisher: str               # Publisher name (nullable)
    publisher_url: str           # Publisher URL (nullable)
    published_at: datetime       # When the article was published (UTC)
    related_tickers: list        # JSON array of related tickers (nullable)
    created_at: datetime

Unique constraint: (symbol, link) so the same article is not stored twice for a symbol. Index on (symbol, published_at) for efficient queries.

StockEarnings Model

Earnings dates and results per symbol from yfinance (loaded daily with portfolio worth).

class StockEarnings(Base):
    id: int                      # Auto-increment primary key
    symbol: str                  # Trading symbol (indexed)
    report_date: datetime        # Earnings report date
    eps_estimate: float          # Estimated EPS (nullable)
    reported_eps: float          # Reported EPS (nullable)
    surprise_pct: float          # Surprise percentage (nullable)
    fiscal_period: str           # Fiscal period if available (nullable)
    created_at: datetime

Unique constraint: (symbol, report_date) to avoid duplicate earnings rows. Index on symbol.

StockInsiderTrade Model

Insider transactions per symbol from yfinance (loaded daily with portfolio worth).

class StockInsiderTrade(Base):
    id: int                      # Auto-increment primary key
    symbol: str                  # Trading symbol (indexed)
    transaction_date: datetime   # Date of the transaction
    insider_name: str            # Name of the insider (nullable)
    transaction_type: str        # Type e.g. Purchase, Sale (nullable)
    shares: float                # Number of shares (nullable)
    value: float                 # Transaction value if available (nullable)
    created_at: datetime

Unique constraint: (symbol, transaction_date, insider_name, transaction_type, shares). Index on (symbol, transaction_date).

Session Management

Always use the context manager:

from tradingbot.utils.db import get_db_session

with get_db_session() as session:
    bot = session.query(Bot).filter_by(name="MyBot").first()
    # Context manager commits automatically

The context manager handles: - Automatic commit on success - Automatic rollback on exceptions - Connection retry logic (3 attempts with exponential backoff) - Proper session cleanup

Next Steps