43 lines
2.0 KiB
PL/PgSQL
43 lines
2.0 KiB
PL/PgSQL
BEGIN;
|
|
|
|
CREATE TABLE firmware_assignment_event (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
assignment_id uuid NOT NULL REFERENCES firmware_assignment(id) ON DELETE CASCADE,
|
|
tenant_id uuid NOT NULL REFERENCES tenant(id),
|
|
device_id uuid NOT NULL,
|
|
command_id uuid NOT NULL REFERENCES device_command(id),
|
|
command_status varchar(24) NOT NULL CHECK(command_status IN ('ACKNOWLEDGED','SUCCEEDED','FAILED')),
|
|
assignment_status varchar(24) NOT NULL CHECK(assignment_status IN ('DOWNLOADING','INSTALLING','CONFIRMED','FAILED')),
|
|
phase text,
|
|
progress_percent integer CHECK(progress_percent BETWEEN 0 AND 100),
|
|
result_code text,
|
|
active_slot text,
|
|
target_slot text,
|
|
previous_slot text,
|
|
failed_slot text,
|
|
boot_attempt_id text,
|
|
boot_health_deadline text,
|
|
rollback_applied boolean,
|
|
rollback_version text,
|
|
installed_version text,
|
|
verified_sha256 text,
|
|
verified_signing_key_id text,
|
|
result_digest char(64) NOT NULL CHECK(result_digest ~ '^[0-9a-f]{64}$'),
|
|
result_summary jsonb NOT NULL CHECK(jsonb_typeof(result_summary)='object'),
|
|
reported_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT firmware_assignment_event_device_tenant_fk
|
|
FOREIGN KEY(device_id,tenant_id) REFERENCES device(id,tenant_id),
|
|
CONSTRAINT firmware_assignment_event_dedup_key
|
|
UNIQUE(assignment_id,command_status,result_digest)
|
|
);
|
|
|
|
CREATE INDEX firmware_assignment_event_timeline_idx
|
|
ON firmware_assignment_event(assignment_id,reported_at,id);
|
|
CREATE INDEX firmware_assignment_event_device_time_idx
|
|
ON firmware_assignment_event(tenant_id,device_id,reported_at DESC);
|
|
|
|
COMMENT ON TABLE firmware_assignment_event IS
|
|
'Immutable, deduplicated cloud receipt timeline for firmware download, staging, reboot, boot health confirmation and rollback evidence.';
|
|
|
|
COMMIT;
|