PhoenixKit V22 Migration: Email System Improvements & Audit Logging

This migration addresses critical issues in the email system and adds comprehensive audit logging:

Changes

Email System Fixes

• Adds aws_message_id field to phoenix_kit_email_logs for AWS SES message ID tracking
• Adds partial unique index on aws_message_id (preventing duplicates while allowing nulls)
• Adds composite index (email_log_id, event_type) for faster duplicate event checking
• Creates phoenix_kit_email_orphaned_events table for tracking unmatched SQS events
• Adds phoenix_kit_email_metrics table for system metrics tracking

Audit Logging System

• Adds phoenix_kit_audit_logs table for comprehensive action tracking
• Records admin actions with complete context (who, what, when, where)
• Supports metadata storage for additional context
• Indexed for efficient querying by user, action, and date

New Tables

• phoenix_kit_email_orphaned_events: Tracks SQS events without matching email logs
• phoenix_kit_email_metrics: Tracks email system metrics (extraction rates, placeholder logs, etc.)
• phoenix_kit_audit_logs: Immutable audit trail for administrative actions

Database Improvements

• Improved email log searching with dual message_id strategy
• Better duplicate prevention for events
• Enhanced debugging capabilities for AWS SES integration
• Complete audit trail for admin password resets and other sensitive operations

Migration Strategy

The aws_message_id field addition is idempotent - it's added only if it doesn't exist. All indexes use create_if_not_exists for safe re-runs.