IoT devices and telemetry

CREATE TYPE alert_state AS ENUM ('open', 'acknowledged', 'resolved');

CREATE TABLE customers (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE sites (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    customer_id uuid NOT NULL,
    name text NOT NULL,
    timezone text DEFAULT 'UTC' NOT NULL
);
CREATE INDEX sites_customer_id_idx ON sites (customer_id);

CREATE TABLE firmware_versions (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    version text NOT NULL UNIQUE,
    released_at date NOT NULL
);

CREATE TABLE devices (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    site_id uuid NOT NULL,
    firmware_id uuid,
    serial_number text NOT NULL UNIQUE,
    model text NOT NULL,
    last_seen_at timestamptz
);
CREATE INDEX devices_site_id_idx ON devices (site_id);
CREATE INDEX devices_firmware_id_idx ON devices (firmware_id);

CREATE TABLE sensors (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    device_id uuid NOT NULL,
    kind text NOT NULL,
    unit text NOT NULL,
    UNIQUE (device_id, kind)
);

CREATE TABLE readings (
    sensor_id uuid NOT NULL,
    at timestamptz NOT NULL,
    value double precision NOT NULL,
    PRIMARY KEY (sensor_id, at)
);

CREATE TABLE alert_rules (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    sensor_id uuid NOT NULL,
    min_value double precision,
    max_value double precision,
    for_seconds int DEFAULT 0 NOT NULL
);
CREATE INDEX alert_rules_sensor_id_idx ON alert_rules (sensor_id);

CREATE TABLE alerts (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    rule_id uuid NOT NULL,
    state alert_state DEFAULT 'open' NOT NULL,
    opened_at timestamptz DEFAULT now() NOT NULL,
    resolved_at timestamptz
);
CREATE INDEX alerts_state_idx ON alerts (state, opened_at);
CREATE INDEX alerts_rule_id_idx ON alerts (rule_id);

ALTER TABLE sites ADD FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE;

ALTER TABLE devices ADD FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE;

ALTER TABLE devices ADD FOREIGN KEY (firmware_id) REFERENCES firmware_versions (id);

ALTER TABLE sensors ADD FOREIGN KEY (device_id) REFERENCES devices (id) ON DELETE CASCADE;

ALTER TABLE readings ADD FOREIGN KEY (sensor_id) REFERENCES sensors (id) ON DELETE CASCADE;

ALTER TABLE alert_rules ADD FOREIGN KEY (sensor_id) REFERENCES sensors (id) ON DELETE CASCADE;

ALTER TABLE alerts ADD FOREIGN KEY (rule_id) REFERENCES alert_rules (id) ON DELETE CASCADE;