System: PostgreSQL database for IoT agricultural monitoring system
Purpose: Environmental parameters, irrigation, and device management across multiple farms and clients
Purpose: SOWIT clients that have IOT devices on their farms
| Column | Type | Constraints | Description |
|---|---|---|---|
id |
integer | PK, NOT NULL | Auto-incrementing client ID |
client_name |
varchar(50) | Client name | |
client_token |
varchar(50) | Authentication token for client |
Relationships: One-to-many with farm table
Purpose: All farms associated to a client where IOT devices have been installed
| Column | Type | Constraints | Description |
|---|---|---|---|
id |
integer | PK, NOT NULL | Auto-incrementing farm ID |
client_id |
integer | FK | References client.id |
farm_name |
varchar(50) | Farm name |
Relationships:
clientplotPurpose: All plots in the farm where IOT devices have been installed
| Column | Type | Constraints | Description |
|---|---|---|---|
id |
integer | PK, NOT NULL | Auto-incrementing plot ID |
farm_id |
integer | FK | References farm.id |
plot_name |
varchar(50) | Plot name |
Relationships:
farmdevice_listPurpose: All devices installed in a plot
Important: This is the central device registry. All sensor data tables reference this table.
| Column | Type | Constraints | Description | Units |
|---|---|---|---|---|
id |
integer | PK, NOT NULL | Auto-incrementing device ID | - |
plot_id |
integer | FK | References plot.id | - |
device_name |
varchar(50) | Device name | - | |
device_type |
varchar | FK | References sensor.device_type | - |
device_subtype |
varchar | Device subtype | - | |
device_eui |
varchar | Device EUI identifier | - | |
longitude |
varchar(50) | GPS longitude | degrees | |
latitude |
varchar(50) | GPS latitude | degrees | |
horizon |
varchar | Horizon d'installation des sondes sol | - | |
sensor_depth_m |
numeric | Sensor depth | meters | |
max_drilled_depth_m |
numeric | Maximum drilled depth | meters | |
pump_clamping |
numeric | Pump clamping | meters | |
iothub_url |
varchar | IoT Hub URL | - | |
iothub_serial |
varchar | IoT Hub serial | - | |
has_et0 |
boolean | default false | Configuration for automatic ET0 computation | - |
hemisphere |
varchar | Geographic hemisphere | - | |
has_cold_hours |
boolean | default false | Configuration for cold hours computation | - |
has_dju |
boolean | default false | Configuration for "degré jour unitaire" | - |
client_defined_interval |
numeric | IoT device data sending interval | minutes | |
alert_emails |
varchar[] | Array of emails for device alerts | - | |
current_status |
boolean | default true | Device status (true=Connected, false=Disconnected) | - |
alert_min |
numeric | Minimum value to trigger alert | - | |
alert_max |
numeric | Maximum value to trigger alert | - | |
between_drippers |
boolean | default false | If device is installed between drippers | - |
deleted |
boolean | default false | Soft delete flag | - |
deleted_at |
timestamp | Deletion timestamp | - | |
c0 |
numeric | Tree circumference at dendrometer height | mm | |
v0 |
numeric | First valid DC4 dendrometer record | mm | |
replaced_by_id |
integer | ID of replacement device | - | |
x |
numeric | Basin dimension X | - | |
y |
numeric | Basin dimension Y | - | |
z |
numeric | Basin dimension Z | - | |
calibration |
numeric | Calibration value | - | |
device_drainage_id |
integer | Drainage device ID | - | |
device_list_ids_drainages |
integer[] | Array of drainage device IDs | - | |
ramp |
integer | default 0 | Ramp parameter | - |
three_number |
integer | default 0 | Three number parameter | - |
number_of_drained_trees |
integer | Number of drained trees | - | |
has_ec_ph_in |
boolean | default false | Has EC/pH input | - |
Critical Constraint: For devices with
id > 1048, longitude and latitude must both be provided or both be null
Purpose: Stores calculated irrigation and environmental metrics
Most Important Table: This contains all processed data from raw sensors
| Column | Type | Constraints | Description | Units |
|---|---|---|---|---|
id |
integer | PK, NOT NULL | Auto-incrementing ID | - |
device_list_id |
integer | FK | References device_list.id | - |
timestamp_data |
timestamp | Data timestamp | - | |
et0 |
numeric | Evapotranspiration | mm | |
instant_water_flow |
numeric | Latest irrigation amount | L (KONTEAU_20), m³ (others) | |
irrigation_round_total |
numeric | Irrigation round total | L (KONTEAU_20), m³ (others) | |
cold_hours |
numeric | Cold hours | hours | |
dju |
numeric | Degree day units | °C | |
daily_irrigation |
numeric | Daily irrigation (midnight to midnight) | L (KONTEAU_20), m³ (others) | |
cold_hours_10 |
numeric | Cold hours at 10°C | hours | |
cold_hours_12 |
numeric | Cold hours at 12°C | hours | |
t_min |
numeric | Daily minimum temperature | °C | |
t_max |
numeric | Daily maximum temperature | °C | |
t_avg |
numeric | Daily average temperature | °C | |
daily_irr |
numeric | Cumulated daily IRR value | - | |
h_min |
numeric | Minimum daily humidity | % | |
h_max |
numeric | Maximum daily humidity | % | |
h_avg |
numeric | Average daily humidity | % | |
dju_framboise |
numeric | Raspberry degree day units | °C | |
dju_myrtille |
numeric | Blueberry degree day units | °C | |
instant_rain_fall |
numeric | Instant rainfall | mm | |
daily_rain_ultra |
numeric | Daily rainfall for ONEWEATHER ULTRA | mm |
Critical Business Rules:
instant_water_flow: Difference between last 2 Konteau readingsirrigation_round_total: Cumulative water flow during irrigation (resets when flow = 0)daily_irrigation: 24-hour total (midnight to midnight)- Unit variations: KONTEAU_20 devices use Liters (L), others use cubic meters (m³)
Indexes:
idx_calculated_data_device_time: (device_list_id, timestamp_data)unique_device_timestamp_future: Unique constraint on (device_list_id, timestamp_data) for id > 6589807Purpose: Water flow measurement data
| Column | Type | Constraints | Description | Units |
|---|---|---|---|---|
id |
integer | PK, NOT NULL | Auto-incrementing ID | - |
device_list_id |
integer | FK | References device_list.id | - |
timestamp_data |
timestamp | Data timestamp | - | |
total_pulse |
numeric | Total pulse count | - | |
water_flow |
numeric | Accumulated water flow | m³ | |
water_flow_rev |
numeric | Accumulated reverse water flow (KONTEAU_50+ only) | m³ | |
instant_flow_rate |
numeric | Instant flow rate (KONTEAU_50+ direct, KONTEAU_RKV/KONTEAU_20 calculated) | m³/h | |
water_temperature |
numeric | Water temperature (KONTEAU_50+ only) | °C | |
water_pressure |
numeric | Water pressure | kPa |
Device Subtype Naming Convention:
In the database, device subtypes are named asKONTEAU_20,KONTEAU_50,KONTEAU_80,KONTEAU_100,KONTEAU_150,KONTEAU_P,KONTEAU_RKV, etc.
Important Device Subtypes:
Instant Flow Rate Calculation:
- KONTEAU_RKV and KONTEAU_20 devices: instant_flow_rate is automatically calculated using the formula :
- Where V is water_flow and T is timestamp_data
- KONTEAU_50+ devices: instant_flow_rate values come directly from the device
- Calculation is performed at the database level when new Konteau data is inserted
Purpose: Capacitive probe soil measurements
| Column | Type | Constraints | Description | Units |
|---|---|---|---|---|
id |
integer | PK, NOT NULL | Auto-incrementing ID | - |
device_list_id |
integer | FK | References device_list.id | - |
timestamp_data |
timestamp | Data timestamp | - | |
soil_humidity |
numeric | Soil humidity | % | |
soil_temperature |
numeric | Soil temperature | °C | |
soil_conductivity |
numeric | Soil conductivity | uS/cm | |
device_battery |
numeric | Device battery voltage | V |
Index: idx_onesensor_device_time (device_list_id, timestamp_data)
Purpose: Weather station measurements
| Column | Type | Constraints | Description | Units |
|---|---|---|---|---|
id |
integer | PK, NOT NULL | Auto-incrementing ID | - |
device_list_id |
integer | FK | References device_list.id | - |
timestamp_data |
timestamp | Data timestamp | - | |
air_temperature |
numeric | Air temperature | °C | |
air_humidity |
numeric | Air humidity | % | |
air_pressure |
numeric | Air pressure | hPa | |
illuminance |
numeric | Illuminance | lux | |
wind_speed |
numeric | Wind speed | m/s | |
wind_direction |
varchar | Wind direction | - | |
rain_gauge |
numeric | Rain gauge (don't use for API calculations) | mm | |
co2 |
numeric | CO2 concentration | ppm | |
rain_snow_detect |
numeric | Rain/snow detection | - | |
irr |
numeric | IRR measurement | µm | |
par |
numeric | PAR measurement | µm | |
wind_direction_angle |
numeric | Wind direction angle | degrees | |
max_wind_speed |
numeric | Maximum wind speed (OneWeather Ultra) | m/s | |
rain_intensity |
numeric | Rain intensity (OneWeather Ultra) | mm/h | |
fall_state |
boolean | Fall status (1=down, 0=upright) | - |
Index: idx_oneweather_device_time (device_list_id, timestamp_data)
Purpose: Temperature and humidity measurements
| Column | Type | Constraints | Description | Units |
|---|---|---|---|---|
id |
integer | PK, NOT NULL | Auto-incrementing ID | - |
device_list_id |
integer | FK | References device_list.id | - |
timestamp_data |
timestamp | Data timestamp | - | |
device_battery |
numeric | Device battery voltage | V | |
builtin_temperature |
numeric | Built-in temperature | °C | |
builtin_humidity |
numeric | Built-in humidity | % RH | |
external_temperature |
numeric | External temperature (>300°C if not connected) | °C |
Purpose: Tree trunk size measurements
| Column | Type | Constraints | Description | Units |
|---|---|---|---|---|
id |
integer | PK, NOT NULL | Auto-incrementing ID | - |
device_list_id |
integer | FK | References device_list.id | - |
timestamp_data |
timestamp(6) | Data timestamp | - | |
diameter_growth |
numeric | Raw dendrometer evolution | µm | |
circumference_dc4 |
numeric | Circumference for DC4 dendrometer | µm |
Note: References
c0andv0fields in device_list for calibration
Purpose: Device connectivity logging
| Column | Type | Constraints | Description | Units |
|---|---|---|---|---|
id |
integer | PK, NOT NULL | Auto-incrementing ID | - |
device_list_id |
integer | FK | References device_list.id | - |
last_timestamp |
timestamp(6) | Last data timestamp | - | |
insertion_timestamp |
timestamp(6) | Log insertion timestamp | - | |
consecutive_missed_sending |
integer | Consecutive missed transmissions | - | |
solved |
boolean | default false | Issue resolved flag | - |
solved_timestamp |
timestamp(6) | Resolution timestamp | - |
Purpose: Drainage event tracking
| Column | Type | Constraints | Description | Units |
|---|---|---|---|---|
id |
integer | PK, NOT NULL | Auto-incrementing ID | - |
device_list_id |
integer | FK | References device_list.id | - |
tour_number |
integer | NOT NULL | Tour number | - |
drain_time_start |
timestamp | NOT NULL | Drainage start time | - |
duration_minutes |
integer | NOT NULL | Duration in minutes | minutes |
ec_in |
double precision | EC input value | - | |
ph_in |
double precision | pH input value | - | |
ec_out |
double precision | EC output value | - | |
ph_out |
double precision | pH output value | - | |
konteau_device_id |
integer | Related Konteau device ID | - | |
drainage_percentage |
double precision | Drainage percentage | % | |
delta_ec |
double precision | EC difference | - | |
delta_ph |
double precision | pH difference | - | |
quantity_water_in |
double precision | Water quantity input | - | |
quantity_water_out |
double precision | Water quantity output | - |
instant_water_flow, irrigation_round_total, daily_irrigation in calculated_data tabletrue = Connected, false = Disconnectedid > 6589807 have unique timestamp constraintsid > 1048 must have both lat/long or both nullSELECT * FROM [sensor_table]
WHERE device_list_id = X
ORDER BY timestamp_data DESC
LIMIT 1;
SELECT * FROM [sensor_table]
WHERE device_list_id = X
AND timestamp_data BETWEEN 'start_date' AND 'end_date'
ORDER BY timestamp_data;
SELECT c.client_name, f.farm_name, p.plot_name, d.device_name
FROM client c
JOIN farm f ON c.id = f.client_id
JOIN plot p ON f.id = p.farm_id
JOIN device_list d ON p.id = d.plot_id
WHERE d.id = X;
SELECT * FROM device_list
WHERE deleted = false
AND current_status = true;
idx_calculated_data_device_time: For calculated_data queriesidx_onesensor_device_time: For soil sensor queriesidx_oneweather_device_time: For weather data queries-- Always include these filters for active devices
WHERE deleted = false AND current_status = true
-- For specific device types
WHERE device_subtype = 'KONTEAU_20' -- or other specific type
-- For date ranges (use indexes)
WHERE device_list_id = X AND timestamp_data BETWEEN 'start' AND 'end'
deleted = false unless specifically requesting deleted devicesid <= 6589807 may have duplicate timestampsclient (1) → (many) farm (1) → (many) plot (1) → (many) device_list (1) → (many) sensor_data_tables
Foreign Key Relationships:
device_list.id (CASCADE DELETE)farm.client_id → client.idplot.farm_id → farm.iddevice_list.plot_id → plot.iddevice_list.device_type → sensor.device_typeThis schema reference provides complete information for understanding the IoT agricultural monitoring system database structure, relationships, units, business rules, and query patterns.