UNIQUE FIELDS

Mark a scalar field as unique per object so two live records can never hold the same value — a unique "Employee ID", "SKU", "Email", or "Invoice No.". This is a field-configuration enhancement, not a new field type: no FieldType value, no column-type change, no new engine. Turning it on adds one partial UNIQUE index; turning it off drops that index.

Uniqueness is scoped to the workspace, never the cluster. Two workspaces may each hold EMP-001.

Which field types support it

options.unique is honoured only for the types in UNIQUE_OPTION_FIELD_TYPES:

All five are single-value scalars on one physical, btree-indexable column whose validator trims and maps empty to NULL — so the stored value is the uniqueness key, and blank records never collide with each other.

Deliberately excluded: long_text (unbounded TEXT would exceed the 2704-byte btree index-tuple limit at INSERT, long after the toggle was set), and every JSONB/array type (relation, tag, multi_select, file, photo, address, smart_catalog, calculation) — a unique index over a JSONB blob compares serialized documents, not business values.

Setting options.unique on any other type is stored but has no effect: no index is created. See Known gaps.

Configuring it

PUT /object/field/:slug
{ "options": { "unique": true } }

unique must be a boolean — anything else is a 400 naming the type (TEXT field "unique" option must be a boolean). Validation runs at config time, before any DDL.

Unique + default value is rejected

A default_value means "every record that doesn't supply a value gets THIS value", which guarantees a collision as soon as a second record exists. On create it is worse: ADD COLUMN … DEFAULT x backfills the identical value into every existing row and the index build fails immediately. The combination is rejected with a 400 at config time.

On the OFF→ON transition, any pre-existing column DEFAULT is dropped first (it is set once at ADD COLUMN time and never re-synced), so default-inserted rows get NULL — distinct — instead of a guaranteed collision.

The index

Named uq_<table>_<col>, truncated to Postgres' 63-char identifier limit with a deterministic SHA-1 suffix when it overflows. Built with CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS so a populated table takes no write lock; a failed build leaves an INVALID index, which is dropped before the error is rethrown so a retry rebuilds cleanly.

The workspace_id prefix on hyper is the single most consequential rule here: a bare UNIQUE(<col>) on the shared crm_hyper DB would make a value unique across every tenant and leak its existence across the tenant boundary.

Two properties follow from the index shape:

• Soft delete frees the value. The partial predicate excludes soft-deleted rows, so a deleted "A" and a live "A" legitimately coexist.
• Empty is never a duplicate. SQL UNIQUE treats NULLs as distinct, so any number of records may leave the field blank.

Enabling on a field that already has data

The enable is blocked while duplicates exist rather than mutating anything:

1. Scan the live domain — GROUP BY <col> HAVING COUNT(*) > 1 WHERE <col> IS NOT NULL AND deleted_at IS NULL, on the workspace-pinned connection.
2. Any duplicate groups → 400 naming the count. The flag does not take effect; no index is created.
3. The admin merges or edits the offending records and retries. Data is never auto-mutated to force uniqueness.

The count is deliberate: the message reports how many values are duplicated, never the values themselves, which would leak PII on an email or phone field.

At write time

There is no application-level pre-check for unique fields. The partial unique index is the sole enforcement point, and that is intentional — a SELECT-before-INSERT would duplicate work and reintroduce a TOCTOU race. Two concurrent writers compete on the index: exactly one commits, the loser gets Postgres 23505, which DynamicRecordRepository.translateUniqueViolation turns into a 409 ConflictException carrying the field label parsed from the index name.

Object-level unique_fields rules with mode: 'warn' are the exception — those get no DB index and are checked in the app by RecordValidatorService.checkDuplicates, which returns the duplicate's slug for the soft-warning UX. The per-field toggle always projects mode: 'reject'.

Storage

The per-field toggle is the UX surface; the object's options.unique_fields array is the storage the shared index engine reads. Turning on unique for a field appends { fields: ['<column_name>'], mode: 'reject' } to it. There is no parallel storage mechanism.

Known gaps

Tracked, not shipped:

• No UNIQUE_UNSUPPORTED_TYPE rejection. unique: true on an ineligible type is silently stored and ignored rather than returning a 422.
• No case-insensitive mode. Uniqueness is byte-exact, so Ali@x.com and ali@x.com are two distinct values on a unique email field. A LOWER(<col>) functional index is not implemented.
• The duplicates report is a count, not a list. No per-group values or record slugs, so a "resolve duplicates" UI cannot be built against it yet.
• The 409 carries no conflicting-record reference — only the field label — so an "already used by <record>" link is not possible.
• Field duplication swallows index-build failure. duplicateFields logs a failed post-create side effect and still reports the field as created, so a cloned field can carry unique: true with no index behind it.