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

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 · 2026-07-17
  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 · 2026-07-17
  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.
• wi-cli-venus assigned · 7w ago
  coder-pluto-cc
• wi-cli-venus note · 7w ago
  Step-2 call-site inventory, measured by coder-pluto-cc 2026-08-22 (code read only, no DB queries yet). SURFACE IS SMALLER THAN THE 2026-07-17 DESIGN NOTE ASSUMES. The note describes the active source as 'still mixing live-fn and emailCanonical reads'. Measured today there is exactly ONE stored-column reader against FIVE live-fn readers: STORED emailCanonical (the odd one out): - src/lib/support-reply-core.ts:799 WHERE "emailCanonical" = $1 AND "deletedAt" IS NULL LIVE fn, canonical-on-both-sides (the de-facto convention): - src/db/queries.ts:262 findUnlinkedUserByEmail (authId IS NULL chokepoint, PLUTO-152) - src/lib/personas-actions.ts:282 duplicate check on persona edit (excludes self by id) - src/lib/admin-batch.ts:322 import duplicate check - src/lib/admin-actions.ts:1248 + :2069 - src/lib/access-request-resolver.ts:91 emitted as a predicate fragment So step 3 ('replace direct active-source user-email-equality SQL') is nearly already done: there are no bare LOWER(email)= or email=$1 identity comparisons left in the active source. The remaining work is folding six callers into one helper and flipping the ONE stored-column reader, not a broad sweep. TWO THINGS THE DESIGN NOTE DOES NOT CALL OUT: 1. The single stored/live split is an ACTIVE disagreement, not just inconsistency. support-reply-core reads the frozen column while everything else recomputes. If any stored value is stale (the exact risk the queries.ts:245-250 comment was written to dodge), those two paths disagree about WHICH USER an email belongs to - support resolves a ticket to one account while auth resolves the same address to another. Needs a staleness scan before anyone decides the migration order: count of active users where emailCanonical IS DISTINCT FROM canonicalizeEmail(email). If that count is 0 the split is harmless today and step 1 can be a plain recompute; if it is non-zero we have a live identity defect and it outranks the rest of the campaign. 2. JS/DB parity is load-bearing at src/app/auth/callback/route.ts:248-249, which compares canonicalizeEmail(authEmail) to canonicalizeEmail(currentEmail) entirely in JS. Every other identity comparison happens in SQL. So a JS/DB divergence does not just produce a bad validation message (PLUTO-36's framing) - it makes the auth callback's identity decision differ from every SQL-side check. Concretely, JS canonicalizeEmail (src/lib/normalize-email.ts:20) splits on lastIndexOf('@'), so 'a@b@gmail.com' yields local 'a@b' and is silently accepted rather than rejected. Whether the DB fn agrees is unmeasured and is a step-1 question. Neither is blocking; both belong in step 1's proof rather than being discovered in step 4.
• wi-cli-venus statusChanged · 7w ago
  Step-2 inventory done (see note). Blocked on step-1 DB work: staleness scan + JS/DB parity corpus, db-pluto-cc lane, queued behind PLUTO-717.
• wi-cli-venus statusChanged · 5w ago
  Board hygiene per pmmaster 10:01: eight rows sat in inProgress at once, which reports nothing. Not actively worked this turn; PLUTO-718 is the single active row. Order: 718, then 688, then the rest.
• wi-cli-venus assigned · 4w ago
  unassigned
• wi-cli-venus statusChanged · 4w ago
  Unassigned and moved to backlog by pmmaster-evolutiva-cc 2026-09-12. The assigned agent did not exist: coder-venus-cc, db-venus-cc, coder-pluto-cc and db-pluto-cc are all absent from the hub roster and have no tmux pane, and venus and pluto currently have no staffed coder or db lane at all. A todo or inProgress row against an absent assignee reads as staffed-and-underway and is neither, so the status was a lie about this row rather than a claim about the work. The work itself is untouched and the priority is unchanged. Cause of the cluster, and it is a fleet-wide pattern rather than a bookkeeping slip: five auto-succession call sites plus resolvePm can leave a row pointing at an agent that has been deregistered, and nothing reconciles an assignee against the roster afterwards. Filed context: 178 rows across these four names, of which 23 carried a non-backlog status. Restaffing venus and pluto is Elazars decision, not a reassignment I can make.