# `PhoenixKit.Migrations.Postgres.V161`
[🔗](https://github.com/BeamLabEU/phoenix_kit/blob/v2.13.9/lib/phoenix_kit/migrations/postgres/v161.ex#L1)

V161: Case-insensitive `phoenix_kit_users.username` via `citext`.

V08 created `username` as `:string` (`VARCHAR(255)`, `v08.ex:38`) with a
partial unique index on non-null values
(`phoenix_kit_users_username_uidx`). Postgres resolves comparison
semantics from the *column's* type, not from how the Ecto schema declares
the field — so despite the schema, every lookup (`Auth.get_user_by_username/1`
→ bare `Repo.get_by(User, username: ...)`, the `unsafe_validate_unique`
changeset check, and the unique index itself) has always been exact-match.
Two accounts differing only by case — `alice` / `Alice` — could both
register, and `get_user_by_username("ALICE")` would find neither.

`email` already gets this for free — it's been `citext` since V01
(`v01.ex:49`) and is explicitly out of scope here (already correct,
verified live). This migration brings `username` to the same shape: one
`ALTER COLUMN ... TYPE citext` fixes writes, reads, and the uniqueness
constraint at once, and makes it structurally impossible for future code
to reintroduce case-sensitive comparison by accident — the same fix
already applied to the CRM party email columns in V151.

## Pre-check

Before any DDL, `up/1` scans for existing rows that would collide once
comparison becomes case-insensitive (`GROUP BY lower(username) HAVING
count(*) > 1`, mirroring V106's down-step pre-check). `username` is
nullable, and `GROUP BY` folds every `NULL` into one group — the scan
filters `WHERE username IS NOT NULL` so two username-less accounts don't
register as a false collision and abort the upgrade. If a real collision
is found, `up/1` raises and names the offending value before touching the
schema; operators resolve it (rename or merge) and re-run.

The pre-check is **best-effort, not a lock**. `@disable_ddl_transaction`
means it runs as its own statement, separate from the `ALTER`, so on a
live system two concurrent registrations of `alice` and `Alice` can still
slip into that window — the old varchar index is case-sensitive and
admits both. The real guard is the index rebuild inside the `ALTER`,
which then fails with Postgres' generic `duplicate key value violates
unique constraint` instead of the readable message above. Nothing is
corrupted and the column is left unconverted; a re-run catches the pair
through the pre-check. Closing that window would mean holding pre-check
and `ALTER` in one transaction — exactly the long `ACCESS EXCLUSIVE` that
`@disable_ddl_transaction` exists to avoid — so the gap is deliberate.

## Cost: catalog-only, no table rewrite

`varchar` → `citext` is binary-coercible in Postgres — confirmed against
`pg_cast` rather than assumed:

    SELECT castmethod, castfunc, castcontext FROM pg_cast
    WHERE castsource = 'varchar'::regtype AND casttarget = 'citext'::regtype;
    -- castmethod = 'b' (binary coercion), castfunc = 0, castcontext = 'a'

`castmethod = 'b'` means Postgres reinterprets the on-disk bytes as-is —
no per-row validation pass, no **table** rewrite. The `username` B-tree
index is a different story: verified live against a throwaway table that
`ALTER COLUMN ... TYPE citext` DOES rebuild any index on that column
(`relfilenode` changes) — expected and necessary, since citext orders and
compares values differently from `varchar` (`lower()`-based, not raw
byte order), and it's exactly what makes
`phoenix_kit_users_username_uidx` start rejecting case-variant
duplicates right after the `ALTER` (confirmed on the same probe:
inserting `'ALICE'` against an existing `'alice'` row raised
`unique_violation` immediately post-conversion). So the honest cost
statement is: no table heap rewrite, but the index rebuild's cost scales
with the number of non-null `username` values — cheap in absolute terms,
not a zero-cost catalog flip. The pre-check adds a second pass of the
same order: `GROUP BY lower(username)` has no index to use, so it is a
full scan of every non-null `username`. It takes no locks and runs before
any DDL, so it lengthens the migration without lengthening the outage. The `ALTER TABLE` holds an `ACCESS
EXCLUSIVE` lock for that (brief) duration, which is why this migration
keeps `@disable_ddl_transaction true` (core convention — `v154.ex:30`,
`v159.ex:36`) so the lock is never held for the length of a whole
migration transaction.

The `ALTER COLUMN` itself is idempotent, guarded by a `udt_name` check on
`information_schema.columns` (same shape as V151's email conversion,
`v151.ex:97-113`), and `prefix` is honored throughout.

# `down`

Reverts `username` to `VARCHAR(255)` (its V08 shape).

No pre-check needed on the way down: while the column is still `citext`,
the unique index already refuses any new case-variant duplicate from
appearing, so there is nothing for a rollback to collide with.

# `up`

---

*Consult [api-reference.md](api-reference.md) for complete listing*