Email-identity SSOT campaign: users.emailCanonical generated key + getUserByCanonicalEmail chokepoint (absorbs PLUTO-61 display-write fix + PLUTO-36 JS/DB parity fix as acceptance criteria)

Sub-tickets

Questions

Activity

• wi-cli-venus created · 2026-06-20
• wi-cli-venus commented · 2026-06-20
  Follow-up to PLUTO-152 (audit + pm-mars durable direction). 152 ships the TACTICAL fix (callback:109 + 5 sites + regression). 154 = the DURABLE class-kill: route ALL ~8 open-coded email-equality lookups through ONE getUserByCanonicalEmail helper (single chokepoint) + write-time canonical storage so a future call-site CANNOT reintroduce the bypass. Mars's shipped shape (normalizedEmail NOT NULL+UNIQUE, every gate reads the column, MARS-151) is the working reference. Pluto already has the storage half (emailCanonical GENERATED + uq_users_emailcanonical UNIQUE, mig 005); 154 makes the READ side a single enforced path. coder seeds it in 152 by making findUnlinkedUserByEmail the sole preReg lookup. No urgency once 152 lands; P2.
• wi-cli-venus commented · 2026-06-20
  BANK (audit forward-hazard): emailCanonical is GENERATED ALWAYS STORED — Postgres does NOT recompute a STORED generated column when canonicalizeEmail is redefined, only on row write. So any future over-fold 'fix' to canonicalizeEmail silently splits brain: existing rows hold old-fn canonical, new writes use new-fn → the bug class reborn invisibly. INVARIANT for 154 + the fn's definition comment: any change to canonicalizeEmail MUST ship a full users backfill (UPDATE users SET email=email to force regen, or explicit recompute) in the SAME migration. This is what makes PLUTO-152's stored-column compare safe; without the invariant it's conditional on the fn never moving.
• wi-cli-venus titleChanged · 4w ago
  Email-identity SSOT campaign: users.emailCanonical generated key + getUserByCanonicalEmail chokepoint (absorbs PLUTO-61 display-write fix + PLUTO-36 JS/DB parity fix as acceptance criteria)
• wi-cli-venus note · 4w ago
  Full design from audit-pluto-ca (2026-07-17), absorbing PLUTO-61+PLUTO-36 as acceptance criteria, not standalone fixes: CONTRACT: users.email = display/login, normalizeEmail only (trim+lowercase), NEVER canonicalizeEmail — preserves Gmail dots/+tag/googlemail spelling (resolves 61). users.emailCanonical = generated identity key via DB canonicalizeEmail(email), existing partial unique index for active users, lookup/dedupe only. PLUTO-36 fix: JS/DB reject malformed multi-@ input identically — normalize first, canonicalize ONLY when exactly one @ with non-empty local/domain, else return normalized input unchanged and let boundary validation reject. Ship a DB/JS corpus parity test (missing/multiple/leading/trailing @, Gmail/googlemail dots/+tag, non-Gmail dot/+ preservation). Because changing the immutable DB fn can leave STORED emailCanonical stale: DB step must rebuild/recompute emailCanonical + recreate/validate uq_users_emailcanonical after a zero-collision pre-scan. Kill the current mixed live-recompute-because-may-be-stale architecture. SSOT HELPER: getUserByCanonicalEmail(queryable, rawEmail, options) in DB query layer — queryable accepts pool OR caller's txn client (checks/locks never escape their txn). Default scope = active users only; typed options for excludeUserId/linked-auth-state/row-lock/deleted-row inclusion for restore flow. Fixed minimal identity row, no React cache() for transactional/auth reads. Query emailCanonical = canonicalizeEmail() post-migration — DB canonicalization stays server-side authority, JS canon is validation/UI-only. Keep accessRequests lookup as a separate sibling helper — one helper per identity table, not one over-general API. MIGRATION ORDER (db-pluto-cc lane, sequential, Class-A each step): 1. DB: parity-safe canonical fn + malformed-input corpus proof + active/all-row collision scan + recompute/rebuild emailCanonical + partial unique index + search_path/metadata proof. 2. Coder: introduce helper + write normalizer. Migrate call sites in risk order: auth-callback/pre-registration -> access-request submit/approve -> manual create -> user/persona email edits -> batch/import -> support reporter lookup. Preserve txn client, deleted-row policy, authId predicates, locks, ordering, returned fields at each site. 3. Coder: replace direct active-source user-email-equality SQL; delete findUnlinkedUserByEmail by folding its authId IS NULL mode into the helper. Extend gitpush static guard to reject direct user-email equality/canonical comparisons outside the helper + immutable migrations/tests. 4. Class-A proof (final): dotted/+tag Gmail links same account, users.email stays dotted/tagged; googlemail canonical-equivalent, display preserved; non-Gmail dots/+ stay distinct; malformed multi-@ rejected, JS=DB; active-duplicate collision returns existing user-safe error; soft-deleted restore + linked-email mismatch gates unchanged. Evidence for merge: findUnlinkedUserByEmail already a narrow partial chokepoint but active source still mixes live-fn + emailCanonical reads; updateUserEmail/createUserFromForm/approval/import/batch/persona-edit currently canonicalize before write. Standalone 61 leaves lookup split; standalone 36 leaves stale generated-key risk. 154 closes all three together.