Time-series readings (partitioned)

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

CREATE TABLE devices (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    project_id uuid NOT NULL,
    name text NOT NULL,
    kind text NOT NULL,
    created_at timestamptz DEFAULT now() NOT NULL
);

CREATE TABLE metrics (
    id serial PRIMARY KEY,
    project_id uuid NOT NULL,
    name text NOT NULL,
    unit text
);

CREATE TABLE readings (
    device_id uuid NOT NULL,
    metric_id integer NOT NULL,
    recorded_at timestamptz NOT NULL,
    value double precision NOT NULL,
    PRIMARY KEY (device_id, metric_id, recorded_at)
) PARTITION BY RANGE (recorded_at);
CREATE TABLE readings_2024_01 PARTITION OF readings FOR VALUES from ('2024-01-01') to ('2024-02-01');
CREATE TABLE readings_2024_02 PARTITION OF readings FOR VALUES from ('2024-02-01') to ('2024-03-01');
CREATE TABLE readings_default PARTITION OF readings DEFAULT;
CREATE INDEX readings_recorded_at_brin ON readings USING brin (recorded_at);

CREATE TABLE alert_rules (
    id serial PRIMARY KEY,
    metric_id integer NOT NULL,
    threshold double precision NOT NULL,
    comparison text NOT NULL
);

CREATE TABLE alerts (
    id bigserial PRIMARY KEY,
    rule_id integer NOT NULL,
    device_id uuid NOT NULL,
    fired_at timestamptz DEFAULT now() NOT NULL
);

ALTER TABLE devices ADD FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE;

ALTER TABLE metrics ADD FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE;

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

ALTER TABLE readings ADD FOREIGN KEY (metric_id) REFERENCES metrics (id);

ALTER TABLE alert_rules ADD FOREIGN KEY (metric_id) REFERENCES metrics (id) ON DELETE CASCADE;

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

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