| 1 | -- ─── notification_email_log ──────────────────────────────────────── |
| 2 | |
| 3 | -- name: InsertEmailLog :exec |
| 4 | -- Records an email send. Caller decides what to bind for thread_id |
| 5 | -- (NULL for thread-less notifications). MessageID is the SMTP / |
| 6 | -- transactional-provider message id when available; empty when |
| 7 | -- the sender doesn't surface one. |
| 8 | INSERT INTO notification_email_log |
| 9 | (recipient_user_id, notification_id, thread_kind, thread_id, message_id) |
| 10 | VALUES ($1, $2, $3, $4, $5); |
| 11 | |
| 12 | -- name: CountEmailsForRecipientThreadSince :one |
| 13 | -- Storm dampener probe: how many emails for this thread did we |
| 14 | -- send to this recipient in the last $4 minutes? Caller compares |
| 15 | -- to the cap. |
| 16 | SELECT count(*) FROM notification_email_log |
| 17 | WHERE recipient_user_id = $1 |
| 18 | AND thread_kind = $2 |
| 19 | AND thread_id = $3 |
| 20 | AND sent_at > now() - make_interval(mins => $4::int); |
| 21 | |
| 22 | -- name: CountEmailsForRecipientSince :one |
| 23 | -- Per-recipient absolute rate cap: how many total emails to this |
| 24 | -- recipient in the last $2 minutes? |
| 25 | SELECT count(*) FROM notification_email_log |
| 26 | WHERE recipient_user_id = $1 |
| 27 | AND sent_at > now() - make_interval(mins => $2::int); |
| 28 |