GitHub User Mappings

When migrating companies and encountering duplicate users, the following SQL queries can be used for cleanup:

To return all GitHub identity mappings associated with legacy accounts:

FROM identity_mappings
WHERE tenant_id = 
  AND type = 'GITHUB'
  AND external_id LIKE '%@domain.com';

To list all @domain.com GitHub identities that have duplicate mappings in the table:

SELECT external_id, COUNT(*) AS mapping_count
FROM identity_mappings
WHERE tenant_id = 
  AND type = 'GITHUB'
  AND external_id LIKE '%@domain.com'
GROUP BY external_id
HAVING COUNT(*) > 1;

To remove obsolete mappings from old identities:

DELETE FROM identity_mappings
WHERE tenant_id=
  AND external_id=''
  AND type='GITHUB'
  AND source='PROVIDER';

To remove duplicate GitHub mappings:

WITH duplicated_identities AS (
  SELECT external_id, MIN(id) AS id
  FROM identity_mappings
  WHERE tenant_id=
    AND type='GITHUB'
  GROUP BY external_id
  HAVING COUNT(*) > 1
)
DELETE FROM identity_mappings
WHERE tenant_id=
  AND type='GITHUB'
  AND id IN (SELECT id FROM duplicated_identities)

These queries should be used with caution and only after verifying their impact on your specific Cortex environment.