Duplicate invoicenumbers when using Sequential Paid Invoice Numbering feature

[niels]

Problem:

When using the Sequential Paid Invoice Numbering feature WHMCS will assign an invoicenum (in addition to the id) when an invoice gets paid.

It appears that, when WHMCS receives a lot of transactions at the same time, it will happily assign the same invoicenum two, three or more times.

This is not a good thing, for obvious reasons.  As WHMCS shows id, not invoicenum, in most places it's easy to go unnoticed.  If you're not processing a lot of transactions in batches, the occurrence may be very rare too.

The database schema does not enforce uniqueness on the invoicenum column and WHMCS doesn't seem to do any double-checking after it inserts the record either.

 

Solution?

Any suggestions how to resolve this? I've considered changing the database schema to require invoicenum to be unique. But this might cause WHMCS to simply throw an error. And it's difficult to test what will happen as I can't reliable reproduce the problem.

 

Related:

I found this old thread with a similar problem: 

Which in turn also links to this issue: 

 

 

But neither have a satisfactory resolution. For all intents and purposes the feature is currently not usable and may get those that are not aware of the issue in trouble with their accountant or worse.

[Kian]

3 hours ago, niels said:

The database schema does not enforce uniqueness on the invoicenum column and WHMCS doesn't seem to do any double-checking after it inserts the record either.

WHMCS simply assumes that Paid documents are invoices and Unpaid ones are proformas. For WHMCS they're the same thing. There's nothing that marks a document as an invoice rather than proforma. That being said, it's obvious that there's no checking involved on invoicenum. This field is just a "friendly name" and nothing more.

In order to implement a checking mechanism on invoicenum, first you have to "explain" to WHMCS the concept of invoice and proforma not to mention credit notes. As if it wasn't enough you can't enforce uniqueness of invoicenum. In several countries having duplicated numbers is perfectly fine (eg. Invoice #100 of 2018, Invoice #100 of 2019, Invoice #100 of 2020 etc.). It's more complicated than you think 😔

3 hours ago, niels said:

It appears that, when WHMCS receives a lot of transactions at the same time, it will happily assign the same invoicenum two, three or more times.

I run into a similar issue long time ago therefore take it with a grain of salt. I suspect that when you are bulking transactions (cron job, importing third-party software etc.), WHMCS doesn't "refresh" Sequential Numbering when a new invoice is created. Instead of doing this:

1. Payment 1 received
2. Retreiving Next Paid Invoice Number. Found #90
3. Invoice #90 issued
4. Payment 2 received
5. Retreiving Next Paid Invoice Number. Found #91
6. Invoice #91 issued

It does this:

1. Retreiving Next Paid Invoice Number. Found #90
2. Payment 1 received
3. Invoice #90 issued
4. Payment 2 received
5. Invoice #90 issued
6. Payment 3 received
7. Invoice #90 issued

Basically it sticks to #90 and uses it for all the involved invoices. Of course I can't double-check it since I can't see the source code of WHMCS. It could also be caused by the code / module / gateway you're using to process payments in bulk.

[WHMCS John]

Hi @niels,

Which version of WHMCS are you running please?

We made changes in 7.7.0 to help mitigate against a simultaneous payment notification race condition, and haven't had any reported incidences since:

Quote

CORE-8289 - Optimize sequential invoice increment logic to mitigate race condition

https://docs.whmcs.com/Changelog:WHMCS_V7.7.0_RC_1#Maintenance

[niels]

We run 7.9.2 as of yesterday. The last duplicates that I've found occured while using 7.9.1 and 7.9.0.

[mystica555]

I don't know if I'm having the same issue.. I am getting support tickets from customers saying that they are receiving invoices from us for services they dont have, the numbers match somewhat near what our current invoice numbers are, but yet are totally different invoices when I check.

I'm trying to get one of the customers to send a full headers included email back to me so i can try and diagnose where things are coming from.