docs(website): unique, changeable, verifiable email addresses (engagement Phase 1b) #180

wtclaude · 2026-08-29T06:55:33Z

wtclaude commented

2026-08-29 06:55:33 +00:00

Companion to website#167.

Records Phase 1b as built, and corrects two things the plan got wrong before anyone builds on them.

The correction that matters

Phase 1b step 2 said to pin the UNIQUE index to a case-insensitive collation "for the same reason username was". Tested against the deployment's own MariaDB 11.8, that is wrong in a way that would have destroyed data: under both utf8mb4_general_ci and the server-default utf8mb4_uca1400_ai_ci, josé@x.com and jose@x.com compare EQUAL. Every _ci collation available here is also accent-insensitive.

That index would refuse the second address forever, and the de-duplication would have cleared a legitimate account's address and reported it as a duplicate that never was.

The step is rewritten to point at the generated-column design that shipped, and a new As built block carries the full reasoning, the collations that would have been right but are MariaDB 11.4+ only (so pinning one just relocates §0.6's boot failure), and — worth reading on its own — the second-order version of the same bug: written the obvious way, the de-dupe's own LOWER(email) = LOWER(email) comparison uses the column's collation and over-folds even when the index does not. A seeded fixture caught it nulling jose@x.com as a duplicate of josé@x.com, one statement after the fix meant to prevent exactly that.

§0.6 undercounted the blast radius

It names two callers of isDuplicateUsername(). There are five, and the three it omits fail worse than the two it names — an invite that fails after the invitee has clicked the link and chosen a password, and two admin routes with no catch at all that turned a duplicate address into an opaque 500. Amended in place as a dated note rather than a rewrite, so the original finding stays readable as what was known on 2026-08-28.

The amendment also records that the violated index name lives only in the driver's message text — and that the same message embeds the bound parameters, so on an email collision it contains the address. That is a second, independent reason those errors must never reach a client.

The rest

BACKEND_DESIGN.md — the users table (already stale before this PR: it predated the player-account work, showing role ENUM('admin','editor'), a NOT NULL password_hash, and no email columns at all), plus email_verifications and email_dedupe_report, plus the five new routes. Each column carries why, not just its type — particularly why the index is on email_norm and never on email.
UPGRADE_NOTES.md — an operator entry. This is exactly the kind of change that file exists for: nothing breaks loudly, and the affected users find out the next time they try to reset a password. It says plainly that those users are the reason the warning exists and that only the operator can tell them.
api-route-inventory.json — regenerated wholesale from server/routes.manifest.json (203 → 210). Still has no CI gate, flagged again; it will drift again.

Deliberately untouched

CLAUDE.md still says "identities are never auto-provisioned", which provisionSsoPlayer contradicts — §0.6 finding 3 already records this. Correcting it is outside Phase 1b's scope; happy to take it separately.
API_V2_PLAN.md — a record of the domain split as it landed, left as written.

AI disclosure

Written with Claude Code (Opus 5).

Companion to **[website#167](https://gitea.whitlocktech.com/RunicGateway/website/pulls/167)**. Records Phase 1b as built, and **corrects two things the plan got wrong** before anyone builds on them. ## The correction that matters Phase 1b step 2 said to pin the UNIQUE index to a case-insensitive collation *"for the same reason `username` was"*. Tested against the deployment's own MariaDB 11.8, that is wrong in a way that would have destroyed data: under **both** `utf8mb4_general_ci` and the server-default `utf8mb4_uca1400_ai_ci`, **`josé@x.com` and `jose@x.com` compare EQUAL**. Every `_ci` collation available here is also accent-insensitive. That index would refuse the second address forever, and the de-duplication would have **cleared a legitimate account's address** and reported it as a duplicate that never was. The step is rewritten to point at the generated-column design that shipped, and a new **As built** block carries the full reasoning, the collations that would have been right but are MariaDB 11.4+ only (so pinning one just relocates §0.6's boot failure), and — worth reading on its own — **the second-order version of the same bug**: written the obvious way, the de-dupe's own `LOWER(email) = LOWER(email)` comparison uses the *column's* collation and over-folds even when the index does not. A seeded fixture caught it nulling `jose@x.com` as a duplicate of `josé@x.com`, one statement after the fix meant to prevent exactly that. ## §0.6 undercounted the blast radius It names **two** callers of `isDuplicateUsername()`. There are **five**, and the three it omits fail worse than the two it names — an invite that fails *after* the invitee has clicked the link and chosen a password, and two admin routes with **no catch at all** that turned a duplicate address into an opaque 500. Amended in place as a dated note rather than a rewrite, so the original finding stays readable as what was known on 2026-08-28. The amendment also records that the violated index name lives **only in the driver's message text** — and that the same message embeds the bound parameters, so on an email collision it *contains the address*. That is a second, independent reason those errors must never reach a client. ## The rest - **`BACKEND_DESIGN.md`** — the `users` table (**already stale before this PR**: it predated the player-account work, showing `role ENUM('admin','editor')`, a NOT NULL `password_hash`, and no email columns at all), plus `email_verifications` and `email_dedupe_report`, plus the five new routes. Each column carries *why*, not just its type — particularly why the index is on `email_norm` and never on `email`. - **`UPGRADE_NOTES.md`** — an operator entry. This is exactly the kind of change that file exists for: nothing breaks loudly, and the affected users find out the next time they try to reset a password. It says plainly that **those users are the reason the warning exists** and that only the operator can tell them. - **`api-route-inventory.json`** — regenerated wholesale from `server/routes.manifest.json` (203 → 210). Still has **no CI gate**, flagged again; it will drift again. ## Deliberately untouched - **`CLAUDE.md` still says *"identities are never auto-provisioned"***, which `provisionSsoPlayer` contradicts — §0.6 finding 3 already records this. Correcting it is outside Phase 1b's scope; happy to take it separately. - **`API_V2_PLAN.md`** — a record of the domain split as it landed, left as written. ## AI disclosure Written with Claude Code (Opus 5).

wtclaude added 1 commit 2026-08-29 06:55:35 +00:00

docs(website): unique, changeable, verifiable email addresses (engagement Phase 1b) 84481ee6b1

Companion to website#<pr>. Records Phase 1b as built, and corrects two things
the plan got wrong before anyone builds on them.

ENGAGEMENT.md
  - Phase 1b step 2 said to pin the index to a case-insensitive collation. Every
    _ci collation MariaDB offers here is also accent-insensitive, so that index
    would refuse jose@x.com once josé@x.com existed and the de-duplication would
    have cleared a legitimate account's address. The as-built block records the
    generated-column design that shipped instead, and the second-order version of
    the same bug that a seeded fixture caught in the de-dupe query itself.
  - §0.6 named two callers of isDuplicateUsername(). There are five, and the
    three it omits fail worse than the two it names.

BACKEND_DESIGN.md — the users table (already stale: it predated the player
account work), plus email_verifications and email_dedupe_report, and the five
new routes.

UPGRADE_NOTES.md — an operator entry, because the de-duplication is the kind of
quiet change this file exists for: nothing breaks, and the affected users find
out the next time they try to reset a password.

api-route-inventory.json — regenerated from the manifest; still ungated.

Co-Authored-By: Claude <noreply@anthropic.com>

whitlocktech merged commit 88de7e3331 into edge

2026-08-29 07:08:31 +00:00

whitlocktech deleted branch docs/unique-verifiable-email

2026-08-29 07:08:32 +00:00

whitlocktech referenced this issue from a commit

2026-08-29 07:08:33 +00:00

Merge pull request 'docs(website): unique, changeable, verifiable email addresses (engagement Phase 1b)' (#180) from docs/unique-verifiable-email into edge

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: RunicGateway/docs#180