How are CiviCase Case ID numbers generated?

Question

I've assumed that CiviCRM simply allocates the next available Case ID number when a new case is created. Certainly it has always looked that way. However, on one site I work with where cases are used extensively, we are seeing case numbering that suggests something else is happening:

Case ID numbers started at 1 and run pretty consistently up to 86, then a jump to 164, then a second jump from 167 to 249, then straight to 332, then from 372 to 617, from 620 to 747, and from 766 to 1059. (Taken from the civicrm_case table in the DB).

On another site, with a comparable number of cases, the case IDs run from 1 up to about 255 (last time I checked) without a break.

I'm concerned that the non-sequential numbering might be indicative of a problem.

What might be happening to make CiviCase create non-sequential case ID numbers?

Answer 1

I am not 100% sure, but there are some peculiarities within the case process. One of them is that the complete set of data is saved at the end of the overall case transaction, and for CiviCRM purposes it is a rather long transaction. And if there is either an error or a cancel somewhere during the whole sequence it might be that a record was created in civicrm_case (allocating an ID) which is then removed later on, but the ID is gone.

If you look at the table it does mention that ID is auto increment, meaning it is sequencing. As a test you might want to try creating a case but then not completing the open case activity and see if it allocated a case ID (if you catch my drift...) We certainly encountered some un-characteristic stuff when creating custom extensions for CiviCase :-)