Skip to content

Case-insensitive usernames with live availability checks in PostgreSQL

Melodius usernames: a citext column with a unique index, an availability check that distinguishes taken, yours, reserved and invalid, live feedback while typing, and the index as the final arbiter.

3 min read

Usernames in Melodius look trivial: one per account, unique, shown on your profile link. Getting the details right is what keeps people from ending up with a name that looks like someone else's.

Storage: citext and one index

The handle lives on the profile in a citext column with a unique index. citext compares case-insensitively, so Manas and manas are the same name to the database, while each user keeps the casing they typed for display. A username is claimed by saving it on the profile; the old one becomes free for anyone.

Five possible answers

typescript
type HandleAvailability =
  | { status: "available"; handle: string }
  | { status: "yours"; handle: string }      // already your username
  | { status: "invalid"; message: string }   // fails the format rules
  | { status: "reserved"; message: string }  // e.g. route names
  | { status: "taken"; message: string };

"Yours" matters more than it looks. Without it, someone editing their profile sees their own name reported as taken. "Reserved" keeps names like routes and system words away from users, so no profile can ever look like an official page.

Checking while typing

The Android app says whether a name is available as it is typed, and only lets you claim one that is. The same check backs the web, so both apps give the same answer for the same input, including the format message for an invalid name.

The index decides

An availability check is advisory: two people can see "available" for the same name at the same moment. The unique index is the real arbiter. When the second save hits the constraint, it is turned into the same "That username is taken." message the check would have shown, rather than an error page.

Illustrated avatar of Kumar Manas

Written by Kumar Manas

Full-stack developer in India. I build Melodius and Krypton.

Related projectMelodius: music streaming platform