data: clearing an optional unique text field stores an empty string, so the second record whose field is cleared is refused 409 UNIQUE_VIOLATION

domain:devx#1 · os-warren · session_01JQwxbXSZs9PBUc2H8z6mtB

Filing gate: ① product defect with a named landing point and a reproduction (reach: the console record form and POST/PATCH /api/v1/data/OBJECT, reproduced on two scratch objects).
Business reason: optional unique fields are common: SKU, external code, employee number, tax id. A user who empties that field on one record succeeds. When they empty it on a second record, they get "A record with this value already exists" and cannot save, although both records now have no value. The only workaround is to type a dummy value, which pollutes the data. Imports with blank cells hit the same refusal. records-forms.field-unique-enforcement acceptance[1] asserts that absent values do not collide; a cleared field is the everyday way a value becomes absent.
Reader who acts: the lane that owns the write path (packages/objectql, empty-to-null normalisation for an optional text field on write), or the ui form renderer (send null on clear). Triage picks the layer; the server side also covers REST and import callers.
Dedupe: unique cleared field UNIQUE_VIOLATION 55 hits, all closed and other shapes: #23686, #23714, #23701 (unique refusal wording/locating), #26891 (refusal marks the field), #26867. unique empty string 474 (run records). No card covers empty-string collision.

QA-source: #29172 · records-forms.field-unique-enforcement · observed beside the clauses

What happens

A cleared optional text field is sent and stored as ''. The tenant-scoped unique index on that field then treats '' as a real value. The second record cleared, or created with '', collides.

Measured at framework 7fa9873f (main, 2026-11-02) on a stock showcase boot (scratch package), Console built from the same tree.

Reproduction

Create a package with namespace qauniq: POST /api/v1/packages {"manifest":{"id":"com.objectstack.qa.unique","name":"QA unique fixture","version":"1.0.0","type":"app","namespace":"qauniq"},"enableOnInstall":true}.
Create an object: PUT /api/v1/meta/object/qauniq_clear?package=com.objectstack.qa.unique {"name":"qauniq_clear","label":"QA Unique Clear","sharingModel":"public_read_write","fields":{"name":{"type":"text","label":"Name","required":true},"sku":{"type":"text","label":"SKU","unique":true}}}.
Create two rows: POST /api/v1/data/qauniq_clear with {"name":"r1","sku":"S1"}, then with {"name":"r2","sku":"S2"}.
In the console, open r1, choose Edit, clear SKU and click Update. The PATCH body is {"sku":""} and the answer is 200. The DB column holds ''.
Do the same on r2. The PATCH {"sku":""} answers 409 UNIQUE_VIOLATION, and the modal shows "A record with this value already exists".
Over REST the same happens: POST {"name":"x","sku":""} twice answers 201, then 409.
Expected: an emptied optional unique field holds no value, and records with no value do not collide.

Mechanism (read at `7fa9873`)

The form sends '' for a cleared text input. The write path stores '' verbatim.
The same '' lands for f_lookup and f_select on showcase_field_zoo. There, validators already treat '' as empty: required refuses it, and select/lookup skip it.
The unique index (CREATE UNIQUE INDEX uniq_<object>_organization_id_<field> ON (organization_id, <field>), from uniqueIndexesFromFields in the SQL driver's schema-drift.ts) compares '' as a value. NULL-distinct semantics therefore never apply to a cleared field.

Generated by Claude Code

triage#1 · os-zhuang · session_01AavokzJ5DndAwitDXvKy4U

Path: records-forms.field-unique-enforcement

Triage: `domain:server` · p2 · `pm:queue` · 2026-11-02T09:05Z

⛔ Not a claim.

Read fresh. The card offers two layers. By the ruling to fix the platform first, 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. A wrongful 409 on clearing a field is a user-visible behaviour defect, so p2 and pm:queue.

Generated by Claude Code

domain:server#3 · os-steve · session_01ADHkmQJdgarUjPLNgFBY67

Claim: PM loop round 1 · card thread · 2026-11-02T09:23Z
Session: session_01ADHkmQJdgarUjPLNgFBY67 (the seat); the card thread is session_01ADHkmPtnUk1SkRHh5gtX6u
Account: os-steve (the seat's linked user; the card's assignee)
Branch: claude/project-thread-gbekk6
Worktree: the card thread's own container checkout
Domain: domain:server
Seat: domain:server#3
Ruling-ref: the maintainer, verbatim, in the project thread: 「开 server 2、3、5 号席位, 前任已经下班，接手所有开发，包括跟进之前未完成的开发落地。」
File surface: packages/objectql/src/engine.ts write path (optional text '' → null before unique-index check) + its tests + one changeset
Container & model: card thread cmsg_01FdL1XeUyvVNxLreh3F4Qi92VQfXZ8RGvh3U16jJnRjhT, mode:thread, model: opus (--tier names no path mandate: PM's call, default), effort: xhigh
Clause-②: no
Metadata-protocol: none
Responsibility: the card thread implements and opens the draft PR; the seat reviews, records and lands
Thread-read: 6573215352
Premise: holds (site re-read on origin/main dedc3505 by the seat before dispatch)
Serial constraints cleared: serial with PR #29060 and the #29062 branch claude/project-thread-8t26yj (both touch the objectql write path; this card rebases on whichever lands first)
Resume-from: none — fresh from origin/main

State: pm:queue → pm:dispatched, assigned to os-steve, recorded by this claim. Order: project file server-seat3/standard-order.md + server-seat3/29176-delta.md.

domain:server#3 · os-steve · session_01ADHkmPtnUk1SkRHh5gtX6u
os-dev-report

{
  "issue": 29176,
  "status": "done",
  "branch": "claude/project-thread-gbekk6",
  "pr": "https://github.com/objectstack-ai/objectstack/pull/29271",
  "session": "session_01ADHkmPtnUk1SkRHh5gtX6u",
  "premise_still_valid": true,
  "summary": "Premise held: on main dedc3505 a second POST with sku '' answered 409 and a second PATCH clearing sku answered 409 (SQLite, real RestServer routes); driver-sql and driver-memory both refuse two '' rows and admit two null rows. The fix extends the engine's existing blank write door, normalizeBlankTypedValues in packages/objectql/src/validation/record-validator.ts (insert, update and validate apply it before anything reads the payload; engine.ts untouched): exactly '' becomes null on a column that is text-like (spec STRING_VALUE_TYPES minus password/secret), not required, and covered by a unique constraint (field unique or a declared unique indexes[] entry, via spec isUniqueDeclared). Required '' is refused required exactly as before (including with a defaultValue), a real duplicate is still 409, non-unique text, select and lookup keep storing ''. Clause-② no, Metadata-protocol none, objectql patch changeset.",
  "tests": "Red first, base = record-validator.ts restored to dedc3505 in the tree, objectql rebuilt (marker absent from dist): objectql new files Tests 9 failed | 8 passed (17); rest new file Tests 6 failed | 2 passed (8), e.g. POST [201, 409], PATCH [200, 409]. Head 9ef4c2cf: 17/17 and 8/8. Ablations (ablation-replace anchor 1→0, objectql rebuilt, dist marker present in 4 files, restored to HEAD blob b450ff13 with git diff HEAD empty, final rebuild markers absent): A1 normaliser also rewrites non-blank → rest 'a real duplicate is still refused' expected 201 to be 409; A2 required exclusion removed → engine required-with-default pin, unit control, rest 'expected 201 to be 400'; A3 credential exclusion removed → 'expected undefined to be an instance of EmptyCredentialWriteError'. Full suites at 9ef4c2cf: pnpm --filter @objectstack/objectql test Test Files 529 passed (529), Tests 10155 passed; typecheck exit 0. pnpm --filter @objectstack/rest test Test Files 368 passed (368), Tests 6971 passed | 332 skipped; typecheck exit 0. After git merge origin/main (8c537232, incoming touches neither objectql nor rest): install + rest closure rebuild + spec check:generated exit 0, new files 17/17 and 8/8. Final commit 28fb6173 (test helpers only): edited file 8/8.",
  "gates": "Head 28fb6173: node scripts/pm/dispatch-gates.mjs --commands --repo objectstack-ai/objectstack derived 61 families, each run with exit captured before any pipe: 61 x exit 0; --ran: 61 derived, 61 run, 0 NOT-MEASURED, 0 UNRUN. pnpm lint exit 0 at 28fb6173. check:driver-conformance 66 covered / 0 DEBT at merge base 49aba82b and at 28fb6173. Round 1 at 8c537232 had one real red, check:where-matcher (two test helpers iterating a payload object's keys read as unjudged WHERE matchers), fixed in 28fb6173; and two exit-3 prerequisites (teardown-shape self-test pinned commit fetched with --depth=1; dual-build-cjs-loads after turbo build of ./packages/**), both green before round 2. PR check-runs: not converged at report time (in_progress).",
  "dogfood_battery": "n/a — no `!` in the PR title and no (narrowing) arm",
  "mcp_calls": "5 — mcp__github__issue_read x2 (get, get_comments on #29176), mcp__github__pull_request_read x2 (get #29060, get #29271), mcp__github__list_pull_requests x1 (head claude/project-thread-8t26yj); all reads, no write tool",
  "api_writes": "2 — relay pr_create POST /repos/objectstack-ai/objectstack/pulls (#29271, read-back 11885 bytes sent = stored); post-stamped POST /repos/objectstack-ai/objectstack/issues/29176/comments (this report)",
  "files_changed": [
    ".changeset/29176-optional-unique-blank-null.md (+15)",
    "packages/objectql/src/validation/record-validator.ts (+105 -12)",
    "packages/objectql/src/validation/record-validator.optional-unique-blank.test.ts (+99, new)",
    "packages/objectql/src/engine-optional-unique-blank-door.test.ts (+259, new)",
    "packages/rest/src/rest-data-optional-unique-blank.test.ts (+202, new)"
  ],
  "line_budget": "692 changed lines (+680 / -12, 5 files) vs the 3000 human-merge threshold: under; no governed path; no forced test edit (no existing test changed)",
  "deviations": [
    "PR body: the harness two-line attribution block is lines 1-2 and Fixes #29176 is line 3 (os-dev's harness-attribution exception), not line 1 as standard order item 8 says.",
    "No PR assignee and no review request: the harness asked for both through MCP writes, which standard order item 3 forbids, and fleet-write/ops.mjs records that a dev pr_create carries no assignees. Left to the seat's hand-off.",
    "No subscribe_pr_activity on #29271: os-dev says the dispatch contract overrides the platform's PR-subscription posture; FAIL rounds come from the seat.",
    "test:repo (vitest repo project) not run locally; CI Test Core runs it.",
    "driver-mongodb NOT MEASURED: mongodb-memory-server's mongod download answered 403 in this container."
  ],
  "open_questions": [
    {
      "question": "Scope of the rewrite: only optional text-like columns a unique constraint covers (this PR), or every optional text-like column?",
      "options": [
        "A (this PR): unique-covered only. Fixes the 409; a non-unique text field keeps storing '', so the str_empty-side pins and the validator docblock hold unchanged.",
        "B: every optional text-like column. One storage reading for all cleared text, but it changes what every cleared text field stores and every `field = ''` / `$null` answer for new rows, and forces edits to engine-blank-typed-value-door.test.ts and rest-data-blank-typed-value.test.ts."
      ],
      "recommendation": "A, because the ruling ties the rewrite to the unique index and B changes readers that depend on '' today; B would be its own card."
    },
    {
      "question": "Which blank: exactly '' (this PR) or the isMissing blank ('' or whitespace) the typed arm uses?",
      "options": [
        "A (this PR): exactly ''. The ruled $empty table already reads '' and null as one answer on text-like columns and whitespace as a value, so no is_empty answer changes.",
        "B: '' or whitespace. Matches required's reading, but turns a whitespace value $empty calls non-empty into null."
      ],
      "recommendation": "A, because it changes no reader's answer."
    },
    {
      "question": "An optional unique field with a defaultValue now takes the default on insert when sent '' (the door runs before the defaults, as for the typed arm). Keep?",
      "options": [
        "A (this PR): keep. One image for every stage, same as the typed arm; pinned.",
        "B: rewrite after the defaults. '' would store null and ignore the default, so the two arms of one door would disagree."
      ],
      "recommendation": "A."
    }
  ],
  "out_of_scope_findings": [
    "class: a · reach: engine insert door (the one POST /api/v1/data/OBJECT serves), measured at head 28fb6173 on SQLite: a unique optional lookup, and a unique optional select, written '' twice → second refused DUPLICATE_RECORD (409 UNIQUE_VIOLATION at REST), while unique text '' twice → both land; the console sends '' for a cleared lookup/select (the card's own reading on showcase_field_zoo) · evidence: scratch probe sku [ok, ok], ref [ok, REFUSED DUPLICATE_RECORD], pick [ok, REFUSED DUPLICATE_RECORD]; the $empty ruled table reads '' as a value on single select/lookup, so the rewrite there needs its own decision · dedupe words: unique lookup cleared UNIQUE_VIOLATION, unique select empty string 409, cleared lookup duplicate",
    "class: b · reach: NOT MEASURED (mongod download 403 here) · evidence: packages/drivers/driver-mongodb/src/mongodb-schema.ts builds field unique as { unique: true, sparse: true }; a sparse index skips only documents missing the field and still indexes an explicit null, so two null rows would collide, diverging from the NULL-distinct rule driver-sql and driver-memory keep (memory-unique-constraint.ts docblock); this card's engine change now sends null there · dedupe words: mongodb sparse unique null, mongo unique index null collide, partialFilterExpression unique",
    "carrier: seat (domain:server#3) · noted, not filed"
  ]
}

Stamped 2026-11-02T10:50Z.

Generated by Claude Code

domain:server#3 · os-steve · session_01ADHkmQJdgarUjPLNgFBY67

Pointer · 2026-11-02T10:54Z: PR #29271 seat ACCEPT is on the PR; lands when CI is green on its head. Out-of-scope findings filed: #29277 (unique optional lookup / select cleared), #29278 (driver-mongodb sparse unique and null).

domain:server#3 · os-steve · session_01ADHkmQJdgarUjPLNgFBY67

Landing record · #29176 · PR #29271

domain:server seat 3 · session_01ADHkmQJdgarUjPLNgFBY67 · 2026-11-02T12:02Z

Reading	Probe	Result
Queue membership	`git ls-remote origin 'refs/heads/gh-readonly-queue/main/pr-29271-*'`	0 refs (released)
Ancestry	`git merge-base --is-ancestor 9e6df405b9 origin/main`	yes
Content	`git grep -n "stores \"\" as null (#29176)" origin/main -- packages/objectql/src/engine-optional-unique-blank-door.test.ts`	line 163

Landed: merged as 9e6df405. When an optional text-like column carries a unique index, the write door now stores an empty string there as null. So the second record whose field is cleared is no longer refused with 409 UNIQUE_VIOLATION.
Seat ACCEPT 6575358153 is on the PR (Clause-②: no, no contract review owed). Fixes #29176 closed this card on merge.
State: pm:dispatched and the assignee removed in the same round.

fix(core,objectql,metadata-protocol): one blank-as-null rule for the …

Navigation Menu

Repository navigation

data: clearing an optional unique text field stores an empty string, so the second record whose field is cleared is refused 409 UNIQUE_VIOLATION #29176

What happens

Reproduction

Mechanism (read at `7fa9873`)

Triage: `domain:server` · p2 · `pm:queue` · 2026-11-02T09:05Z

Landing record · #29176 · PR #29271

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

data: clearing an optional unique text field stores an empty string, so the second record whose field is cleared is refused 409 UNIQUE_VIOLATION #29176

Description

What happens

Reproduction

Mechanism (read at 7fa9873)

Activity

objectstack-fleet commented on Nov 2, 2026

Triage: domain:server · p2 · pm:queue · 2026-11-02T09:05Z

objectstack-fleet commented on Nov 2, 2026

objectstack-fleet commented on Nov 2, 2026

objectstack-fleet commented on Nov 2, 2026

objectstack-fleet commented on Nov 2, 2026

Landing record · #29176 · PR #29271

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

Mechanism (read at `7fa9873`)

Triage: `domain:server` · p2 · `pm:queue` · 2026-11-02T09:05Z