Fixometer page - download of item records available to everyone

[ngm]

You can currently 'Download all data' only if you're an admin.

Everybody should be able to download the repair records data.

It also takes a very long time to download the dataset due to its size now, currently resulting in 504 Gateway Time-out.

[edwh]

Done in #926.

What it does

• Download all data is shown to everyone on the Fixometer page. The download route was already open. Each person gets the records they're allowed to see, under the same rules as before: approved events of approved groups, plus unapproved ones for their own groups (hosts) and their networks (coordinators), and everything for admins.
• Speed. Before, the export loaded every record into memory and then looked up each record's event and group one at a time. Now the visibility rules are part of the database query, events and groups are loaded along with the records, and it works through the records 1000 at a time. The number of database queries no longer grows with the number of events.
• Deleted events are left out. Before, one deleted event could make the whole download fail.

For discussion

1. How fast it is now on the full dataset. Not measured yet: the preview of Data engagement: reporting links, group stats, Fixometer fixes (#919) #926 (restarters-pr-926.fly.dev) restores the production data, so it can be timed there.
2. If it's still too slow, the next step would be a file rebuilt on a schedule (e.g. nightly) and served straight away, at the cost of the data being up to a day old.

[edwh]

Timed on the #926 preview, which restores production data: the full Download all data export as an anonymous user took 64 seconds for 269,365 rows (40 MB). The timing was taken from inside the preview machine, so it doesn't include download time over the internet.

The file is built completely before the download starts, so there's a wait of about a minute with no visible progress. If that's still too long, two options:

• Stream the file. The download starts at once and fills in as rows are produced. It takes the same total time but gives immediate feedback.
• Pre-build the file on a schedule (e.g. nightly) and serve it instantly, at the cost of the data being up to a day old.

I haven't timed the old code on the same data for comparison.

[ngm]

@edwh Great, thanks.

I'd opt for daily pre-built file. Save a (little) bit of compute and bandwidth, and no problem with the data being a day old for this purpose. Streaming also fine for me if preferable.

[edwh]

Done in #926, as a daily pre-built file.

What changed

• The full download is now a file the web server hands out directly, at /exports/repair-data.csv. It doesn't go through PHP, so it starts at once and is compressed in transit.
• The file is rebuilt at most once a day, and only if repairs, events or groups have changed since the last build.
• The rebuild check runs hourly. This matters because a newly deployed server starts without the file and gets one within the hour. Until it exists, the button falls back to the old live export, so it never breaks.
• Everyone can download it, not just admins. It contains what anyone can see: repairs from approved events in approved groups.
• The per-event and per-group downloads still work as before.

Checked on the preview with production data: building the file took 44 seconds and made 40 MB. The file is served as compressed CSV without going through PHP. A second run straight after correctly skipped the rebuild.