Fix Code Coverage feature Project.with_code_coverage joining `projects` and `ci_daily_build_group_report_results`

From !62092 (closed) we've identified Project.with_code_coverage joins between non ci_* tables and ci_* tables. This will not be possible when ci_* tables are moved to a separate database.

The query is:

SELECT "projects".* FROM "projects"
INNER JOIN "ci_daily_build_group_report_results" ON "ci_daily_build_group_report_results"."project_id" = "projects"."id"
WHERE ((data->'coverage') IS NOT NULL)
AND "ci_daily_build_group_report_results"."default_branch" = TRUE
GROUP BY "projects"."id"

Options (Before #336617 (closed) was done)

  1. Change this query to 2 queries to avoid the join (not sure if this will be possible yet as it depends on how this scope is used if you can avoid loading too many project IDs into memory)
  2. Refactor/change the feature so that this query is not necessary
  3. De-normalize data in some way so that you don't need to join

Sharding team recommended option

This seems like it's very similar to #336590 (closed) . Probably the best option is to introduce a combined table to solve both problems called projects_with_ci_feature_usage with 2 columns project_id, ci_feature and then we can just store "code_coverage" and "security_report" in the ci_feature column when this is first used for the project.

The resulting query would then be:

SELECT "projects".* FROM "projects"
INNER JOIN "projects_with_ci_feature_usage" ON "projects_with_ci_feature_usage"."project_id" = "projects"."id"
WHERE "projects_with_ci_feature_usage"."ci_feature" = 'code_coverage'

Steps to take based on #336617 (closed)

  1. Create MR to introduce the new projects_with_ci_feature_usage table. Don't implement any logic yet that inserts into the table so we can quickly iterate and have the model available to both #336199 (closed) and #336590 (closed).
  2. For #336199 (closed), we can implement MR that upserts into projects_with_ci_feature_usage when we execute the Ci::DailyBuildGroupReportResultService. But don't query the new table yet.
  3. Implement MR for backfilling new table with projects that have coverage.
  4. Finally, fix the query to use the new table.

Steps 1 to 2 or 1 to 3 can possibly be in the same MR.

Edited by Erick Bajao