001_users.sql

CREATE TABLE IF NOT EXISTS users (
		id INT PRIMARY KEY AUTO_INCREMENT,
		email VARCHAR(255) NOT NULL UNIQUE,
		username VARCHAR(255) NOT NULL UNIQUE,
		first_name VARCHAR(255) NOT NULL,
		last_name VARCHAR(255) NOT NULL,
		password_hash VARCHAR(255) NOT NULL,
		email_verified BOOLEAN NOT NULL DEFAULT FALSE,
		email_verification_token VARCHAR(255) UNIQUE DEFAULT NULL,
		email_verification_expires_at TIMESTAMP DEFAULT NULL,
		password_reset_token VARCHAR(255) UNIQUE DEFAULT NULL,
		password_reset_expires_at TIMESTAMP DEFAULT NULL,
		pending_new_email VARCHAR(255) UNIQUE DEFAULT NULL,
		pending_new_email_token VARCHAR(255) DEFAULT NULL,
		pending_new_email_expires_at TIMESTAMP DEFAULT NULL,
		is_admin BOOLEAN NOT NULL DEFAULT FALSE,
		created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
		updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

		INDEX idx_users_email (email),
		INDEX idx_users_username (username)
);