Investigate alert system not triggering alerts

[hellais]

There are some recent cases in which the censorship alert system has not generated alerts. It's unclear if this is due to wrong settings in the h thresholds or if it's a bug in the cusum implementation.

Here is a concrete example of it: https://explorer.test.ooni.org/chart/alerts?probe_asn=AS20294&probe_cc=UG&domain=twitter.com&since=2026-01-01&until=2026-02-12

Here is another example of where the alert system did not generate a trigger when the censorship method changed from one protocol to the other: https://explorer.test.ooni.org/chart/alerts?probe_asn=AS39028&probe_cc=RU&domain=www.instagram.com&since=2025-12-07&until=2026-02-04

[hellais]

Taking note here of some findings while attempting to debug this.

A a big problem is related to fetching the previous CUSUMS in order to support online processing

• There is a series of issues related to handling of timezones in the timestamp columns. The clickhouse driver performs a series of transparent transformations to columns as can be seen in the several timezone related issues reported: https://github.com/mymarilyn/clickhouse-driver/issues?q=is%3Aissue%20state%3Aopen%20timezone. So far I have found that stripping the timezone from the last_df lookup seems to address part of the issue: https://github.com/ooni/data/blob/main/oonipipeline/src/oonipipeline/analysis/detector.py#L62
• There is a bug in get_lastcusums where last_value_respect_nulls is used together with ORDER BY DESC instead of ORDER BY ASC
• Still even with the above patch, in a controlled environment the last_cusum is still not being loaded accurately. Specifically it will sometimes skip one CUSUM even if it's present in the table.
• The ts is missing from the event_detector_cusums table, which leads to rows being collapsed in a way that's not entirely predictable

Finally the Uganda case is not being caught since twitter.com is no longer inside of the test-lists as a GRP categorised domain, hence we aren't triggering any alerts.

I have a branch where I have addressed these problems, but it's still fully working predictably. I will update this issue as more progress is made.