Email Verification Database Tables

Here are the complete database tables you’ll need for email verification system:

1. Main Users Table

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('user','admin','moderator') DEFAULT 'user',
  `email_verified_at` datetime DEFAULT NULL,
  `verification_token` varchar(64) DEFAULT NULL,
  `token_expires_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `verification_token` (`verification_token`),
  KEY `email_verified_at` (`email_verified_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. Password Reset Table (Optional but Recommended)

CREATE TABLE `password_resets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `token` varchar(64) NOT NULL,
  `expires_at` datetime NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `used_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `token` (`token`),
  CONSTRAINT `password_resets_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3. Email Change Requests Table (Optional)

CREATE TABLE `email_change_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `new_email` varchar(100) NOT NULL,
  `token` varchar(64) NOT NULL,
  `expires_at` datetime NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `token` (`token`),
  CONSTRAINT `email_change_requests_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Key Fields Explanation:

In Users Table:

• email_verified_at: Timestamp when email was verified (NULL means not verified)
• verification_token: Random token for email verification
• token_expires_at: When the token expires (typically 24 hours)

In Password Resets Table:

• Stores temporary password reset tokens separately from verification tokens
• Tracks when tokens are used

In Email Change Requests:

• Stores requests to change email addresses
• Requires verification of new email

Sample Queries:

1. Create new user with verification token:

INSERT INTO users (username, email, password, verification_token, token_expires_at)
VALUES ('john_doe', 'john@example.com', 'hashed_password', 'random_token_123', '2023-12-31 23:59:59');

2. Verify email:

UPDATE users 
SET email_verified_at = NOW(), 
    verification_token = NULL,
    token_expires_at = NULL
WHERE verification_token = 'provided_token'
AND token_expires_at > NOW();

3. Check if email is verified:

SELECT id FROM users WHERE email = 'user@example.com' AND email_verified_at IS NOT NULL;

This structure provides:

• Complete email verification system
• Secure token handling
• Expiration tracking
• Audit capabilities
• Support for future features like password reset