39 lines
1.9 KiB
PL/PgSQL
39 lines
1.9 KiB
PL/PgSQL
BEGIN;
|
|
|
|
ALTER TABLE device_twin
|
|
ADD COLUMN telemetry_sequence bigint NOT NULL DEFAULT 0 CHECK (telemetry_sequence >= 0),
|
|
ADD COLUMN last_error_code varchar(80),
|
|
ADD COLUMN last_error_severity varchar(16),
|
|
ADD COLUMN last_error_at timestamptz;
|
|
|
|
CREATE TABLE device_telemetry_event (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id uuid NOT NULL REFERENCES tenant(id),
|
|
device_id uuid NOT NULL,
|
|
conversation_id uuid,
|
|
event_key varchar(255) NOT NULL,
|
|
sequence_no bigint NOT NULL CHECK (sequence_no > 0),
|
|
event_type varchar(24) NOT NULL CHECK (event_type IN ('status','error','command_result')),
|
|
occurred_at timestamptz NOT NULL,
|
|
payload_digest char(64) NOT NULL,
|
|
payload jsonb NOT NULL CHECK (jsonb_typeof(payload)='object'),
|
|
error_code varchar(80),
|
|
error_severity varchar(16) CHECK (error_severity IS NULL OR error_severity IN ('warning','error','critical')),
|
|
command_id uuid REFERENCES device_command(id),
|
|
received_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT device_telemetry_device_tenant_fk FOREIGN KEY (device_id,tenant_id)
|
|
REFERENCES device(id,tenant_id),
|
|
CONSTRAINT device_telemetry_conversation_tenant_fk FOREIGN KEY (conversation_id,tenant_id)
|
|
REFERENCES conversation(id,tenant_id),
|
|
CONSTRAINT device_telemetry_event_key UNIQUE (device_id,event_key),
|
|
CONSTRAINT device_telemetry_sequence_key UNIQUE (device_id,sequence_no)
|
|
);
|
|
|
|
CREATE INDEX device_telemetry_time_idx ON device_telemetry_event(device_id,occurred_at DESC,id);
|
|
CREATE INDEX device_telemetry_conversation_idx ON device_telemetry_event(conversation_id,occurred_at DESC)
|
|
WHERE conversation_id IS NOT NULL;
|
|
CREATE INDEX device_telemetry_error_idx ON device_telemetry_event(tenant_id,error_severity,occurred_at DESC)
|
|
WHERE event_type='error';
|
|
|
|
COMMIT;
|