@Shadow's answer above worked. Posting the answer for others visit later.

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(50) NOT NULL,
  deleted_at VARCHAR(50) NULL,
  
  CONSTRAINT UNIQUE INDEX `users_email_unique` ((CASE WHEN deleted_at IS NULL THEN email END))
);

This allowed me to reuse the email of soft-deleted record(with the same email and not null value in deleted_at), while explicitly blocking duplicated email usage with null value at deleted_at