Problem statement

Each alert in the database has a unique alert_id and a non-unique fingerprint, which separates a UNIQUE alert from its appearance. This allows us to track minor changes in alerts. Currently, in the UI, we always show the last appearance of the alert grouped by fingerprint.

AlertToIncident many-to-many relation right now uses alert_id as a key, and there is a runtime logic that assigns all alert_ids with the same fingerprint to the incident when we assign the last appearance. This leads to a huge size of the AlertToIncident table.

More of that - we still need to group alerts back by fingerprint to show the last one in an incident UI.

Current database structure

Assumption

If we will refactor AlertToIncident to have a relation via fingerprint we will dramatically reduce the amount of entities in AlertToIncident tables.

Also, to simplify finding a last appearance we want to introduce a new table latest_alerts which will contain the fingerprint and last alert_id. This will allow us to avoid grouping by fingerprint to get last alert appearance

Target database structure

For smooth migration, we suggest making a new table FingerprintToIncident with a new structure, then performing data migration, and then drop an old AlertToIncident table later