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.

References

• Unicode Standard Annex #15 Unicode Normalization Forms
• Unicode Standard Annex #31 Identifier and Pattern Syntax