Database migration and Notifications::EmailEvent model creation

Database migration/table creation for Notifications::EmailEvent

Problem

We currently track email notifications through various timestamp fields scattered across different models (e.g., Order#increased_billing_rate_notified_at, Order#seat_overage_notified_at, Order#auto_renew_error_notified_at). This approach lacks consistency, makes it difficult to track all sent notifications in one place, and complicates preventing duplicate emails when jobs are retried.

Proposal

Create a new Notifications::EmailEvent model and corresponding database table to centralize tracking of all email notifications sent by our system.

The table structure will include:

Field	Type	Description	Example
id	bigint	Primary key	1
notification_type	string	The type of notification sent	"upcoming_renewal_30_days"
sent_at	datetime	When the notification was sent	2024-01-01 12:00:00
recipient	string	Email address of recipient	test@example.com
source_type	string	Object type that triggered notification	"Order"
source_id	bigint	ID of the object that triggered notification	123
metadata	jsonb	Additional context data (flexible)	{"plan_name": "Premium", "amount": 1000}

Indexing Strategy: Based on the current UpcomingNotificationCronJob implementation and the future need to support multiple notification periods (90/60/45/30/1 day cadence), we need the following indexes:

# Composite index for efficiently finding notifications for a specific order and notification type
add_index :email_events, [:source_type, :source_id, :notification_type], 
          name: 'index_email_events_on_source_and_notification_type'

# Index for time-based queries (to replace the NOTIFICATION_PERIOD.ago check)
add_index :email_events, :sent_at

# Index for filtering by notification type (useful when implementing multiple periods)
add_index :email_events, :notification_type

The primary composite index will support queries that check if a specific notification has already been sent for an order, which will replace the current pattern of using the upcoming_auto_renewal_notified_at timestamp field. This will be particularly important when we implement multiple notification periods, as we'll need to track each period separately.

Result

• A centralized system for tracking all email notifications
• Ability to prevent duplicate emails by checking if a notification has already been sent
• Support for multiple notification periods (90/60/45/30/1 day cadence) in the future
• Improved auditing capabilities to see what notifications were sent, when, and to whom
• Foundation for future analytics on email notification patterns
• Efficient querying through properly indexed fields

Next steps (if any)

1. After this implementation, update existing notification services to use this new model
2. Consider adding a cleanup job to prevent unbounded table growth (low priority)

How will we measure success?

• All new email notifications are properly recorded in the EmailEvent table
• We can successfully prevent duplicate emails when jobs are retried
• We can query the table to get accurate counts of different notification types
• Query performance remains efficient as the table grows
• The table design supports the future implementation of multiple notification periods