Add GraphQL type to allow retrieval of compliance violations within a group (!78681) · Merge requests · GitLab.org / GitLab

Robert Hunt requested to merge 347325-add-graphql-type-to-allow-retrieval-of-compliance-violations-within-a-group into master Jan 20, 2022

What does this MR do and why?

This MR adds a new GraphQL type and applies it to the Enterprise Edition group GraphQL type.

The type has a resolver, finder and associated enums to restrict what values can be given to the filters/sorting.

Expected query being used on the frontend

Query

query getComplianceViolations($fullPath: ID!, $filters: ComplianceViolationInput, $sort: ComplianceViolationSort) {
  group(fullPath: $fullPath) {
    id
    mergeRequestViolations(filters: filters, sort: sort) {
      nodes {
        id
        severityLevel
        reason
        violatingUser {
          id
          name
          username
          state
          avatarUrl
          webUrl
        }
        mergeRequest {
          id
          title
          mergedAt
          milestone {
            id
            title
          }
          webUrl
          author {
            id
            name
            username
            state
            avatarUrl
            webUrl
          }
          mergeUser {
            id
            name
            username
            state
            avatarUrl
            webUrl
          }
          # Requires https://gitlab.com/gitlab-org/gitlab/-/issues/352029 to be merged
          committers {
            nodes {
              id
              name
              username
              state
              avatarUrl
              webUrl
            }
          }
          participants {
            nodes {
              id
              name
              username
              state
              avatarUrl
              webUrl
            }
          }
          approvedBy {
            nodes {
              id
              name
              username
              state
              avatarUrl
              webUrl
            }
          }
          ref: reference
          fullRef: reference(full: true)
          sourceBranch
          sourceBranchExists
          targetBranch
          targetBranchExists
          headPipeline {
            detailedStatus {
              id
              icon
              favicon
              text
              label
              group
              tooltip
              hasDetails
              detailsPath
            }
          }
          project {
            id
            avatarUrl
            name
            webUrl
            complianceFrameworks {
              nodes {
                id
                name
                description
                color
              }
            }
          }
        }
      }
    }
  }
}

Database explain plans

No filters or sorting

Finder call: ComplianceManagement::MergeRequests::ComplianceViolationsFinder.new(user, group).execute
Plan: https://console.postgres.ai/shared/3f63222e-1b7e-4823-ba07-96d99ad942df
Raw SQL:

SELECT
    "merge_requests_compliance_violations".*
FROM
    "merge_requests_compliance_violations"
    LEFT OUTER JOIN "merge_requests" ON "merge_requests"."id" = "merge_requests_compliance_violations"."merge_request_id"
    LEFT OUTER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
    LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
WHERE
    "projects"."namespace_id" = 9970
ORDER BY
    "merge_requests_compliance_violations"."severity_level" DESC,
    "merge_requests_compliance_violations"."id" DESC;

Filter by project IDs

Finder call: ComplianceManagement::MergeRequests::ComplianceViolationsFinder.new(user, group, { filters: { project_ids: [278964, 13083] } }).execute
Plan: https://console.postgres.ai/shared/0ccbc9f0-d45c-4176-bd9d-d4d46ddbb5e8
Raw SQL:

SELECT
    "merge_requests_compliance_violations".*
FROM
    "merge_requests_compliance_violations"
    LEFT OUTER JOIN "merge_requests" ON "merge_requests"."id" = "merge_requests_compliance_violations"."merge_request_id"
    LEFT OUTER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
    LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
WHERE
    "projects"."namespace_id" = 9970
    AND "projects"."id" IN (278964, 13083)
ORDER BY
    "merge_requests_compliance_violations"."severity_level" DESC,
    "merge_requests_compliance_violations"."id" DESC;

Filter by merged before

Finder call: ComplianceManagement::MergeRequests::ComplianceViolationsFinder.new(user, group, { filters: { merged_before: '2021-10-01' } }).execute
Plan: https://console.postgres.ai/shared/250334a5-0f9b-466f-914b-a23d27ddb2e4
Raw SQL:

SELECT
    "merge_requests_compliance_violations".*
FROM
    "merge_requests_compliance_violations"
    LEFT OUTER JOIN "merge_requests" ON "merge_requests"."id" = "merge_requests_compliance_violations"."merge_request_id"
    LEFT OUTER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
    LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
    LEFT OUTER JOIN "merge_request_metrics" ON "merge_request_metrics"."merge_request_id" = "merge_requests"."id"
WHERE
    "projects"."namespace_id" = 9970
    AND ("merge_request_metrics"."merged_at" <= '2021-10-01')
ORDER BY
    "merge_requests_compliance_violations"."severity_level" DESC,
    "merge_requests_compliance_violations"."id" DESC;

Filter by merged after

Finder call: ComplianceManagement::MergeRequests::ComplianceViolationsFinder.new(user, group, { filters: { merged_after: '2021-01-01' } }).execute
Plan: https://console.postgres.ai/shared/8e304829-6d02-4dc5-a2ef-fed1431942ef
Raw SQL:

SELECT
    "merge_requests_compliance_violations".*
FROM
    "merge_requests_compliance_violations"
    LEFT OUTER JOIN "merge_requests" ON "merge_requests"."id" = "merge_requests_compliance_violations"."merge_request_id"
    LEFT OUTER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
    LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
    LEFT OUTER JOIN "merge_request_metrics" ON "merge_request_metrics"."merge_request_id" = "merge_requests"."id"
WHERE
    "projects"."namespace_id" = 9970
    AND ("merge_request_metrics"."merged_at" >= '2021-01-01')
ORDER BY
    "merge_requests_compliance_violations"."severity_level" DESC,
    "merge_requests_compliance_violations"."id" DESC;

Filter by all filters

Finder call: ComplianceManagement::MergeRequests::ComplianceViolationsFinder.new(user, group, { filters: { project_ids: [278964, 13083], merged_before: '2021-10-01', merged_after: '2021-01-01' } }).execute
Plan: https://console.postgres.ai/shared/f1ca3dab-91f3-4cfb-a4e7-8e29440dbf3c
Raw SQL:

SELECT
    "merge_requests_compliance_violations".*
FROM
    "merge_requests_compliance_violations"
    LEFT OUTER JOIN "merge_requests" ON "merge_requests"."id" = "merge_requests_compliance_violations"."merge_request_id"
    LEFT OUTER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
    LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
    LEFT OUTER JOIN "merge_request_metrics" ON "merge_request_metrics"."merge_request_id" = "merge_requests"."id"
WHERE
    "projects"."namespace_id" = 9970
    AND "projects"."id" IN (278964, 13083)
    AND ("merge_request_metrics"."merged_at" <= '2021-10-01')
    AND ("merge_request_metrics"."merged_at" >= '2021-01-01')
ORDER BY
    "merge_requests_compliance_violations"."severity_level" DESC,
    "merge_requests_compliance_violations"."id" DESC;

Sort by severity level ASC

Finder call: ComplianceManagement::MergeRequests::ComplianceViolationsFinder.new(user, group, { sort: 'SEVERITY_LEVEL_ASC' }).execute
Plan: https://console.postgres.ai/shared/ad339c5f-6f4f-4931-97b3-b8169312a5bd
Raw SQL:

SELECT
    "merge_requests_compliance_violations".*
FROM
    "merge_requests_compliance_violations"
    LEFT OUTER JOIN "merge_requests" ON "merge_requests"."id" = "merge_requests_compliance_violations"."merge_request_id"
    LEFT OUTER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
    LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
WHERE
    "projects"."namespace_id" = 9970
ORDER BY
    "merge_requests_compliance_violations"."severity_level" ASC,
    "merge_requests_compliance_violations"."id" ASC;

Sort by severity level DESC

Finder call: ComplianceManagement::MergeRequests::ComplianceViolationsFinder.new(user, group, { sort: 'SEVERITY_LEVEL_DESC' }).execute
Plan: https://console.postgres.ai/shared/a337efad-07c6-4c3c-8d5a-d4aeb2d18762
Raw SQL:

SELECT
    "merge_requests_compliance_violations".*
FROM
    "merge_requests_compliance_violations"
    LEFT OUTER JOIN "merge_requests" ON "merge_requests"."id" = "merge_requests_compliance_violations"."merge_request_id"
    LEFT OUTER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
    LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
WHERE
    "projects"."namespace_id" = 9970
ORDER BY
    "merge_requests_compliance_violations"."severity_level" DESC,
    "merge_requests_compliance_violations"."id" DESC;

Sort by violation reason ASC

Finder call: ComplianceManagement::MergeRequests::ComplianceViolationsFinder.new(user, group, { sort: 'VIOLATION_REASON_ASC' }).execute
Plan: https://console.postgres.ai/shared/2a9a29b9-7d3c-417d-8aed-1727af952929
Raw SQL:

SELECT
    "merge_requests_compliance_violations".*
FROM
    "merge_requests_compliance_violations"
    LEFT OUTER JOIN "merge_requests" ON "merge_requests"."id" = "merge_requests_compliance_violations"."merge_request_id"
    LEFT OUTER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
    LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
WHERE
    "projects"."namespace_id" = 9970
ORDER BY
    "merge_requests_compliance_violations"."reason" ASC,
    "merge_requests_compliance_violations"."id" ASC;

Sort by violation reason DESC

Finder call: ComplianceManagement::MergeRequests::ComplianceViolationsFinder.new(user, group, { sort: 'VIOLATION_REASON_DESC' }).execute
Plan: https://console.postgres.ai/shared/4b76532e-6c83-4240-83a1-6a847275644f
Raw SQL:

SELECT
    "merge_requests_compliance_violations".*
FROM
    "merge_requests_compliance_violations"
    LEFT OUTER JOIN "merge_requests" ON "merge_requests"."id" = "merge_requests_compliance_violations"."merge_request_id"
    LEFT OUTER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
    LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
WHERE
    "projects"."namespace_id" = 9970
ORDER BY
    "merge_requests_compliance_violations"."reason" DESC,
    "merge_requests_compliance_violations"."id" DESC;

Sort by merge request title ASC

Finder call: ComplianceManagement::MergeRequests::ComplianceViolationsFinder.new(user, group, { sort: 'MERGE_REQUEST_TITLE_ASC' }).execute
Plan: https://console.postgres.ai/shared/8f10359a-ccff-498a-b8eb-04879a982967
Raw SQL:

SELECT
    "merge_requests_compliance_violations".*
FROM
    "merge_requests_compliance_violations"
    LEFT OUTER JOIN "merge_requests" ON "merge_requests"."id" = "merge_requests_compliance_violations"."merge_request_id"
    LEFT OUTER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
    LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
WHERE
    "projects"."namespace_id" = 9970
ORDER BY
    "merge_requests"."title" ASC,
    "merge_requests_compliance_violations"."id" ASC;

Sort by merge request title DESC

Finder call: ComplianceManagement::MergeRequests::ComplianceViolationsFinder.new(user, group, { sort: 'MERGE_REQUEST_TITLE_DESC' }).execute
Plan: https://console.postgres.ai/shared/a154d266-0f2d-4ef4-8300-e9e4e68b1292 Raw SQL:

SELECT
    "merge_requests_compliance_violations".*
FROM
    "merge_requests_compliance_violations"
    LEFT OUTER JOIN "merge_requests" ON "merge_requests"."id" = "merge_requests_compliance_violations"."merge_request_id"
    LEFT OUTER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
    LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
WHERE
    "projects"."namespace_id" = 9970
ORDER BY
    "merge_requests"."title" DESC,
    "merge_requests_compliance_violations"."id" DESC;

Sort by merged at ASC

Finder call: ComplianceManagement::MergeRequests::ComplianceViolationsFinder.new(user, group, { sort: 'MERGED_AT_ASC' }).execute
Plan: https://console.postgres.ai/shared/7f5004ba-657e-4cea-9e3f-0184c28516b9 Raw SQL:

SELECT
    "merge_requests_compliance_violations".*
FROM
    "merge_requests_compliance_violations"
    LEFT OUTER JOIN "merge_requests" ON "merge_requests"."id" = "merge_requests_compliance_violations"."merge_request_id"
    LEFT OUTER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
    LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
    LEFT OUTER JOIN "merge_request_metrics" ON "merge_request_metrics"."merge_request_id" = "merge_requests"."id"
WHERE
    "projects"."namespace_id" = 9970
ORDER BY
    "merge_request_metrics"."merged_at" ASC,
    "merge_requests_compliance_violations"."id" ASC;

Sort by merged at DESC

Finder call: ComplianceManagement::MergeRequests::ComplianceViolationsFinder.new(user, group, { sort: 'MERGED_AT_DESC' }).execute
Plan: https://console.postgres.ai/shared/9921b5fc-216d-472e-94e9-66024d9b52e7 Raw SQL:

SELECT
    "merge_requests_compliance_violations".*
FROM
    "merge_requests_compliance_violations"
    LEFT OUTER JOIN "merge_requests" ON "merge_requests"."id" = "merge_requests_compliance_violations"."merge_request_id"
    LEFT OUTER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
    LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
    LEFT OUTER JOIN "merge_request_metrics" ON "merge_request_metrics"."merge_request_id" = "merge_requests"."id"
WHERE
    "projects"."namespace_id" = 9970
ORDER BY
    "merge_request_metrics"."merged_at" DESC,
    "merge_requests_compliance_violations"."id" DESC;

How to set up and validate locally

Setting up the violations

Enable the feature flag compliance_violations_graphql_type: echo "Feature.enable(:compliance_violations_graphql_type)" | bundle exec rails c
Go to a projects general settings and make sure Prevent approval by author. is unticked under the Merge request approvals section
Edit a file in the project and create a new merge request
Use the merge requests author to approve the merge request and then merge it
Wait for merging to be completed

Running the query

Open your preferred GraphQL caller. You could use [GDK_HOST]/-/graphql-explorer
Check that the violations you created above are shown using the query
Test the different filters and sorting options to check that they work as expected

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

I have evaluated the MR acceptance checklist for this MR.

Related to #347325 (closed)

Edited Feb 18, 2022 by Robert Hunt

Add GraphQL type to allow retrieval of compliance violations within a group