The check that looked at one column

We run an automated integrity check across one product's file storage. It compares what the database says exists against what is actually on disk, and reports anything missing.

On the day in question it reported zero orphaned files. It ran twice and said the same thing both times.

Nine files referenced by that database were missing. They had been missing for days, and users had been getting 404s for all of them.

The check was correct and useless

The check was not broken. It did exactly what it had been written to do, which was to look at one column — the one that names generated image files — and confirm every value in it resolved to something on disk. Every value did.

The problem is that the schema names files in nine different columns. Photographs have two, one for the original and one for a resized version. Receipts have one. Uploaded documents have one. Tickets, avatars, and two separate arrays of image names attached to other records. The check had been written when the product had one kind of file, and the product had since grown eight more, and nobody had gone back to it.

This is a specific and very common failure: not a check that gives wrong answers, but a check whose scope silently stopped matching the system. It kept passing, so nobody looked at it. A check that always passes and a check that is not running are hard to tell apart from the outside.

Two details that make this class of check awkward

Writing the corrected version turned up two things worth knowing before you write your own.

Some columns hold arrays. Two of the nine were lists of filenames on a single record, not one filename. A naive query reads them as a single opaque value and finds nothing on disk that matches, so it reports every one of them as missing. The first corrected version produced a spectacular list of hundreds of missing files, all of which were fine. Arrays have to be expanded into one row per element before you compare.

Files do not all live in the same directory. Receipts, avatars and tickets were each in their own subdirectory. Resolving every name against the storage root alone reports all of them missing. You have to know, per column, where that kind of file is expected to be — which means the check needs to encode a small map of column to location, and that map is another thing that can fall out of date.

Both mistakes have the same shape: the check invents failures, which is arguably worse than missing real ones, because a check that cries wolf gets muted.

Where the nine files actually were

They existed. They were in the storage belonging to a different environment, because a person had uploaded them there — and the two environments shared a database while keeping separate file storage. The row was visible everywhere. The file was not.

Recovery was a plain copy from one store to the other. Nothing was lost, and the fix was additive, with nothing deleted.

The guard nobody called

Here is the detail that has stayed with us. The codebase already contained a function whose entire job was to assert that the process was writing to the live file store, precisely to prevent this.

It had six callers. All six were maintenance scripts. No upload path called it.

So the protection existed, was correct, was tested, and did nothing, because it was never on the path where the damage happened. A guard that is not called is not a guard. It is a comment with a function signature.

We now treat "where is this invariant enforced?" as a different question from "is this invariant implemented?" — and the answer to the first has to be a list of call sites on the paths that actually mutate things.

What we changed

• The check enumerates every column that names a file, and the list is asserted against the schema, so adding a tenth column that stores filenames fails the check until the check is updated.
• Array columns are expanded before comparison.
• Each column declares where its files live.
• The result records the total number of references examined, not just the number missing. 0 missing means nothing on its own; 0 missing out of 870 examined is a statement about coverage.

That last one is the change we would recommend to anyone. Report the denominator. A check that says everything is fine should also tell you how much it looked at, because the day it silently starts looking at nothing, that number is the only thing that will give it away.