Can I make a backend workflow (that is triggered by more than 1 user) execute the actions in sequential order? Like in a queue or something, not in parallel
Here’s what I have:
A user (or multiple users) can create an invoice, each invoice has an ID (INV-0001, INV-0002, INV-0003… etc) that is calculated at the moment of creation, there can not be invoices with the same Invoice-ID.
When an invoice is created, I do an API call to get the last invoiceID (I do this with a search for and sort by InvoiceID), then I just add +1 to that number and that’s the new invoiceID assigned to the newly created invoice.
HOWEVER, when 2 users (or more) create an invoice at the exact same time, their invoices will have the same number. This is because the action responsible for getting the last invoice, are both executing at the same time!!
How can I have this not execute in parallel or avoid the duplicates at least?
it would be pretty unlikely for users to start the api workflow at exactly the same time and get the same invoice number - especially if you are doing invoice numbers per account.
generally I’d also store the invoice number as a number on an “account” data.
ie create invoice for account (user account/company), get accounts invoice number+ 1, update accounts invoice number +1
this achieves a few things
1 - account is known so no need to search for it ie faster response
2 - invoice number is known and not needed to be calculated - faster response
3 - invoice numbers are sequential even if invoices are deleted - standard accounting practice
account in this respect is a users account ie your account at quickbooks.
an account may have multiple users but all the users to the account only see that accounts data/invoices and cannot see other accounts data. the invoice number is also counted per account.
you’re doing
search for all invoices > invoice number >truncated > converted > max + 1
I’d suggest
current users account > invoice number + 1
Something that I didn’t mention is that users can schedule invoices, and they’ll be at specific times like 12 o’clock. Invoices can also be recurring (like a subscription) which just increases the odds of more than 1 invoices coinciding at the same time.
You can see how this becomes a problem as more users use this feature and the number of users increases in the app. Currently I’m at a point where about 1 out of 3 invoices have duplicate ID’s because they coincide at the exact same time
There’s really no way in Bubble to guarantee non-duplicated sequential numbers, due to race conditions, as there is no way to utilise database-locking.
So anything you do here will never fully eliminate the risk of duplicates.
There are some ways to reduce the likelihood significantly, but you can’t get away from race conditions so there’s still always a chance of duplicates.
The real questions are:
why do you need to use sequential numbers?
What does it matter if there are duplicates?
Can’t the invoices be specific to Users (still sequential, but just in relation to a User rather than the entire database)?
Is it worth the considerable time and effort trying to build a complex queuing system to mitigate the risk of duplicates, rather than just creating a system where duplicates aren’t a problem.
If you really do need unique sequential numbers, then an external database is the solution where you can use transactions and database locking.
Alternatively you can build some kind of que-system, with additional checks after items are created, which can significantly reduce the likelihood of collisions (but anything done on the Bubble database is still subject to race conditions).
the issues you’re having with duplicate invoice numbers are a workflow design issue.
yes, bubble has race conditions and yes you’ll have issues if you try to do scheduled invoices that all create on the 1st of the month using an api workflow on a list since that processes multiple at the same time…
but you can work around that in workflow design
just use a recursive workflow to do 1 invoice at a time
if it’s still a problem after fixing the scheduled invoices (I doubt it if you have unique invoice numbers per account since there will only be 1 or 2 users making invoices in real time per account and the chances of them both hitting the create button at the exact same time are extremely slim). then you can build a simple queue system to fetch the invoice number - although this would be overkill for 99% of invoice apps.
I liked your solution however, I mainly struggle with the queue. Our users can bill their clients on subscription payments (think Spotify, Netflix, etc.)
The problem comes when users create a subscription. For context, the invoice for next month is created at the moment the current one is paid, the workflow checks if it’s from a subscription, then it creates the invoice due next month and schedules the next payment to charge on the due date of that invoice which is next month.
All of those invoices get scheduled at 12am, the problem happens when occasionally one of our users signs more than 1 client on the same day of the month. Which I found is not that uncommon and when it does happen 1/3 times there will be a duplicate invoice number
How do I add those to a queue since all the invoices execute independently?
The solution that I implemented in the meantime is I just schedule out for 30 days later PLUS a random number of seconds between 1-30. It’s a solution for now but it doesn’t follow good design exactly.
