Every compliance tool sends reminders. The difference between one that people act on and one that people filter into a folder is not the copy in the email — it is whether the same reminder can arrive twice. A duplicate is not a cosmetic bug. It is the moment a user learns that the system is approximate, and from then on every alert it sends is discounted.
So the scheduler is built around a single claim: for a given rule, subject, threshold and due date, exactly one notification is ever sent. Not usually one. One.
The obvious design, and why it fails
The naive approach is a nightly job that queries for obligations crossing a threshold and sends an email for each. It works until the day it does not, and the ways it breaks are all mundane:
- The job times out halfway through and the retry re-sends everything it already sent.
- Someone deploys during the run and two instances overlap.
- The job is run manually to debug an unrelated problem, and every user gets a second copy.
- A clock skew or a daylight-saving transition makes the job fire twice for one calendar day.
Every one of these is an operational accident rather than a logic error, which is why they survive code review. The fix cannot be more careful scheduling. It has to be a design where re-running the job is free.
A ledger, not a flag
Instead of marking the obligation as notified, we write a row to a notification ledger, and the uniqueness lives in the database rather than in the application. The scan is then idempotent by construction: it computes what should have been sent, tries to claim each one, and skips whatever is already claimed.
create table notification_ledger (
id bigint generated always as identity primary key,
org_id bigint not null references organisation (id),
rule_id bigint not null references alert_rule (id),
subject_id bigint not null,
threshold text not null,
due_on date not null,
claimed_at timestamptz not null default now(),
delivered_at timestamptz,
constraint notification_once
unique (org_id, rule_id, subject_id, threshold, due_on)
);The scan inserts with on conflict do nothing. If the insert reports no row, someone else already owns that notification and this worker moves on. Replaying an entire day of scanning produces zero outbound messages, which means a retry is safe, an overlapping deploy is safe, and running the job by hand to debug something is safe.
Claiming and delivering are different events
Note that the row records two timestamps. Claiming the notification and delivering it are separate steps, and collapsing them is how you get the opposite failure: a message that was never sent but is recorded as sent, because the transaction committed before the mail provider was called.
So the claim commits first, delivery is attempted after, and a claim with no delivery after a grace period is visible in the application rather than buried in a log. A notification that silently failed is worse than one that failed loudly, because the user's mental model says the register is being watched.
Due dates are calendar dates
The due_on column is a date, not a timestamp, and that is deliberate. A permit expiring on 31 March expires on the 31st where the site is. If you store an instant, you have silently picked a timezone for a legal deadline, and a site in another country will get its alert on the wrong day — or twice, on the two days its instant straddles.
Having emailed someone proves nothing to an inspector. The ledger is the evidence that the alert existed, not just that a job ran.
What this buys you
The ledger is also the audit artefact. When an inspector asks how you knew a certificate was lapsing, the answer is a row with a claim time, a delivery time and an acknowledgement — not a screenshot of an inbox. The mechanism that makes the system correct and the mechanism that makes it defensible turn out to be the same table, which is usually a sign the model is right.