foxhunt/migrations/019_fix_compliance_integration.sql

-- Migration 019: Fix Compliance Integration Issues
-- Purpose: Resolve IP address type mismatch and add missing SOX compliance columns
-- Date: 2025-10-07
-- Agent: 95

-- ============================================================================
-- Issue 1: IP Address Type Mismatch
-- The `client_ip` column is INET but application provides TEXT strings.
-- We need to ensure proper casting in the application code.
-- This migration adds helper function for validation.
-- ============================================================================

-- Add validation function for IP addresses (helps with error messages)
CREATE OR REPLACE FUNCTION validate_ip_format(ip_text TEXT) RETURNS INET AS $$
BEGIN
    RETURN ip_text::INET;
EXCEPTION WHEN OTHERS THEN
    RAISE EXCEPTION 'Invalid IP address format: %', ip_text;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

COMMENT ON FUNCTION validate_ip_format IS 'Validates and converts text to INET type with better error messages';

-- ============================================================================
-- Issue 2: Missing SOX Compliance Columns
-- Add required columns for SOX access control and retention testing
-- ============================================================================

-- Add missing columns to audit_trail table
ALTER TABLE audit_trail
ADD COLUMN IF NOT EXISTS access_denied BOOLEAN DEFAULT FALSE,
ADD COLUMN IF NOT EXISTS denial_reason TEXT,
ADD COLUMN IF NOT EXISTS retention_period_days INTEGER,
ADD COLUMN IF NOT EXISTS access_granted BOOLEAN DEFAULT TRUE;

-- Add comments for documentation
COMMENT ON COLUMN audit_trail.access_denied IS 'SOX: Tracks unauthorized access attempts';
COMMENT ON COLUMN audit_trail.denial_reason IS 'SOX: Explanation for access denial';
COMMENT ON COLUMN audit_trail.retention_period_days IS 'SOX: Data retention period (typically 2555 days = 7 years)';
COMMENT ON COLUMN audit_trail.access_granted IS 'SOX: Tracks successful access grants';

-- Create index for access control queries (SOX requirement)
CREATE INDEX IF NOT EXISTS idx_audit_trail_access_denied
ON audit_trail(access_denied, event_timestamp)
WHERE access_denied = TRUE;

-- Create index for retention policy queries
CREATE INDEX IF NOT EXISTS idx_audit_trail_retention
ON audit_trail(retention_period_days, event_timestamp)
WHERE retention_period_days IS NOT NULL;

-- ============================================================================
-- Issue 3: Add SOX Access Control Tracking View
-- Provides quick access to security audit information
-- ============================================================================

CREATE OR REPLACE VIEW sox_access_control_audit AS
SELECT
    id,
    event_id,
    user_id,
    username,
    session_id,
    ip_address,
    target_type,
    target_id,
    operation,
    access_granted,
    access_denied,
    denial_reason,
    event_timestamp,
    compliance_review_required
FROM audit_trail
WHERE access_denied = TRUE
   OR compliance_review_required = TRUE
ORDER BY event_timestamp DESC;

COMMENT ON VIEW sox_access_control_audit IS 'SOX: View of all access control events requiring review';

-- ============================================================================
-- Issue 4: Add Retention Policy View
-- Helps with SOX 7-year retention compliance
-- ============================================================================

CREATE OR REPLACE VIEW sox_retention_policy AS
SELECT
    id,
    event_id,
    event_type,
    user_id,
    target_type,
    target_id,
    retention_period_days,
    event_timestamp,
    event_timestamp + (retention_period_days || ' days')::INTERVAL AS retention_expires_at,
    CASE
        WHEN event_timestamp + (retention_period_days || ' days')::INTERVAL < NOW()
        THEN 'EXPIRED'
        ELSE 'ACTIVE'
    END AS retention_status
FROM audit_trail
WHERE retention_period_days IS NOT NULL
ORDER BY event_timestamp DESC;

COMMENT ON VIEW sox_retention_policy IS 'SOX: Tracks data retention compliance (7-year requirement)';

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

-- To rollback this migration:
-- DROP VIEW IF EXISTS sox_retention_policy;
-- DROP VIEW IF EXISTS sox_access_control_audit;
-- DROP INDEX IF EXISTS idx_audit_trail_retention;
-- DROP INDEX IF EXISTS idx_audit_trail_access_denied;
-- ALTER TABLE audit_trail DROP COLUMN IF EXISTS access_granted;
-- ALTER TABLE audit_trail DROP COLUMN IF EXISTS retention_period_days;
-- ALTER TABLE audit_trail DROP COLUMN IF EXISTS denial_reason;
-- ALTER TABLE audit_trail DROP COLUMN IF EXISTS access_denied;
-- DROP FUNCTION IF EXISTS validate_ip_format(TEXT);