Typical DB’s utilize BEGIN TRANSACTION to ensure multiple table updates required to complete a “transaction” complete entirely. If the entire set of DB writes do not complete it allows for ROLLBACK, which restores all the tables affected hence guaranteeing referential integrity.
It seems Bubble does not support this even though PostgreSQL includes this functionality.
Am I missing something? If I am correct I suggest this is a significant flaw in Bubble’s DB functional architecture. If wrong, could someone please show me where to find this or how to activate it.
You are correct there is no transactional commits so this is a deal breaker for any app where data integrity is of a serious concern (i.e. financial data).
The best you could do is query for the data to see if it is available in some further workflow but that still isn’t a guarantee if the workflow dies on you.
Typically you must “inform” the DB to start a trascation via a SQL instruction. There or no such workflow elements for this, so my hypothisis is that it is not implemented at all.
Why is this important? Two reasons
If a multi table write is needed and one write fails in the sequence all can be lost. For example: a person buys a widget, this requires multiple steps.
Deduct from inventory
Allocate to order
Create Invoice
Allocate to order…
I am sure you get the idea, but lets say it fails after step one, now the inventory is wrong and no payment done… this is just a simple example and not intended to be perfectly accurate.
We are dealing with real-time online systems which allow simutineous multi user access to tables. Normally, when we start a “transaction” the DB handles the table locking to prevent other users from getting to the table before we complete all the steps in the transaction. This a real concern if the write action is an update to an existing record that two users need to hit at the “same time”. In the above example if 2 users are allowed to hit the inventory table at the same time it is possible for the inventory to go negitive. DB’s handle this by creating table locks that timeout if needed. Transactions handle all of this for us. The DB does not lock the table(s) until it has captured the entire sequence of writes (typically is SQL form) then it executes them all at once. It first locks all the associated tables, does the writes, then unlocks the tables if ALL the writes are successfull - or it can ROLLBACK then unlock the tables. This ensures mult-table referencial integrity and data correctness.
Kinda, thought so… BTW your said – “financial data”. It seems to me that every app that is designed to sell something, and needs to track payments ect… is at risk.
To be fair; I would enjoy if Bubble could jump in on this. Maybe they already have this under control but since the DB implementation is under the covers, I can’t tell.
John
Most apps on Bubble (and arguably on other platforms as well) use a payment provider like Stripe. Stripe is able to handle failures gracefully and uses web hooks and other such methods that ensure reliability and therefore doesn’t present the same problem. If you are handling payments directly within the app, then yes, it would run the same risk.
Yes, we use stripe, and yes we leave it up to stripe to ensure the secure payment. However, we do store “financial” information locally so that there is real time integration with Quickbooks. So, yes there is a risk that things can get out of sync. We do get around this by logging every finacial transaction in a GL file using double entry accounting (GAPP) processes so we are confident we can meet our audit requirments and if necessary back-create any transation. That is us; and we are lucky to have an Accountant and MBA’s on the design team. Not sure if most no-coders are as foretunate. Have a great day. J
