INVITE API creates case sensitive e-mail accounts

[tiblu]

INVITE API creates case sensitive e-mail accounts

[tiblu]

Release no. 1 - 21.02.2022 17:00

Released fixes so that no more new caseSensitive e-mails would appear in the DB.

Migration to come later.

[tiblu]

Migration

The migration logic will be following:

• Find all Users with duplicate e-mails with different capitalization.
• Find which account of the duplicates has most Activities.
• Mark all other accounts as deleted. (deletedAt).
• Record account delete activity with the reference to the migration.

Why we do not migrate data to 1 account from the duplicates?

1. Most likely the most active account is the one that sees use.
2. It is likely that if User was invited, they did not see the invitation anyway due to the capitalization difference in e-mail and was re-invited.
3. We cannot alter vote history.
4. Resolving conflicts where User has access to a resource with different accounts is going to be expensive.

TLDR: Migration const vs benefit ratio makes it unfeasible.

[tiblu]

Release 05.03.2022 01:27 - Migration

https://github.com/citizenos/citizenos-api/blob/37decc3c2747ae53197115e8a566759a3180eaf8/db/migrations/20220228174313-duplicate-email-users-issue-234.js

• DB backup - b2299
• Unique Users migrated: 36
• Total Users marked as deleted: 37

NOTE!: IF by any chance we still end up creating Users with NOT lowercased e-mails, this migration can be re-run.

How can I spot a migrated User?

• Migrated Users - SELECT id, email FROM "Users" WHERE "email" SIMILAR TO '%\_\_migrated\_\d{1,}';
• Activities - SELECT COUNT(1) FROM "Activities" WHERE data->>'__migrationId' = '20220228174313-duplicate-email-users-issue-234.js';

[anettlinno]

Triage 60. Important update. Est. dev. time 8 hours. Sending to development.