Derived Counters Can Drift: Build a Repair Path

A derived counter is convenient. Instead of running an expensive COUNT() for every query, we store a ready value such as usage_count and read it in O(1).

The problem begins when that number is gradually treated as the source of truth.

In my case, the media usage counter could be fully rebuilt from canonical usage relationships. That means usage_count is only a derived projection, and projections can drift.

If usage_count disappears, the system can rebuild it by counting rows in media_usages.

If the usage relationships disappear and only the number remains, there is no way to reconstruct which records actually use the file.

That asymmetry makes the source of truth clear.

The fact that a counter is not the source of truth does not mean it should be removed.

It may be useful for lists, filters, quick "is this file in use?" checks, sorting, or admin statistics.

But its role should be explicit: it is an optimized representation of canonical data.

A derived value can diverge from reality for many reasons:

• an event or listener did not run;
• legacy code changed the relationship directly and skipped the counter update;
• a migration or one-off script inserted canonical rows outside the normal flow;
• the main transaction completed but an asynchronous counter update failed;
• a manual database correction changed only one side;
• new counter logic was introduced after old data already existed.

This flow is useful for keeping the counter close to real time.

But it cannot be the only correctness mechanism. If any step is skipped, the derived value remains wrong until something explicitly rebuilds it.

Increment/decrement works only when every canonical relationship change is guaranteed to produce exactly one matching counter change.

One missed increment creates drift. One duplicated event can create drift in the opposite direction.

For an important derived counter, I prefer "recalculate the current value" over "trust the complete history of previous increments."

This recalculation is idempotent: it can run once, twice, or ten times, and with unchanged canonical data the result is the same.

That is an important property for a repair operation.

I prefer repair tools that do more than silently fix data. They should also show what was wrong.

That makes it easier to see whether the problem was one legacy record, a systematic bug, or the result of a particular migration.

A dry run is especially useful the first time a repair path is introduced.

It lets us measure drift without changing production data and investigate suspicious records before writing anything.

If drift is already suspected, the old counter cannot be trusted even as a starting point.

A repair should be a function of canonical state, not an adjustment to accumulated error.

For thousands of rows, one long transaction may create unnecessary locks and make the repair more expensive than needed.

If each counter can be independently rebuilt from canonical relationships, records can be repaired in smaller batches.

Idempotency makes it safe to rerun the command if it stops halfway through.

If production is actively creating and deleting usage relationships during repair, state may change between COUNT() and UPDATE.

For a non-critical cached counter, that may be acceptable, especially if the normal runtime flow will reconcile the next change.

If stronger guarantees are required, locking, a maintenance window, or a more atomic SQL rebuild should be considered explicitly.

Another practical lesson is that adding a usage_count column with a default of 0 and wiring new events is not enough.

The new code may correctly maintain future changes while all historical records remain wrong.

For a derived field, I think about three things together: schema, runtime maintenance, and initial backfill.

Once a rebuild mechanism exists, it can be useful before an incident too.

For example, a scheduled dry run can periodically count drifted records and report when the number becomes non-zero.

The repair path then also becomes a way to verify that runtime maintenance is actually working.

• I know exactly which data are canonical.
• The counter can be fully rebuilt without using its previous value.
• The runtime flow maintains it after create, update, and delete.
• A new counter column has a backfill for historical data.
• There is an idempotent repair operation for one record.
• There is a full-data-set command with chunking.
• There is a dry run or at least a drift report.
• Rerunning the repair is safe.
• Concurrency expectations are explicit.

The main shift for me is simple: a counter does not become true just because it is stored in a database column.

If its value is derived from other records, it remains derived. Events and listeners help keep it current, but they do not eliminate drift.

That is why every important derived counter should come with a rebuild formula, an idempotent recalculation operation, and a command that can reconstruct it from canonical data.