Add license information related to sbom_occurrences

Why are we doing this work

The current implementation of the dependency list (project-level) supports licenses. We want to extend the recent sbom related finder, which supports both project and group levels to also include license information. Currently, finder is being used by this graphql resolver on a project level and by this controller on a group level.

As discussed here, assuming that the record on the following tables are consistent: pm_packages, pm_package_versions, pm_package_version_licenses and pm_licenses, those could be used in order to fetch a spdx_identifier to a specific sbom_occurrences record.

If we were to consider only project level changes, an approach similar to the following could be considered:

diff --git a/ee/app/models/sbom/occurrence.rb b/ee/app/models/sbom/occurrence.rb
index 0538a72befba..e89b3aa51ddd 100644
--- a/ee/app/models/sbom/occurrence.rb
+++ b/ee/app/models/sbom/occurrence.rb
@@ -49,6 +49,16 @@ class Occurrence < ApplicationRecord
     scope :with_source, -> { includes(:source) }
     scope :with_version, -> { includes(:component_version) }

+    scope :with_licenses, -> {
+      select('sbom_occurrences.*, pm_licenses.spdx_identifier as spdx_identifier')
+        .joins(:component, :component_version)
+        .joins('LEFT JOIN pm_packages ON pm_packages.purl_type = sbom_components.purl_type AND pm_packages.name = sbom_components.name')
+        .joins('LEFT JOIN pm_package_versions ON pm_package_versions.pm_package_id = pm_packages.id AND pm_package_versions.version = sbom_component_versions.version')
+        .joins('LEFT JOIN pm_package_version_licenses ON pm_package_version_licenses.pm_package_version_id = pm_package_versions.id')
+        .joins('LEFT JOIN pm_licenses ON pm_licenses.id = pm_package_version_licenses.pm_license_id')
+        .order('sbom_occurrences.id ASC')
+  }
+
     def location
       {
         blob_path: input_file_blob_path,

However the existing complexity of the group level query and the related performance concerns, I would propose having either pm_license_id or the spdx_identifier as part of sbom_occurrences table.

Keeping in mind that this implementation directly affects Post-MVC Group/Sub-group level Dependency List (&10090), having @mokhax and @dpisek as part of this decision seems to be extremely valuable.

I would propose the following steps:

• Have a consensus on the proposed approach
• Verify the performance impact of the proposed solution
• Implement the solution behind a new feature flag (Thanks for suggesting the FF @mokhax )

/cc @thiagocsf

Relevant links

Non-functional requirements

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

Implementation plan

Verification steps