The repository had a script to fill in the numbers the product was meant to compute

26 August 2026

The headline compliance figure on this product’s dashboard does not come from the credential tables. It comes from an aggregate table of periodic snapshots, and the query that reads it takes the most recent row for the requested scope.

Nothing in the application ever writes to that table. The schema comment says the aggregates are refreshed by a job. There is no job. The only writers are the demo seeding script and a second script whose purpose is to put rows there when the seeding script has not.

So a tenant can have complete staff records, correct credentials, accurate expiry dates and a working requirement model, and the dashboard will show a dash where the compliance percentage should be, because the one table the headline reads from is empty.

The two scripts

There are two, and they are both good pieces of work, which is what makes them worth writing about.

The first is an idempotent backfill. It checks whether the tenant has snapshots and, if not, inserts a current one plus several historical ones. It generates its identifiers deterministically from a seed, so re-running it produces the same rows and the inserts conflict away to nothing. It runs in a transaction and rolls back on error.

The second is a validator. It runs nine named checks, prints them as structured output, and exits with a failure code if any check fails — the correct shape for something you want to run in a pipeline. It checks that the organisation exists, that staff and sites exist, that there are snapshots, that the comparison against the prior period is not null, and that there are scheduled sessions in the windows the dashboard asks about.

Both are careful. Both are documented. Both are pointed at the wrong problem.

The thresholds give the game away

The backfill’s conditions are the interesting part, because they are not domain conditions. They are the dashboard’s own predicates, copied.

It inserts snapshots when the tenant has fewer than two. Two, not one and not zero. Two, because the change-versus-prior tile needs two rows to compute a difference, and with one row the tile renders empty and the trend chart is a single dot.

It inserts a training session at a specific number of days out when there are none inside the fourteen-day window — because there is a tile counting sessions in the next fourteen days. It inserts another at a different offset when there are fewer than two inside an eight-week window, because there is a chart that looks wrong with a single bar.

The three counting queries in the backfill are copied from the dashboard code character for character, including the interval expressions. That is deliberate and, given the goal, correct: the fix is written against the reader’s exact predicates so that it cannot miss.

But look at what that means. The script’s specification is “make these tiles non-empty”, not “these figures are true”. It is a compensating control for a missing writer, and its correctness criterion is the appearance of the screen.

How this happens

Nobody decides to build this. It accumulates in a specific and very ordinary order.

The schema is designed first, and it is designed well — the aggregate table exists because computing a compliance percentage across every credential and every requirement on every page load is not something you want to do, and pre-aggregating it is the right call. The comment saying “refresh via job” is a note to self.

Then the API is built, and the dashboard is built, and both read the aggregate table because that is what the schema says to do. The job is not on anyone’s list, because it is not a screen and nobody asks for it in a demonstration.

Then the demo comes up blank. Somebody writes a seed that populates the table. Then a tenant that was not fully seeded comes up blank, and somebody writes the backfill. Then somebody wants to know beforehand whether a tenant will come up blank, and writes the validator.

Three artefacts, each a reasonable response to the immediate problem, and the actual missing component is now further away than it was at the start — because the symptom is handled, and a handled symptom does not generate pressure.

A repository that contains a script whose job is to make a screen look populated is telling you about a missing writer. That is the finding, and it is available to anyone who reads the file names without opening them.

The validator checks that snapshots exist. It does not check that they are consistent with the operational tables — that the stored compliance percentage bears any relationship to the credentials in the same tenant.

That is the check that would have mattered, and it is the check that a compensating validator will never contain, because its author’s problem was emptiness rather than wrongness. A tenant with snapshots saying eighty-four per cent and credential data implying thirty passes every one of the nine checks.

It also only examines the organisation-wide scope. A tenant with no site-level snapshots passes validation, and then renders an empty bar chart and a blank “worst-performing site” field, which is exactly the shape of the failure the validator was written to prevent.

When you write a data validator, assert the relationship between the derived value and its source. Existence checks are cheap and they catch the failure you already know about. The failures you do not know about are all disagreements, and only a comparison finds those.

The comparison that assumes even spacing

One more, because it comes from the same missing job. The change-versus-prior figure is computed by taking the two most recent snapshots for the scope and subtracting. There is no check that the two periods are comparable — no alignment of their start and end dates, no requirement that they are the same length or adjacent.

With a real scheduled job, snapshots arrive at a fixed cadence and taking the previous one is equivalent to taking last month’s. Without one, they arrive whenever a script ran, at irregular intervals, and “versus prior” means “versus whenever the last one happened to be written”.

The trend series has a related quiet limit: the number of points fetched is capped, so a long date range silently returns a truncated series rather than a sparser one. Nothing on the screen says the line has been cut short.

A period comparison must compare periods, not rows. If the query cannot state which two periods it is comparing, the delta it produces is not a trend, it is a subtraction.

The rules

When a schema comment says a table is refreshed by a job, the job is a deliverable with a name and a place in the plan. Write it down at the moment you write the comment, because a comment is not a ticket and nobody schedules a comment.

Treat a compensating script as evidence, not as a solution. Every one of them encodes an assumption about a reader, and the assumptions are usually right and always undocumented elsewhere.

Validators assert relationships. If yours only asserts existence, it will pass every wrong number you ever store.

The limit I will admit: the job still does not exist, and building it means answering a question the product has not settled — what period a compliance snapshot covers, and whether the figure is a point-in-time measurement or an average over a period. Those two produce different numbers and the current data cannot tell you which one it is.