-- Virt Cloud Go Agent control-plane schema v4. -- The filename remains v1 because it is the v1 API installer; the marker -- version is v4 after adding operation CAS and host/instance identity fences; -- it must not accept an -- older pre-CAS database or a UUID-only instance mutex. -- -- This is a fresh schema for the new panel <-> host-agent protocol. It is -- intentionally not an upgrade script: it does not read, decrypt, rename, or -- backfill any Python engine table or any legacy *_cipher column. Deploy it -- only in a database created for the new control plane. -- -- Business accounts, orders, billing periods, public-IP allocation and user -- authorization remain panel-owned. These tables contain only agent -- registration, desired/observed state and an auditable operation journal. CREATE TABLE cloud_agents ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, agent_id VARCHAR(96) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, host_id BIGINT UNSIGNED NOT NULL, endpoint VARCHAR(255) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, protocol_version VARCHAR(16) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, client_certificate_fingerprint CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, capabilities_json JSON NOT NULL, status VARCHAR(16) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, desired_revision BIGINT UNSIGNED NOT NULL DEFAULT 1, observed_revision BIGINT UNSIGNED NOT NULL DEFAULT 0, last_seen_at BIGINT UNSIGNED NULL, last_error_code VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '', created_at BIGINT UNSIGNED NOT NULL, updated_at BIGINT UNSIGNED NOT NULL, UNIQUE KEY cloud_agent_agent_id (agent_id), UNIQUE KEY cloud_agent_host_id (host_id), KEY cloud_agent_status (status, updated_at), KEY cloud_agent_heartbeat (last_seen_at, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Certificate rotation is explicit. The active overlap is represented as -- rows, so replacing a panel certificate never requires mutating an agent's -- identity or accepting an unpinned client during the transition. CREATE TABLE cloud_agent_client_certificates ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, agent_id VARCHAR(96) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, fingerprint CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, state VARCHAR(16) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, not_before BIGINT UNSIGNED NOT NULL, not_after BIGINT UNSIGNED NULL, created_at BIGINT UNSIGNED NOT NULL, updated_at BIGINT UNSIGNED NOT NULL, UNIQUE KEY cloud_agent_client_cert_identity (agent_id, fingerprint), KEY cloud_agent_client_cert_state (agent_id, state, not_after) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE cloud_agent_operations ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, operation_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, idempotency_key CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, agent_id VARCHAR(96) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, host_id BIGINT UNSIGNED NOT NULL, instance_uuid CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NULL, instance_id BIGINT UNSIGNED NULL, kind VARCHAR(48) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, target VARCHAR(128) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '', request_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, desired_json JSON NOT NULL, status VARCHAR(20) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, -- Monotonic CAS token for panel-side lease/state writes. It is separate -- from updated_at because second-resolution timestamps cannot fence two -- workers that claim the same operation concurrently. version BIGINT UNSIGNED NOT NULL DEFAULT 1, attempt_count INT UNSIGNED NOT NULL DEFAULT 0, lease_owner VARCHAR(96) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '', lease_expires_at BIGINT UNSIGNED NULL, commit_boundary VARCHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'not_started', result_json JSON NULL, error_code VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '', error_detail VARCHAR(512) NOT NULL DEFAULT '', created_at BIGINT UNSIGNED NOT NULL, updated_at BIGINT UNSIGNED NOT NULL, completed_at BIGINT UNSIGNED NULL, UNIQUE KEY cloud_agent_operation_id (operation_id), UNIQUE KEY cloud_agent_operation_idempotency (agent_id, idempotency_key), KEY cloud_agent_operation_instance (host_id, instance_id, instance_uuid, created_at), KEY cloud_agent_operation_readonly_retention (kind, status, created_at, id), KEY cloud_agent_operation_status (agent_id, status, updated_at), KEY cloud_agent_operation_lease (status, lease_expires_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE cloud_agent_instance_state ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, agent_id VARCHAR(96) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, host_id BIGINT UNSIGNED NOT NULL, instance_id BIGINT UNSIGNED NOT NULL, instance_uuid CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, desired_revision BIGINT UNSIGNED NOT NULL DEFAULT 1, desired_json JSON NOT NULL, observed_revision BIGINT UNSIGNED NOT NULL DEFAULT 0, observed_json JSON NULL, convergence_status VARCHAR(20) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, last_operation_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NULL, last_error_code VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '', created_at BIGINT UNSIGNED NOT NULL, updated_at BIGINT UNSIGNED NOT NULL, UNIQUE KEY cloud_agent_instance_identity (agent_id, host_id, instance_id, instance_uuid), KEY cloud_agent_instance_convergence (agent_id, convergence_status, updated_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Artifact metadata is plaintext operational state. Secret-bearing URLs and -- credentials are represented by a provider-side secret_ref, never copied -- into this database. CREATE TABLE cloud_agent_artifacts ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, agent_id VARCHAR(96) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, artifact_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, kind VARCHAR(24) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, source_ref VARCHAR(255) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '', secret_ref VARCHAR(128) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '', sha256 CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, bytes BIGINT UNSIGNED NOT NULL DEFAULT 0, state VARCHAR(20) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, created_at BIGINT UNSIGNED NOT NULL, updated_at BIGINT UNSIGNED NOT NULL, UNIQUE KEY cloud_agent_artifact_identity (agent_id, artifact_id), KEY cloud_agent_artifact_state (agent_id, state, updated_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- A fresh-schema marker prevents a same-named, incompatible table set from -- being mistaken for this protocol version. It is not a migration ledger and -- contains no legacy row or secret. The fingerprint is SHA-256 of this file -- after LF normalization and replacement of the marker literal below with 64 -- zeroes; the contract test recomputes that value to detect DDL drift. CREATE TABLE cloud_agent_schema_meta ( schema_name VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL PRIMARY KEY, schema_version INT UNSIGNED NOT NULL, schema_fingerprint CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, created_at BIGINT UNSIGNED NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO cloud_agent_schema_meta (schema_name, schema_version, schema_fingerprint, created_at) VALUES ('virt-cloud-agent', 4, '87a45391989171906c485383fa1f57fabad828adcb23ff01fe67cae99e7ed6e4', UNIX_TIMESTAMP());