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:
- 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.
- 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)
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").
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 {...}(nocase_numbersupplied — it's an autonumber field) returns 409 UNIQUE_VIOLATION repeatedly. The server log shows the attempted number climbing by one per failed insert:attempted
CASE-00010→ 409 →CASE-00011→ 409 → … → succeeded atCASE-00039after 25 attempts.Two compounding defects:
MAX(existing) + 1per tenant on boot/seed, or the insert should re-check on collision instead of failing the request.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)
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 automationcreate_recordNULL-stamps issue, "Part of hotcrm#700").