A daily reminder email, an hourly cron, and a query window wide enough to hold today in every time zone

CogniPrep now tracks the jobs a candidate has applied
to, with the dates attached: online tests due, assessment centre, interview.
Dates mean reminder emails, and reminder emails mean the scheduler, which is
the whole of vercel.json:

{
  "crons": [
    { "path": "/api/cron/update-population-stats",  "schedule": "0 2 * * *" },
    { "path": "/api/admin/data-retention",          "schedule": "0 3 * * *" },
    { "path": "/api/cron/nurture-emails",           "schedule": "0 * * * *" },
    { "path": "/api/cron/application-reminders",    "schedule": "15 * * * *" },
    { "path": "/api/cron/calendar-practice-days",   "schedule": "30 */6 * * *" }
  ]
}

Five entries, three of which run on the hour and two of which deliberately do
not. Both new routes are live and both refuse you:

curl -s https://cogniprep.app/api/cron/application-reminders
# {"error":"Unauthorized"}
curl -s https://cogniprep.app/api/cron/calendar-practice-days
# {"error":"Unauthorized"}

The email is daily. The job is hourly. That is not a contradiction

A reminder has to arrive at a sensible local hour. Our candidates are in a lot
of countries, and "sensible" is defined in theirs, not in UTC. If this were a
single daily job at 07:00 UTC, it would be 3am for someone and 11pm for someone
else, and the people who most need the reminder are the ones who would get it
while asleep.

So the schedule is hourly and the decision is per row, in the row's own zone:

export const EARLY_REMINDER_DAYS = 3;
export const FINAL_REMINDER_DAYS = 1;

/** Local hours (inclusive start, exclusive end) in which reminders may be sent. */
export const SEND_WINDOW_START_HOUR = 8;
export const SEND_WINDOW_END_HOUR = 21;

export function reminderDue(event: ReminderCandidate, now: Date): ReminderKind | null {
  const hour = Number(wallTimeIn(now, event.time_zone).slice(0, 2));
  if (hour < SEND_WINDOW_START_HOUR || hour >= SEND_WINDOW_END_HOUR) return null;

  const daysLeft = daysBetween(todayIn(event.time_zone, now), event.occurs_on);
  if (daysLeft < 0 || event.final_reminder_sent_at) return null;
  if (daysLeft <= FINAL_REMINDER_DAYS) return 'final';
  if (daysLeft <= EARLY_REMINDER_DAYS && !event.early_reminder_sent_at) return 'early';
  return null;
}

A pure function with no clock and no database, which is the only reason the
timing rules are testable. A date added inside the three-day window skips the
early reminder instead of firing both back to back, and every date carries the
zone the candidate was in when they typed it, because a deadline is a calendar
date until a time is attached to it.

The job runs at :15. The nurture campaign already owns :00, and two email
jobs in the same minute compete for the same provider rate limit for no reason.
Fifteen minutes is free.

A window wide enough for every zone, narrowed one row at a time

Postgres cannot evaluate reminderDue, and I did not want a query per zone. So
the SQL fetches a deliberately generous window and the function throws most of
it away:

// A window wide enough to hold "today" in every zone on Earth plus the early
// reminder's lead, narrowed per row by reminderDue in the row's own zone.
const utcToday = todayIn('UTC', now);
// ...
between(
  applicationEventsTable.occurs_on,
  addDays(utcToday, -1),
  addDays(utcToday, EARLY_REMINDER_DAYS + 1)
)

Six calendar days of candidate dates, filtered down to the ones whose local
clock has reached 08:00 and whose reminder is owed. The WHERE clause also
checks three switches before any of that: the application is still active,
reminders are on for it, and the account has email notifications enabled. The
cheapest email to send is the one the query never selects.

The date arithmetic underneath is the boring part that has to be right:

 * Dates are ISO strings (YYYY-MM-DD) throughout, which is what a Postgres `date`
 * column returns through drizzle's default string mode. Arithmetic on them goes
 * through Date.UTC so a daylight-saving change can never make a day 23 or 25
 * hours long and shift the count by one.
 *
 * Zero imports, so the client can use the same helpers to label days.

Zero imports is not an aesthetic choice. The browser labels "in 3 days" with the
same function the cron counts with, so the badge on the page and the subject
line of the email cannot disagree.

Claim, send, release

Email jobs have one failure mode that users actually notice, and it is not
missing mail, it is the same mail twice. The run claims each reminder by writing
its sent-at column first, and only sends what it won:

const claimed = await db
  .update(applicationEventsTable)
  .set({ early_reminder_sent_at: now })
  .where(
    and(
      inArray(applicationEventsTable.id, early),
      isNull(applicationEventsTable.early_reminder_sent_at),
      isNull(applicationEventsTable.final_reminder_sent_at)
    )
  )
  .returning({ id: applicationEventsTable.id });

RETURNING is the whole mechanism: a row already claimed by an overlapping run
does not come back, so it is not in the batch that gets emailed. Then the
failures are undone:

if (failedIndexes.size > 0) {
  await release(chunk.filter((_, index) => failedIndexes.has(index)).flat())
    .catch((error) => logError('[application-reminders] failed to release claims', error));
}

Which leaves one deliberate hole, stated in the module's own comment: a run that
dies between claiming and sending loses those reminders rather than resending
them. For a deadline reminder that is the right way round. A candidate who does
not get a nudge about Thursday's test still has the date in the product and in
their calendar; a candidate who gets the same nudge twice an hour apart has
learned something about us.

One more thing the claim buys: reminders are grouped per candidate before
sending, dates sorted, so somebody with an interview and a test in the same week
gets one email rather than two. The batches are chunked at 100, which is one
request per 100 recipients rather than one per recipient.

The second cron exists because only one half of a sync needs a job

Dates can leave the product two ways. Either your calendar app subscribes to a
feed, or you connect Google Calendar and we write events into it. Only one of
those needs a schedule, and the route comment says which:

 * Moves the practice days in candidates' Google calendars on to today's plan.
 * Runs every 6 hours (vercel.json) so each candidate's calendar catches up
 * within a few hours of their own midnight, wherever they are. The subscription
 * feed needs no job: it is built fresh on every fetch.

A subscription is a pull: your calendar app asks, we generate the current
answer, there is nothing to keep in sync and nothing to go stale. Google is a
push: it holds a copy, and a copy has to be corrected when the thing it copied
moves. Practice days move every midnight, in 24 different midnights.

Edits do not wait for that job, though. Every write to an application queues a
sync for after the response:

export function scheduleCalendarSync(userId: string): void {
  after(() => syncGoogleCalendar(userId));
}

after() from next/server, so the user's save returns immediately and Google
is updated a second later. The sync is a full reconcile rather than a diff, which
is what makes it safe to fire from every write: five edits in ten seconds queue
five syncs and the fifth leaves the calendar in exactly the state one sync would
have. Candidates with no calendar connected pay one indexed read and nothing else.

So the six-hourly job is not the sync. It is the part of the sync that no user
action triggers, which is the day changing underneath a plan nobody touched.

Both of these are behind a login, so the honest "see it" here is two curls and
one page: run the two curl commands above and watch the guard answer first,
then read section 12 of the privacy policy,
which states the same three-days-and-one-day rule in prose, including how to
switch it off. If you want the emails, the tracker is on the dashboard after you
sign up.