Runbook: Email normalize + dedupe migration (T6)

Production data migration for the account-linking / email-identity plan (Rev 4, rollout step 3).

What this does

Pre-flight audit — duplicate groups (normalized email), non-normalized row count, email = '' population, and the lower() parity precondition.
Full-table normalize — lower() + trim of the enumerated JS-whitespace class used by normalizeEmail (not Postgres btrim/[[:space:]] alone — those miss NBSP/BOM).
Purge the normalize-touched users’ caches immediately — before the post-normalize gate can halt the run.
Collision re-detect after normalize (case-variant twins).
Ranked dedupe — keep one winner per address; park losers.
Hard manual gate — refuse mechanical park while any loser holds an ACCEPTED team membership or a non-PENDING personal subscription.
Redis purge — full user-cache key set mirroring removeUserFromCache / getUserCacheKeys for winners, losers, and normalized-only rows: authenticatedUser:<id>, prismaUser:<id>, prismaUser:<providerId>, userSubscription:<id>, collaboratorUserProfile:<id>, authenticatedUserV2:<id>, plus creditSummary:<id>:<customerId> 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.)
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, <gated-address> stands for that value.

Commands

Dry-run is default. Nothing writes without --execute.

# 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=<that file> --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=<ms> 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):

last_seen_at DESC NULLS LAST
personal subscription customer_id present beats absent
activated_at DESC NULLS LAST
created_at DESC
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:

-- 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, <js-ws>)) IN (
        SELECT lower(btrim(email, <js-ws>))
        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, <js-ws>)).
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:

Right after the normalize UPDATE — for every normalize-touched user, before the post-normalize manual gate can halt the run.
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:

REDIS_URL=... bun scripts/email-normalize-dedupe-migration.ts \
  --purge-user-ids-from ./.email-dedupe-out/email-dedupe-normalize-diff-<ts>.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:

REDIS_URL=... bun scripts/email-normalize-dedupe-migration.ts \
  --purge-user-ids-from ./.email-dedupe-out/email-dedupe-park-recovery-<ts>.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, <js-ws>)). 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:

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:<id> 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=<normalize-diff export> --execute`
Run died during/after parking, before Step 5 completes	`--purge-user-ids-from=<park-recovery export> --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) 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. 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)

Tasks

Integrations

Email normalize dedupe migration runbook

Runbook: Email normalize + dedupe migration (T6)

What this does

Rows this migration deliberately does not touch

Parking semantics (do not overstate)

Prerequisites

Commands

Output directory

Timeouts

JSON exports (timestamped)

Ranking

Manual gate

Pre-window feasibility query (run this before booking the window)

Pre-write preconditions

Idempotency

Cache purge ordering

`lower()` parity (forward-only concern — not a live defect)

Post-run assertions (automatic on `--execute`)

Failure / recovery

Why the drop must come first

JS whitespace class

Out of scope

​Runbook: Email normalize + dedupe migration (T6)

​What this does

​Rows this migration deliberately does not touch

​Parking semantics (do not overstate)

​Prerequisites

​Commands

​Output directory

​Timeouts

​JSON exports (timestamped)

​Ranking

​Manual gate

​Pre-window feasibility query (run this before booking the window)

​Pre-write preconditions

​Idempotency

​Cache purge ordering

​lower() parity (forward-only concern — not a live defect)

​Post-run assertions (automatic on --execute)

​Failure / recovery

​Why the drop must come first

​JS whitespace class

​Out of scope

Runbook: Email normalize + dedupe migration (T6)

What this does

Rows this migration deliberately does not touch

Parking semantics (do not overstate)

Prerequisites

Commands

Output directory

Timeouts

JSON exports (timestamped)

Ranking

Manual gate

Pre-window feasibility query (run this before booking the window)

Pre-write preconditions

Idempotency

Cache purge ordering

`lower()` parity (forward-only concern — not a live defect)

Post-run assertions (automatic on `--execute`)

Failure / recovery

Why the drop must come first

JS whitespace class

Out of scope