Codex Local
bf1233d5df
ci / verify (push) Canceled after 0s
Add customer and operator login, BLE provisioning, device fleet controls, call memory, memoir generation, and refreshed family and maintenance portals. Include API contracts, migrations, firmware sources, tests, assets, and deployment documentation.
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');
|