Data Model & Schema

Complete reference for the Postgres schema behind Crop Picker, grouped by concern, with relationships, views and RPCs, the snowflake scoring tables, provenance tables, and the regional data pattern.

Complete reference for the Postgres schema that backs Crop Picker. Every table, grouped by concern, with its primary relationships and access posture.

#Schema overview

All tables live in the public schema. Row-Level Security is enabled on every table (see Security & Access Control); the anon role has SELECT on the public-catalog tables and nothing on the user-owned farm_* tables.

#Core catalog

Table Purpose
crops One row per crop. Slug, common name, scientific name, category, lifecycle, image, hardiness zone range, labor hours/acre, data completeness.
regions The regions Crop Picker covers. name, display_name, description, country, states[], hardiness_zones[], latitude, longitude, boundary (PostGIS geography), is_active. Drives the location picker; only is_active = true regions are returned by /api/v1/regions.

#Economics & market

Table Purpose
crop_economics Gross revenue/acre, net return, establishment cost, annual operating cost, price trend (3-yr), insurance availability.
price_history Per-year wholesale price points per crop.
crop_market_channels Distribution channels (direct-to-consumer, wholesale, processor, etc.) — many per crop.

#Nutrition & ecosystem services

Table Purpose
crop_nutrition USDA FoodData Central–derived nutrient profile per edible form: macros, vitamins, minerals, fdc_id/fdc_data_type provenance, edible_fraction, yield_to_kg_factor, and a computed nutrient_density_score. is_primary_form marks the canonical row for a crop.
crop_ecosystem_services Long-form ecosystem-service claims per crop: category, service_name, magnitude, optional metric_value/metric_unit, evidence_strength, confidence, and applies_when scoping.

#Agronomy

Table Purpose
crop_soil_preferences Preferred soil types (sandy loam, clay, etc.)
crop_drainage_preferences Drainage class preferences
crop_equipment_requirements Machinery needed
crop_storage_requirements Post-harvest storage specs
crop_risks Pests, diseases, weather, market risks

#Site attributes & polyculture (added 2026-10-01)

Columns and tables that let /api/v1/recommend answer farm-level questions — the Grape Belt's dominant variables (slope, aspect, frost pockets) had no crop-side attribute to match against. Everything here is nullable; the matching engine treats null as unknown, never as a pass or a fail, so nothing changes until a value is researched and cited.

Object Purpose
crops.max_slope_pct Steepest slope the crop is commercially workable on
crops.frost_sensitivity_bloom low / moderate / high — sensitivity of bloom or fruit set to spring frost
crops.prefers_air_drainage Extension guidance recommends sloping or elevated sites so cold air drains away
crops.shade_tolerance none / partial / full — for understory and alley placement
crops.canopy_height_ft, crops.root_depth_in Structural inputs for guild and alley layout
crops.wind_exposure_tolerance low / moderate / high — lake-shore and ridge fields
cultivars Named cultivars of a crop. Non-null attributes (zone, GDD, days to maturity, chill, resistances) override the species envelope; null inherits. One citation per column, as for crops.
crop_interactions Directional, cited pairwise relations between crops: nurse, alley_overstory, understory, nitrogen_source, pollination_partner, pest_suppression, shared_pest_host, allelopathic, competition, rotation_benefit, rotation_conflict — with effect, magnitude, evidence_strength, mechanism and spatial pattern. The minimum structure for companion and guild recommendations.

Both tables have RLS enabled with a public SELECT policy, matching the rest of the catalog.

#Land evaluation (added 2026-10-22)

Structures that make the catalog usable by a land evaluation in the style of the FAO Framework for Land Evaluation (FAO 1976; Guidelines: land evaluation for rainfed agriculture, Soils Bulletin 52, 1983; the revised framework, 2007) and D.G. Rossiter's theoretical framework (Geoderma 72, 1996). In that vocabulary a land utilization type (a crop, a crop combination or a cropping system, at a stated management level) has land use requirements, graded into factor ratings, that are matched against the land qualities of a piece of land.

Object Purpose
land_qualities Reference vocabulary: the 25 land qualities of FAO Soils Bulletin 52 (radiation, temperature, moisture, oxygen availability, nutrient availability and retention, rooting conditions, flood hazard, climatic hazards, salts, toxicities, pests and diseases, workability, mechanization, timing of production, erosion hazard, soil degradation and others) plus pollination and biodiversity_habitat, which crop systems need.
land_use_types, land_use_type_components A land utilization type and the crops in it, with each crop's role, area share, sequence position, season and harvest years. The arrangement is explicit (sole, rotation, double, relay, row_intercrop, strip_intercrop, mixed_intercrop, alley, understory, multistrata, compound), as is the management level (low / intermediate / high). Every catalog crop has a sole-crop type at the intermediate level.
crop_requirements Graded land use requirements: per crop (or land use type), land quality and diagnostic characteristic, the limits of each rating in bands (two bands for two-sided variables like pH), a separate optimum, and threshold-plus-slope yield-decline functions for salinity and boron. scheme is the normalized family (optimal_absolute, fao_ratings, yield_decline, categorical_rating, single_range); source_scheme and source_classes keep the source's own rating scale verbatim, so nothing is forced onto S1–N. About 1,835 rows for 80 crops, each cited (mostly FAO ECOCROP and FAO Irrigation & Drainage Papers 29 and 48, USDA PLANTS, GAEZ v4 and extension ratings).
evaluation_profiles The rating scale and combination rule as data. The default is FAO Soils Bulletin 52's maximum limitation (the overall class is the worst rating among the important land qualities); a multiplicative profile is also defined. verdict_ratings maps the catalog's existing categorical ratings to factor ratings (ideal s1, suitable s2, marginal s3, poor n).
crop_interactions.land_quality, secondary_land_qualities, affected, lq_direction Which land quality an interaction changes, for which crop, and in which direction — the bridge that lets a crop system's evaluation adjust what each component experiences (a legume raising nutrient availability for its partner, a canopy lowering radiation for an understory crop, juglone as a soil toxicity). Judged from each row's own cited mechanism text; null where the text names no mechanism.

All five tables have RLS enabled with a public SELECT policy. /api/v1/recommend reads them for its fao block and /api/v1/systems/evaluate for crop systems.

#Intercropping trials (added 2026-10-23)

Measured evidence for crop systems: how much a mix yields compared with its crops grown alone.

Object Purpose
intercrop_trials One trial: a published study (literature), a research station trial, or an every.farm on-farm trial. It records system, design, years, a coarse location, the site characteristics in the matcher's vocabulary plus site_reported (as the paper states them), the land use type it measures, the published LER or LER range, and verification (verified_by, verified_at, verification_notes). About 300 cited literature trials today.
intercrop_trial_components One row per crop. The partial LER is generated from the yields, or recorded as published (partial_ler_reported). Yields are on the whole-mix-area basis (see the on-farm protocol), along with economics and perennial establishment.
intercrop_trial_observations Mechanism measurements: pests, pollinators, light, soil moisture, tissue N, and yield by distance from a tree row.
intercrop_trial_interactions Links a trial to the crop_interactions rows it is evidence for.
intercrop_trial_ler (view) Per trial: LER, its basis (yields, reported_partials or reported_total), area × time equivalent ratio, mix value, and best sole-crop value. It runs with the caller's access rules.

Literature and verified trials are public; unverified on-farm trials are not.

#Scoring (snowflake model)

Table Purpose
snowflake_checks The 30 binary/scaled check definitions: 5 axes × 6 checks each. Fixed catalog; rarely edited.
crop_snowflake_scores Per-crop, per-region score for each check. Includes the rationale string written during evaluation.
crop_snowflake_summary View: roll-up of crop_snowflake_scores into axis totals per crop per region. What the screener reads.
crop_screener View: denormalized crop + economics join used by the filter UI.

#Snowflake axes

Every crop is scored on 5 axes out of 6 points each (30 total):

  • market_fit — demand strength, buyer concentration, pricing power
  • climate_fit — hardiness match, water needs, heat/cold tolerance
  • infrastructure_fit — equipment availability, storage, processing
  • financial_return — net return/acre, payback period, capital intensity
  • risk_profile — pest/disease pressure, weather risk, regulatory exposure

Each axis is the sum of 6 per-check scores (0–1 each). A rationale string per check explains the score — critical for the "show me where this number came from" UX.

Note: since 2026-09 every crop_snowflake_scores citation points at the primary sources of the score's inputs (the extension budget or NASS series the underlying value came from), never at crops.every.farm itself. Scores with uncited inputs carry no citation. The snowflake is a regional roll-up and is not being expanded to further regions; farm-level questions go to /api/v1/recommend, which evaluates crop attributes directly against supplied conditions.

#Buyers network

Table Purpose
buyers Registered buyers: name, type, address, location (PostGIS geography), contact, is_active.
buyer_crops Junction table: which crops each buyer purchases, with optional price per unit and contract terms.
RPC nearby_buyers PostGIS function: returns buyers within N miles of a lat/lng, optionally filtered by crop_id.

Geocoding is now essentially complete — all 48 buyers have a location. The "All buyers" tab on the detail page still handles the ungeocoded remainder via useCropBuyers, which skips distance math.

#Provenance

Table Purpose
data_sources Registry of every upstream source: USDA NASS, Penn State Extension, peer-reviewed journals, etc. Type, coverage category, region coverage, last-checked date.
data_audit_log Append-only log: every insert/update/delete with source_name, source_url, source_date, task_run_id, task_day_type, and region. Each changed field becomes one row (field_name, old_value, new_value, change_summary) — there is no JSON diff column; the diff is the row itself.
source_citations Column-level citation table layered on the data_sources registry: table_name • row_id • column_name → source_id, with source_url, source_quote, accessed_date, role, and confidence. Required for every research-agent write.

The audit log is what powers /api/v1/crops/:slug/sources and the Sources section on every crop page.

#The farm_* scaffold (not the integration path)

Table Purpose
farm_profiles Per-farm record — will join to Every.Farm users
farm_fields Field geometries, soil, drainage
farm_equipment Owned equipment per farm
farm_crop_scores Per-farm, per-crop suitability score

All four have RLS enabled without any anon policy and all four are empty. They were scaffolded for a design in which Crop Picker would hold farm records itself. That is no longer the plan: farm context lives on Every.Farm, and Crop Picker answers farm-level questions through /api/v1/recommend, which takes the farm's conditions as parameters and stores nothing. The tables are kept for now and may be dropped. See Integration with Every.Farm.

#Relationships

crops ─┬─ crop_economics (1:n — one row per crop × year × region)
       ├─ crop_soil_preferences (1:n)
       ├─ crop_drainage_preferences (1:n)
       ├─ crop_market_channels (1:n)
       ├─ crop_risks (1:n)
       ├─ crop_equipment_requirements (1:n)
       ├─ crop_storage_requirements (1:n)
       ├─ crop_snowflake_scores (n — one per region × check)
       ├─ price_history (1:n)
       └─ buyer_crops (n) ── buyers

regions ─── referenced by name from crop_economics.region,
                                 crop_snowflake_scores.region

data_audit_log references any table via table_name + record_id (note: the companion source_citations table uses row_id — the two are asymmetric)
data_sources is referenced by source_citations.source_id (FK); data_audit_log.source_name is a soft link

#Views and RPCs

  • crop_screener — public view joining crops + crop_economics for the filter UI. security_invoker = true so anon SELECT policies on underlying tables apply.
  • crop_snowflake_summary — rollup of crop_snowflake_scores by (crop_id, region, axis). Same security_invoker setting.
  • nearby_buyers(lat, lng, radius_miles, target_crop_id) — SQL RPC. Uses ST_DWithin on buyers.location and (if target_crop_id provided) inner-joins buyer_crops.

#Regional data pattern

Any column named region on any table references regions.name (the string key, not the UUID). Today the active regions are lake_erie and finger_lakes. The ingestion pipeline is expected to populate:

  • crop_economics.region (if per-region pricing exists)
  • crop_snowflake_scores.region
  • price_history.region (per-year price points are region-scoped)

And no other table — crop facts like hardiness zones and soil prefs are global. See Caching & Performance for how the app handles multi-region correctness.

#USDA GRIN anchors

Crops carry grin_taxon_id, the USDA GRIN Taxonomy nomen number for the species, which anchors scientific_name to the National Plant Germplasm System's name authority and is cited to the taxonomy page. Cultivars carry grin_accession (the NPGS PI number), grin_accession_id (the GRIN-Global record) and their own grin_taxon_id, cited to the accession page's passport data (cultivar name, origin, maintaining genebank, developer). The Geneva, NY repository holds the apple, grape and tart cherry collections; Corvallis holds the berries, hops and hazelnut. Anchoring began with apple, grape and cherry; accession observation data (disease reactions, hardiness, phenology) is the next cultivar-trait source.

Updated

Something missing or out of date? Tell us — the docs are updated with every release.