The schema that lives only in the running database

Everything this service does is in version control. The code is, anyway. The database it runs on is 32 tables, and 15 of them have no definition anywhere in the repository — no CREATE TABLE, no schema file, nothing. If the server were lost, the code would come back exactly and the shape of the data would have to be reconstructed from what the code happens to ask for.

Those figures come from comparing the live database against 736 files and 5,671,710 characters of repository. They are not an estimate.

How half a schema goes missing

It is not carelessness, and that is what makes it worth writing down. Each individual step was reasonable.

Tables created before the current codebase existed were never written down, because at the time they were simply there. Tables added since arrived through migration scripts, and those scripts are in the repository — that is where the 17 definitions come from. Columns added later were added with a one-line ALTER TABLE typed at a prompt, because typing it was faster than writing a script for a change that took a second.

Indexes are the worst case. An index is added when something is slow, at the moment it is slow, and it fixes the problem immediately. There is no artefact left behind, nothing to review, nothing that fails if you forget. Eleven of the thirty-two secondary indexes here exist for exactly that reason and are recorded nowhere.

The table that holds the counters themselves, 166,438 rows of them, is among the fifteen with no definition in the repository.

What actually breaks

Not much, until one specific moment: when the database is restored from a dump.

A dump carries the structure, so a straight restore is fine. The danger is any path that rebuilds a table rather than restoring it — moving to another machine, importing selected tables, recreating one from a script. The data comes back and the indexes quietly do not. Nothing fails. Queries return the right answers. They just take longer, and the cause is invisible, because the only record of what the index was is the machine that no longer has it.

That is the failure mode worth naming: a missing index does not produce an error, it produces a slower correct answer. There is no test for it, because the tests pass.

What we do about it in the meantime

The honest position is that this is not fixed. The schema is not in the repository today. What exists is smaller than a fix and better than nothing:

A dump before anything destructive, kept off the web root. A restore that has actually been performed at least once, rather than assumed. And a short list, checked after every restore, of the indexes that are known to exist only in the running database — because the machine can be asked what indexes it has, and the answer can be compared against the last time it was asked.

The general version applies to more than databases. If part of your system is only in the running system, you do not have a copy of it, you have a hostage. The question worth asking about any piece of infrastructure is not "is it backed up" but "if this machine went away, what could not be rebuilt from the repository". Here the answer is fifteen tables and eleven indexes, and it took an afternoon to find out.