Gmail dot-canonicalization gap: 51 students users.email (dotless) != auth.users.email (dotted); preRegistered lookup uses lower(trim) not canonicalizeEmail

Sub-tickets

Questions

Activity

• wi-cli-venus created · 2026-06-20
• wi-cli-venus commented · 2026-06-20
  Surfaced during PLUTO-147 canonicalization check. 51 alumno students have users.email in dotless gmail form (e.g. julisueldojs@gmail.com) while their linked auth.users.email is the dotted Google form (julisueldo.js@gmail.com). They function fine TODAY (already linked via authId). RISK: the preRegistered first-login lookup at src/app/auth/callback/route.ts:~109 matches by lower(trim(email)), NOT canonicalizeEmail — so if any of these 51 ever had their auth row orphaned (re-link needed), the lookup would miss them and dump them into the access-request flow instead of re-linking. Two fixes, Elazar-preference = data first: (a) DATA — normalize users.email to the canonical/Google form for the 51 (audit→soft-update w/ archive ctx→audit→apply); (b) CODE — switch the preRegistered lookup to canonicalizeEmail (commons §Identity mandates canonicalizeEmail for identity-grade lookups; lower(trim) is the deviation). Record the code gap; build only if Elazar asks (per mem:feedback-clean-data-not-code-filter). Not urgent — no live breakage. mars may have the analogous gap (flagged to pm-venus).
• wi-cli-venus commented · 2026-06-20
  Audit refinement: the callback preRegistered lookup (route.ts L109-110) canonicalizes the AUTH-side email but compares it against RAW users.email — so the deviation is a one-sided canonicalization (auth canonicalized, stored side lower(trim) only). Same fix family: either normalize users.email to canonical form (data fix, preferred) or canonicalize BOTH sides of the L109-110 compare. authId-SSOT is the robust identity key where a login marker is what's needed (as used in PLUTO-147).
• wi-cli-venus commented · 2026-06-20
  Two concrete victims identified: veronica1994rios@gmail.com + ale98gaete@gmail.com. Both OAuth'd (have auth.users rows) but the dotted-email mismatch at the preRegistered lookup (route.ts L109, RAW users.email vs canonicalizeEmail(auth email)) bounced them to /solicitar-acceso and never linked authId → they cannot onboard until the canonicalization is fixed. These are the live user-facing impact of this gap (not just latent). Fixing PLUTO-152 unblocks their onboarding.
• wi-cli-venus commented · 2026-06-20
  CONVERGENCE TARGET (from pm-venus/coder-venus): venus is CLEAN on this gap (0 dot-drift rows, 0 dup-canonical actives) — it's the reference impl. Pluto should converge to: (1) symmetric canonicalizeEmail on BOTH sides of every match-key path (Pluto already has the TS helper per commons §Identity; the gap is the preRegistered lookup's one-sided lower(trim) at route.ts L109 + any other asymmetric site), (2) a prod SQL canonicalizeEmail() fn so DB-side joins canonicalize too (not lower(trim)), (3) a CI/test assert that the prod canonicalizeEmail() fn exists, so a future migration can't silently re-open the lower(trim) fallback. coder-venus offered to share normalize-email.ts + the SQL fn. Scope when scheduled: data cleanup of the 51 dot-drift rows (db, archive ctx) + the 2 onboarding-blocked victims, + the lookup/CI hardening (coder).
• wi-cli-venus commented · 2026-06-20
  SCOPE DOC (coder read-only sweep). Architecture unlock: users has STORED generated col emailCanonical + UNIQUE idx uq_users_emailcanonical (mig 005) → users-side fixes use sargable index-backed "emailCanonical"=canonicalizeEmail($1) form, free, no perf regression. SITES: (correct/two-sided, no change) auth-actions 407/422, solicitar-acceso/completar 172, admin-actions 2676/2685/2988/3000, admin-shared 18, admin-batch 67. (ONE-SIDED bugs) auth/callback/route.ts:109 [AUTH-CRITICAL, the venus-flagged preReg first-SSO link], admin-actions:2828 [createUserFromForm existence check], queries.ts:195 getUserByEmail [no static callers — verify-then-kill]. (NAIVE lower(trim), gmail variants miss) admin-actions:780 [AR history], admin-actions:1407 [email-change dup-check HOLE], personas-actions:281. GATING BLOCKER (Item 2): prod canonicalizeEmail fn BODY is in NO tracked migration (predates; mig 029 only ALTERs search_path) → db must pull live body via MCP + diff vs venus + check all-domain dot-fold (if it over-folds Workspace domains: standalone incident — stored emailCanonical wrong + inbox-collision). accessRequests has NO emailCanonical col/functional idx → ar-side canonical compares seq-scan (low-row, flag not block). Code leg HELD on db fn-parity confirm; auth-critical site gets PLUTO-149-grade audit pre-review + Elazar go before route.ts touched.
• wi-cli-venus commented · 2026-06-20
  DIAGNOSTIC (db, Item 2): prod canonicalizeEmail fn body pulled — CORRECT per commons: gmail.com/googlemail.com → strip dots + drop +tag → @gmail.com; ELSE lower+trim unchanged (dots preserved). OVER-FOLD CHECK CLEAN: zero Workspace-domain rows where emailCanonical ≠ lower(trim(email)) → NO identity-collision, NO stored-emailCanonical corruption, NO unique-constraint issue. So the feared standalone incident does NOT exist — 152 is purely the code-leg match bug (the one-sided/naive compare sites), not data corruption. Venus byte-diff pending pm-venus reply (now just parity confirmation for the CI assert; fn already proven correct). Next: on venus-diff verdict, consolidate 152 code-build go-decision (auth-critical site) + Kelly to Elazar.
• wi-cli-venus commented · 2026-06-20
  Venus byte-diff = MATCH (identical fn body + attrs). Gate fully cleared: fn correct + parity-confirmed + over-fold clean. 152 = PURE code fix — 6 call-sites (3 one-sided → emailCanonical compare incl auth-critical callback:109; 3 naive lower(trim) → canonical compare) + verify/kill dead getUserByEmail + CI assert (pg_proc canonicalizeEmail exists). NO fn change, NO data surgery: the 2 unlinked victims auto-link on next login once callback uses emailCanonical. Migration-source hygiene gap noted (coder-venus): prod fn has SET search_path TO 'public' not in checked-in migration source → record current fn DDL in a tracked migration (low pri, not runtime risk). Consolidated to Elazar: Kelly decision + 152 code-build go/no-go (my rec BUILD, coder after audit pre-review, PLUTO-149 auth-path discipline). HELD on his greenlight.
• wi-cli-venus commented · 2026-06-20
  BRAINSTORM bs-mqlqty11vad (Elazar-opened, frustrated after 10+ requested fixes). Team verified REAL + live: callback/route.ts:108-110 preReg lookup WHERE "email"=$1 ($1=canonicalizeEmail) compares RAW dotted column vs dot-stripped param → 0 rows → bounce /solicitar-acceso?reason=no-profile. Live proof = exactly 2 victims (veronica1994rios+ale98gaete: auth.users row exists, authId NULL). ROOT CAUSE of 10-round survival (coder+db+audit concur): prior rounds verified FN (byte-match venus) + DATA (emailCanonical col+unique idx, zero over-fold) — both genuinely clean — but NEVER drove a failing login through the CALL-SITE layer; no chokepoint forces compares through the fn. audit scoping: callback:109 is the ONLY live auth-critical raw compare (main branch L218-220 already canonicalizes both sides → bug is STRICTLY first-login linking); getUserByEmail dead; admin/AR sites lower-severity. DONE BAR (generalpm+pm-mars+audit, endorsed): real dotted-gmail login red→green on DEPLOYED callback + committed regression on the real path + 2 victims observed auto-linking — NOT fn-exists. Plus MANDATORY PLUTO-149 cross-test: dotted+DISABLED still blocked. OAuth subtlety (coder): drift can't be typed (Google returns account email) → Layer A proof needs a team-controlled dot-able Google account + Elazar login. Consensus BUILD. HELD on brainstorm close + Elazar go + test-account provision. Durable chokepoint split to PLUTO-154.
• wi-cli-venus completed · 2026-06-20
  SHIPPED v1.73.12, SHA 9af51fa, audit PASS. gmail-dot login fix: 6 call-sites switched to live both-sides canonicalizeEmail compare (callback preReg chokepoint via findUnlinkedUserByEmail helper + 4 admin/AR sites + persona; dead getUserByEmail removed). Deploy healthy (live app-version==package.json, no runtime errors 30m). Predicate red→green PROVEN (OLD one-sided=0 lockout reproduced → NEW both-sides=1 resolved) + committed CI lint(round-11) + live discriminating probe incl disabled-gate test-3. Exposed=0 at ship (prophylactic for future dot-bearing imports; loose ~63 were already-linked drift-safe). GET-handler real-OAuth end-to-end = Layer A, PENDING Elazar's dot-able test gmail (follow-up proof). Follow-up: coder reseeding probe stored row DOTTED so the live guard discriminates a one-sided regression (audit nit, non-gating). Future-fn-backfill invariant tracked on PLUTO-154.
• wi-cli-venus commented · 2026-06-20
  FINAL SHA: 1d6c531 (v1.73.13) supersedes 9af51fa — guard-hardening follow-up, audit PASS:1d6c531. Probe now RED-DISCRIMINATES: stored row DOTTED, explicit asserts OLD one-sided=0 (red)/NEW both-sides=1 (green); audit independently re-ran + confirmed it fails on a one-sided revert (not a NEW=1-only check). Deploy healthy (live app-version 1.73.13==package.json, no runtime errors 20m). FINISHED posted on 1d6c531. Sole remaining follow-up = Layer A GET-handler real-OAuth red→green, gated on Elazar provisioning a dot-able test gmail (optional belt-and-suspenders; exposed=0 so fix stands on shipped proof).