Use security policy read model for compliance frameworks

Why are we doing this work

As a part of Use database read model for merge request appr... (&9971 - closed) we will be persisting the policies from YAML to security_policies table. Currently, when a policy is scoped with a compliance framework, we store the policy reference (by policy index in YAML file) and the framework in compliance_framework_security_policies. There is also security_policy_requirements table introduced as a part of Add relation between security policies and comp... (!163535 - merged).

As a part of this issue, we want to add security_policy_id to compliance_framework_security_policies and security_policy_requirements table to store the policy reference from DB instead of from YAML.

Relevant links

• Related Discussion: !163535 (comment 2077144377)

Non-functional requirements

• Documentation:
• Feature flag:
• Performance:
• Testing:

Implementation plan

• Add security_policy_id to compliance_framework_security_policies and populate the security policy in SecurityOrchestrationPolicies::ComplianceFrameworks::SyncService
• Create backfill migration to backfill security_policy_id to compliance_framework_security_policies table
• Lookup the compliance frameworks from namespace_ids like:

diff

diff --git a/ee/app/services/security/security_orchestration_policies/compliance_frameworks/sync_service.rb b/ee/app/services/security/security_orchestration_policies/compliance_frameworks/sync_service.rb
index 7ec2b2a587f57..8dab70c9f23a7 100644
--- a/ee/app/services/security/security_orchestration_policies/compliance_frameworks/sync_service.rb
+++ b/ee/app/services/security/security_orchestration_policies/compliance_frameworks/sync_service.rb
@@ -4,24 +4,26 @@ module Security
   module SecurityOrchestrationPolicies
     module ComplianceFrameworks
       class SyncService
+        include ::Gitlab::Utils::StrongMemoize
+
         def initialize(configuration)
           @configuration = configuration
         end
 
         def execute
-          container = configuration.source
           framework_ids_with_policy_index = configuration.compliance_framework_ids_with_policy_index
           framework_ids = framework_ids_with_policy_index.flat_map { |ids_with_idx| ids_with_idx[:framework_ids] }.uniq
 
-          root_namespace = container.root_ancestor
-          frameworks_count = root_namespace.compliance_management_frameworks.id_in(framework_ids).count
+          frameworks_count = linked_compliance_frameworks(framework_ids).count
 
           if frameworks_count != framework_ids.count
+            container = configuration.source
+
             Gitlab::AppJsonLogger.info(
               message: 'inaccessible compliance_framework_ids found in policy',
               configuration_id: configuration.id,
               configuration_source_id: container.id,
-              root_namespace_id: root_namespace.id,
+              root_namespace_ids: linked_source_root_namespaces.map(&:id),
               policy_framework_ids: framework_ids,
               inaccessible_framework_ids_count: (framework_ids.count - frameworks_count)
             )
@@ -45,6 +47,21 @@ def execute
         private
 
         attr_reader :configuration
+
+        def linked_source_root_namespaces
+          Security::OrchestrationPolicyConfiguration
+            .for_management_project(configuration.security_policy_management_project_id)
+            .with_project_and_namespace
+            .filter_map { |linked_configuration| linked_configuration.source&.root_ancestor }
+            .uniq
+        end
+        strong_memoize_attr :linked_source_root_namespaces
+
+        def linked_compliance_frameworks(framework_ids)
+          ComplianceManagement::Framework
+            .with_namespaces(linked_source_root_namespaces)
+            .id_in(framework_ids)
+        end
       end
     end
   end
diff --git a/ee/spec/services/security/security_orchestration_policies/compliance_frameworks/sync_service_spec.rb b/ee/spec/services/security/security_orchestration_policies/compliance_frameworks/sync_service_spec.rb
index 0f06494ed39c0..b572901b8874a 100644
--- a/ee/spec/services/security/security_orchestration_policies/compliance_frameworks/sync_service_spec.rb
+++ b/ee/spec/services/security/security_orchestration_policies/compliance_frameworks/sync_service_spec.rb
@@ -67,7 +67,7 @@
         message: 'inaccessible compliance_framework_ids found in policy',
         configuration_id: policy_configuration.id,
         configuration_source_id: policy_configuration.source.id,
-        root_namespace_id: namespace.id,
+        root_namespace_ids: [namespace.id],
         policy_framework_ids: [inaccessible_framework.id],
         inaccessible_framework_ids_count: 1
       ).and_call_original
@@ -90,7 +90,7 @@
         message: 'inaccessible compliance_framework_ids found in policy',
         configuration_id: policy_configuration.id,
         configuration_source_id: policy_configuration.source.id,
-        root_namespace_id: namespace.id,
+        root_namespace_ids: [namespace.id],
         policy_framework_ids: [non_existing_record_id],
         inaccessible_framework_ids_count: 1
       ).and_call_original
@@ -135,4 +135,34 @@
       expect(all_records.map(&:framework_id)).to contain_exactly(framework1.id, framework2.id)
     end
   end
+
+  context 'when multiple compliance frameworks from different groups are linked to different policies' do
+    let_it_be(:namespace2) { create(:group) }
+    let_it_be(:policy_configuration2) do
+      create(:security_orchestration_policy_configuration,
+        namespace: namespace2,
+        project: nil,
+        security_policy_management_project: policy_configuration.security_policy_management_project)
+    end
+
+    let_it_be(:framework3) { create(:compliance_framework, namespace: namespace2, name: 'SOX2') }
+
+    let(:framework_ids_and_idx) do
+      [
+        { framework_ids: [framework1.id], policy_index: 0 },
+        { framework_ids: [framework3.id], policy_index: 1 },
+        { framework_ids: [framework2.id], policy_index: 2 }
+      ]
+    end
+
+    it 'creates ComplianceFramework::SecurityPolicy' do
+      execute
+
+      expect(all_records.count).to eq(3)
+      expect(all_records.map(&:policy_index)).to contain_exactly(0, 1, 2)
+      expect(all_records.map(&:policy_configuration_id)).to contain_exactly(policy_configuration.id,
+        policy_configuration.id, policy_configuration.id)
+      expect(all_records.map(&:framework_id)).to contain_exactly(framework1.id, framework2.id, framework3.id)
+    end
+  end
 end

Verification steps