-- Additional email addresses per user. The primary address stays in users.email (sign-in, notifications); -- rows here are the user's other addresses. Several accounts may have the same address pending, but -- only one may hold it verified: confirming it removes everyone else's pending claim. CREATE TABLE user_emails ( user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE, email TEXT NOT NULL COLLATE NOCASE, verified_at INTEGER, created_at INTEGER NOT NULL, PRIMARY KEY (user_id, email) ); CREATE UNIQUE INDEX user_emails_verified ON user_emails(email) WHERE verified_at IS NOT NULL; CREATE INDEX user_emails_email ON user_emails(email);