Added filters for compliance violations

What does this MR do?

This MR implements filtering capabilities for compliance violations in the compliance management system. It adds support for filtering violations by project, control, status, and date ranges through both the finder class and GraphQL API.

Query plan:

When all the filters are applied

Query:

Click to expand query

SELECT *
FROM (
	WITH RECURSIVE "array_cte" AS MATERIALIZED(SELECT "namespaces"."id" FROM UNNEST(COALESCE((
						SELECT ids
						FROM (
							SELECT "namespace_descendants"."self_and_descendant_group_ids" AS ids
							FROM "namespace_descendants"
							WHERE "namespace_descendants"."outdated_at" IS NULL
								AND "namespace_descendants"."namespace_id" = 12395790
							) cached_query
						), (
						SELECT ids
						FROM (
							SELECT ARRAY_AGG("namespaces"."id") AS ids
							FROM (
								SELECT namespaces.traversal_ids [array_length(namespaces.traversal_ids, 1)] AS id
								FROM "namespaces"
								WHERE "namespaces"."type" = 'Group'
									AND (traversal_ids @ > ('{12395790}'))
								) namespaces
							) consistent_query
						))) AS namespaces(id))
		,"recursive_keyset_cte" AS (
			(
				SELECT NULL::project_compliance_violations AS records
					,array_cte_id_array
					,project_compliance_violations_created_at_array
					,project_compliance_violations_id_array
					,0::BIGINT AS count
				FROM (
					SELECT ARRAY_AGG("array_cte"."id") AS array_cte_id_array
						,ARRAY_AGG("project_compliance_violations"."created_at") AS project_compliance_violations_created_at_array
						,ARRAY_AGG("project_compliance_violations"."id") AS project_compliance_violations_id_array
					FROM (
						SELECT "array_cte"."id"
						FROM array_cte
						) array_cte
					LEFT JOIN LATERAL(SELECT "project_compliance_violations"."created_at" AS created_at, "project_compliance_violations"."id" AS id FROM "project_compliance_violations" WHERE "project_compliance_violations"."project_id" = 27673890
							AND "project_compliance_violations"."compliance_requirements_control_id" = 14084
							AND "project_compliance_violations"."status" = 0
							AND "project_compliance_violations"."created_at" <= '2025-11-09 23:59:59.999999'
							AND "project_compliance_violations"."created_at" >= '2025-11-01 00:00:00'
							AND "project_compliance_violations"."namespace_id" = "array_cte"."id" ORDER BY "project_compliance_violations"."created_at" DESC, "project_compliance_violations"."id" DESC LIMIT 1) project_compliance_violations ON TRUE
					WHERE "project_compliance_violations"."created_at" IS NOT NULL
						AND "project_compliance_violations"."id" IS NOT NULL
					) array_scope_lateral_query LIMIT 1
				)
			
			UNION ALL
			
			(
				SELECT (
						SELECT project_compliance_violations
						FROM "project_compliance_violations"
						WHERE "project_compliance_violations"."id" = recursive_keyset_cte.project_compliance_violations_id_array [position] LIMIT 1
						)
					,array_cte_id_array
					,recursive_keyset_cte.project_compliance_violations_created_at_array [:position_query.position-1] || next_cursor_values.created_at || recursive_keyset_cte.project_compliance_violations_created_at_array [position_query.position+1:]
					,recursive_keyset_cte.project_compliance_violations_id_array [:position_query.position-1] || next_cursor_values.id || recursive_keyset_cte.project_compliance_violations_id_array [position_query.position+1:]
					,recursive_keyset_cte.count + 1
				FROM recursive_keyset_cte
					,LATERAL(SELECT created_at, id, position FROM UNNEST(project_compliance_violations_created_at_array, project_compliance_violations_id_array) WITH ORDINALITY AS u(created_at, id, position) WHERE created_at IS NOT NULL
						AND id IS NOT NULL ORDER BY 1 DESC, 2 DESC LIMIT 1) AS position_query
					,LATERAL(SELECT "record"."created_at", "record"."id" FROM (
						VALUES (
							NULL
							,NULL
							)
						) AS nulls LEFT JOIN (
						SELECT "project_compliance_violations"."created_at" AS created_at
							,"project_compliance_violations"."id" AS id
						FROM "project_compliance_violations"
						WHERE "project_compliance_violations"."project_id" = 27673890
							AND "project_compliance_violations"."compliance_requirements_control_id" = 14084
							AND "project_compliance_violations"."status" = 0
							AND "project_compliance_violations"."created_at" <= '2025-11-09 23:59:59.999999'
							AND "project_compliance_violations"."created_at" >= '2025-11-01 00:00:00'
							AND "project_compliance_violations"."namespace_id" = recursive_keyset_cte.array_cte_id_array [position]
							AND (
								(
									"project_compliance_violations"."created_at"
									,"project_compliance_violations"."id"
									) < (
									recursive_keyset_cte.project_compliance_violations_created_at_array [position]
									,recursive_keyset_cte.project_compliance_violations_id_array [position]
									)
								)
						ORDER BY "project_compliance_violations"."created_at" DESC
							,"project_compliance_violations"."id" DESC LIMIT 1
						) record ON TRUE LIMIT 1) AS next_cursor_values
				)
			)
	SELECT (records).*
	FROM "recursive_keyset_cte" AS "project_compliance_violations"
	WHERE (count <> 0)
	) project_compliance_violations LIMIT 100;

Query plan:

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/45333/commands/139015

Changes included:

Finder Updates

• Add params parameter support to ComplianceViolationFinder
• Implement filtering methods for project, control, status, and date ranges
• Add comprehensive test coverage for all filter combinations

Model Updates

• Add scopes to ComplianceViolation model for filtering:
  • for_projects - Filter by project IDs
  • for_controls - Filter by control IDs
  • for_status - Filter by violation status
  • created_before - Filter by creation date (before)
  • created_after - Filter by creation date (after)

GraphQL Updates

• Add ComplianceViolationFilterInputType for filter parameters
• Update GroupViolationsResolver to accept and process filters
• Support filtering by project ID, control ID, status, and date ranges

How to validate locally

1. Enable the compliance framework feature in Rails console:
2. Create a compliance framework with requirements and controls via GraphQL
3. Generate some compliance violations with different statuses and dates
4. Test the filtering functionality via GraphQL:

query groupComplianceViolations {
  group(fullPath: "<group_path>") {
    id
    name
    projectComplianceViolations(filters:
      {
        controlId: "gid://gitlab/ComplianceManagement::ComplianceFramework::ComplianceRequirementsControl/<control_id>",
        createdAfter: "<date, ex: 2025-10-01>",
        createdBefore: "<date, ex: 2025-11-04>",
        projectId: "gid://gitlab/Project/<id>",
        status: DETECTED
      }
    ) {
      nodes {
        id
        createdAt
        project {
          id
          name
        }
        complianceControl {
          id
          name
        }
        status
      }
    }
  }
}   

5. Verify that the filters work correctly and return the expected results

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.

Related to

Closes #542342 (closed) #566903 (closed)