g1t/services/identity/migrations/0019_user_emails.sql
Pick any line to see why it is the way it is: the commit, the pull request and issue it came from, and what the agent was thinking.
| Invite-only launch: sign in with GitHub, repository access and lifecycle, many emails, a new look | 1 | -- Many email addresses per account, one of them primary. Every timestamp |
| 2 | -- is RFC 3339 UTC. See src/emails.rs and src/security.rs. | |
| 3 | -- | |
| 4 | -- users.email stays, as a copy of the primary address that identity keeps | |
| 5 | -- in step, and users.email_verified_at as the primary's verified_at: other | |
| 6 | -- services and older queries read them, and "the account is confirmed" | |
| 7 | -- still means exactly that the primary is. user_emails is the source of | |
| 8 | -- truth for every address. | |
| 9 | ||
| 10 | CREATE TABLE IF NOT EXISTS user_emails ( | |
| 11 | id TEXT PRIMARY KEY, | |
| 12 | user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE, | |
| 13 | -- Trimmed and lowercased: what uniqueness and lookups use. | |
| 14 | email TEXT NOT NULL, | |
| 15 | -- As the person typed it, for showing. | |
| 16 | display TEXT NOT NULL, | |
| 17 | -- Null until a link sent to the address is followed. | |
| 18 | verified_at TEXT, | |
| 19 | -- When a confirmation link was last sent, to space out resends. | |
| 20 | sent_at TEXT, | |
| 21 | created_at TEXT NOT NULL | |
| 22 | ); | |
| 23 | -- A confirmed address belongs to one account. Anyone may add an address | |
| 24 | -- they have not confirmed; the first account to confirm it keeps it, and | |
| 25 | -- everyone else's unconfirmed claim to it is dropped (src/emails.rs). | |
| 26 | CREATE UNIQUE INDEX IF NOT EXISTS user_emails_verified ON user_emails (email) WHERE verified_at IS NOT NULL; | |
| 27 | CREATE UNIQUE INDEX IF NOT EXISTS user_emails_per_user ON user_emails (user_id, email); | |
| 28 | CREATE INDEX IF NOT EXISTS user_emails_email ON user_emails (email); | |
| 29 | ||
| 30 | -- The primary address: where account mail and password resets go. | |
| 31 | ALTER TABLE users ADD COLUMN primary_email_id TEXT; | |
| 32 | -- A second confirmed address that also gets security notices. Null: the | |
| 33 | -- primary only. | |
| 34 | ALTER TABLE users ADD COLUMN backup_email_id TEXT; | |
| 35 | -- Commits g1t makes for the person on the web use their noreply address | |
| 36 | -- (<id suffix>+<username>@users.noreply.g1t.sh) instead of the primary. | |
| 37 | -- On by default, so no address is ever published without asking. | |
| 38 | ALTER TABLE users ADD COLUMN private_email INTEGER NOT NULL DEFAULT 1; | |
| 39 | -- Refuse pushes whose commits carry one of the person's private addresses. | |
| 40 | -- Stored now; enforced by repos once it asks (see docs). | |
| 41 | ALTER TABLE users ADD COLUMN block_private_pushes INTEGER NOT NULL DEFAULT 0; | |
| 42 | ||
| 43 | -- Which address a confirmation link is for. Null on links sent before | |
| 44 | -- this migration: those confirm the primary. | |
| 45 | ALTER TABLE email_tokens ADD COLUMN email_id TEXT; | |
| 46 | ||
| 47 | -- When the session's owner last proved who they are (signed in, or typed | |
| 48 | -- their password again). Sensitive changes need it to be recent | |
| 49 | -- (src/security.rs). Null on older sessions: never recent. | |
| 50 | ALTER TABLE sessions ADD COLUMN authenticated_at TEXT; | |
| 51 | ||
| 52 | -- The addresses accounts have now. users.email has been unique, so no two | |
| 53 | -- rows collide on the confirmed index. | |
| 54 | INSERT OR IGNORE INTO user_emails (id, user_id, email, display, verified_at, created_at) | |
| 55 | SELECT 'eml_' || lower(hex(randomblob(13))), id, lower(trim(email)), trim(email), email_verified_at, created_at | |
| 56 | FROM users | |
| 57 | WHERE email IS NOT NULL AND trim(email) <> ''; | |
| 58 | ||
| 59 | UPDATE users SET primary_email_id = ( | |
| 60 | SELECT user_emails.id FROM user_emails | |
| 61 | WHERE user_emails.user_id = users.id AND user_emails.email = lower(trim(users.email)) | |
| 62 | ) | |
| 63 | WHERE email IS NOT NULL AND trim(email) <> ''; | |
| 64 | ||
| 65 | -- Uniqueness moves to confirmed addresses (above): two accounts may both | |
| 66 | -- have an unconfirmed address, and then neither has it yet. | |
| 67 | DROP INDEX IF EXISTS users_email; | |
| 68 | ||
| 69 | -- Every place an account is made (password, invite, GitHub) inserts into | |
| 70 | -- users with an email; this gives that address its row and makes it the | |
| 71 | -- primary, so none of them has to know about user_emails. An account made | |
| 72 | -- with a confirmed address (GitHub's) also wins it from anyone's | |
| 73 | -- unconfirmed claim. An address another account has confirmed fails the | |
| 74 | -- insert with a UNIQUE error, which those places report as taken. | |
| 75 | CREATE TRIGGER IF NOT EXISTS users_primary_email AFTER INSERT ON users | |
| 76 | WHEN NEW.email IS NOT NULL AND trim(NEW.email) <> '' | |
| 77 | BEGIN | |
| 78 | SELECT RAISE(ABORT, 'UNIQUE constraint failed: user_emails.email') | |
| 79 | WHERE EXISTS ( | |
| 80 | SELECT 1 FROM user_emails | |
| 81 | WHERE email = lower(trim(NEW.email)) AND verified_at IS NOT NULL AND user_id <> NEW.id | |
| 82 | ); | |
| 83 | UPDATE users SET email = NULL, email_verified_at = NULL, primary_email_id = NULL | |
| 84 | WHERE NEW.email_verified_at IS NOT NULL AND id <> NEW.id | |
| 85 | AND primary_email_id IN ( | |
| 86 | SELECT id FROM user_emails WHERE email = lower(trim(NEW.email)) AND verified_at IS NULL | |
| 87 | ); | |
| 88 | DELETE FROM user_emails | |
| 89 | WHERE NEW.email_verified_at IS NOT NULL AND user_id <> NEW.id | |
| 90 | AND email = lower(trim(NEW.email)) AND verified_at IS NULL; | |
| 91 | INSERT INTO user_emails (id, user_id, email, display, verified_at, created_at) | |
| 92 | VALUES ('eml_' || lower(hex(randomblob(13))), NEW.id, lower(trim(NEW.email)), trim(NEW.email), | |
| 93 | NEW.email_verified_at, NEW.created_at); | |
| 94 | UPDATE users SET email = lower(trim(NEW.email)), primary_email_id = ( | |
| 95 | SELECT id FROM user_emails WHERE user_id = NEW.id AND email = lower(trim(NEW.email)) | |
| 96 | ) WHERE id = NEW.id; | |
| 97 | END; | |
| 98 | ||
| 99 | -- What happened to an account's security: addresses added, confirmed, | |
| 100 | -- removed or made primary, passwords changed. Shown to the person as their | |
| 101 | -- security log, and to staff. Never another person's address in `detail`. | |
| 102 | CREATE TABLE IF NOT EXISTS security_events ( | |
| 103 | id TEXT PRIMARY KEY, | |
| 104 | user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE, | |
| 105 | -- email_added, email_verified, email_removed, primary_email_changed, | |
| 106 | -- backup_email_changed, email_privacy_changed, password_changed. | |
| 107 | kind TEXT NOT NULL, | |
| 108 | -- The address concerned, or what changed, in words. | |
| 109 | detail TEXT, | |
| 110 | -- Who did it: the person (null), or staff, by email. | |
| 111 | staff TEXT, | |
| 112 | -- Why staff did it. | |
| 113 | reason TEXT, | |
| 114 | created_at TEXT NOT NULL | |
| 115 | ); | |
| 116 | CREATE INDEX IF NOT EXISTS security_events_user ON security_events (user_id, created_at); | |
| 117 | ||
| 118 | -- Guessing passwords, and asking for mail, slowed down (src/throttle.rs). | |
| 119 | -- `key` such as `signin.account:usr_…`, `signin.client:<sha256 of the IP>` | |
| 120 | -- or `reset.email:<sha256 of the address>`. | |
| 121 | CREATE TABLE IF NOT EXISTS auth_throttle ( | |
| 122 | key TEXT PRIMARY KEY, | |
| 123 | -- Failures (or requests) since window_start. | |
| 124 | hits INTEGER NOT NULL, | |
| 125 | window_start TEXT NOT NULL, | |
| 126 | -- Nothing more is tried for this key until then. Null: not locked. | |
| 127 | locked_until TEXT, | |
| 128 | -- When the account's owner was last told it was locked. | |
| 129 | notified_at TEXT | |
| 130 | ); | |
| 131 | CREATE INDEX IF NOT EXISTS auth_throttle_window ON auth_throttle (window_start); |