Traceability sounds like "store where things came from." In agriculture, it's harder: coffee from 800 smallholders is combined at a cooperative, split across exporters, roasted, blended, and packed. The data model has to survive every one of those steps.
Chain-of-custody models
Pick your model before your schema:
| Model | Mixing allowed? | Traceability |
|---|---|---|
| Identity preserved | No | Product traces to a single source |
| Segregated | Only among compliant sources | Product traces to a known set of compliant sources |
| Mass balance | Yes, with volume accounting | Volumes balance, but physical product may not trace |
Regulations like the EUDR require tracing products to the plots of land where they were produced, which pushes toward segregation and away from classic mass balance.
Event-based design
Model events, not just current state:
CREATE TABLE lots ( id uuid PRIMARY KEY, commodity text NOT NULL, quantity_kg numeric NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE lot_events ( id uuid PRIMARY KEY, type text NOT NULL CHECK (type IN ('harvest','aggregate','split','transform','transfer')), occurred_at timestamptz NOT NULL, actor_id uuid NOT NULL ); -- Inputs and outputs of each event CREATE TABLE lot_event_inputs ( event_id uuid REFERENCES lot_events(id), lot_id uuid REFERENCES lots(id), quantity_kg numeric NOT NULL ); CREATE TABLE lot_event_outputs ( event_id uuid REFERENCES lot_events(id), lot_id uuid REFERENCES lots(id), quantity_kg numeric NOT NULL ); -- Harvest events link plots to lots CREATE TABLE harvest_plots ( event_id uuid REFERENCES lot_events(id), plot_id uuid NOT NULL, quantity_kg numeric );
Tracing back with a recursive query
"Which plots feed this shipment lot?"
WITH RECURSIVE ancestry AS ( SELECT $1::uuid AS lot_id UNION SELECT i.lot_id FROM ancestry a JOIN lot_event_outputs o ON o.lot_id = a.lot_id JOIN lot_event_inputs i ON i.event_id = o.event_id ) SELECT DISTINCT hp.plot_id FROM ancestry a JOIN lot_event_outputs o ON o.lot_id = a.lot_id JOIN lot_events e ON e.id = o.event_id AND e.type = 'harvest' JOIN harvest_plots hp ON hp.event_id = e.id;
For most volumes, Postgres handles this fine with indexes on lot_id and event_id.
Quantity reconciliation
Every event should balance: inputs ≈ outputs, allowing for defined losses (drying, processing yield). Validate on write. Unbalanced events are either data errors or, worse, untraceable material entering the chain.
Practical lessons
- IDs at the source. Give every harvest lot an ID at collection (QR codes on bags help). Reconstructing later is guesswork.
- Offline capture. Collection points often lack connectivity. See Offline-First React Native Apps.
- Producer deduplication. The same farmer appears with different spellings across cooperatives.
- Immutable events, correctable via new events. Never edit history. Record a correction.