We have no marketing state table, only five SQL windows over timestamps we already had

CogniPrep sends five lifecycle emails. Somebody tries an assessment-centre exercise and does not unlock it. Somebody plays a practice test but has not bought feedback. Somebody signs up and owns nothing an hour later.

The normal way to build this is a marketing state machine: a table per user holding which campaigns they are enrolled in, which step they are on, when they last received something. That table then has to be written by every part of your app that changes a user's situation, and it goes wrong the first time somebody buys a thing through a path that forgot to update it.

We have none of it. Each campaign is one SQL query over timestamps that already exist for other reasons.

A campaign is a window, not a state

SELECT users_table.id AS "userId", users_table.email AS email
FROM users_table
WHERE users_table.email_notifications_enabled = true
  AND users_table.plan_tier <> 'premium'
  AND cardinality(users_table.unlocked_providers) >= 1
  AND (SELECT MAX(game_sessions.created_at) FROM game_sessions
       WHERE game_sessions.user_id = users_table.id) >= :start
  AND (SELECT MAX(game_sessions.created_at) FROM game_sessions
       WHERE game_sessions.user_id = users_table.id) < :end
  AND NOT EXISTS (
    SELECT 1 FROM nurture_discount_codes
    WHERE nurture_discount_codes.user_id = users_table.id
      AND nurture_discount_codes.created_at > :antispamCutoff
  )
LIMIT 500

That is the whole of the "you played but never bought feedback" campaign. Read it as a sentence: opted in, not already Premium, owns at least one provider, last played between one and two days ago, and has not been emailed recently.

Every predicate reads current truth. There is no enrolment to keep in sync, because ownership is checked at query time. If somebody buys the thing five minutes before the cron runs, they fall out of the audience automatically. Nobody had to remember to unenrol them.

The anchors are correlated subqueries and the choice of aggregate carries meaning: MAX(created_at) is "last active", used by the campaigns that fire after someone goes quiet. MIN(created_at) is "first ever attempt", used by the campaigns that fire after somebody tries a surface for the first time. Two campaigns, same table, different aggregate, completely different intent.

These are written as raw SQL rather than through the query builder, deliberately. Correlated aggregates in a query builder read like a puzzle; here they read like the sentence above.

The de-dup is a row we were minting anyway

Each campaign mints a personal single-use discount code for the user it emails. That code row has a unique (user_id, campaign) constraint.

So the code row is the send log. There is no separate emails_sent table, because the thing we already had to create for the offer is also a durable record that this user got this campaign.

// Idempotent: null means this user already has this campaign's code, so
// they were emailed already, so skip.
const code = await mintNurtureCode({ userId, campaign, productType, productId, now });
if (!code) continue;

One unique constraint gives you idempotency for free. A user who matches the same window on two consecutive runs gets a code minted exactly once, and therefore an email exactly once, with no coordination beyond what Postgres already does.

One campaign sells nothing at all. The referral email carries the reader's own referral code, not a discount. It still mints a row, purely as a send-log marker, so that it counts against the one-email-at-a-time rule like everything else. The row is prefixed REFERLOG and is never shown to anyone.

The anti-spam predicate is also the priority system

This is the part I did not design and only noticed afterwards.

Campaigns run sequentially in priority order:

const CAMPAIGNS: CampaignSpec[] = [
  { campaign: 'assessment_centre', candidates: assessmentCentreCandidates },
  { campaign: 'interview',         candidates: interviewCandidates },
  { campaign: 'scores_feedback',   candidates: scoresFeedbackCandidates },
  { campaign: 'win_back',          candidates: winBackCandidates },
  { campaign: 'referral',          candidates: referralCandidates, sendLogOnly: true },
];

Every audience query carries that NOT EXISTS ... created_at > antispamCutoff clause. When an earlier campaign mints a code for someone, the later campaigns' queries stop matching them in the same run, because the row they are checking for now exists.

So "do not send two emails at once" and "the higher-value conversion email wins over the catch-all" are the same mechanism. There is no separate arbitration step, no priority column, no pre-pass that computes one winner per user. Ordering the array and running it sequentially is sufficient.

The anti-spam gap itself is one day, and it used to be three. Three was quietly costing sends: the activity campaigns anchor on something one to two days old, so anybody held back for three days had usually aged out of every window by the time they were allowed another email. A gap intended to space emails out had turned into a de-facto cap of one email per user, ever.

Two windows on an hourly cron, because runs get skipped

The welcome email fires shortly after signup:

export const WIN_BACK_MIN_AGE_HOURS = 1;
export const WIN_BACK_MAX_AGE_HOURS = 3;

The cron runs hourly. A one-hour-wide window would catch every account exactly once if every run happened. Runs do get skipped: a Vercel cron overlapping a slow predecessor, a deploy landing at the top of the hour. An account that fell into a skipped run would then never be offered anything at all, because the window has moved past it permanently.

A wider window means each account is scanned by two or three consecutive runs, and the unique constraint makes every scan after the first a no-op. You get skip tolerance for free, with the cost being nothing at all. This is worth internalising as a general rule for time-window cron jobs: make the window wider than the interval, and make the action idempotent. Either one alone is not enough.

The gate that stops the discounts being farmable

export const NURTURE_MAX_ACCOUNT_AGE_DAYS = 7;

Every campaign that carries a discount also requires the account to be younger than a week.

Without it the whole system is farmable, and the reason is precisely the property I praised above. The anchors are activity-based, and activity-based anchors do not care how old the account is. A long-dormant user could play one free game, wait a day, and collect 25% off. Anyone about to buy would learn to stall rather than pay list price, and the welcome offer becomes a standing discount on everything.

Anchoring eligibility on signup gives every account exactly one window it cannot re-enter. Note the separation that makes this honest: the gate bounds who is offered a discount, not how long an offer already made lasts. A code keeps its own validity period from the moment it is minted, so one issued on day six is still spendable on day nine and the expiry date printed in the email stays true.

Two failure modes worth stealing

Render failures release the send log. The mint happens before the email is built. If building it throws, the row has to be undone or the user is permanently marked as emailed for something they never received:

} catch (err) {
  // Rendering failed, so this user is getting nothing: release the
  // send-log row rather than marking them emailed.
  await discardUnsentNurtureCode(code.id);
}

Running out of time stops the whole run, not just the batch. The function has a time budget. When it expires, the loop sends what it has already minted and then stops:

// Out of time: leave the remaining campaigns for the next run rather than
// querying audiences we cannot email.
if (stoppedEarly) break;

Querying an audience you cannot email is not harmless. It is a set of correlated aggregate queries over the whole users table that produces nothing.

The bug that broke every campaign at once

A postscript, because it cost an evening and is entirely non-obvious.

Our postgres.js client is configured with transform: postgres.camel. The drizzle query builder reads results positionally and maps them itself, so the transform is invisible there. db.execute hands back the driver's own row objects, where the transform has rewritten every column name.

A plain AS user_id therefore arrives in JavaScript as userId. Reading row.user_id gives undefined. The cron then minted codes with an undefined user id, drizzle substituted DEFAULT, the NOT NULL column rejected it, and every campaign failed.

Hence the quoted aliases in every query above:

SELECT users_table.id AS "userId", users_table.email AS email

Selecting the camelCase name outright is stable whether or not the transform is configured. If you mix a query builder and raw SQL against the same client, check what your driver is doing to your column names.

See it

Sign up at cogniprep.app/signup and do nothing else. Roughly an hour later the welcome email arrives, and it is the win_back query above picking you up on the created_at anchor. Play a practice test at cogniprep.app/games instead and a different window catches you a day later.

If you want to see the state that drives it, it is all the ordinary account state: your plan, what you own, when you last played. That is the point. There is nothing else to look at.