> ## Documentation Index > Fetch the complete documentation index at: https://docs.traycer.ai/llms.txt > Use this file to discover all available pages before exploring further. # Email normalize dedupe migration runbook # Runbook: Email normalize + dedupe migration (T6) Production data migration for the account-linking / email-identity plan (Rev 4, rollout step 3). ## What this does 1. **Pre-flight audit** — duplicate groups (normalized email), non-normalized row count, `email = ''` population, and the `lower()` parity precondition. 2. **Full-table normalize** — `lower()` + trim of the **enumerated JS-whitespace class** used by `normalizeEmail` (not Postgres `btrim`/`[[:space:]]` alone — those miss NBSP/BOM). 3. **Purge the normalize-touched users' caches immediately** — before the post-normalize gate can halt the run. 4. **Collision re-detect** after normalize (case-variant twins). 5. **Ranked dedupe** — keep one winner per address; park losers. 6. **Hard manual gate** — refuse mechanical park while any loser holds an ACCEPTED team membership or a non-PENDING personal subscription. 7. **Redis purge** — full user-cache key set mirroring `removeUserFromCache` / `getUserCacheKeys` for winners, losers, and normalized-only rows: `authenticatedUser:`, `prismaUser:`, `prismaUser:`, `userSubscription:`, `collaboratorUserProfile:`, `authenticatedUserV2:`, plus `creditSummary::` for every customerId recovered from DB and/or cached blobs. (Partial prismaUser-only purge is wrong: auth caches embed email and are 24h read-through.) 8. **Farm-group export** — sizes `{18,24,43,48,49}` for trust/safety only (no enforcement). ## Rows this migration deliberately does not touch `users.email = ''` is **outside** T7's partial unique index (`WHERE email IS NOT NULL AND email <> ''`) — the product allows those rows, and production holds **629** of them (2026-07-26). They are therefore never grouped as a collision, never ranked, and never parked. The predicate is evaluated on the **normalized** value, because the normalize pass folds whitespace-only values (`" "`, NBSP, BOM…) to `''` and so creates more of them. Normalization still applies to those rows: `lower(btrim(' '))` is `''`, so a whitespace-only value would fail T7's CHECK if it were left unfolded. The dry run prints the population and exports it to `email-dedupe-empty-email-*.json`. If a park plan ever names one, the executor refuses **before writing anything** (see Pre-write preconditions). ## Parking semantics (do not overstate) Moving a loser's `email` → `former_email` preserves **recoverability of the address value only**. It does **not** preserve live team access. Team-join sites read `users.email` and degrade to no-access when it is NULL, regardless of `former_email`. ## Prerequisites | Step | Requirement | | ------ | ------------------------------------------------------------- | | Schema | T2 applied: `users.former_email` (and related) present | | Guards | T2 + T3 deployed so sign-in cannot re-duplicate mid-flight | | Client | T5 (cloud-ui 409 / step-up) deployed per rollout order | | Env | `DATABASE_URL` (auth/users Postgres), `REDIS_URL` for execute | 2026-07-25 baseline: **66** duplicated addresses across **392** rows (all GitHub-keyed). One known dual live-row address is a **hard gate** requiring an explicit ack. It is deliberately **not written down here or in the source**: the script pins it by SHA-256 and the dry run names the actual address in `email-dedupe-manual-gate-*.json`, alongside the exact command to copy. Below, `` stands for that value. ## Commands Dry-run is default. **Nothing writes without `--execute`.** ```bash theme={null} # Pre-flight counts only DATABASE_URL=... bun scripts/email-normalize-dedupe-migration.ts --audit-only # Full plan + JSON exports (read-only) DATABASE_URL=... bun scripts/email-normalize-dedupe-migration.ts # Apply (refuses if manual-gate queue non-empty, including unacked manual-decision email). # GATED_ADDRESS is named in the dry run's email-dedupe-manual-gate-*.json; # it is pinned by digest in the script and never stored in this repo. GATED_ADDRESS='...' DATABASE_URL=... REDIS_URL=... bun scripts/email-normalize-dedupe-migration.ts \ --execute \ --ack-manual-decision="$GATED_ADDRESS" ``` `--ack-manual-decision=` is **mandatory on every invocation that would park the known dual-row address**, including re-runs and T7's index-failure recovery loop — it is not sticky. Every refusal prints the exact command that satisfies the gates; copy that line rather than reconstructing one. An ack naming any other address is rejected at parse time (a typo used to be accepted silently and then hit the same exit 2 with no explanation). `--skip-cache-purge` is debug-only. Combined with `--execute` it prints a loud warning and writes `email-dedupe-skipped-cache-purge-*.json` with user ids and keys that still need purging — do not use in production. Sweep the fallout with `--purge-user-ids-from= --execute`. ### Output directory Exports contain **full lists of production email addresses**. `--output-dir` defaults to `./.email-dedupe-out`, which is git-ignored, and `email-dedupe-*.json` is ignored repo-wide as a second line of defence. Do not redirect them somewhere tracked. ### Timeouts `getPrismaPgConfig` defaults `query_timeout` to 30s — a web-request budget. This script issues an unbounded `SELECT` over every in-scope user joined to `subscriptions`, plus one unindexed full-table `UPDATE`, so it raises both `query_timeout` and `statement_timeout` to **30 minutes** for its own connection and prints the effective value on startup. An explicit `?query_timeout=` in `DATABASE_URL` still wins if you need something else. ### JSON exports (timestamped) | File | Contents | | ------------------------------------ | -------------------------------------------------------------------------------------------------- | | `email-dedupe-preflight-*.json` | Counts, duplicate groups, non-normalized sample, `lower()` parity precondition | | `email-dedupe-normalize-diff-*.json` | Every row the normalize pass would rewrite (also the resume source for a cache purge) | | `email-dedupe-winners-*.json` | Full winner list for pre-execution review | | `email-dedupe-park-plan-*.json` | Pre-normalize loser plan for review | | `email-dedupe-park-recovery-*.json` | Final post-normalize park plan, written before the first loser UPDATE; cache-purge recovery source | | `email-dedupe-manual-gate-*.json` | Support queue (must be empty to execute) + reason breakdown + re-run command | | `email-dedupe-empty-email-*.json` | `email = ''` rows — exempt from dedupe by design | | `email-dedupe-farm-groups-*.json` | Suspected trial farms (T\&S hand-off) | ## Ranking For each collision group (same normalized email, ≥2 rows): 1. `last_seen_at DESC NULLS LAST` 2. personal subscription `customer_id` **present** beats absent 3. `activated_at DESC NULLS LAST` 4. `created_at DESC` 5. `id ASC` (stable tie-break for idempotent re-runs) Winner keeps `email`. Each loser: `former_email = email`, `email = NULL`. ## Manual gate A **loser** is gated (and `--execute` refuses the whole park pass, exit 2) when: * there is an ACCEPTED `team_memberships` row whose email normalizes to the same address, or * the user has a personal subscription with `subscription_status <> PENDING`, or * the group is the known dual-live-row address **and** the operator did not pass `--ack-manual-decision="$GATED_ADDRESS"` (after support decides which row wins). The dry run names that address; the script never stores it. Support resolves membership/subscription gated rows **before** re-running `--execute`. For the known dual-row address, decide the winner offline, then re-run with the ack flag. Do not bypass the gate by editing the script. Normalize may still run; if the gate is non-empty **after** normalize, the script halts before parking. That halt is safe to re-run *because step 3 already purged the normalize-touched users' caches* — see Cache purge ordering. ### Pre-window feasibility query (run this before booking the window) The gate treats **any** non-`PENDING` subscription status as a block, and `FREE` is a `SubscriptionStatus`. If production eagerly creates `FREE` rows, essentially every loser is gated and `--execute` can never proceed. That is a question about the real distribution, not a bug in the gate — so check it first: ```sql theme={null} -- Subscription-status distribution across rows that would be dedupe candidates. SELECT COALESCE(s.subscription_status::text, '(none)') AS status, COUNT(*) AS rows FROM users u LEFT JOIN subscriptions s ON s.user_id = u.id WHERE u.email IS NOT NULL AND u.email <> '' AND lower(btrim(u.email, )) IN ( SELECT lower(btrim(email, )) FROM users WHERE email IS NOT NULL AND email <> '' GROUP BY 1 HAVING COUNT(*) > 1) GROUP BY 1 ORDER BY 2 DESC; ``` **Decision it unlocks:** if the gated population is dominated by `FREE`, decide with support *before* the window whether `FREE` is one of the statuses the plan meant to gate. Either narrow the gate deliberately (a plan change, not a script edit) or accept that the queue must be cleared by hand. Discovering this during the window means the window is wasted. The dry run reports the same thing from the plan side — `MANUAL-GATE BREAKDOWN` counts gated losers by reason and by `subscription_status`, and warns explicitly when every loser is gated. ## Pre-write preconditions Anything knowable from the plan alone is checked **before the first write**, and the executor refuses (exit 2) rather than discovering it afterwards: * no park plan may carry an empty `normalizedEmail` / `emailBefore` (an `email = ''` row must never be parked) * every park plan's stored email must normalize to its own group key * no plan may park the winner of its own group, and no loser may appear twice * JS↔Postgres `lower()` parity must hold (see below) A precondition that can only fail after the write is not a precondition. The post-run assertions verify the result; they are not the gate. ## Idempotency * Normalize only updates rows where `email <> lower(btrim(email, ))`. * Park only updates rows that still hold a matching non-null email. * Re-run after success: zero normalize updates, zero parks, assertions still pass. ## Cache purge ordering The purge runs **twice**, and the first one is load-bearing: 1. **Right after the normalize `UPDATE`** — for every normalize-touched user, before the post-normalize manual gate can halt the run. 2. **After parking** — for winners, parked losers and normalize-touched users. Without (1), a run that halted at the post-normalize gate left those users serving their pre-normalize address for the full 24h TTL, **and a re-run could not repair it**: the touched-user set comes from `normalizeDiff`, which is computed on the pre-normalize snapshot, so run 2 recomputes it against already-normalized rows and gets `[]`. If a run dies harder than that (crash, killed session) between the normalize `UPDATE` and its purge, the ids are still on disk — `email-dedupe-normalize-diff-*.json` is written *before* normalization starts: ```bash theme={null} REDIS_URL=... bun scripts/email-normalize-dedupe-migration.ts \ --purge-user-ids-from ./.email-dedupe-out/email-dedupe-normalize-diff-.json \ --execute ``` Without `--execute` that mode prints the plan and deletes nothing. It also accepts `email-dedupe-skipped-cache-purge-*.json` and `email-dedupe-cache-purge-*.json`. Parking has a separate recovery source because normalization can create a new collision that did not exist in the preflight park plan. Immediately before the first loser `UPDATE`, the executor writes `email-dedupe-park-recovery-*.json`, including the final post-normalize park set and every customer id it can recover for the credit-summary sweep. If the process dies during parking or before its Step 5 purge finishes, run: ```bash theme={null} REDIS_URL=... bun scripts/email-normalize-dedupe-migration.ts \ --purge-user-ids-from ./.email-dedupe-out/email-dedupe-park-recovery-.json \ --execute ``` This intentionally may purge a cache for a plan entry that had not yet been parked at the moment of the crash; that is safe and ensures no committed loser is missed. Do not rely on a normal re-run for this recovery: parked rows are excluded by the loader, so the normal touched-user set cannot rediscover them. ## `lower()` parity (forward-only concern — not a live defect) `normalizeEmail` is JS `.trim().toLowerCase()`; the SQL is `lower(btrim(email, ))`. The whitespace class is pinned and tested both directions. The case-folding half is not pinned: PG `lower()` is `lc_ctype`-dependent and strictly 1:1 per character, while JS `.toLowerCase()` is locale-invariant with full Unicode mappings. The straggler count cannot see a divergence — both of its sides use PG's own `lower()`. For **printable ASCII** the two are identical under every locale including `C`, so the dry run checks that precondition rather than doing per-row work: ```sql theme={null} SELECT count(*) FROM users WHERE email IS NOT NULL AND email <> '' AND email !~ '^[\x20-\x7E]+$'; ``` * **0** (production, 2026-07-26) → parity holds by construction; logged, no row-level scan. * **> 0** → the script loads exactly those rows with Postgres' own `lower(btrim(…))` and compares row-by-row against `normalizeEmail`. Any mismatch is printed and blocks `--execute` via the pre-write preconditions. **What would actually go wrong:** not an outage. A divergence means two rows for one mailbox both survive the unique index as distinct strings — JS folds a trailing `Σ` to final sigma (`ΟΔΟΣ@x.com` → `οδοσ@x.com`) where a 1:1 PG `lower()` does not. That is a uniqueness leak, not a 500. T7 step 0 records `datcollate`/`datctype` as the backstop. ## Post-run assertions (automatic on `--execute`) * Zero duplicate groups among rows inside T7's index predicate (`email IS NOT NULL AND email <> ''` — `''` rows are exempt and are *not* a duplicate group) * Zero non-normalized rows * Every planned parked loser has `email IS NULL` and `former_email` populated (a legitimately-empty planned value is not a complaint — and can no longer be planned) * **No cached blob still serves a pre-migration address** That last one is deliberately content-based, not "the key is absent". These are read-through caches: any `/api/user` hit repopulates `authenticatedUser:` with *correct* post-migration data while the sweep is still running, so an existence check fails a successful migration and gives the operator no way to tell that from a real one. Reappeared keys are reported as an expected note; the run only fails if a blob still contains a parked address or a pre-normalize value. ## Failure / recovery | Situation | Action | | ------------------------------------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------- | | Manual-gate refuse (exit 2) | Resolve queue; re-run dry-run; then `--execute` with the ack flag (the refusal prints the exact command) | | Pre-write precondition refuse (exit 2) | Nothing was written. Fix the plan input; do not bypass | | Normalize applied, park halted | Safe: normalize-touched caches were already purged. Clear the gate, re-run `--execute` (idempotent) | | Run died between normalize and purge | `--purge-user-ids-from= --execute` | | Run died during/after parking, before Step 5 completes | `--purge-user-ids-from= --execute`; do this before declaring a re-run healthy | | Index build (T7) fails on duplicates | **`DROP INDEX CONCURRENTLY IF EXISTS users_email_key` first** — see below — then re-run this script with the ack flag, then recreate the index | | Mis-ranked user | Support: un-park via `former_email` / reassign email carefully after T7 index exists | | Need to un-park | Set `email = former_email`, clear `former_email` only when uniqueness still holds | ### Why the drop must come first After a failed `CREATE UNIQUE INDEX CONCURRENTLY`, `users_email_key` remains an invalid index and reserves its name. `indisready` is **phase-dependent**: a duplicate encountered during the phase-2 build can leave it `false`, while a concurrent duplicate caught during phase-3 validation can leave it `true`. PostgreSQL documents update overhead for failed concurrent builds, so do not use either value to infer whether the leftover participates in writes or whether it is safe to keep. The recovery action is the same in both cases: drop the failed index before retrying so the name is available and no leftover state affects the retry. Order is unconditionally: `DROP INDEX CONCURRENTLY IF EXISTS users_email_key` → re-run T6 (`--execute --ack-manual-decision=…`) → re-assert T7 step 0 → recreate the index. The T7 runbook ([`docs/email-uniqueness-check-and-index-runbook.md`](./email-uniqueness-check-and-index-runbook.md)) is authoritative for the DDL half. ## JS whitespace class Must match `String.prototype.trim()` (see `scripts/email-normalize-dedupe-lib.ts`): `U+0009..U+000D`, `U+0020`, `U+00A0`, `U+1680`, `U+2000..U+200A`, `U+2028`, `U+2029`, `U+202F`, `U+205F`, `U+3000`, `U+FEFF`. Postgres `[[:space:]]` / default `btrim` alone are **not** sufficient. ## Out of scope * CHECK + unique index DDL (T7) — see [`docs/email-uniqueness-check-and-index-runbook.md`](./email-uniqueness-check-and-index-runbook.md). Run T7 only after this script's post-run assertions pass (zero stragglers, zero duplicates). * authn-v3 write-path changes (T2/T3) * Farm enforcement / trial clawback (separate T\&S work) * `team_memberships.email` normalization (noted follow-up)