<!--IssueSummary start--> <details> <summary> Everyone can contribute. [Help move this issue forward](https://handbook.gitlab.com/handbook/marketing/developer-relations/contributor-success/community-contributors-workflows/#contributor-links) while earning points, leveling up and collecting rewards. </summary> - [Close this issue](https://contributors.gitlab.com/manage-issue?action=close&projectId=278964&issueIid=585903) </details> <!--IssueSummary end--> Currently, the `emails` table [is sharded by `user_id`](https://gitlab.com/gitlab-org/gitlab/-/blob/426563d77f2b3069126f1c0e0b7d9636a35f9fbc/db/docs/emails.yml#L12) . However, when pursuing [removing UNIQUE indexes from tables owned](https://gitlab.com/groups/gitlab-org/-/epics/20097) by ~"group::authentication" , we discovered that having UNIQUE indexes on this table per user creates some problems compared to per-organization. We implemented a workaround where secondary email confirmation requires a `user_id` parameter https://gitlab.com/gitlab-org/gitlab/-/merge_requests/214499 . This also required adding the `user_id` parameter to secondary email confirmation links: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/214600 . We also discussed this while de-scoping the UNIQUE index on `users.confirmation_token` https://gitlab.com/gitlab-org/gitlab/-/merge_requests/214348#note_2978468397 Based on this work, we should re-shard the table to use `organization_id` as a sharding key rather than `user_id` . This will require: 1. Adding an `organization_id` column to the `emails` table 2. Ensure that we always set `organization_id` for new `emails` records. 3. A batched background migration to copy `user.organization_id` from `users` to `emails` based on `emails.user_id` 4. Finalizing the migration 5. Cleaning up the changes in https://gitlab.com/gitlab-org/gitlab/-/merge_requests/214499 - remove the controller method we overrode from Devise, and instead override `Email.confirm_by_token` to use `Current.organization` 6. Remove the changes in https://gitlab.com/gitlab-org/gitlab/-/merge_requests/214600 adding the `user_id` param to secondary email confirmation links