The symptom
Every stage of production logs its own operations — receiving, prep, processing, shipping — in its own log. All the data technically exists. And yet a simple query kept being impossible to answer: what upstream inputs actually went into this specific unit of output?
That query matters most exactly when it's hardest to run: during a quality complaint. Complaint on a finished unit → need to trace backward to inputs. Problem found in an input → need to trace forward to every output it touched. Neither direction is answerable if the logs are just independent tables with no foreign key between them.
Root cause: no lineage pointer between stages
The logs aren't missing data, they're missing a relationship. Each stage writes its own record, but nothing links stage N's output row to the stage N−1 input row(s) it was built from. It's not a normalization problem, it's a missing edge in what should be a directed graph.
The fix is almost embarrassingly simple in the abstract: every new batch record carries a reference to its parent batch (or batches — this is many-to-one in both directions depending on where you look). We built this out using Log Sheet's configurable production logs, and walked through it with cheesemaking as a concrete worked example, since it has a clean multi-stage pipeline: receiving → mix prep → cooking → drying/ripening → optional smoking → packaging.
Walking the graph on a real complaint
Say a complaint comes in on one packaged batch. Query the packaging log for that batch ID, and it returns two parent cooking-batch IDs — this package was made from cheese out of two separate cooking runs.
One hop up: query the cooking log filtered by mix ID, and it returns five cooking batches sharing that same mix — only two of which are relevant to this specific complaint. If the complaint were about the mix itself instead, all five would be in scope.
One more hop: the mix's own parent records are two milk batches, resolvable through the mix-prep log, with their original receiving records one hop further back.
Four lookups, zero phone calls, and the full lineage graph is reconstructed: two milk batches → one mix → five cooking batches (two of which matter here) → one packaged batch. This isn't a special traceability feature bolted on top — it's just what you get for free once every write includes a parent reference.
You don't have to walk it row by row either — the same graph renders as a batch tree for a visual pass.
The failure mode running the query in reverse
Flip the direction: QA flags an issue in a milk batch, not a finished good. Now it's not a single-row lookup anymore — that milk batch feeds into a mix, and that mix could have fed multiple cooking runs, each of which could have fed multiple packaging batches. It's a full downstream traversal, not a point lookup.
And here's the part that actually matters for data quality: this traversal is only as reliable as the weakest link that recorded (or didn't record) its parent reference. Miss it once at any stage, and you've silently turned a deterministic join into a fuzzy match on timestamps and equipment IDs — which is not the same guarantee at all.
Takeaway
Cheese is just the worked example — swap in whatever your actual production stages are. The schema-level takeaway doesn't change: every batch record needs a parent reference, written at the time of creation, not reconstructed later from adjacent metadata. Get that one field right and traceability isn't a project, it's a query.
Top comments (0)