foxhunt/migrations/020_create_executions_table.sql

-- Migration 020: Create Executions Table for Trading Operations
-- Purpose: Create executions table required by trading service for load testing
-- Date: 2025-10-08
-- Agent: 118 (Wave 127)
-- Note: Database uses 'account_id' instead of 'user_id' as per existing schema patterns

-- ============================================================================
-- Create executions table
-- Tracks individual order executions/fills with pricing and timing
-- ============================================================================

CREATE TABLE IF NOT EXISTS executions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    order_id UUID NOT NULL,
    account_id VARCHAR(64) NOT NULL,
    symbol VARCHAR(32) NOT NULL,
    side order_side NOT NULL,
    quantity BIGINT NOT NULL CHECK (quantity > 0),
    price BIGINT NOT NULL CHECK (price > 0),
    timestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================================
-- Create indexes for performance
-- ============================================================================

-- Index for account queries (most common access pattern)
CREATE INDEX IF NOT EXISTS idx_executions_account_id
ON executions(account_id, timestamp DESC);

-- Index for order lookups
CREATE INDEX IF NOT EXISTS idx_executions_order_id
ON executions(order_id);

-- Index for symbol-based queries
CREATE INDEX IF NOT EXISTS idx_executions_symbol_timestamp
ON executions(symbol, timestamp DESC);

-- Index for time-based queries (reporting, analytics)
CREATE INDEX IF NOT EXISTS idx_executions_timestamp
ON executions(timestamp DESC);

-- ============================================================================
-- Add foreign key constraint to orders table (if exists)
-- ============================================================================

DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'orders') THEN
        ALTER TABLE executions
        ADD CONSTRAINT fk_executions_order_id
        FOREIGN KEY (order_id) REFERENCES orders(id)
        ON DELETE CASCADE;
    END IF;
END $$;

-- ============================================================================
-- Add comments for documentation
-- ============================================================================

COMMENT ON TABLE executions IS 'Order execution records - tracks fills/trades for load testing and trading operations';
COMMENT ON COLUMN executions.id IS 'Unique execution identifier';
COMMENT ON COLUMN executions.order_id IS 'Reference to parent order';
COMMENT ON COLUMN executions.account_id IS 'Account that owns this execution';
COMMENT ON COLUMN executions.symbol IS 'Trading symbol (e.g., AAPL, BTCUSD)';
COMMENT ON COLUMN executions.side IS 'Order side: buy, sell, short, cover';
COMMENT ON COLUMN executions.quantity IS 'Number of units executed (base units)';
COMMENT ON COLUMN executions.price IS 'Execution price (in base units, e.g., cents)';
COMMENT ON COLUMN executions.timestamp IS 'When the execution occurred';
COMMENT ON COLUMN executions.created_at IS 'When the record was created';

-- ============================================================================
-- Rollback Instructions
-- ============================================================================

-- To rollback this migration:
-- DROP TABLE IF EXISTS executions CASCADE;