One customer, two spellings: when a capital letter split an account in two

The bug report

A customer messaged us: "I signed in, but my booking isn't there."

The booking was real. Staff had confirmed it, the advance was recorded, and the confirmation email had gone out. The customer signed in with Google, using the same email address, and landed on an empty dashboard as if they had never booked anything.

Except it wasn't quite the same email address. The booking had been made as Asha.Rao@gmail.com. Google signed them in as asha.rao@gmail.com. (We've swapped in a made-up address; the real one differed in exactly the same way, by a single capital letter.)

To a person those are obviously the same inbox. To our code they were two different strings, and so two different customers.

How one capital letter made two accounts

Here is the path, step by step. Part 4 describes the booking model in more detail.

1. A visitor fills in the booking form and types their email the way they usually write it, with capitals. We store it exactly as typed.
2. Staff confirm the booking. BookingConfirmationService looks for an existing customer, by phone and then by email. It finds none, so it creates a customer account with the email from the booking: Asha.Rao@gmail.com.
3. Later the customer clicks "Continue with Google". Google returns a verified email, always in lower case: asha.rao@gmail.com.
4. loginWithGoogle first looks up the Google account id. It's the first Google sign-in, so there's no match. Then it looks up the email:

Optional<Customer> byEmail = customerRepository.findByEmail(email);

That was a Spring Data derived query, which becomes WHERE c.email = ?. In PostgreSQL, = on text is an exact, byte-for-byte comparison. 'asha.rao@gmail.com' = 'Asha.Rao@gmail.com' is false.

1. No match, so the code did what it was designed to do for a brand-new visitor: it created a fresh Google-only account. The customer was now signed in to an account with no bookings, while their real account, with the booking, the payments and soon the photos, sat untouched next to it.

Nothing crashed and nothing was logged as an error. Every step did exactly what it was written to do. That's what makes this kind of bug easy to miss: it only shows up as a confused customer.

Are email addresses case-sensitive?

Strictly speaking, partly. The standard that defines email addresses says the domain (after the @) is case-insensitive, but the local part (before it) may be case-sensitive, and only the receiving mail server knows.

In practice, no mainstream provider uses that freedom. Gmail, Outlook, Yahoo and every company mail system we've seen deliver Asha.Rao@ and asha.rao@ to the same inbox. A system that treats them as different people protects nobody, and it splits real customers in two.

So we chose the rule that matches how people think: an email address is compared without regard to case, and surrounding spaces are ignored.

We deliberately stopped there. Gmail also ignores dots and anything after a + (asharao+shoots@gmail.com reaches the same inbox), but those are Gmail rules, not email rules. Applying them to every address would merge people on other providers who really are different, and someone using a + address usually wants it kept separate on purpose.

How many places had the same problem

Once we knew the cause, the question was how far it went. We searched the backend for every email lookup and found the code was inconsistent, which is the real lesson here.

Some lookups were already careful:

• The shared-gallery guest list compared lower(g.email) = lower(:email).
• "Your requests" on the dashboard matched bookings with lower(b.clientEmail) = lower(:email).
• Announcements matched typed addresses against lower(c.email).
• The email-change flow lower-cased the new address before saving it.

The most important ones were not:

• Google sign-in, through findByEmail. The reported bug.
• Password sign-in, the login code and forgot-password, through findByEmailOrPhone, which compared c.email = :emailOrPhone. A customer typing their email with a different capital than the one on file was told their credentials were wrong.
• Booking confirmation, which uses findByEmail to find a returning customer. If someone who had first signed up with Google booked again with a capitalised email (and a different phone), staff confirming the booking would create a second account.

Each careful lookup had been written by someone who thought about case at that moment. Nobody had made it a rule for the whole system, so the default (exact match) won wherever nobody thought about it.

The fix, in three layers

A fix in one place would leave the next new lookup free to repeat the mistake. So we fixed it at three levels: how we look customers up, how we store their emails, and what the database itself allows.

1. Lookups ignore case

Instead of finding and patching every caller, we changed the two repository methods everything goes through. They keep their names and signatures, so no caller changed, but now they run a case-insensitive query:

default Optional<Customer> findByEmail(String email) {
    if (email == null || email.isBlank()) return Optional.empty();
    return findAllByEmailIgnoringCase(email).stream().findFirst();
}

@Query("SELECT c FROM Customer c WHERE lower(c.email) = lower(trim(:email)) ORDER BY c.id")
List<Customer> findAllByEmailIgnoringCase(@Param("email") String email);

findByEmailOrPhone got the same treatment.

Two details are worth pointing out:

• It returns a list, then takes the first. Until old data is cleaned up (see below), two rows can match the same address in different case. A query that returns Optional<Customer> throws if it finds two rows, which would have turned "wrong account" into "can't sign in at all". Taking the oldest match is safe and predictable.
• A default method on a Spring Data interface is not parsed as a query. Spring only derives queries from abstract methods, so the name findByEmail can stay and every caller picks up the new behaviour.

2. Emails are stored in one form

Lookups that ignore case fix the symptom. Storing every email in one canonical form fixes the cause, and makes every other comparison (in SQL, in Java, in a spreadsheet export) behave too.

The safest place for that is the entity itself, so no code path can save an email without going through it:

@PrePersist
protected void onCreate() {
    // ...timestamps, defaults...
    normalizeEmails();
}

@PreUpdate
protected void onUpdate() {
    this.updatedAt = Instant.now();
    normalizeEmails();
}

public static String normalizeEmail(String value) {
    if (value == null) return null;
    String trimmed = value.trim();
    return trimmed.isEmpty() ? null : trimmed.toLowerCase(Locale.ROOT);
}

Locale.ROOT matters. Plain toLowerCase() uses the server's language settings, and in a Turkish locale, for example, the capital I becomes a dotless ı. That would quietly create a third spelling. Fixed locale, same result on every machine.

We left the booking's own client_email as the customer typed it. It's what staff see on the booking, and every lookup against it already ignored case. The account is what has to be unique, so the account is what we normalise.

3. The database refuses duplicates

The customers table already had email UNIQUE. It didn't help, because a unique constraint compares the same way = does: Asha.Rao@gmail.com and asha.rao@gmail.com are different values, so both were allowed.

PostgreSQL gives two ways to make uniqueness ignore case:

• The citext extension, a case-insensitive text type. It works well, but it changes the column type, which ripples into the ORM mapping, schema validation and every query.
• A unique index on an expression: lower(email). The column stays a normal VARCHAR, and the database refuses a second row whose lower-cased email already exists.

We chose the index:

CREATE UNIQUE INDEX uq_customers_email_lower ON customers (lower(email));

Now even a future bug in our Java code can't create a second account for the same address. The insert fails loudly instead of succeeding quietly, which is exactly the trade we want.

Repairing the accounts already split

New code stops new duplicates. It does nothing for the customer who already had two accounts, and the Google one would keep winning, because sign-in checks the Google account id before the email.

The repair is a Flyway migration (V116). It had to be careful, because it runs against live customer data and can't ask anyone for confirmation.

Find the groups. Customers whose emails are equal once trimmed and lower-cased.

Pick the account to keep. In each group, the one with the most bookings; on a tie, the oldest. Bookings are what customers come back for, so that's the account their history lives in.

ROW_NUMBER() OVER (
    PARTITION BY lower(trim(c.email))
    ORDER BY booking_count DESC, c.id
) AS rn

Move the Google sign-in. If a duplicate with no bookings has a Google account id and the keeper doesn't, the id moves to the keeper. The next time the customer clicks "Continue with Google", they land in the account with their booking.

There's an ordering trap here. google_id is unique, so the keeper can't receive it while the duplicate still holds it. The migration clears the duplicates first, then hands the id over, all inside one transaction.

Detach, don't delete. The empty duplicates get their email and Google id cleared. We did not delete them. A customer row is referenced from many tables (game rounds, sessions, audit entries, coupon wins), and deleting it means either cascading through all of those or failing on a foreign key halfway through a deploy. A detached row can't be signed into or found by email, which is all that matters.

Leave the hard cases to a person. If two accounts with the same email both have bookings, the migration doesn't guess. It skips them, lower-cases everything else, and only creates the unique index if no clashes remain. Otherwise it logs a notice saying which kind of account needs a manual merge, and the deploy carries on.

Lower-case everything else. Every remaining email is lower-cased, so the stored data matches the new rule.

We ran the migration on a local PostgreSQL 16 with a copy of the bug: the booking account in mixed case, the empty Google account in lower case, a third customer with stray spaces, and a pair that both had bookings. Afterwards the Google id sat on the booking account, the empty account was detached, the stray spaces were gone, the both-have-bookings pair was untouched with a notice, and on a clean copy the new index rejected a mixed-case insert.

The one thing the migration can't fix

The customer who reported this was still signed in, to the empty account. Sessions identify a customer by their account id, not their email, so that session stayed valid after the repair.

The fix for them is one step: sign out and sign in with Google again. This time the Google id points at the account with the booking. We told them that directly, which was quicker and less surprising than writing code to end sessions on detached accounts.

Takeaways

• Pick one rule for comparing identifiers and enforce it everywhere. Our code had several careful lookups and a few exact ones. The exact ones were the ones that mattered most.
• Fix it in layers. Lookups that ignore case, one stored form, and a database index that refuses duplicates. Each layer catches what the one above misses.
• UNIQUE is only as smart as =. For case-insensitive uniqueness in PostgreSQL, use a unique index on lower(column) or the citext type.
• Lower-case with a fixed locale. toLowerCase(Locale.ROOT), never the server's default.
• Normalise only what the standard allows. Case, yes. Gmail's dots and + tags, no: those are one provider's rules.
• Repair data you can repair safely, and hand the rest to a person. A migration that merges accounts should detach rather than delete, and skip any case where it would have to guess.
• Look for the bugs that never throw. Nothing failed here. The only signal was a customer saying "my booking isn't there".