From b58066a648ea43f04599ee656448d293a4867972 Mon Sep 17 00:00:00 2001
From: Gregory Havenga <11164960-ghavenga@users.noreply.gitlab.com>
Date: Wed, 1 Oct 2025 15:39:09 +0200
Subject: [PATCH] Add detected_at column to vulnerability_occurrences table

This migration adds the `detected_at` timestamp column to the
`vulnerability_occurrences` table as specified in issue
https://gitlab.com/gitlab-org/gitlab/-/issues/556251.

The column is defined as:
- `detected_at timestamp with time zone DEFAULT now()`

This change supports the effort to enable tracking vulnerabilities
across multiple contexts by moving ref-contextual data from the
higher-level `vulnerabilities` table to the `vulnerability_occurrences`
table where it belongs.

Changelog: added
---
 ...etected_at_to_vulnerability_occurrences.rb | 21 +++++++++++++++++++
 db/schema_migrations/20251001133644           |  1 +
 db/structure.sql                              |  1 +
 3 files changed, 23 insertions(+)
 create mode 100644 db/migrate/20251001133644_add_detected_at_to_vulnerability_occurrences.rb
 create mode 100644 db/schema_migrations/20251001133644

diff --git a/db/migrate/20251001133644_add_detected_at_to_vulnerability_occurrences.rb b/db/migrate/20251001133644_add_detected_at_to_vulnerability_occurrences.rb
new file mode 100644
index 00000000000000..5709bde047ff6f
--- /dev/null
+++ b/db/migrate/20251001133644_add_detected_at_to_vulnerability_occurrences.rb
@@ -0,0 +1,21 @@
+# frozen_string_literal: true
+
+class AddDetectedAtToVulnerabilityOccurrences < Gitlab::Database::Migration[2.3]
+  milestone '18.6'
+
+  disable_ddl_transaction!
+
+  def up
+    # rubocop:disable Migration/PreventAddingColumns -- The oversized nature of this table will be addressed through planned partitioning
+    with_lock_retries do
+      add_column :vulnerability_occurrences, :detected_at, :timestamptz, if_not_exists: true
+    end
+    # rubocop:enable Migration/PreventAddingColumns
+  end
+
+  def down
+    with_lock_retries do
+      remove_column :vulnerability_occurrences, :detected_at, if_exists: true
+    end
+  end
+end
diff --git a/db/schema_migrations/20251001133644 b/db/schema_migrations/20251001133644
new file mode 100644
index 00000000000000..1219ce578b07b0
--- /dev/null
+++ b/db/schema_migrations/20251001133644
@@ -0,0 +1 @@
+55004e8a0c4820c7fb30966331ea2af35c900e150421d67dfbd04641ce63bcf7
\ No newline at end of file
diff --git a/db/structure.sql b/db/structure.sql
index 022d5f53aef668..055b26038ea39a 100644
--- a/db/structure.sql
+++ b/db/structure.sql
@@ -28394,6 +28394,7 @@ CREATE TABLE vulnerability_occurrences (
     initial_pipeline_id bigint,
     latest_pipeline_id bigint,
     security_project_tracked_context_id bigint,
+    detected_at timestamp with time zone,
     CONSTRAINT check_4a3a60f2ba CHECK ((char_length(solution) <= 7000)),
     CONSTRAINT check_ade261da6b CHECK ((char_length(description) <= 15000)),
     CONSTRAINT check_f602da68dd CHECK ((char_length(cve) <= 48400))
-- 
GitLab