refactor(db): migrate off `user_email.go` to `users.go` (#7452)

10 files changed, 403 insertions, 396 deletions

diff --git a/internal/db/backup_test.go b/internal/db/backup_test.go
index fc00ada8..4c5e4752 100644
--- a/internal/db/backup_test.go
+++ b/internal/db/backup_test.go
@@ -31,8 +31,9 @@ func TestDumpAndImport(t *testing.T) {
 	}
 	t.Parallel()
 
-	if len(Tables) != 6 {
-		t.Fatalf("New table has added (want 6 got %d), please add new tests for the table and update this check", len(Tables))
+	const wantTables = 7
+	if len(Tables) != wantTables {
+		t.Fatalf("New table has added (want %d got %d), please add new tests for the table and update this check", wantTables, len(Tables))
 	}
 
 	db := dbtest.NewDB(t, "dumpAndImport", Tables...)
@@ -131,6 +132,19 @@ func setupDBToDump(t *testing.T, db *gorm.DB) {
 			CreatedUnix:  1588568886,
 		},
 
+		&EmailAddress{
+			ID:          1,
+			UserID:      1,
+			Email:       "alice@example.com",
+			IsActivated: false,
+		},
+		&EmailAddress{
+			ID:          2,
+			UserID:      2,
+			Email:       "bob@example.com",
+			IsActivated: true,
+		},
+
 		&Follow{
 			ID:       1,
 			UserID:   1,
diff --git a/internal/db/db.go b/internal/db/db.go
index 20573334..d50b934f 100644
--- a/internal/db/db.go
+++ b/internal/db/db.go
@@ -42,6 +42,7 @@ func newLogWriter() (logger.Writer, error) {
 // NOTE: Lines are sorted in alphabetical order, each letter in its own line.
 var Tables = []any{
 	new(Access), new(AccessToken), new(Action),
+	new(EmailAddress),
 	new(Follow),
 	new(LFSObject), new(LoginSource),
 }
@@ -121,7 +122,6 @@ func Init(w logger.Writer) (*gorm.DB, error) {
 	// Initialize stores, sorted in alphabetical order.
 	AccessTokens = &accessTokens{DB: db}
 	Actions = NewActionsStore(db)
-	EmailAddresses = NewEmailAddressesStore(db)
 	LoginSources = &loginSources{DB: db, files: sourceFiles}
 	LFS = &lfs{DB: db}
 	Orgs = NewOrgsStore(db)
diff --git a/internal/db/email_addresses.go b/internal/db/email_addresses.go
deleted file mode 100644
index d27b926d..00000000
--- a/internal/db/email_addresses.go
+++ /dev/null
@@ -1,80 +0,0 @@
-// Copyright 2022 The Gogs Authors. All rights reserved.
-// Use of this source code is governed by a MIT-style
-// license that can be found in the LICENSE file.
-
-package db
-
-import (
-	"context"
-	"fmt"
-
-	"github.com/pkg/errors"
-	"gorm.io/gorm"
-
-	"gogs.io/gogs/internal/errutil"
-)
-
-// EmailAddressesStore is the persistent interface for email addresses.
-type EmailAddressesStore interface {
-	// GetByEmail returns the email address with given email. If `needsActivated` is
-	// true, only activated email will be returned, otherwise, it may return
-	// inactivated email addresses. It returns ErrEmailNotExist when no qualified
-	// email is not found.
-	GetByEmail(ctx context.Context, email string, needsActivated bool) (*EmailAddress, error)
-}
-
-var EmailAddresses EmailAddressesStore
-
-var _ EmailAddressesStore = (*emailAddresses)(nil)
-
-type emailAddresses struct {
-	*gorm.DB
-}
-
-// NewEmailAddressesStore returns a persistent interface for email addresses
-// with given database connection.
-func NewEmailAddressesStore(db *gorm.DB) EmailAddressesStore {
-	return &emailAddresses{DB: db}
-}
-
-var _ errutil.NotFound = (*ErrEmailNotExist)(nil)
-
-type ErrEmailNotExist struct {
-	args errutil.Args
-}
-
-// IsErrEmailAddressNotExist returns true if the underlying error has the type
-// ErrEmailNotExist.
-func IsErrEmailAddressNotExist(err error) bool {
-	_, ok := errors.Cause(err).(ErrEmailNotExist)
-	return ok
-}
-
-func (err ErrEmailNotExist) Error() string {
-	return fmt.Sprintf("email address does not exist: %v", err.args)
-}
-
-func (ErrEmailNotExist) NotFound() bool {
-	return true
-}
-
-func (db *emailAddresses) GetByEmail(ctx context.Context, email string, needsActivated bool) (*EmailAddress, error) {
-	tx := db.WithContext(ctx).Where("email = ?", email)
-	if needsActivated {
-		tx = tx.Where("is_activated = ?", true)
-	}
-
-	emailAddress := new(EmailAddress)
-	err := tx.First(emailAddress).Error
-	if err != nil {
-		if err == gorm.ErrRecordNotFound {
-			return nil, ErrEmailNotExist{
-				args: errutil.Args{
-					"email": email,
-				},
-			}
-		}
-		return nil, err
-	}
-	return emailAddress, nil
-}
diff --git a/internal/db/email_addresses_test.go b/internal/db/email_addresses_test.go
deleted file mode 100644
index 523f5fc5..00000000
--- a/internal/db/email_addresses_test.go
+++ /dev/null
@@ -1,77 +0,0 @@
-// Copyright 2022 The Gogs Authors. All rights reserved.
-// Use of this source code is governed by a MIT-style
-// license that can be found in the LICENSE file.
-
-package db
-
-import (
-	"context"
-	"testing"
-
-	"github.com/stretchr/testify/assert"
-	"github.com/stretchr/testify/require"
-
-	"gogs.io/gogs/internal/dbtest"
-	"gogs.io/gogs/internal/errutil"
-)
-
-func TestEmailAddresses(t *testing.T) {
-	if testing.Short() {
-		t.Skip()
-	}
-	t.Parallel()
-
-	tables := []any{new(EmailAddress)}
-	db := &emailAddresses{
-		DB: dbtest.NewDB(t, "emailAddresses", tables...),
-	}
-
-	for _, tc := range []struct {
-		name string
-		test func(t *testing.T, db *emailAddresses)
-	}{
-		{"GetByEmail", emailAddressesGetByEmail},
-	} {
-		t.Run(tc.name, func(t *testing.T) {
-			t.Cleanup(func() {
-				err := clearTables(t, db.DB, tables...)
-				require.NoError(t, err)
-			})
-			tc.test(t, db)
-		})
-		if t.Failed() {
-			break
-		}
-	}
-}
-
-func emailAddressesGetByEmail(t *testing.T, db *emailAddresses) {
-	ctx := context.Background()
-
-	const testEmail = "alice@example.com"
-	_, err := db.GetByEmail(ctx, testEmail, false)
-	wantErr := ErrEmailNotExist{
-		args: errutil.Args{
-			"email": testEmail,
-		},
-	}
-	assert.Equal(t, wantErr, err)
-
-	// TODO: Use EmailAddresses.Create to replace SQL hack when the method is available.
-	err = db.Exec(`INSERT INTO email_address (uid, email, is_activated) VALUES (1, ?, FALSE)`, testEmail).Error
-	require.NoError(t, err)
-	got, err := db.GetByEmail(ctx, testEmail, false)
-	require.NoError(t, err)
-	assert.Equal(t, testEmail, got.Email)
-
-	// Should not return if we only want activated emails
-	_, err = db.GetByEmail(ctx, testEmail, true)
-	assert.Equal(t, wantErr, err)
-
-	// TODO: Use EmailAddresses.MarkActivated to replace SQL hack when the method is available.
-	err = db.Exec(`UPDATE email_address SET is_activated = TRUE WHERE email = ?`, testEmail).Error
-	require.NoError(t, err)
-	got, err = db.GetByEmail(ctx, testEmail, true)
-	require.NoError(t, err)
-	assert.Equal(t, testEmail, got.Email)
-}
diff --git a/internal/db/errors/user_mail.go b/internal/db/errors/user_mail.go
deleted file mode 100644
index fcdeb78c..00000000
--- a/internal/db/errors/user_mail.go
+++ /dev/null
@@ -1,33 +0,0 @@
-// Copyright 2017 The Gogs Authors. All rights reserved.
-// Use of this source code is governed by a MIT-style
-// license that can be found in the LICENSE file.
-
-package errors
-
-import "fmt"
-
-type EmailNotFound struct {
-	Email string
-}
-
-func IsEmailNotFound(err error) bool {
-	_, ok := err.(EmailNotFound)
-	return ok
-}
-
-func (err EmailNotFound) Error() string {
-	return fmt.Sprintf("email is not found [email: %s]", err.Email)
-}
-
-type EmailNotVerified struct {
-	Email string
-}
-
-func IsEmailNotVerified(err error) bool {
-	_, ok := err.(EmailNotVerified)
-	return ok
-}
-
-func (err EmailNotVerified) Error() string {
-	return fmt.Sprintf("email has not been verified [email: %s]", err.Email)
-}
diff --git a/internal/db/models.go b/internal/db/models.go
index 76a11fb4..715df242 100644
--- a/internal/db/models.go
+++ b/internal/db/models.go
@@ -58,7 +58,7 @@ func init() {
 		new(Mirror), new(Release), new(Webhook), new(HookTask),
 		new(ProtectBranch), new(ProtectBranchWhitelist),
 		new(Team), new(OrgUser), new(TeamUser), new(TeamRepo),
-		new(Notice), new(EmailAddress))
+		new(Notice))
 
 	gonicNames := []string{"SSL"}
 	for _, name := range gonicNames {
diff --git a/internal/db/testdata/backup/EmailAddress.golden.json b/internal/db/testdata/backup/EmailAddress.golden.json
new file mode 100644
index 00000000..55141538
--- /dev/null
+++ b/internal/db/testdata/backup/EmailAddress.golden.json
@@ -0,0 +1,2 @@
+{"ID":1,"UserID":1,"Email":"alice@example.com","IsActivated":false}
+{"ID":2,"UserID":2,"Email":"bob@example.com","IsActivated":true}
diff --git a/internal/db/user_mail.go b/internal/db/user_mail.go
deleted file mode 100644
index 01ab4c5b..00000000
--- a/internal/db/user_mail.go
+++ /dev/null
@@ -1,199 +0,0 @@
-// Copyright 2016 The Gogs Authors. All rights reserved.
-// Use of this source code is governed by a MIT-style
-// license that can be found in the LICENSE file.
-
-package db
-
-import (
-	"context"
-	"fmt"
-	"strings"
-
-	"gogs.io/gogs/internal/db/errors"
-	"gogs.io/gogs/internal/errutil"
-)
-
-// EmailAddresses is the list of all email addresses of a user. Can contain the
-// primary email address, but is not obligatory.
-type EmailAddress struct {
-	ID          int64  `gorm:"primaryKey"`
-	UserID      int64  `xorm:"uid INDEX NOT NULL" gorm:"column:uid;index;not null"`
-	Email       string `xorm:"UNIQUE NOT NULL" gorm:"unique;not null"`
-	IsActivated bool   `gorm:"not null;default:FALSE"`
-	IsPrimary   bool   `xorm:"-" gorm:"-" json:"-"`
-}
-
-// GetEmailAddresses returns all email addresses belongs to given user.
-func GetEmailAddresses(uid int64) ([]*EmailAddress, error) {
-	emails := make([]*EmailAddress, 0, 5)
-	if err := x.Where("uid=?", uid).Find(&emails); err != nil {
-		return nil, err
-	}
-
-	u, err := Users.GetByID(context.TODO(), uid)
-	if err != nil {
-		return nil, err
-	}
-
-	isPrimaryFound := false
-	for _, email := range emails {
-		if email.Email == u.Email {
-			isPrimaryFound = true
-			email.IsPrimary = true
-		} else {
-			email.IsPrimary = false
-		}
-	}
-
-	// We always want the primary email address displayed, even if it's not in
-	// the emailaddress table (yet).
-	if !isPrimaryFound {
-		emails = append(emails, &EmailAddress{
-			Email:       u.Email,
-			IsActivated: true,
-			IsPrimary:   true,
-		})
-	}
-	return emails, nil
-}
-
-func isEmailUsed(e Engine, email string) (bool, error) {
-	if email == "" {
-		return true, nil
-	}
-
-	has, err := e.Get(&EmailAddress{Email: email})
-	if err != nil {
-		return false, err
-	} else if has {
-		return true, nil
-	}
-
-	// We need to check primary email of users as well.
-	return e.Where("type=?", UserTypeIndividual).And("email=?", email).Get(new(User))
-}
-
-// IsEmailUsed returns true if the email has been used.
-func IsEmailUsed(email string) (bool, error) {
-	return isEmailUsed(x, email)
-}
-
-func addEmailAddress(e Engine, email *EmailAddress) error {
-	email.Email = strings.ToLower(strings.TrimSpace(email.Email))
-	used, err := isEmailUsed(e, email.Email)
-	if err != nil {
-		return err
-	} else if used {
-		return ErrEmailAlreadyUsed{args: errutil.Args{"email": email.Email}}
-	}
-
-	_, err = e.Insert(email)
-	return err
-}
-
-func AddEmailAddress(email *EmailAddress) error {
-	return addEmailAddress(x, email)
-}
-
-func AddEmailAddresses(emails []*EmailAddress) error {
-	if len(emails) == 0 {
-		return nil
-	}
-
-	// Check if any of them has been used
-	for i := range emails {
-		emails[i].Email = strings.ToLower(strings.TrimSpace(emails[i].Email))
-		used, err := IsEmailUsed(emails[i].Email)
-		if err != nil {
-			return err
-		} else if used {
-			return ErrEmailAlreadyUsed{args: errutil.Args{"email": emails[i].Email}}
-		}
-	}
-
-	if _, err := x.Insert(emails); err != nil {
-		return fmt.Errorf("Insert: %v", err)
-	}
-
-	return nil
-}
-
-func (email *EmailAddress) Activate() error {
-	email.IsActivated = true
-	if _, err := x.ID(email.ID).AllCols().Update(email); err != nil {
-		return err
-	}
-	return Users.Update(context.TODO(), email.UserID, UpdateUserOptions{GenerateNewRands: true})
-}
-
-func DeleteEmailAddress(email *EmailAddress) (err error) {
-	if email.ID > 0 {
-		_, err = x.Id(email.ID).Delete(new(EmailAddress))
-	} else {
-		_, err = x.Where("email=?", email.Email).Delete(new(EmailAddress))
-	}
-	return err
-}
-
-func DeleteEmailAddresses(emails []*EmailAddress) (err error) {
-	for i := range emails {
-		if err = DeleteEmailAddress(emails[i]); err != nil {
-			return err
-		}
-	}
-
-	return nil
-}
-
-func MakeEmailPrimary(userID int64, email *EmailAddress) error {
-	has, err := x.Get(email)
-	if err != nil {
-		return err
-	} else if !has {
-		return errors.EmailNotFound{Email: email.Email}
-	}
-
-	if email.UserID != userID {
-		return errors.New("not the owner of the email")
-	}
-
-	if !email.IsActivated {
-		return errors.EmailNotVerified{Email: email.Email}
-	}
-
-	user := &User{ID: email.UserID}
-	has, err = x.Get(user)
-	if err != nil {
-		return err
-	} else if !has {
-		return ErrUserNotExist{args: map[string]any{"userID": email.UserID}}
-	}
-
-	// Make sure the former primary email doesn't disappear.
-	formerPrimaryEmail := &EmailAddress{Email: user.Email}
-	has, err = x.Get(formerPrimaryEmail)
-	if err != nil {
-		return err
-	}
-
-	sess := x.NewSession()
-	defer sess.Close()
-	if err = sess.Begin(); err != nil {
-		return err
-	}
-
-	if !has {
-		formerPrimaryEmail.UserID = user.ID
-		formerPrimaryEmail.IsActivated = user.IsActive
-		if _, err = sess.Insert(formerPrimaryEmail); err != nil {
-			return err
-		}
-	}
-
-	user.Email = email.Email
-	if _, err = sess.ID(user.ID).AllCols().Update(user); err != nil {
-		return err
-	}
-
-	return sess.Commit()
-}
diff --git a/internal/db/users.go b/internal/db/users.go
index 631a7ff8..c39f9f39 100644
--- a/internal/db/users.go
+++ b/internal/db/users.go
@@ -49,7 +49,7 @@ type UsersStore interface {
 	// Create creates a new user and persists to database. It returns
 	// ErrNameNotAllowed if the given name or pattern of the name is not allowed as
 	// a username, or ErrUserAlreadyExist when a user with same name already exists,
-	// or ErrEmailAlreadyUsed if the email has been used by another user.
+	// or ErrEmailAlreadyUsed if the email has been verified by another user.
 	Create(ctx context.Context, username, email string, opts CreateUserOptions) (*User, error)
 
 	// GetByEmail returns the user (not organization) with given email. It ignores
@@ -101,6 +101,27 @@ type UsersStore interface {
 	// DeleteInactivated deletes all inactivated users.
 	DeleteInactivated() error
 
+	// AddEmail adds a new email address to given user. It returns
+	// ErrEmailAlreadyUsed if the email has been verified by another user.
+	AddEmail(ctx context.Context, userID int64, email string, isActivated bool) error
+	// GetEmail returns the email address of the given user. If `needsActivated` is
+	// true, only activated email will be returned, otherwise, it may return
+	// inactivated email addresses. It returns ErrEmailNotExist when no qualified
+	// email is not found.
+	GetEmail(ctx context.Context, userID int64, email string, needsActivated bool) (*EmailAddress, error)
+	// ListEmails returns all email addresses of the given user. It always includes
+	// a primary email address.
+	ListEmails(ctx context.Context, userID int64) ([]*EmailAddress, error)
+	// MarkEmailActivated marks the email address of the given user as activated,
+	// and new rands are generated for the user.
+	MarkEmailActivated(ctx context.Context, userID int64, email string) error
+	// MarkEmailPrimary marks the email address of the given user as primary. It
+	// returns ErrEmailNotExist when the email is not found for the user, and
+	// ErrEmailNotActivated when the email is not activated.
+	MarkEmailPrimary(ctx context.Context, userID int64, email string) error
+	// DeleteEmail deletes the email address of the given user.
+	DeleteEmail(ctx context.Context, userID int64, email string) error
+
 	// Follow marks the user to follow the other user.
 	Follow(ctx context.Context, userID, followID int64) error
 	// Unfollow removes the mark the user to follow the other user.
@@ -386,7 +407,7 @@ func (db *users) Create(ctx context.Context, username, email string, opts Create
 		}
 	}
 
-	email = strings.ToLower(email)
+	email = strings.ToLower(strings.TrimSpace(email))
 	_, err = db.GetByEmail(ctx, email)
 	if err == nil {
 		return nil, ErrEmailAlreadyUsed{
@@ -1061,6 +1082,183 @@ func (db *users) UseCustomAvatar(ctx context.Context, userID int64, avatar []byt
 		Error
 }
 
+func (db *users) AddEmail(ctx context.Context, userID int64, email string, isActivated bool) error {
+	email = strings.ToLower(strings.TrimSpace(email))
+	_, err := db.GetByEmail(ctx, email)
+	if err == nil {
+		return ErrEmailAlreadyUsed{
+			args: errutil.Args{
+				"email": email,
+			},
+		}
+	} else if !IsErrUserNotExist(err) {
+		return errors.Wrap(err, "check user by email")
+	}
+
+	return db.WithContext(ctx).Create(
+		&EmailAddress{
+			UserID:      userID,
+			Email:       email,
+			IsActivated: isActivated,
+		},
+	).Error
+}
+
+var _ errutil.NotFound = (*ErrEmailNotExist)(nil)
+
+type ErrEmailNotExist struct {
+	args errutil.Args
+}
+
+// IsErrEmailAddressNotExist returns true if the underlying error has the type
+// ErrEmailNotExist.
+func IsErrEmailAddressNotExist(err error) bool {
+	_, ok := errors.Cause(err).(ErrEmailNotExist)
+	return ok
+}
+
+func (err ErrEmailNotExist) Error() string {
+	return fmt.Sprintf("email address does not exist: %v", err.args)
+}
+
+func (ErrEmailNotExist) NotFound() bool {
+	return true
+}
+
+func (db *users) GetEmail(ctx context.Context, userID int64, email string, needsActivated bool) (*EmailAddress, error) {
+	tx := db.WithContext(ctx).Where("uid = ? AND email = ?", userID, email)
+	if needsActivated {
+		tx = tx.Where("is_activated = ?", true)
+	}
+
+	emailAddress := new(EmailAddress)
+	err := tx.First(emailAddress).Error
+	if err != nil {
+		if err == gorm.ErrRecordNotFound {
+			return nil, ErrEmailNotExist{
+				args: errutil.Args{
+					"email": email,
+				},
+			}
+		}
+		return nil, err
+	}
+	return emailAddress, nil
+}
+
+func (db *users) ListEmails(ctx context.Context, userID int64) ([]*EmailAddress, error) {
+	user, err := db.GetByID(ctx, userID)
+	if err != nil {
+		return nil, errors.Wrap(err, "get user")
+	}
+
+	var emails []*EmailAddress
+	err = db.WithContext(ctx).Where("uid = ?", userID).Order("id ASC").Find(&emails).Error
+	if err != nil {
+		return nil, errors.Wrap(err, "list emails")
+	}
+
+	isPrimaryFound := false
+	for _, email := range emails {
+		if email.Email == user.Email {
+			isPrimaryFound = true
+			email.IsPrimary = true
+			break
+		}
+	}
+
+	// We always want the primary email address displayed, even if it's not in the
+	// email_address table yet.
+	if !isPrimaryFound {
+		emails = append(emails, &EmailAddress{
+			Email:       user.Email,
+			IsActivated: user.IsActive,
+			IsPrimary:   true,
+		})
+	}
+	return emails, nil
+}
+
+func (db *users) MarkEmailActivated(ctx context.Context, userID int64, email string) error {
+	return db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
+		err := db.WithContext(ctx).
+			Model(&EmailAddress{}).
+			Where("uid = ? AND email = ?", userID, email).
+			Update("is_activated", true).
+			Error
+		if err != nil {
+			return errors.Wrap(err, "mark email activated")
+		}
+
+		return NewUsersStore(tx).Update(ctx, userID, UpdateUserOptions{GenerateNewRands: true})
+	})
+}
+
+type ErrEmailNotVerified struct {
+	args errutil.Args
+}
+
+// IsErrEmailNotVerified returns true if the underlying error has the type
+// ErrEmailNotVerified.
+func IsErrEmailNotVerified(err error) bool {
+	_, ok := errors.Cause(err).(ErrEmailNotVerified)
+	return ok
+}
+
+func (err ErrEmailNotVerified) Error() string {
+	return fmt.Sprintf("email has not been verified: %v", err.args)
+}
+
+func (db *users) MarkEmailPrimary(ctx context.Context, userID int64, email string) error {
+	var emailAddress EmailAddress
+	err := db.WithContext(ctx).Where("uid = ? AND email = ?", userID, email).First(&emailAddress).Error
+	if err != nil {
+		if err == gorm.ErrRecordNotFound {
+			return ErrEmailNotExist{args: errutil.Args{"email": email}}
+		}
+		return errors.Wrap(err, "get email address")
+	}
+
+	if !emailAddress.IsActivated {
+		return ErrEmailNotVerified{args: errutil.Args{"email": email}}
+	}
+
+	user, err := db.GetByID(ctx, userID)
+	if err != nil {
+		return errors.Wrap(err, "get user")
+	}
+
+	return db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
+		// Make sure the former primary email doesn't disappear.
+		err = tx.FirstOrCreate(
+			&EmailAddress{
+				UserID:      user.ID,
+				Email:       user.Email,
+				IsActivated: user.IsActive,
+			},
+			&EmailAddress{
+				UserID: user.ID,
+				Email:  user.Email,
+			},
+		).Error
+		if err != nil {
+			return errors.Wrap(err, "upsert former primary email address")
+		}
+
+		return tx.Model(&User{}).
+			Where("id = ?", user.ID).
+			Updates(map[string]any{
+				"email":        email,
+				"updated_unix": tx.NowFunc().Unix(),
+			},
+			).Error
+	})
+}
+
+func (db *users) DeleteEmail(ctx context.Context, userID int64, email string) error {
+	return db.WithContext(ctx).Where("uid = ? AND email = ?", userID, email).Delete(&EmailAddress{}).Error
+}
+
 // UserType indicates the type of the user account.
 type UserType int
 
@@ -1422,6 +1620,15 @@ func isUsernameAllowed(name string) error {
 	return isNameAllowed(reservedUsernames, reservedUsernamePatterns, name)
 }
 
+// EmailAddress is an email address of a user.
+type EmailAddress struct {
+	ID          int64  `gorm:"primaryKey"`
+	UserID      int64  `xorm:"uid INDEX NOT NULL" gorm:"column:uid;index;uniqueIndex:email_address_user_email_unique;not null"`
+	Email       string `xorm:"UNIQUE NOT NULL" gorm:"uniqueIndex:email_address_user_email_unique;not null;size:254"`
+	IsActivated bool   `gorm:"not null;default:FALSE"`
+	IsPrimary   bool   `xorm:"-" gorm:"-" json:"-"`
+}
+
 // Follow represents relations of users and their followers.
 type Follow struct {
 	ID       int64 `gorm:"primaryKey"`
diff --git a/internal/db/users_test.go b/internal/db/users_test.go
index d83ffe50..8b2e7e59 100644
--- a/internal/db/users_test.go
+++ b/internal/db/users_test.go
@@ -116,6 +116,12 @@ func TestUsers(t *testing.T) {
 		{"SearchByName", usersSearchByName},
 		{"Update", usersUpdate},
 		{"UseCustomAvatar", usersUseCustomAvatar},
+		{"AddEmail", usersAddEmail},
+		{"GetEmail", usersGetEmail},
+		{"ListEmails", usersListEmails},
+		{"MarkEmailActivated", usersMarkEmailActivated},
+		{"MarkEmailPrimary", usersMarkEmailPrimary},
+		{"DeleteEmail", usersDeleteEmail},
 		{"Follow", usersFollow},
 		{"IsFollowing", usersIsFollowing},
 		{"Unfollow", usersUnfollow},
@@ -1100,7 +1106,19 @@ func usersUpdate(t *testing.T, db *users) {
 	})
 
 	t.Run("update email but already used", func(t *testing.T) {
-		// todo
+		bob, err := db.Create(
+			ctx,
+			"bob",
+			"bob@example.com",
+			CreateUserOptions{
+				Activated: true,
+			},
+		)
+		require.NoError(t, err)
+
+		got := db.Update(ctx, alice.ID, UpdateUserOptions{Email: &bob.Email})
+		want := ErrEmailAlreadyUsed{args: errutil.Args{"email": bob.Email}}
+		assert.Equal(t, want, got)
 	})
 
 	loginSource := int64(1)
@@ -1204,6 +1222,161 @@ func TestIsUsernameAllowed(t *testing.T) {
 	}
 }
 
+func usersAddEmail(t *testing.T, db *users) {
+	ctx := context.Background()
+
+	t.Run("multiple users can add the same unverified email", func(t *testing.T) {
+		alice, err := db.Create(ctx, "alice", "unverified@example.com", CreateUserOptions{})
+		require.NoError(t, err)
+		err = db.AddEmail(ctx, alice.ID+1, "unverified@example.com", false)
+		require.NoError(t, err)
+	})
+
+	t.Run("only one user can add the same verified email", func(t *testing.T) {
+		bob, err := db.Create(ctx, "bob", "verified@example.com", CreateUserOptions{Activated: true})
+		require.NoError(t, err)
+		got := db.AddEmail(ctx, bob.ID+1, "verified@example.com", true)
+		want := ErrEmailAlreadyUsed{args: errutil.Args{"email": "verified@example.com"}}
+		require.Equal(t, want, got)
+	})
+}
+
+func usersGetEmail(t *testing.T, db *users) {
+	ctx := context.Background()
+
+	const testUserID = 1
+	const testEmail = "alice@example.com"
+	_, err := db.GetEmail(ctx, testUserID, testEmail, false)
+	wantErr := ErrEmailNotExist{
+		args: errutil.Args{
+			"email": testEmail,
+		},
+	}
+	assert.Equal(t, wantErr, err)
+
+	err = db.AddEmail(ctx, testUserID, testEmail, false)
+	require.NoError(t, err)
+	got, err := db.GetEmail(ctx, testUserID, testEmail, false)
+	require.NoError(t, err)
+	assert.Equal(t, testEmail, got.Email)
+
+	// Should not return if we ask for a different user
+	_, err = db.GetEmail(ctx, testUserID+1, testEmail, false)
+	assert.Equal(t, wantErr, err)
+
+	// Should not return if we only want activated emails
+	_, err = db.GetEmail(ctx, testUserID, testEmail, true)
+	assert.Equal(t, wantErr, err)
+
+	err = db.MarkEmailActivated(ctx, testUserID, testEmail)
+	require.NoError(t, err)
+	got, err = db.GetEmail(ctx, testUserID, testEmail, true)
+	require.NoError(t, err)
+	assert.Equal(t, testEmail, got.Email)
+}
+
+func usersListEmails(t *testing.T, db *users) {
+	ctx := context.Background()
+
+	t.Run("list emails with primary email", func(t *testing.T) {
+		alice, err := db.Create(ctx, "alice", "alice@example.com", CreateUserOptions{})
+		require.NoError(t, err)
+		err = db.AddEmail(ctx, alice.ID, "alice2@example.com", true)
+		require.NoError(t, err)
+		err = db.MarkEmailPrimary(ctx, alice.ID, "alice2@example.com")
+		require.NoError(t, err)
+
+		emails, err := db.ListEmails(ctx, alice.ID)
+		require.NoError(t, err)
+		got := make([]string, 0, len(emails))
+		for _, email := range emails {
+			got = append(got, email.Email)
+		}
+		want := []string{"alice2@example.com", "alice@example.com"}
+		assert.Equal(t, want, got)
+	})
+
+	t.Run("list emails without primary email", func(t *testing.T) {
+		bob, err := db.Create(ctx, "bob", "bob@example.com", CreateUserOptions{})
+		require.NoError(t, err)
+		err = db.AddEmail(ctx, bob.ID, "bob2@example.com", false)
+		require.NoError(t, err)
+
+		emails, err := db.ListEmails(ctx, bob.ID)
+		require.NoError(t, err)
+		got := make([]string, 0, len(emails))
+		for _, email := range emails {
+			got = append(got, email.Email)
+		}
+		want := []string{"bob2@example.com", "bob@example.com"}
+		assert.Equal(t, want, got)
+	})
+}
+
+func usersMarkEmailActivated(t *testing.T, db *users) {
+	ctx := context.Background()
+
+	alice, err := db.Create(ctx, "alice", "alice@example.com", CreateUserOptions{})
+	require.NoError(t, err)
+
+	err = db.AddEmail(ctx, alice.ID, "alice2@example.com", false)
+	require.NoError(t, err)
+	err = db.MarkEmailActivated(ctx, alice.ID, "alice2@example.com")
+	require.NoError(t, err)
+
+	gotEmail, err := db.GetEmail(ctx, alice.ID, "alice2@example.com", true)
+	require.NoError(t, err)
+	assert.True(t, gotEmail.IsActivated)
+
+	gotAlice, err := db.GetByID(ctx, alice.ID)
+	require.NoError(t, err)
+	assert.NotEqual(t, alice.Rands, gotAlice.Rands)
+}
+
+func usersMarkEmailPrimary(t *testing.T, db *users) {
+	ctx := context.Background()
+	alice, err := db.Create(ctx, "alice", "alice@example.com", CreateUserOptions{})
+	require.NoError(t, err)
+	err = db.AddEmail(ctx, alice.ID, "alice2@example.com", false)
+	require.NoError(t, err)
+
+	// Should fail because email not verified
+	gotError := db.MarkEmailPrimary(ctx, alice.ID, "alice2@example.com")
+	wantError := ErrEmailNotVerified{args: errutil.Args{"email": "alice2@example.com"}}
+	assert.Equal(t, wantError, gotError)
+
+	// Mark email as verified and should succeed
+	err = db.MarkEmailActivated(ctx, alice.ID, "alice2@example.com")
+	require.NoError(t, err)
+	err = db.MarkEmailPrimary(ctx, alice.ID, "alice2@example.com")
+	require.NoError(t, err)
+	gotAlice, err := db.GetByID(ctx, alice.ID)
+	require.NoError(t, err)
+	assert.Equal(t, "alice2@example.com", gotAlice.Email)
+
+	// Former primary email should be preserved
+	gotEmail, err := db.GetEmail(ctx, alice.ID, "alice@example.com", false)
+	require.NoError(t, err)
+	assert.False(t, gotEmail.IsActivated)
+}
+
+func usersDeleteEmail(t *testing.T, db *users) {
+	ctx := context.Background()
+	alice, err := db.Create(ctx, "alice", "alice@example.com", CreateUserOptions{})
+	require.NoError(t, err)
+
+	err = db.AddEmail(ctx, alice.ID, "alice2@example.com", false)
+	require.NoError(t, err)
+	_, err = db.GetEmail(ctx, alice.ID, "alice2@example.com", false)
+	require.NoError(t, err)
+
+	err = db.DeleteEmail(ctx, alice.ID, "alice2@example.com")
+	require.NoError(t, err)
+	_, got := db.GetEmail(ctx, alice.ID, "alice2@example.com", false)
+	want := ErrEmailNotExist{args: errutil.Args{"email": "alice2@example.com"}}
+	require.Equal(t, want, got)
+}
+
 func usersFollow(t *testing.T, db *users) {
 	ctx := context.Background()