Revise Alert model to include both start and end timestamps

[lunkwill42]

In the current incarnation of the codebase, the Alert model has only a single timestamp, and another relation is used to indicate whether an alert is active or not. This is somewhat removed from the original requirements, which were based on NAV - this model does not support the requirement to retain historic information about the time extents of problems (i.e. most problems were detected at a specific time, and were resolved at a later specific time).

To recap the required model, based on NAV's model:

There are two basic types of alerts

Stateful alerts

A stateful alert has a time extent, and represents an ongoing problem. I.e. it has a start timestamp, and either has, or is expected to have at some point in the future, an end timestamp.

The start timestamp indicates when the problem was detected. The end timestamp indicates when the problem was resolved.

• A stateful alert should be considered active as long as it has not yet received an end timestamp.
• A stateful alert should always be listed when fetching a list of currently active problems.

Stateless alerts

A stateless alert has no time extent. It has only a single timestamp, indicating when the alert was generated.

A stateless alert is never displayed on any lists of active problems, only when searching for historic alerts. Normally, when a stateless alert is generated, it is only logged and any user subscribed to matching criteria receives a one-time notification.

Data model

NAV's internal data model for this heavily relies on PostgreSQL's timestamp data types. These do not always translate well into other databases, such as SQLite or MySQL.

NAV uses the end timestamp to indicate several things:

1. A NULL value indicates the alert represents a stateless alert. Only the start timestamp is considered.
2. A value of infinity (which is PostgreSQL specific), indicates the alert represents an ongoing and active problem, that is expected to resolve some time in the future. I.e. this timestamp is updated once the problem is resolved.
3. Any valid timestamp value indicates that this problem was resolved at that time, and is no longer active.

Using this representation enables uncomplicated time-based SQL queries against the data:

• Querying active problems means querying everything that has end_time >= infinity, or even just end_time >= NOW().
• Querying any alert that was active at a specific point in time (A) only requires start_time <= A AND end_time <= A
• PostgreSQL provides the OVERLAPS operator, allowing for simple query statements to find alerts that overlap with some given time period between A and B: (start_time, end_time) OVERLAPS (A, B)

I would really like for this model to be replicated in AAS, even though it would inexorably link it to using only PostgreSQL as the underlying RDBMS. The same design choice was made for NAV many years ago, and has worked reasonably well so far.

As long as only the API is the acceptable way of accessing AAS data, the actual implementation can change later, without causing breaking API changes.

[lunkwill42]

This piece of GPLv3-licensed code from NAV can be copied to implement Django specific support for infinity values in PostgreSQL timestamp fields, by translating infinity and -infinity into datetime.max and datetime.min in Python:

https://github.com/Uninett/nav/blob/44a67a5037305c946eb69666d0a4b3b51ea5cff4/python/nav/models/fields.py#L41-L53

[hmpf]

1. How would infinity be serialized to JSON?

2. Why use infinity when you instead can use datetime.max and datetime.min?

[lunkwill42]

1. How would infinity be serialized to JSON?

A text string? :-)

2. Why use infinity when you instead can use datetime.max and datetime.min?

Well, these are actual dates, subject to timezone magic once you pass them around. I suspect you might end up with broken comparisons if they are used verbatim in the database ...

[lunkwill42]

For reference:

>>> from datetime import datetime
>>> datetime.max
datetime.datetime(9999, 12, 31, 23, 59, 59, 999999)
>>> datetime.min
datetime.datetime(1, 1, 1, 0, 0)
>>>