A pretty reliable way to handle concurrency and save WU at the same time

Tips
1

btw I could be wrong; open to suggestions and alternate methods.

One of Bubble’s biggest flaws is handling concurrency.

Say for example you have to display reporting data for sales.

Current sales: $100.

When 2 simultaneous orders are placed, both read 100 and update it at the same time.

So the values from 1 order get missed.

Solution(this solution also helps you save workload units. Esp for for datatypes with 100k+ records)

Step 1

Have an aggregate datatype.

Eg ReportingDaily(Field - Total Sales: 100, Date label: Date Text)

Every time an order is placed, create a “NEW” thing in the table.

Whenever you are displaying your sales, do a sum of all the Reporting Daily’s > Total Sales.

Step 2

Set a common daily recurring wf that runs at a certain time.

Preferably when there is less activity.

At this time, roll up the entries of the previous day.

Rollup means taking all the entries for the date label of the previous day and SUM them.

Then delete the other entries. This should leave you with 1 entry for that day.

So if you have about 10k orders on that day, you only need to look at 1 entry.

This is missing a lot of the nitty-gritty stuff, but that you can figure out.

Hope this helps.

2

This doesn’t save WUs, it adds a considerable amount of WUs. It also doesn’t do anything to solve concurrency issues as far as I can tell, since no mention of how system is stopping concurrent writes to DB having same values at write time.

What was the use case and problem to solve?

3

Yeah my bad, I missed a lot of context.
Use case: This is for a seller dashboard, not the buyer-facing checkout/dash. Some sellers have 100k+ orders, and the dashboard shows gross, net and profit over different date ranges. Searching and aggregating the raw Orders every time the dashboard loaded was slow and expensive.

On WUs: The saving is on the read side. Without the rollup, every dashboard load searches over every order in the range. With it, past days are one pre-summed row per day, so a yearly view reads about 365 rows instead of 100k+ orders.
Writes and the rollup happen once, but reads happen many times a day, and that’s where it nets out lower for us.
For apps that read totals rarely, or doesn’t have high-volume sellers, it won’t save anything and you shouldn’t use it.

On concurrency: I’m not claiming it fixes concurrent writes in general. It avoids the problem in this one scenario by never doing a read-modify-write on a shared field. The usual approach is a single “Total sales” field that each order updates.
Here, each order only creates its own entry and never edits a shared record, so there’s nothing for two workflows to overwrite. The total is computed when it’s read (sum of entries). The only step that writes a combined value is the nightly rollup. It runs once, as a single job, over a day that’s already closed, so nothing else is writing to those records at the same time.

So it’s really an append-only ledger with a periodic rollup.

Is there a better way to do this?

4

Yes, it’s similar though in sense you do a nightly rollup as you describe it, but it allows you to keep all details of the order so you can provide more features in the dashboard other than a simple number.

You can use a Hybrid Data Structure, so you’re orders are API objects. Save as you do normally into a single data type entry but instead of multiple fields for each detail use one for the API object. Then on your nightly basis add all order api objects to the reporting data type with field for date and field for list of API Objects.

Then if you wanted to add a chart or spreadsheet to the dashboard to filter orders not only by date as you do now to show only the total of all orders you could add filters for product or buyer or any other detail you save for the order.

This gets you WU savings in same way you do now, which is only once per day do you aggregate all orders so to save on fetching multiple orders each time you show a dashboard, since saving API objects as a list field on single data entry only requires to search and return a single entry as you do now to find the sum value.

But if all you want to do for your sellers is show a total per day, the simple number field as you’re doing is fine.

5

Hmm, wouldn’t the payload for that days single reporting entry be massive, thus increasing the WU usage when trying to fetch?
Somedays we have 16k+ orders, so not sure list fields would be the best way.

For other projects I have tried raw json payload in a single text field only 4-8k objects and parsing that on the frontend using JS, but still takes quite a while in terms of page load.

What I did was create a separate aggregate table at the product level. So we get Item level (discounts, gross, net) filters as you mentioned.

Also to clarify API objects you mean an object initialised via the api connector or raw string?

6

this is a really smart approach. creating separate entries first and rolling them up later should help avoid concurrency issues and keep searches faster.

definitely worth trying for larger datasets.

7

16K per seller or total across all sellers?

WU cost for single search of single entry plus the per character cost which is like 0.00003 per character so not much if having the extra data is important, and with API objects you create them so only add the fields necessary.

API objects are different than raw JSON, one because you don’t need to parse, and two you get same built in lazy loading feature of the RG…so, compared to raw JSON api objects much faster if loading large lists, and compared to loading 8,000 data entries of custom data type, API objects are faster. But if comparing loading 100 regular custom data type entries to one entry with list of 8,000 api objects, yes the 100 entires is faster for first 100 but every other set fetched after is slower than API objects as they would already be loaded.

Plus if it’s a reporting dashboard, a second or two of load time doesn’t matter.

So you took values from one data type and created two other data type entries? Is this second aggregate product level so you are creating multiple of them, one for each product? And fetching multiple times from the entire list to fetch for each product? And running backend workflows to process all of this for each product? And do you do all of that for each individual seller?

Initialized in api connector

8

Yes 16k for a seller on 1 day.

Oh nice!! I never knew this, This method of adding api objects as a type expands possibilities I never thought of, for other things aswell

One product daily rollup datatype - The If the product is different it gets its own line item.

yes. listing all product types, and fetching rollups for that type. I am sure there is a better way to do this?
Luckily in our case products are few(split across events), so the nested fetches arn’t too bad for UX.

Yes, didnt want to run into issues in the future. Reliability is of higher concern than WU usage.