34 lines
1.9 KiB
PL/PgSQL
34 lines
1.9 KiB
PL/PgSQL
|
BEGIN;
|
||
|
|
|
||
|
|
CREATE TABLE device_command (
|
||
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
public_id uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
|
||
|
|
tenant_id uuid NOT NULL REFERENCES tenant(id),
|
||
|
|
device_id uuid NOT NULL,
|
||
|
|
command_type varchar(40) NOT NULL CHECK (command_type IN ('apply_config','reboot','diagnostics','firmware_update')),
|
||
|
|
payload jsonb NOT NULL DEFAULT '{}'::jsonb CHECK (jsonb_typeof(payload)='object'),
|
||
|
|
status varchar(24) NOT NULL DEFAULT 'QUEUED'
|
||
|
|
CHECK (status IN ('QUEUED','DELIVERED','ACKNOWLEDGED','SUCCEEDED','FAILED','EXPIRED','CANCELLED')),
|
||
|
|
result_summary jsonb NOT NULL DEFAULT '{}'::jsonb CHECK (jsonb_typeof(result_summary)='object'),
|
||
|
|
requested_by uuid NOT NULL,
|
||
|
|
idempotency_key varchar(255) NOT NULL,
|
||
|
|
request_digest char(64) NOT NULL,
|
||
|
|
expires_at timestamptz NOT NULL,
|
||
|
|
delivery_attempts integer NOT NULL DEFAULT 0 CHECK (delivery_attempts >= 0),
|
||
|
|
delivered_at timestamptz,
|
||
|
|
acknowledged_at timestamptz,
|
||
|
|
completed_at timestamptz,
|
||
|
|
version bigint NOT NULL DEFAULT 1 CHECK (version > 0),
|
||
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
CONSTRAINT device_command_device_tenant_fk FOREIGN KEY (device_id,tenant_id) REFERENCES device(id,tenant_id),
|
||
|
|
CONSTRAINT device_command_requester_tenant_fk FOREIGN KEY (requested_by,tenant_id) REFERENCES user_account(id,tenant_id),
|
||
|
|
CONSTRAINT device_command_actor_idempotency_key UNIQUE (tenant_id,requested_by,idempotency_key)
|
||
|
|
);
|
||
|
|
CREATE INDEX device_command_delivery_idx ON device_command(device_id,status,created_at)
|
||
|
|
WHERE status IN ('QUEUED','DELIVERED','ACKNOWLEDGED');
|
||
|
|
CREATE INDEX device_command_expiry_idx ON device_command(expires_at)
|
||
|
|
WHERE status IN ('QUEUED','DELIVERED','ACKNOWLEDGED');
|
||
|
|
|
||
|
|
COMMIT;
|