EUDR Coffee Traceability: Plots, Lots and GeoJSON
Resumo
Pixel’s announcement explores how coffee systems can model EUDR lot-to-plot traceability using plots as distinct entities, recorded lot composition edges and production periods. It explains why these design choices matter for linking farm geolocation to factory and warehouse movements, and highlights GeoJSON exports and upcoming compliance deadlines.
DEV Community
Modelling EUDR Lot-to-Plot Traceability for Coffee: Plots, Lot Edges and GeoJSON Exports
By Isaac Muthui, CEO, Tamalaki Business Network Ltd. Originally published at factoraerp.com. If you build or integrate systems for coffee processors in Kenya, the EU Deforestation Regulation (EUDR) is a lineage problem. From 30 December 2026, the EU operator that first places coffee on the EU market (usually the importer) must file a due diligence statement that includes the geolocation of every plot the coffee came from, plus the production period and supplier details. Micro and small operators follow on 30 June 2027. The field data (plot points and polygons) is increasingly there. What usually breaks is the join between that data and the lots a factory, mill and warehouse actually move. Four design decisions matter: Plots as first-class entities, not grower attributes. A grower can farm more than one plot. Store plot with its own ID, a foreign key to grower, a geometry, area in hectares and a mapping date. Geometry rules enforced at write time. The guidance summarised by the European Coffee Federation: a GPS point with six decimal places is enough for plots up to 4 ha; plots above 4 ha need a polygon. Data must be exportable as GeoJSON. Lot composition as a recorded edge, not a reconstruction. When deliveries are pooled into a factory lot, or lots are bulked into a grade, write the edges (input, output, weight) at that moment. Lineage becomes a graph walk. Production period on the lot. The statement needs a date or time range of production (for coffee, the harvest season). Derive it from the min and max delivery dates and store it with the lot. A minimal schema CREATE TABLE grower (
grower_id TEXT PRIMARY KEY, -- same ID used in delivery, payment and mapping systems
coop_id TEXT NOT NULL,
name TEXT NOT NULL
);
CREATE TABLE plot (
plot_id TEXT PRIMARY KEY,
grower_id TEXT NOT NULL REFERENCES grower(grower_id),
area_ha NUMERIC(8,3) NOT NULL,
geom_geojson JSONB NOT NULL, -- Point or Polygon, WGS84
mapped_on DATE NOT NULL,
mapped_by TEXT
);
CREATE TABLE delivery (
delivery_id TEXT PRIMARY KEY,
grower_id TEXT NOT NULL REFERENCES grower(grower_id),
plot_id TEXT REFERENCES plot(plot_id), -- NULL = unknown plot: treat as a gap
delivered_on DATE NOT NULL,
kg_cherry NUMERIC(10,2) NOT NULL
);
CREATE TABLE lot (
lot_id TEXT PRIMARY KEY,
stage TEXT NOT NULL, -- 'factory', 'milled', 'grade', 'bulk'
season TEXT,
produced_from DATE,
produced_to DATE
);
-
- Edges: deliveries into factory lots, and lots into downstream lots
CREATE TABLE lot_input (
lot_id TEXT NOT NULL REFERENCES lot(lot_id),
delivery_id TEXT REFERENCES delivery(delivery_id),
input_lot_id TEXT REFERENCES lot(lot_id),
kg NUMERIC(10,2) NOT NULL,
CHECK ((delivery_id IS NULL) <> (input_lot_id IS NULL))
);
Validating plot geometry def validate_plot(geom: dict, area_ha: float) -> list[str]:
errs = []
t = geom.get("type")
def six_dp(x): return len(repr(float(x)).split(".")[-1]) >= 6
if area_ha > 4 and t != "Polygon":
errs.append("plot > 4 ha must be a Polygon")
if t == "Point":
lon, lat = geom["coordinates"]
if not (six_dp(lon) and six_dp(lat)):
errs.append("point needs 6 decimal places")
elif t == "Polygon":
ring = geom["coordinates"][0]
if len(ring) < 4 or ring[0] != ring[-1]:
errs.append("polygon ring must be closed with >= 4 positions")
else:
errs.append(f"unsupported geometry {t}")
return errs
Add overlap checks between plots (in PostGIS, ST_Overlaps on a geometry column) and flag implausible areas against the recorded area_ha. Walking the lineage Given a bag's grade lot, a recursive query collects every delivery, and so every plot, upstream: WITH RECURSIVE up(lot_id) AS (
SELECT 'GRADE-AB-2026-0141'
UNION
SELECT li.input_lot_id FROM lot_input li JOIN up ON li.lot_id = up.lot_id
...

Powered by Readybot.io
- Edges: deliveries into factory lots, and lots into downstream lots