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

#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.

#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 — 39 of 40 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.

#Future user-owned data

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 — locked down entirely until authentication is wired up. 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 a standalone registry (no FK; 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 only active region is lake_erie. 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.

Updated September 1, 2026

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