Drop sbom_occurrences.pipeline_id and sbom_occurrences.commit_sha columns

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

• Close this issue

Summary

Following the resolution of #582961 (closed), we should remove the pipeline_id and commit_sha columns from the sbom_occurrences table. These columns were causing stale pipeline references and incorrect vulnerability context associations.

Context

In #582961 (closed), we solved the issue by getting tracked context information from the project instead of the pipeline. However, as noted in the discussion, it makes no sense for sbom_occurrence.pipeline to point to the first pipeline the occurrence appeared in.

Process

This effort must follow the database column removal process documented in doc/development/database/avoiding_downtime_in_migrations.md to ensure zero-downtime migrations.

The process requires three releases:

Release M: Ignore the columns

1. Identify all dependencies on sbom_occurrences.pipeline_id and sbom_occurrences.commit_sha

   • Search codebase for references to these columns
   • Identify all code paths that depend on them
   • Document each usage

2. Migrate dependent code to get information from the project instead

   • Update any queries or methods that rely on these columns
   • Ensure all functionality is preserved
   • Add tests for migrated code paths

3. Add ignore rules to the SbomOccurrence model

   class SbomOccurrence < ApplicationRecord
     ignore_columns %i[pipeline_id commit_sha], remove_with: 'M+2', remove_after: 'DATE'
   end

Release M+1: Drop the columns

1. Create a post-deployment migration to drop the columns

   • If there are no indexes or constraints on these columns, use a transactional migration
   • If there are indexes or constraints, use a non-transactional migration with disable_ddl_transaction!

2. Migration example:

   class RemoveSbomOccurrencesPipelineColumns < Gitlab::Database::Migration[2.1]
     def up
       remove_column :sbom_occurrences, :pipeline_id
       remove_column :sbom_occurrences, :commit_sha
     end
   
     def down
       add_column :sbom_occurrences, :pipeline_id, :bigint
       add_column :sbom_occurrences, :commit_sha, :string
     end
   end

Release M+2: Remove the ignore rules

1. Remove the ignore_columns directive from the SbomOccurrence model
2. Only merge after the remove_after date has passed

Related Issues

• #582961 (closed) - SBOM occurrences retain stale pipeline_id causing incorrect vulnerability context in GlobalAdvisoryScanWorker