foxhunt/scripts/init-db.sql

-- Foxhunt HFT Trading System - Database Initialization
-- Creates required databases and basic schema for the 3 standalone services

-- Create additional databases
CREATE DATABASE foxhunt_backtesting;
CREATE DATABASE foxhunt_ml_training;

-- Create basic users with environment variable passwords
-- SECURITY: Passwords must be set via environment variables
CREATE USER trading_service WITH PASSWORD :'TRADING_SERVICE_PASSWORD';
CREATE USER backtesting_service WITH PASSWORD :'BACKTESTING_SERVICE_PASSWORD';
CREATE USER ml_service WITH PASSWORD :'ML_SERVICE_PASSWORD';

-- Grant permissions to main database
GRANT ALL PRIVILEGES ON DATABASE foxhunt TO trading_service;
GRANT ALL PRIVILEGES ON DATABASE foxhunt_backtesting TO backtesting_service;
GRANT ALL PRIVILEGES ON DATABASE foxhunt_ml_training TO ml_service;

-- Connect to main database and create basic schema
\c foxhunt;

-- Trading service tables
CREATE TABLE IF NOT EXISTS trades (
    id SERIAL PRIMARY KEY,
    symbol VARCHAR(20) NOT NULL,
    side VARCHAR(10) NOT NULL,
    quantity DECIMAL(20,8) NOT NULL,
    price DECIMAL(20,8) NOT NULL,
    timestamp TIMESTAMPTZ DEFAULT NOW(),
    order_id UUID,
    execution_id UUID
);

CREATE TABLE IF NOT EXISTS positions (
    id SERIAL PRIMARY KEY,
    symbol VARCHAR(20) NOT NULL UNIQUE,
    quantity DECIMAL(20,8) NOT NULL DEFAULT 0,
    average_price DECIMAL(20,8),
    unrealized_pnl DECIMAL(20,8) DEFAULT 0,
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    symbol VARCHAR(20) NOT NULL,
    side VARCHAR(10) NOT NULL,
    quantity DECIMAL(20,8) NOT NULL,
    price DECIMAL(20,8),
    order_type VARCHAR(20) NOT NULL,
    status VARCHAR(20) DEFAULT 'PENDING',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Configuration table for SQLite-style config but in PostgreSQL
CREATE TABLE IF NOT EXISTS config_settings (
    id SERIAL PRIMARY KEY,
    category VARCHAR(50) NOT NULL,
    key VARCHAR(100) NOT NULL,
    value TEXT NOT NULL,
    data_type VARCHAR(20) NOT NULL DEFAULT 'string',
    hot_reload BOOLEAN DEFAULT TRUE,
    description TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    modified_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(category, key)
);

-- Insert basic configuration
INSERT INTO config_settings (category, key, value, data_type, description) VALUES
('system', 'log_level', 'info', 'string', 'Global log level'),
('grpc', 'trading_service_port', '50051', 'number', 'Trading service gRPC port'),
('grpc', 'backtesting_service_port', '50052', 'number', 'Backtesting service gRPC port'),
('grpc', 'ml_training_service_port', '50053', 'number', 'ML training service gRPC port'),
('trading', 'max_position_size', '1000000', 'number', 'Maximum position size in USD'),
('risk', 'max_daily_loss', '50000', 'number', 'Maximum daily loss threshold'),
('ml', 'model_update_frequency', '300', 'number', 'Model update frequency in seconds')
ON CONFLICT (category, key) DO NOTHING;

-- Connect to backtesting database
\c foxhunt_backtesting;

CREATE TABLE IF NOT EXISTS backtest_runs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL,
    strategy_name VARCHAR(50) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    initial_capital DECIMAL(20,2) NOT NULL,
    final_value DECIMAL(20,2),
    total_return DECIMAL(10,6),
    sharpe_ratio DECIMAL(10,6),
    max_drawdown DECIMAL(10,6),
    status VARCHAR(20) DEFAULT 'RUNNING',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    completed_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS backtest_trades (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    backtest_run_id UUID REFERENCES backtest_runs(id) ON DELETE CASCADE,
    symbol VARCHAR(20) NOT NULL,
    side VARCHAR(10) NOT NULL,
    quantity DECIMAL(20,8) NOT NULL,
    price DECIMAL(20,8) NOT NULL,
    timestamp TIMESTAMPTZ NOT NULL,
    pnl DECIMAL(20,8)
);

-- Connect to ML training database
\c foxhunt_ml_training;

CREATE TABLE IF NOT EXISTS model_training_jobs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    model_name VARCHAR(50) NOT NULL,
    model_type VARCHAR(30) NOT NULL,
    status VARCHAR(20) DEFAULT 'PENDING',
    start_time TIMESTAMPTZ,
    end_time TIMESTAMPTZ,
    hyperparameters JSONB,
    metrics JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS model_versions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    model_name VARCHAR(50) NOT NULL,
    version VARCHAR(20) NOT NULL,
    file_path TEXT NOT NULL,
    performance_metrics JSONB,
    is_active BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(model_name, version)
);

-- Grant schema permissions
\c foxhunt;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO trading_service;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO trading_service;

\c foxhunt_backtesting;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO backtesting_service;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO backtesting_service;

\c foxhunt_ml_training;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO ml_service;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO ml_service;