foxhunt/migrations/015_auth_schema.sql

-- Authentication and Security Schema for Foxhunt Trading System
-- Implements comprehensive security for financial trading platform
-- Compliant with SOX, FINRA, and financial industry standards

-- Users table for authentication
CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(255) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    salt VARCHAR(255) NOT NULL,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    phone VARCHAR(50),
    department VARCHAR(100),
    job_title VARCHAR(100),
    manager_id UUID REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_login TIMESTAMP WITH TIME ZONE,
    failed_login_attempts INTEGER DEFAULT 0,
    account_locked_until TIMESTAMP WITH TIME ZONE,
    password_changed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    must_change_password BOOLEAN DEFAULT FALSE,
    two_factor_enabled BOOLEAN DEFAULT FALSE,
    two_factor_secret VARCHAR(255),
    active BOOLEAN DEFAULT TRUE,
    deleted_at TIMESTAMP WITH TIME ZONE,

    -- Audit fields
    created_by UUID REFERENCES users(id),
    updated_by UUID REFERENCES users(id)
);

-- Roles table for RBAC
CREATE TABLE IF NOT EXISTS roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    permissions TEXT[], -- JSON array of permissions
    parent_role_id UUID REFERENCES roles(id),
    resource_constraints JSONB, -- Resource-based constraints
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    active BOOLEAN DEFAULT TRUE,

    -- Hierarchy depth for performance
    hierarchy_level INTEGER DEFAULT 0,

    -- Audit fields
    created_by UUID REFERENCES users(id),
    updated_by UUID REFERENCES users(id)
);

-- User role assignments
CREATE TABLE IF NOT EXISTS user_roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    granted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE,
    granted_by UUID NOT NULL REFERENCES users(id),
    resource_constraints JSONB, -- Additional constraints for this assignment
    active BOOLEAN DEFAULT TRUE,

    UNIQUE(user_id, role_id)
);

-- Sessions table for session management
CREATE TABLE IF NOT EXISTS sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    token_hash VARCHAR(255) UNIQUE NOT NULL, -- Hashed session token
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_activity TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    client_ip INET,
    user_agent TEXT,
    device_fingerprint VARCHAR(255),
    active BOOLEAN DEFAULT TRUE,

    -- Session metadata
    login_method VARCHAR(50), -- password, api_key, certificate
    session_type VARCHAR(50) DEFAULT 'web' -- web, api, mobile
);

-- API keys table
CREATE TABLE IF NOT EXISTS api_keys (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    key_hash VARCHAR(255) UNIQUE NOT NULL, -- Hashed API key
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    permissions TEXT[], -- JSON array of permissions
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_used TIMESTAMP WITH TIME ZONE,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    usage_count BIGINT DEFAULT 0,
    rate_limit_override INTEGER, -- Custom rate limit for this key
    ip_whitelist INET[], -- Allowed IP addresses
    active BOOLEAN DEFAULT TRUE,
    revoked_at TIMESTAMP WITH TIME ZONE,
    revoked_by UUID REFERENCES users(id),
    revoke_reason TEXT,

    -- Key metadata
    key_type VARCHAR(50) DEFAULT 'standard', -- standard, trading, readonly
    scopes TEXT[] -- API scopes this key can access
);

-- Audit log table for compliance
CREATE TABLE IF NOT EXISTS audit_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    event_type VARCHAR(100) NOT NULL,
    severity VARCHAR(20) NOT NULL CHECK (severity IN ('info', 'warning', 'error', 'critical')),
    user_id UUID REFERENCES users(id),
    session_id UUID REFERENCES sessions(id),
    api_key_id UUID REFERENCES api_keys(id),
    client_ip INET,
    user_agent TEXT,
    resource VARCHAR(255),
    action VARCHAR(100) NOT NULL,
    result VARCHAR(100) NOT NULL,
    details JSONB,
    correlation_id UUID,
    request_id UUID,
    service_name VARCHAR(100),
    service_version VARCHAR(50),

    -- Compliance fields
    compliance_category VARCHAR(100), -- SOX, FINRA, MiFID, etc.
    retention_until TIMESTAMP WITH TIME ZONE,

    -- Tamper detection
    checksum VARCHAR(255),
    previous_log_hash VARCHAR(255)
);

-- Rate limiting buckets
CREATE TABLE IF NOT EXISTS rate_limit_buckets (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    client_identifier VARCHAR(255) NOT NULL,
    bucket_type VARCHAR(50) NOT NULL, -- auth, api, trading, market_data
    requests JSONB NOT NULL DEFAULT '[]', -- Array of request timestamps
    total_requests BIGINT DEFAULT 0,
    last_request TIMESTAMP WITH TIME ZONE,
    violations INTEGER DEFAULT 0,
    blocked_until TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    UNIQUE(client_identifier, bucket_type)
);

-- TLS certificates table
CREATE TABLE IF NOT EXISTS certificates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    certificate_type VARCHAR(50) NOT NULL, -- server, client, ca
    subject VARCHAR(500) NOT NULL,
    issuer VARCHAR(500) NOT NULL,
    serial_number VARCHAR(100) NOT NULL,
    fingerprint VARCHAR(255) UNIQUE NOT NULL,
    not_before TIMESTAMP WITH TIME ZONE NOT NULL,
    not_after TIMESTAMP WITH TIME ZONE NOT NULL,
    key_algorithm VARCHAR(50) NOT NULL,
    key_size INTEGER NOT NULL,
    signature_algorithm VARCHAR(100) NOT NULL,
    san_dns_names TEXT[],
    san_ip_addresses INET[],
    certificate_pem TEXT NOT NULL,
    private_key_encrypted TEXT, -- Encrypted private key (if stored)
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    active BOOLEAN DEFAULT TRUE,

    -- Certificate chain relationships
    parent_certificate_id UUID REFERENCES certificates(id),
    root_ca_id UUID REFERENCES certificates(id)
);

-- Permission cache table for performance
CREATE TABLE IF NOT EXISTS permission_cache (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    permissions JSONB NOT NULL,
    cached_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    cache_version INTEGER DEFAULT 1,

    UNIQUE(user_id, cache_version)
);

-- Compliance violations table
CREATE TABLE IF NOT EXISTS compliance_violations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    violation_type VARCHAR(100) NOT NULL,
    severity VARCHAR(20) NOT NULL CHECK (severity IN ('low', 'medium', 'high', 'critical')),
    user_id UUID REFERENCES users(id),
    description TEXT NOT NULL,
    detected_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    resolved_at TIMESTAMP WITH TIME ZONE,
    resolved_by UUID REFERENCES users(id),
    resolution_notes TEXT,
    compliance_framework VARCHAR(100), -- SOX, FINRA, MiFID II, etc.
    rule_violated VARCHAR(255),
    evidence JSONB,
    status VARCHAR(50) DEFAULT 'open' CHECK (status IN ('open', 'investigating', 'resolved', 'false_positive')),

    -- Regulatory reporting
    reported_to_regulator BOOLEAN DEFAULT FALSE,
    regulator_reference VARCHAR(255),
    reporting_deadline TIMESTAMP WITH TIME ZONE
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_active ON users(active) WHERE active = TRUE;
CREATE INDEX IF NOT EXISTS idx_users_last_login ON users(last_login);

CREATE INDEX IF NOT EXISTS idx_roles_name ON roles(name);
CREATE INDEX IF NOT EXISTS idx_roles_active ON roles(active) WHERE active = TRUE;
CREATE INDEX IF NOT EXISTS idx_roles_parent ON roles(parent_role_id);

CREATE INDEX IF NOT EXISTS idx_user_roles_user_id ON user_roles(user_id);
CREATE INDEX IF NOT EXISTS idx_user_roles_role_id ON user_roles(role_id);
CREATE INDEX IF NOT EXISTS idx_user_roles_active ON user_roles(active) WHERE active = TRUE;
CREATE INDEX IF NOT EXISTS idx_user_roles_expires ON user_roles(expires_at);

CREATE INDEX IF NOT EXISTS idx_sessions_token_hash ON sessions(token_hash);
CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_sessions_active ON sessions(active) WHERE active = TRUE;
CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at);
CREATE INDEX IF NOT EXISTS idx_sessions_last_activity ON sessions(last_activity);

CREATE INDEX IF NOT EXISTS idx_api_keys_key_hash ON api_keys(key_hash);
CREATE INDEX IF NOT EXISTS idx_api_keys_user_id ON api_keys(user_id);
CREATE INDEX IF NOT EXISTS idx_api_keys_active ON api_keys(active) WHERE active = TRUE;
CREATE INDEX IF NOT EXISTS idx_api_keys_expires ON api_keys(expires_at);

CREATE INDEX IF NOT EXISTS idx_audit_logs_timestamp ON audit_logs(timestamp);
CREATE INDEX IF NOT EXISTS idx_audit_logs_user_id ON audit_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_audit_logs_event_type ON audit_logs(event_type);
CREATE INDEX IF NOT EXISTS idx_audit_logs_severity ON audit_logs(severity);
CREATE INDEX IF NOT EXISTS idx_audit_logs_correlation_id ON audit_logs(correlation_id);

CREATE INDEX IF NOT EXISTS idx_rate_limit_buckets_client ON rate_limit_buckets(client_identifier, bucket_type);
CREATE INDEX IF NOT EXISTS idx_rate_limit_buckets_updated ON rate_limit_buckets(updated_at);

CREATE INDEX IF NOT EXISTS idx_certificates_fingerprint ON certificates(fingerprint);
CREATE INDEX IF NOT EXISTS idx_certificates_not_after ON certificates(not_after);
CREATE INDEX IF NOT EXISTS idx_certificates_active ON certificates(active) WHERE active = TRUE;

CREATE INDEX IF NOT EXISTS idx_permission_cache_user_id ON permission_cache(user_id);
CREATE INDEX IF NOT EXISTS idx_permission_cache_expires ON permission_cache(expires_at);

CREATE INDEX IF NOT EXISTS idx_compliance_violations_user_id ON compliance_violations(user_id);
CREATE INDEX IF NOT EXISTS idx_compliance_violations_status ON compliance_violations(status);
CREATE INDEX IF NOT EXISTS idx_compliance_violations_detected ON compliance_violations(detected_at);
CREATE INDEX IF NOT EXISTS idx_compliance_violations_framework ON compliance_violations(compliance_framework);

-- Triggers for updated_at timestamps
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_roles_updated_at BEFORE UPDATE ON roles
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_certificates_updated_at BEFORE UPDATE ON certificates
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_rate_limit_buckets_updated_at BEFORE UPDATE ON rate_limit_buckets
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Function to clean up expired sessions
CREATE OR REPLACE FUNCTION cleanup_expired_sessions()
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM sessions
    WHERE expires_at < NOW() - INTERVAL '7 days';

    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

-- Function to clean up old audit logs (based on retention policy)
CREATE OR REPLACE FUNCTION cleanup_audit_logs(retention_days INTEGER DEFAULT 2555)
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM audit_logs
    WHERE timestamp < NOW() - INTERVAL '1 day' * retention_days;

    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

-- Function to check password complexity
CREATE OR REPLACE FUNCTION check_password_complexity(password_hash TEXT)
RETURNS BOOLEAN AS $$
BEGIN
    -- In production, implement proper password complexity checking
    -- This is a placeholder that assumes passwords are already validated
    RETURN LENGTH(password_hash) >= 60; -- Assuming bcrypt hash length
END;
$$ LANGUAGE plpgsql;

-- Function to log security events
CREATE OR REPLACE FUNCTION log_security_event(
    event_type VARCHAR(100),
    user_id UUID,
    session_id UUID,
    client_ip INET,
    details JSONB
)
RETURNS UUID AS $$
DECLARE
    log_id UUID;
BEGIN
    INSERT INTO audit_logs (
        event_type,
        severity,
        user_id,
        session_id,
        client_ip,
        action,
        result,
        details,
        service_name
    ) VALUES (
        event_type,
        'info',
        user_id,
        session_id,
        client_ip,
        'security_event',
        'logged',
        details,
        'tli'
    ) RETURNING id INTO log_id;

    RETURN log_id;
END;
$$ LANGUAGE plpgsql;

-- Insert default roles
INSERT INTO roles (id, name, description, permissions, hierarchy_level) VALUES
    ('00000000-0000-0000-0000-000000000001', 'system_admin', 'System Administrator - Full Access',
     ARRAY['system:admin', 'system:config', 'system:user_management', 'system:role_management'], 0),
    ('00000000-0000-0000-0000-000000000002', 'trader', 'Senior Trader - Full Trading Access',
     ARRAY['trade:execute', 'trade:view', 'trade:cancel', 'trade:modify', 'order:place', 'order:cancel', 'order:modify', 'order:view', 'position:view', 'position:close', 'position:modify', 'risk:view', 'market_data:view', 'market_data:subscribe', 'portfolio:view', 'report:view', 'analytics:view', 'api:access', 'ml:signal_view'], 1),
    ('00000000-0000-0000-0000-000000000003', 'junior_trader', 'Junior Trader - Limited Trading Access',
     ARRAY['order:place', 'order:view', 'position:view', 'trade:view', 'risk:view', 'market_data:view', 'portfolio:view', 'report:view', 'ml:signal_view'], 2),
    ('00000000-0000-0000-0000-000000000004', 'risk_manager', 'Risk Manager - Risk Oversight',
     ARRAY['risk:view', 'risk:config', 'risk:override', 'risk:limits', 'risk:drawdown_monitor', 'position:view', 'position:limit', 'trade:view', 'order:view', 'portfolio:view', 'report:view', 'report:generate', 'analytics:view', 'compliance:view', 'audit:view'], 1),
    ('00000000-0000-0000-0000-000000000005', 'viewer', 'Viewer - Read-only Access',
     ARRAY['trade:view', 'order:view', 'position:view', 'risk:view', 'market_data:view', 'portfolio:view', 'report:view', 'analytics:view', 'ml:signal_view'], 3),
    ('00000000-0000-0000-0000-000000000006', 'api_user', 'API User - Programmatic Access',
     ARRAY['api:access', 'market_data:view', 'market_data:subscribe', 'order:place', 'order:view', 'order:cancel', 'position:view', 'trade:view', 'ml:signal_view'], 2)
ON CONFLICT (id) DO NOTHING;

-- Insert default admin user (password should be changed on first login)
-- Default password hash is for 'DefaultAdmin123!' - MUST be changed in production
INSERT INTO users (
    id,
    username,
    email,
    password_hash,
    salt,
    first_name,
    last_name,
    must_change_password
) VALUES (
    '00000000-0000-0000-0000-000000000001',
    'admin',
    'admin@foxhunt.local',
    '$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/lewZJYHCB7vMNZJdK', -- DefaultAdmin123!
    'default_salt_change_in_production',
    'System',
    'Administrator',
    TRUE
) ON CONFLICT (id) DO NOTHING;

-- Assign admin role to default admin user
INSERT INTO user_roles (user_id, role_id, granted_by) VALUES
    ('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000001')
ON CONFLICT (user_id, role_id) DO NOTHING;

-- Create view for active user permissions
CREATE OR REPLACE VIEW user_permissions AS
SELECT DISTINCT
    u.id as user_id,
    u.username,
    unnest(r.permissions) as permission,
    ur.expires_at as permission_expires_at
FROM users u
JOIN user_roles ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id
WHERE u.active = TRUE
  AND ur.active = TRUE
  AND r.active = TRUE
  AND (ur.expires_at IS NULL OR ur.expires_at > NOW());

-- Create view for session summary
CREATE OR REPLACE VIEW active_sessions AS
SELECT
    s.id,
    s.user_id,
    u.username,
    s.created_at,
    s.last_activity,
    s.expires_at,
    s.client_ip,
    s.session_type,
    EXTRACT(EPOCH FROM (s.expires_at - NOW())) as seconds_until_expiry
FROM sessions s
JOIN users u ON s.user_id = u.id
WHERE s.active = TRUE
  AND s.expires_at > NOW();

-- Grant permissions to application role (adjust as needed)
-- GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO foxhunt_app;
-- GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO foxhunt_app;

-- Add comments for documentation
COMMENT ON TABLE users IS 'User accounts for authentication and authorization';
COMMENT ON TABLE roles IS 'Role definitions for RBAC system';
COMMENT ON TABLE user_roles IS 'User to role assignments with optional expiration';
COMMENT ON TABLE sessions IS 'Active user sessions for session management';
COMMENT ON TABLE api_keys IS 'API keys for programmatic access';
COMMENT ON TABLE audit_logs IS 'Comprehensive audit trail for compliance';
COMMENT ON TABLE rate_limit_buckets IS 'Rate limiting state tracking';
COMMENT ON TABLE certificates IS 'TLS certificate management';
COMMENT ON TABLE permission_cache IS 'Cached user permissions for performance';
COMMENT ON TABLE compliance_violations IS 'Compliance violations tracking and reporting';

COMMENT ON COLUMN users.password_hash IS 'Bcrypt hash of user password';
COMMENT ON COLUMN users.salt IS 'Salt used for password hashing';
COMMENT ON COLUMN users.failed_login_attempts IS 'Count of consecutive failed login attempts';
COMMENT ON COLUMN users.account_locked_until IS 'Account lockout expiration timestamp';
COMMENT ON COLUMN users.two_factor_secret IS 'TOTP secret for 2FA (encrypted)';

COMMENT ON COLUMN audit_logs.checksum IS 'Tamper detection checksum for audit integrity';
COMMENT ON COLUMN audit_logs.previous_log_hash IS 'Hash of previous log entry for chain verification';
COMMENT ON COLUMN audit_logs.retention_until IS 'Data retention deadline for compliance';

COMMENT ON COLUMN api_keys.key_hash IS 'SHA-256 hash of the API key for secure storage';
COMMENT ON COLUMN api_keys.scopes IS 'API scopes this key can access';
COMMENT ON COLUMN api_keys.ip_whitelist IS 'Allowed source IP addresses for this key';

COMMENT ON COLUMN certificates.certificate_pem IS 'PEM-encoded certificate';
COMMENT ON COLUMN certificates.private_key_encrypted IS 'Encrypted private key (if stored)';
COMMENT ON COLUMN certificates.san_dns_names IS 'Subject Alternative Names - DNS names';
COMMENT ON COLUMN certificates.san_ip_addresses IS 'Subject Alternative Names - IP addresses';