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:
- 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.
- 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
- Boot a dev server against a database that already contains cases (seeded is enough, warm-boot re-seed makes it worse).
POST /api/v1/data/crm_case with a minimal valid body, no case_number.- 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
Found during the 17.0 GA acceptance sweep on
@objectstack/*17.0.0-rc.2 (currentmain), action-sweep phase.Symptom
POST /api/v1/data/crm_case {...}(nocase_numbersupplied — it is an autonumber) 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 (devserver2.log from 07:23:51Z onward).Two defects compound here:
MAX(existing) + 1per tenant on boot/seed, or the insert should re-check on collision instead of failing the request.Aggravator (CRM-side seed inconsistency)
The DB contains a duplicate
CASE-00003pair: a seed row withorganization_id = NULLand an API-created row with the org set. Seed rows do not carryorganization_idwhile 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
POST /api/v1/data/crm_casewith a minimal valid body, nocase_number.Attribution
Counter-sync and burn-on-failure: platform (autonumber provisioning).
organization_id NULLon seed rows: CRM (seed data). Filed here with both halves stated; split upstream if the owner prefers.Refs #689