Four email campaigns that anchored on activity now anchor on signup, and the correlated subqueries went with them

CogniPrep sends a small set of nurture emails from an hourly cron. Four of them
carry a discount. Until last week each one picked its audience by asking a
different question about when you last did something:

• assessment_centre: first attempted an assessment centre exercise 1 to 2 days ago
• interview: first practised a mock interview 1 to 2 days ago
• scores_feedback: most recent game 1 to 2 days ago
• win_back: signed up 1 to 3 hours ago

Three activity anchors and one signup anchor. They are all signup anchors now,
and the change is more interesting in the SQL than in the product.

What an activity anchor costs you in a WHERE clause

The old scores_feedback predicate:

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}

Two correlated subqueries, each aggregating that user's sessions, evaluated per
candidate row, to answer a question about a window.

The new one:

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

where the shared predicate is a plain column range:

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

MAX(...) has to read the user's rows to know the answer. EXISTS can stop at
the first one. And the timing question moved onto a column of the table already
being scanned, so the expensive part of the predicate no longer participates in
the time filter at all. The same rewrite happened in all three activity
campaigns: MIN(exercise_attempts.created_at) in a window became
EXISTS (SELECT 1 FROM exercise_attempts ...), and the same for interviews.

The real payoff is a property, not a plan

Every discount campaign now scans the same 1 to 3 hour signup window. The
anti-spam rule is that a user who has been sent any nurture email in the last
day is skipped. One day is longer than two hours. So:

An account receives at most one discount email, ever, and which one it is
comes from the order the cron walks the campaigns.

That is not a rule anyone wrote. It falls out of two windows and their relative
sizes. The ordering comment says it plainly:

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 },
];

A reader who tried an exercise in their first hours hears about the exercise
library. One who tried an interview hears about interviews. win_back is the
email for everyone else, which is why it sits fourth: it is the fallback, not
the default.

referral sits last and is the only campaign still anchored on activity, 1 to 2
days after the user's last. It sells nothing, so it is allowed to arrive later,
and it cannot collide with a discount email because a code minted during the
signup window keeps every other campaign away for a day.

Why the old gap had to shrink first

The anti-spam gap used to be three days. The comment on why it was cut is my
favourite kind of comment, because it records a cost that was invisible:

A longer one was quietly costing sends: the activity campaigns anchor on
something 1 to 2 days old, so a reader held back for three days had usually
aged out of every window by the time they were allowed another email, which
turned the gap into a de-facto one-email-per-user cap.

A spam guard that is wider than every window it guards is not a guard, it is a
cap. Nobody would have written "send each user exactly one email" as a rule, but
three days plus a two day window is that rule in disguise.

The gate that is now redundant and stays anyway

export const NURTURE_MAX_ACCOUNT_AGE_DAYS = 7;

No account older than a week is ever offered a discount. With every campaign now
firing in the first three hours, this can never be the binding constraint. It
stays, and the comment explains the reasoning in the second person to whoever
changes this next:

Every discount campaign now anchors on a signup window far narrower than this,
so today the gate is a second line of defence rather than the only one. It is
kept because the campaigns used to anchor on activity, which any dormant
account can manufacture on demand. If a campaign is ever anchored on activity
again, this gate is what stops that leak.

That is the whole argument for keeping dead guards: the thing it defends against
is not gone, only unreachable by the current routing. Deleting it means the next
person who adds an activity anchored campaign reopens a hole they never knew was
closed.

It also notes the distinction that trips people up on offers: this bounds who is
offered a discount, not how long an offer already made stays valid. A code
carries its own validity from the moment it is minted.

Why a two hour window for an hourly job

export const NURTURE_MIN_AGE_HOURS = 1;
export const NURTURE_MAX_AGE_HOURS = 3;

An hourly cron with a one hour window would catch each account exactly once, if
every run happened. Runs do get skipped. A two hour window means most accounts
are scanned twice, and the idempotent (user_id, campaign) code row plus the
anti-spam predicate make the second scan a no-op. Only accounts created during a
genuinely skipped hour need the extra hour, and they get it for free.

The dispatcher has a 240 second run budget and stops cleanly when it runs out,
which is the other reason the window is wider than the cadence: anyone deferred
by the budget is still inside the window on the next run.

See it

The campaigns exist because three separate things can be unlocked, and each has
a page that explains what it is:

• cogniprep.app/interview is the surface behind the interview campaign.
• cogniprep.app/assessment-centre-practice is the one behind assessment_centre.
• cogniprep.app/pricing is where a code lands when one is used, and it is also the page that makes the one-email-per-account property matter: everything here is a one time purchase, so there is no renewal to discount later.