Update query for banned user project deletion

What does this MR do and why?

This MR fixes an issue with auto-deletion of banned user projects where projects were deleted if the creator was banned and if any owner was also banned. This caused a problem since the without_created_and_owned_by_banned_user scope, which is used to hide projects of banned users, considers only projects that are created and owned by the same banned user.

In addition to a query fix, additional conditions were added to ensure the following:

• The project is still created and owned by the banned user
• The project is not paid.
• The project has not had any activity in 30 days
• The project is not owned by any active users

Database

Added scope .with_created_and_owned_by_banned_user

The scope is run using a batch of user IDs so I simulated that with SELECT user_id FROM banned_users ORDER BY user_id DESC LIMIT 1000. I also added a limit to the entire query since it is also batched.

Query

SELECT "projects"."creator_id",
       "projects"."id"
FROM "projects"
WHERE "projects"."creator_id" IN
    (SELECT user_id
     FROM banned_users
     ORDER BY user_id DESC
     LIMIT 1000)
  AND "projects"."pending_delete" = FALSE
  AND (EXISTS
         (SELECT 1
          FROM "banned_users"
          INNER JOIN project_authorizations ON project_authorizations.user_id = banned_users.user_id
          WHERE (projects.creator_id = banned_users.user_id)
            AND (project_authorizations.project_id = projects.id)
            AND "project_authorizations"."access_level" = 50))
LIMIT 1000;

Explain output: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/36283/commands/111794

Time: 293.820 ms
  - planning: 10.826 ms
  - execution: 282.994 ms
    - I/O read: 252.575 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 7427 (~58.00 MiB) from the buffer pool
  - reads: 2532 (~19.80 MiB) from the OS file cache, including disk I/O
  - dirtied: 82 (~656.00 KiB)
  - writes: 0 

Added scope .with_active_owners

Scope is used with a single project

Query

SELECT 1 AS one
FROM "projects"
WHERE "projects"."id" = 278964
  AND (EXISTS
         (SELECT 1
          FROM "users"
          INNER JOIN project_authorizations ON project_authorizations.user_id = users.id
          WHERE ("users"."state" IN ('active'))
            AND "users"."user_type" IN (0,
                                        6,
                                        4,
                                        13)
            AND "users"."user_type" = 0
            AND (project_authorizations.project_id = projects.id)
            AND "project_authorizations"."access_level" = 50))
LIMIT 1

Explain output: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/36283/commands/111708

Time: 5.600 ms
  - planning: 5.482 ms
  - execution: 0.118 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 16 (~128.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

References

Please include cross links to any resources that are relevant to this MR. This will give reviewers and future readers helpful context to give an efficient review of the changes introduced.

• https://gitlab.zendesk.com/agent/tickets/605326
• https://gitlab.com/gitlab-org/modelops/anti-abuse/team-tasks/-/issues/702

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Before	After

How to set up and validate locally

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

1. Enable the feature flag

   Feature.enable(:delete_banned_user_projects)

2. Create a user.
3. With the user create a project.
4. Invite the user as an owner of a project the user did not create.
5. Ban the user
6. Update the last_activity_at timestamp for the project

   project.update!(last_activity_at: 31.days.ago)

7. Monitor the sidekiq and application logs

   tail -f log/sidekiq.log | grep BannedUserProjectDeletion
   tail -f log/application_json.log | grep BannedUserProjectDeletion

8. Trigger the AntiAbuse::BannedUserProjectDeletionCronWorker

   AntiAbuse::BannedUserProjectDeletionCronWorker.new.perform

9. Nothing should have happened since the user has not been banned for 60 days. Update the created_at timestamp of the banned user and re-trigger the worker. You should now see logs emitted related to the project deletion and the project created by the banned user should be destroyed.

   Users::BannedUser.last.update!(created_at: 61.days.ago)
   AntiAbuse::BannedUserProjectDeletionCronWorker.new.perform

10. Attempt the steps again but downgrade the project creator to a maintainer. The project should not be deleted since the banned user is not an owner and creator.
11. Attempt the steps again with a project in a paid namespace. The project should not be deleted.
12. Attempt the steps again but set last_activity_at to 29.days.ago. The project should not be deleted.