Suppose you have a page where users can request an email to reset their password:

Here’s how you can implement this password reset functionality.

1. Create a database table to store password reset requests

• Table name
  • password_reset_request
• Columns
  • id: number
  • user_id: number (FK: references user.id)
  • token: varchar(255)
  • last_email_sent_at: datetime
  • created_at: datetime
• Indices
  • user_id: unique
    • Ensure there’s only one password_reset_request record at a time for a single user_id
  • token: unique
    • Ensure only one token is used across the table.

2. Set up a scheduled job to clean up password reset requests

Decide on how long a password reset request should be valid for. Let’s assume you want it to be valid for 24 hours from the moment it’s created.

Create a scheduled job (e.g., Cron job, Laravel job, Rails job, etc) to go through all password_reset_request records every hour and delete expired requests (a request that was created over 24 hours ago).

3. Implement password reset flow

• User enters email address of the account for which they want password reset instructions.
• If valid:
  • Check the user didn’t request a password reset email in the last 10 minutes. If they did, don’t send the request to avoid abuse. Otherwise proceed.
  • Create a password_reset_request record with a unique token.
    • Check if there’s already a valid password_reset_request for that email. If so, reuse that valid record.
  • Send an email to the user containing a password reset link that includes that token. For example: cpad.app/auth/reset-password/<token>.
• If invalid:
  • Return a generic message like If the email is valid, you will receive password reset instructions.
• User clicks on the password reset link in the email and resets their password. The code responsible for processing the reset password request should verify that the user provided a valid token in the request.
• On successfully resetting their password, delete the password_reset_request record.