Custom OTP-Based Password Reset with Supabase

Need a password reset flow that uses a short code in the user’s inbox instead of magic links? Many products prefer a 6-digit OTP and a one-time token so users stay in your app and you control the email copy and expiry. This guide walks you through building that flow with Supabase, from the database and Edge Functions to the React frontend, with security and clarity in mind.

What You'll Build

By the end you'll have:

• A 3-step flow: Request code → Verify OTP → Set new password
• A Postgres table storing OTP and reset token hashes (no plain codes or tokens)
• Three Edge Functions that handle each step and talk to Supabase Auth
• A React “Forgot password” page that never puts the reset token in the URL or storage
• Solid security: constant-time OTP check, no email enumeration, single-use tokens, and RLS

You’ll use a 6-digit OTP and a short-lived, DB-backed token. No magic links.

Prerequisites

Before you start, make sure you have:

• A Supabase project with database and Auth enabled
• Basic familiarity with TypeScript, React, and Supabase Edge Functions
• SMTP credentials (e.g. Gmail, SendGrid) if you want to send the OTP by email from your own Edge Function

How the Flow Works

When a user clicks “Forgot password?” on the login page, they land on /forgot-password. They enter their email and receive a 6-digit code. After they enter that code, your app gets a one-time reset token and shows the “Set new password” form. Submitting the new password sends only the token and password to the backend; the backend updates the user’s password and then redirects them to login. The table below summarizes who does what at each step.

Entry: Login page “Forgot password?” → /forgot-password.
Exit: After success, redirect to /login; the user signs in with the new password.

Architecture Overview

┌─────────────────────────────────────────────────────────────────────────┐
│  Frontend (ForgotPasswordPage)                                            │
│  Route: /forgot-password                                                  │
└─────────────────────────────────────────────────────────────────────────┘
         │                    │                      │
         │ 1. POST email       │ 2. POST email + OTP   │ 3. POST resetToken + newPassword
         ▼                    ▼                      ▼
┌─────────────────┐  ┌─────────────────────┐  ┌──────────────────────┐
│ request-        │  │ check-password-     │  │ confirm-password-    │
│ password-reset  │  │ reset-otp            │  │ reset                │
└────────┬────────┘  └──────────┬──────────┘  └──────────┬───────────┘
         │                      │                         │
         │ insert row            │ update row               │ lookup by token
         │ (otp_hash,            │ (reset_token_hash,       │ update auth.users
         │  expires_at)          │  token_expires_at)       │ delete row
         │ send email            │ return resetToken        │
         ▼                      ▼                         ▼
┌─────────────────────────────────────────────────────────────────────────┐
│  Postgres: public.password_reset_otps                                    │
│  Auth: auth.users (password updated via Admin API)                       │
└─────────────────────────────────────────────────────────────────────────┘

Why this design? The OTP is used only in step 2; step 3 is authorized purely by a one-time token, so the code is never sent or checked again. The token lives in your table as a hash with an expiry, giving you a single source of truth and easy cleanup. Because everything is in your app (no magic links), you control the email text, expiry times, and overall UX.

Phase 1: Setting Up the Database

What we're doing: We need one table to store OTP hashes and reset token hashes so the Edge Functions can validate codes and tokens without ever storing plain values. Only the service role should access this table.

Table: public.password_reset_otps

Indexes: Use (email, expires_at) for step 1 insert and step 2 lookup by email and OTP validity. Use (reset_token_hash) where reset_token_hash IS NOT NULL for step 3 lookup by token.

Security note: Enable RLS and allow only the service role to read and write this table (e.g. auth.role() = 'service_role'). The frontend never touches it.

Migration 1: Create table and first index

Save as supabase/migrations/20260305000000_password_reset_otps.sql:

-- Custom password reset OTPs (step 1: store OTP hash; step 2–3: reset token)
CREATE TABLE public.password_reset_otps (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT NOT NULL,
  otp_hash TEXT NOT NULL,
  expires_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_password_reset_otps_email_expires
  ON public.password_reset_otps (email, expires_at);

ALTER TABLE public.password_reset_otps ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Service role only"
  ON public.password_reset_otps
  FOR ALL
  USING (auth.role() = 'service_role')
  WITH CHECK (auth.role() = 'service_role');

Migration 2: Add reset token columns and index

Save as supabase/migrations/20260312000000_password_reset_otps_reset_token.sql:

ALTER TABLE public.password_reset_otps
  ADD COLUMN reset_token_hash TEXT,
  ADD COLUMN token_expires_at TIMESTAMPTZ;

CREATE INDEX idx_password_reset_otps_reset_token_hash
  ON public.password_reset_otps (reset_token_hash)
  WHERE reset_token_hash IS NOT NULL;

Optional: Look up user by email

If you prefer not to query auth.users directly from Edge Functions, add an RPC that the service role can call:

-- Optional: safe user lookup by email (called from Edge Function with service role)
CREATE OR REPLACE FUNCTION public.get_user_id_by_email(user_email TEXT)
RETURNS UUID
LANGUAGE sql
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT id FROM auth.users WHERE email = lower(trim(user_email)) LIMIT 1;
$$;

Your first Edge Function can then call this RPC to get user_id by email before inserting into password_reset_otps.

Phase 2: Building the Edge Functions

All three functions use POST and JSON bodies, return JSON, and send CORS headers. They rely on SUPABASE_URL, SUPABASE_SERVICE_ROLE_KEY, and PASSWORD_RESET_OTP_SECRET. The first function also needs SMTP env vars if you send the OTP email yourself.

Step 1: Request password reset

Purpose: Send the user a 6-digit OTP by email and store its hash in the database.

What it does: The client sends an email. You validate it, look up the user (e.g. via get_user_id_by_email or the Auth Admin API). If there’s no user, you still return the same success message so you don’t reveal whether the email exists. If there is a user, you generate a 6-digit OTP, hash it with your secret, insert a row with otp_hash and expires_at (e.g. 15 minutes), and send an email with the OTP. If anything fails after the user lookup (e.g. DB or SMTP), you still return the same generic success message.

When the client sends:

{ "email": "[email protected]" }

the function always responds with (for valid JSON input):

{ "success": true, "message": "If an account exists with this email, we've sent a code." }

You’ll need a shared SMTP helper (e.g. _shared/smtp.ts) and a way to resolve email to user_id.

Step 2: Verify OTP

Purpose: Check the 6-digit code and issue a one-time reset token.

What it does: The client sends the same email and the OTP they received. You load the latest valid row for that email (where expires_at is still in the future), compute the OTP hash with your secret, and compare it to otp_hash in constant time (e.g. crypto.timingSafeEqual) so timing doesn’t leak information. If the code is wrong or expired, you return a 400 with a generic error. If it’s valid, you generate a 32-byte random token, hash it, set token_expires_at (e.g. 10 minutes), update the row with reset_token_hash and token_expires_at, and return the plain token to the client once over HTTPS.

When the client sends:

{ "email": "[email protected]", "otp": "123456" }

on success the function returns:

{ "success": true, "resetToken": "<64-char hex string>" }

On error (invalid or expired code):

{ "success": false, "error": "Invalid or expired code" }

Pro tip: Use constant-time comparison for the OTP so attackers can’t guess the code by measuring response time.

Step 3: Confirm new password

Purpose: Set the user’s new password using the one-time token (no OTP or email in the body).

What it does: The client sends only the reset token from step 2 and the new password. You hash the token, find the row by reset_token_hash and token_expires_at > now(), and if you find it you call supabase.auth.admin.updateUserById(row.user_id, { password: newPassword }), then delete the row so the token can’t be reused. If the token is missing, invalid, or expired, you return a 400 with a friendly message asking the user to request a new code.

When the client sends:

{ "resetToken": "<from step 2>", "newPassword": "newSecurePassword" }

on success the function returns:

{ "success": true }

On error:

{ "success": false, "error": "Invalid or expired reset link. Please request a new code." }

Security note: The token is single-use; deleting the row after a successful password update ensures it can’t be reused.

Phase 3: Connecting the Frontend

Your “Forgot password” page (e.g. ForgotPasswordPage.tsx) lives at /forgot-password. Users get there from a “Forgot password?” link on the login page. The page drives the three steps: first the user enters their email and you call request-password-reset; on success you show the OTP input and store the email in state. When they submit the code you call check-password-reset-otp with email and OTP; on success you store the resetToken in memory (never in the URL or localStorage) and show the new-password form. When they submit the new password you call confirm-password-reset with only the token and new password; on success you clear the token, show a success toast, and redirect to /login.

Use your Supabase client (anon key is fine; the functions use the service role internally) to invoke each function as below:

// Step 1
const { data, error } = await supabase.functions.invoke("request-password-reset", {
  body: { email },
});

// Step 2
const { data, error } = await supabase.functions.invoke("check-password-reset-otp", {
  body: { email, otp },
});
// On success: data.resetToken

// Step 3
const { data, error } = await supabase.functions.invoke("confirm-password-reset", {
  body: { resetToken, newPassword },
});

Validation (e.g. with Zod): Validate email format for step 1; for the OTP use exactly 6 digits (strip spaces or dashes if you allow them in the UI); for the password require a minimum length (e.g. 6) and that the confirmation field matches.

UX details: Offer “Use a different email” to go back to step 1 and clear OTP/token state. Keep the reset token only in memory and discard it after step 3 or when the user leaves the flow. Use autoComplete="new-password" and, if needed, decoy fields to avoid confirm-password autofill issues.

Security and Best Practices

• OTP: Single use (the row is deleted after the password is updated). Store only a hash (e.g. SHA-256 of otp + secret). Always compare with a constant-time function in check-password-reset-otp.
• Reset token: Single use and time-limited (e.g. 10 minutes). Use a cryptographically random value and store only its hash. Never log or expose the plain token except in the one success response over HTTPS.
• Email enumeration: Step 1 always returns the same message whether the email exists or not; never leak “user not found” or “email not sent.”
• Secrets: Keep PASSWORD_RESET_OTP_SECRET in Supabase Edge Function secrets only; use a long random string (e.g. 32+ characters).
• HTTPS: Use HTTPS in production so the reset token is never sent in the clear.
• RLS: Only the service role should read or write password_reset_otps; the frontend must not access this table directly.

Environment and Configuration

Set these in your Supabase project so the Edge Functions can use them.

Edge Function secrets (required):

• SUPABASE_URL: set by Supabase for the project
• SUPABASE_SERVICE_ROLE_KEY: set by Supabase; used for DB and Auth Admin API
• PASSWORD_RESET_OTP_SECRET: required for all three functions; used to hash the OTP and reset token. Generate a long random string and set it under Dashboard → Project Settings → Edge Functions → Secrets

SMTP (for request-password-reset only):

• SMTP_HOST (e.g. smtp.gmail.com)
• SMTP_PORT (e.g. 465)
• SMTP_USER
• SMTP_PASSWORD (required)

If SMTP is missing or the send fails, the function should still return the same generic success message so you don’t leak whether the email exists.

Deployment and Maintenance

1. Run migrations so password_reset_otps exists with both migrations applied.
2. Set Edge Function secrets: PASSWORD_RESET_OTP_SECRET and your SMTP variables.
3. Deploy the Edge Functions: request-password-reset, check-password-reset-otp, and confirm-password-reset (and any shared code like _shared/smtp.ts).

Rows are removed when step 3 completes. Expired rows (past expires_at or token_expires_at) are not deleted automatically. You can add a scheduled job or cron to delete rows where expires_at < now() OR token_expires_at < now().

You now have a production-ready, OTP-based password reset flow with Supabase and full control over UX, security, and email content. For more on Supabase Auth, see the Auth documentation.