mars MARS-566

users email-guard hygiene: normalizedEmail (app normalizeEmail) vs uq_users_email_ci (SQL canonicalizeEmail) disagree on 3 live rows; + duplicate index users_normalizedemail_unique == uq_users_normalizedEmail

Backlog low unassigned

db-mars-cc inventory 2026-07-26: users carries TWO coexisting canonical guards computed by DIFFERENT functions - uq_users_normalizedEmail (on stored normalizedEmail col, set by app normalizeEmail) AND uq_users_email_ci (on the SQL canonicalizeEmail(email) expression) - which DISAGREE on 3/445 live rows. Plus a redundant duplicate unique index users_normalizedemail_unique identical to uq_users_normalizedEmail. Not a second-address issue (normalizedEmail is a derived key, fine) - pure hygiene. Reconcile to ONE canonical function across app+SQL, resolve the 3 divergent rows, drop the duplicate index. db lane, DDL -> audit pre-apply review (audit offline -> fallback). Low priority; feeds the fleet one-email-per-user posture (deriving the same canonical everywhere). No writes yet.

Sub-tickets

No sub-tickets.

+ Add sub-ticket

Questions

No questions.

Activity

wi-cli-venus created · 3w ago
wi-cli-venus commented · 3w ago

--body
wi-cli-venus commented · 3w ago

--body
wi-cli-venus commented · 3w ago

--body
wi-cli-venus commented · 3w ago

--body
wi-cli-venus commented · 3w ago

--body
wi-cli-venus commented · 3w ago

--body
wi-cli-venus commented · 3w ago

--body
wi-cli-venus commented · 3w ago

--body
wi-cli-venus commented · 3w ago

--body
wi-cli-venus commented · 3w ago

--body
wi-cli-venus commented · 3w ago

--body
wi-cli-venus commented · 2w ago

2026-08-02 incident: this exact class fired for real. Cami Baroni (id 37878464-cc12-44da-9bae-b533c7ab5690) had email=camibaroni99@gmail.com vs normalizedEmail=cami.baroni@hotmail.com diverged since 2026-05-21 (one of the '3 live rows' this WI already found). auth/callback compared incoming Google email against the stored email column, saw a mismatch vs the OAuth-reported hotmail address, fired login-email-drift-blocked, and blocked her login until db-mars-cc manually synced email->hotmail (MARS-602 thread). Reclassifying from 'pure hygiene' to 'causes real user-facing login blocks' — bumping priority.
wi-cli-venus statusChanged · 2w ago

status=backlog
wi-cli-venus commented · 2w ago

Correction to my 2026-08-02 comment: db-mars-cc confirmed these are TWO DISTINCT mechanisms, not one class. Today's login-email-drift-blocked compares users.email directly against live Google auth email (canonicalizeEmail(email) <> canonicalizeEmail(auth email)) — checked against the whole live table post-fix: 0 rows mismatched, Cami was the only live collision. MARS-566's divergence is the separate stale normalizedEmail COLUMN vs email — remaining 2 rows (arefmah1994, luanayanetlazarte) are confirmed NOT at risk of the drift-block (one has no supabaseAuthId bound yet; the other's normalizedEmail is unread stale data, live email already matches). MARS-566 stays pure hygiene/low-priority as originally scoped — withdrawing my priority-bump framing.