81 lines
4.4 KiB
PL/PgSQL
81 lines
4.4 KiB
PL/PgSQL
|
BEGIN;
|
||
|
|
|
||
|
|
CREATE TABLE firmware_signing_key (
|
||
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
tenant_id uuid NOT NULL REFERENCES tenant(id),
|
||
|
|
key_id varchar(120) NOT NULL,
|
||
|
|
public_key_base64 varchar(100) NOT NULL,
|
||
|
|
status varchar(24) NOT NULL DEFAULT 'ACTIVE' CHECK(status IN ('ACTIVE','REVOKED')),
|
||
|
|
created_by uuid NOT NULL,
|
||
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
revoked_at timestamptz,
|
||
|
|
CONSTRAINT firmware_key_creator_tenant_fk FOREIGN KEY(created_by,tenant_id) REFERENCES user_account(id,tenant_id),
|
||
|
|
CONSTRAINT firmware_key_tenant_key UNIQUE(tenant_id,key_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE firmware_release (
|
||
|
|
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),
|
||
|
|
household_id uuid NOT NULL,
|
||
|
|
model varchar(120) NOT NULL,
|
||
|
|
hardware_revision varchar(80),
|
||
|
|
version varchar(80) NOT NULL,
|
||
|
|
object_ref varchar(700) NOT NULL,
|
||
|
|
expected_size bigint NOT NULL CHECK (expected_size BETWEEN 1 AND 268435456),
|
||
|
|
expected_digest char(64) NOT NULL,
|
||
|
|
signature_algorithm varchar(24) NOT NULL DEFAULT 'ed25519' CHECK (signature_algorithm='ed25519'),
|
||
|
|
signature varchar(500) NOT NULL,
|
||
|
|
signing_key_id varchar(120) NOT NULL,
|
||
|
|
minimum_security_version varchar(80),
|
||
|
|
release_notes varchar(2000),
|
||
|
|
status varchar(24) NOT NULL DEFAULT 'UPLOADING'
|
||
|
|
CHECK (status IN ('UPLOADING','READY','PAUSED','RETIRED')),
|
||
|
|
created_by uuid NOT NULL,
|
||
|
|
verified_at timestamptz,
|
||
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
CONSTRAINT firmware_release_creator_tenant_fk FOREIGN KEY(created_by,tenant_id) REFERENCES user_account(id,tenant_id),
|
||
|
|
CONSTRAINT firmware_release_household_tenant_fk FOREIGN KEY(household_id,tenant_id) REFERENCES household(id,tenant_id),
|
||
|
|
CONSTRAINT firmware_release_signing_key_fk FOREIGN KEY(tenant_id,signing_key_id) REFERENCES firmware_signing_key(tenant_id,key_id),
|
||
|
|
CONSTRAINT firmware_release_version_key UNIQUE(tenant_id,model,hardware_revision,version)
|
||
|
|
);
|
||
|
|
CREATE INDEX firmware_release_tenant_model_idx ON firmware_release(tenant_id,model,status,created_at DESC);
|
||
|
|
|
||
|
|
CREATE TABLE firmware_rollout (
|
||
|
|
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),
|
||
|
|
household_id uuid NOT NULL,
|
||
|
|
release_id uuid NOT NULL REFERENCES firmware_release(id),
|
||
|
|
name varchar(160) NOT NULL,
|
||
|
|
status varchar(24) NOT NULL DEFAULT 'DRAFT'
|
||
|
|
CHECK (status IN ('DRAFT','RUNNING','PAUSED','COMPLETED','ROLLED_BACK')),
|
||
|
|
target_percentage integer NOT NULL CHECK (target_percentage BETWEEN 1 AND 100),
|
||
|
|
created_by uuid NOT NULL,
|
||
|
|
started_at timestamptz,
|
||
|
|
completed_at timestamptz,
|
||
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
CONSTRAINT firmware_rollout_household_tenant_fk FOREIGN KEY(household_id,tenant_id) REFERENCES household(id,tenant_id),
|
||
|
|
CONSTRAINT firmware_rollout_creator_tenant_fk FOREIGN KEY(created_by,tenant_id) REFERENCES user_account(id,tenant_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE firmware_assignment (
|
||
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
rollout_id uuid NOT NULL REFERENCES firmware_rollout(id) ON DELETE CASCADE,
|
||
|
|
tenant_id uuid NOT NULL REFERENCES tenant(id),
|
||
|
|
device_id uuid NOT NULL,
|
||
|
|
command_id uuid REFERENCES device_command(id),
|
||
|
|
previous_version varchar(80),
|
||
|
|
status varchar(24) NOT NULL DEFAULT 'PENDING'
|
||
|
|
CHECK (status IN ('PENDING','COMMAND_QUEUED','SUCCEEDED','FAILED','ROLLED_BACK')),
|
||
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
CONSTRAINT firmware_assignment_device_tenant_fk FOREIGN KEY(device_id,tenant_id) REFERENCES device(id,tenant_id),
|
||
|
|
CONSTRAINT firmware_assignment_rollout_device_key UNIQUE(rollout_id,device_id)
|
||
|
|
);
|
||
|
|
CREATE INDEX firmware_assignment_device_idx ON firmware_assignment(device_id,status,created_at DESC);
|
||
|
|
|
||
|
|
COMMIT;
|