I’m trying to implement a simple application credit system, where users are assigned credits and then they consume them by performing actions in the application.
I have multiple database trigger workflows that fire whenever a user creates a chargeable thing, which tracks consumption in various database tables. Since users can create chargeable things in bulk, these workflows obviously fire asynchronously and many run at the same time.
The part I’m struggling with is that there doesn’t seem to be any way to perform an atomic increment/decrement on number fields in the database, and there is no DB locking mechanism - the only option to update a number field is to do credit = credit - 1 which suffers from consistency issues and stale updates when multiple of these workflows run at the same time (ie, start with 100 credits, perform 5 bulk actions, end up with 98 credits rather than 95)
For now I am forced to keep a table of Consumption items, and then do a search for all -> sum but that’s getting ridiculously slow as the table now has 100s of thousands of records…
Does anyone have a scalable way of tracking credit usage (ie not performing a sum over millions of records) or a way to atomically update a number field?
You can do “offline” locks if you need to. Although I try to avoid them. “Optimistic Offline Lock” to be technical
Yeah, super annoying, and that issue has been around forever (and database consistency used to be on the roadmap before it got replaced).
The “answer” is a recursive workflow here I would suggest. So you keep bulk updates synchronous via a workflow that runs one thing at a time on a list it maintains itself.
The next step up in terms of speed here is to use the data API to do the updates.
Thanks for explaining, glad I’m not missing something obvious here.
The “answer” is a recursive workflow here I would suggest. So you keep bulk updates synchronous via a workflow that runs one thing at a time on a list it maintains itself.
Do you have an example of some more details on this. I assume what you mean is that I can save consumption records and then have a singleton backend workflow that recurses over the records and updates credit fields. What’s the kickoff for this workflow, cron or manual or something else? How do I go about looping when there are no more consumption records to process?
I wonder what percentage of bubble apps have data inconsistency issues but just don’t realise it, I’d guess high 90s…
