Derive the queue, do not store it: resumable email campaigns

Sending one email is easy. Sending forty thousand is a different problem, and the difficulty is not throughput. It is what happens when the run stops halfway.

Because it will. A cron tick times out. PHP hits its memory limit. The SMTP relay starts refusing connections. Someone deploys. At that point you have sent an unknown number of messages, and you have to decide what to do next.

The two usual answers, and why both are bad

Restart from the beginning, and everyone who already received the message receives it again. On a list of forty thousand, that is a mass duplicate send, an unsubscribe spike, and a spam-complaint rate that follows your domain around for months.

Keep a cursor: store "last processed row" and resume from there. This works until the audience changes mid-run. Someone unsubscribes, someone new joins the segment, and the cursor now points at a position in a list that has shifted underneath it. You skip people, or you repeat them, and you cannot tell which.

Both fail for the same underlying reason: they treat progress as the thing to remember. Progress is a fact about the run, and runs die.

Derive it instead

The durable fact is not "how far did we get". It is "this person has been sent this campaign": one row, written at the moment of sending, that survives the death of the run that wrote it.

Once that record exists, the pending queue does not need to be stored at all. It is a query:

SELECT c.ID AS c_id, c.title, c.content, u.ID AS u_id, u.user_email
FROM   wp_users u
JOIN   mm_user_category     uc  ON u.ID = uc.user_id
JOIN   mm_campaign_category cc  ON uc.category_id = cc.category_id
JOIN   mm_campaigns         c   ON cc.campaign_id = c.ID
LEFT JOIN mm_user_campaign  d   ON u.ID = d.user_id AND c.ID = d.campaign_id
WHERE  d.status IS NULL
  AND  c.is_active = 1
LIMIT  19

The LEFT JOIN plus IS NULL is the whole idea: subscribers who have no delivery row for this campaign. Everything else follows from it.

Crash recovery comes free: the next tick asks the same question and gets the remaining people, so nobody has to know a run died. Double sends become impossible rather than merely unlikely, because a person with a delivery row cannot be selected and there is no flag to forget to set.

Audience changes look after themselves too. Someone added to the segment today appears in tomorrow's query, someone removed disappears, and there is no cursor to invalidate. The state also stays inspectable: "who has not received this yet" is a query anyone can run rather than an internal counter you have to trust.

Pacing belongs next to it

The same loop is where you keep the send inside the host's limits:

$send_per_time = (int) get_option('mm_send_amount_per_time');   // 19
$sleep         = (int) get_option('mm_send_sleep_time');        // 3_000_000 µs

foreach (UserCampaignPendingDTO::load_pending($campaign_id, $send_per_time) as $pending) {
    usleep($sleep);
    UserCampaignPendingMail::send_pending($pending, true);
}

A fixed batch per tick with a pause between messages. Unremarkable code, and it only works because the queue is derived. With a cursor, sleeping inside the loop widens the window in which a crash leaves the cursor lying about where you were.

What it costs

Every tick runs a five-table join. On a large list that needs indexes on the pivot tables and on the delivery status, and it costs more per tick than reading a cursor.

The delivery table grows as subscribers multiplied by campaigns. That is the price of the guarantee, and it doubles as the reporting table, since pending, sent and failed per campaign come out of those same rows.

A send that succeeds but fails to record will be retried. That is the correct direction to fail in, but it means the record must be written immediately after the send rather than batched at the end of the run.

The general shape

When you need to know what work remains, derive it from what has been completed instead of recording where you stopped. Completed work is durable and monotonic; position in a run is neither.

This is the same reasoning behind idempotent message consumers, reconciliation over event replay, and "desired state versus current state" in deployment tooling. In each case the system stops depending on remembering what it did, and starts depending on being able to look at what is true.