5

From time to time, I notice user emails are duplicated. Even with model validation (app level) to check for uniqueness. I suspect this is related to concurrency concerns.

I've decided to add an index on the db level to address this.

The lower index is probably sensible to have anyway.

Even if we are guaranteeing that emails are lowered on the application level. Database provides a stronger guarantee of consistency.

It may never matter, but having the DB enforce it also can't hurt.

I can put in lots of extra safeguards to maximize the odds that there's never an uppercase character put in from the app side but the app still can't control for data going into the db from other sources, and all that code is prone to developer error, maintenance cost, etc.

class CreateUniqueIndexForUserEmails < ActiveRecord::Migration
  def up
    remove_index :users, :email

    execute <<-SQL
      CREATE UNIQUE INDEX index_users_on_lower_email ON users (LOWER(email));
    SQL
  end

  def down
    execute <<-SQL
      DROP INDEX index_users_on_lower_email;
    SQL

    add_index :users, :email
  end
end

I've also coupled this logic with this block of code in the user model:

def email=(value)
  write_attribute :email, value.downcase
end

Which ensures FooBar@email.com gets re-written as foobar@email.com (on the app level).

I've read an RFC online, mainly from the pointers at Are email addresses case sensitive? that indicate some email servers care about case sensitivity.

Even still, when I send emails out today. I hardly don't take mind into casing. In fact, I type the email out as down-cased. Email still gets delivered.

Is the RFC something to consider highly? i.e. If user inputs FooBar@email.com, app registers email as foobar@email.com. Will this have any impact on email "deliverability"?

If not, what other concerns should I take into account?

Community
  • 1
  • 1
Christian Fazzini
  • 19,613
  • 21
  • 110
  • 215
  • Not an actual answer, just a hack suggestion: Why not have a separate column for storing the "normalied" email (like `normalized_email`) which should also be unique? I mean, in rare cases when case indeed matters AND there are two users with otherwise identical emails, you can either ask one of them to use other email or handle this in some other way. – Ivan Kolmychek Feb 16 '17 at 09:31
  • Having a 2nd column is a suggestion I've considered as well. But before this consideration, I would like to know if the proposal about lower-casing emails is a recommended approach – Christian Fazzini Feb 18 '17 at 08:17

1 Answers1

1

Devise applies downcase on the email attribute, so I think it is not an issue.

See: https://github.com/plataformatec/devise/blob/f7b6d786066cef2f5e8d2ce9c6b6cc83918580eb/test/models/database_authenticatable_test.rb#L17

See other assertions in the Devise test file for transformations to consider.

tildedash
  • 71
  • 1
  • 5