A credential expiring today was in neither the expiring list nor the expired list

A certificate that runs out today is the single most urgent row in a compliance system. It is the one where somebody is about to walk onto a shift without a valid credential, and it is the one the software exists to surface.

In this system it appeared on no counter at all.

The dashboard has a tile for credentials expiring within seven days. Its predicate is end_date > CURRENT_DATE AND end_date <= CURRENT_DATE + 7 days. Strictly greater than today, so today is excluded. The thirty-day tile uses the same lower bound and excludes today too. The expired counter uses end_date < CURRENT_DATE, so today is excluded there as well.

Today is not before today and it is not after today. A credential whose end date is today satisfies none of the three, and it vanishes from every number on the page.

Four expressions for one question

The action centre — a separate panel, on the same screen — has its own definition: end_date >= CURRENT_DATE AND end_date <= CURRENT_DATE + 15 days. Inclusive lower bound, fifteen days rather than seven or thirty.

The credential list has a fourth: a caller-supplied window in days, inclusive of today, capped at ten years. And unlike the other three, it applies no status predicate at all — so a revoked credential with a future end date appears in the list of things expiring soon, having already been withdrawn.

So the same question is asked four times with four different answers, and a user comparing the panel to the tile beside it sees two numbers that cannot be reconciled. The action centre lists someone the tiles do not count. Nobody reports this as a bug, because a compliance dashboard is exactly the kind of screen where people assume the discrepancy means something they have not understood yet.

The boundary is the decision, not the number

The window lengths — seven, fifteen, thirty — being different is fine. Those are legitimately different questions with different audiences: what is urgent this week, what needs booking onto a training session, what the manager should be aware of this month.

The inclusivity of the boundary is not a legitimate difference. It is the same decision, made three times, by three people who each wrote whichever comparison operator came to hand. Two of them typed > and one typed >=, and there is no world in which those express different intents about a credential that expires this morning.

When a date range appears in more than one query, the inclusivity of each end is a domain decision and it belongs in one named place. Not a comment, not a convention — a function, or a database view, or at minimum a constant that carries the operator with it. The moment the comparison is written inline in a query, it is a coin flip.

The general form of this is worth stating because it is not specific to dates. Any time you find the same predicate written more than once with small variations, the variations are almost never deliberate. They are the accumulated typing of several people, and the differences between them are the bug.

What “today” means is a second question

Underneath the operator problem is a bigger one. Every one of those predicates uses the database server’s idea of the current date.

The schema has a timezone column on each site. Nothing reads it. There is no timezone setting applied to the database connection, and the API’s own date-range parser hard-codes a fixed offset when it builds range boundaries — so a user in one part of the world asking for “today” gets a different day’s data than a colleague in another, on the same screen, from the same query.

A credential’s end date is stored as a plain date with no time and no zone. “Expires on the first of the month” is a different instant in two countries, and for a compliance system the instant matters, because the question is whether somebody is covered for the shift they are about to start.

A date boundary in a multi-site product needs a stated timezone, and the statement has to be somewhere the query can reach. Modelling the column and never reading it is the worst of both — it looks handled and it is not, and the next person to touch it will assume the hard part was done.

How the demo hid it

The last part is the one I did not expect, and it changed how I look at seeded data.

The seeding script generates its “expiring soon” credentials with end dates between seven and fifteen days out. That range is exactly the action centre’s window, and exactly outside the seven-day tile’s window.

So in every demonstration, the action centre is full of urgent-looking rows and the seven-day tile reads zero or close to it. That is not a coincidence and it is not sabotage. Whoever tuned the seed was making the action centre look populated, checked the action centre, saw it populated, and stopped.

The result is a demo environment where the specific inconsistency between the two components is invisible, because the data was shaped — without anyone deciding to — to sit in the gap between them.

Seed data written to make a screen look good will hide every disagreement between that screen and the ones beside it. If the fixtures are generated, generate them from the domain, not from the predicates: pick end dates spread across the whole range including today, yesterday and tomorrow, and let the screens disagree in front of you. A boundary bug is only visible when there is a row sitting on the boundary.

That is the habit worth taking away. When you write test or demo data, deliberately include the values that sit exactly on every boundary you can find — the day itself, zero, the maximum, the empty string. Data generated in the comfortable middle of every range will pass everything and prove nothing.

The rules

Write each domain predicate once, name it, and import it. Four copies of a date window is four chances to type the wrong operator, and this codebase used all four.

Decide what happens on the boundary, in writing. For expiry, the answer that matches how people think is that today is expiring, not expired — you are still covered until the end of the day. Whatever you choose, the choice must be visible to the next person, and it must be the same in every query.

Do not read the clock in more than one layer. One place resolves the current business date, in a stated timezone, and everything else receives it. A query that calls the database’s clock directly is a query that has silently chosen a timezone.

The honest limit: the timezone column is still not read. Fixing the operators took an afternoon. Making expiry boundaries site-local means deciding what a plain date means for an organisation that operates in two countries, and that is a product question I do not yet have an answer to.