SQL Based Notifications

Notification Type 1: A user registers and you want to send them a welcome email

def register_user(email, pwd):
    user_id = insert_into_db(email, pwd)
    send_welcome(email)

Notification Type 2: A user registers and you want to send them a welcome email five minutes later.

This is where we can write some sql that runs every minute:

SELECT email FROM user 
WHERE created_at > NOW() - INTERVAL 5 MINUTE;

This is a bit problematic since if we run every minute we'll double send notifications.

We could make it a bit safer by:

SELECT email FROM user 
WHERE created_at > NOW() - INTERVAL 5 MINUTE
     AND created_at <= NOW() - INTERVAL 4 MINUTE;

This is still a bit problematic since if it were run twice at the same time it may double send. We can work around this with a ledger:

CREATE TABLE notifications_ledger (
  `idempotency_key` varchar(255) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `status` ENUM('not_sent', 'sent') DEFAULT 'not_sent',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idempotency_key`)
);

Now we run:

WITH

users_of_interest AS (
    SELECT email, CONCAT('welcome:', email) as idempotency_key
    FROM user WHERE created_at > NOW() - INTERVAL 5 MINUTE; 
)

users_of_interest_without_sends (
   SELECT email, users_of_interest.idempotency_key FROM users_of_interest 
   LEFT JOIN notifications_ledger 
                ON users_of_interest.idempotency_key = notifications_ledger.idempotency_key
   WHERE notifications_ledger.idempotency_key IS NULL
)

INSERT IGNORE INTO notifications_ledger (idempotency_key)
SELECT idempotency_key FROM users_of_interest_without_sends;

Now just emit to all items in:

SELECT email FROM notifications_ledger WHERE status = 'not_sent'

Notification Type 3: Suppose you are twitter and need to notify followers when someone replies but only if they haven't received 5 notifications in the past hour:

WITH

new_posts AS (...)

new_posts_without_sends AS (...)

followers_of_new_posts AS (...)

followers_of_new_posts_with_less_than_3_notifications AS (
   SELECT * FROM followers_of_new_posts WHERE email NOT IN (

       SELECT COUNT(*), email FROM idempotency_ledger 
       WHERE created_at > NOW() - interval 1 hour
       GROUP BY email
       HAVING COUNT(*) > 3

  )

)

INSERT IGNORE INTO notifications_ledger (idempotency_key)
SELECT idempotency_key FROM followers_of_new_posts_with_less_than_3_notifications;

Good luck.