Improve grade aggregation using severity-based ordering
What does this MR do and why?
This code change improves how vulnerability security grades are calculated and displayed for projects in GitLab. The main improvements include:
-
The system now processes vulnerability statistics more efficiently by using database queries that group and order data properly, rather than processing each project individually.
-
Refactored the vulnerability grading system to be more organised and easier to maintain. The code now handles different scenarios (like groups, subgroups, and individual projects) in a more unified and generalised way.
-
Changed how the system aggregates vulnerability data by severity levels (critical, high, medium, low, etc.) and groups projects by their security grades (A, B, C, D, F).
-
Ensured the system works correctly when dealing with empty or invalid data.
These changes corrects the ordering functionality from a user perspective but also makes the underlying system more reliable.
Adds deterministic project ordering by severity levels. Projects within each letter grade are ordered by:
- highest number of critical vulnerabilities first
- then high, medium, low, unknown, info
- ties broken by project ID ascending
If no filter is provided, return all supported project grades in the response even without projects.
This ensures that projects with the most severe security risks appear first in each grade’s list, providing consistent and predictable ordering in dashboards and reports.
Queries
https://console.postgres.ai/gitlab/gitlab-production-sec/sessions/41337/commands/127009
Click to expand
SELECT
vulnerability_statistics.letter_grade,
array_agg(project_id ORDER BY critical DESC, high DESC, medium DESC, low DESC, unknown DESC, info DESC, project_id ASC) AS project_ids
FROM (
SELECT
vulnerability_statistics.project_id,
vulnerability_statistics.letter_grade,
vulnerability_statistics.critical,
vulnerability_statistics.high,
vulnerability_statistics.medium,
vulnerability_statistics.low,
vulnerability_statistics.unknown,
vulnerability_statistics.info,
vulnerability_statistics.id
FROM vulnerability_statistics
WHERE vulnerability_statistics.project_id IN (278964, 37445721)
ORDER BY
critical DESC,
high DESC,
medium DESC,
low DESC,
unknown DESC,
info DESC,
project_id ASC
) AS vulnerability_statistics
GROUP BY vulnerability_statistics.letter_grade;https://console.postgres.ai/gitlab/gitlab-production-sec/sessions/41337/commands/127006
Click to expand
-- I used the range-based `CASE` statement here to verify that the `CASE` logic and ordering work as expected,
-- without depending on specific IDs.
-- For reference, the real query would look something like this:
--
-- SELECT projects.id,
-- CASE projects.id
-- WHEN 7 THEN 0
-- WHEN 3 THEN 1
-- WHEN 12 THEN 2
-- ELSE 3
-- END AS order_index
-- FROM projects
-- ORDER BY order_index;
-- The range-based version is for testing the logic
SELECT
projects.id,
projects.name,
projects.path,
projects.description,
projects.created_at,
projects.updated_at,
projects.creator_id,
projects.namespace_id,
projects.last_activity_at,
projects.import_url,
projects.visibility_level,
projects.archived,
projects.avatar,
projects.merge_requests_template,
projects.star_count,
projects.merge_requests_rebase_enabled,
projects.import_type,
projects.import_source,
projects.approvals_before_merge,
projects.reset_approvals_on_push,
projects.merge_requests_ff_only_enabled,
projects.issues_template,
projects.mirror,
projects.mirror_last_update_at,
projects.mirror_last_successful_update_at,
projects.mirror_user_id,
projects.shared_runners_enabled,
projects.runners_token,
projects.build_allow_git_fetch,
projects.build_timeout,
projects.mirror_trigger_builds,
projects.pending_delete,
projects.public_builds,
projects.last_repository_check_failed,
projects.last_repository_check_at,
projects.only_allow_merge_if_pipeline_succeeds,
projects.has_external_issue_tracker,
projects.repository_storage,
projects.repository_read_only,
projects.request_access_enabled,
projects.has_external_wiki,
projects.ci_config_path,
projects.lfs_enabled,
projects.description_html,
projects.only_allow_merge_if_all_discussions_are_resolved,
projects.repository_size_limit,
projects.printing_merge_request_link_enabled,
projects.auto_cancel_pending_pipelines,
projects.service_desk_enabled,
projects.cached_markdown_version,
projects.delete_error,
projects.last_repository_updated_at,
projects.disable_overriding_approvers_per_merge_request,
projects.storage_version,
projects.resolve_outdated_diff_discussions,
projects.remote_mirror_available_overridden,
projects.only_mirror_protected_branches,
projects.pull_mirror_available_overridden,
projects.jobs_cache_index,
projects.external_authorization_classification_label,
projects.mirror_overwrites_diverged_branches,
projects.pages_https_only,
projects.external_webhook_token,
projects.packages_enabled,
projects.merge_requests_author_approval,
projects.pool_repository_id,
projects.runners_token_encrypted,
projects.bfg_object_map,
projects.detected_repository_languages,
projects.merge_requests_disable_committers_approval,
projects.require_password_to_approve,
projects.max_pages_size,
projects.max_artifacts_size,
projects.pull_mirror_branch_prefix,
projects.remove_source_branch_after_merge,
projects.marked_for_deletion_at,
projects.marked_for_deletion_by_user_id,
projects.autoclose_referenced_issues,
projects.suggestion_commit_message,
projects.project_namespace_id,
projects.hidden,
projects.organization_id
FROM projects
WHERE
projects.namespace_id = 9970
AND projects.archived = FALSE
ORDER BY
CASE
WHEN projects.id BETWEEN 0 AND 5 THEN 0
WHEN projects.id BETWEEN 6 AND 10 THEN 1
WHEN projects.id BETWEEN 11 AND 15 THEN 2
WHEN projects.id BETWEEN 16 AND 20 THEN 3
WHEN projects.id BETWEEN 21 AND 25 THEN 4
ELSE 5
END,
projects.id ASC;References
- Issue: Group Security Dashboard - Project security sta... (#545479 - closed)
- Issue: ProjectsGrade.grades_for returns same grades fo... (#507992 - closed)
Screenshots or screen recordings
| Before | After |
|---|---|
 |
 |
How to set up and validate locally
- Import projects into your group that contain vulnerabilities, see https://gitlab.com/gitlab-org/govern/threat-insights-demos/verification-projects.
- Open:
http://gdk.test:3000/<group-name>/-/security/dashboard - Look at Project security status.
- Verify that projects are ordered within each grade by:
- highest number of critical vulnerabilities first
- then high, medium, low, unknown, info
- ties broken by project ID ascending
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.