Designing a File-Tracking System That Can't Lose a File

A file-tracking system has exactly one job: know where every file is, at all times, without ambiguity. That sounds simple until you build one and discover the actual failure mode — files silently splitting into duplicate records as they move between departments, because "the file" and "this transfer record" get conflated in the data model.

The bug pattern

In an earlier version of a system I worked on, each department transfer created what was effectively a new record instead of updating the existing file's status. From the database's perspective, a file that moved from Department A to Department B to Department C wasn't one file with a history — it looked like three separate, disconnected entries. Status displays became unreliable, and staff couldn't trust what the system told them.

The fix: separate identity from history

The fix was to enforce a clear separation: the file itself is one row, with a current_department_id field showing where it is right now. Every transfer creates a new row in a separate file_tracking_histories table, linked back to the file's permanent ID. The file's identity never changes; its history accumulates.

This is a pattern worth recognizing beyond file tracking — anywhere you have an entity with an evolving status (an order, a support ticket, an application under review), the same principle applies: one row for "what this thing is," a related table for "everything that's happened to it."

Why this mattered for a live system

This wasn't a greenfield rebuild — it was a fix applied to a system already in active use, which meant the change had to be surgical: add the new field, migrate existing data carefully, and fix the transfer/receive logic without breaking anything staff were actively relying on. Minimal, well-tested changes beat a full rewrite when real people depend on the system working every day.