PhoenixKit V13 Migration: Enhanced Email Tracking with AWS SES Integration

This migration enhances the email tracking system to support comprehensive AWS SES event types and improved message ID synchronization.

Changes

Email Log Enhancements

• Adds aws_message_id column for improved AWS SES message correlation
• Adds timestamp columns for detailed event tracking (bounced_at, complained_at, opened_at, clicked_at)
• Expands status enum to include all AWS SES event types
• Adds partial unique index on aws_message_id for duplicate prevention (only for non-NULL values)

Email Event Enhancements

• Adds support for reject, delivery_delay, subscription, and rendering_failure events
• Adds specific fields for new event types (reject_reason, delay_type, subscription_type, failure_reason)
• Expands event_type enum validation

New Event Types Supported

• reject: Email rejected by SES before sending
• delivery_delay: Temporary delivery delays
• subscription: List subscription/unsubscription events
• rendering_failure: Template rendering failures

New Status Types

• rejected: Email rejected by SES
• delayed: Email delivery temporarily delayed
• hard_bounced: Permanent bounce (non-recoverable)
• soft_bounced: Temporary bounce (recoverable)
• complaint: Spam complaint received

PostgreSQL Support

• Supports PostgreSQL prefix for schema isolation
• Uses proper timestamp types for event tracking
• Adds necessary constraints for data integrity
• Backward compatible with existing data