Audience: Backend developers Scope: Implementation. Behaviour is on the features page.

Newsletter - Backend

1. Documentation evidence

2. Scope and boundaries

Owns

• newsletter_subscriber, newsletter_campaign, newsletter_delivery
• The consent predicate, in exactly one place
• The three jobs on QueueName.NEWSLETTER
• Two email templates, composed from the shared design system

Does not own

• Email delivery. EmailNotificationService (notifications) owns the provider. This module hands it a rendered message and records what happened.
• The email design system. common/email/ owns the layout, the components and every hex literal. A module owns its copy, never its styling.
• Scheduling. The outbox and BullMQ own dispatch; this module writes intent.

3. File and directory map

apps/api/src/modules/newsletter/
├── newsletter.module.ts               root — imports the two leaves
├── newsletter-worker.module.ts        the async surface, registered separately
├── admin/campaign/                    compose, edit, send
├── customer/                          subscribe, confirm, unsubscribe
├── emails/                            the two templates
├── shared/                            link builder, constants
└── workers/
    ├── newsletter-queue.processor.ts        THE only @Processor on this queue
    ├── newsletter-send-campaign.processor.ts
    ├── newsletter-send-confirmation.processor.ts
    ├── newsletter-sweep-stalled.processor.ts
    └── newsletter-maintenance.scheduler.ts

NewsletterWorkerModule is registered separately from NewsletterModule in app.module.ts, so the HTTP surface and the worker surface can be deployed and reasoned about independently — matching PromotionWorkerModule and OutboxWorkerModule.

4. Data model

4.1 newsletter_subscriber

uq_newsletter_subscriber_email is deliberately not partial on unsubscribed_at. A partial index would let a second row shadow an unsubscribe; the re-subscribe must find and revive the existing row, because that row carries the original creation date and the suppression history.

Why the history columns exist separately from unsubscribed_at. The revive path is reachable by any unauthenticated caller who knows an address, and it clears unsubscribed_at. On its own that column answers "are they currently unsubscribed", never "did they ever ask to be left alone, and when" — and only the second question is asked after a complaint. A CHECK enforces that a currently unsubscribed row carries its history, so the two cannot drift into "unsubscribed now, never unsubscribed".

The email format check is deliberately loose: strict email regexes reject valid addresses, and the authoritative check is that the confirmation email arrives.

4.2 newsletter_campaign

recipient_count is frozen at send time. Two CHECKs make the counts self-consistent:

sent_count + failed_count <= recipient_count
status <> 'sent' OR sent_at IS NOT NULL

The first one is load-bearing far beyond tidiness — see §6.

4.3 newsletter_delivery

One row per (campaign, subscriber), and uq_newsletter_delivery_campaign_subscriber is the entire resume strategy. Without it a worker that dies at recipient 900 of 1,000 can only resume by sending all 1,000 again.

idx_newsletter_delivery_pending on (campaign_id, id) WHERE status = 'pending' serves both the claim query and the sweep.

5. The claim protocol

Sending is network I/O against a third party, and it must not happen inside a transaction.

┌─ claim transaction (short) ─────────────────────────────┐
│ SELECT id … WHERE status='pending'                       │
│   AND (claimed_at IS NULL OR claimed_at < now() - 15min) │
│   ORDER BY id LIMIT 50 FOR UPDATE SKIP LOCKED            │
│ UPDATE … SET claimed_at = now()                          │
│ SELECT the recipients (no lock on subscriber)            │
└─────────────────────── COMMIT ───────────────────────────┘
              │
              ▼   outside any transaction
        send, then settle each row individually

Why not just hold FOR UPDATE across the send. Two options, both wrong. One transaction for the whole batch means a failure at recipient 40 rolls back the status of the 39 emails that were genuinely delivered — and the retry sends them again. One transaction per recipient holds a lock across every send anyway.

Why the subscriber rows are not locked. The claim reads recipient details after the claim, in a plain select. Locking them would block unsubscribe, which is the one operation that must never wait on a send in progress.

The bias is toward not sending twice. A crash between the provider accepting a message and the row reaching sent leaves it claimed and pending; the reclaim only happens after the stale window. A duplicate promotional email is a complaint; a missing one is not.

6. The three phases

Materialise

One INSERT … SELECT … ON CONFLICT DO NOTHING per batch, inside one transaction holding pg_advisory_xact_lock on the campaign id, with the remaining allowance re-read from the live row count inside the same statement.

Both of those are the fix for the same defect, and it is worth stating precisely because it survived a fully green gate. The cap used to be a local counter decremented by each pass's own insert count, which is not a cap once two runs overlap. With recipient_count frozen at 1,000 and the confirmed set since grown to 1,500:

• A inserts subscribers 1–1000, its local counter hits zero, it stops.
• B read existing = 0 before A committed. Its first batch conflicts entirely, so its insert count is 0, its counter never moves, and its cursor advances past 1000 — batch two inserts 1001–1500.

finalise would then write sent + failed = 1500 against recipient_count = 1000, which chk_newsletter_campaign_counts_within_recipients rejects as a 23514, on every retry, forever. The campaign wedges in sending and 500 people outside the frozen set have been mailed.

The cursor is seeded from max(subscriber_id) already materialised rather than restarting at 0, and the scan limit is the full batch rather than the remaining allowance. Conflating those two meant a resume with one row left scanned one already-inserted candidate per statement — roughly 200,000 round trips on a list that size.

Claim and send

Per §5. Consent is re-asserted per recipient — both halves, confirmed_at IS NOT NULL AND unsubscribed_at IS NULL. Checking only the second half is exploitable: subscribe clears both timestamps on revive and is public, so anyone who knows an address can move a pending delivery's subscriber from "unsubscribed" to "unconfirmed" mid-campaign.

A provider rejection is recorded on the row and the loop continues. The catch narrows with error instanceof Error — a thrown string or null would otherwise raise a TypeError that escapes the try/catch that exists precisely so one bad address cannot abort the send. The log carries the delivery id only; provider text routinely echoes the recipient address, and the application log has different retention from the subscriber table.

Finalise

Counts the terminal rows and closes the campaign — only once nothing is pending. Two workers can be sending the same campaign after a stale reclaim, and the first to run out of claimable rows would otherwise freeze sent_count at a wrong number and tell an admin the campaign is finished.

7. BullMQ and async work

Exactly one class carries @Processor(QueueName.NEWSLETTER). Two decorated classes on one queue name are two independent workers racing for every job, and the loser's handler returns quietly on a name mismatch while BullMQ marks the job completed. That happened on QueueName.INVENTORY with four decorated classes; roughly three of four jobs were dropped and every gate stayed green.

Why the confirmation dedupe key is a token fingerprint. Keying on the subscriber would dedupe forever: somebody who never received the first email could ask again and again and the outbox would swallow every attempt. Keying on the raw token would copy a bearer secret into outbox_events.

Why the sweep exists. The send job is resumable, which is only useful if something resumes it. A job that exhausts its BullMQ attempts, or returns after its own pass ceiling, leaves the campaign in sending with pending rows and nothing scheduled — and the admin surface cannot help, because send refuses a non-draft by design. The sweep calls the send handler directly rather than implementing a second resume path.

The cron enqueues directly rather than through the outbox: a tick has no accompanying database write, which is the first of the two stated outbox exemptions.

8. Security and abuse controls

9. Testing and validation

The integration suites cover, among others: two concurrent materialisation passes respecting the frozen cap, a resume that does not rescan, consent cleared mid-campaign, a provider throwing a non-Error, stale-claim reclaim while leaving a fresh claim alone, and the unique index in both directions — the accept case matters, because a unique index on subscriber_id alone would pass every rejection test and make it impossible to ever mail anyone twice.

10. Operational runbook

A campaign is stuck in sending. Check for pending deliveries:

SELECT status, count(*) FROM newsletter_delivery
 WHERE campaign_id = (SELECT id FROM newsletter_campaign WHERE public_id = '…')
 GROUP BY status;

If rows are pending and unclaimed for over fifteen minutes, the sweep will resume it within five. If they are freshly claimed, a worker is on it — wait.

Nobody is receiving anything. Almost always zero confirmed subscribers:

SELECT count(*) FROM newsletter_subscriber
 WHERE confirmed_at IS NOT NULL AND unsubscribed_at IS NULL;

If that is zero and rows exist, the confirmation email is not arriving — check RESEND_API_KEY. Without it EmailNotificationService logs instead of delivering, which is deliberate and is what makes the whole path testable before anyone supplies a key.

A complaint about an unwanted email. source says how they got on the list, confirmed_at says when they agreed, and unsubscribe_count / last_unsubscribed_at say whether they had ever asked to leave before.

See Also

• Overview
• Features and flows
• API