Resolve "User mapping - Delete placeholder users when top-level group is deleted"

What does this MR do and why?

This MR introduces changes to clean up placeholder users when their associated top-level group is deleted.

During the import process, when a placeholder user is created, we now also create a corresponding record in a new table: import_placeholder_user_details (table schema provided below). This table includes a namespace_id along with other user-related metadata. The table has been added as part of this MR.

Import::PlaceholderUserDetail
placeholder_user_id
deletion_attempts
last_deletion_attempt_at
namespace_id
organization_id
created_at
updated_at

Indexes

CREATE INDEX index_import_placeholder_user_details_on_eligible_for_deletion ON import_placeholder_user_details USING btree (deletion_attempts, last_deletion_attempt_at, id) WHERE (namespace_id IS NULL);

CREATE INDEX index_import_placeholder_user_details_on_namespace_id ON import_placeholder_user_details USING btree (namespace_id);

CREATE INDEX index_import_placeholder_user_details_on_organization_id ON import_placeholder_user_details USING btree (organization_id);

CREATE INDEX index_import_placeholder_user_details_on_placeholder_user_id ON import_placeholder_user_details USING btree (placeholder_user_id);

The reason for this auxiliary table is that when a top-level group is deleted, its associated Import::SourceUser are also removed. As a result, we lose the direct link between deleted groups and their Placeholder Users (users with user_type: placeholder). With this new table, however, we can identify placeholder users whose namespace_id becomes nil — indicating that their parent group has been deleted.

The cleanup process is handled by a daily scheduled cron job, which invokes the existing DeletePlaceholderUserWorker. This worker DeletePlaceholderUserWorker checks if a placeholder user is safe to delete (i.e., not in use elsewhere) before removing them. Additionally, the deletion process is retried up to MAX_ATTEMPTS (15 times). After that, any placeholder users that still cannot be deleted will be skipped.

SQL Query

Since this is a newly created table, I've run EXPLAIN ANALYZE queries in the development environment. Both query plans are available for detailed inspection on explain.depesz.com as per mentioned here.

Method - eligible_for_deletion

Query plan - https://explain.depesz.com/s/fCBO#html

SELECT import_placeholder_user_details.* FROM (
  SELECT "import_placeholder_user_details".* 
  FROM "import_placeholder_user_details" 
  WHERE "import_placeholder_user_details"."deletion_attempts" < 15 
  AND "import_placeholder_user_details"."namespace_id" IS NULL 
  AND "import_placeholder_user_details"."last_deletion_attempt_at" IS NULL 
  
  UNION 
  
  SELECT "import_placeholder_user_details".* 
  FROM "import_placeholder_user_details" 
  WHERE "import_placeholder_user_details"."deletion_attempts" < 15 
  AND "import_placeholder_user_details"."namespace_id" IS NULL 
  AND "import_placeholder_user_details"."last_deletion_attempt_at" < '2025-05-05 07:03:14.336849'
) AS import_placeholder_user_details;

---

Method - increment_deletion_attempt

Query plan - https://explain.depesz.com/s/48Db#html

UPDATE "import_placeholder_user_details" 
SET "deletion_attempts" = COALESCE("deletion_attempts", 0) + 1, 
    "updated_at" = '2025-05-02 12:19:39.455014', 
    "last_deletion_attempt_at" = '2025-05-02 12:19:39.455014' 
WHERE "import_placeholder_user_details"."id" = 36;

References

User mapping - Delete placeholder users when top-level group is deleted

Screenshots or screen recordings

Before	After

How to set up and validate locally

Cases to verify

1. When a top-level group is deleted, all placeholder users that belong to that group should be deleted.
2. When a top-level group is deleted but some projects were transferred to another group, Preserve any placeholder users that are connected to transferred projects. Only delete placeholder users that aren't associated with any transferred projects.

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