Skip to content

AI-powered virtual executive team — a single coherent executive persona backed by 8 specialist agents (FastAPI + Next.js).

17.7 KB
Raw Blame
"""Alert lifecycle: TTL, live view, expiry sweep, and the new store helpers."""
from __future__ import annotations

import sqlite3
from datetime import UTC, datetime, timedelta
from pathlib import Path

import pytest

from openexecutive.alerts import lifecycle
from openexecutive.alerts import store as alert_store
from openexecutive.alerts.store import (
    bulk_set_status,
    coalesce_alert,
    count_superseded_by,
    get_alert,
    initialize_db,
    insert_alert,
    mark_superseded,
    reopen_alert,
    set_review,
    update_alert_content,
    update_alert_routing,
)


@pytest.fixture()
def db(tmp_path: Path, monkeypatch: pytest.MonkeyPatch) -> Path:
    db_path = tmp_path / "alerts.db"
    initialize_db(db_path)
    monkeypatch.setattr(alert_store, "DB_PATH", db_path)
    monkeypatch.setattr(lifecycle, "_ttl_settings", lambda: (3, 14))
    return db_path


def _backdate(db: Path, alert_id: int, days: float) -> None:
    ts = (datetime.now(UTC) - timedelta(days=days)).isoformat()
    with sqlite3.connect(str(db)) as conn:
        conn.execute("UPDATE alerts SET created_at=? WHERE id=?", (ts, alert_id))
        conn.commit()


def _insert(db: Path, **kw: object) -> int:
    defaults: dict = dict(
        source="triage", external_id=None, severity="medium",
        headline="h", body="b", db_path=db,
    )
    defaults.update(kw)
    if defaults["external_id"] is None:
        defaults["external_id"] = f"ext-{datetime.now(UTC).timestamp()}-{kw.get('headline', 'h')}"
    aid = insert_alert(**defaults)
    assert aid is not None
    return aid


# --------------------------------------------------------------------------- #
# Migration
# --------------------------------------------------------------------------- #


def test_migration_adds_lifecycle_columns_to_existing_db(tmp_path: Path) -> None:
    """A DB created by an older build gains the new columns with defaults."""
    legacy = tmp_path / "legacy.db"
    with sqlite3.connect(str(legacy)) as conn:
        conn.executescript(
            """
            CREATE TABLE alerts (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                external_id TEXT, source TEXT NOT NULL, severity TEXT NOT NULL,
                headline TEXT NOT NULL, body TEXT NOT NULL,
                suggested_action TEXT DEFAULT '', topic_tags TEXT DEFAULT '[]',
                channels_attempted TEXT DEFAULT '[]', channels_delivered TEXT DEFAULT '[]',
                dedup_key TEXT DEFAULT '', status TEXT NOT NULL DEFAULT 'unread',
                created_at TEXT NOT NULL, UNIQUE(source, external_id)
            );
            INSERT INTO alerts (external_id, source, severity, headline, body, created_at)
            VALUES ('x', 'email', 'high', 'old row', 'b', '2026-09-01T00:00:00+00:00');
            """
        )
    initialize_db(legacy)
    initialize_db(legacy)  # idempotent
    row = get_alert(1, db_path=legacy)
    assert row is not None
    assert row.occurrence_count == 1
    assert row.review_verdict == ""
    assert row.last_seen_at is None
    assert row.snoozed_until is None


# --------------------------------------------------------------------------- #
# TTL predicate
# --------------------------------------------------------------------------- #


def test_ttl_monitoring_vs_action(db: Path) -> None:
    monitoring = _insert(db, source="stock", severity="low", topic_tags=["external:stock"])
    action = _insert(db, source="triage", severity="high")
    now = datetime.now(UTC)
    m = get_alert(monitoring, db_path=db)
    a = get_alert(action, db_path=db)
    assert m is not None and a is not None
    assert lifecycle.ttl_days_for(m, monitoring_days=3, action_days=14) == 3
    assert lifecycle.ttl_days_for(a, monitoring_days=3, action_days=14) == 14
    # Fresh rows are live under both TTLs.
    assert not lifecycle.is_expired(m, now, monitoring_days=3, action_days=14)
    assert not lifecycle.is_expired(a, now, monitoring_days=3, action_days=14)
    _backdate(db, monitoring, 4)
    _backdate(db, action, 4)
    m = get_alert(monitoring, db_path=db)
    a = get_alert(action, db_path=db)
    assert m is not None and a is not None
    assert lifecycle.is_expired(m, now, monitoring_days=3, action_days=14)
    assert not lifecycle.is_expired(a, now, monitoring_days=3, action_days=14)


def test_zero_ttl_disables_expiry(db: Path) -> None:
    aid = _insert(db, source="triage", severity="high")
    _backdate(db, aid, 400)
    a = get_alert(aid, db_path=db)
    assert a is not None
    assert lifecycle.ttl_days_for(a, monitoring_days=3, action_days=0) is None
    assert not lifecycle.is_expired(a, monitoring_days=3, action_days=0)


def test_a_snooze_restarts_the_ttl_so_the_card_comes_back(db: Path) -> None:
    aid = _insert(db, source="stock", severity="low", topic_tags=["external:stock"])
    _backdate(db, aid, 2)
    now = datetime.now(UTC)
    # Snoozed for two days: past the 3-day monitoring TTL counted from creation.
    alert_store.set_snoozed_until(aid, (now + timedelta(days=2)).isoformat(), db_path=db)
    a = get_alert(aid, db_path=db)
    assert a is not None
    later = now + timedelta(days=2, hours=1)
    assert not lifecycle.is_expired(a, later, monitoring_days=3, action_days=14)
    assert lifecycle.is_live(a, later)
    assert not lifecycle.is_live(a, now)


def test_exempt_sources_never_expire(db: Path) -> None:
    art = _insert(db, source="artifact", severity="medium", topic_tags=["artifact"])
    dec = _insert(db, source="decision_scheduling", severity="medium")
    _backdate(db, art, 90)
    _backdate(db, dec, 90)
    for aid in (art, dec):
        a = get_alert(aid, db_path=db)
        assert a is not None
        assert lifecycle.ttl_days_for(a, monitoring_days=3, action_days=14) is None
        assert not lifecycle.is_expired(a, monitoring_days=3, action_days=14)


# --------------------------------------------------------------------------- #
# Live view + sweep
# --------------------------------------------------------------------------- #


def test_list_live_alerts_skips_past_ttl_and_snoozed_before_any_sweep(db: Path) -> None:
    live = _insert(db, headline="live")
    stale = _insert(db, headline="stale")
    snoozed = _insert(db, headline="snoozed")
    _backdate(db, stale, 20)
    with sqlite3.connect(str(db)) as conn:
        conn.execute(
            "UPDATE alerts SET snoozed_until=? WHERE id=?",
            ((datetime.now(UTC) + timedelta(hours=3)).isoformat(), snoozed),
        )
        conn.commit()
    ids = {a.id for a in lifecycle.list_live_alerts(db_path=db)}
    assert ids == {live}
    # Still unread on disk — the view hides, the sweep persists.
    stale_row = get_alert(stale, db_path=db)
    assert stale_row is not None and stale_row.status == "unread"


def test_list_live_alerts_not_starved_by_expired_rows(db: Path) -> None:
    """A run of expired rows newer than the live one must not push it out."""
    old_live = _insert(db, headline="old but live")
    _backdate(db, old_live, 1)
    for i in range(12):
        aid = _insert(db, headline=f"stale-{i}")
        _backdate(db, aid, 15 + i / 100)
    # Expired rows are older, so they sort after; the live one leads.
    rows = lifecycle.list_live_alerts(limit=5, db_path=db)
    assert [a.id for a in rows] == [old_live]


def test_expire_stale_alerts_marks_expired_and_audits(
    db: Path, monkeypatch: pytest.MonkeyPatch
) -> None:
    events: list[tuple[str, dict]] = []

    def fake_audit(event_type: str, summary: str, **kw: object) -> None:
        events.append((event_type, dict(kw.get("details") or {})))

    import openexecutive.audit as audit_pkg

    monkeypatch.setattr(audit_pkg, "log_event", fake_audit)

    live = _insert(db, headline="live")
    stale_action = _insert(db, headline="stale action", severity="high")
    stale_monitoring = _insert(
        db, headline="stale mon", source="rss", severity="low", topic_tags=["external:rss"]
    )
    exempt = _insert(db, headline="artifact", source="artifact", topic_tags=["artifact"])
    _backdate(db, stale_action, 15)
    _backdate(db, stale_monitoring, 4)
    _backdate(db, exempt, 60)

    count = lifecycle.expire_stale_alerts(db_path=db)
    assert count == 2
    assert get_alert(stale_action, db_path=db).status == "expired"  # type: ignore[union-attr]
    assert get_alert(stale_monitoring, db_path=db).status == "expired"  # type: ignore[union-attr]
    assert get_alert(live, db_path=db).status == "unread"  # type: ignore[union-attr]
    assert get_alert(exempt, db_path=db).status == "unread"  # type: ignore[union-attr]
    assert events and events[0][0] == "alert_sweep"
    assert events[0][1]["count"] == 2
    assert set(events[0][1]["alert_ids"]) == {stale_action, stale_monitoring}

    # Second sweep: nothing left to do, no audit row.
    events.clear()
    assert lifecycle.expire_stale_alerts(db_path=db) == 0
    assert events == []


def test_expire_stale_alerts_never_raises(db: Path, monkeypatch: pytest.MonkeyPatch) -> None:
    def boom(*a: object, **k: object) -> list:
        raise RuntimeError("disk on fire")

    monkeypatch.setattr(alert_store, "list_alerts", boom)
    assert lifecycle.expire_stale_alerts(db_path=db) == 0


# --------------------------------------------------------------------------- #
# Store helpers
# --------------------------------------------------------------------------- #


def test_coalesce_alert_bumps_count_last_seen_and_severity(db: Path) -> None:
    aid = _insert(db, source="stock", dedup_key="watch:stock-aapl", severity="low", body="v1")
    hit = coalesce_alert(
        source="stock", dedup_key="watch:stock-aapl", severity="medium", body="v2", db_path=db
    )
    assert hit == (aid, True)
    row = get_alert(aid, db_path=db)
    assert row is not None
    assert row.occurrence_count == 2
    assert row.last_seen_at is not None
    assert row.body == "v2"
    assert row.severity == "medium"
    # A milder repeat never lowers severity, and reports no escalation.
    hit = coalesce_alert(
        source="stock", dedup_key="watch:stock-aapl", severity="low", body="v3", db_path=db
    )
    assert hit == (aid, False)
    row = get_alert(aid, db_path=db)
    assert row is not None and row.severity == "medium" and row.occurrence_count == 3


def test_coalesce_alert_ignores_closed_rows_and_requires_key(db: Path) -> None:
    aid = _insert(db, source="email", dedup_key="churn-acme")
    alert_store.set_status(aid, "ack", db_path=db)
    assert coalesce_alert(
        source="email", dedup_key="churn-acme", severity="high", body="x", db_path=db
    ) is None
    assert coalesce_alert(
        source="email", dedup_key="", severity="high", body="x", db_path=db
    ) is None
    # Different source, same key → no match either.
    _insert(db, source="slack", dedup_key="k1")
    assert coalesce_alert(
        source="email", dedup_key="k1", severity="high", body="x", db_path=db
    ) is None


def test_bulk_set_status_by_ids_and_by_age_with_category(db: Path) -> None:
    a1 = _insert(db, headline="a1")
    a2 = _insert(db, headline="a2")
    m1 = _insert(db, headline="m1", source="rss", severity="low", topic_tags=["external:rss"])
    _backdate(db, a2, 10)
    _backdate(db, m1, 10)

    assert bulk_set_status("dismissed", alert_ids=[a1], db_path=db) == [a1]
    assert get_alert(a1, db_path=db).status == "dismissed"  # type: ignore[union-attr]
    # Already dismissed → not unread → not counted again.
    assert bulk_set_status("dismissed", alert_ids=[a1], db_path=db) == []

    cutoff = (datetime.now(UTC) - timedelta(days=7)).isoformat()
    assert bulk_set_status("dismissed", before=cutoff, category="monitoring", db_path=db) == [m1]
    assert get_alert(m1, db_path=db).status == "dismissed"  # type: ignore[union-attr]
    assert get_alert(a2, db_path=db).status == "unread"  # type: ignore[union-attr]
    assert bulk_set_status("ack", before=cutoff, category="action", db_path=db) == [a2]
    assert get_alert(a2, db_path=db).status == "ack"  # type: ignore[union-attr]
    # No selector → no-op.
    assert bulk_set_status("ack", db_path=db) == []
    assert bulk_set_status("ack", alert_ids=[], db_path=db) == []


def test_update_content_review_routing_and_reopen(db: Path) -> None:
    aid = _insert(db, headline="old", body="old body", severity="low")
    assert update_alert_content(aid, headline="new", severity="high", db_path=db)
    row = get_alert(aid, db_path=db)
    assert row is not None and row.headline == "new" and row.body == "old body"
    assert row.severity == "high"
    assert not update_alert_content(aid, db_path=db)  # nothing to set

    assert set_review(
        aid, verdict="likely_stale", note="no activity in 9 days",
        recommended_move="close", why_now="", db_path=db,
    )
    row = get_alert(aid, db_path=db)
    assert row is not None
    assert row.review_verdict == "likely_stale"
    assert row.review_note == "no activity in 9 days"
    assert row.recommended_move == "close"
    assert row.last_reviewed_at is not None

    assert update_alert_routing(aid, 7, db_path=db)
    assert get_alert(aid, db_path=db).routed_to_person_id == 7  # type: ignore[union-attr]

    alert_store.set_status(aid, "expired", db_path=db)
    assert reopen_alert(aid, db_path=db)
    row = get_alert(aid, db_path=db)
    assert row is not None and row.status == "unread" and row.review_verdict == ""
    assert row.last_reviewed_at is None and row.why_now == "" and row.due_at is None
    assert not reopen_alert(aid, db_path=db)  # already unread
    assert not reopen_alert(9999, db_path=db)
    # `ack` (approved + executed) is not an Undo target; exempt sources never reopen.
    alert_store.set_status(aid, "ack", db_path=db)
    assert not reopen_alert(aid, db_path=db)
    art = _insert(db, source="artifact", topic_tags=["artifact"])
    alert_store.set_status(art, "dismissed", db_path=db)
    assert not reopen_alert(art, db_path=db, exclude_sources=("artifact",))
    assert reopen_alert(art, db_path=db)  # without the exclusion the store itself allows it


def test_mark_superseded_folds_into_survivor(db: Path) -> None:
    survivor = _insert(db, headline="story")
    dup = _insert(db, headline="dup")
    assert mark_superseded(dup, survivor, db_path=db)
    d = get_alert(dup, db_path=db)
    s = get_alert(survivor, db_path=db)
    assert d is not None and d.status == "dismissed" and d.superseded_by_alert_id == survivor
    assert s is not None and s.occurrence_count == 2  # absorbs the dup's own count (1)
    assert count_superseded_by(db_path=db) == {survivor: 1}
    # Self-merge, missing survivor, or already-closed row → False.
    assert not mark_superseded(survivor, survivor, db_path=db)
    assert not mark_superseded(dup, 9999, db_path=db)
    assert not mark_superseded(dup, survivor, db_path=db)


def test_is_expired_uses_last_seen_as_the_age_anchor(db: Path) -> None:
    """A situation that keeps re-firing stays live: coalescing bumps last_seen_at."""
    aid = _insert(db, source="stock", severity="low", topic_tags=["external:stock"], dedup_key="watch:x")
    _backdate(db, aid, 10)  # created well past the 3-day monitoring TTL
    assert lifecycle.is_expired(get_alert(aid, db_path=db), monitoring_days=3, action_days=14)  # type: ignore[arg-type]
    coalesce_alert(source="stock", dedup_key="watch:x", severity="low", body="again", db_path=db)
    assert not lifecycle.is_expired(get_alert(aid, db_path=db), monitoring_days=3, action_days=14)  # type: ignore[arg-type]
    assert lifecycle.is_live(get_alert(aid, db_path=db))  # type: ignore[arg-type]


def test_mute_pattern_allowed_rejects_broad_patterns(db: Path) -> None:
    from openexecutive.alerts.models import Alert

    alert = Alert(source="rss", severity="low", headline="h", body="b", created_at="2026-09-11T00:00:00+00:00",
                  topic_tags=["external:rss", "external:acme-news", "press"])
    assert lifecycle.mute_pattern_allowed(alert, "external:acme-news")
    assert lifecycle.mute_pattern_allowed(alert, "press")
    assert lifecycle.mute_pattern_allowed(alert, "department:finance")
    assert not lifecycle.mute_pattern_allowed(alert, "e")
    assert not lifecycle.mute_pattern_allowed(alert, ":")
    assert not lifecycle.mute_pattern_allowed(alert, "finance")  # not this alert's tag, not scoped
    assert not lifecycle.mute_pattern_allowed(alert, "external:")


def test_reopen_makes_a_past_ttl_row_live_again(db: Path) -> None:
    aid = _insert(db, headline="old but wanted")
    _backdate(db, aid, 20)
    assert lifecycle.expire_stale_alerts(db_path=db) == 1
    assert reopen_alert(aid, db_path=db)
    assert lifecycle.is_live(get_alert(aid, db_path=db))  # type: ignore[arg-type]
    assert lifecycle.expire_stale_alerts(db_path=db) == 0  # not re-expired by the next sweep
    assert [a.id for a in lifecycle.list_live_alerts(db_path=db)] == [aid]


def test_reopen_after_merge_hands_back_the_absorbed_count(db: Path) -> None:
    survivor = _insert(db, headline="story")
    dup = _insert(db, headline="dup")
    with sqlite3.connect(str(db)) as conn:
        conn.execute("UPDATE alerts SET occurrence_count = 4 WHERE id = ?", (dup,))
        conn.commit()
    assert mark_superseded(dup, survivor, db_path=db)
    assert get_alert(survivor, db_path=db).occurrence_count == 5  # type: ignore[union-attr]
    assert reopen_alert(dup, db_path=db)
    assert get_alert(survivor, db_path=db).occurrence_count == 1  # type: ignore[union-attr]
    assert get_alert(dup, db_path=db).superseded_by_alert_id is None  # type: ignore[union-attr]


def test_bulk_age_cutoff_uses_last_seen_anchor(db: Path) -> None:
    refiring = _insert(db, headline="refiring", source="stock", dedup_key="watch:y", severity="low",
                       topic_tags=["external:stock"])
    idle = _insert(db, headline="idle")
    _backdate(db, refiring, 10)
    _backdate(db, idle, 10)
    coalesce_alert(source="stock", dedup_key="watch:y", severity="low", body="again", db_path=db)
    cutoff = (datetime.now(UTC) - timedelta(days=7)).isoformat()
    assert bulk_set_status("dismissed", before=cutoff, db_path=db) == [idle]