Move SBOMs into their own table

[andrew]

The sbom column in the versions table is very large and makes performance pretty painful in certain places, so I want to extract it into it's own table.

There's 300GB+ of data in the versions table atm, so it'll need to be done in stages, this issue is for tracking the progress and steps to complete it.

[andrew]

SBOM Migration Task List

Phase 1: Preparation and Testing

• Review and test the current SBOM functionality
• Run test suite: bundle exec rails test test/models/version_test.rb
• Backup the database (especially the versions table)
• Test the migration in a staging environment

Phase 2: Deploy Infrastructure

• Deploy code with new models and dual-mode support
• Run migrations to create new tables and columns:

  bundle exec rails db:migrate

• Verify application still works with existing SBOM data

Phase 3: Data Migration

• Start the background migration:

  bundle exec rails sbom:migrate

• Monitor migration progress:

  bundle exec rails sbom:status

• Periodically verify data integrity:

  bundle exec rails sbom:verify

Phase 4: Cleanup

• Remove old sbom column from versions table: remove_column :versions, :sbom, :json
• Clean up Version model by removing fallback code:
  • Remove self.sbom = json line from parse_sbom method
  • Remove sbom: nil from error handling in parse_sbom
  • Remove fallback code from distro, syft_version, and purls methods
  • Remove || sbom.present? from has_sbom? method
  • Update sbom_data to only return sbom_record&.data
• Run tests again to ensure cleanup didn't break anything
• Deploy

Phase 6: Post-Migration

• Monitor application performance improvements
• Update any documentation that references the old sbom column
• Remove migration worker and rake tasks

[andrew]

Running this now, but it's pretty hard on the server!