Add archived project filter for Vulnerabilities

What does this MR do and why?

We have issue 213144 open to stop including archived project vulnerabilities in various places in the UI

As step 1 for this change, we added the for_projects scope to the Vulnerability and Vulnerability::Read models.

In this MR we use utilize that new scope in changing the associated finders to have different behavior with regards to archived projects

We want the default behavior for all finders to be:

Do not include vulnerability` or `vulnerability_read` objects in the
result-set if the project they are associated with is currently
`archived`

At the same time we want to, in a followup commit, provide API access
to toggle on /off the inclusion of `archived` objects

follow-up MRs will use this field to:

• expose this as a nullable argument in the VulnerabilitiesResolver

---

Changelog: fixed EE: true

Raw SQL

SQL derived from:

• tailing the logs:

  tail -f log/*.log | grep --line-buffered -E ".*FROM \"vulnerability\_reads\".*"

• loading vulnerabilities dashboard: e.g. http://gdk.test:3000/groups/flightjs/-/security/vulnerabilities/

original

SELECT
    "vulnerability_reads".*
FROM
    "vulnerability_reads"
WHERE
    "vulnerability_reads"."vulnerability_id" IN (
        SELECT
            "vulnerability_reads"."vulnerability_id"
        FROM
            unnest(ARRAY (
                    SELECT
                        "namespaces"."id"
                    FROM "namespaces"
                    WHERE
                        "namespaces"."type" = 'Group'
                        AND (traversal_ids @> ('{33}')))::bigint[]) AS "namespace_ids" ("namespace_id"),
            unnest('{6,2,5,3,1,0,4,99}'::smallint[]) AS "report_types" ("report_type"),
            unnest('{1,4}'::smallint[]) AS "states" ("state"),
            LATERAL (
                SELECT
                    "vulnerability_reads"."namespace_id",
                    "vulnerability_reads"."report_type",
                    "vulnerability_reads"."state",
                    "vulnerability_reads"."severity",
                    "vulnerability_reads"."vulnerability_id"
                FROM
                    "vulnerability_reads"
                WHERE (vulnerability_reads."namespace_id" = "namespace_ids"."namespace_id")
                AND (vulnerability_reads."report_type" = "report_types"."report_type")
                AND (vulnerability_reads."state" = "states"."state")
            ORDER BY
                "vulnerability_reads"."severity" DESC,
                "vulnerability_reads"."vulnerability_id" DESC
            LIMIT 21) AS vulnerability_reads
    ORDER BY
        "vulnerability_reads"."severity" DESC,
        "vulnerability_reads"."vulnerability_id" DESC
    LIMIT 21)
ORDER BY
    "vulnerability_reads"."severity" DESC,
    "vulnerability_reads"."vulnerability_id" DESC
LIMIT 21

updated

SELECT
    "vulnerability_reads".*
FROM
    "vulnerability_reads"
    INNER JOIN "projects" ON "projects"."id" = "vulnerability_reads"."project_id"
WHERE
    "vulnerability_reads"."namespace_id" IN (
        SELECT
            "namespaces"."id"
        FROM
            "namespaces"
        WHERE
            "namespaces"."type" = 'Group'
            AND (traversal_ids @> ('{33}')))
    AND "projects"."archived" = FALSE
    AND "vulnerability_reads"."report_type" IN (6, 2, 5, 3, 1, 0, 4, 99)
    AND "vulnerability_reads"."state" IN (1, 4)
ORDER BY
    "vulnerability_reads"."severity" DESC,
    "vulnerability_reads"."vulnerability_id" DESC
LIMIT 21

explain queries

• original: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/21399/commands/69759
• updated: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/21399/commands/69763

Screenshots or screen recordings

Before	After

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

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 #213144 (closed)