The Soft Delete Bug That Lets Duplicate Accounts Slip Through (And the Fix in Laravel)

A client creates an account using the email [e-mail]. Later, they delete the account. A few weeks later, they register again, using the same email they used in their former account.

The registration fails with 'email already taken'.

Why? Because Laravel's soft delete doesn't literally remove the row from the database. It is only a filter on Laravel's side. To your database, the email is still a duplicate.

What Soft Delete Actually Does

When a model uses the SoftDeletes trait, delete() doesn't actually run a DELETE query. Instead, it runs an UPDATE query, changing the deleted_at column to the timestamp it was deleted at. The rest of the row remains completely intact.

Behind the scenes, Laravel adds WHERE deleted_at IS NULL to queries such as User::find() or User::all(), filtering out soft deleted rows. The rows aren't gone, they only get filtered out. Using a command such as User::withTrashed() can still retrieve them.

The database doesn't know 'soft deleted'. It only sees deleted_at with a timestamp.

Where the unique constraint actually lives

A unique constraint on email isn't enforced by Laravel. Instead, it's enforced by the database. When a new row is inserted, the DB checks: 'does any existing row in this table have the same email?'

The check is never enforced by Laravel, and the DB isn't aware that deleted_at with a timestamp means soft deleted, so every single row is checked, including soft deleted ones. If a soft deleted row has the same email as the new row, it will reject it.

The Heavily Flawed Naive Approach

The logical fix seems to just compare both email and deleted_at, making sure they aren't identical. However, this actually introduces a bigger issue.

$table->unique(['email', 'deleted_at']);

You see, in standard SQL, NULL is never equal to another NULL, even inside a unique index. Rows that aren't soft deleted have deleted_at set to NULL by default. This means when the database compares the two 'identical' rows, it will see that both rows have deleted_at set to NULL, and since NULL ≠ NULL, it will allow the new account's creation, even if they are duplicates. This not only fails at fixing the main issue, but now allows for the creation of duplicate accounts.

The Correct Implementation

Never rely on deleted_at being NULL for active rows. Instead, add a column called deleted_at_unix, set its default to 0, and create a composite unique constraint spanning both email and deleted_at_unix:

$table->unsignedInteger('deleted_at_unix')->default(0);
$table->unique(['email', 'deleted_at_unix']);

Rows that have deleted_at_unix set to 0 are active, while soft deleted rows should have the deletion timestamp stored.

Make sure deleted_at_unix is always set correctly by hooking it straight into the model's deleting event:

protected static function booted()
{
    static::deleting(function ($model) {
        $model->deleted_at_unix = now()->timestamp;
        $model->saveQuietly();
    });
}

Since 0 is a real comparable value, MySQL would evaluate any two rows that have deleted_at_unix set to 0 as duplicates, and it won't allow the creation of the new row. A soft-deleted row has deleted_at_unix set to the row's deletion timestamp instead of 0, so a new signup that shares the same email as the soft-deleted row succeeds. Multiple soft-deleted rows sharing the same email also don't cause any issues since they have unique timestamps.

Why Not Just Default deleted_at to 0?

Laravel's SoftDeletes trait identifies active rows as the ones with deleted_at set to NULL, so if anything else is stored, including 0, the row will be recognized as soft deleted.

This is why a separate deleted_at_unix column is used, to keep Laravel's SoftDeletes behaving as it should, while deleted_at_unix enforces a database level unique constraint to prevent duplicate active accounts from being created.

Summary

In other words, soft-delete is a filter that hides rows from Laravel's database queries. The row still exists and takes up storage in the database, and a unique constraint on a column such as email checks every single row, including 'soft-deleted' ones that have a timestamp on deleted_at.

It seems the obvious fix is to create a composite unique constraint across email and deleted_at, however, this introduces a bigger issue. Active rows have deleted_at set to NULL by default, and to MySQL, NULL is never equal to another NULL.

The correct implementation is to add a column called deleted_at_unix defaulted to 0, and setting a composite unique constraint spanning email and deleted_at_unix. When the row is deleted, the value of deleted_at_unix gets updated from 0 to the deletion timestamp.

Integrating this approach correctly ensures new accounts cannot get rejected for sharing the same email as a deleted account, and for duplicate accounts to never slip through.