Technical brief
How MilletLedger holds a grain lot's record together
Written for food-safety leads and engineers evaluating the system: what is stored, who can read it, and what comes out at audit time.
Identity model
A traceability lot is a batch row keyed by a human-readable code (STATE-GRAIN-SEQ-CROPYEAR). The code is unique and immutable — a database trigger rejects any update to it, so downstream references, printed bags, and QR codes cannot drift from the record.
Provenance graph
Farm → plot → batch → event, with documents attachable at farm, batch, or event level. Plots carry acreage, county geography, soil type, irrigation source, and coordinates; farms carry FSA farm and tract numbers plus organic certificate number and expiry. No SSN, EIN, or other national identifiers are stored anywhere.
Event ledger
Every field, post-harvest, lab, and logistics step is an append-oriented event row with a schema slug and a JSON payload validated against a single shared schema catalog. One generic renderer drives all sixteen forms, so a new key data element is a one-line change rather than a UI migration.
Access control
Row-level security is enabled on every table with no default-permissive policies. Roles live in a dedicated user_roles table read only through security-definer helper functions, which removes the privilege-escalation path that role columns on a profile table create. Farm-side users resolve to their own farm IDs; staff roles read across farms; buyers and viewers are read-only in both UI and policy.
Consumer-facing trace
The public lot page reads through a single security-definer function that projects a curated view: grain, variety, crop year, field facts, event timeline, and documents explicitly flagged public. Draft lots are excluded and no contact details are ever returned, so the endpoint can serve anonymous traffic without a PII surface.
Units
Storage is SI — kilograms, hectares, Celsius. Display and input are US customary — pounds, bushels at grain-specific test weights, acres, Fahrenheit. All conversion runs through one module, so no conversion factor is ever inlined in a component.
Audit outputs
Two paths: a sortable FSMA 204 CSV export covering every lot and its CTE coverage, and a per-lot printable dossier with lot facts, origin, CTE-by-CTE KDE tables, the full ledger, and attached records. Both are generated from the same server-side dossier query, so the spreadsheet and the report cannot disagree.
FSMA 204 coverage
Critical Tracking Events the system captures, with the Key Data Elements required before a lot counts as complete.
| Critical Tracking Event | What it records | Required KDEs |
|---|---|---|
| Harvesting | Field of origin, harvest date, quantity, and the harvester for the traceability lot. | Harvest date, Quantity harvested (lb) |
| Cooling / conditioning | Post-harvest drying or aeration that stabilizes the lot before storage. | Final moisture (%) |
| Initial packing | Traceability lot code assignment, pack date, package count, and the packing location. | Pack date, Number of packages |
| Transformation | Cleaning, milling, or blending that creates a new lot from input lots. | Process date, Output quantity (lb) |
| Shipping | Ship date, quantity, carrier, and the immediate subsequent recipient of the lot. | Ship date, Ship-to location |
Stack
React 19 with TanStack Start on an edge runtime; Postgres with row-level security for storage, auth, and private file storage. All reads and writes go through typed server functions — no direct database access from route or component code. Signed, short-lived URLs gate every private document download.
On the roadmap
Merkle-hashed event roots anchored to a public chain, giving an independent timestamp proof for each lot's ledger. Roots only — no payloads, no personal data leaves the database.