Write a script for cleaning up backend emails

[jbubar]

Overview

We have a problem where emails are saved in the backend with uppercase letters. We need a script that is designed to comb through mongo database and change the uppercase letters to lowercase letters.

Action Items

• create a script to pull all of the emails from the database that have uppercase letters
• check to see if these emails have any collisions (there might be an email that is saved twice i.e. [email protected] && [email protected])
• If there is no collision change the uppercase letters to lowercase letters
• if there is a collision check if the user has any permissions and transfer them to the older account.
• instead of deleting the younger account change the email to their same email + _olderAccountUserID i.e. josh_F4G&d#[email protected]
• create a new folder called scripts in the backend and place this file there.

Resources/Instructions

[evanyang1]

For reference and to just put it down. I'm stuck but I'll continue thinking about it.

const MongoClient = require('mongodb').MongoClient;
const uri = 'pull from env';
const client = new MongoClient(uri, { useNewUrlParser: true });

// Pull all emails with uppercase letters

client.connect((err) => {
  const collection = client.db('test').collection('users');
  collection.find({ email: { $regex: /[A-Z]/ } }).toArray((err, users) => {
    // users is an array of user documents with uppercase emails
  });
});

// Check for email collisions

collection.find({ email: { $regex: /[A-Z]/ } }).toArray((err, users) => {
  users.forEach((user) => {
    const lowerCaseEmail = user.email.toLowerCase();
    collection.findOne({ email: lowerCaseEmail }, (err, existingUser) => {
      if (existingUser) {
        // handle collision
      } else {
        // convert email to lowercase
      }
    });
  });
});

// Handle collisions and convert emails to lowercase

if (existingUser) {
  // transfer permissions from user to existingUser
  // change user's email to email + _olderAccountUserID
} else {
  // convert user's email to lowercase
  collection.updateOne({ _id: user._id }, { $set: { email: lowerCaseEmail } });
}

[JackHaeg]

@freaky4wrld Please provide update

1. Progress: "What is the current status of your project? What have you completed and what is left to do?"
2. Blockers: "Difficulties or errors encountered."
3. Availability: "How much time will you have this week to work on this issue?"
4. ETA: "When do you expect this issue to be completed?"
5. Pictures or links* (if necessary): "Add any pictures or links that will help illustrate what you are working on."

• remember to add links to the top of the issue if they are going to be needed again.

[freaky4wrld]

@jbubar @spiteless the issue here describes of pulling uppercase emails and then detect for collisions, what about emails that are not uppercase and still have collisions, for eg:

{
  name: { firstName: 'Trillium', lastName: 'Smith' },
  accessLevel: 'admin',
  skillsToMatch: [],
  projects: [],
  textingOk: false,
  managedProjects: [],
  isActive: true,
  _id: '633b9a74d98663001f8b5c46',
  email: '[email protected]',
  currentRole: 'Supreme Leader',
  desiredRole: 'Front end developer',
  newMember: false,
  firstAttended: 'OCT 2022',
  createdDate: '2022-10-04T02:29:08.363Z',
  __v: 0
}
{
  name: { firstName: 'Trillium', lastName: 'Smith' },
  accessLevel: 'admin',
  skillsToMatch: [],
  projects: [],
  textingOk: false,
  managedProjects: [ '6407a1a8f97d2497a9f09dfa', '640500c62f66fc21b41289a5' ],
  isActive: false,
  _id: '5e965e554e2fc70017aa3970',
  email: '[email protected]',
  currentRole: 'Student',
  desiredRole: 'Full stack or front end development',
  newMember: true,
  firstAttended: 'APR 2020',
  createdDate: '2020-04-15T01:07:33.445Z',
  __v: 0
}

should we detect collisions for those emails as well and then do the changes as described above

[trillium]

Yeah, that's a great example. We want all emails in the db to be saved lower case, so if any character in the email is uppercase it needs to be migrated.

This is a good case too in that we need to figure out which account information needs to be preserved. In general we're taking the stance taht the older information is more important to the user.

Some things to keep in mind:

• The array states should be a union of their values eg:

//profile 1
 { managedProjects: [  project_id_1, project_id_2 ] }
// profile 2
 { managedProjects: [  project_id_3 ] }
// output
 { managedProjects: [  project_id_1, project_id_2, project_id_3 ] }

• The highest access level the user has should be preserved eg admin over user
• The older createdDate and firstAttended should be preserved

// profile 1
 {
 createdDate: '2022-10-04T02:29:08.363Z',
 firstAttended: 'OCT 2022',
}
// profile 2
 {
 createdDate: '2020-04-15T01:07:33.445Z',
 firstAttended: 'APR 2020',
} 
// output
 {
 createdDate: '2020-04-15T01:07:33.445Z',
 firstAttended: 'APR 2020',
 } 

• The boolean states of textingOk, isActive, newMember, should be an OR operation between the two states eg:

// profile 1 
{ isActive: false }
// profile 2
{ isActive: true }
// output
{ isActive: true }

• The newer currentRole and desiredRole should be preserved

[JackHaeg]

@jbubar Will review today

[JackHaeg]

Nayan (freaky4wrld) is no longer on the team, however his closed PR has some excellent work on this issue.

• Please reference: feat: script to clean up backend emails #1612 when working on the backend portion of this issue.

@jbubar Mentioned this might be a good issue for @ntrehan to take up after finalizing the User Permission Management issues.