AE trigger watermark can silently halt forever (F8/AE-9) — bounded-lookback + poison-clamp + metrics

[ConstanzeTU]

Blocker (public-release): the trigger watermark can silently halt AE forever

adaptive_export's kubescape trigger gates on a strict forward-only high-water-mark of the content
event_time (internal/trigger/clickhouse.go: WHERE event_time >= watermark, watermark advances to
maxSeen). Any anomaly with event_time < watermark is dropped and never re-queried. This is the
production "writes succeed, then stop, data still on Pixie" failure.

Evidence (real PG runs — loadtest E8, FINDINGS_AND_BACKLOG.md F8)

• Watermark poisoned at a leftover nanos value 1781559619170395824; 25/25 ticks of fresh seconds
  anomalies → n_anomalies stayed 0 (writes stopped; Pixie + data-plane fine). Evidence e8_steady.csv.
• Recovery required a manual ALTER TABLE trigger_watermark DELETE WHERE 1=1 + AE restart; then
  n_anomalies grew 1→2→3→4, delta=1/tick. Evidence e8_recov.csv.

Three failure modes (all reproduce the halt)

1. Unit heterogeneity (catastrophic): one nanos/millis row → watermark ≈ 1.78e18; every later seconds
   row (~1.78e9) is < watermark → dropped forever.
2. Clock skew / out-of-order: a late/earlier-stamped anomaly after a newer one → dropped.
3. Restart re-scan: on reboot the persisted/max-existing watermark buries anomalies stamped below it.

A partial mitigation already landed (normalizeEventTimeNanos, trigger/clickhouse.go:205,285) — it
reduces the unit-poison odds but does NOT fix skew/out-of-order/restart drops, and there is no metric
to even see a halt. This issue is the durable AE-9 fix.

Targets (acceptance criteria)

• T1 — no permanent halt: after the worst-case poison (a 1.78e18 row) or an out-of-order/skewed row,
  the trigger self-recovers without any manual ALTER TABLE … DELETE + restart. Re-run E8: fresh
  seconds anomalies must flow within one lookback window.
• T2 — late arrivals captured exactly once: an anomaly arriving with event_time within a bounded
  lookback of the watermark is processed exactly once (no drop, no duplicate).
• T3 — observability: new metrics ae_trigger_watermark_ns (gauge), ae_trigger_below_watermark_total
  (rows seen below the prior HWM — i.e. would-have-been-dropped, now captured), ae_trigger_event_time_rejected_total
  (poison clamps). A halt is visible + alertable.
• T4 — no happy-path regression: the monotonic-exact path stays exact (E1: 71/71 then 20/20, std=0).

Planned change (stacks on PR #92 branch fix/ae-protocol-export-pxexport)

1. Bounded lookback + content-dedup (internal/trigger/clickhouse.go): query
   event_time >= max(0, watermark - LOOKBACK_NS) instead of >= watermark; extend the existing
   boundary-fingerprint set into a bounded LRU covering [watermark-LOOKBACK, watermark] so re-seen rows
   are deduped. New env ADAPTIVE_TRIGGER_LOOKBACK_SEC (default 300; 0 = legacy strict HWM). Catches
   skew/out-of-order/restart drops (failure modes 2+3).
2. Wall-clock poison clamp (failure mode 1): after normalizeEventTimeNanos, reject any event_time
> now + MaxSkew (default 1h) from advancing the watermark; count it in ..._rejected_total. A single
   1.78e18 row can no longer jump the cursor past all real data.
3. Metrics (T3) wired in the poll loop.
4. Keep normalizeEventTimeNanos (AE-2 partial) as the first line; this stacks on it. AE-2 (remove the
   auto-detect, enforce one unit) tracked separately.

Trade-offs: each poll re-scans the lookback window (bounded by PollLimit + window); the dedup set is a
bounded LRU. Both configurable; LOOKBACK=0 preserves today's behavior.

Test plan

• Unit (trigger/clickhouse_internal_test.go, watermark_test.go): late-arrival-in-lookback captured once;
  below-lookback dropped (documented bound); 1.78e18 poison row rejected + watermark stays wall-clock-bounded
  • subsequent seconds rows still processed; each metric increments.
• Rig re-run of E8 on a live PG: poison the watermark, fire seconds anomalies → with the fix, anomalies
  flow with NO manual reset (T1). Capture e8_recov equivalent.

Non-goals

• Switching the cursor to an ingest-order column (inserted_at DEFAULT now64()) — a heavier schema change,
  deferred; bounded-lookback+clamp achieves the targets without a kubescape_logs schema change.
• AE-2 (unit standardization / remove auto-detect) — separate issue.

[ConstanzeTU]

Validated live on rig 6a81a7a6 (aeprod64) — E8 poison → automatic recovery ✅

Reproduced the F8 catastrophe and confirmed the fix self-heals with no manual ALTER TABLE trigger_watermark DELETE + restart (which the old build required):

1. Baseline healthy: adaptive_attribution growing, watermark tracking wall-clock nanos.
2. Poison: set persisted trigger_watermark = 2000000000000000000 (~year 2033, far > now) for cplane-01/kubescape_logs; restarted AE (loads the poison).
3. Anti-halt (load-clamp) — CONFIRMED: ae_trigger_watermark_ns came up 1.7868846e18 (=now), NOT 2e18 — the poisoned persisted watermark was clamped to wall-clock at load, so the trigger kept polling instead of freezing. (Old build: gauge stuck at 2e18, trigger halted.)
4. Recovery — CONFIRMED: fired fresh anomalies post-clamp → adaptive_attribution 4 → 8, ae_trigger_watermark_ns advanced 12:50:26 → 12:55:49, and the persisted watermark was re-saved to 1786884949563685760 (~12:55:49) — the 2e18 poison is purged, so recovery is durable across restarts.

Metrics live at AE_METRICS_ADDR: ae_trigger_watermark_ns, ae_trigger_below_watermark_total, ae_trigger_event_time_rejected_total.

Note/follow-up: the load-clamp path isn't yet counted by a dedicated metric (observable via the gauge reading ~now vs the poison). A ae_trigger_watermark_clamped_total counter would make the clamp directly alertable — minor, can fold into this PR.