I’m working with a data model that includes a main ‘box’ which contains multiple ‘sub-boxes’, and each sub-box can contain several ‘documents’. This is all being managed through Supabase.
When a user submits a box, my current workflow involves multiple steps: inserting the main box into a ‘boxes’ table, looping through to insert each document into a ‘documents’ table, and similarly looping to insert each sub-box into a ‘subboxes’ table.
I’m concerned about the reliability of this multi-step process. If the user’s internet connection drops during one of these loops, it could leave the database in a half-baked state - with a box that has no documents, or sub-boxes that are incomplete, for example.
How do you all manage such scenarios? My understanding is that Supabase does not support grouping inserts into a single PostgreSQL transaction out-of-the-box, and creating a custom edge function to handle this is beyond my current skill set. Is there a way to implement a robust solution directly within WeWeb, or perhaps is there another approach that’s recommended for ensuring data integrity during these kind of multi-step insert operations?
So, I have figured out how to use the REST API with Supabase.
I can see that from Supabase, I can create an SQL function which could take data and insert it into multiple tables as a single transaction (usual Postgres function).
Then, I can pass the data in json format in the body of a POST request from a WeWeb workflow.
I do not really know how I will do that, and how to retrieve an error on the front-end, but I think I am on the right track… If I manage to do it I will update my post for future viewers.
Actually, soon there is gonna be a release by @Alexis with the new Supabase plugin, that will allow you to do these bulk INSERT/UPSERT/DELETE actions from WeWeb.
You’re right, this loop is actually not the best solution, as not only (as you mentioned) it could be not reliable in some cases, it also can become slow, if you have 20 records you’re looping through, and each one takes some time to come back, you’ll get quite some latency.
If you wanted to avoid using PGSQL functions, you could tap into the Supabase SDK (which in my opinion is a more simple task - as the code is almost human readable).
