The status column said active, and nothing had looked at it since the day it was written
Every credential row in this system carries two facts about whether it is still valid. It has an end date, and it has a status label with values for active, expiring soon, expired and revoked.
One of those two changes meaning by itself. The end date was true yesterday, is true today and will still be true in a year, and the answer it gives to “is this current” changes every midnight without anybody touching the row. The label does not. The label is whatever was written when the row was last saved.
There is no job that walks the table and moves rows from active to expiring to expired. The schema comments imply one. The code does not contain one. So the label is a snapshot of an opinion held on the day of the last write, sitting in a column whose name promises it is current.
The tell is the defensive expression
You can find this class of bug without reading a single migration, because the query layer confesses it. Here is the expression every read path uses to decide whether a credential has expired:
status = 'expired'
OR (end_date IS NOT NULL AND end_date < CURRENT_DATE AND status <> 'revoked')
That is somebody who does not trust the label. A row counts as expired if it says expired, or if the date has passed and it has not been revoked. The label is consulted, then second-guessed against the date, then a specific label value is allowed to override the date.
Read that as a spec and it is actually well thought out. Revoked beating the date is correct — a withdrawn credential is not “expired”, it is void, and the distinction matters when someone asks why a person is non-compliant. The expression encodes a real precedence rule.
But the existence of the expression is the finding. When you see a query that consults a stored
state and then re-derives it anyway, the stored state is decoration. Nobody writes that expression
in a system where the column is maintained. They write status = 'expired' and go home.
Three copies, no import
The same expression appears three times across two files, written out rather than imported. Two of those are in the dashboard, one in the action centre, and they agree — today. They agree because they were copied from each other, which is not the same thing as being consistent.
Change the precedence rule and you have to find all three. Find two of them, and the dashboard and the panel underneath it disagree about who is expired, on the same screen, with no indication which is right.
I have written elsewhere about date windows in this codebase that were copied and then drifted. This is the same defect earlier in its life: the copies still match, and the only reason is that nobody has edited them yet. A copied predicate that has not drifted is not correct. It is unexploded.
The decision that was skipped
There are two defensible designs here and this system is doing neither of them.
Derive it entirely. Drop the label, compute validity from the end date and a revocation flag, and let every query ask the same expression through a view or a shared helper. The state is always right, there is nothing to maintain, and the cost is that “expiring soon” has to be computed on read, which for this data volume is nothing.
Maintain it properly. Keep the label because it is useful for indexing and for filtering by status without evaluating date arithmetic across millions of rows, and run a job every night that moves rows between states. Then the column is true, subject to a stated freshness of one day, and that freshness gets written down where users can see it.
What was built is the third option nobody chooses on purpose: keep the column, do not maintain it, and paper over it in the query layer. That gives you the storage cost of the denormalisation, the correctness of the derivation, and a column that lies to anyone who queries the table directly — which includes every report written outside the application, every export, and every person who opens a database client to answer a question quickly.
If a stored state is not maintained, delete it. A column that is only correct when read through one specific expression is a trap for everyone who does not know the expression exists.
The requirement check had a bigger hole
While reading the same area I found something worse, and it is worth including because it is the kind of gap that a status-label bug distracts you from.
The system works out who is missing a required credential by checking, for each requirement, that no matching row exists in a valid state. The predicate for “valid” is: the status is active or expiring soon, and the end date is either absent or in the future.
It does not look at the verification state.
A credential in this system has a separate workflow — submitted, then verified or rejected by a manager. That workflow is the entire point of having evidence rather than a tick box. And the compliance check ignores it, so an unverified submission satisfies the requirement, and a submission that a manager has explicitly rejected also satisfies it, as long as the lifecycle status is still active.
Which means a person can clear their own compliance gap by uploading any file. The gap closes on the dashboard immediately, before anyone has looked at what was uploaded, and stays closed after someone looks at it and says no.
A requirement is satisfied by verified evidence, not by the existence of a row. If your model has a verification step, every query that asks “is this requirement met” must include the verification state, and the safest way to guarantee that is to make the satisfying condition a single named expression that the verification state is part of.
There is a third related gap in the same query. Requirements come from a person’s job designation, and designations in this schema carry effective dates — someone can move from one role to another. The requirement query ignores those dates and treats every designation row as currently in force. A person who changed role two years ago is still measured against the credentials of the role they left.
What ties the three together
All three are the same shape. The schema models a distinction — current versus stale label, verified versus submitted, in force versus historical designation — and the query that matters does not use it.
That is the failure mode of a schema written ahead of its queries. The model is richer than the code, every column looks like it is doing something, and the only way to find out which distinctions are actually enforced is to read the predicates. A column that no query filters on is not a feature. It is a plan.
The rules
Derived state gets derived, or it gets a job. Never both halves of neither.
Write the “is this valid” expression once, name it after the business question, and make every caller use it — including the reports, including the exports, including the ad-hoc query somebody runs during an audit.
When a schema models a workflow, check that the workflow’s states appear in the predicates that consume it. Search for the column name across the query layer. If the only hits are the writes, the workflow is theatre.