Autonumber counter neither syncs to MAX(existing) per tenant nor re-checks on collision — warm-DB creates 409 in bursts, each failure burning a number (25 retries observed)

[yinlianghui]

Part of objectstack-ai/hotcrm#698

Found during HotCRM's 17.0 GA acceptance sweep on @objectstack/* 17.0.0-rc.2, action-sweep phase.

Symptom

POST /api/v1/data/crm_case {...} (no case_number supplied — it's an autonumber field) 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.

Two compounding defects:

1. The counter does not sync to existing rows. The DB already held cases with much higher numbers (seeded + prior data), but the counter started at 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 past the collision zone and the sequence gap is permanent. A caller without a retry loop simply cannot create a record.

Repro guidance (important)

Same-day follow-up measurement: once the counter has ground past the seeded range, the storm stops (later creates succeeded on attempt 1). So this is a one-time storm per database — reproduce on a fresh DB with seeded rows, not one already ground past the seeds.

Impact

Any integration or UI path creating autonumbered records on a warm database hits bursts of 409s and, without client-side retry, hard failures.

Prior related issues (checked, none open on this defect)

• 多租户缺陷:unique 物化为全局唯一索引、无视 tenancy,与按租户分裂的 autonumber 序列自相矛盾(跨租户必然撞号 + 存在性探测泄露) #3696 (closed) — per-tenant autonumber vs global unique index contradiction
• DB-native autonumber sequence (multi-instance correctness) #1603 (closed) — DB-native autonumber sequence for multi-instance correctness
• bug(objectql): 批降级重写时 beforeInsert 钩子对好行执行两次,autonumber 双消耗 #3152 / objectql: 批插入部分成功 API——根治降级重写下 beforeInsert 钩子 at-least-once(#3152 跟进) #3172 (closed) — double-consumption in batch downgrade rewrites

App-side aggravator (seed rows carrying organization_id = NULL, putting them outside the unique-index partition) is tracked on the HotCRM side in objectstack-ai/hotcrm#698 and interacts with the NULL-stamping defect filed as a sibling from the same sweep (see the automation create_record NULL-stamps issue, "Part of hotcrm#700").