Data validation: Consistency and migration

Treats database unique indexes as the source of truth for natural keys, returns a stable conflict code, documents Unicode and case folding, and refuses to reveal whether a private identifier already exists.

Package status: reference context ready for human review. The contract and test scenarios are complete, but no claim is made that an adopting implementation has passed them.

Decision

Create-or-update APIs rely on a real unique index for the natural key, not a check-then-insert race. Public handles such as tenant-scoped slugs return a stable unique_constraint_conflict with HTTP 409. Normalization is explicit: Unicode NFC, trim, and case-fold to a slug pattern before insert. Private identifiers such as email must not be enumerable: unauthenticated callers receive a generic success-shaped identity_not_available rather than 409. Idempotent retries with the same key are not conflicts. Password-reset token abuse is out of scope except as a related control.

Scope

Create and update endpoints whose natural keys must be unique per tenant.
Normalization rules, conflict codes, and anti-enumeration for private fields.
Interaction with idempotency keys on POST.

Outside this block

Full authentication flows and reset-token hashing.
Distributed unique allocation across shards beyond a single database unique index.
Search-index uniqueness.

Contract

Uniqueness is enforced by workspace_tenant_slug_uidx (or the mapped index); application pre-checks cannot be the only guard.
Public handle conflicts return unique_constraint_conflict and 409 with the field name slug.
Stored slugs match slug_pattern after NFC, trim, and case-fold; North-Star and NORTH-STAR collide.
Private email conflicts do not return 409 to unauthenticated clients; they return identity_not_available with 200.
Replay of the same Idempotency-Key and body returns the original resource without a conflict error.
Concurrent inserts serialize on the unique index so at most one row exists for (tenant_id, stored_slug).

Implementation guidance

Map unique-violation SQLSTATE in one place. Normalize in one function shared with tests.
Idempotent replay is not a conflict. Do not SELECT-for-existence on email from public endpoints.

Failure handling and safeguards

Empty slugs are validation errors. Missing unique indexes fail health checks.
Self-updates to the same slug succeed. Rate-limit public creates so 409 is not a slug oracle.

Verification and operations

Dual-writer tests, folding goldens, and indistinguishable unauthenticated email responses.

The executable-looking examples in this package are fixtures and acceptance contracts. Run python tools/validate.py from the collection root to check package structure and metadata; then implement and execute the scenarios in the target repository.

Adoption assumptions

Names and numeric values in example.yaml are an adoption profile, not universal defaults.
The adopting team must map actors, data classes, error vocabulary, and ownership to its system.