Database tables and migrations Windows Profiles

[georgekarrv]

Context

Windows has "groups of settings" that can be applied to a host via commands. We call these "profiles" (macOS profiles are "groups of settings" as well.)

Profiles are set for a team or "no team".

Schema

Similar to Apple profiles, one table to track the team profiles, and another one to track the status of the profile for each host.

[!WARNING] The tables below are a rough draft, the person assigned to the issue should give them the final form.

Migration statements

-- track the team/no-team profiles. Acts as a "blueprint" of sorts
CREATE TABLE `mdm_microsoft_configuration_profiles` (
  `profile_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `team_id` int(10) unsigned NOT NULL DEFAULT '0',
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `syncml` blob NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`profile_id`),
  UNIQUE KEY `idx_mdm_microsoft_config_prof_team_name` (`team_id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- track the status of each profile for each host
CREATE TABLE `host_mdm_microsoft_profiles` (
  `profile_id` int(10) unsigned NOT NULL,
  `host_uuid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `operation_type` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `detail` text COLLATE utf8mb4_unicode_ci,
  `command_uuid` varchar(127) COLLATE utf8mb4_unicode_ci NOT NULL,
  `profile_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `retries` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`host_uuid`,`profile_id`),
  KEY `status` (`status`),
  KEY `operation_type` (`operation_type`),
  -- TODO: update operation_types and delivery_status tables to drop "apple" in the name
  CONSTRAINT `host_mdm_microsoft_profiles_ibfk_1` FOREIGN KEY (`status`) REFERENCES `mdm_apple_delivery_status` (`status`) ON UPDATE CASCADE,
  CONSTRAINT `host_mdm_microsoft_profiles_ibfk_2` FOREIGN KEY (`operation_type`) REFERENCES `mdm_apple_operation_types` (`operation_type`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Pros

• No need to refactor all the existent profile delivery logic due to subtle details between macOS and Windows
• Too early to understand if we're building a leaky abstraction

Cons

• Many duplicated columns
• Extra JOIN for joint reports (profile summary for host, filter by state, etc)

In a team meeting, we decided to not repurpose the existing apple tables and go with separate ones like it's outlined here instead

[georgekarrv]

Option: A

[georgekarrv]

Hey team! Please add your planning poker estimate with Zenhub @mna @roperzh @marcosd4h @gillespi314

[fleet-release]

Windows profiles bloom, In a cloud city of code, A team's dream takes form.