| 1 | -- Multiple email addresses per user, one marked primary, plus a per-user toggle |
| 2 | -- to show the primary email on the public profile. |
| 3 | ALTER TABLE users ADD COLUMN email_public INTEGER NOT NULL DEFAULT 0; |
| 4 | |
| 5 | CREATE TABLE user_emails ( |
| 6 | id TEXT PRIMARY KEY, |
| 7 | user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE, |
| 8 | email TEXT NOT NULL, |
| 9 | email_lower TEXT NOT NULL, |
| 10 | is_primary INTEGER NOT NULL DEFAULT 0, |
| 11 | created_at BIGINT NOT NULL |
| 12 | ); |
| 13 | CREATE UNIQUE INDEX idx_user_emails_lower ON user_emails (email_lower); |
| 14 | CREATE INDEX idx_user_emails_user ON user_emails (user_id); |
| 15 | |
| 16 | -- Seed each existing user's current email as their primary address. |
| 17 | INSERT INTO user_emails (id, user_id, email, email_lower, is_primary, created_at) |
| 18 | SELECT id, id, email, email_lower, 1, created_at FROM users; |