Data Model

TimescaleDB (PostgreSQL 16 + hypertables) stores every measurement, event, plan, and observation.

Core Tables

Table	Type	Rows	Write Pattern
climate	Hypertable	199K+	~2 min batch (ESP32 sensors + outdoor merge)
equipment_state	Hypertable	39K+	Event-driven (relay changes)
energy	Hypertable	512K+	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)

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	4 lessons

Key Views (34 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