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.