AULD: Migration

[JordanMontgomery]

Sub-task: AULD: DB migrations for software-update assets + per-host target

Related user story

#39085

Task

• Migration creating two tables:

CREATE TABLE `apple_software_update_assets` (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  class enum('macos','ios') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  product_version varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  build varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  posting_date date DEFAULT NULL,
  expiration_date date DEFAULT NULL,
  supported_devices json NOT NULL,
  first_seen_at datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  created_at datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY idx_asset_class_version_build (class, product_version, build)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE host_mdm_apple_os_updates (
  host_uuid varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  software_update_device_id varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  target_os_version varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  target_deadline datetime(6) DEFAULT NULL,
  resolved_at datetime(6) DEFAULT NULL,
  created_at datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`host_uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Notes for later subtasks:

• The 24h GDMF cron uses MAX(updated_at) on apple_software_update_assets (every successful fetch upserts the full current asset set, so updated_at advances even with no version change). first_seen_at is only set on insert.
• Migration registering FLEET_VAR_HOST_TARGET_OS_VERSION and FLEET_VAR_HOST_TARGET_OS_DEADLINE in fleet_variables (is_prefix=0).

Condition of satisfaction

• Both migrations have _test.go coverage and apply cleanly; schema.sql regenerated.
• The two new variables exist in fleet_variables.