SQRP-299 Add query with filter/sort to ScaIssuesReleasesDetailsDao

9 files changed, 957 insertions, 16 deletions

diff --git a/server/sonar-db-dao/src/it/java/org/sonar/db/sca/ScaIssuesReleasesDetailsDaoIT.java b/server/sonar-db-dao/src/it/java/org/sonar/db/sca/ScaIssuesReleasesDetailsDaoIT.java
index 067424aa818..42eba53cfb8 100644
--- a/server/sonar-db-dao/src/it/java/org/sonar/db/sca/ScaIssuesReleasesDetailsDaoIT.java
+++ b/server/sonar-db-dao/src/it/java/org/sonar/db/sca/ScaIssuesReleasesDetailsDaoIT.java
@@ -19,13 +19,21 @@
  */
 package org.sonar.db.sca;
 
+import com.google.common.collect.Lists;
 import java.math.BigDecimal;
+import java.util.Collections;
+import java.util.Comparator;
+import java.util.EnumMap;
 import java.util.List;
+import java.util.function.Function;
+import java.util.stream.Stream;
 import org.junit.jupiter.api.Test;
 import org.junit.jupiter.api.extension.RegisterExtension;
 import org.sonar.api.utils.System2;
 import org.sonar.db.DbTester;
 import org.sonar.db.Pagination;
+import org.sonar.db.component.ComponentDto;
+import org.sonar.db.component.ProjectData;
 
 import static org.assertj.core.api.Assertions.assertThat;
 
@@ -36,6 +44,15 @@ class ScaIssuesReleasesDetailsDaoIT {
 
   private final ScaIssuesReleasesDetailsDao scaIssuesReleasesDetailsDao = db.getDbClient().scaIssuesReleasesDetailsDao();
 
+  private static Comparator<ScaIssueReleaseDetailsDto> identityComparator() {
+    Function<ScaIssueReleaseDetailsDto, String> typeString = dto -> dto.scaIssueType().name();
+    return Comparator.comparing(typeString)
+      .thenComparing(ScaIssueReleaseDetailsDto::vulnerabilityId)
+      .thenComparing(ScaIssueReleaseDetailsDto::packageUrl)
+      .thenComparing(ScaIssueReleaseDetailsDto::spdxLicenseId)
+      .thenComparing(ScaIssueReleaseDetailsDto::scaIssueReleaseUuid);
+  }
+
   @Test
   void selectByBranchUuid_shouldReturnIssues() {
     var projectData = db.components().insertPrivateProject();
@@ -72,7 +89,7 @@ class ScaIssuesReleasesDetailsDaoIT {
 
     assertThat(foundPage).hasSize(1).isSubsetOf(expected1, expected2);
     var foundAllIssues = scaIssuesReleasesDetailsDao.selectByBranchUuid(db.getSession(), componentDto.branchUuid(), Pagination.forPage(1).andSize(10));
-    assertThat(foundAllIssues).hasSize(2).containsExactlyInAnyOrder(expected1, expected2);
+    assertThat(foundAllIssues).hasSize(2).containsExactlyElementsOf(Stream.of(expected1, expected2).sorted(identityComparator()).toList());
   }
 
   @Test
@@ -87,4 +104,419 @@ class ScaIssuesReleasesDetailsDaoIT {
 
     assertThat(scaIssuesReleasesDetailsDao.countByBranchUuid(db.getSession(), "bogus-branch-uuid")).isZero();
   }
+
+  @Test
+  void withNoQueryFilters_shouldReturnAllIssues() {
+    setupAndExecuteQueryTest(Function.identity(), QueryTestData::expectedIssuesSortedByIdentityAsc, "All issues should be returned");
+  }
+
+  @Test
+  void withNoQueryFilters_shouldCountAllIssues() {
+    setupAndExecuteQueryCountTest(Function.identity(), 6);
+  }
+
+  @Test
+  void withNoQueryFilters_shouldSort() {
+    QueryTestData testData = createQueryTestData();
+    var expectedLists = new EnumMap<ScaIssuesReleasesDetailsQuery.Sort, List<ScaIssueReleaseDetailsDto>>(ScaIssuesReleasesDetailsQuery.Sort.class);
+    for (var sort : ScaIssuesReleasesDetailsQuery.Sort.values()) {
+      var expectedIssues = testData.expectedIssuesSorted(sort);
+      executeQueryTest(testData, queryBuilder -> queryBuilder.setSort(sort), expectedIssues,
+        "Sort %s should return expected issues".formatted(sort));
+      expectedLists.put(sort, expectedIssues);
+    }
+
+    // The assertions below here are actually about the expectations, but above
+    // we've just established that the actual matches the expectations.
+
+    // The point of this is to assert that the test data contains a distinct ordering for each
+    // ordering in ScaIssuesReleasesDetailsQuery.Sort, because if it doesn't we could get
+    // false negatives in our tests.
+    assertThat(expectedLists.values().stream().distinct().toList())
+      .as("Expected issues should have distinct orderings for each sort")
+      .containsExactlyInAnyOrderElementsOf(expectedLists.values());
+
+    // for identity, assert that our ASC and DESC actually invert each other.
+    // for severity and cvss score, this isn't supposed to be true because the
+    // secondary sort is IDENTITY_ASC even when we sort by DESC severity; but the
+    // severity and score values ignoring the other attributes should still be
+    // reversed.
+    assertThat(Lists.reverse(expectedLists.get(ScaIssuesReleasesDetailsQuery.Sort.IDENTITY_ASC)))
+      .as("IDENTITY sort should be reversed when sorted by DESC")
+      .containsExactlyElementsOf(expectedLists.get(ScaIssuesReleasesDetailsQuery.Sort.IDENTITY_DESC));
+    assertThat(
+      Lists.reverse(expectedLists.get(ScaIssuesReleasesDetailsQuery.Sort.SEVERITY_ASC)).stream()
+        .map(ScaIssueReleaseDetailsDto::severity)
+        .toList())
+          .as("SEVERITY sort should be reversed when sorted by DESC")
+          .containsExactlyElementsOf(expectedLists.get(ScaIssuesReleasesDetailsQuery.Sort.SEVERITY_DESC).stream()
+            .map(ScaIssueReleaseDetailsDto::severity)
+            .toList());
+    assertThat(Lists.reverse(expectedLists.get(ScaIssuesReleasesDetailsQuery.Sort.CVSS_SCORE_ASC).stream()
+      .map(ScaIssueReleaseDetailsDto::cvssScore)
+      .toList()))
+        .as("CVSS_SCORE sort should be reversed when sorted by DESC")
+        .containsExactlyElementsOf(expectedLists.get(ScaIssuesReleasesDetailsQuery.Sort.CVSS_SCORE_DESC).stream()
+          .map(ScaIssueReleaseDetailsDto::cvssScore)
+          .toList());
+  }
+
+  @Test
+  void withQueryFilteredByIssueType_shouldReturnExpectedTypes() {
+    QueryTestData testData = createQueryTestData();
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setTypes(List.of(ScaIssueType.VULNERABILITY)),
+      testData.expectedIssuesSortedByIdentityAsc().stream()
+        .filter(expected -> expected.scaIssueType() == ScaIssueType.VULNERABILITY)
+        .toList(),
+      "Only vulnerability issues should be returned");
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setTypes(List.of(ScaIssueType.PROHIBITED_LICENSE)),
+      testData.expectedIssuesSortedByIdentityAsc().stream()
+        .filter(expected -> expected.scaIssueType() == ScaIssueType.PROHIBITED_LICENSE)
+        .toList(),
+      "Only vulnerability issues should be returned");
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setTypes(List.of(ScaIssueType.values())),
+      testData.expectedIssuesSortedByIdentityAsc(),
+      "All issues should be returned");
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setTypes(Collections.emptyList()),
+      Collections.emptyList(),
+      "No issues should be returned when searching for zero types");
+  }
+
+  @Test
+  void withQueryFilteredByIssueType_shouldCountSelectedIssues() {
+    QueryTestData testData = createQueryTestData();
+    executeQueryCountTest(testData,
+      queryBuilder -> queryBuilder.setTypes(List.of(ScaIssueType.VULNERABILITY)),
+      4);
+    executeQueryCountTest(testData,
+      queryBuilder -> queryBuilder.setTypes(List.of(ScaIssueType.PROHIBITED_LICENSE)),
+      2);
+    executeQueryCountTest(testData,
+      queryBuilder -> queryBuilder.setTypes(List.of(ScaIssueType.values())),
+      6);
+    executeQueryCountTest(testData,
+      queryBuilder -> queryBuilder.setTypes(Collections.emptyList()),
+      0);
+  }
+
+  @Test
+  void withQueryFilteredByVulnerabilityId_shouldReturnExpectedItems() {
+    QueryTestData testData = createQueryTestData();
+    var expectedEndsInId1 = testData.expectedIssues().stream()
+      .filter(issue -> issue.vulnerabilityId() != null && issue.vulnerabilityId().endsWith("Id1"))
+      .toList();
+    assertThat(expectedEndsInId1).hasSize(1);
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setVulnerabilityIdSubstring("Id1"),
+      expectedEndsInId1,
+      "Only the vulnerability ending in Id1 should be returned");
+
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setVulnerabilityIdSubstring("NotInThere"),
+      Collections.emptyList(),
+      "No issues should be returned when searching for the substring 'NotInThere'");
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setVulnerabilityIdSubstring("Escape% NULL AS!%"),
+      Collections.emptyList(),
+      "No issues should be returned when searching for a string that needs escaping");
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setVulnerabilityIdSubstring(ScaIssueDto.NULL_VALUE),
+      Collections.emptyList(),
+      "No vulnerabilities should be returned when searching for ScaIssueDto.NULL_VALUE");
+
+    var allVulnerabilityIssues = testData.expectedIssuesSortedByIdentityAsc().stream()
+      .filter(issue -> issue.scaIssueType() == ScaIssueType.VULNERABILITY)
+      .toList();
+    assertThat(allVulnerabilityIssues).hasSize(4);
+
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setVulnerabilityIdSubstring("Vulnerability"),
+      allVulnerabilityIssues,
+      "All vulnerabilities should be returned when searching for the substring 'Vulnerability'");
+
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setVulnerabilityIdSubstring(""),
+      allVulnerabilityIssues,
+      "All vulnerabilities should be returned when searching for empty vulnerabilityId");
+  }
+
+  @Test
+  void withQueryFilteredByPackageName_shouldReturnExpectedItems() {
+    QueryTestData testData = createQueryTestData();
+    var expectedEndsInName1 = testData.expectedIssues().subList(0, 1);
+
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setPackageNameSubstring("Name1"),
+      expectedEndsInName1,
+      "Only the packages containing Name1 should be returned");
+
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setPackageNameSubstring("NotInThere"),
+      Collections.emptyList(),
+      "No issues should be returned when searching for the substring 'NotInThere'");
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setPackageNameSubstring("Escape% NULL AS!%"),
+      Collections.emptyList(),
+      "No issues should be returned when searching for a string that needs escaping");
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setPackageNameSubstring(ScaIssueDto.NULL_VALUE),
+      Collections.emptyList(),
+      "No vulnerabilities should be returned when searching for ScaIssueDto.NULL_VALUE");
+
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setPackageNameSubstring("Package"),
+      testData.expectedIssuesSortedByIdentityAsc(),
+      "All issues should be returned when searching for the substring 'Package'");
+
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setPackageNameSubstring(""),
+      testData.expectedIssuesSortedByIdentityAsc(),
+      "All issues should be returned when searching for empty package name");
+  }
+
+  @Test
+  void withQueryFilteredBySeverity_shouldReturnExpectedItems() {
+    QueryTestData testData = createQueryTestData();
+    var expectedSeverityInfo = testData.expectedIssuesSortedByIdentityAsc().stream()
+      .filter(issue -> issue.severity() == ScaSeverity.INFO)
+      .toList();
+    var expectedSeverityCritical = testData.expectedIssuesSortedByIdentityAsc().stream()
+      .filter(issue -> issue.severity() == ScaSeverity.BLOCKER)
+      .toList();
+    assertThat(expectedSeverityInfo).hasSize(5);
+    assertThat(expectedSeverityCritical).hasSize(1);
+
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setSeverities(List.of(ScaSeverity.INFO)),
+      expectedSeverityInfo,
+      "Only the issues of severity INFO should be returned");
+
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setSeverities(List.of(ScaSeverity.BLOCKER)),
+      expectedSeverityCritical,
+      "Only the issues of severity CRITICAL should be returned");
+
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setSeverities(List.of(ScaSeverity.LOW, ScaSeverity.HIGH)),
+      Collections.emptyList(),
+      "Should not match any severities of LOW or HIGH");
+
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setSeverities(List.of(ScaSeverity.BLOCKER, ScaSeverity.INFO, ScaSeverity.LOW)),
+      testData.expectedIssuesSortedByIdentityAsc(),
+      "All issues should be returned when searching for a list that contains them all");
+
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setSeverities(Collections.emptyList()),
+      Collections.emptyList(),
+      "No issues should be returned when searching for zero severities");
+  }
+
+  @Test
+  void withQueryFilteredByPackageManager_shouldReturnExpectedItems() {
+    QueryTestData testData = createQueryTestData();
+    var expectedPackageManagerNpm = testData.expectedIssuesWithPackageManager(PackageManager.NPM).stream()
+      .sorted(identityComparator()).toList();
+    var expectedPackageManagerMaven = testData.expectedIssuesWithPackageManager(PackageManager.MAVEN).stream()
+      .sorted(identityComparator()).toList();
+
+    assertThat(expectedPackageManagerNpm).hasSize(2);
+    assertThat(expectedPackageManagerMaven).hasSize(4);
+
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setPackageManagers(List.of(PackageManager.NPM)),
+      expectedPackageManagerNpm,
+      "Only the npm issues should be returned");
+
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setPackageManagers(List.of(PackageManager.MAVEN)),
+      expectedPackageManagerMaven,
+      "Only the Maven issues should be returned");
+
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setPackageManagers(List.of(PackageManager.NPM, PackageManager.MAVEN)),
+      testData.expectedIssuesSortedByIdentityAsc(),
+      "All issues should be returned when searching for two package managers");
+
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder.setPackageManagers(Collections.emptyList()),
+      Collections.emptyList(),
+      "No issues should be returned when searching for zero package managers");
+  }
+
+  @Test
+  void withQueryMultipleFiltersNonDefaultSort_shouldReturnExpectedItems() {
+    QueryTestData testData = createQueryTestData();
+    var expectedPackageManagerMaven = testData.expectedIssuesWithPackageManager(PackageManager.MAVEN);
+    var expectedTypeVulnerability = testData.expectedIssuesSortedByIdentityAsc().stream()
+      .filter(issue -> issue.scaIssueType() == ScaIssueType.VULNERABILITY)
+      .toList();
+    var sortedByCvssDesc = testData.expectedIssuesSortedByCvssDesc();
+    var expectedResults = sortedByCvssDesc.stream()
+      .filter(expectedPackageManagerMaven::contains)
+      .filter(expectedTypeVulnerability::contains)
+      .toList();
+    assertThat(expectedResults).hasSize(3);
+
+    executeQueryTest(testData,
+      queryBuilder -> queryBuilder
+        .setSort(ScaIssuesReleasesDetailsQuery.Sort.CVSS_SCORE_DESC)
+        .setPackageManagers(List.of(PackageManager.MAVEN))
+        .setTypes(List.of(ScaIssueType.VULNERABILITY)),
+      expectedResults,
+      "Maven vulnerabilities returned in cvss score desc order");
+  }
+
+  private void setupAndExecuteQueryTest(Function<ScaIssuesReleasesDetailsQuery.Builder, ScaIssuesReleasesDetailsQuery.Builder> builderFunction,
+    Function<QueryTestData, List<ScaIssueReleaseDetailsDto>> expectedIssuesFunction, String assertAs) {
+    QueryTestData testData = createQueryTestData();
+    executeQueryTest(testData, builderFunction, expectedIssuesFunction.apply(testData), assertAs);
+  }
+
+  private void executeQueryTest(QueryTestData testData,
+    Function<ScaIssuesReleasesDetailsQuery.Builder, ScaIssuesReleasesDetailsQuery.Builder> builderFunction,
+    List<ScaIssueReleaseDetailsDto> expectedIssues,
+    String assertAs) {
+    var query = builderFunction.apply(
+      new ScaIssuesReleasesDetailsQuery.Builder()
+        .setBranchUuid(testData.branchUuid())
+        .setSort(ScaIssuesReleasesDetailsQuery.Sort.IDENTITY_ASC))
+      .build();
+    var foundPage = scaIssuesReleasesDetailsDao.selectByQuery(db.getSession(), query, Pagination.forPage(1).andSize(10));
+
+    assertThat(foundPage).as(assertAs).containsExactlyElementsOf(expectedIssues);
+  }
+
+  private void setupAndExecuteQueryCountTest(Function<ScaIssuesReleasesDetailsQuery.Builder, ScaIssuesReleasesDetailsQuery.Builder> builderFunction,
+    int expectedCount) {
+    QueryTestData testData = createQueryTestData();
+    executeQueryCountTest(testData, builderFunction, expectedCount);
+  }
+
+  private void executeQueryCountTest(QueryTestData testData,
+    Function<ScaIssuesReleasesDetailsQuery.Builder, ScaIssuesReleasesDetailsQuery.Builder> builderFunction,
+    int expectedCount) {
+    var query = builderFunction.apply(
+      new ScaIssuesReleasesDetailsQuery.Builder()
+        .setBranchUuid(testData.branchUuid())
+        .setSort(ScaIssuesReleasesDetailsQuery.Sort.IDENTITY_ASC))
+      .build();
+    var count = scaIssuesReleasesDetailsDao.countByQuery(db.getSession(), query);
+
+    assertThat(count).isEqualTo(expectedCount);
+  }
+
+  private QueryTestData createQueryTestData() {
+    var projectData = db.components().insertPrivateProject();
+    var componentDto = projectData.getMainBranchComponent();
+    // the first two are set to NPM, the others default to MAVEN
+    var issue1 = db.getScaIssuesReleasesDetailsDbTester().insertIssue(ScaIssueType.VULNERABILITY, "1", componentDto.uuid(), projectData.projectUuid(),
+      scaIssueDto -> scaIssueDto,
+      scaVulnerabilityIssueDto -> scaVulnerabilityIssueDto,
+      scaReleaseDto -> scaReleaseDto.toBuilder().setPackageManager(PackageManager.NPM).build(),
+      scaIssueReleaseDto -> scaIssueReleaseDto);
+    var issue2 = db.getScaIssuesReleasesDetailsDbTester().insertIssue(ScaIssueType.PROHIBITED_LICENSE, "2", componentDto.uuid(), projectData.projectUuid(),
+      scaIssueDto -> scaIssueDto,
+      scaVulnerabilityIssueDto -> scaVulnerabilityIssueDto,
+      scaReleaseDto -> scaReleaseDto.toBuilder().setPackageManager(PackageManager.NPM).build(),
+      scaIssueReleaseDto -> scaIssueReleaseDto);
+    var issue3 = db.getScaIssuesReleasesDetailsDbTester().insertIssue(ScaIssueType.VULNERABILITY, "3", componentDto.uuid(), projectData.projectUuid());
+    var issue4 = db.getScaIssuesReleasesDetailsDbTester().insertIssue(ScaIssueType.PROHIBITED_LICENSE, "4", componentDto.uuid(), projectData.projectUuid());
+    // low cvss but high severity
+    var issue5 = db.getScaIssuesReleasesDetailsDbTester().insertIssue(ScaIssueType.VULNERABILITY, "5", componentDto.uuid(), projectData.projectUuid(),
+      scaIssueDto -> scaIssueDto,
+      scaVulnerabilityIssueDto -> scaVulnerabilityIssueDto.toBuilder()
+        .setCvssScore(new BigDecimal("2.1"))
+        .setBaseSeverity(ScaSeverity.BLOCKER)
+        .build(),
+      scaReleaseDto -> scaReleaseDto,
+      scaIssueReleaseDto -> scaIssueReleaseDto.toBuilder().setSeverity(ScaSeverity.BLOCKER).build());
+    // high cvss but low severity
+    var issue6 = db.getScaIssuesReleasesDetailsDbTester().insertIssue(ScaIssueType.VULNERABILITY, "6", componentDto.uuid(), projectData.projectUuid(),
+      scaIssueDto -> scaIssueDto,
+      scaVulnerabilityIssueDto -> scaVulnerabilityIssueDto.toBuilder()
+        .setCvssScore(new BigDecimal("9.1"))
+        .setBaseSeverity(ScaSeverity.INFO)
+        .build(),
+      scaReleaseDto -> scaReleaseDto,
+      scaIssueReleaseDto -> scaIssueReleaseDto.toBuilder().setSeverity(ScaSeverity.INFO).build());
+
+    return new QueryTestData(projectData, componentDto,
+      List.of(issue1, issue2, issue3, issue4, issue5, issue6));
+  }
+
+  private record QueryTestData(ProjectData projectData,
+    ComponentDto componentDto,
+    List<ScaIssueReleaseDetailsDto> expectedIssues) {
+    private static Comparator<ScaIssueReleaseDetailsDto> cvssScoreComparator() {
+      return Comparator.comparing(ScaIssueReleaseDetailsDto::cvssScore,
+        // we treat null cvss as a score of 0.0
+        Comparator.nullsFirst(Comparator.naturalOrder()));
+    }
+
+    private static Comparator<ScaIssueReleaseDetailsDto> severityComparator() {
+      return Comparator.comparing(dto -> dto.severity().databaseSortKey());
+    }
+
+    public String branchUuid() {
+      return componentDto.branchUuid();
+    }
+
+    public List<ScaIssueReleaseDetailsDto> expectedIssuesSortedByIdentityAsc() {
+      return expectedIssues.stream().sorted(identityComparator()).toList();
+    }
+
+    public List<ScaIssueReleaseDetailsDto> expectedIssuesSortedByIdentityDesc() {
+      return expectedIssues.stream().sorted(identityComparator().reversed()).toList();
+    }
+
+    public List<ScaIssueReleaseDetailsDto> expectedIssuesSortedBySeverityAsc() {
+      return expectedIssues.stream().sorted(severityComparator()
+        .thenComparing(cvssScoreComparator())
+        .thenComparing(identityComparator())).toList();
+    }
+
+    public List<ScaIssueReleaseDetailsDto> expectedIssuesSortedBySeverityDesc() {
+      return expectedIssues.stream().sorted(severityComparator().reversed()
+        .thenComparing(cvssScoreComparator().reversed())
+        .thenComparing(identityComparator())).toList();
+    }
+
+    public List<ScaIssueReleaseDetailsDto> expectedIssuesSortedByCvssAsc() {
+      return expectedIssues.stream().sorted(cvssScoreComparator()
+        .thenComparing(ScaIssueReleaseDetailsDto::severity)
+        .thenComparing(identityComparator())).toList();
+    }
+
+    public List<ScaIssueReleaseDetailsDto> expectedIssuesSortedByCvssDesc() {
+      return expectedIssues.stream().sorted(cvssScoreComparator().reversed()
+        .thenComparing(Comparator.comparing(ScaIssueReleaseDetailsDto::severity).reversed())
+        .thenComparing(identityComparator())).toList();
+    }
+
+    public List<ScaIssueReleaseDetailsDto> expectedIssuesSorted(ScaIssuesReleasesDetailsQuery.Sort sort) {
+      return switch (sort) {
+        case IDENTITY_ASC -> expectedIssuesSortedByIdentityAsc();
+        case IDENTITY_DESC -> expectedIssuesSortedByIdentityDesc();
+        case SEVERITY_ASC -> expectedIssuesSortedBySeverityAsc();
+        case SEVERITY_DESC -> expectedIssuesSortedBySeverityDesc();
+        case CVSS_SCORE_ASC -> expectedIssuesSortedByCvssAsc();
+        case CVSS_SCORE_DESC -> expectedIssuesSortedByCvssDesc();
+      };
+    }
+
+    public List<ScaIssueReleaseDetailsDto> expectedIssuesWithPackageManager(PackageManager packageManager) {
+      // we just have hardcoded knowledge of how we set them up, because ScaIssueReleaseDetailsDto doesn't
+      // contain the ScaReleaseDto to look at this
+      return switch (packageManager) {
+        case NPM -> expectedIssues.subList(0, 2);
+        case MAVEN -> expectedIssues.subList(2, expectedIssues.size());
+        default -> Collections.emptyList();
+      };
+    }
+  }
 }
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssueReleaseDetailsDto.java b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssueReleaseDetailsDto.java
index d2dd1674c03..30d73abcf7c 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssueReleaseDetailsDto.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssueReleaseDetailsDto.java
@@ -51,4 +51,93 @@ public record ScaIssueReleaseDetailsDto(String scaIssueReleaseUuid,
   @Nullable ScaSeverity vulnerabilityBaseSeverity,
   @Nullable List<String> cweIds,
   @Nullable BigDecimal cvssScore) implements ScaIssueIdentity {
+
+  public Builder toBuilder() {
+    return new Builder()
+      .setScaIssueReleaseUuid(scaIssueReleaseUuid)
+      .setSeverity(severity)
+      .setScaIssueUuid(scaIssueUuid)
+      .setScaReleaseUuid(scaReleaseUuid)
+      .setScaIssueType(scaIssueType)
+      .setPackageUrl(packageUrl)
+      .setVulnerabilityId(vulnerabilityId)
+      .setSpdxLicenseId(spdxLicenseId)
+      .setVulnerabilityBaseSeverity(vulnerabilityBaseSeverity)
+      .setCweIds(cweIds)
+      .setCvssScore(cvssScore);
+  }
+
+  public static class Builder {
+    private String scaIssueReleaseUuid;
+    private ScaSeverity severity;
+    private String scaIssueUuid;
+    private String scaReleaseUuid;
+    private ScaIssueType scaIssueType;
+    private String packageUrl;
+    private String vulnerabilityId;
+    private String spdxLicenseId;
+    private ScaSeverity vulnerabilityBaseSeverity;
+    private List<String> cweIds;
+    private BigDecimal cvssScore;
+
+    public Builder setScaIssueReleaseUuid(String scaIssueReleaseUuid) {
+      this.scaIssueReleaseUuid = scaIssueReleaseUuid;
+      return this;
+    }
+
+    public Builder setSeverity(ScaSeverity severity) {
+      this.severity = severity;
+      return this;
+    }
+
+    public Builder setScaIssueUuid(String scaIssueUuid) {
+      this.scaIssueUuid = scaIssueUuid;
+      return this;
+    }
+
+    public Builder setScaReleaseUuid(String scaReleaseUuid) {
+      this.scaReleaseUuid = scaReleaseUuid;
+      return this;
+    }
+
+    public Builder setScaIssueType(ScaIssueType scaIssueType) {
+      this.scaIssueType = scaIssueType;
+      return this;
+    }
+
+    public Builder setPackageUrl(String packageUrl) {
+      this.packageUrl = packageUrl;
+      return this;
+    }
+
+    public Builder setVulnerabilityId(String vulnerabilityId) {
+      this.vulnerabilityId = vulnerabilityId;
+      return this;
+    }
+
+    public Builder setSpdxLicenseId(String spdxLicenseId) {
+      this.spdxLicenseId = spdxLicenseId;
+      return this;
+    }
+
+    public Builder setVulnerabilityBaseSeverity(@Nullable ScaSeverity vulnerabilityBaseSeverity) {
+      this.vulnerabilityBaseSeverity = vulnerabilityBaseSeverity;
+      return this;
+    }
+
+    public Builder setCweIds(@Nullable List<String> cweIds) {
+      this.cweIds = cweIds;
+      return this;
+    }
+
+    public Builder setCvssScore(@Nullable BigDecimal cvssScore) {
+      this.cvssScore = cvssScore;
+      return this;
+    }
+
+    public ScaIssueReleaseDetailsDto build() {
+      return new ScaIssueReleaseDetailsDto(scaIssueReleaseUuid, severity, scaIssueUuid, scaReleaseUuid, scaIssueType, packageUrl, vulnerabilityId, spdxLicenseId,
+        vulnerabilityBaseSeverity, cweIds, cvssScore);
+    }
+  }
 }
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesDetailsDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesDetailsDao.java
index 77be2a81ea5..ae7044b5ba7 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesDetailsDao.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesDetailsDao.java
@@ -43,4 +43,12 @@ public class ScaIssuesReleasesDetailsDao implements Dao {
   public int countByBranchUuid(DbSession dbSession, String branchUuid) {
     return mapper(dbSession).countByBranchUuid(branchUuid);
   }
+
+  public List<ScaIssueReleaseDetailsDto> selectByQuery(DbSession dbSession, ScaIssuesReleasesDetailsQuery query, Pagination pagination) {
+    return mapper(dbSession).selectByQuery(query, pagination);
+  }
+
+  public int countByQuery(DbSession dbSession, ScaIssuesReleasesDetailsQuery query) {
+    return mapper(dbSession).countByQuery(query);
+  }
 }
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesDetailsMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesDetailsMapper.java
index 02bea84eb9c..f9111e13922 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesDetailsMapper.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesDetailsMapper.java
@@ -27,4 +27,8 @@ public interface ScaIssuesReleasesDetailsMapper {
   List<ScaIssueReleaseDetailsDto> selectByBranchUuid(@Param("branchUuid") String branchUuid, @Param("pagination") Pagination pagination);
 
   int countByBranchUuid(String branchUuid);
+
+  List<ScaIssueReleaseDetailsDto> selectByQuery(@Param("query") ScaIssuesReleasesDetailsQuery query, @Param("pagination") Pagination pagination);
+
+  int countByQuery(@Param("query") ScaIssuesReleasesDetailsQuery query);
 }
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesDetailsQuery.java b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesDetailsQuery.java
new file mode 100644
index 00000000000..a718581ffdf
--- /dev/null
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaIssuesReleasesDetailsQuery.java
@@ -0,0 +1,158 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2025 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.db.sca;
+
+import java.util.List;
+import java.util.Locale;
+import java.util.Objects;
+import java.util.Optional;
+import javax.annotation.CheckForNull;
+import javax.annotation.Nullable;
+import org.sonar.db.WildcardPosition;
+
+import static org.sonar.db.DaoUtils.buildLikeValue;
+import static org.sonar.db.WildcardPosition.BEFORE_AND_AFTER;
+
+public record ScaIssuesReleasesDetailsQuery(
+  String branchUuid,
+  Sort sort,
+  @Nullable String vulnerabilityIdSubstring,
+  @Nullable String packageNameSubstring,
+  @Nullable List<ScaIssueType> types,
+  @Nullable List<ScaSeverity> severities,
+  @Nullable List<PackageManager> packageManagers) {
+
+  public ScaIssuesReleasesDetailsQuery {
+    Objects.requireNonNull(branchUuid);
+    Objects.requireNonNull(sort);
+  }
+
+  /** For use in the mapper after <code>upper(vulnerabilityId) LIKE</code>,
+   * and per the {@link org.sonar.db.DaoUtils#buildLikeValue(String, WildcardPosition)}}
+   * docs, we have to say <code>ESCAPE '/'</code>. We are using uppercase because
+   * most ids will be uppercase already.
+   */
+  @CheckForNull
+  public String vulnerabilityIdUppercaseEscapedAsLikeValue() {
+    return vulnerabilityIdSubstring == null ? null : buildLikeValue(vulnerabilityIdSubstring.toUpperCase(Locale.ROOT), BEFORE_AND_AFTER);
+  }
+
+  /** For use in the mapper after <code>lower(packageName) LIKE</code>,
+   * and per the {@link org.sonar.db.DaoUtils#buildLikeValue(String, WildcardPosition)}}
+   * docs, we have to say <code>ESCAPE '/'</code>. We are using lowercase because most
+   * package names will be all or mostly lowercase already.
+   */
+  @CheckForNull
+  public String packageNameLowercaseEscapedAsLikeValue() {
+    return packageNameSubstring == null ? null : buildLikeValue(packageNameSubstring.toLowerCase(Locale.ROOT), BEFORE_AND_AFTER);
+  }
+
+  public Builder toBuilder() {
+    return new Builder()
+      .setBranchUuid(branchUuid)
+      .setSort(sort)
+      .setVulnerabilityIdSubstring(vulnerabilityIdSubstring)
+      .setPackageNameSubstring(packageNameSubstring)
+      .setTypes(types)
+      .setSeverities(severities)
+      .setPackageManagers(packageManagers);
+  }
+
+  public enum Sort {
+    IDENTITY_ASC("+identity"),
+    IDENTITY_DESC("-identity"),
+    SEVERITY_ASC("+severity"),
+    SEVERITY_DESC("-severity"),
+    CVSS_SCORE_ASC("+cvssScore"),
+    CVSS_SCORE_DESC("-cvssScore");
+
+    private final String queryParameterValue;
+
+    Sort(String queryParameterValue) {
+      this.queryParameterValue = queryParameterValue;
+    }
+
+    /**
+     * Convert a query parameter value to the corresponding {@link Sort} enum value.
+     * The passed-in string must not be null.
+     */
+    public static Optional<Sort> fromQueryParameterValue(String queryParameterValue) {
+      for (Sort sort : values()) {
+        if (sort.queryParameterValue.equals(queryParameterValue)) {
+          return Optional.of(sort);
+        }
+      }
+      return Optional.empty();
+    }
+
+    public String queryParameterValue() {
+      return queryParameterValue;
+    }
+  }
+
+  public static class Builder {
+    private String branchUuid;
+    private Sort sort;
+    private String vulnerabilityIdSubstring;
+    private String packageNameSubstring;
+    private List<ScaIssueType> types;
+    private List<ScaSeverity> severities;
+    private List<PackageManager> packageManagers;
+
+    public Builder setBranchUuid(String branchUuid) {
+      this.branchUuid = branchUuid;
+      return this;
+    }
+
+    public Builder setSort(Sort sort) {
+      this.sort = sort;
+      return this;
+    }
+
+    public Builder setVulnerabilityIdSubstring(@Nullable String vulnerabilityIdSubstring) {
+      this.vulnerabilityIdSubstring = vulnerabilityIdSubstring;
+      return this;
+    }
+
+    public Builder setPackageNameSubstring(@Nullable String packageNameSubstring) {
+      this.packageNameSubstring = packageNameSubstring;
+      return this;
+    }
+
+    public Builder setTypes(@Nullable List<ScaIssueType> types) {
+      this.types = types;
+      return this;
+    }
+
+    public Builder setSeverities(@Nullable List<ScaSeverity> severities) {
+      this.severities = severities;
+      return this;
+    }
+
+    public Builder setPackageManagers(@Nullable List<PackageManager> packageManagers) {
+      this.packageManagers = packageManagers;
+      return this;
+    }
+
+    public ScaIssuesReleasesDetailsQuery build() {
+      return new ScaIssuesReleasesDetailsQuery(branchUuid, sort, vulnerabilityIdSubstring, packageNameSubstring, types, severities, packageManagers);
+    }
+  }
+}
diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/sca/ScaIssuesReleasesDetailsMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/sca/ScaIssuesReleasesDetailsMapper.xml
index 80218190ced..5f293547d1d 100644
--- a/server/sonar-db-dao/src/main/resources/org/sonar/db/sca/ScaIssuesReleasesDetailsMapper.xml
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/sca/ScaIssuesReleasesDetailsMapper.xml
@@ -23,6 +23,7 @@
   <sql id="issuesWithScaColumns">
     sir.uuid,
     sir.severity,
+    sir.severity_sort_key,
     sir.sca_issue_uuid,
     sir.sca_release_uuid,
     si.sca_issue_type,
@@ -34,19 +35,23 @@
     svi.cvss_score
   </sql>
 
-  <sql id="sqlSelectByBranchUuid">
+  <sql id="sqlBaseJoins">
     from sca_issues_releases sir
     inner join sca_issues si on sir.sca_issue_uuid = si.uuid
     inner join sca_releases sr on sir.sca_release_uuid = sr.uuid
     inner join components c on sr.component_uuid = c.uuid
     left join sca_vulnerability_issues svi on sir.sca_issue_uuid = svi.uuid
+  </sql>
+
+  <sql id="sqlSelectByBranchUuid">
+    <include refid="sqlBaseJoins"/>
     where c.branch_uuid = #{branchUuid,jdbcType=VARCHAR}
   </sql>
 
   <select id="selectByBranchUuid" parameterType="map" resultMap="scaIssueReleaseDetailsResultMap">
     select <include refid="issuesWithScaColumns"/>
     <include refid="sqlSelectByBranchUuid"/>
-    ORDER BY sir.sca_issue_uuid ASC
+    ORDER BY <include refid="sqlIdentityOrderColumns"/>
     <include refid="org.sonar.db.common.Common.pagination"/>
   </select>
 
@@ -54,4 +59,113 @@
     select count(sir.uuid)
     <include refid="sqlSelectByBranchUuid"/>
   </select>
+
+  <sql id="sqlSelectByQueryWhereClause">
+    <where>
+      c.branch_uuid = #{query.branchUuid,jdbcType=VARCHAR}
+      <if test="query.vulnerabilityIdSubstring != null">
+          <!-- this screens out non-vulnerability-having issue types even if the search is for empty string -->
+        AND si.vulnerability_id != '${@org.sonar.db.sca.ScaIssueDto@NULL_VALUE}'
+        <if test="query.vulnerabilityIdSubstring.length > 0">
+          AND upper(si.vulnerability_id) LIKE #{query.vulnerabilityIdUppercaseEscapedAsLikeValue, jdbcType=VARCHAR} ESCAPE '/'
+        </if>
+      </if>
+      <if test="query.packageNameSubstring != null and query.packageNameSubstring.length > 0">
+          AND lower(sr.package_name) LIKE #{query.packageNameLowercaseEscapedAsLikeValue, jdbcType=VARCHAR} ESCAPE '/'
+      </if>
+      <if test="query.types != null">
+        <if test="query.types.isEmpty()">
+          AND 1=0
+        </if>
+        <if test="!query.types.isEmpty()">
+          AND si.sca_issue_type in
+          <foreach collection="query.types" open="(" close=")" item="type" separator=",">
+            #{type, jdbcType=VARCHAR}
+          </foreach>
+        </if>
+      </if>
+      <if test="query.severities != null">
+        <if test="query.severities.isEmpty()">
+          AND 1=0
+        </if>
+        <if test="!query.severities.isEmpty()">
+          AND sir.severity in
+          <foreach collection="query.severities" open="(" close=")" item="severity" separator=",">
+            #{severity, jdbcType=VARCHAR}
+          </foreach>
+        </if>
+      </if>
+      <if test="query.packageManagers != null">
+        <if test="query.packageManagers.isEmpty()">
+          AND 1=0
+        </if>
+        <if test="!query.packageManagers.isEmpty()">
+          AND sr.package_manager in
+          <foreach collection="query.packageManagers" open="(" close=")" item="packageManager" separator=",">
+            #{packageManager, jdbcType=VARCHAR}
+          </foreach>
+        </if>
+      </if>
+    </where>
+  </sql>
+
+  <sql id="sqlIdentityOrderColumns">
+    <!-- the unique index is ordered as: scaIssueType, vulnerabilityId, packageUrl, spdxLicenseId
+         so we're guessing (or hoping?) that is the most efficient sort order, and it should sort of make
+         more sense to users than random -->
+    si.sca_issue_type ASC, si.vulnerability_id ASC, si.package_url ASC, si.spdx_license_id ASC, sir.uuid ASC
+  </sql>
+
+  <sql id="sqlOrderByQuery">
+    <choose>
+      <when test="query.sort == @org.sonar.db.sca.ScaIssuesReleasesDetailsQuery$Sort@IDENTITY_ASC">
+        ORDER BY <include refid="sqlIdentityOrderColumns"/>
+      </when>
+      <when test="query.sort == @org.sonar.db.sca.ScaIssuesReleasesDetailsQuery$Sort@IDENTITY_DESC">
+        <!-- This is a bizarre and useless sort order and we really only have it for symmetry in the REST API -->
+        ORDER BY si.sca_issue_type DESC, si.vulnerability_id DESC, si.package_url DESC, si.spdx_license_id DESC, sir.uuid DESC
+      </when>
+      <when test="query.sort == @org.sonar.db.sca.ScaIssuesReleasesDetailsQuery$Sort@SEVERITY_ASC">
+        <!-- because many severities are the same, we try to keep the user intent by ordering by cvss score secondarily -->
+        ORDER BY sir.severity_sort_key ASC, cvss_sort_key ASC, <include refid="sqlIdentityOrderColumns"/>
+      </when>
+      <when test="query.sort == @org.sonar.db.sca.ScaIssuesReleasesDetailsQuery$Sort@SEVERITY_DESC">
+        <!-- because many severities are the same, we try to keep the user intent by ordering by cvss score secondarily -->
+        ORDER BY sir.severity_sort_key DESC, cvss_sort_key DESC, <include refid="sqlIdentityOrderColumns"/>
+      </when>
+      <when test="query.sort == @org.sonar.db.sca.ScaIssuesReleasesDetailsQuery$Sort@CVSS_SCORE_ASC">
+        <!-- because cvss score can be null, we try to keep the user intent by ordering by severity secondarily -->
+        ORDER BY cvss_sort_key ASC, sir.severity_sort_key ASC, <include refid="sqlIdentityOrderColumns"/>
+      </when>
+      <when test="query.sort == @org.sonar.db.sca.ScaIssuesReleasesDetailsQuery$Sort@CVSS_SCORE_DESC">
+        <!-- because cvss score can be null, we try to keep the user intent by ordering by severity secondarily -->
+        ORDER BY cvss_sort_key DESC, sir.severity_sort_key DESC, <include refid="sqlIdentityOrderColumns"/>
+      </when>
+      <otherwise>
+        <!-- generate a noisy failure -->
+        ORDER BY SYNTAX ERROR SHOULD NOT BE REACHED
+      </otherwise>
+    </choose>
+  </sql>
+
+  <select id="selectByQuery" parameterType="map" resultMap="scaIssueReleaseDetailsResultMap">
+    select <include refid="issuesWithScaColumns"/>,
+    <!-- It seems that the behavior of NULL in ORDER BY varies by database backend, with different
+     defaults and a lack of universal support for NULLS FIRST / NULLS LAST.
+     This poses an issue for nullable columns we want to sort by such as cvss_score.
+     On databases that support it, NULLS FIRST could probably use the index while this COALESCE
+     hack does not, so maybe someday we want to conditionalize on db backend somehow. -->
+    <!-- NULL score is treated as least severe -->
+    COALESCE(svi.cvss_score, 0.0) as cvss_sort_key
+    <include refid="sqlBaseJoins"/>
+    <include refid="sqlSelectByQueryWhereClause"/>
+    <include refid="sqlOrderByQuery"/>
+    <include refid="org.sonar.db.common.Common.pagination"/>
+  </select>
+
+  <select id="countByQuery" parameterType="string" resultType="int">
+    select count(sir.uuid)
+    <include refid="sqlBaseJoins"/>
+    <include refid="sqlSelectByQueryWhereClause"/>
+  </select>
 </mapper>
diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/sca/ScaIssueReleaseDetailsDtoTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/sca/ScaIssueReleaseDetailsDtoTest.java
new file mode 100644
index 00000000000..893f191389f
--- /dev/null
+++ b/server/sonar-db-dao/src/test/java/org/sonar/db/sca/ScaIssueReleaseDetailsDtoTest.java
@@ -0,0 +1,44 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2025 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.db.sca;
+
+import java.math.BigDecimal;
+import java.util.List;
+import org.junit.jupiter.api.Test;
+
+import static org.assertj.core.api.AssertionsForClassTypes.assertThat;
+
+class ScaIssueReleaseDetailsDtoTest {
+  @Test
+  void test_toBuilder_build_shouldRoundTrip() {
+    var dto = new ScaIssueReleaseDetailsDto("scaIssueReleaseUuid",
+      ScaSeverity.INFO,
+      "scaIssueUuid",
+      "scaReleaseUuid",
+      ScaIssueType.VULNERABILITY,
+      "packageUrl",
+      "vulnerabilityId",
+      "spdxLicenseId",
+      ScaSeverity.BLOCKER,
+      List.of("cwe1"),
+      BigDecimal.ONE);
+    assertThat(dto.toBuilder().build()).isEqualTo(dto);
+  }
+}
diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/sca/ScaIssuesReleasesDetailsQueryTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/sca/ScaIssuesReleasesDetailsQueryTest.java
new file mode 100644
index 00000000000..6d9f67b14eb
--- /dev/null
+++ b/server/sonar-db-dao/src/test/java/org/sonar/db/sca/ScaIssuesReleasesDetailsQueryTest.java
@@ -0,0 +1,55 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2025 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.db.sca;
+
+import java.util.List;
+import org.assertj.core.api.AssertionsForClassTypes;
+import org.junit.jupiter.api.Test;
+
+import static org.assertj.core.api.Assertions.assertThat;
+
+class ScaIssuesReleasesDetailsQueryTest {
+
+  @Test
+  void test_toBuilder_build_shouldRoundTrip() {
+    var query = new ScaIssuesReleasesDetailsQuery("branchUuid", ScaIssuesReleasesDetailsQuery.Sort.IDENTITY_ASC,
+      "vulnerabilityIdSubstring", "packageNameSubstring",
+      List.of(ScaIssueType.VULNERABILITY), List.of(ScaSeverity.BLOCKER), List.of(PackageManager.NPM));
+    AssertionsForClassTypes.assertThat(query.toBuilder().build()).isEqualTo(query);
+  }
+
+  @Test
+  void test_queryParameterValues() {
+    for (var value : ScaIssuesReleasesDetailsQuery.Sort.values()) {
+      var queryParameterValue = value.queryParameterValue();
+      var fromQueryParameterValue = ScaIssuesReleasesDetailsQuery.Sort.fromQueryParameterValue(queryParameterValue);
+      assertThat(fromQueryParameterValue).contains(value);
+      assertThat((queryParameterValue.startsWith("+") && value.name().endsWith("_ASC")) ||
+        (queryParameterValue.startsWith("-") && value.name().endsWith("_DESC")))
+          .as("+/- prefix and ASC/DESC suffix line up")
+          .isTrue();
+    }
+  }
+
+  @Test
+  void test_whenValueIsInvalid_fromQueryParameterValue() {
+    assertThat(ScaIssuesReleasesDetailsQuery.Sort.fromQueryParameterValue("invalid")).isEmpty();
+  }
+}
diff --git a/server/sonar-db-dao/src/testFixtures/java/org/sonar/db/sca/ScaIssuesReleasesDetailsDbTester.java b/server/sonar-db-dao/src/testFixtures/java/org/sonar/db/sca/ScaIssuesReleasesDetailsDbTester.java
index 56f527bc6be..a60c8c7f705 100644
--- a/server/sonar-db-dao/src/testFixtures/java/org/sonar/db/sca/ScaIssuesReleasesDetailsDbTester.java
+++ b/server/sonar-db-dao/src/testFixtures/java/org/sonar/db/sca/ScaIssuesReleasesDetailsDbTester.java
@@ -20,6 +20,7 @@
 package org.sonar.db.sca;
 
 import java.util.Optional;
+import java.util.function.Function;
 import javax.annotation.Nullable;
 import org.sonar.db.DbClient;
 import org.sonar.db.DbTester;
@@ -34,39 +35,75 @@ public class ScaIssuesReleasesDetailsDbTester {
   }
 
   public ScaIssueReleaseDetailsDto fromDtos(@Nullable String projectUuid, ScaIssueReleaseDto scaIssueReleaseDto, ScaIssueDto scaIssueDto,
-    Optional<ScaVulnerabilityIssueDto> scaVulnerabilityIssueDto) {
+    Optional<ScaVulnerabilityIssueDto> scaVulnerabilityIssueDtoOptional) {
     // this should emulate what the mapper does when joining these tables
-    return new ScaIssueReleaseDetailsDto(scaIssueReleaseDto.uuid(), ScaSeverity.INFO,
+    return new ScaIssueReleaseDetailsDto(scaIssueReleaseDto.uuid(), scaIssueReleaseDto.severity(),
       scaIssueReleaseDto.scaIssueUuid(), scaIssueReleaseDto.scaReleaseUuid(), scaIssueDto.scaIssueType(),
       scaIssueDto.packageUrl(), scaIssueDto.vulnerabilityId(), scaIssueDto.spdxLicenseId(),
-      scaVulnerabilityIssueDto.map(ScaVulnerabilityIssueDto::baseSeverity).orElse(null),
-      scaVulnerabilityIssueDto.map(ScaVulnerabilityIssueDto::cweIds).orElse(null),
-      scaVulnerabilityIssueDto.map(ScaVulnerabilityIssueDto::cvssScore).orElse(null));
+      scaVulnerabilityIssueDtoOptional.map(ScaVulnerabilityIssueDto::baseSeverity).orElse(null),
+      scaVulnerabilityIssueDtoOptional.map(ScaVulnerabilityIssueDto::cweIds).orElse(null),
+      scaVulnerabilityIssueDtoOptional.map(ScaVulnerabilityIssueDto::cvssScore).orElse(null));
   }
 
-  private ScaIssueReleaseDetailsDto insertIssue(ScaIssueDto scaIssue, String suffix, String componentUuid, @Nullable String projectUuid) {
+  private ScaIssueReleaseDetailsDto insertIssue(ScaIssueDto scaIssue, Optional<ScaVulnerabilityIssueDto> scaVulnerabilityIssueDtoOptional,
+    String suffix, String componentUuid,
+    @Nullable String projectUuid) {
     // insertScaRelease has suffix and componentUuid swapped vs. our own method...
     var scaRelease = db.getScaReleasesDbTester().insertScaRelease(componentUuid, suffix);
     var scaIssueRelease = new ScaIssueReleaseDto("sca-issue-release-uuid-" + suffix, scaIssue, scaRelease, ScaSeverity.INFO, 1L, 2L);
     dbClient.scaIssuesReleasesDao().insert(db.getSession(), scaIssueRelease);
-    return fromDtos(projectUuid, scaIssueRelease, scaIssue, Optional.empty());
+    return fromDtos(projectUuid, scaIssueRelease, scaIssue, scaVulnerabilityIssueDtoOptional);
   }
 
   public ScaIssueReleaseDetailsDto insertVulnerabilityIssue(String suffix, String componentUuid) {
-    var scaIssue = db.getScaIssuesDbTester().insertVulnerabilityIssue(suffix).getKey();
-    return insertIssue(scaIssue, suffix, componentUuid, null);
+    var entry = db.getScaIssuesDbTester().insertVulnerabilityIssue(suffix);
+    return insertIssue(entry.getKey(), Optional.of(entry.getValue()), suffix, componentUuid, null);
   }
 
   public ScaIssueReleaseDetailsDto insertProhibitedLicenseIssue(String suffix, String componentUuid) {
     var scaIssue = db.getScaIssuesDbTester().insertProhibitedLicenseIssue(suffix);
-    return insertIssue(scaIssue, suffix, componentUuid, null);
+    return insertIssue(scaIssue, Optional.empty(), suffix, componentUuid, null);
   }
 
   public ScaIssueReleaseDetailsDto insertIssue(ScaIssueType scaIssueType, String suffix, String componentUuid, @Nullable String projectUuid) {
+    return insertIssue(scaIssueType, suffix, componentUuid, projectUuid,
+      null, null, null, null);
+  }
+
+  public ScaIssueReleaseDetailsDto insertIssue(ScaIssueType scaIssueType, String suffix, String componentUuid, @Nullable String projectUuid,
+    Function<ScaIssueDto, ScaIssueDto> scaIssueModifier,
+    Function<ScaVulnerabilityIssueDto, ScaVulnerabilityIssueDto> scaVulnerabilityIssueModifier,
+    Function<ScaReleaseDto, ScaReleaseDto> scaReleaseModifier,
+    Function<ScaIssueReleaseDto, ScaIssueReleaseDto> scaIssueReleaseModifier) {
+    var scaRelease = db.getScaReleasesDbTester().newScaReleaseDto(componentUuid, suffix, PackageManager.MAVEN, "packageName" + suffix);
+    if (scaIssueModifier != null) {
+      scaRelease = scaReleaseModifier.apply(scaRelease);
+    }
+    dbClient.scaReleasesDao().insert(db.getSession(), scaRelease);
     var scaIssue = switch (scaIssueType) {
-      case VULNERABILITY -> db.getScaIssuesDbTester().insertVulnerabilityIssue(suffix).getKey();
-      case PROHIBITED_LICENSE -> db.getScaIssuesDbTester().insertProhibitedLicenseIssue(suffix);
+      case PROHIBITED_LICENSE -> db.getScaIssuesDbTester().newProhibitedLicenseScaIssueDto(suffix);
+      case VULNERABILITY -> db.getScaIssuesDbTester().newVulnerabilityScaIssueDto(suffix);
     };
-    return insertIssue(scaIssue, suffix, componentUuid, projectUuid);
+    if (scaIssueModifier != null) {
+      scaIssue = scaIssueModifier.apply(scaIssue);
+    }
+    dbClient.scaIssuesDao().insert(db.getSession(), scaIssue);
+    ScaVulnerabilityIssueDto scaVulnerabilityIssue = null;
+    if (scaIssue.scaIssueType() == ScaIssueType.VULNERABILITY) {
+      scaVulnerabilityIssue = db.getScaIssuesDbTester().newVulnerabilityIssueDto(suffix);
+      if (!scaVulnerabilityIssue.uuid().equals(scaIssue.uuid())) {
+        throw new IllegalStateException("ScaVulnerabilityIssueDto.uuid must match ScaIssueDto.uuid or we won't find the ScaVueberabilityIssueDto");
+      }
+      if (scaVulnerabilityIssueModifier != null) {
+        scaVulnerabilityIssue = scaVulnerabilityIssueModifier.apply(scaVulnerabilityIssue);
+      }
+      dbClient.scaVulnerabilityIssuesDao().insert(db.getSession(), scaVulnerabilityIssue);
+    }
+    var scaIssueRelease = new ScaIssueReleaseDto("sca-issue-release-uuid-" + suffix, scaIssue, scaRelease, ScaSeverity.INFO, 1L, 2L);
+    if (scaIssueReleaseModifier != null) {
+      scaIssueRelease = scaIssueReleaseModifier.apply(scaIssueRelease);
+    }
+    dbClient.scaIssuesReleasesDao().insert(db.getSession(), scaIssueRelease);
+    return fromDtos(projectUuid, scaIssueRelease, scaIssue, Optional.ofNullable(scaVulnerabilityIssue));
   }
 }