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`, `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

Assert the database locale is the one JS-parity was derived against, zero stragglers, and zero duplicate non-empty emails (hard gates).
Add users_email_normalized_check as a CHECK NOT VALID.
VALIDATE CONSTRAINT in a separate statement.
CREATE UNIQUE INDEX CONCURRENTLY users_email_key ON users (email) WHERE email IS NOT NULL AND email <> ''.
Post-deploy: assert the index exists and is indisvalid.
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

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

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:

-- 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.

-- 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), 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:

SET lock_timeout = '3s' — then ALTER TABLE … ADD CONSTRAINT users_email_normalized_check … NOT VALID
ALTER TABLE … VALIDATE CONSTRAINT users_email_normalized_check
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.

-- 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:

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:

# 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:

-- 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).

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):

Confirm users_email_key is valid.
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).
Attempt UPDATE setting email to a non-normalized value (e.g. 'Foo@Example.com') → expect CHECK / SQLSTATE 23514 (Prisma P2039 or P2010).
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:

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()
Leave out of scope: team_memberships.email joins (un-normalized on the other side — separate follow-up).
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 <name> 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

Tasks

Integrations

Email uniqueness check and index runbook

Runbook: Email uniqueness — CHECK + partial unique index (T7)

What this does

Why NOT VALID, then VALIDATE

Measured production facts

What the pin is actually for

Prerequisites

Ordering relative to T6

Commands

Step 0 — assert preconditions

Steps 1–3 — DDL

Build-failure loop (expected path)

Why the DROP comes first, always

Post-deploy assertions

Application belt-and-braces (SQLSTATE `23514`)

Pre-deploy forced-violation test (needs live DB)

Deferred step — switch `lower()` lookups to strict equality

Companion index — `users (lower(email))`

Prisma / schema notes

Failure / recovery summary

Out of scope

​Runbook: Email uniqueness — CHECK + partial unique index (T7)

​What this does

​Why NOT VALID, then VALIDATE

​Measured production facts

​What the pin is actually for

​Prerequisites

​Ordering relative to T6

​Commands

​Step 0 — assert preconditions

​Steps 1–3 — DDL

​Build-failure loop (expected path)

​Why the DROP comes first, always

​Post-deploy assertions

​Application belt-and-braces (SQLSTATE 23514)

​Pre-deploy forced-violation test (needs live DB)

​Deferred step — switch lower() lookups to strict equality

​Companion index — users (lower(email))

​Prisma / schema notes

​Failure / recovery summary

​Out of scope

Runbook: Email uniqueness — CHECK + partial unique index (T7)

What this does

Why NOT VALID, then VALIDATE

Measured production facts

What the pin is actually for

Prerequisites

Ordering relative to T6

Commands

Step 0 — assert preconditions

Steps 1–3 — DDL

Build-failure loop (expected path)

Why the DROP comes first, always

Post-deploy assertions

Application belt-and-braces (SQLSTATE `23514`)

Pre-deploy forced-violation test (needs live DB)

Deferred step — switch `lower()` lookups to strict equality

Companion index — `users (lower(email))`

Prisma / schema notes

Failure / recovery summary

Out of scope