Email verification confirms a user controls the email address they registered with, blocking fake accounts, disposable addresses, and typo’d emails that lead to lost users. Core design: generate a cryptographically random token, store its hash, send the link, handle expiry and resend requests, and securely complete verification in a single database update. Getting this wrong leads to user lockout, token enumeration, or dangling unverified accounts cluttering the database.
Core Data Model
CREATE TABLE User (
user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT NOT NULL UNIQUE,
email_verified BOOLEAN NOT NULL DEFAULT FALSE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE EmailVerification (
verification_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES User(user_id) ON DELETE CASCADE,
email TEXT NOT NULL, -- the email being verified
token_hash CHAR(64) NOT NULL, -- SHA-256 of the raw token (NEVER store raw)
expires_at TIMESTAMPTZ NOT NULL,
verified_at TIMESTAMPTZ, -- NULL until verified
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_verification_user ON EmailVerification (user_id, created_at DESC);
CREATE INDEX idx_verification_token ON EmailVerification (token_hash) WHERE verified_at IS NULL;
-- Rate limiting: track resend requests per user per hour
CREATE TABLE VerificationResend (
user_id UUID NOT NULL,
sent_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
ip_address INET
);
CREATE INDEX idx_resend_user_time ON VerificationResend (user_id, sent_at DESC);
Generating and Sending the Verification Token
import secrets, hashlib
from datetime import datetime, timezone, timedelta
import psycopg2
TOKEN_BYTES = 32 # 256 bits of entropy
EXPIRY_HOURS = 24
MAX_RESENDS_PER_HOUR = 3
def send_verification_email(conn, user_id: str, email: str, ip_address: str) -> str:
"""
Generate a verification token, store its hash, and send the email.
Rate-limited to MAX_RESENDS_PER_HOUR per user.
Returns the verification_id (for testing/logging).
"""
# Rate limit check
with conn.cursor() as cur:
cur.execute("""
SELECT COUNT(*) FROM VerificationResend
WHERE user_id = %s AND sent_at >= NOW() - interval '1 hour'
""", (user_id,))
count = cur.fetchone()[0]
if count >= MAX_RESENDS_PER_HOUR:
raise ValueError("Too many verification emails sent. Try again later.")
# Generate raw token (never stored)
raw_token = secrets.token_urlsafe(TOKEN_BYTES) # ~43 URL-safe chars
token_hash = hashlib.sha256(raw_token.encode()).hexdigest()
expires_at = datetime.now(timezone.utc) + timedelta(hours=EXPIRY_HOURS)
with conn.cursor() as cur:
# Invalidate any existing pending tokens for this user+email
cur.execute("""
UPDATE EmailVerification
SET expires_at = NOW()
WHERE user_id = %s AND email = %s AND verified_at IS NULL
""", (user_id, email))
# Insert new token
cur.execute("""
INSERT INTO EmailVerification (user_id, email, token_hash, expires_at)
VALUES (%s, %s, %s, %s)
RETURNING verification_id
""", (user_id, email, token_hash, expires_at))
verification_id = cur.fetchone()[0]
# Log resend for rate limiting
cur.execute(
"INSERT INTO VerificationResend (user_id, ip_address) VALUES (%s, %s)",
(user_id, ip_address)
)
conn.commit()
# Build verification URL — token in query param (not path) for cleaner logs
verify_url = f"https://app.example.com/verify-email?token={raw_token}"
send_email(
to=email,
subject="Verify your email address",
body=f"Click this link to verify your email (valid for {EXPIRY_HOURS} hours):n{verify_url}nnIf you didn't request this, ignore this email."
)
return str(verification_id)
Verifying the Token
def verify_email(conn, raw_token: str) -> dict:
"""
Verify an email address using the token from the link.
Returns {"status": "ok", "email": ...} or raises ValueError.
"""
# Hash the incoming token to look it up
token_hash = hashlib.sha256(raw_token.encode()).hexdigest()
with conn.cursor() as cur:
cur.execute("""
SELECT verification_id, user_id, email, expires_at, verified_at
FROM EmailVerification
WHERE token_hash = %s
""", (token_hash,))
row = cur.fetchone()
if not row:
raise ValueError("Invalid verification link")
verification_id, user_id, email, expires_at, verified_at = row
if verified_at is not None:
return {"status": "already_verified", "email": email}
if datetime.now(timezone.utc) > expires_at:
raise ValueError("Verification link has expired. Request a new one.")
# Mark as verified — both EmailVerification and User in same transaction
with conn.cursor() as cur:
cur.execute(
"UPDATE EmailVerification SET verified_at = NOW() WHERE verification_id = %s",
(verification_id,)
)
cur.execute(
"UPDATE User SET email_verified = TRUE WHERE user_id = %s",
(user_id,)
)
conn.commit()
return {"status": "ok", "email": email, "user_id": str(user_id)}
Edge Cases and Security Hardening
# Token enumeration: the token hash lookup returns the same "Invalid" error
# for both nonexistent and expired tokens — don't reveal which.
# Timing attack on hash comparison:
# Use hmac.compare_digest instead of == for token comparison if doing
# application-level comparison (not needed here since we hash and use DB lookup).
import hmac
def safe_token_verify(stored_hash: str, input_token: str) -> bool:
"""Use constant-time comparison to prevent timing oracle attacks."""
input_hash = hashlib.sha256(input_token.encode()).hexdigest()
return hmac.compare_digest(stored_hash, input_hash)
# Cleanup job: remove expired unverified tokens older than 30 days
def cleanup_expired_verifications(conn):
with conn.cursor() as cur:
cur.execute("""
DELETE FROM EmailVerification
WHERE verified_at IS NULL
AND expires_at < NOW() - interval '30 days'
""")
deleted = cur.rowcount
conn.commit()
return deleted
# Prevent unverified users from accessing protected resources
def require_verified_email(user_id: str, conn):
with conn.cursor() as cur:
cur.execute("SELECT email_verified FROM User WHERE user_id = %s", (user_id,))
row = cur.fetchone()
if not row or not row[0]:
raise PermissionError("Email verification required")
Key Interview Points
- Store hash, send raw: If the database is breached, token hashes cannot be reversed to valid tokens. An attacker with the hash cannot verify email addresses or take over accounts. SHA-256 is sufficient here (unlike passwords) because tokens have 256 bits of entropy — brute-force is infeasible even without bcrypt’s slowness. Passwords need slow hashes because they have low entropy.
- Token invalidation on resend: When a user requests a new verification link, expire all previous pending tokens for that email. This prevents confusion from multiple valid links floating around and reduces the attack window for token theft.
- URL token placement: Tokens in query params (?token=…) appear in server access logs and browser history. Tokens in path segments (/verify/TOKEN) also appear in logs. Mitigation: (1) HTTPS (tokens are encrypted in transit); (2) short expiry (24h); (3) single-use (invalidated on first use). Some systems POST the token from a landing page to avoid Referer header leakage.
- Email change verification: When a user changes their email address, require verification of the new address before updating the primary email. Sequence: (1) store new_email and new_email_token on the User row; (2) send verification to new_email; (3) only update email and clear new_email_token on successful verification. This prevents account hijacking via email change.
- Rate limiting layers: Per-user rate limit (MAX_RESENDS_PER_HOUR) prevents spamming a victim’s inbox. IP-level rate limit catches coordinated attacks across multiple accounts. Add CAPTCHA after 2 failed verifications. Block known disposable email domains (mailinator.com, guerrillamail.com) at registration time using a maintained blocklist.
{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”Why store the token hash instead of the raw token in the database?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”An email verification token grants access without authentication — clicking the link verifies the email. If your database is breached and tokens are stored in plaintext, an attacker can verify any email address by constructing the URL themselves, effectively taking over unverified accounts. Storing SHA-256(token) prevents this: the hash cannot be reversed to the raw token. The raw token exists only in the email link in the user’s inbox. Even if an attacker reads your entire EmailVerification table, they cannot construct valid links. This is the same security principle as password hashing applied to tokens. Use SHA-256 (not bcrypt) because tokens are already high entropy (256 bits from secrets.token_urlsafe) — brute-force is infeasible, so the slowness of bcrypt is unnecessary overhead.”}},{“@type”:”Question”,”name”:”How does the verification link URL design affect security?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Token placement options: (1) Query parameter: /verify-email?token=abc123 — token appears in server access logs, browser history, and Referer headers. (2) Path segment: /verify-email/abc123 — same logging issues plus route-matching complexity. (3) Fragment: /verify-email#abc123 — fragment is not sent to the server in HTTP requests, but is visible in browser history and cannot be read server-side. The practical defense: use HTTPS (token is encrypted in transit and not in server logs), short expiry (24 hours limits the exposure window), and single-use (token is invalidated on first successful use). Log token_hash, not the raw token. If the token is in the URL path, exclude it from access logs using log format patterns. Facebook and Google both use query parameter tokens — this is the industry standard.”}},{“@type”:”Question”,”name”:”How do you handle the email change verification flow to prevent account takeover?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Scenario: attacker knows a target’s account email address (alice@example.com). If the attacker changes their own account’s email to alice@example.com without verification, they take over Alice’s account. Safe email change flow: (1) user requests change to new_email; (2) store new_email and a new verification token on the user row (pending change — do NOT update the primary email yet); (3) send verification link to new_email; (4) only on successful verification: UPDATE User SET email = new_email WHERE user_id = X AND new_email_token_hash = $hash; (5) send a notification to the old email address: "Your email was changed from alice@example.com to new_email@example.com. If this wasn’t you, click here to revert." The revert link with a separate revert_token gives the legitimate user 48 hours to cancel an unauthorized change.”}},{“@type”:”Question”,”name”:”How does rate limiting on resend prevent inbox flooding of a victim’s email?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Without rate limiting, an attacker who knows a victim’s email (bob@victim.com) can call POST /resend-verification 1,000 times, flooding Bob’s inbox with verification emails. This is an email bombing attack. Mitigations: (1) Per-user rate limit: MAX_RESENDS_PER_HOUR = 3 — reject additional requests with HTTP 429. (2) IP rate limit as a secondary guard: even across multiple accounts, the same IP is limited to N resend requests per hour. (3) CAPTCHA after 2 resends — requires human interaction. (4) Exponential backoff on the UI: disable the Resend button for 60s after click, then 120s, then 300s. Log all resend attempts with IP and user_id for abuse detection. These measures together make inbox flooding impractical without blocking legitimate users who mistype their email.”}},{“@type”:”Question”,”name”:”How do you clean up expired unverified accounts to avoid database bloat?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Users who register but never verify their email produce orphaned User and EmailVerification rows. After 30 days, these accounts are definitively abandoned. Cleanup job: DELETE FROM User WHERE email_verified = FALSE AND created_at < NOW() – interval ’30 days’. The CASCADE on EmailVerification (ON DELETE CASCADE) removes verification rows automatically. Before deleting: check that no payment or order is associated with the user (rare for unverified accounts, but safe guard). Run the cleanup job weekly with a LIMIT clause to avoid long-running delete transactions: DELETE FROM User WHERE user_id IN (SELECT user_id FROM User WHERE email_verified=FALSE AND created_at < NOW() – interval ’30 days’ LIMIT 1000). Loop until no rows deleted. This bounded-batch approach prevents table bloat without blocking writes.”}}]}
Email verification and account security system design is discussed in Google system design interview questions.
Email verification and account confirmation design is covered in Coinbase system design interview preparation.
Email verification and user onboarding security design is discussed in LinkedIn system design interview guide.