-- ===================================================================== -- TradeForge — SQLite schema (v0.1 / MVP) -- Options research & portfolio management platform -- Owner: Jan Erik | Stack: PHP + SQLite + JS | Broker: Charles Schwab -- -- Conventions: -- * All monetary values are stored as INTEGER cents (avoids float -- rounding errors in P/L math). Divide by 100 for display. -- * All timestamps are TEXT in ISO-8601 ("YYYY-MM-DD HH:MM:SS", UTC). -- This format sorts lexically and maps cleanly onto MySQL -- DATETIME if the app is ever migrated to a multi-user MySQL setup. -- * Every account/user-scoped table carries user_id and/or -- broker_account_id from day one, even though the app is single-user -- today (user_id will always be 1). This is what makes a future -- SQLite -> MySQL multi-user migration a data-copy exercise instead -- of a schema rewrite. -- * Tables marked [PHASE 2] support features that are lower priority -- for the MVP (Backtesting Engine, saved Screener/Trade Lab -- presets) — safe to create now, fine to leave empty until built. -- ===================================================================== PRAGMA foreign_keys = ON; -- --------------------------------------------------------------------- -- Identity & broker connections -- --------------------------------------------------------------------- CREATE TABLE users ( id INTEGER PRIMARY KEY, username TEXT NOT NULL UNIQUE, email TEXT, password_hash TEXT, -- nullable: not needed until multi-user created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now')) ); CREATE TABLE broker_accounts ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), broker TEXT NOT NULL DEFAULT 'schwab', account_number TEXT NOT NULL, nickname TEXT, account_type TEXT, -- margin | cash | ira is_active INTEGER NOT NULL DEFAULT 1, created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now')), UNIQUE (user_id, broker, account_number) ); -- Access/refresh tokens. Encrypt access_token/refresh_token at the -- application layer before writing (e.g. libsodium) — the DB file -- itself is not a secrets vault. CREATE TABLE broker_oauth_tokens ( id INTEGER PRIMARY KEY, broker_account_id INTEGER NOT NULL REFERENCES broker_accounts(id), access_token TEXT NOT NULL, refresh_token TEXT NOT NULL, expires_at TEXT NOT NULL, updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now')) ); -- --------------------------------------------------------------------- -- Reference / market data cache -- --------------------------------------------------------------------- CREATE TABLE underlyings ( id INTEGER PRIMARY KEY, symbol TEXT NOT NULL UNIQUE, name TEXT, sector TEXT, industry TEXT, asset_type TEXT NOT NULL DEFAULT 'equity', -- equity | etf | index updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now')) ); -- Polled quote/IV snapshots (Schwab REST, not push streaming). Feeds -- IV Rank/Percentile history and the Market Intelligence module. CREATE TABLE quote_snapshots ( id INTEGER PRIMARY KEY, underlying_id INTEGER NOT NULL REFERENCES underlyings(id), captured_at TEXT NOT NULL, last_price INTEGER, -- cents bid INTEGER, ask INTEGER, volume INTEGER, iv_rank REAL, iv_percentile REAL, hv_30 REAL, beta REAL ); CREATE INDEX idx_quote_snapshots_underlying_time ON quote_snapshots(underlying_id, captured_at); CREATE TABLE earnings_calendar ( id INTEGER PRIMARY KEY, underlying_id INTEGER NOT NULL REFERENCES underlyings(id), earnings_date TEXT NOT NULL, time_of_day TEXT, -- BMO | AMC | unknown confirmed INTEGER NOT NULL DEFAULT 0, source TEXT ); CREATE INDEX idx_earnings_underlying_date ON earnings_calendar(underlying_id, earnings_date); CREATE TABLE dividend_calendar ( id INTEGER PRIMARY KEY, underlying_id INTEGER NOT NULL REFERENCES underlyings(id), ex_date TEXT NOT NULL, amount INTEGER, -- cents/share pay_date TEXT ); CREATE INDEX idx_dividends_underlying_date ON dividend_calendar(underlying_id, ex_date); -- --------------------------------------------------------------------- -- Positions & raw trade fills (synced from Schwab) -- --------------------------------------------------------------------- CREATE TABLE positions ( id INTEGER PRIMARY KEY, broker_account_id INTEGER NOT NULL REFERENCES broker_accounts(id), underlying_id INTEGER NOT NULL REFERENCES underlyings(id), instrument_type TEXT NOT NULL CHECK (instrument_type IN ('equity','option')), option_type TEXT CHECK (option_type IN ('call','put') OR option_type IS NULL), strike INTEGER, -- cents, NULL for equity expiration TEXT, -- date, NULL for equity quantity REAL NOT NULL, -- signed: + long, - short avg_cost INTEGER, -- cents per share/contract current_price INTEGER, -- cents, cached from last sync delta REAL, gamma REAL, theta REAL, vega REAL, rho REAL, broker_position_id TEXT, -- external id, for reconciliation updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now')) ); CREATE INDEX idx_positions_account ON positions(broker_account_id); CREATE INDEX idx_positions_underlying ON positions(underlying_id); CREATE TABLE trades ( id INTEGER PRIMARY KEY, broker_account_id INTEGER NOT NULL REFERENCES broker_accounts(id), underlying_id INTEGER NOT NULL REFERENCES underlyings(id), instrument_type TEXT NOT NULL CHECK (instrument_type IN ('equity','option')), option_type TEXT CHECK (option_type IN ('call','put') OR option_type IS NULL), strike INTEGER, expiration TEXT, action TEXT NOT NULL CHECK (action IN ( 'buy_to_open','sell_to_open','buy_to_close','sell_to_close', 'assignment','exercise','expiration')), quantity INTEGER NOT NULL, price INTEGER NOT NULL DEFAULT 0, -- cents per share/contract fees INTEGER NOT NULL DEFAULT 0, commission INTEGER NOT NULL DEFAULT 0, executed_at TEXT NOT NULL, broker_order_id TEXT, broker_execution_id TEXT UNIQUE, -- idempotency key for re-imports created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now')) ); CREATE INDEX idx_trades_account_time ON trades(broker_account_id, executed_at); CREATE INDEX idx_trades_underlying ON trades(underlying_id); CREATE TABLE import_log ( id INTEGER PRIMARY KEY, broker_account_id INTEGER NOT NULL REFERENCES broker_accounts(id), run_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now')), status TEXT NOT NULL CHECK (status IN ('success','partial','failed')), trades_imported INTEGER NOT NULL DEFAULT 0, error_message TEXT ); -- --------------------------------------------------------------------- -- Strategy Classification Engine + Trade Journal -- --------------------------------------------------------------------- CREATE TABLE strategy_types ( id INTEGER PRIMARY KEY, code TEXT NOT NULL UNIQUE, -- CSP, CC, WHEEL, PMCC, IRON_CONDOR, IRON_FLY, -- BUTTERFLY, VERTICAL, STRANGLE, STRADDLE, -- CALENDAR, DIAGONAL, COVERED_STRANGLE, -- JADE_LIZARD, CUSTOM ... label TEXT NOT NULL, leg_count_min INTEGER, leg_count_max INTEGER ); -- One row per identified "trade" in the trade-journal sense: a group -- of one or more legs that together form a strategy occurrence. CREATE TABLE strategy_instances ( id INTEGER PRIMARY KEY, broker_account_id INTEGER NOT NULL REFERENCES broker_accounts(id), underlying_id INTEGER NOT NULL REFERENCES underlyings(id), strategy_type_id INTEGER NOT NULL REFERENCES strategy_types(id), status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open','closed','assigned','expired','rolled')), opened_at TEXT NOT NULL, closed_at TEXT, capital_at_risk INTEGER, -- cents max_profit INTEGER, -- cents, NULL if undefined (naked call, etc.) max_loss INTEGER, -- cents, NULL if undefined realized_pl INTEGER, -- cents, set on close roc REAL, -- realized_pl / capital_at_risk annualized_roc REAL, dte_at_open INTEGER, notes TEXT, tags TEXT, -- comma-separated classified_by TEXT NOT NULL DEFAULT 'auto' CHECK (classified_by IN ('auto','manual')), created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now')), updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now')) ); CREATE INDEX idx_strategy_instances_account ON strategy_instances(broker_account_id, status); CREATE INDEX idx_strategy_instances_underlying ON strategy_instances(underlying_id); CREATE INDEX idx_strategy_instances_type ON strategy_instances(strategy_type_id); -- Join table: which raw trade fills make up which strategy instance. CREATE TABLE strategy_instance_legs ( id INTEGER PRIMARY KEY, strategy_instance_id INTEGER NOT NULL REFERENCES strategy_instances(id), trade_id INTEGER NOT NULL REFERENCES trades(id), leg_role TEXT, -- e.g. 'short_put', 'long_call' UNIQUE (strategy_instance_id, trade_id) ); CREATE INDEX idx_instance_legs_trade ON strategy_instance_legs(trade_id); -- --------------------------------------------------------------------- -- Wheel Tracker (the flagship niche module) -- --------------------------------------------------------------------- CREATE TABLE wheel_cycles ( id INTEGER PRIMARY KEY, broker_account_id INTEGER NOT NULL REFERENCES broker_accounts(id), underlying_id INTEGER NOT NULL REFERENCES underlyings(id), cycle_number INTEGER NOT NULL, -- increments per ticker status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','completed')), started_at TEXT NOT NULL, completed_at TEXT, shares_assigned INTEGER NOT NULL DEFAULT 0, assignment_price INTEGER, -- cents/share adjusted_basis INTEGER, -- cents/share, after premium offsets total_premium_collected INTEGER NOT NULL DEFAULT 0, -- cents total_pl INTEGER, -- cents rotations INTEGER NOT NULL DEFAULT 0, -- # of CSP/CC rounds in this cycle UNIQUE (underlying_id, broker_account_id, cycle_number) ); CREATE INDEX idx_wheel_cycles_underlying ON wheel_cycles(underlying_id, status); CREATE TABLE wheel_cycle_strategy_instances ( id INTEGER PRIMARY KEY, wheel_cycle_id INTEGER NOT NULL REFERENCES wheel_cycles(id), strategy_instance_id INTEGER NOT NULL REFERENCES strategy_instances(id), UNIQUE (wheel_cycle_id, strategy_instance_id) ); -- --------------------------------------------------------------------- -- Portfolio Hub (account-level snapshots for equity curve / risk history) -- --------------------------------------------------------------------- CREATE TABLE portfolio_snapshots ( id INTEGER PRIMARY KEY, broker_account_id INTEGER NOT NULL REFERENCES broker_accounts(id), snapshot_at TEXT NOT NULL, net_liq INTEGER, -- cents cash INTEGER, buying_power INTEGER, margin_used INTEGER, portfolio_delta REAL, portfolio_gamma REAL, portfolio_theta REAL, portfolio_vega REAL, beta_weighted_delta REAL, UNIQUE (broker_account_id, snapshot_at) ); CREATE INDEX idx_portfolio_snapshots_account_time ON portfolio_snapshots(broker_account_id, snapshot_at); -- --------------------------------------------------------------------- -- Watchlists -- --------------------------------------------------------------------- CREATE TABLE watchlists ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), name TEXT NOT NULL, created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now')), UNIQUE (user_id, name) ); CREATE TABLE watchlist_items ( id INTEGER PRIMARY KEY, watchlist_id INTEGER NOT NULL REFERENCES watchlists(id), underlying_id INTEGER NOT NULL REFERENCES underlyings(id), added_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now')), notes TEXT, UNIQUE (watchlist_id, underlying_id) ); -- --------------------------------------------------------------------- -- Opportunity Screener & Trade Lab presets [PHASE 2] -- --------------------------------------------------------------------- CREATE TABLE screener_filters ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), name TEXT NOT NULL, strategy_type_id INTEGER REFERENCES strategy_types(id), filter_json TEXT NOT NULL, -- {delta_min, delta_max, dte_min, dte_max, iv_rank_min, roc_min, ...} created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now')) ); CREATE TABLE trade_lab_scenarios ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), underlying_id INTEGER NOT NULL REFERENCES underlyings(id), name TEXT, legs_json TEXT NOT NULL, -- [{type, strike, expiration, side, quantity}, ...] iv_shift REAL NOT NULL DEFAULT 0, -- simulation parameter, e.g. +0.10 date_shift_days INTEGER NOT NULL DEFAULT 0, -- simulation parameter, e.g. +21 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now')) ); -- --------------------------------------------------------------------- -- Backtesting Engine [PHASE 2] -- --------------------------------------------------------------------- CREATE TABLE backtest_runs ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), underlying_id INTEGER REFERENCES underlyings(id), strategy_type_id INTEGER REFERENCES strategy_types(id), params_json TEXT NOT NULL, -- {delta_target, dte_target, date_from, date_to, ...} status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','running','completed','failed')), started_at TEXT, completed_at TEXT, results_json TEXT -- {cagr, max_drawdown, win_rate, assignments, roc, ...} ); -- --------------------------------------------------------------------- -- Seed data: strategy type lookup table -- --------------------------------------------------------------------- INSERT INTO strategy_types (code, label, leg_count_min, leg_count_max) VALUES ('CSP', 'Cash-Secured Put', 1, 1), ('CC', 'Covered Call', 1, 1), ('WHEEL', 'Wheel', 1, NULL), ('PMCC', 'Poor Man''s Covered Call', 2, 2), ('COVERED_STRANGLE', 'Covered Strangle', 2, 2), ('VERTICAL', 'Vertical Spread', 2, 2), ('IRON_CONDOR', 'Iron Condor', 4, 4), ('IRON_FLY', 'Iron Butterfly', 4, 4), ('BUTTERFLY', 'Butterfly', 3, 4), ('CALENDAR', 'Calendar Spread', 2, 2), ('DIAGONAL', 'Diagonal Spread', 2, 2), ('STRANGLE', 'Strangle', 2, 2), ('STRADDLE', 'Straddle', 2, 2), ('JADE_LIZARD', 'Jade Lizard', 3, 3), ('CUSTOM', 'Custom / Unclassified', 1, NULL); INSERT INTO users (id, username) VALUES (1, 'jan_erik');