fix(objectql): a cleared optional unique text field stores null, so the second record cleared is no longer refused 409 UNIQUE_VIOLATION (#29176) - #29271

Requested by Steve · project thread
Fixes #29176

Clause-②: no
Metadata-protocol: none

维护者速读

• 改了什么：可选的唯一文本字段（SKU、税号、工号这类）被清空时，服务端写入口把 '' 存成 null。第二条清空的记录不再被 409 UNIQUE_VIOLATION 拒绝；REST、批量、导入都走这同一个入口。
• 为什么改：唯一索引把 '' 当成一个真实值，而 null 互不冲突。用户清空第二条记录就保存不了，只能填假值污染数据。按分诊裁决修在服务端写路径（packages/objectql），不改表单。
• 风险与回滚：只动「可选 + 有唯一约束 + 文本类」字段上恰好为 '' 的值；必填字段照旧拒绝，真重复照旧 409，password/secret 的空串拒绝不变，非唯一字段仍存 ''。已存的 '' 不迁移。回滚 = revert 本 PR。
• 先看：packages/objectql/src/validation/record-validator.ts 的 optionalUniqueTextColumns 与 normalizeBlankTypedRow 改动的那一行；packages/rest/src/rest-data-optional-unique-blank.test.ts；.changeset/29176-optional-unique-blank-null.md。
• 两分钟验证：pnpm --filter @objectstack/rest exec vitest run src/rest-data-optional-unique-blank.test.ts，预期 8 个测试全绿（主干上同一文件 6 红：第二次清空是 409）。
• 需要你做的：无，席位落地。

What changes

• The seam. normalizeBlankTypedValues (packages/objectql/src/validation/record-validator.ts) is the engine's existing blank write door: ObjectQL.insert() / update() and the dry run validate() apply it to the caller's payload before the middleware, the caller snapshots, the defaults, the hooks and validation read it (record write door: '' skips every type check, so a number, boolean, date, datetime or time column stores an empty string — normalise it to null at the door (seam from objectui#10813) #20308 put it there for non-string-typed columns). Every REST, batch, upsert and import door reaches the engine through those methods. engine.ts is not touched.
• The new arm. Exactly '' becomes null on a column that is all of:
  • text-like: the spec's STRING_VALUE_TYPES, read as the constant, minus password and secret;
  • not required: true;
  • covered by a unique constraint, on either surface the drivers build one from: the field's own unique, or membership in a declared indexes[] entry carrying unique. Both are read through the spec's isUniqueDeclared, the predicate the drivers use.
  • Lifecycle columns the engine owns (id, created_at, …) are never rewritten.
• Edges, each deliberate.
  • Whitespace stays a value. The $empty operator's ruled table reads '' and null as one answer on a text-like column and whitespace as a value, so this rewrite changes no $empty / is_empty answer.
  • required columns are excluded, so a required unique field's '' is refused required exactly as today. Without the exclusion, a required field with a defaultValue would take the default instead of being refused.
  • password / secret are excluded: a maintainer ruling refuses '' on them while null means clear, and rewriting would turn that refusal into a silent clear.
  • select / radio / lookup / user are excluded: the same $empty table reads their '' as a value. See Acceptance notes.
• Defaults. An optional unique field with a defaultValue takes it on insert when sent '', exactly as when sent null (the typed arm already behaves this way).
• FieldDef in the validator gains unique?: unknown; the function's parameter type gains indexes?: unknown. Both widen only.

Why

Triage 6573215352 ruled the layer: "the fix lands on the server write path in packages/objectql: normalise an empty string to null for an optional field before the unique index sees it, which also covers REST and import callers." The scope is the fields a unique constraint covers. A non-unique optional text field keeps storing '': the validator's docblock, the str_empty round-trip case and two existing pins (engine-blank-typed-value-door.test.ts, rest-data-blank-typed-value.test.ts) depend on that, and none of them needed an edit.

Tests

No existing test was edited. Three new files:

• packages/objectql/src/validation/record-validator.optional-unique-blank.test.ts (9 tests): the population pinned to the spec set (12 types), all three unique scopes, the declared-index surface (a plain index member keeps ''), the controls (no unique, unique: false, required, credentials, other string-stored types, a lifecycle column), whitespace and non-blank pass-through, purity.
• packages/objectql/src/engine-optional-unique-blank-door.test.ts (8 tests): what the driver receives on insert, insert array, insertMany, update by id, update by predicate; whitespace and non-blank untouched; the defaultValue interaction; required unchanged on insert, update and the dry run, including the required-with-default case; '' on a unique password still refused (EmptyCredentialWriteError, VALIDATION_ERROR / 400).
• packages/rest/src/rest-data-optional-unique-blank.test.ts (8 tests), real ObjectQL + SQLite SqlDriver + RestServer routes, physical column read past the engine: POST '' twice, PATCH clear twice, a declared composite unique index member, batch create and createMany, a non-unique control keeping '', a real duplicate still 409 UNIQUE_VIOLATION (by POST and by PATCH), a required unique field 400 VALIDATION_FAILED / required with and without a defaultValue, and a legacy stored '' row.

Acceptance notes

• Driver families, measured on dedc3505 (two rows in a unique text column, through each driver's own create): driver-sql on SQLite: two '' → second refused, two null → both land. driver-memory: the same. sqlite-wasm and turso local share SqlDriver's compiler and DDL path, so they were not measured separately. The engine now hands every driver null; driver-memory already pins NULL-distinct in memory-unique-constraint.test.ts.
• MongoDB, NOT MEASURED (the mongod download for mongodb-memory-server is refused with 403 here). By reading: mongodb-schema.ts builds field uniqueness as { unique: true, sparse: true }. A sparse index skips a document only when the field is absent, and still indexes an explicit null. If that holds, two cleared rows still collide on that driver, because the engine now sends null. That would be a driver divergence from the NULL-distinct rule the other drivers keep, and it is outside this card's ruled layer. Reported to the seat.
• Not covered, by scope. A unique single-value select / lookup that is cleared also sends '' (the card's own reading on showcase_field_zoo) and still collides on the second record. The $empty table reads '' as a value on those types, so rewriting it is a separate decision. Reported to the seat.
• Stored rows. Existing '' values are not migrated (pinned): they keep matching field = ''; a newly cleared row matches $null: true; $empty: true matches both; a new clear no longer collides with a stored ''.

Verification

Head after git merge origin/main: see the PR's check-runs. Local readings:

Human Minimum

• 1. Schemas start as Zod; packages/spec holds no runtime logic. (not touched)
• 2. Every name I added uses the casing for its kind.
• 3. No formatter ran; pnpm lint passes.
• 4. One issue, one branch off an up-to-date main, one pull request.
• 5. A change that publishes carries a changeset, and both declaration lines are filled in here and in the changeset.
• 6. packages/spec touched: n/a.
• 7. No hand edits to content/docs/references/, packages/*/CHANGELOG.md or content/docs/releases/.
• 8. Title, body and comments are in English (the maintainer brief above is Chinese by repo rule).

Generated by Claude Code