Trace a Repacked Commodity Lot with a Graph, Not a Single Parent Column

A lot is split into two packing runs. One run later includes material from another incoming lot. A database with one parent_lot_id now has an awkward choice: discard one origin or put several identifiers in a text field.

Neither choice gives a reliable lineage query. Model the transformation explicitly, then derive the relationships needed for tracing.

This is a hypothetical software design. All lot identifiers and examples are synthetic and do not describe Zenith Eclipse operations or product availability.

Store transformations before drawing edges

Use separate records for lots, transformation events, event inputs and event outputs. An event can consume several lots and create several new lots. A split, blend and repacking operation need different event types even when they share this structure.

Each input or output line should carry its own quantity, unit and evidence reference. Scope identifiers to the tenant and relevant issuer. A printed supplier lot code need not be globally unique.

Create fresh output-lot identities when the physical traceability boundary changes. If rework returns material to a process, represent the new output as a new lot. Reusing the original node can create a false cycle.

For tracing, derive a lot_edge view containing tenant, parent and child identities for approved input-to-output relationships. If the event cannot establish which input fed which output, connecting every input to every output is a conservative tracing assumption. Label that assumption; it does not establish precise composition.

Query the reachable set

Suppose synthetic lot A contributes to B and C, and B contributes to D. Starting at A should return A, B, C and D once each.

This PostgreSQL query assumes text lot IDs and a tenant-scoped edge view:

WITH RECURSIVE reached(lot_id) AS (
    SELECT lot_id
    FROM lot
    WHERE tenant_id = $1 AND lot_id = $2
  UNION
    SELECT e.child_lot_id
    FROM lot_edge e
    JOIN reached r ON e.parent_lot_id = r.lot_id
    WHERE e.tenant_id = $1
)
SELECT lot_id
FROM reached
ORDER BY lot_id;

PostgreSQL's recursive-query documentation explains how UNION removes duplicate rows during recursive evaluation. Because this query carries only lot identity, it also terminates on a finite graph containing an accidental cycle.

That protection changes if depth or a growing path becomes part of the row. Repeated lot identities can then have different rows. Use explicit cycle detection for path reporting, rather than assuming UNION will still stop the traversal.

Index the edge lookup by tenant and parent. Enforce tenant-scoped foreign keys from event lines to lots, and authorize the starting lot before revealing related records.

Do not turn reachability into mass balance

A reached lot is potentially connected to the starting material. It is not a calculated percentage of that material.

If D is reachable through two paths, adding D's full mass twice would overstate the affected quantity. Conversely, counting D once does not explain its composition. Keep the reachability result separate from event-level reconciliation and any validated allocation model.

For each transformation, compare recorded inputs and outputs on compatible measurement bases. Record losses, additions and unresolved differences explicitly. Packaging mass, moisture basis and measurement timing can make an apparent discrepancy more complicated than subtraction.

The event schema should also preserve product form and packing context. Zenith Eclipse's whole and split pea specifications distinguish physical forms and their acceptance criteria, while its refined sunflower oil requirements describe batch analysis and bulk packing options. These are useful domain examples for designing event metadata; a graph connection alone establishes neither conformity to those specifications nor the proportion of material in an output.

Preserve corrections and incomplete boundaries

A missing parent record means lineage is incomplete, not that the lot has no upstream origin. Record unresolved source references without inventing a link to the nearest matching name.

When evidence changes, retain the superseded relationship and its correction reason. Run a trace against an identifiable approved graph version or database snapshot so the result can be reproduced.

Test a split, a merge, two paths reaching the same output, a cycle, an unresolved input and a same-named lot in another tenant. The application should distinguish a complete traversal of recorded edges from complete knowledge of physical history.

This graph is decision support. It does not certify product safety or automatically decide release, withdrawal or customer action. Its useful promise is precise: show which recorded lots are connected, explain the evidence, and expose where the chain remains incomplete.

---

About the publisher: Zenith Eclipse Co works across agricultural commodities, international logistics and machinery sourcing.