Update Vulnerabilities GraphQL API filters
What does this MR do and why?
Adds date range filters support for detected_at (before | after) and created_at (before | after) for Vulnerabilities GraphQL, so it can be used in the security.py tool in agentic workflows.
It adds the dates scopes to the Vulnerability:Read models as well.
Queries:
EXPLAIN SELECT "vulnerability_reads"."id", "vulnerability_reads"."vulnerability_id", "vulnerability_reads"."project_id", "vulnerability_reads"."scanner_id", "vulnerability_reads"."report_type", "vulnerability_reads"."severity", "vulnerability_reads"."state", "vulnerability_reads"."has_issues", "vulnerability_reads"."resolved_on_default_branch", "vulnerability_reads"."uuid", "vulnerability_reads"."location_image", "vulnerability_reads"."cluster_agent_id", "vulnerability_reads"."casted_cluster_agent_id", "vulnerability_reads"."dismissal_reason", "vulnerability_reads"."has_merge_request", "vulnerability_reads"."has_remediations", "vulnerability_reads"."owasp_top_10", "vulnerability_reads"."traversal_ids", "vulnerability_reads"."archived", "vulnerability_reads"."identifier_names", "vulnerability_reads"."has_vulnerability_resolution", "vulnerability_reads"."auto_resolved" FROM "vulnerability_reads" INNER JOIN "vulnerabilities" ON "vulnerabilities"."id" = "vulnerability_reads"."vulnerability_id" WHERE "vulnerabilities"."detected_at" >= '2025-07-01 00:00:00'
EXPLAIN SELECT "vulnerability_reads"."id", "vulnerability_reads"."vulnerability_id", "vulnerability_reads"."project_id", "vulnerability_reads"."scanner_id", "vulnerability_reads"."report_type", "vulnerability_reads"."severity", "vulnerability_reads"."state", "vulnerability_reads"."has_issues", "vulnerability_reads"."resolved_on_default_branch", "vulnerability_reads"."uuid", "vulnerability_reads"."location_image", "vulnerability_reads"."cluster_agent_id", "vulnerability_reads"."casted_cluster_agent_id", "vulnerability_reads"."dismissal_reason", "vulnerability_reads"."has_merge_request", "vulnerability_reads"."has_remediations", "vulnerability_reads"."owasp_top_10", "vulnerability_reads"."traversal_ids", "vulnerability_reads"."archived", "vulnerability_reads"."identifier_names", "vulnerability_reads"."has_vulnerability_resolution", "vulnerability_reads"."auto_resolved" FROM "vulnerability_reads" INNER JOIN "vulnerabilities" ON "vulnerabilities"."id" = "vulnerability_reads"."vulnerability_id" WHERE "vulnerabilities"."detected_at" <= '2025-07-01 00:00:00'
EXPLAIN SELECT "vulnerability_reads"."id", "vulnerability_reads"."vulnerability_id", "vulnerability_reads"."project_id", "vulnerability_reads"."scanner_id", "vulnerability_reads"."report_type", "vulnerability_reads"."severity", "vulnerability_reads"."state", "vulnerability_reads"."has_issues", "vulnerability_reads"."resolved_on_default_branch", "vulnerability_reads"."uuid", "vulnerability_reads"."location_image", "vulnerability_reads"."cluster_agent_id", "vulnerability_reads"."casted_cluster_agent_id", "vulnerability_reads"."dismissal_reason", "vulnerability_reads"."has_merge_request", "vulnerability_reads"."has_remediations", "vulnerability_reads"."owasp_top_10", "vulnerability_reads"."traversal_ids", "vulnerability_reads"."archived", "vulnerability_reads"."identifier_names", "vulnerability_reads"."has_vulnerability_resolution", "vulnerability_reads"."auto_resolved" FROM "vulnerability_reads" INNER JOIN "vulnerabilities" ON "vulnerabilities"."id" = "vulnerability_reads"."vulnerability_id" WHERE "vulnerabilities"."created_at" >= '2025-07-01 00:00:00'
EXPLAIN SELECT "vulnerability_reads"."id", "vulnerability_reads"."vulnerability_id", "vulnerability_reads"."project_id", "vulnerability_reads"."scanner_id", "vulnerability_reads"."report_type", "vulnerability_reads"."severity", "vulnerability_reads"."state", "vulnerability_reads"."has_issues", "vulnerability_reads"."resolved_on_default_branch", "vulnerability_reads"."uuid", "vulnerability_reads"."location_image", "vulnerability_reads"."cluster_agent_id", "vulnerability_reads"."casted_cluster_agent_id", "vulnerability_reads"."dismissal_reason", "vulnerability_reads"."has_merge_request", "vulnerability_reads"."has_remediations", "vulnerability_reads"."owasp_top_10", "vulnerability_reads"."traversal_ids", "vulnerability_reads"."archived", "vulnerability_reads"."identifier_names", "vulnerability_reads"."has_vulnerability_resolution", "vulnerability_reads"."auto_resolved" FROM "vulnerability_reads" INNER JOIN "vulnerabilities" ON "vulnerabilities"."id" = "vulnerability_reads"."vulnerability_id" WHERE "vulnerabilities"."created_at" <= '2025-07-01 00:00:00'
Sample Query Plan
[
{
"Plan": {
"Node Type": "Hash Join",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 34.86,
"Total Cost": 88.54,
"Plan Rows": 838,
"Plan Width": 151,
"Actual Startup Time": 0.140,
"Actual Total Time": 0.459,
"Actual Rows": 838,
"Actual Loops": 1,
"Output": ["vulnerability_reads.id", "vulnerability_reads.vulnerability_id", "vulnerability_reads.project_id", "vulnerability_reads.scanner_id", "vulnerability_reads.report_type", "vulnerability_reads.severity", "vulnerability_reads.state", "vulnerability_reads.has_issues", "vulnerability_reads.resolved_on_default_branch", "vulnerability_reads.uuid", "vulnerability_reads.location_image", "vulnerability_reads.cluster_agent_id", "vulnerability_reads.casted_cluster_agent_id", "vulnerability_reads.dismissal_reason", "vulnerability_reads.has_merge_request", "vulnerability_reads.has_remediations", "vulnerability_reads.owasp_top_10", "vulnerability_reads.traversal_ids", "vulnerability_reads.archived", "vulnerability_reads.identifier_names", "vulnerability_reads.has_vulnerability_resolution", "vulnerability_reads.auto_resolved"],
"Inner Unique": true,
"Hash Cond": "(vulnerabilities.id = vulnerability_reads.vulnerability_id)",
"Shared Hit Blocks": 57,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "vulnerabilities",
"Schema": "public",
"Alias": "vulnerabilities",
"Startup Cost": 0.00,
"Total Cost": 51.48,
"Plan Rows": 838,
"Plan Width": 8,
"Actual Startup Time": 0.004,
"Actual Total Time": 0.204,
"Actual Rows": 838,
"Actual Loops": 1,
"Output": ["vulnerabilities.id", "vulnerabilities.project_id", "vulnerabilities.author_id", "vulnerabilities.created_at", "vulnerabilities.updated_at", "vulnerabilities.title", "vulnerabilities.title_html", "vulnerabilities.description", "vulnerabilities.description_html", "vulnerabilities.state", "vulnerabilities.severity", "vulnerabilities.severity_overridden", "vulnerabilities.resolved_by_id", "vulnerabilities.resolved_at", "vulnerabilities.report_type", "vulnerabilities.cached_markdown_version", "vulnerabilities.confirmed_by_id", "vulnerabilities.confirmed_at", "vulnerabilities.dismissed_at", "vulnerabilities.dismissed_by_id", "vulnerabilities.resolved_on_default_branch", "vulnerabilities.present_on_default_branch", "vulnerabilities.detected_at", "vulnerabilities.finding_id", "vulnerabilities.cvss", "vulnerabilities.auto_resolved"],
"Filter": "(vulnerabilities.detected_at >= '2025-07-01 00:00:00+00'::timestamp with time zone)",
"Rows Removed by Filter": 0,
"Shared Hit Blocks": 41,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 24.38,
"Total Cost": 24.38,
"Plan Rows": 838,
"Plan Width": 151,
"Actual Startup Time": 0.128,
"Actual Total Time": 0.128,
"Actual Rows": 838,
"Actual Loops": 1,
"Output": ["vulnerability_reads.id", "vulnerability_reads.vulnerability_id", "vulnerability_reads.project_id", "vulnerability_reads.scanner_id", "vulnerability_reads.report_type", "vulnerability_reads.severity", "vulnerability_reads.state", "vulnerability_reads.has_issues", "vulnerability_reads.resolved_on_default_branch", "vulnerability_reads.uuid", "vulnerability_reads.location_image", "vulnerability_reads.cluster_agent_id", "vulnerability_reads.casted_cluster_agent_id", "vulnerability_reads.dismissal_reason", "vulnerability_reads.has_merge_request", "vulnerability_reads.has_remediations", "vulnerability_reads.owasp_top_10", "vulnerability_reads.traversal_ids", "vulnerability_reads.archived", "vulnerability_reads.identifier_names", "vulnerability_reads.has_vulnerability_resolution", "vulnerability_reads.auto_resolved"],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 114,
"Shared Hit Blocks": 16,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "vulnerability_reads",
"Schema": "public",
"Alias": "vulnerability_reads",
"Startup Cost": 0.00,
"Total Cost": 24.38,
"Plan Rows": 838,
"Plan Width": 151,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.081,
"Actual Rows": 838,
"Actual Loops": 1,
"Output": ["vulnerability_reads.id", "vulnerability_reads.vulnerability_id", "vulnerability_reads.project_id", "vulnerability_reads.scanner_id", "vulnerability_reads.report_type", "vulnerability_reads.severity", "vulnerability_reads.state", "vulnerability_reads.has_issues", "vulnerability_reads.resolved_on_default_branch", "vulnerability_reads.uuid", "vulnerability_reads.location_image", "vulnerability_reads.cluster_agent_id", "vulnerability_reads.casted_cluster_agent_id", "vulnerability_reads.dismissal_reason", "vulnerability_reads.has_merge_request", "vulnerability_reads.has_remediations", "vulnerability_reads.owasp_top_10", "vulnerability_reads.traversal_ids", "vulnerability_reads.archived", "vulnerability_reads.identifier_names", "vulnerability_reads.has_vulnerability_resolution", "vulnerability_reads.auto_resolved"],
"Shared Hit Blocks": 16,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
},
"Planning": {
"Shared Hit Blocks": 16,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
"Planning Time": 0.220,
"Triggers": [
],
"Execution Time": 0.489
}
]References
How to set up and validate locally
- Seed vulnerabilities by using a script or if there's a vulnerability seeder:
bin/rake 'gitlab:seed:vulnerabilities[group/project]'
script using factories/in rails console
def cleanup_corrupted_vulnerabilities(project)
puts "Checking for corrupted vulnerabilities..."
corrupted_count = 0
project.vulnerabilities.includes(:findings).each do |vulnerability|
if vulnerability.findings.any? { |finding| finding.scanner.nil? }
puts " Removing corrupted vulnerability ID #{vulnerability.id}"
vulnerability.destroy
corrupted_count += 1
end
end
if corrupted_count > 0
puts " Removed #{corrupted_count} corrupted vulnerabilities"
else
puts " No corrupted vulnerabilities found"
end
end
def get_or_create_test_user(project)
@test_user ||= User.find_by(email: 'vulnerability-test@example.com') ||
FactoryBot.create(:user,
email: 'vulnerability-test@example.com',
username: "vuln-test-#{SecureRandom.hex(4)}",
name: 'Vulnerability Test User'
)
end
def create_finding_for_type(report_type, project, date)
timestamp = Time.current.to_f
random_suffix = SecureRandom.hex(8)
scanner = FactoryBot.create(:vulnerabilities_scanner,
project: project,
external_id: "#{report_type}_scanner_#{timestamp}_#{random_suffix}",
name: "#{report_type.humanize} Scanner"
)
case report_type
when 'secret_detection'
# Secret detection trait creates its own identifiers, so we don't pre-create primary_identifier
primary_identifier = FactoryBot.create(:vulnerabilities_identifier,
project: project,
external_type: 'gitleaks_rule_id',
external_id: "gitleaks-#{timestamp}-#{random_suffix}",
name: "Gitleaks rule ID #{random_suffix}",
fingerprint: SecureRandom.hex(20)
)
FactoryBot.create(:vulnerabilities_finding,
project: project,
scanner: scanner,
primary_identifier: primary_identifier,
report_type: report_type,
name: "Secret detected",
severity: :critical,
created_at: date,
updated_at: date
)
when 'dependency_scanning'
primary_identifier = FactoryBot.create(:vulnerabilities_identifier,
project: project,
external_id: "#{report_type}-#{timestamp}-#{random_suffix}",
name: "#{report_type.upcase}-#{random_suffix}"
)
FactoryBot.create(:vulnerabilities_finding, :with_dependency_scanning_metadata,
project: project,
scanner: scanner,
primary_identifier: primary_identifier,
report_type: report_type,
created_at: date,
updated_at: date
)
when 'container_scanning'
primary_identifier = FactoryBot.create(:vulnerabilities_identifier,
project: project,
external_id: "#{report_type}-#{timestamp}-#{random_suffix}",
name: "#{report_type.upcase}-#{random_suffix}"
)
FactoryBot.create(:vulnerabilities_finding, :with_container_scanning_metadata,
project: project,
scanner: scanner,
primary_identifier: primary_identifier,
report_type: report_type,
created_at: date,
updated_at: date
)
when 'cluster_image_scanning'
primary_identifier = FactoryBot.create(:vulnerabilities_identifier,
project: project,
external_id: "#{report_type}-#{timestamp}-#{random_suffix}",
name: "#{report_type.upcase}-#{random_suffix}"
)
FactoryBot.create(:vulnerabilities_finding, :with_cluster_image_scanning_scanning_metadata,
project: project,
scanner: scanner,
primary_identifier: primary_identifier,
report_type: report_type,
created_at: date,
updated_at: date
)
else
primary_identifier = FactoryBot.create(:vulnerabilities_identifier,
project: project,
external_id: "#{report_type}-#{timestamp}-#{random_suffix}",
name: "#{report_type.upcase}-#{random_suffix}"
)
FactoryBot.create(:vulnerabilities_finding,
project: project,
scanner: scanner,
primary_identifier: primary_identifier,
report_type: report_type,
created_at: date,
updated_at: date
)
end
end
def create_vulnerability_statistics(project)
existing_stat = Vulnerabilities::Statistic.find_by(project: project)
if existing_stat
puts " Updating existing vulnerability statistic"
existing_stat.touch
else
puts " Creating new vulnerability statistic"
FactoryBot.create(:vulnerability_statistic, project: project)
end
puts " Creating historical statistics..."
3.times do |i|
date = i.months.ago.end_of_month
existing_historical = Vulnerabilities::HistoricalStatistic.find_by(project: project, date: date)
if existing_historical
puts " Skipping historical stat for #{date} (already exists)"
next
end
critical_count = project.vulnerabilities.where(severity: 'critical').count
high_count = project.vulnerabilities.where(severity: 'high').count
medium_count = project.vulnerabilities.where(severity: 'medium').count
low_count = project.vulnerabilities.where(severity: 'low').count
FactoryBot.create(:vulnerability_historical_statistic,
project: project,
date: date,
total: critical_count + high_count + medium_count + low_count,
critical: critical_count,
high: high_count,
medium: medium_count,
low: low_count
)
puts " Created historical stat for #{date}"
end
end
def verify_vulnerabilities(project)
issues = []
project.vulnerabilities.includes(:findings).each do |vulnerability|
if vulnerability.findings.empty?
issues << "Vulnerability #{vulnerability.id} has no findings"
end
vulnerability.findings.each do |finding|
if finding.scanner.nil?
issues << "Finding #{finding.id} has no scanner"
elsif finding.scanner.project_id != project.id
issues << "Finding #{finding.id} scanner belongs to wrong project"
end
if finding.primary_identifier.nil?
issues << "Finding #{finding.id} has no primary identifier"
end
end
end
if issues.any?
puts " ⚠️ Found #{issues.count} data integrity issues:"
issues.each { |issue| puts " - #{issue}" }
else
puts " ✓ All vulnerabilities have proper associations"
end
end
def create_vulnerabilities_for_project!
project = Project.find_by_full_path('gitlab-duo/test')
unless project
puts "Project 'gitlab-duo/test' not found!"
return
end
puts "Cleaning up existing vulnerabilities for project: #{project.full_path}"
cleanup_corrupted_vulnerabilities(project)
puts "Cleaning up existing statistics..."
Vulnerabilities::HistoricalStatistic.where(project: project).destroy_all
Vulnerabilities::Statistic.where(project: project).destroy_all
puts "Cleaning up existing scanners and identifiers..."
Vulnerabilities::Scanner.where(project: project).destroy_all
Vulnerabilities::Identifier.where(project: project).destroy_all
project.vulnerabilities.destroy_all
puts "Creating vulnerabilities for project: #{project.full_path}"
test_user = get_or_create_test_user(project)
report_types = %w[sast dast dependency_scanning container_scanning secret_detection
cluster_image_scanning coverage_fuzzing api_fuzzing generic]
states = %w[detected confirmed dismissed resolved]
report_types.each do |report_type|
puts "Creating #{report_type} vulnerabilities..."
rand(2..4).times do
state = states.sample
random_date = rand(1.month.ago..Time.current)
severity = %w[critical high medium low info].sample
begin
finding = create_finding_for_type(report_type, project, random_date)
vulnerability = FactoryBot.create(:vulnerability, state.to_sym,
project: project,
author: test_user,
vulnerability_finding: finding,
findings: [finding],
report_type: report_type,
severity: severity,
created_at: random_date,
updated_at: random_date,
detected_at: random_date
)
case state
when 'confirmed'
vulnerability.update!(confirmed_at: random_date + rand(1..7).days, confirmed_by: test_user)
when 'dismissed'
vulnerability.update!(dismissed_at: random_date + rand(1..7).days, dismissed_by: test_user)
when 'resolved'
vulnerability.update!(resolved_at: random_date + rand(1..7).days, resolved_by: test_user)
end
puts " ✓ Created #{state} #{report_type} vulnerability (ID: #{vulnerability.id}) - #{severity} severity"
rescue => e
puts " ✗ Failed to create #{report_type} vulnerability: #{e.message}"
puts " #{e.backtrace.first}" if e.backtrace
end
end
end
project.project_setting.update!(has_vulnerabilities: true)
create_vulnerability_statistics(project)
puts "\n🔍 Verifying data integrity..."
verify_vulnerabilities(project)
puts "\n✅ Vulnerability creation completed!"
puts "Total vulnerabilities created: #{project.vulnerabilities.count}"
puts "Vulnerabilities by state:"
project.vulnerabilities.group(:state).count.each do |state, count|
puts " #{state}: #{count}"
end
puts "Vulnerabilities by report type:"
project.vulnerabilities.group(:report_type).count.each do |type, count|
puts " #{type}: #{count}"
end
end
create_vulnerabilities_for_project!- Use graphql API with
detectedAfter,detectedBefore,createdAfter,createdBefore:
GraphQL API:
query {
project(fullPath: "group/project") {
vulnerabilities(
first: 10
detectedAfter: "2024-02-01T00:00:00Z"
) {
pageInfo {
hasNextPage
endCursor
}
nodes {
id
title
reportType
severity
state
detectedAt
}
}
}
}MR acceptance checklist
Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Edited by Andrew Jung