Build an Explainable Lineage Report From Imperfect Evidence

Flow

An operational build sequence

1. 1Bound the query⌄

   Choose an authorized identifier, scope, time window, and result limit.

2. 2Normalize evidence⌄

   Create stable nodes, typed relationships, and unit-aware events.

3. 3Reconcile safely⌄

   Compare arithmetic with observed snapshots and preserve ambiguity.

4. 4Expose and test⌄

   Publish a strict projection, safe export, warnings, and failure-case coverage.

A practical implementation begins with a narrow question. Pick one product or package identifier, one authorized scope, and one bounded time window. Do not start with “show everything.” A bounded query makes the evidence reviewable and gives you a way to test behavior with a small fixture.

Define the source contract before writing the report. List the surfaces that can contribute evidence: inventory snapshots, package records, movement events, received lines, sales, returns, and product metadata. For each surface, record the identifier fields, the observation time, the effective time if one exists, the quantity and unit fields, and the conditions under which the source is considered covered. Preserve the source surface on each event or cost record. When two surfaces describe the same receipt, document which one is canonical and how duplicates are detected.

Next define the node identity. Prefer a physical-lot identifier when one exists, but retain package, inventory, product, and SKU references as separate fields. Use a deterministic node key that includes the scope and a normalized identifier. For records without a physical-lot value, use a distinct package or inventory identity; never attach them to the nearest known lot merely because the names look similar.

Build relationships from explicit parent and child evidence. Normalize case for comparisons, reject self-links, and retain the relationship type and occurrence time. From the relationship set, compute roots and depths. Guard the traversal against cycles. If a cycle or incomplete ancestry prevents a reliable depth, return a null depth and a warning rather than looping or assigning a misleading number.

Create the event ledger separately. Map source event names into a small typed vocabulary such as receive, create, sale, return, adjust, combine, and convert. Exclude void events from rollups while retaining enough metadata to explain why they were ignored. Group quantities by normalized unit. An adjustment with both before and after values can contribute a delta; an incomplete adjustment should be excluded from arithmetic and called out in the notes.

Treat costs conservatively. Preserve the source unit cost, total cost, currency, source surface, and source time on each package or event. A complete, single-currency, non-transformed result might support an exact summary. A split, combine, conversion, truncated result, missing currency, or incomplete package set should downgrade the summary to unavailable or ambiguous. The report should say why. Never turn a source decimal into a made-up cents total simply because a dashboard wants one.

Reconcile against an observed snapshot. Compare received plus created plus adjustments minus sold, and keep returned quantities as a separate interpretation unless the source explicitly says they were restocked. Compare the calculated result with observed on-hand quantities by unit. Report matched, ambiguous, or insufficient evidence along with confidence and flags. Include coverage rows so a reader can distinguish zero from “no observation.”

Put strict validation at the route boundary. Require an exact identifier, pair start and end dates, reject reversed or oversized ranges, bound the limit, and reject unknown query keys. Derive gateway destinations and credentials on the server. Apply the correct read permission and disable raw payload access. Use no-store behavior if the result contains sensitive operational evidence.

Test the failure cases first: missing identifiers, nullable lot fields, cycles, mixed units, returns, incomplete adjustments, truncated events, uncovered stores, ambiguous costs, formula-like CSV values, leading-zero IDs, and query injection attempts. Finish with a stable contract test that proves JSON and CSV use the same bounded input. The useful operational measure is not just response time. Track the percentage of reports that reconcile, the share carrying warnings, the frequency of truncation, and how often an analyst can explain a mismatch without opening raw source payloads.