The Invernaderos API is built with a modern, event-driven architecture optimized for high-throughput IoT sensor data ingestion, storage, and real-time distribution.
GREENHOUSE/{tenantId} # Multi-tenant sensor dataGREENHOUSE # Legacy (maps to DEFAULT tenant)GREENHOUSE/RESPONSE # Echo responses for verificationgreenhouse/+/actuators/# # Actuator commandsgreenhouse/+/actuators/status # Actuator status updatesgreenhouse/+/alerts/# # Alert notificationssystem/events/# # System events
Multi-Tenant Routing: Topic format GREENHOUSE/{tenantId} enables automatic tenant isolation. The API extracts tenantId from the topic path and associates all data with that tenant.
The system uses two specialized databases for different data types:
TimescaleDB (Time-Series)
PostgreSQL (Metadata)
Purpose: Store sensor readings with time-series optimizationsSpecifications:
Image: timescale/timescaledb:latest-pg16
Port: 5432
Schema: iot
Hypertable: sensor_readings
Chunk Interval: 7 days
Compression: Enabled after 7 days (saves ~90% storage)
Retention: 2 years automatic cleanup
Schema:
CREATE TABLE iot.sensor_readings ( time TIMESTAMPTZ NOT NULL, sensor_id VARCHAR(50) NOT NULL, greenhouse_id BIGINT NOT NULL, tenant_id BIGINT, sensor_type VARCHAR(30) NOT NULL, value DOUBLE PRECISION NOT NULL, unit VARCHAR(20), PRIMARY KEY (time, sensor_id));-- Convert to hypertableSELECT create_hypertable('iot.sensor_readings', 'time', chunk_time_interval => INTERVAL '7 days');-- Configure compressionALTER TABLE iot.sensor_readings SET ( timescaledb.compress, timescaledb.compress_segmentby = 'sensor_id, greenhouse_id', timescaledb.compress_orderby = 'time DESC');-- Add retention policySELECT add_retention_policy('iot.sensor_readings', INTERVAL '2 years');
Indexes:
CREATE INDEX idx_sensor_readings_greenhouse_id ON iot.sensor_readings(greenhouse_id, time DESC);CREATE INDEX idx_sensor_readings_tenant_time ON iot.sensor_readings(tenant_id, time DESC) WHERE tenant_id IS NOT NULL;CREATE INDEX idx_sensor_readings_greenhouse_sensor_time ON iot.sensor_readings(greenhouse_id, sensor_id, time DESC);
Continuous Aggregates:
-- Hourly aggregates (refreshed every 1 hour)CREATE MATERIALIZED VIEW iot.sensor_readings_hourlyWITH (timescaledb.continuous) ASSELECT time_bucket('1 hour', time) AS bucket, sensor_id, greenhouse_id, tenant_id, AVG(value) AS avg_value, MIN(value) AS min_value, MAX(value) AS max_value, STDDEV(value) AS stddev_value, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) AS median, PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY value) AS p95, COUNT(*) AS countFROM iot.sensor_readingsGROUP BY bucket, sensor_id, greenhouse_id, tenant_id;-- Daily tenant-level aggregatesCREATE MATERIALIZED VIEW iot.sensor_readings_daily_by_tenantWITH (timescaledb.continuous) ASSELECT time_bucket('1 day', time) AS day, tenant_id, COUNT(*) AS total_readings, COUNT(DISTINCT sensor_id) AS unique_sensors, COUNT(DISTINCT greenhouse_id) AS unique_greenhousesFROM iot.sensor_readingsWHERE tenant_id IS NOT NULLGROUP BY day, tenant_id;
Performance:
Queries on compressed data: 10-100x faster
Storage reduction: ~90% with compression
Continuous aggregates: 60x faster than raw queries
Purpose: Store reference data and relationshipsSpecifications:
Image: postgres:16-alpine
Port: 5433 (external), 5432 (internal)
Schema: metadata
Tables:
tenants - Master tenant registry
CREATE TABLE metadata.tenants ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name VARCHAR(100) NOT NULL UNIQUE, company_name VARCHAR(200), legal_name VARCHAR(200), tax_id VARCHAR(50), address TEXT, city VARCHAR(100), country VARCHAR(100) DEFAULT 'Spain', postal_code VARCHAR(20), phone VARCHAR(30), email VARCHAR(100), website VARCHAR(200), industry VARCHAR(100), is_active BOOLEAN DEFAULT true, created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW());
greenhouses - Greenhouse definitions
CREATE TABLE metadata.greenhouses ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES tenants(id), name VARCHAR(100) NOT NULL, greenhouse_code VARCHAR(50) UNIQUE, mqtt_topic VARCHAR(100), location_coordinates GEOGRAPHY(POINT, 4326), address TEXT, city VARCHAR(100), country VARCHAR(100) DEFAULT 'Spain', timezone VARCHAR(50) DEFAULT 'Europe/Madrid', area_m2 DECIMAL(10, 2), is_active BOOLEAN DEFAULT true, created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW());CREATE INDEX idx_greenhouses_tenant_id ON metadata.greenhouses(tenant_id);CREATE INDEX idx_greenhouses_code ON metadata.greenhouses(greenhouse_code);
sensors - Sensor devices
CREATE TABLE metadata.sensors ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES tenants(id), greenhouse_id UUID REFERENCES greenhouses(id), name VARCHAR(100) NOT NULL, sensor_type VARCHAR(50) NOT NULL, mqtt_topic VARCHAR(100), unit VARCHAR(20), min_value DECIMAL(10, 4), max_value DECIMAL(10, 4), last_seen_at TIMESTAMPTZ, is_active BOOLEAN DEFAULT true, metadata JSONB, created_at TIMESTAMPTZ DEFAULT NOW());CREATE INDEX idx_sensors_tenant_id ON metadata.sensors(tenant_id);CREATE INDEX idx_sensors_greenhouse_id ON metadata.sensors(greenhouse_id);CREATE INDEX idx_sensors_type ON metadata.sensors(sensor_type);
users - User accounts
CREATE TABLE metadata.users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES tenants(id), username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, first_name VARCHAR(100), last_name VARCHAR(100), role VARCHAR(20) NOT NULL CHECK (role IN ('ADMIN', 'USER', 'VIEWER')), is_active BOOLEAN DEFAULT true, last_login_at TIMESTAMPTZ, created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW());CREATE INDEX idx_users_tenant_id ON metadata.users(tenant_id);CREATE INDEX idx_users_email ON metadata.users(email);
alerts - System alerts
CREATE TYPE alert_severity AS ENUM ('INFO', 'WARNING', 'ERROR', 'CRITICAL', 'LOW', 'MEDIUM', 'HIGH');CREATE TYPE alert_type AS ENUM ('THRESHOLD_EXCEEDED', 'SENSOR_OFFLINE', 'ACTUATOR_FAILURE', 'SYSTEM_ERROR');CREATE TABLE metadata.alerts ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES tenants(id), greenhouse_id UUID REFERENCES greenhouses(id), sensor_id UUID REFERENCES sensors(id), alert_type alert_type NOT NULL, severity alert_severity NOT NULL DEFAULT 'INFO', title VARCHAR(200) NOT NULL, message TEXT NOT NULL, alert_data JSONB, is_resolved BOOLEAN DEFAULT false, resolved_at TIMESTAMPTZ, resolved_by_user_id UUID REFERENCES users(id), created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW());CREATE INDEX idx_alerts_tenant_id ON metadata.alerts(tenant_id);CREATE INDEX idx_alerts_unresolved ON metadata.alerts(tenant_id, is_resolved) WHERE is_resolved = false;