Data model
Two halves of one asset. Fixed data is the tree's identity and is never edited — corrections are audited amendments. Variable data is append-only: each observation is an event, and the tree simply shows its latest state.
Lifecycle state machine
Nursery→Planted→Establishment→Juvenile→Productive→Peak→Declining→Dead→Replaced
A dead tree is never deleted. It is closed out, and the replant occupies the same physical slot with a new tree_id linked through replacement_tree_id — so survival, cost per surviving tree and client guarantees stay auditable.
Variable data
Append-only observations and events. Every write is timestamped, attributed and reversible.
Lifecycle state
- stage enum
- Juvenile → Productive
- Nursery → Planted → Establishment → Juvenile → Productive → Peak → Declining → Dead/Replaced.
- age_months derived
- 38
- Computed from the fixed planting_date — never stored by hand.
- replacement_tree_id fk
- …-T0902
- Set when a dead tree is replanted; keeps the lineage without mutating fixed data.
- certification_status enum
- Rainforest Alliance
- Audited annually; can be suspended and restored.
Growth measurements
- height_cm / trunk_diameter_mm number
- 214 · 61
- One row per inspection; the tree shows the latest, analytics use the series.
- canopy_diameter_cm number
- 178
- Drives shade and thinning decisions.
- health_score / health_band 0-100 / enum
- 82 · Good
- Composite of vigour, defoliation and pest scoring.
Health & interventions
- pest_pressure / disease_flags enum / array
- Moderate · [Black pod]
- Open flags escalate the block to the agronomy queue.
- last_pruned / last_fertilised date
- 2026-03-04 · 2026-05-19
- Each intervention is an event row with inputs and cost.
- last_inspection / inspections_ytd date / int
- 2026-07-14 · 5
- SLA tracked per client contract.
Production & value
- pods_current_cycle / yield_kg_12m number
- 34 · 2.4
- Harvest events roll up to lot and client level.
- cumulative_yield_kg number
- 11.8
- Lifetime production, used for cohort yield curves.
- carbon_tco2e number
- 0.247
- Recalculated on each biometric update.
- asset_value_usd number
- 64
- Age × health × yield curve; the tree as a balance-sheet asset.
Why the split matters at 3M+ trees
- Storage. Fixed rows are written once (~3.1M rows). Variable rows grow with every inspection — partition by year and plot, keep only the latest snapshot hot.
- Trust. Client audits (EUDR, carbon, survival guarantees) depend on planting identity being tamper-evident and separate from field-reported measurements.
- Analytics. Cohort curves — yield by cultivar, survival by nursery batch — only work when the fixed dimensions never drift.