Notification Logs

We store and use notification logs for every notification that implements \App\Notifications\Support\Log\ShouldLog.

The logs are generated using event handlers (@see \App\Modules\Notification\Providers\NotificationServiceProvider::boot)`.

Use-cases

We use notification logs for different purposes. Namely:

1. Frequency limit checks
2. Payment disputes: provide info to payment gateways (Stripe, PayPal) that we notify our customers before charging their stored payment methods
3. Health check (notification audit relies on logs)

Note: We need to store all sent-out notifications (the complete event log) to fulfill #2.

Log Expiration

The notification log expiration system automatically manages the lifecycle of notification logs to prevent the database from growing unboundedly. This is critical for performance since notification logs can accumulate millions of records over time.

How It Works

1. Automatic Expiration: When certain notifications are sent, they can specify how long their log records should be kept (min: 2 months (62 days))
2. Background Cleanup: A scheduled job (⚙️notification:cleanup-expired-logs) runs regularly to remove expired log records
3. Storage Management: This prevents the notification logs table from becoming too large, which would slow down the application
4. Selective Retention: Different types of notifications can have different retention periods based on their importance and frequency limit (⚙️see ShouldProvideNotificationLogExpiration)

Technical Implementation

DB and performance

We use MySQL (our main DB) to store notification logs. In order to query the logs (millions of records), we need to add proper indexes.

Since the notification log is queried each time we send out a new notification, the performance (response time) is crucial.

In order to reduce the load on our main DB and not have the cost of adding indexes (reduce the cost of write operations), we decided to simplify the notification logs.

Known indexes and their creation history:

1. recipient_type + recipient_id (notification__notification_log_recipient_index)

   • Created in squashed migrations (part of initial table creation)

2. arbitrary_recipient (notification__notification_log_arbitrary_recipient_index)

   • Created in squashed migrations (part of initial table creation)

3. notification_class + sent_at (notification_class_sent_at_index)

   • Created in commit: 46b42cc3dc7 (#18214 Reverse compound index order) 2021-06-17

4. chain_class + chain_level (notification__notification_log_chain_index)

   • Created in squashed migrations (part of initial table creation)

5. sent_at + created_at (sent_at_with_order_by_index)

   • Created in commit: 20148a4f945 (#18214 Add indices to increase performance)

6. failed_at (failed_at_index)

   • Created in commit: 20148a4f945 (#18214 Add indices to increase performance)

7. [removed] notification_class (notification__notification_log_notification_class_index)

   • Created in squashed migrations (part of initial table creation)

8. [removed] sent_at (notification__notification_log_sent_at_index)

   • Created in squashed migrations (part of initial table creation)

Index Creation Timeline

Original table creation:

• 9c755e8b32322683f98dca208079c7c77bae37e6 (#20320 Use .sql file instead migration) - This commit created the table with most indexes from the production database dump, replacing the previous migration-based approach.

Earlier migration history (now squashed):

• 73b29cbda02 (2015) - Initial notification activity logs model creation
• 20148a4f945 (2021) - Added failed_at_index and sent_at_with_order_by_index
• 011c8d01c31 (2021) - Added compound index (later modified)
• 46b42cc3dc7 (2021) - Reversed compound index order to notification_class_sent_at_index