Email Verification System/Phase 1 of 515-30 minutes

Database Schema Setup

Phase 1: Database Schema

Overview

Create the database schema for storing verification tokens. This phase sets up the foundation for secure token storage and retrieval.

Requirements

Functional:

•Store verification tokens linked to users
•Track token creation and expiry times
•Support efficient token lookups

Non-functional:

•Indexed for fast token queries
•Cascade delete when user deleted
•Unique constraint on tokens

Schema Design

Option A: Separate Tokens Table (Recommended)

Best for: Multiple token types (email, password reset, etc.)

CREATE TABLE verification_tokens (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  token VARCHAR(64) UNIQUE NOT NULL,
  type VARCHAR(32) NOT NULL DEFAULT 'email_verification',
  expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  
  CONSTRAINT valid_token_length CHECK (length(token) >= 32)
);

-- Index for token lookup (most common query)
CREATE INDEX idx_verification_tokens_token ON verification_tokens(token);

-- Index for user cleanup
CREATE INDEX idx_verification_tokens_user_id ON verification_tokens(user_id);

-- Index for expired token cleanup
CREATE INDEX idx_verification_tokens_expires_at ON verification_tokens(expires_at);

Option B: Add to Users Table

Best for: Simple apps with only email verification

ALTER TABLE users ADD COLUMN email_verification_token VARCHAR(64);
ALTER TABLE users ADD COLUMN email_verification_expires_at TIMESTAMP WITH TIME ZONE;
ALTER TABLE users ADD COLUMN email_verified_at TIMESTAMP WITH TIME ZONE;

CREATE INDEX idx_users_verification_token ON users(email_verification_token) 
  WHERE email_verification_token IS NOT NULL;

Prisma Schema (if using Prisma)

model VerificationToken {
  id        String   @id @default(uuid())
  userId    String   @map("user_id")
  token     String   @unique @db.VarChar(64)
  type      String   @default("email_verification") @db.VarChar(32)
  expiresAt DateTime @map("expires_at")
  createdAt DateTime @default(now()) @map("created_at")

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([token])
  @@index([userId])
  @@index([expiresAt])
  @@map("verification_tokens")
}

model User {
  id                 String              @id @default(uuid())
  email              String              @unique
  emailVerifiedAt    DateTime?           @map("email_verified_at")
  // ... other fields
  verificationTokens VerificationToken[]
  
  @@map("users")
}

Migration Commands

Prisma

npx prisma migrate dev --name add_verification_tokens

Raw SQL (PostgreSQL)

psql -d your_database -f migrations/001_verification_tokens.sql

Drizzle

npx drizzle-kit generate:pg
npx drizzle-kit push:pg

Cleanup Job (Optional)

Periodically remove expired tokens to keep table small:

-- Run via cron or scheduled job
DELETE FROM verification_tokens 
WHERE expires_at < NOW() - INTERVAL '7 days';

Success Criteria

• Migration created and applied
• Indexes verified with EXPLAIN ANALYZE
• Foreign key constraint working (test cascade)
• Unique constraint on token working

Related Files

•Create: prisma/schema.prisma (if Prisma)
•Create: migrations/001_verification_tokens.sql
•Modify: User model to add relation

Token Generation Service