Add new violations export

What does this MR do and why?

Add backend for violations export

References

• Refs: #551244 (closed)

Screenshots or screen recordings

Export_Demo

How to set up and validate locally

1. Make sure you have violations present for a group. (E.g. Group w/ ID=33) If not check out hraghuvanshi-comp-violations-seeder and run FILTER=compliance_report_data SEED_COMPLIANCE_REPORT_DATA=1 GROUP_ID=33 bundle exec rake db:seed_fu, or leave out the group ID to have it create one for you

2. Go to compliance center

3. Click on Export

4. Select "Export compliance violations report"

5. Check local emails in letter_opener http://127.0.0.1:3000/rails/letter_opener

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.

Database

Note: production doesn't have any rows for this yet. Hence it Joe cannot necessarily return very meaningful results other than that we see that the indexes defined are used.

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/42139/commands/129177

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" = 9970) 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 @> ('{9970}'))) namespaces) consistent_query))
) AS namespaces(id)
), "recursive_keyset_cte" AS ((SELECT NULL::bigint AS id, array_cte_id_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"."id") AS project_compliance_violations_id_array FROM (SELECT "array_cte"."id" FROM array_cte) array_cte LEFT JOIN LATERAL (SELECT "project_compliance_violations"."id" AS id FROM "project_compliance_violations" WHERE "project_compliance_violations"."namespace_id" = "array_cte"."id" AND ("project_compliance_violations"."id" < 208622702) ORDER BY "project_compliance_violations"."id" DESC LIMIT 1) project_compliance_violations ON TRUE WHERE "project_compliance_violations"."id" IS NOT NULL) array_scope_lateral_query LIMIT 1)
UNION ALL
(SELECT recursive_keyset_cte.project_compliance_violations_id_array[position], array_cte_id_array, 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 id, position FROM UNNEST(project_compliance_violations_id_array) WITH ORDINALITY AS u(id, position) WHERE id IS NOT NULL ORDER BY 1 DESC LIMIT 1) AS position_query,
LATERAL (SELECT "record"."id" FROM (VALUES (NULL)) AS nulls LEFT JOIN (SELECT "project_compliance_violations"."id" AS id FROM "project_compliance_violations" WHERE "project_compliance_violations"."namespace_id" = recursive_keyset_cte.array_cte_id_array[position] AND ("project_compliance_violations"."id" < recursive_keyset_cte.project_compliance_violations_id_array[position]) ORDER BY "project_compliance_violations"."id" DESC LIMIT 1) record ON TRUE LIMIT 1) AS next_cursor_values
)) SELECT id FROM "recursive_keyset_cte" AS "project_compliance_violations" WHERE (count <> 0)) project_compliance_violations LIMIT 25 /*application:sidekiq,correlation_id:01K1XY9QD8FBS2JGXHEEQ71YAK,jid:e147adc0724b27b5cdc4329b,endpoint_id:ComplianceManagement::Groups::ComplianceViolationsExportMailerWorker,db_config_database:gitlabhq_development,db_config_name:main,line:/lib/gitlab/pagination/keyset/iterator.rb:26:in `block in each_batch'*/