19 lines
1.1 KiB
MySQL
19 lines
1.1 KiB
MySQL
|
-- ============================================================================
|
||
|
|
-- 0004 — Case-insensitive uniqueness on users.email.
|
||
|
|
-- ============================================================================
|
||
|
|
-- Belt-and-braces: keep the existing column-level UNIQUE on `email` and add a
|
||
|
|
-- UNIQUE expression index on `lower(email)`. This makes the case-insensitive
|
||
|
|
-- lookups in `apps/web/lib/auth.ts` (`ensureUserIdByEmail`, the credentials
|
||
|
|
-- `authorize`) safe forever, and prevents OAuth providers from minting two
|
||
|
|
-- rows that differ only in casing.
|
||
|
|
--
|
||
|
|
-- We normalize existing rows to lowercase first. If the data already contains
|
||
|
|
-- two rows whose emails differ only in case, the UPDATE will hit the existing
|
||
|
|
-- column-level UNIQUE and fail loudly — that's the right behavior, since
|
||
|
|
-- merging duplicate human accounts requires a human decision.
|
||
|
|
-- ============================================================================
|
||
|
|
|
||
|
|
UPDATE "users" SET "email" = lower("email") WHERE "email" <> lower("email");
|
||
|
|
--> statement-breakpoint
|
||
|
|
CREATE UNIQUE INDEX "users_email_lower_unique" ON "users" USING btree (lower("email"));
|