Add post deployment migration to delete orphaned user_group_member_roles

What does this MR do and why?

Add a post-deployment migration to clean up orphaned user_group_member_roles records.

These orphaned records were created due to a bug in the SAML group sync process where:

1. member.member_role_id was set to nil
2. The corresponding user_group_member_roles record wasn't properly deleted

• Bug identified in: #568186 (closed)
• Bug fixed in: !204122 (merged)

References

Bug: some user_group_member_roles have no corre... (#568186 - closed).

DB changes

Queries

• Batching query 1 EXPLAIN: https://console.postgres.ai/shared/49d52d8e-ddf2-4e7c-81ad-fce3e9c0c166
• Batching query 2 EXPLAIN: https://console.postgres.ai/shared/98982372-fca5-46aa-a787-a6bf8b5457d9
• Batch delete query EXPLAIN: https://console.postgres.ai/shared/9e325537-3cf0-4c4c-875d-cca263c9c2a6

Preparation when adding data migrations

src

If the migration itself is not reversible, details of how data changes could be reverted in the event of an incident. For example, in the case of a migration that deletes records (an operation that most of the times is not automatically reversible), how could the deleted records be recovered.

user_group_member_roles records are recoverable by looking at members and group_group_links tables. We have previously back-filled this table in !202181 (merged) and !201710 (merged).

Concise descriptions of possible user experience impact of an error; for example, “Issues would unexpectedly go missing from Epics”.

No impact for now since we haven't switched to using data from user_group_member_roles table yet (see #554706 (closed)).

Relevant data from the query plans that indicate the query works as expected; such as the approximate number of records that are modified or deleted.

There are around 600+ orphaned records in production as of Sept 9.

Screenshots or screen recordings

Before	After

How to set up and validate locally

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.