Data Model
The data pipeline: ESP32 sensors publish state changes over aioesphomeapi (encrypted, port 6053). The ingestor maps 286 ESPHome object IDs across climate, equipment, state, setpoint, diagnostics, and daily accumulator surfaces, plus 135 cfg_* readback aliases for firmware confirmation. Seventeen periodic tasks enrich and check the data: water-flow sync, materialized-view refresh, Shelly energy, Tempest weather, Home Assistant sensors, alert monitoring, setpoint dispatch and confirmation, forecast sync/actions/deviation checks, daily summaries, grow-light rollups, planner heartbeat, midnight watch, and public inference-infra samples. Everything runs locally on a single VM.
TimescaleDB (PostgreSQL 16 + hypertables) stores every measurement, event, plan, and observation.
Catalog counts below were checked against the live production database on 2026-05-23.
Public Sample Dataset
For public readers who want to inspect the receipts without database access:
Hour-by-hour greenhouse climate averages and equipment runtime minutes. The generated page links to the current dated archive and keeps prior versions in the same folder.
5-minute indoor climate, outdoor weather, hydro, soil, water, and solar fields. SHA-256: 055ae3a41847edccec82c5fc1d9dc00ac5153f2b9eaa7163c390a126aa5d9faf.
Plan scorecards, stress hours, resource cost, hypotheses, expected outcomes, and actual outcomes. SHA-256: 5be16adfb5cb089ca882669219f2ba82892db9fee7a1c70339d3ae0b861d8711.
The export omits local IPs, device IDs, trigger UUIDs, alert channels, hostnames, and raw sensor entity names.
Core Tables
| Table | Type | Rows | Write Pattern |
|---|---|---|---|
climate | Hypertable | 273K+ | ~60s batch (ESP32 sensors + outdoor merge) |
equipment_state | Hypertable | 118K+ | Event-driven (relay and switch changes) |
energy | Hypertable | 526K+ | Every 5 min (Shelly EM50) |
setpoint_changes | Hypertable | 86K+ | Event-driven (ESP32 reports + plan pushes) |
setpoint_plan | Hypertable | 46K+ | Planner waypoints and tunable updates |
weather_forecast | Hypertable | 311K+ | Hourly (Open-Meteo, 16-day, 28 columns) |
daily_summary | Regular | 288 | Nightly and live summary rows |
diagnostics | Hypertable | 146K+ | ~60s (ESP32 health) |
Setpoint & Tunable Delivery
The AI planning agent writes future intent to setpoint_plan, not directly to relays. The active value for each parameter resolves through v_active_plan; the ingestor dispatcher pushes changed values to ESPHome and the ESP32’s reported values land in setpoint_changes. Firmware cfg_* readbacks land in setpoint_snapshot, which is the ground-truth table for whether bounded tunables actually reached the controller.
| Object | Role |
|---|---|
setpoint_plan | AI-authored waypoints: timestamp, parameter, value, plan id, source, and reason. |
v_active_plan | Latest active plan value per parameter after supersession. |
setpoint_changes | Event log of pushed and ESP32-reported setpoint values; drives fn_setpoint_at(). |
setpoint_snapshot | ESP32 configured-value readbacks from cfg_* sensors. |
plan_delivery_log | Trigger id, planner instance, delivery status, and resulting plan id for MCP audit. |
/setpoints | Legacy key=value compatibility endpoint that combines active plan values, crop-band functions, lighting policy, activity windows, and direct-wet defaults for diagnostics/recovery tooling. Current firmware uses ESPHome push/readback instead of HTTP polling. |
See AI Tunables Traceability for the bounded tunable taxonomy and relay impact.
Crop & Operations Tables
| Table | Purpose | Status |
|---|---|---|
crops | Active plantings with position, zone, stage | 8 records |
crop_events | Stage changes, transplants, harvests | 14 logged events |
observations | Pest scouting, visual notes, camera assessments | 797 observations |
treatments | Spray/biological applications with PHI/REI | Empty |
harvests | Yield records | Empty |
plan_journal | Per-plan hypothesis, outcome, score | 196 entries |
planner_lessons | Validated patterns from planning cycles | 111 lessons |
Key Views (113 total)
| View | Purpose |
|---|---|
v_greenhouse_now | Single-row snapshot: all zones, hydro, costs, health |
v_equipment_now | Current state of 39 equipment-state rows |
v_cost_today | Real-time electric + gas + water cost |
v_active_plan | Resolves plan supersession (latest waypoint per parameter) |
v_band_trace_recent | Crop band, firmware-enforced band, cfg readback band, and compliance flags |
v_stress_hours_today | Hours above/below target bands per day |
v_disease_risk | Botrytis + condensation risk from RH/temp/VPD |
v_mister_effectiveness | VPD drop per pulse by zone, with outdoor context |
v_forecast_vs_actual | Hourly forecast accountability (bias detection) |
v_indoor_outdoor_correlation | Thermal gain coefficient |
v_state_durations | Time-in-state per day |
v_estimated_plant_dli | Corrected DLI estimate (sensor × correction factor) |
Key Functions
| Function | Returns |
|---|---|
fn_equipment_health() | 0–100 composite health score |
fn_stress_summary(date) | Human-readable stress text |
fn_system_health() | 4-component health: sensors, alerts, equipment, controller |
fn_house_vpd_control_band() | Whole-house VPD band derived from crop and zone policy |
fn_band_timeline() | Actual-to-forecast dashboard compliance band plus traceable firmware-derived trigger/padding thresholds |
fn_band_setpoint_provenance() | Four-edge source trace: crop target, dispatcher value, firmware push, cfg readback, and latest planner context |
fn_band_trace() | Canonical sample-level crop/firmware/readback band trace |
fn_compliance_pct() | % time within firmware-enforced target bands |
fn_forecast_dli() | Predicted natural DLI from forecast radiation |
fn_forecast_correction() | Rolling 7-day bias per forecast parameter |
The data-model page owns table, view, and function names. Live health and planner outcome panels stay on Operations and the Scorecard so this reference page stays a schema map rather than a second dashboard.
Compression & Retention
| Table | Compression | Retention |
|---|---|---|
| climate | 7 days | 365 days |
| energy | 7 days | 365 days |
| diagnostics | 7 days | 180 days |
| esp32_logs | — | 30 days |
Where to Go Next
- Planning Loop — how the AI planner reads this data and writes setpoints
- Operations — live health checks and data freshness monitoring
- Lessons Learned — what the data has taught us about greenhouse control