Improve aggregated data loading speed

[jkleiber]

Summary
The aggregated data page is good, but it takes a long time to load. We should try to make this faster by keeping track of event averages in a separate table. Here are a couple strategies that we could use to accomplish this:

Strategy 1
Recompute a specific team's averages whenever a scouting form is uploaded (or whenever new data is submitted from local sync). This way the event page is fast to load, and the scouting form doesn't take forever to submit since it is only recomputing a single team's data. Additionally, the database would always be up to date.

The downside of this solution is that when data is uploaded in batches (see #23), we would be bottle-necked by each submission needing to update the database

Strategy 2
Maintain another table for the last time an event was updated (or the most recent primary key in the data table that was used to compute the averages in the averages table). Then, whenever the event data page is loaded we would pull any new scouting data, update the averages table for any applicable rows, and update the latest time/primary key in the table tracking data staleness. This way we would not need to change anything about the scouting form, but the event data page will load a lot faster since the number of rows to process will be way less. This would also be easier to test with VABLA data since it fulfills the requirement of having a way to manually refresh up to the current newest data.

The downside of this solution is that the data in the database is not up to date unless a user tries to view it. Sort of a Schrodinger's cat thing here, plus there's a chance that we somehow fail to keep the primary key up to date, causing missing data in the averages, or slow load times.

Scope

• Add a table in the database for team averages
  • The columns will be team number, event ID, number of matches, and all the averages we are interested in computing
• Recompute a team's event average based on one of the strategies above.
• Make a tool or page to recompute the table manually in case we get out of date somehow. This could be useful for migrating the data from VABLA to the table
• Update the event page to load data from the averages table rather than doing the full computation every page load

[linglejack06]

@jkleiber migration tool repository is here: https://github.com/linglejack06/average-integration

[jkleiber]

@linglejack06 is there any value in adding that into our repo here in a utility folder or something?

[linglejack06]

@jkleiber it would be useful for when we run into issues with internet and have to QR code scan the data into scout data table. All you'd have to do is run it for that event after all data is loaded into scout data and it would recalculate all averages.

[jkleiber]

Yeah could we add it to the site as a page that you can go to if you know the route? But not included in the title bar so people aren't accidentally clicking it and getting lost

[linglejack06]

Yeah as like a /api/updateAverage/:eventName

And just an empty post route or post with the data?

[jkleiber]

Yeah that makes sense to me

[linglejack06]

@jkleiber I went ahead and built an express server to post to /updateAverages. It is not connected to the scouting app ( as with the way react app was designed it would be hard to connect express). But it is currently deployed seperately through https://copperscoututil.onrender.com

to update averages one would POST to https://copperscoututil.onrender.com/updateaverages/{event name}