---
title: Make users.email absent rather than empty, so two emailless registrations stop colliding
type: task
status: planned
description: >-
  users.email is NOT NULL DEFAULT '' and UNIQUE, so only one row may carry no email and the second
  invited registration without one collides today. One DDL line plus deleting a coalesce in
  auth.mjs.
base_uri: user:task/league/make-users-email-absent-rather-than-empty.md
created_at: '2026-09-17T18:16:27.955Z'
entity_id: 3b268fae-05ff-4f80-9498-e827d1649b6d
owner_identity_uri: █████████████████████████
priority: Medium
public_read: true
relations:
  - relates [[██████████████████████-████████-██████-██████-█████████████]]
tags:
  - user:tag/league-xo-football.md
updated_at: '2026-09-17T18:16:27.955Z'
visibility_analyzed_at: '2026-11-05T23:49:42.129Z'
---

## Overview

`users.email` is `NOT NULL DEFAULT ''` and UNIQUE, so exactly one row may carry no email. `api/routes/auth.mjs:667` writes `coalesce(cast(? as varchar), '')`, which turns an absent email into the empty string — so the SECOND invited registration without an email collides on the unique index today.

## Background

Measured 2026-09-17, carried out of [[██████████████████████-████████-██████-██████-█████████████]], whose scoping sweep found it. Re-measure before working it.

```sql
select count(*) total, count(*) filter (where email = '') empty_email from users;
```

Postgres treats NULLs as distinct in a unique index, so "no email" wants to be absence rather than an empty string. That makes the fix one DDL line plus deleting the `coalesce`.

## Plan

- [ ] **Make `email` nullable with no default, and stop coalescing on write**
  - Files: `api/routes/auth.mjs` (the `coalesce(cast(? as varchar), '')` at the invited-registration insert), an adhoc DDL file, `db/schema.postgres.sql`
  - Description: drop `NOT NULL`, drop the `DEFAULT ''`, and write `null` rather than `''`. Convert existing `''` rows to `null` in the same statement so the column has one representation for absence.
  - Purpose: the unique index is correct; the empty-string default is what makes it a one-row cap on emailless accounts.
  - Verify: register two invited accounts with no email and assert BOTH succeed — the control is today's schema, where the second must fail on `users_email_key`. Then assert a duplicate REAL email is still refused, since that is the constraint's actual job and a nullable column must not relax it. Sweep every reader for an `email === ''` or falsy-email comparison before shipping; a reader that tested for the empty string now sees null. DDL, `yarn export:schema` and code in ONE commit.