-- 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);