Nagging by Timer — the Ten-Hour Swipe-Out Email

After part 9's buried failures, a palate cleanser: the smallest, most human executable in the estate. When the platform grew a work-from-home mode, employees got a virtual timer — swipe in from the browser in the morning, swipe out when you stop. Except people don't stop; they close the laptop. The timer runs on, the day's recorded productivity climbs past anything credible, and — because WFH days can carry an allowance — inflated hours eventually become somebody's payroll conversation. The estate's answer is a nag: an Azure Function on a five-minute timer that finds everyone virtually swiped in for more than ten hours and sends them an email asking, politely, whether they meant it.

The query with magic in it

Each tick, the function joins employees to their open virtual sessions and filters:

var candidates =
    from v in db.VirtualCheckIn
    join e in db.Employees on v.EmployeeId equals e.EmployeeId
    where v.LastNode == 8                       // 8 = swiped in. Obviously.
       && !v.IsReminded
       && (v.LogDate == today || v.LogDate == yesterday)
    select new { e, v };

LastNode == 8 is doing a lot of unexplained work. Node types come from the badge world — 8 means an in event, 7 an out — a vocabulary defined nowhere in code, inferable only from a stored procedure elsewhere that labels them Login/Logout in a CASE expression. The virtual-swipe tables borrowed the physical gates' encoding, so a browser click is recorded as if it were door hardware, and every consumer since has to know the numbers. Two constants — CheckIn = 8, CheckOut = 7 — would have documented the entire protocol; instead the magic number is load-bearing in at least three executables.

The today || yesterday window is smarter than it looks: it exists for the person who swiped in at 22:00, so their session straddles midnight without escaping the query — and it also quietly caps the blast radius, because a session abandoned three days ago falls out of the window rather than nagging forever. Elapsed time is then computed in one of two shapes — now − StartTime for a fresh session, or now − LastDateTime + Productivity for one that has been paused and resumed — and anyone past the configured threshold (600 minutes) gets mail.

At most once, with a hole in it

What stops the five-minute loop from emailing the same person every five minutes for the rest of the evening is one bit:

foreach (var c in overThreshold)
{
    await SendReminderAsync(c.e, c.v);   // 1. the side effect
    c.v.IsReminded = true;               // 2. the record of it
}
db.SaveChanges();                        // 3. the commit, later still

Send, then flag, and the flag's commit happens after the loop. Crash anywhere between step 1 and step 3 — the function host recycles, the database blips — and on the next tick the email goes out again, because the flag never landed. So the mechanism is not exactly-once, and it is not at-most-once either; it is at-most-once per swipe-in, at-least-once per failure window — duplicates possible, infinite loops impossible, since the flag eventually sticks.

Here is the thing though: for this workload, that is the correct corner to cut, and I want to defend it explicitly. The two available failure modes are “occasionally a person gets the nag twice” and “flag first, send second — and a send failure means a person who should have been nagged never is”. The first costs a shrug. The second costs the exact outcome the function exists to prevent, invisibly. When a side effect and its record cannot be made atomic — and email plus SQL genuinely cannot, without an outbox — choose the failure mode you can apologise for. A duplicate nag is self-explaining; a missing one is a silent defect. The estate got this right, even if I suspect by accident rather than analysis.

Templates from the database, rendered by reflection

The email itself is assembled from RawEmail rows — subject and HTML body stored in the database, selected by IsActive and an Environment column, with #{Model.Property} placeholders resolved recursively by about sixty lines of regex-and-reflection. That mini-language, its seeding inside OnModelCreating, and its Cc-recipients-added-to-To bug are the web series' territory — Email Templates Live in the Database dissects the engine this function merely calls.

One rendering hazard belongs here, because it is the notifier that detonates it. Inline images are attached by scanning the HTML for cid: references against an images folder, and then — to inject the attachment's content ID — the code runs string.Format over the entire HTML body. string.Format treats every { as a format placeholder. HTML email bodies contain CSS. The day a template gained an embedded style block, every send threw FormatException — a template edit, made in a database table by a non-developer, able to take down the transport. The estate's git history even contains a migration whose name records a CSS change to an email template; braces in email HTML were a lived concern, not a hypothetical.

The config holes

Transport is environment-branched: staging sends through a sandbox SMTP service so test nags never reach real inboxes; production sends as the platform's own mailbox through the Gmail API — a service account with domain-wide delegation, authenticated by a private-key file that was, in the pattern this estate repeats everywhere, committed to the repository. (The key's passphrase, notasecret, is Google's own documented default for P12 files, which tells you how much protection that layer was ever going to add. The estate's secret postures get a proper reckoning in the retrospective.)

The branch itself hides two traps:

1. Staging's config omits the images folder. The EmailImages path key simply isn't present in the staging settings file — and the image-association code runs unconditionally, so new DirectoryInfo(null) throws on the first send that includes an image. Whole categories of template worked in production and failed in staging, which is precisely backwards from what staging is for. Config files that drift by omission are invisible in diffs unless you diff against the other environment, which nobody does.
2. local.settings.json says Environment: production. The file a developer's F5 picks up locally is checked in pointing at the live branch of every switch — live templates, live threshold and, decisively, the live Gmail path. A developer debugging the notifier on a laptop, with production database access, is one unguarded run away from sending real nag emails to real employees from the real mailbox. The blast radius of a local debug session should be the laptop; here it is the org chart.

Two hundred and eighty-eight heartbeats

Every tick ends with a StatusSheet row: job name, "Success", and a message that ships a typo so faithfully preserved it deserves quoting — “Remainder Email Job Exceuted”. At a five-minute cadence, that is ~288 rows a day, every day — over a hundred thousand rows a year of nothing happened, written into the same table that serves as another job's watermark and the whole estate's incident log. The signal — the occasional Failure row, the sync that didn't run — sits in a haystack that grows by 288 straws daily. Heartbeats are legitimate; heartbeats in the log-of-record are how logs stop being read.

And buried in that same projection, one more fossil worth a smile on the way out: the filter compares dates in UtcNow while the human-readable message formats DateTime.Now — two clocks in one query, disagreeing by five and a half hours. In an attendance system, whose entire product is when things happened, that is not a nitpick; it is the subject of the next part. Next, an attendance system plays timezone roulette.