Five lifecycle campaigns, one two-hour window, and the subqueries that became EXISTS

CogniPrep sends a small set of lifecycle emails. An hourly cron selects an audience per campaign, mints a single-use code, and sends. There are five campaigns and they are processed in a fixed priority order:

const CAMPAIGNS = [
  { 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 },
];

Until yesterday, four of those five asked the database a question about activity. The change I want to describe replaced that question with a question about signup, and the interesting part is not the marketing reasoning. It is that one rewrite removed a farming route, removed four correlated subqueries, and removed a rule I had written to compensate for the old shape.

The old shape

Here is the audience query for the assessment-centre campaign as it was. The product surface it nudges is assessment centre practice: e-tray and in-tray tasks, written exercises, group discussions.

SELECT ...
FROM users_table
WHERE users_table.email_notifications_enabled = true
  AND users_table.created_at > $signupCutoff          -- account younger than 7 days
  AND users_table.assessment_centre_unlocked = false
  AND (SELECT MIN(exercise_attempts.created_at) FROM exercise_attempts
       WHERE exercise_attempts.user_id = users_table.id) >= $start   -- 2 days ago
  AND (SELECT MIN(exercise_attempts.created_at) FROM exercise_attempts
       WHERE exercise_attempts.user_id = users_table.id) < $end      -- 1 day ago
  AND NOT EXISTS (/* no nurture email in the last day */)
LIMIT 500

Read it as English: you tried an exercise between one and two days ago, you have not unlocked the thing, so here is an offer. The interview campaign had the same shape over a different table, and the scores campaign had it with MAX instead of MIN (last game rather than first attempt).

Three things were wrong with it.

It was farmable. The anchor does not care how old the account is, only how long ago the activity was. A dormant account could play one free game, wait a day, and collect a discount. Anyone about to pay list price would learn to stall instead. The 7-day account-age gate on line 4 was the only thing standing between that and a standing discount on everything.

The window fought the anti-spam rule. A user who has had any nurture email in the last day is skipped. But an anchor that is one to two days old means a reader held back for a day has usually aged out of every window by the time they are allowed another email. The gap quietly became a one-email-per-user cap, which is not what a gap is for.

The anchor was expensive to ask about. MIN(...) over a user's attempts has to look at all of that user's rows, and it was evaluated twice per candidate, once per bound.

The new shape

Every campaign that carries a discount now anchors on the same thing: the account was created one to three hours ago.

const signedUpInWindow = (start, end) => sql`
  users_table.created_at >= ${start}
  AND users_table.created_at < ${end}`;

And the activity condition stops being a timing anchor. It becomes an existence check:

  AND ${signedUpInWindow(start, end)}
  AND EXISTS (SELECT 1 FROM exercise_attempts
              WHERE exercise_attempts.user_id = users_table.id)

MIN(x) >= $start AND MIN(x) < $end becomes EXISTS. Semantically that is the whole point of the change: when you tried an exercise no longer matters, because the signup window has already pinned you to a two-hour slice of your own history. Everything you did happened inside it.

Operationally it is also the cheaper question. EXISTS can stop at the first matching row; MIN cannot. And the plain column comparison on created_at runs before the correlated subquery has to be evaluated at all, so it rules out all but the newest sliver of the table first.

The rule that deleted itself

Here is the part I did not expect when I started.

Because every discount campaign now scans the same two-hour window, and the anti-spam rule is a day wide, an account can only ever match one of them. The first campaign it matches mints a code; that code excludes it from every later campaign for 24 hours; by then the account is permanently out of the signup window, because a window anchored on created_at is one an account can never re-enter.

So "a new account receives at most one discount email" is not enforced anywhere. It is a consequence of two numbers, one of which is bigger than the other. The priority order in CAMPAIGNS is what decides which email: someone who had already tried an exercise or a mock interview in their first hours hears about that, and the catch-all welcome offer is the email for everyone else.

The account-age gate survived the rewrite, for one reason that is worth writing down somewhere other than a commit message. It is now a second line of defence rather than the only one, and it stays because the leak it blocks is a property of activity anchors in general. If a future campaign ever anchors on activity again, that gate is what stops the farming route coming back with it.

The campaign that kept the old anchor

One did not move: the referral ask, which is the only email that carries a reader's own referral code rather than a discount.

export const REFERRAL_MIN_AGE_DAYS = 1;
export const REFERRAL_MAX_AGE_DAYS = 2;

It anchors on the user's most recent activity, one to two days ago, and it sits last in the priority list. Both of those are deliberate. A customer worth asking to recommend the product has had time to use what they bought, which an account three hours old by definition has not. And because it sells nothing, anyone who also qualifies for a conversion email should get that one instead. Ordering alone enforces that, since a freshly minted row excludes the user from every later campaign in the same run. No second rule.

Why the window is wider than the cron interval

The cron runs hourly and the window is two hours wide. That overlap is not sloppiness.

A one-hour window would catch each account exactly once if every run happened. Runs do get skipped: a scheduled invocation that overlaps a deploy, a platform hiccup, a function that dies on its own time budget. An hour of signups falling into a skipped run would never be emailed at all, because the window they belonged to has closed for good.

Two hours means every account is scanned by two runs. The idempotent (user_id, campaign) row makes the second scan a no-op, so the only accounts the extra hour actually catches are the ones created inside a genuinely skipped hour. The de-duplication is the unique constraint, not the arithmetic.

What I would take from this

The rewrite was not motivated by query cost. It was motivated by the farming route, and the plan was just to add a created_at predicate.

What it turned into was the realisation that once eligibility is anchored on signup, the timing half of every activity anchor is dead weight. Four pairs of correlated subqueries collapsed into four EXISTS clauses, and a rule about one-email-per-account stopped needing to exist. The three problems had one shape underneath them, and the anchor was it.

If you want to see what the emails are selling, the products are on the pricing page and the provider practice hubs are at cogniprep.app/games.