Scripts to find staff users with duplicated emails, phone numbers, mobile phone numbers

Problem: Users who have duplicate emails, phone numbers, or mobile phone numbers will not receive emails for the Forgot Password/Forgot Username functionality.

Solution: Run the set of provided scripts to find users with duplicate data and remove the duplicates.

Information about staff users across all tenants is stored in the central tenant, table user_tenant. This table stores information only about staff users.

Important: replace ${consortium} with the appropriate tenant schema of the centrlal tenant before execution.

1.1 SQL script to find staff users with duplicate email addresses across tenants:

  SELECT                                                                                                                                                                            
      ut.username,                                                                                                                                                                  
      ut.email,                                                                                                                                                                     
      ut.tenant_id AS tenant                                                                                                                                                        
  FROM                                                                                                                                                                              
      consortium_mod_users.user_tenant ut                                                                                                                                                                
  WHERE                                                                                                                                                                             
      ut.email IN (                                                                                                                                                                 
          SELECT email                                                                                                                                                              
          FROM consortium_mod_users.user_tenant                                                                                                                                                          
          WHERE email IS NOT NULL                                                                                                                                                   
            AND email != ''                                                                                                                                                         
          GROUP BY email                                                                                                                                                            
          HAVING COUNT(*) > 1                                                                                                                                                       
      )                                                                                                                                                                             
  ORDER BY                                                                                                                                                                          
      ut.email,                                                                                                                                                                     
      ut.tenant_id,                                                                                                                                                                 
      ut.username;

1.2 SQL script companion that shows a summary count of how many duplicates exist per email:

SELECT                                                                                                                                                                            
      email,                                                                                                                                                                        
      COUNT(*) AS user_count,                                                                                                                                                       
      STRING_AGG(DISTINCT tenant_id, ', ') AS tenants                                                                                                                               
  FROM                                                                                                                                                                              
      consortium_mod_users.user_tenant                                                                                                                                                                   
  WHERE                                                                                                                                                                             
      email IS NOT NULL                                                                                                                                                             
      AND email != ''                                                                                                                                                               
  GROUP BY                                                                                                                                                                          
      email                                                                                                                                                                         
  HAVING                                                                                                                                                                            
      COUNT(*) > 1                                                                                                                                                                  
  ORDER BY                                                                                                                                                                          
      user_count DESC,                                                                                                                                                              
      email;

2.1 SQL script to find staff users with duplicate phone numbers across tenants:

  SELECT                                                                                                                                                                            
      ut.username,                                                                                                                                                                  
      ut.phone_number,                                                                                                                                                                     
      ut.tenant_id AS tenant                                                                                                                                                        
  FROM                                                                                                                                                                              
      consortium_mod_users.user_tenant ut                                                                                                                                                                
  WHERE                                                                                                                                                                             
      ut.phone_number IN (                                                                                                                                                                 
          SELECT phone_number                                                                                                                                                              
          FROM consortium_mod_users.user_tenant                                                                                                                                                          
          WHERE phone_number IS NOT NULL                                                                                                                                                   
            AND phone_number != ''                                                                                                                                                         
          GROUP BY phone_number                                                                                                                                                            
          HAVING COUNT(*) > 1                                                                                                                                                       
      )                                                                                                                                                                             
  ORDER BY                                                                                                                                                                          
      ut.phone_number,                                                                                                                                                                     
      ut.tenant_id,                                                                                                                                                                 
      ut.username;

2.2 SQL script companion that shows a summary count of how many duplicates exist per phone number:

SELECT                                                                                                                                                                            
      phone_number,                                                                                                                                                                        
      COUNT(*) AS user_count,                                                                                                                                                       
      STRING_AGG(DISTINCT tenant_id, ', ') AS tenants                                                                                                                               
  FROM                                                                                                                                                                              
      consortium_mod_users.user_tenant                                                                                                                                                                   
  WHERE                                                                                                                                                                             
      phone_number IS NOT NULL                                                                                                                                                             
      AND phone_number != ''                                                                                                                                                               
  GROUP BY                                                                                                                                                                          
      phone_number                                                                                                                                                                         
  HAVING                                                                                                                                                                            
      COUNT(*) > 1                                                                                                                                                                  
  ORDER BY                                                                                                                                                                          
      user_count DESC,                                                                                                                                                              
      phone_number;

3.1 SQL script to find staff users with duplicate mobile phone numbers across tenants:

  SELECT                                                                                                                                                                            
      ut.username,                                                                                                                                                                  
      ut.mobile_phone_number,                                                                                                                                                                     
      ut.tenant_id AS tenant                                                                                                                                                        
  FROM                                                                                                                                                                              
      consortium_mod_users.user_tenant ut                                                                                                                                                                
  WHERE                                                                                                                                                                             
      ut.mobile_phone_number IN (                                                                                                                                                                 
          SELECT mobile_phone_number                                                                                                                                                              
          FROM consortium_mod_users.user_tenant                                                                                                                                                          
          WHERE mobile_phone_number IS NOT NULL                                                                                                                                                   
            AND mobile_phone_number != ''                                                                                                                                                         
          GROUP BY mobile_phone_number                                                                                                                                                            
          HAVING COUNT(*) > 1                                                                                                                                                       
      )                                                                                                                                                                             
  ORDER BY                                                                                                                                                                          
      ut.mobile_phone_number,                                                                                                                                                                     
      ut.tenant_id,                                                                                                                                                                 
      ut.username;

3.2 SQL script companion that shows a summary count of how many duplicates exist per mobile phone number:

SELECT                                                                                                                                                                            
      mobile_phone_number,                                                                                                                                                                        
      COUNT(*) AS user_count,                                                                                                                                                       
      STRING_AGG(DISTINCT tenant_id, ', ') AS tenants                                                                                                                               
  FROM                                                                                                                                                                              
      consortium_mod_users.user_tenant                                                                                                                                                                   
  WHERE                                                                                                                                                                             
      mobile_phone_number IS NOT NULL                                                                                                                                                             
      AND mobile_phone_number != ''                                                                                                                                                               
  GROUP BY                                                                                                                                                                          
      mobile_phone_number                                                                                                                                                                         
  HAVING                                                                                                                                                                            
      COUNT(*) > 1                                                                                                                                                                  
  ORDER BY                                                                                                                                                                          
      user_count DESC,                                                                                                                                                              
      mobile_phone_number;

SQL script to find users with duplicate emails:

  SELECT id, username, "personal.email", "personal.phone", "personal.mobilePhone"                                                                                                                                                   
  FROM (                                                                                                                                                                                                                            
      SELECT                                                                                                                                                                                                                        
          id,                                                                                                                                                                                                                       
          jsonb->>'username' AS username,                                                                                                                                                                                           
          jsonb->'personal'->>'email' AS "personal.email",                                                                                                                                                                          
          jsonb->'personal'->>'phone' AS "personal.phone",                                                                                                                                                                          
          jsonb->'personal'->>'mobilePhone' AS "personal.mobilePhone",                                                                                                                                                              
          COUNT(*) OVER (PARTITION BY jsonb->'personal'->>'email') AS cnt                                                                                                                                                           
      FROM <<tenant>>_mod_users.users                                                                                                                                                                                                                    
      WHERE jsonb->'personal'->>'email' IS NOT NULL                                                                                                                                                                                 
        AND jsonb->'personal'->>'email' != ''
		AND (jsonb->>'type' IS NULL OR jsonb->>'type' = '' OR jsonb->>'type' = 'staff')
  ) t                                                                                                                                                                                                                               
  WHERE cnt > 1                                                                                                                                                                                                                     
  ORDER BY "personal.email", username;

SQL script to find users with duplicate phone numbers:

  SELECT id, username, "personal.email", "personal.phone", "personal.mobilePhone"                                                                                                                                                   
  FROM (                                                                                                                                                                                                                            
      SELECT                                                                                                                                                                                                                        
          id,                                                                                                                                                                                                                       
          jsonb->>'username' AS username,                                                                                                                                                                                           
          jsonb->'personal'->>'email' AS "personal.email",                                                                                                                                                                          
          jsonb->'personal'->>'phone' AS "personal.phone",                                                                                                                                                                          
          jsonb->'personal'->>'mobilePhone' AS "personal.mobilePhone",                                                                                                                                                              
          COUNT(*) OVER (PARTITION BY jsonb->'personal'->>'phone') AS cnt                                                                                                                                                           
      FROM <<tenant>>_mod_users.users                                                                                                                                                                                                                    
      WHERE jsonb->'personal'->>'phone' IS NOT NULL                                                                                                                                                                                 
        AND jsonb->'personal'->>'phone' != ''
		AND (jsonb->>'type' IS NULL OR jsonb->>'type' = '' OR jsonb->>'type' = 'staff')
  ) t                                                                                                                                                                                                                               
  WHERE cnt > 1                                                                                                                                                                                                                     
  ORDER BY "personal.phone", username;

SQL script to find users with duplicate mobile phone numbers:

  SELECT id, username, "personal.email", "personal.phone", "personal.mobilePhone"                                                                                                                                                   
  FROM (                                                                                                                                                                                                                            
      SELECT                                                                                                                                                                                                                        
          id,                                                                                                                                                                                                                       
          jsonb->>'username' AS username,                                                                                                                                                                                           
          jsonb->'personal'->>'email' AS "personal.email",                                                                                                                                                                          
          jsonb->'personal'->>'phone' AS "personal.phone",                                                                                                                                                                          
          jsonb->'personal'->>'mobilePhone' AS "personal.mobilePhone",                                                                                                                                                              
          COUNT(*) OVER (PARTITION BY jsonb->'personal'->>'mobilePhone') AS cnt                                                                                                                                                     
      FROM <<tenant>>_mod_users.users                                                                                                                                                                                                                    
      WHERE jsonb->'personal'->>'mobilePhone' IS NOT NULL                                                                                                                                                                           
        AND jsonb->'personal'->>'mobilePhone' != ''
		AND (jsonb->>'type' IS NULL OR jsonb->>'type' = '' OR jsonb->>'type' = 'staff')
  ) t                                                                                                                                                                                                                               
  WHERE cnt > 1                                                                                                                                                                                                                     
  ORDER BY "personal.mobilePhone", username;