23 lines
1.5 KiB
SQL
23 lines
1.5 KiB
SQL
|
CREATE TABLE fleet_firmware (
|
||
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id uuid NOT NULL REFERENCES tenant(id),
|
||
|
|
version_code bigint GENERATED ALWAYS AS IDENTITY UNIQUE,
|
||
|
|
model text NOT NULL, version text NOT NULL, release_notes text NOT NULL DEFAULT '',
|
||
|
|
size bigint NOT NULL CHECK(size > 0 AND size <= 33554432), sha256 text NOT NULL,
|
||
|
|
object_ref text NOT NULL UNIQUE, status text NOT NULL DEFAULT 'UPLOADING' CHECK(status IN ('UPLOADING','READY')),
|
||
|
|
created_at timestamptz NOT NULL DEFAULT now(), UNIQUE(tenant_id,model,version)
|
||
|
|
);
|
||
|
|
CREATE INDEX fleet_firmware_latest ON fleet_firmware(tenant_id,model,version_code DESC) WHERE status='READY';
|
||
|
|
CREATE TABLE fleet_presence (
|
||
|
|
device_id uuid PRIMARY KEY REFERENCES device(id), password_hash bytea NOT NULL,
|
||
|
|
connection_id uuid, connected boolean NOT NULL DEFAULT false, last_seen_at timestamptz,
|
||
|
|
firmware_version text NOT NULL DEFAULT '', version_code bigint NOT NULL DEFAULT 0 CHECK(version_code>=0),
|
||
|
|
ota_supported boolean NOT NULL DEFAULT false
|
||
|
|
);
|
||
|
|
CREATE TABLE fleet_ota (
|
||
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), device_id uuid NOT NULL REFERENCES device(id),
|
||
|
|
release_id uuid NOT NULL REFERENCES fleet_firmware(id), request_id uuid NOT NULL UNIQUE,
|
||
|
|
status text NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','accepted','success','failed','timeout')),
|
||
|
|
message text NOT NULL DEFAULT '', created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now()
|
||
|
|
);
|
||
|
|
CREATE UNIQUE INDEX fleet_ota_active ON fleet_ota(device_id) WHERE status IN ('pending','accepted');
|