Saving complex data sets in Bubble

Questions
1

I’ve been working on a problem for some time now and it’s been giving me some problems. It revolves around best practise when it comes to saving complex data sets in Bubble, and trying to reduce workload as much as possible. It’s a bit of a time consuming one to explain, so forgive the long message -

First, to describe my app and data structures a little bit.

Users complete ‘Tests’ in the app, which generates various summary metrics in return. I’ll call these summary metrics A, B and C, as well as a ‘Topic’, which is a category for the test. There are maybe 15 topics in total.

So a given ‘Test’ in the database would possess:

Date User Topic Metric A Metric B Metric C

A ‘Performance Summary (PS)’ in the database summarises the scores for a collection of these ‘Tests’ for a given ‘Topic’. So a user would have a series of these PS’s, always one per ‘Topic’, and each would have a series of summary metrics based on the underlying ‘Tests’. I’ll call these summary metrics X, Y and Z.

So a given ‘PS’ in the database would possess:

User Topic Metric X Metric Y Metric Z

Each time a user completes a ‘Test’ for a given ‘Topic’, the app needs to generate a new PS for this ‘Topic’. That process involves taking all the ‘Tests’ completed in the preceding 8 weeks (now including the newly completed test), and re-calculate metrics X, Y and Z for the ‘Topic’, before overwriting the old entry.

The challenge is that a user might submit 6 tests in a single sitting (each a different topic), and upon submitting their results, the app needs to generate the summary metrics for each of the 6 new tests (metrics A, B and C), before then recalculating the PS’s for the 6 topics (X, Y, Z).

Of course the data objects are also significantly more complicated that indicated here, each possessing multiple summary metrics - not just the 3.

What I need is a very efficient way to process all this data and save it all back to Bubble. It doesn’t need to be instant, so backend options are on the table.

I do have a working solution, but trying to prevent issues at scale. Any thoughts, feedback greatly appreciated!

2

Does that working solution involve having to search and fetch all previous tests and metrics again to re-calculate everything when one new test is submitted? If yes, perhaps, having a separate data type that holds onto the aggregates so you do not need to search ALL previously created tests/metrics and instead just look to this other data type with aggregates and add to it the new test and calculate off of that.

The idea is the other data type will have fields like ‘number tests completed’ as a number, ‘a field for a metric you track’ as a number and after updating these aggregates based on the new test submission, you run the math on those numbers. This will avoid the WU costs with fetching large data sets just to calculate from them.

3

In answer to your question, yes - each new submission requires searching and fetching all previous tests. The problem with saving the aggregates and adding to them is that as well as adding the new test to the results, the app must also remove any tests that have fallen out of the 8 week window since the last time this PS was created.

So the current process is to re-search for all tests performed in the past 8 weeks, which will include the newly created test and exclude any outside of this window. There might well be a more efficient way to achieve this….

4

There are ways to make it so that you do not need to fetch multiple records (each record returned costs WUs and is where most of them are used and why search is expensive). You can just have a single record that holds onto text or api objects and work with those to store the tests and the PS for the past 8 weeks.

If using api objects as a list field you can have on each of those objects a date and use it for filtering and do not need to remove older tests and can just filter them out for the PS values. You can set it so that you maybe just create a new record each week on a regular interval and just save the api objects to the field for that record week period, and when search to calculate fetch only the records for the past 8 weeks (ie: 8 records with list field of api objects) and you’ll have the complete test object to work with, but only fetch 8 records.

If you are not concerned with WUs at all, then the issue is simple to resolve for and sounds like you have a working solution and are not concerned with the time to process, so I do not see why that wouldn’t work at scale since the processing time is per test. You could end up with issues for the PS and aggregation as it might be getting written to multiple times simulatenously.

It might be best, to just schedule a weekly or daily run to aggregate and save PS once, so it is not done after each test submission. Or if this is just for Admin, only run the aggregate when the admin requests it from the dashboard.

Lot of different options, just depend on use case of the data and focus on what is an issue of concern with an already working solution.

5

@boston85719 can you explain what you mean when you refer to API objects?

I think using an API workflow might be a good approach. Current method uses database triggers and custom events.

6

In Bubble we have four data types we can work with.

  1. Custom Data Types
  2. Option Sets
  3. API Objects
  4. Plugin Objects

API Objects and Plugin Objects get lumped together as a category by Bubble, although they are constructed differently. This is because API Objects are created by initializing an API call via the API connector which is a Bubble plugin, and because all Plugin Objects come from Plugins that initialized an API call within the Plugin editor.

Majority of developers are comfortable working with, and usually only work with, two data types, the custom data types and option sets. I refer to this as a legacy approach.

Developers can use what I call a Hybrid data structuring approach and make more use of API Objects and Plugin Objects in their data structuring. I started using this approach to begin with for the purposes of reducing the WU charges incurred when fetching and returning data.

You can create these without an actual API call to make. Just use the API connector to manually initialize an API call and paste in the JSON structure of the data, and set the field types during initialization so you can dates, yes/no and even option sets as the ‘type’ of data the key value would be determined by Bubble so you can properly use them in dynamic expressions and have access to all appropriate operators so you can manipulate those values as needed in dynamic expressions.

I have a plugin called Data Jedi that makes the process of working with API objects easier. It is a paid plugin, that has a lot of features that make it valuable other than the way it makes it simple to work with API objects. Some of the things it does that makes it easier to work with API objects that other plugins that enable the use of JSON as a way to ‘create’ API Objects for use in dynamic expressions, is that it allows you to target the objects by fields of your choosing, and can even create the unique IDs for you. Most other JSON parsers just use JSON standards like referencing a index value (which starts at 0 unlike the bubble repeating group that starts at 1), or from my experience just make it more complicated than it should be in a NoCode environment like Bubble to work with.

There may be some free plugins out there that are able to make it easier, but I’m not sure of which ones those are.

API Objects basically can then be saved into the database via a custom data type data field. That means you can save an entire object as a single field on a custom data type. The main benefit as it is in regard to reducing WUs for returning data, is that you can save those api objects onto a list field on a custom data type. Same limitations and rules apply here, which is a data field on a custom data type that is a list field can only hold onto 10K list items. This on it’s own is not really an issue, since there are numerous ways you can ‘categorize’ your api objects to split them up in the event that you expect to need more than 10K.

If you are not familiar with JSON, I suggest asking a couple of questions to ChatGPT to get as much of an understanding as you would need, which is the basic principles of how to properly structure the JSON, which is really not hard to remember and understand. Then check out some free plugins that make it simple to parse JSON and try your hand at using them to power the creation of api objects that can be used in dynamic expressions. If you find one that fits your needs, awesome. If not, consider checking out my plugin.