State Is Not History

A chart that disagreed with our own notes

On 23 August we recorded 1,555 sites scanned that day. On 29 August we asked the same database the same question about the same day and it said 1,123.

For 27 August the pair is 2,093 recorded at the time, 1,107 when we asked again.

Nothing was deleted. No records were lost, no job failed, and no cleanup ran. The database answered both questions correctly. The number moved because the question was not what we thought it was.

The mechanism, in three sentences

Our scan results live in a collection keyed by domain. There is one document per website, holding that site's latest result, and a re-scan overwrites the document and moves its date forward.

So a count of documents dated 23 August does not return the sites scanned on 23 August. It returns the sites whose most recent scan is 23 August, which is a different set, and a set that shrinks every day as those domains get scanned again.

That is one sentence of design and it silently rewrote six days of history.

The chart was not wrong. The axis label was.

This distinction is worth sitting with, because it is the reason nobody catches it.

Every value on that chart was a correct answer to the query behind it. The query was correct too. What was wrong was the four words under the axis, which said scanned that day when the data said most recent scan was that day.

On day one those two phrases return identical numbers. They are still close on day two. They diverge steadily after that, and they diverge most for exactly the periods you look at least, which is the past.

There was no error to find. There was a label to read carefully, and reading labels carefully is not a thing anyone does to a chart that looks plausible.

Which rows disappear first, and why that is the worst possible subset

Here is the part that turns an annoying quirk into a real measurement problem.

A row leaves a historical day when its domain gets scanned again. So the rows that vanish fastest are the ones belonging to domains we re-scan most often, and we re-scan most often the domains we are watching most closely.

The best-instrumented things disappear from your history first. The sites checked once and never again sit in the record permanently. The sites under active monitoring evaporate from every past day they ever appeared in.

Generalise that away from scanning and it gets worse. In a customer table keyed by customer, your most engaged customers are the ones whose records are rewritten most often, so a historical cut by last-updated date systematically loses your best accounts. In a product catalogue keyed by SKU, your most actively managed products vanish from the past. In every case the decay is not random. It is inversely proportional to how much attention the thing gets.

Entity store, event log, same database, same syntax

There are two honest ways to store a fact and they answer different questions.

An entity store answers what is true now. One row per thing. The customer, the product, the domain. When something changes you update the row, and the row is always current. This is the right shape for almost everything operational, because operations need the current state and nothing else.

An event log answers what happened then. One row per occurrence, appended, never updated. A scan happened at this time with this result. A price changed on this date. Rows accumulate and the past is immutable because nothing ever writes back over it.

Neither is better. Each is correct at its own job, and most real stacks legitimately need both.

The trap is that they usually live in the same database, are queried in the same language, and look identical in a result grid. Nothing in the syntax of a query tells you which kind of table you just aggregated over time. The shape of the storage is invisible at the point of use, and that is precisely why a trend line gets built on the wrong one. Somebody needed a chart, there was a date column, and a date column is all a chart asks for.

Why silent decay is worse than a wrong number

A wrong number tends to announce itself. It is negative, or it is ten times too large, or it contradicts something everyone knows. Someone queries it within a week.

This failure does none of that. Every point on the chart is plausible. The shape is smooth. The recent end is accurate, which is the end people check. And the distortion runs in one direction only, always understating the past, so the line always appears to trend upward whether or not anything improved.

A chart that is wrong gets caught. A chart that is quietly and consistently flattering does not, because it is telling everyone what they hoped to hear in a format that looks like evidence.

It belongs to the same family as the failures in zero versus missing and the API that retired without telling your dashboard. All three render confidently while meaning something other than what they say, and all three survive because the output looks fine.

The tell

There is one symptom and it is easy to check.

A historical number that moves when you re-run the query. That is it. Last month's total should be the same today as it was last month. If it is not, and nothing was deleted, you are aggregating an entity store over time and the past is being rewritten underneath you.

Most teams never notice because they never re-run an old query. The number goes into a deck, the deck goes into a drive, and nobody asks the database the same question twice. We only caught ours because we happened to have written the earlier figure down, which is an argument for keeping a record of what you reported and when, of the sort we made in Your Vendor Changed 200 Things On Your Site.

How the Privacy Index deals with this, which is not clever

The Website Privacy Index runs on the same collection, with the same keyed-by-domain design, and the same property of overwriting a domain's previous result when it is scanned again.

It sidesteps the problem by not plotting anything over time. It is a fixed snapshot: 18,613 sites scanned, 18,524 scored, with the crawl window stated on the page. Every figure describes one pull of the data, and when we refresh it, all the figures move together and the stated window moves with them. There is no trend line, because we cannot honestly draw one from this data.

We want to be clear that this is a limitation handled honestly rather than a smart design. If we could plot the change in consent adoption over eighteen months, we would. We cannot, because the store we have does not remember what it used to say, and pretending otherwise would produce exactly the chart this article is about.

The window on that page was itself a hand-set string until this week, which meant it stopped describing the data the moment the crawl ran on past it. It is now derived from the records. Same failure family, one level up: a caption that was true when written and quietly stopped being true, with nothing in the system to notice.

Two checks worth running

Neither needs a project and both give you an answer today.

1. Re-run a number you reported last month, and compare it to what you actually said at the time. Same query, same period, no adjustments. If the two disagree and nothing was deleted, you have this problem and you now know its size. If you cannot find what you reported at the time, that is the more urgent finding.
2. For each source feeding a trend line, ask one question: does this store one row per thing, or one row per event? If it is one row per thing, the date column is the date of the last write, not the date of the occurrence, and any time series built on it is measuring recency of update rather than activity. You do not need to fix that today. You do need to know which of your charts are standing on it.

The second check usually takes longer than people expect, not because it is hard but because nobody currently knows the answer for half the sources. That is itself the finding.

Who owns this

It is tempting to file this under database behaviour and move on. It is not a database problem. A keyed store that overwrites on update is doing exactly what it was designed to do, and doing it correctly, and no product is at fault here.

The defect was ours, and it was a labelling defect. Someone wrote a query, someone wrote an axis, and the two were describing different things in language close enough that nobody noticed for months.

Which raises the question the whole piece is really about: for each chart your organisation makes decisions on, who has actually read the query behind it and confirmed the label matches? If the honest answer is nobody since it was built, that is not unusual. It is just not a reason to trust the line.