Add deleted_at column to security_scan_profiles

rossfuhrmanrequested to merge
607385-add-deleted-at-to-security-scan-profiles into master

What does this MR do?

Adds a nullable deleted_at timestamp column and a partial index index_security_scan_profiles_on_namespace_id_where_not_deleted (WHERE deleted_at IS NULL) to the security_scan_profiles table.

This is step 1 of adding a SecurityScanProfileDelete GraphQL mutation. A Security::ScanProfile can be attached to thousands of projects through security_scan_profiles_projects. To keep the delete request cheap, we adopt the Security::Attribute soft-delete pattern: the upcoming mutation will set deleted_at synchronously (hiding the profile immediately) and enqueue a background worker to clean up the join records and hard-delete the row. This MR only lays the schema groundwork — the column is not read or written yet.

Migration

Modeled on db/migrate/20251016105900_add_deleted_at_to_security_attributes.rb:

  • add_column :security_scan_profiles, :deleted_at, :datetime_with_timezone
  • add_concurrent_index :security_scan_profiles, :namespace_id, where: 'deleted_at IS NULL', name: 'index_security_scan_profiles_on_namespace_id_where_not_deleted'

Regular (not post-deploy) migration, since it adds a column. security_scan_profiles is a small configuration table (roughly one profile per namespace and scan type). Verified reversible locally with db:rollback followed by re-migrate.

The existing unique index index_security_scan_profiles_namespace_scan_type_name is left unchanged on purpose; scoping name uniqueness to non-deleted rows is deferred to a later step to avoid disturbing the ON CONFLICT target used by FindOrCreateService.

Related

Merge request reports