# When does a case-insensitive uniqueness constraint hold on one host and not another?

**Date of Record:** 2026 November 1

**Scope:** Applies to uniqueness enforced on user-facing identifiers (email, username, path) where case-insensitivity is assumed but the folding rule is supplied by the host filesystem, database collation, or application code. Excludes fields explicitly declared binary or case-sensitive.

## Core Distinction

**Predicate.** A case-insensitive uniqueness constraint is portable if and only if the folding function is identical at every point that creates, checks, or indexes the key. The constraint holds if fold_application(x) == fold_application(y) implies x == y at exactly one store, and that store is the sole authority.

**Failure.** The application folds on write using one rule; the database enforces uniqueness using another; a second service checks using a third. Two identifiers that differ only in a character the rules treat differently can be created on one host and rejected on another.

**Asymmetry.** The constraint appears to hold because the primary store enforces something. Divergence only appears when a second store, a backup restore, or a host migration applies a different rule.

## What Was Observed

**Signature.** Duplicate identifiers present in a replicated table that a single-host check says cannot exist. The duplicates differ in characters whose folding is host-dependent (Turkish dotted I, German sharp S, Greek final sigma, full-width forms). A restore onto a different platform fails the unique index.

**Trace.** Application folds with Python lower(). PostgreSQL enforces with a locale-aware collation. A username containing the Turkish uppercase dotted I folds to a plain i in one and to a dotted i in the other. Write succeeds via the application path, replication to a host with the other collation fails the unique index, and the row is skipped or the replica halts depending on error policy.

**Why monitoring stays green.** The primary accepts the write. The unique index is present and correct for its own locale. The divergence lives in the space between two correct configurations.

## Condition of Validity

Portable case-insensitive uniqueness requires all of:

1. **Single fold.** Exactly one folding function is applied, and it is applied at the storage boundary rather than in application code.
2. **Pinned locale.** The database collation is pinned explicitly and does not inherit the host locale.
3. **Portable fold.** The fold function is locale-independent (full Unicode case folding, not lower()), so it does not vary by host language settings.

Condition 3 is the one most often violated, because lower() is locale-sensitive in several languages including Python and Java.

## Decision Boundary

- One fold, pinned locale, portable function: constraint is portable.
- Application fold plus database collation: two rules, divergence is possible. Normalize before write and store the folded form as the indexed column.
- lower() used anywhere in the path: not portable. Replace with full case folding.
- Case-insensitive key restored across platforms without a pinned collation: restore may fail or may silently accept duplicates depending on error handling. Treat as unsafe.

## Operational Effect

The defect is dormant until a platform change, so it survives years of operation and surfaces during a migration when the cost is highest. Remediation is to compute the folded key once, store it in a dedicated column, and enforce uniqueness on that column. The trap is to set the database collation to case-insensitive and call it done. That moves the rule to the store but leaves the application computing a different folded form for lookups, so writes and reads disagree.

```python
from dataclasses import dataclass


class CaseFoldingError(RuntimeError):
    """Raised when case-insensitive uniqueness depends on host- or locale-varying folding."""


@dataclass(frozen=True)
class CaseFoldGate:
    fold_function: str  # "full-casefold", "lower", "db-collation"
    locale_pinned: bool
    single_authority: bool

    def verify(self) -> bool:
        if self.fold_function == "lower":
            raise CaseFoldingError("lower() is locale-sensitive and not portable")
        if not self.locale_pinned:
            raise CaseFoldingError("collation not pinned: inherits host locale")
        if not self.single_authority:
            raise CaseFoldingError("multiple folding authorities in the write path")
        return True


def dispatch_key_strategy(gate: CaseFoldGate) -> str:
    try:
        gate.verify()
    except CaseFoldingError:
        return "folded-column"
    return "indexed-fold"
```