foxhunt/migrations/017_mfa_tables.sql

-- MFA (Multi-Factor Authentication) Tables
-- Created in Wave 112 to support TOTP-based MFA functionality
-- Addresses CVSS 9.1 vulnerability by enforcing MFA for all users

-- MFA configuration per user
CREATE TABLE IF NOT EXISTS mfa_config (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID UNIQUE NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    totp_secret_encrypted BYTEA NOT NULL,
    totp_algorithm VARCHAR(10) DEFAULT 'SHA1' NOT NULL,
    totp_digits INTEGER DEFAULT 6 NOT NULL,
    totp_period INTEGER DEFAULT 30 NOT NULL,
    is_enabled BOOLEAN DEFAULT FALSE NOT NULL,
    is_verified BOOLEAN DEFAULT FALSE NOT NULL,
    enrolled_at TIMESTAMP WITH TIME ZONE,
    verified_at TIMESTAMP WITH TIME ZONE,
    last_used_at TIMESTAMP WITH TIME ZONE,
    backup_codes_remaining INTEGER DEFAULT 0 NOT NULL,
    failed_verification_attempts INTEGER DEFAULT 0 NOT NULL,
    last_failed_attempt_at TIMESTAMP WITH TIME ZONE,
    locked_until TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);

-- MFA backup codes for account recovery
CREATE TABLE IF NOT EXISTS mfa_backup_codes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    code_hash VARCHAR(64) NOT NULL,  -- SHA-256 hash
    code_hint VARCHAR(10) NOT NULL,  -- First 4 characters for user reference
    is_used BOOLEAN DEFAULT FALSE NOT NULL,
    used_at TIMESTAMP WITH TIME ZONE,
    used_from_ip INET,  -- IP address where code was used
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_mfa_backup_codes_user_id ON mfa_backup_codes(user_id);
CREATE INDEX IF NOT EXISTS idx_mfa_backup_codes_active ON mfa_backup_codes(user_id, is_used) WHERE is_used = FALSE;

-- MFA enrollment sessions (temporary, 15 min TTL)
CREATE TABLE IF NOT EXISTS mfa_enrollment_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    temp_totp_secret_encrypted BYTEA NOT NULL,
    qr_code_data TEXT NOT NULL,
    is_active BOOLEAN DEFAULT TRUE NOT NULL,
    verification_attempts INTEGER DEFAULT 0 NOT NULL,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    completed_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_mfa_enrollment_sessions_user_id ON mfa_enrollment_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_mfa_enrollment_sessions_active ON mfa_enrollment_sessions(id, is_active, expires_at) WHERE is_active = TRUE;

-- MFA verification audit log
CREATE TABLE IF NOT EXISTS mfa_verification_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    method VARCHAR(20) NOT NULL,  -- 'totp', 'backup_code', 'trusted_device'
    success BOOLEAN NOT NULL,
    ip_address INET,
    user_agent TEXT,
    device_id UUID,
    error_code VARCHAR(50),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_mfa_verification_log_user_id ON mfa_verification_log(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_mfa_verification_log_failed ON mfa_verification_log(user_id, success, created_at DESC) WHERE success = FALSE;

-- Function to check if MFA is required for a user
CREATE OR REPLACE FUNCTION is_mfa_required(p_user_id UUID)
RETURNS BOOLEAN AS $$
BEGIN
    -- In production, this would check user role, department, access level, etc.
    -- For now, MFA is required for all users
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql STABLE;

-- Function to record MFA attempt
CREATE OR REPLACE FUNCTION record_mfa_attempt(
    p_user_id UUID,
    p_method VARCHAR(20),
    p_success BOOLEAN,
    p_ip_address VARCHAR(45),
    p_user_agent TEXT,
    p_device_id UUID,
    p_error_code VARCHAR(50)
)
RETURNS UUID AS $$
DECLARE
    v_log_id UUID;
BEGIN
    -- Insert verification log
    INSERT INTO mfa_verification_log (
        user_id, method, success, ip_address, user_agent, device_id, error_code
    ) VALUES (
        p_user_id, p_method, p_success, p_ip_address::INET, p_user_agent, p_device_id, p_error_code
    )
    RETURNING id INTO v_log_id;

    -- Update MFA config
    IF p_success THEN
        UPDATE mfa_config
        SET
            last_used_at = NOW(),
            failed_verification_attempts = 0,
            last_failed_attempt_at = NULL,
            locked_until = NULL
        WHERE user_id = p_user_id;
    ELSE
        UPDATE mfa_config
        SET
            failed_verification_attempts = failed_verification_attempts + 1,
            last_failed_attempt_at = NOW(),
            locked_until = CASE
                WHEN failed_verification_attempts + 1 >= 5 THEN NOW() + INTERVAL '30 minutes'
                ELSE NULL
            END
        WHERE user_id = p_user_id;
    END IF;

    RETURN v_log_id;
END;
$$ LANGUAGE plpgsql;