diegobeyl/backtesting

1# PostgreSQL initialization script2# Creates initial database schema for backtesting app3 4-- Create extensions5CREATE EXTENSION IF NOT EXISTS "uuid-ossp";6 7-- Create tables8CREATE TABLE IF NOT EXISTS backtest_runs (9    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),10    symbol VARCHAR(20) NOT NULL,11    algorithm VARCHAR(50) NOT NULL,12    start_date DATE NOT NULL,13    end_date DATE NOT NULL,14    initial_capital DECIMAL(15, 2) NOT NULL,15    final_capital DECIMAL(15, 2) NOT NULL,16    total_return DECIMAL(10, 4),17    sharpe_ratio DECIMAL(10, 4),18    max_drawdown DECIMAL(10, 4),19    total_trades INTEGER,20    win_rate DECIMAL(5, 2),21    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,22    parameters JSONB,23    metadata JSONB24);25 26CREATE TABLE IF NOT EXISTS trades (27    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),28    backtest_run_id UUID REFERENCES backtest_runs(id) ON DELETE CASCADE,29    entry_date TIMESTAMP NOT NULL,30    exit_date TIMESTAMP,31    direction VARCHAR(10) NOT NULL,32    entry_price DECIMAL(15, 8) NOT NULL,33    exit_price DECIMAL(15, 8),34    position_size DECIMAL(15, 8) NOT NULL,35    pnl DECIMAL(15, 2),36    pnl_percent DECIMAL(10, 4),37    stop_loss DECIMAL(15, 8),38    take_profit DECIMAL(15, 8),39    reason VARCHAR(50),40    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP41);42 43CREATE TABLE IF NOT EXISTS metrics (44    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),45    backtest_run_id UUID REFERENCES backtest_runs(id) ON DELETE CASCADE,46    metric_name VARCHAR(100) NOT NULL,47    metric_value DECIMAL(20, 8),48    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP49);50 51-- Create indexes52CREATE INDEX idx_backtest_runs_symbol ON backtest_runs(symbol);53CREATE INDEX idx_backtest_runs_created_at ON backtest_runs(created_at);54CREATE INDEX idx_trades_backtest_run ON trades(backtest_run_id);55CREATE INDEX idx_trades_entry_date ON trades(entry_date);56CREATE INDEX idx_metrics_backtest_run ON metrics(backtest_run_id);57 58-- Grant permissions59GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO backtesting;60GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO backtesting;61