Clarifying data/state tables from event tables

[frgfm]

I've been thinking about the relevance of some of the early tables we created. I identify the core purpose of the backend as:

• identifying start & end of wildfires events
• provide live monitoring during the wildfire (access latest images/prediction)

Now think about the tables & columns involved in each stage:

• each device needs to be registered in the API (access or device table) with each location & orientation (potentially it's site)
• when a device detects a wildfire (locally a buffer/confirmation mechanism is possible), using its authentication, it needs to send the image. To simplify, I'd argue that if the device can't send the image, it can be used for training/data collection later, but it's no use for firefighters.
• on the backend, the image is uploaded to S3 & generates a URL for it. It creates an event at the location of the device, using the current timestamp
• for monitoring purposes, in the device table, each device should have last_ping, and last_image (url to S3)

Now since we have a lot of noise between events & alerts, I suggest considering the following table with their columns:

• groups, accesses, users & sites (rename stations?): same as before
• devices: id, access_id, login, group_id, current_site_id, last_ping, last_image_bucket_key, lat+lon+elevation+azimuth+angle of view
• alerts: id, device_id, image_bucket_key, detection_localization, created_at (the device gives lat+lon+azimuth & cie) Finally, I think we need to rework the event table and simplify it considering that it only aggregates the alerts from a single device (i.e. if we have 4 devices on a site, we can have 4 events at the same time):
• events: id, device_id, type, first_alert, last_alert, created_at (the last_alert gets updated by detections, and type by firefighters)

(we remove the legacy media, installations & notifications tables. and I think we should merge webhooks & recipients)

In order to see more clearly, I can set an APM like PostHog to be able to explore events. That should help us figure out which tables are still relevant afterward.

What do you think?

[frgfm]

Also, we definitely need to add authentication requirements on notifications & recipients routes if we keep them :sweat_smile:

[blenzi]

Both are protected for admin scope. You probably didn't notice here and here.

[frgfm]

Oh I didn't see the router-level dependency, my bad :+1: