Skip to content

[17.0-rc][疑似平台] crm_case 自动编号计数器落后于既有 case_number:REST 建单连续 409,每次失败还烧掉一个号(实测重试 25 次才成功) #698

Closed
Closed
[17.0-rc][疑似平台] crm_case 自动编号计数器落后于既有 case_number:REST 建单连续 409,每次失败还烧掉一个号(实测重试 25 次才成功)#698
Labels
bugSomething isn't workingpriority:p1High: required for production / M2upstream:objectstackBlocked on / caused by the ObjectStack platform — tracked upstream

Description

@yinlianghui
yinlianghui
opened on Aug 5, 2026

Found during the 17.0 GA acceptance sweep on @objectstack/* 17.0.0-rc.2 (current main), action-sweep phase.

Symptom

POST /api/v1/data/crm_case {...} (no case_number supplied — it is an autonumber) returns 409 UNIQUE_VIOLATION, repeatedly. The server log shows the attempted number climbing by one per failed insert:

UNIQUE constraint failed: crm_case.organization_id, crm_case.case_number

attempted CASE-00010 → 409 → CASE-00011 → 409 → … → succeeded at CASE-00039 after 25 attempts (devserver2.log from 07:23:51Z onward).

Two defects compound here:

  1. The autonumber counter does not sync to the existing rows. The DB already held cases up to a much higher number (seeded + prior test data), but the counter started from 10. It should initialize to MAX(existing) + 1 per tenant on boot/seed, or the insert should re-check on collision instead of failing the request.
  2. Every failed create burns a number. The counter advances on failure, so the eventual success lands far beyond the collision zone, and the sequence gap is permanent. A caller with no retry loop simply cannot create a case.

Aggravator (CRM-side seed inconsistency)

The DB contains a duplicate CASE-00003 pair: a seed row with organization_id = NULL and an API-created row with the org set. Seed rows do not carry organization_id while API rows do, so the unique index (organization_id, case_number) only bites within the org partition and the seed rows sit outside it. That's a HotCRM seed-data problem worth fixing regardless of the platform half: it makes "what numbers are taken" ambiguous per tenant.

Impact

Any integration or UI path that creates cases on a warm database will hit bursts of 409s and, without client-side retry, hard failures. The acceptance run only got through by brute-force retrying.

Reproduction

  1. Boot a dev server against a database that already contains cases (seeded is enough, warm-boot re-seed makes it worse).
  2. POST /api/v1/data/crm_case with a minimal valid body, no case_number.
  3. Observe 409 UNIQUE_VIOLATION; repeat and watch the attempted number increment one per call in the server log.

Attribution

Counter-sync and burn-on-failure: platform (autonumber provisioning). organization_id NULL on seed rows: CRM (seed data). Filed here with both halves stated; split upstream if the owner prefers.

Refs #689

Activity

  1. yinlianghui
    added
    bugSomething isn't working
    prio:p1
    upstream:objectstackBlocked on / caused by the ObjectStack platform — tracked upstream
    on Aug 5, 2026
  2. yinlianghui
    added a commit that references this issue on Aug 5, 2026

    docs(acceptance): record the full action sweep and all ten blocker ve…

    fbcda76

  3. yinlianghui commented on Aug 5, 2026

    @yinlianghui
    yinlianghui
    on Aug 5, 2026 – with Claude
    CollaboratorAuthor

    Status update from the flow phase, same day: the 409 storm did not reproduce — all 4 case creates succeeded on attempt 1 (CASE-00041…44). That's consistent with the mechanism as filed: the earlier grind burned the counter forward past the seeded numbers (up to CASE-00039), so the collision zone is behind it now. The underlying defect — counter not syncing to MAX(existing) per tenant on boot/seed — remains unfixed and will reproduce on any fresh database with seeded cases; the burn-through just makes it a one-time storm per DB rather than a permanent state. Repro guidance for whoever picks this up: test on a fresh DB, not one that has already been ground past the seeds.

    Generated by Claude Code

  4. yinlianghui
    added a commit that references this issue on Aug 5, 2026

    docs(acceptance): record the full flow sweep — 24/24 pass, #684 verdi…

    baeba52
  5. yinlianghui
    added
    pm:blocked
    on Aug 5, 2026

  6. yinlianghui commented on Aug 5, 2026

    @yinlianghui
    yinlianghui
    on Aug 5, 2026 – with Claude
    CollaboratorAuthor

    Blocked-by: objectstack-ai/objectstack#5495

    分诊(PM 循环第 1 轮,会话 session_01VHrPAGEgFDoHjphqYG4BMa):计数器不同步 + 失败烧号判定为平台缺陷(autonumber 供给),⛔ 不在 hotcrm 侧修,已立镜像单 objectstack#5495(查重:#3696/#1603/#3152 均已关闭,不覆盖「计数器不对齐既有行」)。含你补充的复现要点:需 fresh DB,烧穿后风暴一次性消失。

    CRM 半侧(seed 行 organization_id = NULL)暂不单独派发:seed 行的 org 戳是平台种子加载路径的行为,与 objectstack#5494(自动化插入不打 org)同类 —— hotcrm 侧没有可写的「生产者」。若平台裁定 seed 可/应带 org,再回本单开 CRM 补种子任务。本单打 pm:blocked。

    Generated by Claude Code

  7. yinlianghui commented on Aug 5, 2026

    @yinlianghui
    yinlianghui
    on Aug 5, 2026 – with Claude
    CollaboratorAuthor

    [17.0-rc2验收] A3(Flows)组补充新证据:fresh DB 上这个缺陷不是 409 风暴,而是零报错的静默重号 —— 且不止 crm_case,crm_quote / crm_contract 同样命中。

    环境:hotcrm@0899b4f + @objectstack 17.0.0-rc.2,全新 dev DB(--seed-admin,仅种子数据,无历史 API 行)。

    实测

    REST 依次创建 case / quote / contract(均不带编号字段),一次 409 都没有,但铸出的号全部与种子行重复:

    crm_case:      CASE-00001 ×2  CASE-00002 ×2  CASE-00003 ×2  CASE-00004 ×2   (API 行 vs 种子行)
crm_quote:     QTE-0001 ×2   QTE-0002 ×2   QTE-0003 ×2   QTE-0004 ×2
crm_contract:  CTR-0001 ×2   CTR-0002 ×2   CTR-0003 ×2

    机制与本单「Aggravator」一节完全吻合:种子行 organization_id = NULL,API 行带 org,唯一索引 (organization_id, case_number) 只在分区内咬合 —— fresh DB 没有任何同分区旧行,于是从 1 起步的计数器每个号都「可用」,重号直接落库。楼主环境里的 409 风暴,其实是「DB 里已有同 org API 行」这个额外条件下的显形;fresh DB 上同一根因表现为更糟的形态:业务标识符静默重复,无任何信号。

    下游已观察到的实害(flows 面)

    • contract_renewal 的幂等闸门以 subject: 'Renewal due: contract {contract_number}' + account 做去重键 —— 同 account 下两份不同合同共号(实测 CTR-0002 就有种子/API 各一)时,第二份合同的续约提醒会被第一份的 open task 误吸收,静默漏提醒。
    • 各 sweep 的通知文案(Contract expired: CTR-0001、SLA breached: case CASE-00004)在重号环境下指向不明。

    给 objectstack#5495 修复者的复现要点:fresh seeded DB(不要用被 409 磨穿过的库)+ 任一带 autonumber 的对象直接 POST,看编号而不是看状态码 —— 状态码这条路在 fresh DB 上是全绿的。

    Generated by Claude Code

  8. huangyiirene commented on Aug 11, 2026

    @huangyiirene
    huangyiirene
    on Aug 11, 2026 – with Claude
    Collaborator

    Re-measured on @objectstack/* 17.0.0-rc.6 during the rc.5 → rc.6 upgrade (#1059, PR #1066). The platform half — defect 1, "the counter does not sync to the existing rows" — is addressed upstream.

    The autonumber seed in @objectstack/objectql no longer reads an arbitrary window. Its own commentary describes the exact failure this card measured:

    The seed used to be one find with limit: 5000, no orderBy and no filter: the max of an ARBITRARY 5000-row window (on SQL, typically the oldest 5000 rows), which for any object past that size — or any scope whose rows sit outside the window because other scopes filled it — seeds BELOW the real MAX. The counter then issues numbers from an already-taken band, and on a unique record-number field that is a duplicate business identifier.

    That is why the counter started at 10 on a database already holding much higher case numbers. What replaces it (#6249):

    • a complete keyset-paginated scan of the scope, not a windowed one (seek pagination, not offset — "an offset walk cannot promise it visited every row");
    • the numeric max computed by parsing every value, never delegated to ORDER BY or an aggregate max, because those rank stored values as text and CASE-99999 sorts above CASE-100000;
    • prefix pushed down as $startsWith so each scope reads its own rows.

    Not established by this probe — two things worth re-running before closing:

    1. Defect 2, burn-on-failure. Whether a failed insert still advances the counter is a separate mechanism from seeding, and I did not measure it. If seeding is now correct the collisions should not arise in the first place, but the "every failure burns a number" behavior may still be there under genuine contention.
    2. The CRM-side aggravator — seed rows carrying organization_id = NULL while API rows carry it, so (organization_id, case_number) doesn't bite across the partition and CASE-00003 can exist twice. That is this repo's half and is untouched by the upgrade. Note crm_case 的 [organization_id, case_number] 手写复合唯一索引在无 organization 的装机上什么也不约束，且 Protocol 18 拒收该拼法（ADR-0120） #1023/fix(case): 案号唯一性改用字段级声明，在无 organization 的装机上也真正生效（#1023） #1029 has since landed case-number uniqueness enforcement for untenanted installs, which may already cover it — worth checking whether that closed this half.

    Scope note: static read of the shipped seeding path, not a live warm-database POST /api/v1/data/crm_case run.

    Generated by Claude Code

  9. hotlong commented on Aug 14, 2026

    @hotlong
    hotlong
    on Aug 14, 2026 – with Claude
    Contributor

    GA reading (17.0.0) — one residual half is FIXED, the other REPRODUCES. Card stays open.

    From #1153 (GA close-out B2), session session_01XAK3brMLjd4ykF4QAhFnuo. Live probe, not a source read: objectstack dev -p 4002 --seed-admin --fresh on @objectstack/* 17.0.0 GA, hotcrm d4ddee0, SqlDriver(better-sqlite3), a fresh database (38 seeded cases, CASE-00001…CASE-00038) — the repro condition the 2026-08-05 follow-up asked for.

    Half (a) — "every failed create burns a number": NOT REPRODUCED

    The mechanism is gone, and it is gone because a taken number no longer produces a failure at all. Planted a contiguous band of taken numbers above the counter, then issued one create:

    counter at 11; planted a TAKEN band CASE-00012..CASE-00020
ONE create -> HTTP 201 case_number=CASE-00021
counter after that single create: 21 (was 11)

    One request, one 201, counter advanced straight past the whole band. The old shape would have been nine 409s with the counter climbing one per failure — both outcomes were printed by the same probe, so this is not a green that could not have gone red.

    A create that genuinely fails also leaves the counter alone:

    POST missing required fields -> HTTP 400 VALIDATION_FAILED
counter before=22 after=22        => a rejected create did NOT consume a number

    Consistent with objectstack#5495 → PR #6932 ("re-seed a stale autonumber counter instead of burning a number per failed create"), merged 2026-08-09.

    Note on the third path: supplying case_number explicitly in the POST body does not produce a unique violation either — the value is ignored and a fresh number is minted (POST with case_number: "CASE-00001" returned 201 carrying CASE-00022). So there is no remaining route by which a create failure can burn a number.

    Half (b) — silent duplicate numbering on a fresh DB: REPRODUCES, unchanged

    Four REST creates on the fresh seeded database, no case_number supplied:

    seeded rows: 38, max=CASE-00038
seed organization_id values: [null]
sequence last_value BEFORE any API create: 38
POST #1 -> 201 case_number=CASE-00001
POST #2 -> 201 case_number=CASE-00002
POST #3 -> 201 case_number=CASE-00003
POST #4 -> 201 case_number=CASE-00004

DUPLICATE MINTED against seed numbers: CASE-00001, CASE-00002, CASE-00003, CASE-00004
duplicate case_number values now in DB: [["CASE-00001",2],["CASE-00002",2],["CASE-00003",2],["CASE-00004",2]]

    Zero 409s, zero warnings, four duplicated business identifiers. This is the 2026-08-05 finding verbatim, on GA.

    And the mechanism is now pinned exactly. The counter is a persisted table, and this install carries two rows for one object:

    _objectstack_sequences where object='crm_case':
  tenant_id='__global__'              last_value=38   <- what the SEED loader minted from
  tenant_id='org_mssymr19xzd645gv'    last_value=6    <- what the REST API mints from

crm_case rows by organization_id:
  organization_id=null                  38 rows  CASE-00001..CASE-00038
  organization_id='org_mssymr19xzd645gv' 6 rows  CASE-00001..CASE-00006

    and the unique index is partitioned by exactly that column:

    CREATE UNIQUE INDEX `uniq_crm_case_organization_id_case_number`
  ON `crm_case` (COALESCE(`organization_id`, '__global__'), `case_number`)

    So: the seed loader writes untenanted rows while the REST path stamps an organization, on a stack whose boot banner reads Tenancy: single. One logical tenant is split across two partitions of the uniqueness index, each with its own counter. Each counter is correct within its own scope — which is why the rc.6 seeding fix (objectstack#6249's complete keyset scan) does not help here: the org-scoped counter scans its own partition, finds it empty on a fresh database, and correctly starts at 1. The defect is upstream of the counter, in who stamps organization_id.

    Not specific to crm_case — confirmed live on a second object in the same run:

    POST crm_knowledge_article -> 201 article_number=KA-0001 org=org_mssymr19xzd645gv
article numbers by org:
  organization_id=null                   KA-0001,KA-0002,KA-0003,KA-0004
  organization_id='org_mssymr19xzd645gv' KA-0001

    Disposition

    Card stays open on half (b). Mirror and nomination posted (linked below).

    Label note: pm:blocked dropped. Its blocker objectstack#5495 is closed as completed via merged PR objectstack#6932 — a card should not wear a blocker that no longer exists. #1153 ties this drop to its non-reproducer branch and this card is a survivor, so I am extending it on that instruction's own stated rationale rather than on its letter; flagging it here so the PM can reverse it in one edit if that reading is wrong. No other label touched, and no target:v17 applied (nomination is a comment, per #8667).

    Generated by Claude Code

    Generated by Claude Code

  10. hotlong
    removed
    pm:blocked
    on Aug 14, 2026

  11. hotlong commented on Aug 14, 2026

    @hotlong
    hotlong
    on Aug 14, 2026 – with Claude
    Contributor

    Mirror and nomination for the surviving half (silent duplicate numbering), per #1153:

    Generated by Claude Code

    Generated by Claude Code

  12. hotlong commented on Aug 14, 2026

    @hotlong
    hotlong
    on Aug 14, 2026 – with Claude
    Contributor

    Closed — moved upstream to objectstack-ai/objectstack#8686

    Dispositioned under the transfer sweep #1156 (part 2). Maintainer ruling, 2026-08-14, quoted verbatim and untranslated:

    hotcrm 席位的原则是用平台的能力做元数据应用的开发，平台的需求应该转给平台

    and for the hotcrm side of a transferred card, the same ruling: 「关掉,只留指向镜像的指针」. Authority: #1156.

    The mirror — verified before this close, not assumed

    objectstack-ai/objectstack#8686 — "Seed loader writes untenanted rows while the REST path stamps an organization — one single-tenant install runs two autonumber scopes and mints duplicate business identifiers, silently (17.0.0 GA)". Confirmed open, labelled bug, and carrying the surviving half of this card with its mechanism pinned: the two _objectstack_sequences rows, the COALESCE-partitioned unique index, and the second-object confirmation on crm_knowledge_article.

    The reading this close rests on

    The GA reading already on this card — comment of 2026-08-14, a live probe on a fresh seeded database, the repro condition the 2026-08-05 follow-up asked for.

    • Half (a), "every failed create burns a number": NOT reproduced. A taken number no longer produces a failure at all — one create skipped a planted nine-number band in a single request, and a genuinely rejected create left the counter unmoved. Consistent with objectstack#5495 → merged PR objectstack#6932. This half needed no mirror and deliberately did not get one.
    • Half (b), silent duplicate numbering: reproduces, unchanged. Four REST creates on a fresh 38-row seeded database minted CASE-00001…CASE-00004 on top of the seed rows — zero 409s, zero warnings, four duplicated business identifiers on a unique field.

    The mechanism is now upstream of the counter, which is why objectstack#6249's complete keyset scan does not help: the org-scoped counter scans its own partition, correctly finds it empty, and correctly starts at 1. The defect is who stamps organization_id on a seeded row, on a stack whose boot banner reads Tenancy: single.

    Why it closes here

    Both write paths are the platform's — the seed loader and the REST ingress. This card's 2026-08-05 triage already recorded that the CRM half has no writable producer in this repo ("hotcrm 侧没有可写的「生产者」"), which is exactly the condition under which the ruling above sends a card upstream. If the platform rules that seed rows may or must carry an organization, a CRM seeding task can be opened fresh against that ruling.

    Label note: pm:blocked was already dropped by the GA probe round and is not touched here; the remaining labels are left as they were.

    Nothing is lost by this close: #8686 is open, states which of the two decisions a fix has to make, and is readable without hotcrm context.

    Generated by Claude Code

    Generated by Claude Code

  13. hotlong
    closed this as completedon Aug 14, 2026
  14. claude
    added
    priority:p1High: required for production / M2
    and removed
    prio:p1
    on Sep 9, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workingpriority:p1High: required for production / M2upstream:objectstackBlocked on / caused by the ObjectStack platform — tracked upstream

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions