28 lines
1.4 KiB
MySQL
28 lines
1.4 KiB
MySQL
|
-- Centaur Quant Architecture — Production Telemetry Schema (v1.0)
|
||
|
|
-- System Finalization deliverable: canonical DDL aligned with the SQLite
|
||
|
|
-- schema created by Python/database/telemetry.py (Track B).
|
||
|
|
-- Target: PostgreSQL. For SQLite (dev), use INTEGER PRIMARY KEY and TEXT
|
||
|
|
-- for the ISO-8601 UTC timestamps; NUMERIC maps to REAL.
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS trades (
|
||
|
|
ticket BIGINT PRIMARY KEY, -- MT5 position/order ticket
|
||
|
|
symbol TEXT NOT NULL,
|
||
|
|
direction TEXT NOT NULL, -- 'buy' | 'sell'
|
||
|
|
lot NUMERIC(12,4) NOT NULL,
|
||
|
|
entry_price NUMERIC(20,8) NOT NULL,
|
||
|
|
ai_score NUMERIC(6,2) NOT NULL DEFAULT 0, -- advisory score at open
|
||
|
|
open_time TIMESTAMPTZ, -- ISO-8601 UTC
|
||
|
|
close_time TIMESTAMPTZ, -- set on close
|
||
|
|
profit NUMERIC(20,2), -- net PnL in deposit currency
|
||
|
|
r_multiple NUMERIC(12,4) -- net PnL / initial risk amount
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_trades_symbol ON trades(symbol);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_trades_ai ON trades(ai_score);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_trades_outcome ON trades(r_multiple);
|
||
|
|
|
||
|
|
-- Calibration view: score -> outcome (the ML training signal)
|
||
|
|
CREATE OR REPLACE VIEW v_feedback AS
|
||
|
|
SELECT ticket, symbol, ai_score, profit, r_multiple, open_time, close_time
|
||
|
|
FROM trades
|
||
|
|
WHERE close_time IS NOT NULL;
|