86 lines
4.0 KiB
PL/PgSQL
86 lines
4.0 KiB
PL/PgSQL
|
BEGIN;
|
||
|
|
|
||
|
|
CREATE TABLE hardware_agent_license (
|
||
|
|
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),
|
||
|
|
provider varchar(40) NOT NULL DEFAULT 'volcengine',
|
||
|
|
instance_id varchar(200) NOT NULL,
|
||
|
|
product_key varchar(200) NOT NULL,
|
||
|
|
license_ref varchar(200) NOT NULL UNIQUE,
|
||
|
|
payload_digest varchar(64) NOT NULL CHECK (payload_digest ~ '^[0-9a-f]{64}$'),
|
||
|
|
encrypted_payload bytea NOT NULL,
|
||
|
|
nonce bytea NOT NULL,
|
||
|
|
status varchar(24) NOT NULL DEFAULT 'AVAILABLE'
|
||
|
|
CHECK (status IN ('AVAILABLE','ALLOCATED','ACTIVE','SUSPENDED','EXPIRED','REVOKED')),
|
||
|
|
allocated_device_id uuid,
|
||
|
|
allocated_at timestamptz,
|
||
|
|
activated_at timestamptz,
|
||
|
|
expires_at timestamptz NOT NULL,
|
||
|
|
released_at timestamptz,
|
||
|
|
revoked_at timestamptz,
|
||
|
|
last_error_code varchar(100),
|
||
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
version bigint NOT NULL DEFAULT 1 CHECK (version > 0),
|
||
|
|
CONSTRAINT hardware_agent_license_device_tenant_fk
|
||
|
|
FOREIGN KEY (allocated_device_id, tenant_id) REFERENCES device(id,tenant_id),
|
||
|
|
CONSTRAINT hardware_agent_license_assignment_check CHECK (
|
||
|
|
(status='AVAILABLE' AND allocated_device_id IS NULL) OR
|
||
|
|
(status IN ('ALLOCATED','ACTIVE','SUSPENDED') AND allocated_device_id IS NOT NULL) OR
|
||
|
|
status IN ('EXPIRED','REVOKED')
|
||
|
|
)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE UNIQUE INDEX hardware_agent_license_device_active_key
|
||
|
|
ON hardware_agent_license(allocated_device_id)
|
||
|
|
WHERE status IN ('ALLOCATED','ACTIVE','SUSPENDED');
|
||
|
|
CREATE INDEX hardware_agent_license_pool_idx
|
||
|
|
ON hardware_agent_license(tenant_id,provider,instance_id,product_key,status,expires_at);
|
||
|
|
|
||
|
|
CREATE FUNCTION enforce_hardware_license_device_state() RETURNS trigger LANGUAGE plpgsql AS $$
|
||
|
|
DECLARE
|
||
|
|
changed_revision bigint;
|
||
|
|
BEGIN
|
||
|
|
IF NEW.allocated_device_id IS NOT NULL
|
||
|
|
AND NEW.status IN ('SUSPENDED','EXPIRED','REVOKED')
|
||
|
|
AND OLD.status IS DISTINCT FROM NEW.status THEN
|
||
|
|
UPDATE device
|
||
|
|
SET activation_state='PROVISIONING',activation_revision=activation_revision+1,
|
||
|
|
activation_reason='license-' || lower(NEW.status),activation_updated_at=now(),version=version+1,updated_at=now()
|
||
|
|
WHERE id=NEW.allocated_device_id AND tenant_id=NEW.tenant_id AND activation_state='ACTIVE'
|
||
|
|
RETURNING activation_revision INTO changed_revision;
|
||
|
|
IF changed_revision IS NOT NULL THEN
|
||
|
|
INSERT INTO device_activation_transition(
|
||
|
|
tenant_id,device_id,from_state,to_state,reason,source,actor_type,activation_revision
|
||
|
|
) VALUES(
|
||
|
|
NEW.tenant_id,NEW.allocated_device_id,'ACTIVE','PROVISIONING',
|
||
|
|
'硬件智能体License状态变为' || NEW.status,'license-state-trigger','system',changed_revision
|
||
|
|
);
|
||
|
|
END IF;
|
||
|
|
END IF;
|
||
|
|
RETURN NEW;
|
||
|
|
END;
|
||
|
|
$$;
|
||
|
|
|
||
|
|
CREATE TRIGGER hardware_agent_license_device_state_trigger
|
||
|
|
AFTER UPDATE OF status ON hardware_agent_license
|
||
|
|
FOR EACH ROW EXECUTE FUNCTION enforce_hardware_license_device_state();
|
||
|
|
|
||
|
|
WITH transitioned AS (
|
||
|
|
UPDATE device d
|
||
|
|
SET activation_state='PROVISIONING',activation_revision=activation_revision+1,
|
||
|
|
activation_reason='license-required-v51',activation_updated_at=now(),version=version+1,updated_at=now()
|
||
|
|
FROM device_twin dt
|
||
|
|
WHERE dt.device_id=d.id AND d.status='registered' AND d.activation_state='ACTIVE'
|
||
|
|
AND COALESCE(dt.desired_config->>'voice_runtime','legacy_voicechat')='volc_hardware_agent'
|
||
|
|
RETURNING d.tenant_id,d.id,d.activation_revision
|
||
|
|
)
|
||
|
|
INSERT INTO device_activation_transition(
|
||
|
|
tenant_id,device_id,from_state,to_state,reason,source,actor_type,activation_revision
|
||
|
|
)
|
||
|
|
SELECT tenant_id,id,'ACTIVE','PROVISIONING','硬件智能体必须分配并确认有效License','migration-51','system',activation_revision
|
||
|
|
FROM transitioned;
|
||
|
|
|
||
|
|
COMMIT;
|