Data Model
The data pipeline: ESP32 sensors publish state changes over aioesphomeapi (encrypted, port 6053). The ingestor maps 172 entities to 128 database columns and writes them to TimescaleDB in near real-time. Twelve periodic tasks enrich the data: outdoor weather from Open-Meteo, energy from a Shelly EM50, forecasts, alert monitoring, and band-driven setpoint dispatch. Everything runs locally on a single VM.
TimescaleDB (PostgreSQL 16 + hypertables) stores every measurement, event, plan, and observation.
Public Sample Dataset
For launch readers who want to inspect the receipts without database access:
5-minute indoor climate, outdoor weather, hydro, soil, water, and solar fields. SHA-256: a6a9321bb55ebd48f872bc8d9bba5a182fae298534e33d358ffa291fa608a8a3.
Plan scorecards, stress hours, resource cost, hypotheses, expected outcomes, and actual outcomes. SHA-256: eba09057e5c70ba8bcbe68200d4213d3315496e207fd207f848217735f0404ea.
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 | 221K+ | ~2 min batch (ESP32 sensors + outdoor merge) |
equipment_state | Hypertable | 39K+ | Event-driven (relay changes) |
energy | Hypertable | 516K+ | Every 5 min (Shelly EM50) |
setpoint_changes | Hypertable | — | Event-driven (ESP32 reports + plan pushes) |
setpoint_plan | Hypertable | 144+ | 3× daily (AI planner waypoints) |
weather_forecast | Hypertable | 28K+ | Hourly (Open-Meteo, 16-day, 27 columns) |
daily_summary | Regular | 236 | Nightly snapshot |
diagnostics | Hypertable | — | ~60s (ESP32 health) |
Setpoint & Tunable Delivery
Iris (our OpenClaw AI 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 | HTTP fallback that combines active plan values, crop-band functions, and outdoor readings for ESP32 pull. |
See AI-Writable Tunables for the bounded tunable taxonomy and relay impact.
Crop & Operations Tables
| Table | Purpose | Status |
|---|---|---|
crops | Active plantings with position, zone, stage | 3 records |
crop_events | Stage changes, transplants, harvests | Empty (awaiting operator input) |
observations | Pest scouting, visual notes, camera assessments | Empty |
treatments | Spray/biological applications with PHI/REI | Empty |
harvests | Yield records | Empty |
plan_journal | Per-plan hypothesis, outcome, score | 12 entries |
planner_lessons | Validated patterns from planning cycles | 75 lessons |
Key Views (56 total)
| View | Purpose |
|---|---|
v_greenhouse_now | Single-row snapshot: all zones, hydro, costs, health |
v_equipment_now | Current state of all 33 equipment items |
v_cost_today | Real-time electric + gas + water cost |
v_active_plan | Resolves plan supersession (latest waypoint per parameter) |
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_compliance_pct() | % time within target bands |
fn_forecast_dli() | Predicted natural DLI from forecast radiation |
fn_forecast_correction() | Rolling 7-day bias per forecast parameter |
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