> ## 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 uniqueness check and index runbook # Runbook: Email uniqueness — CHECK + partial unique index (T7) Production ops for the account-linking / email-identity plan (Rev 5, data-model steps 3–4, rollout step 4). This step **follows** T6. It does not replace it. | Prior | Doc / tooling | | ---------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------- | | T6 normalize + dedupe | [`docs/email-normalize-dedupe-migration-runbook.md`](./email-normalize-dedupe-migration-runbook.md), `scripts/email-normalize-dedupe-migration.ts` | | This file (T7) | `packages/common/prisma/ops/users-email-check-and-unique-index.sql` | | Independent, run early | `packages/common/prisma/ops/users-email-lower-index.sql` — the `lower(email)` functional index; see "Companion index" below | ## What this does 1. Assert the database locale is the one JS-parity was derived against, **zero stragglers**, and **zero duplicate non-empty emails** (hard gates). 2. Add `users_email_normalized_check` as a `CHECK` **`NOT VALID`**. 3. `VALIDATE CONSTRAINT` in a separate statement. 4. `CREATE UNIQUE INDEX CONCURRENTLY users_email_key ON users (email) WHERE email IS NOT NULL AND email <> ''`. 5. Post-deploy: assert the index exists **and is `indisvalid`**. 6. **Deferred (not in this deploy):** switch interim `lower()` lookups to plain strict equality — only after this migration has actually executed in production (execution-run D4). ## Why NOT VALID, then VALIDATE Adding a CHECK that validates immediately takes an ACCESS EXCLUSIVE lock and full-scans under that lock. Adding **`NOT VALID`** avoids that; `VALIDATE CONSTRAINT` then scans under a weaker lock. **Trap (do not skip the straggler gate):** a `NOT VALID` CHECK still validates **every UPDATE of an existing row**. `updateUserLastSeen` runs on routine cached `/api/user` traffic. One non-normalized straggler therefore 500s normal sessions with SQLSTATE `23514` (surfaced by Prisma as `P2039` on the delegate path and `P2010` on the raw path — **not** `P2004`, despite the Prisma docs; probed against `@prisma/adapter-pg@7.9`). Zero stragglers is a hard precondition, asserted immediately before step 2 — not a nicety. `NOT VALID` also does **not** avoid taking the lock, only holding it. The `ALTER TABLE` still needs ACCESS EXCLUSIVE on `users`, and while it waits behind an in-flight long read it blocks every newer statement queued behind it. The `.sql` sets `lock_timeout = '3s'` around steps 1–2 for that reason and explicitly sets it to `0` before step 3 (a timeout would abort a `CONCURRENTLY` build partway). A `55P03` abort means nothing was applied — find the long query, retry when `users` is quiet. ## Measured production facts Three open questions from the cold review, all answered against production on **2026-07-26**. The `.sql` cites them; step 0 re-checks them at run time. | Query | Value | What it settles | | --------------------------------------------------------------------------------- | --------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `SELECT count(*) FROM users WHERE email = ''` | **629** | The partial predicate `WHERE email IS NOT NULL AND email <> ''` is load-bearing against real data — a plain full-column `@@unique([email])` would reject 628 of those rows. **Do not** weaken the predicate or fold the index into `schema.prisma`. Note the NULL half of the old justification was wrong: PostgreSQL treats NULLs as distinct by default, so parked dedupe losers never needed the predicate. Empty strings are the whole reason. | | `… WHERE email IS NOT NULL AND email <> '' AND email !~ '^[\x20-\x7E]+$'` | **0** | Every stored address is entirely printable ASCII, and on ASCII PG `lower()` and JS `.toLowerCase()` map `A-Z → a-z` identically under **every** locale. So `lower()` parity holds by construction for the current data. Cold-review finding 12 is **not a live defect**. | | `SELECT datcollate, datctype FROM pg_database WHERE datname = current_database()` | `C.UTF-8` / `C.UTF-8` | Recorded so the row above can be re-derived if the locale ever changes. Step 0 **pins** this value. | ### What the pin is actually for It is a **forward-only tripwire**, not a precondition for this run. With the table all-ASCII, existing rows are safe regardless of locale; the pin exists for the day a non-ASCII address first lands — which can only happen via a GitHub verified primary or a GoTrue JWT email, since `update-email` is gated by ajv's ASCII-only `format: "email"`. Two mappings then diverge regardless of `datctype`, because PG `lower()` is strictly 1:1 per character while JS `.toLowerCase()` applies the full, partly context-sensitive Unicode mappings: * **final sigma** — JS `"ΟΔΟΣ".toLowerCase()` ends in U+03C2 (`ς`); PG `lower()` gives U+03C3 (`σ`). * **U+0130 `İ`** — JS yields two code points, `i` + U+0307; PG yields one. **The consequence is not an outage.** Both divergent forms are already lowercase as far as PG is concerned, so `email = lower(btrim(email))` still holds and the CHECK never fires — no `23514`, no 500. The failure is quieter: `ΟΔΟΣ@x.com` and `οδοσ@x.com` normalize to two different strings under JS's final-sigma rule, so they occupy two rows for one real mailbox and the uniqueness design does not see them as the same address. Step 0's straggler count **cannot** find any of this — both of its sides use PG's own `lower()`, so it is satisfied by any value PG considers already-folded, including one JS would fold further. The cheap non-ASCII count is the guard, in both this file and T6's dry run, which escalates to a per-row JS↔SQL comparison only when that count comes back non-zero. ## Prerequisites | Step | Requirement | | --------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | T6 executed | Normalize + dedupe + cache purge completed in prod | | T6 verified | Zero non-normalized rows; zero duplicate non-null emails | | Auth code | T2/T3 (and T4 if live) deployed so writes stay normalized and reconcile skips email conflicts | | Client | T5 deployed (rollout order) | | Companion index | `users-email-lower-index.sql` applied (any time from rollout step 1 — see below). Not a hard gate for this file, but it is what keeps sign-in off a double seq scan during the T6 window. | | Env | Admin/**psql** access to the auth users Postgres; no transaction wrapping the whole script. psql specifically: the `.sql` relies on a `\set` meta-command (below). | There is **no local Postgres / Docker / container runtime** on typical agent machines. Do not claim a dry-run of this DDL without a real DB. Unit tests cover the SQLSTATE `23514` application arms only. ## Ordering relative to T6 ```text theme={null} users-email-lower-index.sql (independent, any time from rollout step 1) · T6 --audit-only / --execute → zero stragglers + zero duplicates ↓ T7 step 0 → locale pin holds; counts still zero ↓ T7 CHECK NOT VALID → VALIDATE → UNIQUE INDEX CONCURRENTLY ↓ T7 post-deploy assertions (index + indisvalid) ↓ Follow-up (separate change, AFTER prod T7): switch lower() → strict equality ``` If T6 was run days earlier, re-assert counts at T7 step 0 — do not trust an old export. ## Commands ```bash theme={null} psql "$DATABASE_URL" -v ON_ERROR_STOP=1 \ -f packages/common/prisma/ops/users-email-check-and-unique-index.sql ``` **`ON_ERROR_STOP` is the difference between a gate and a printed warning.** psql defaults it to **off**: without it, step 0's `RAISE EXCEPTION` is printed and psql runs the *next* statement anyway — adding the CHECK on top of the very stragglers step 0 just refused, which is the `updateUserLastSeen` outage this whole file exists to prevent, reached by following the file. The `.sql` now carries `\set ON_ERROR_STOP on` as its first executable line, so it is self-guarding. The `-v ON_ERROR_STOP=1` above is belt-and-braces; keep both. `\set` is a **psql meta-command**. Run this through anything else — a GUI client, a migration runner, `psql -c` per statement — and that line is ignored or rejected, so the file guarantees nothing on its own. In that case you own both halves yourself: abort on the first error, and do not open a transaction around the file (`CREATE INDEX CONCURRENTLY` cannot run inside one). ### Step 0 — assert preconditions The `.sql` runs all of these inside one enforced `DO` block. They are reproduced here for the case where you are inspecting production before booking the window. Three abort, two are reported: ```sql theme={null} -- ABORT (pin / forward-only tripwire): must be C.UTF-8 / C.UTF-8, the locale -- the lower()-parity argument was derived under. Existing rows are safe -- regardless — they are all ASCII — so this guards future non-ASCII addresses. SELECT datcollate, datctype FROM pg_database WHERE datname = current_database(); -- REPORTED: measured 0 on 2026-07-26. While it is 0, PG lower() and JS -- .toLowerCase() agree by construction and there is nothing to check. -- (octet_length <> char_length is "multi-byte", i.e. non-ASCII under UTF8.) SELECT count(*) FROM users WHERE email IS NOT NULL AND octet_length(email) <> char_length(email); -- REPORTED: production baseline 629 on 2026-07-26. Exempt from the unique index -- by predicate. A sharp drop means a dedupe pass consumed them. SELECT count(*) FROM users WHERE email = ''; ``` Then the two counts that can 500 production, which must both be 0: > **Do not rewrite the whitespace list using `E'...'` escapes.** PostgreSQL > recognises only `\b \f \n \r \t` (plus octal/hex/unicode) in E-strings and > takes any other backslashed character literally — so `E'\v'` is the **letter > `v`**, not U+000B. That puts `v` in the trim set, and every address starting > with `v` or ending in `.tv` then fails the CHECK. Since `updateUserLastSeen` > fires on routine `/api/user` traffic, those users would 500 on every request. > All 25 code points belong in the single Unicode-escape string literal — the > `U&` form — below. ```sql theme={null} -- Stragglers (must be 0) SELECT count(*) AS non_normalized FROM users WHERE email IS NOT NULL AND email <> lower( btrim( email, U&'\0009\000A\000B\000C\000D\0020\00A0\1680\2000\2001\2002\2003\2004\2005\2006\2007\2008\2009\200A\2028\2029\202F\205F\3000\FEFF' ) ); -- Duplicates (must be 0 rows returned) SELECT email, count(*) AS n FROM users WHERE email IS NOT NULL AND email <> '' GROUP BY email HAVING count(*) > 1; ``` If either is non-zero: **stop**. Re-run T6 ([runbook](./email-normalize-dedupe-migration-runbook.md)), then return here. Do not add the CHECK. Note the straggler query cannot detect a JS↔SQL case-folding divergence — both of its sides use PG's own `lower()`. See "What the pin is actually for" above. ### Steps 1–3 — DDL As in `packages/common/prisma/ops/users-email-check-and-unique-index.sql`: 1. `SET lock_timeout = '3s'` — then `ALTER TABLE … ADD CONSTRAINT users_email_normalized_check … NOT VALID` 2. `ALTER TABLE … VALIDATE CONSTRAINT users_email_normalized_check` 3. `SET lock_timeout = 0; SHOW lock_timeout;` — then `CREATE UNIQUE INDEX CONCURRENTLY users_email_key ON users (email) WHERE email IS NOT NULL AND email <> ''` Setting `lock_timeout` to `0` before step 3 is deliberate: `RESET` would restore any timeout configured at startup, for the database, or for the deploy role. The `SHOW` records the effective setting. A `CONCURRENTLY` build waits for existing transactions by design, and a non-zero `lock_timeout` could abort it partway — leaving the invalid index described below. ## Build-failure loop (expected path) Index or VALIDATE failures are **normal** if any row slipped past T6 or was written mid-flight. Treat the loop below as the default recovery, not an incident exception. | Failure | What Postgres left behind | Recovery | | --------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `ALTER TABLE` aborts with `55P03 lock_timeout` | Nothing — the transaction rolled back | Find the long-running query holding a lock on `users` (`pg_stat_activity` / `pg_locks`), wait it out, re-run the file. Do not raise `lock_timeout` to force it through. | | `VALIDATE CONSTRAINT` finds stragglers | CHECK exists but is not validated (`convalidated = false`) | Drop CHECK → re-run T6 → re-assert step 0 → re-add NOT VALID → VALIDATE → index | | `CREATE UNIQUE INDEX CONCURRENTLY` finds duplicates | **Invalid** index (`pg_index.indisvalid = false`) still named `users_email_key`. `indisready` can be `false` when the duplicate is found during phase 2, or `true` after the index becomes write-ready. | `DROP INDEX CONCURRENTLY IF EXISTS users_email_key` **first** — before anything else, including before re-running T6 → then re-run T6 → re-assert → recreate index | | Concurrent writes re-introduce a bad row mid-build | Same as above | Same loop; confirm T2/T3 write-path normalization is live | ### Why the DROP comes first, always A failed `CREATE UNIQUE INDEX CONCURRENTLY` leaves the index with: * `indisvalid = false` — the **planner** ignores it. It answers no queries. * `indisready` is phase-dependent: it can be `false` when a duplicate aborts the phase-2 build before the index becomes ready. If it is `true`, every **INSERT and UPDATE** still maintains it and therefore enforces its uniqueness. Either reading has the same recovery: **drop the incomplete index before fixing data.** `indisready = true` is an enforcing write hazard; either state blocks a replacement with the same name, and no recovery step benefits from keeping it. Drop the index, *then* re-run T6. > The T6 runbook must agree with this ordering. If it says "re-run this script, then retry the concurrent unique index" without a `DROP INDEX CONCURRENTLY` first, that instruction is wrong and this section wins. ```sql theme={null} -- Drop invalid / incomplete objects before retrying. In this order. DROP INDEX CONCURRENTLY IF EXISTS users_email_key; ALTER TABLE users DROP CONSTRAINT IF EXISTS users_email_normalized_check; ``` Confirm the drop landed before touching data: ```sql theme={null} SELECT i.relname, ix.indisvalid, ix.indisready FROM pg_class t JOIN pg_index ix ON ix.indrelid = t.oid JOIN pg_class i ON i.oid = ix.indexrelid WHERE t.relname = 'users' AND i.relname = 'users_email_key'; -- expect: zero rows ``` Then: ```bash theme={null} # Re-dedupe / re-normalize stragglers (T6). # GATED_ADDRESS is named in the T6 manual-gate export # (email-dedupe-manual-gate-*.json) — it is not committed to this repo. GATED_ADDRESS='...' DATABASE_URL=... REDIS_URL=... bun scripts/email-normalize-dedupe-migration.ts \ --execute \ --ack-manual-decision="$GATED_ADDRESS" \ --output-dir ./email-dedupe-out # Re-assert step 0, then re-run T7 DDL ``` Repeat until VALIDATE and the concurrent index both succeed. ## Post-deploy assertions All of these must pass before calling T7 done: ```sql theme={null} -- 1. CHECK exists and is validated SELECT conname, convalidated FROM pg_constraint WHERE conname = 'users_email_normalized_check'; -- expect: one row, convalidated = true -- 2. Index exists AND is valid (indisvalid is the CONCURRENTLY footgun) SELECT i.relname AS index_name, ix.indisvalid, ix.indisready, ix.indisunique FROM pg_class t JOIN pg_index ix ON ix.indrelid = t.oid JOIN pg_class i ON i.oid = ix.indexrelid WHERE t.relname = 'users' AND i.relname = 'users_email_key'; -- expect: one row, indisvalid = true, indisready = true, indisunique = true -- 3. Still zero stragglers / duplicates (same queries as step 0) ``` If `users_email_key` exists with `indisvalid = false`, T7 is **not** done. Do not use `indisready` to choose a recovery: `false` is possible when a duplicate aborts phase 2 before the index is ready, and `true` means it is still enforced on writes even though the planner cannot use it. In either case the object blocks a later `CREATE UNIQUE INDEX CONCURRENTLY` with the same name. Drop and rebuild before data fixes (see [Why the DROP comes first](#why-the-drop-comes-first-always)). ## Application belt-and-braces (SQLSTATE `23514`) After the CHECK is live, a non-normalized write fails with Postgres `23514`. Detection keys on that SQLSTATE, not on a `P…` code: this stack emits `P2039` (delegate) or `P2010` (raw), never `P2004`. authn-v3 handles this on user-update paths: | Path | Behavior | | -------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------- | | Sign-in reconcile (`reconcileUserWithEmailConflictSkip`) | Catch → `Logger.error` (Sentry in prod) → **skip email field**, still land non-email writes. **Must never 500 the sign-in.** | | `POST /user/update-email` | Catch → `Logger.error` (Sentry) → **400**, no silent success | Stable log prefix for grepping: `EMAIL_CHECK_VIOLATION` (see `authn-v3` unique-constraint / reconcile modules). Expected steady state after T6+T7: **0**. ### Pre-deploy forced-violation test (needs live DB) Not runnable without Postgres. Before enabling traffic confidence, on a **staging** clone (or a one-row throwaway): 1. Confirm `users_email_key` is valid. 2. Attempt `INSERT`/`UPDATE` of a second row with the same non-null email → expect unique violation; record Prisma `meta.target` for T1 probe (email index literal). 3. Attempt `UPDATE` setting `email` to a non-normalized value (e.g. `'Foo@Example.com'`) → expect CHECK / SQLSTATE `23514` (Prisma `P2039` or `P2010`). 4. Attempt a non-email `UPDATE` (e.g. `last_seen_at` only) on a **normalized** row → must succeed (CHECK not tripped). ## Deferred step — switch `lower()` lookups to strict equality **Not part of the T7 code deploy.** Execution-run **D4**: shipping strict equality before production data is normalized forks accounts and re-breaks the incident cohort. Only after **this runbook's post-deploy assertions pass in production**: 1. Replace interim `lower(email) = $normalized` matchers with plain equality on: * sign-in resolution * `update-email` duplicate guard * `findExistingUser` insensitive email fallback * dedupe tooling lookups that still use `lower()` 2. Leave out of scope: `team_memberships.email` joins (un-normalized on the other side — separate follow-up). 3. Ship as its own PR/deploy after T7 ops, with a greppable checklist that prod `non_normalized = 0` still holds. Until that follow-up lands, T3's interim `lower()` lookups remain correct and required. ## Companion index — `users (lower(email))` `packages/common/prisma/ops/users-email-lower-index.sql`. Separate file, separate step, **no dependency on this runbook's gates** — run it any time from rollout step 1 onward, and earlier is better. Every interim email lookup in authn-v3 matches `lower(email) = $1`. `users_email_key` is a plain btree on `email`, so it cannot serve those queries — and neither could the `mode: "insensitive"` form they replaced, so this is not a regression. What changed is the *count*: the reconcile pre-check (`isEmailClaimedByAnotherUser`) adds a **second** full scan of `users` to every sign-in that refreshes an email, on top of resolution's own lookup. Why it is not folded into the T7 `.sql`: | Reason | Detail | | ------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Ordering | T7 aborts unless stragglers and duplicates are already zero. The functional index is a pure read optimisation, correct at any data-quality state — and the period it is needed *most* is the T6 normalize/dedupe window, i.e. **before** T7 may run at all. Behind T7's gate it would arrive after the pressure. | | Independence | It has nothing to do with the CHECK or with uniqueness, and nothing in T7's build-failure loop should ever drop or rebuild it. | | Lifetime | Not transitional. Execution-run **D4** defers the switch to plain equality indefinitely, so `lower(email) = $1` is the live shape for the foreseeable future. | It is non-unique and non-partial on purpose (rationale in the file header). Because it is non-unique, a failed `CONCURRENTLY` build leaves an invalid index that enforces nothing — unlike `users_email_key`, it is not a write hazard, only unusable. Drop and retry: `DROP INDEX CONCURRENTLY IF EXISTS users_email_lower_idx;` Dropping it later is a deliberate separate step: verify no `lower(email)` matcher remains in authn-v3 first. ## Prisma / schema notes * The partial unique index is **not** declared as `@@unique` on `User` (inexpressible predicate — and per the measured 629 `email = ''` rows, the predicate is load-bearing, not stylistic). * `prisma migrate diff` may propose dropping `users_email_key`. **Reject that drop.** The schema comment on `User.email` documents this. The same applies to `users_email_lower_idx` — expression indexes are equally inexpressible in the Prisma schema. * App code already treats `users_email_key` as a known `P2002` target (`USER_EMAIL_UNIQUE_TARGETS`). * The `user_identities` migration is split in two — `20260725190000_user_identities_and_email_source` (schema, `lock_timeout = '3s'`) then `20260725190100_backfill_user_identities` (backfill) — so the ACCESS EXCLUSIVE lock from `ALTER TABLE "users"` is not held across a full-table backfill. Both apply in one `prisma migrate deploy`; if the second fails, do not start serving on the half-applied deploy (`user_identities` exists but is empty). * `.github/workflows/db_migrations.yaml` auto-runs `prisma migrate deploy` against **Dev / Local / Staging** on any push to `development` touching `packages/common/prisma/migrations/**`. A `55P03 lock_timeout` there rolls back cleanly but records a *failed* migration that blocks later deploys — clear it with `prisma migrate resolve --rolled-back ` and re-deploy. Recovery details are in each migration's header. ## Failure / recovery summary | Situation | Action | | ---------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Step 0 straggler / duplicate count non-zero | Re-run T6; do not DDL | | Step 0 aborts on `datcollate`/`datctype` | This is not the locale the parity argument was derived under. Re-derive it (start with the non-ASCII count; escalate to T6's per-row JS↔SQL comparison only if it is non-zero), then update the pin in the `.sql` — do not widen it to make the abort go away | | Step 0 reports non-ASCII emails > 0 | Not a blocker, and **not** a 500 risk — the CHECK cannot fire on it. Run T6's per-row JS↔SQL comparison to see whether any pair of rows is one mailbox split in two by JS's final-sigma / U+0130 rules | | Step 0 reports `email = ''` well under 629 | Not a blocker, but stop and find out what consumed those rows before adding a unique index over the result | | `55P03 lock_timeout` on `ALTER TABLE` | Nothing applied. Clear the long-running query on `users`, retry | | VALIDATE fails | Drop CHECK → T6 → retry (build-failure loop) | | CONCURRENTLY fails / `indisvalid = false` | `DROP INDEX CONCURRENTLY` **first** (it still enforces on writes) → T6 → retry | | `EMAIL_CHECK_VIOLATION` alerts non-zero after deploy | Find non-normalized writes or stragglers; fix data; do not ignore | | Need to roll back DDL | Drop index concurrently, drop CHECK; app remains safe on `lower()` lookups. Leave `users_email_lower_idx` alone — it is independent | ## Out of scope * Switching `lower()` → equality (deferred step above) * Monitoring dashboards * `team_memberships.email` normalization * Farm / trial clawback