Run #3382

{
  "ok": true,
  "failed": [],
  "hallucinated": [],
  "context_errors": []
}

{}

Tu es un agent de monitoring du pool agencecom.

Lessons pertinentes:
LESSONS APPRISES PERTINENTES :
- L453-HUB: Health endpoints K8s : separer liveness no-DB / readiness DB-1s alert / startup cached
- L328-CROSS-S150-blocD2-deploy: Bloc D2 cache layer DEPLOYED Sprint 2 P2 — REUSE Redis 7.0.15 BSD existing Contabo (skip Valkey doublon) + smoke 5/5 PASS via /api/memory/cache/health endpoint dedie
- L-S199-LL2-V52-SPEC-REVIEW: L-S199-LL2 — Review V5.2 Monitoring spec (3 BLOCKERS + 15 medium + 5 low = 23 findings, GO sous condition)
- L-S199-CROSS-V52-SPEC-DRAFTED: L-S199-CROSS-V52-SPEC-DRAFTED — V5.2 Monitoring event-driven spec push DB
- L-S189-LL2-observateur-recap: L-S189-LL2 — Observateur PHASE 1 recap 13+ cycles SILENT + cron clôture

Donnees pool actuelles:
[missing:pool_status]

CONTRAINTE STRICTE -- Tables autorisees UNIQUEMENT :
- agencecom.agents (id, name, agent_level, status, current_session_id, error_count_last_hour, total_sessions, total_errors, total_cost_usd, quarantined_until, acquired_at, last_released_at)
- agencecom.agent_task_runs (id, agent_id, status, started_at, completed_at, error_message, llm_tokens_in, llm_tokens_out, cost_usd, validation_result)

Toute autre table (v_agents_critical, agent_pool, healthcheck_config, error_log, file_locks, zombie_agents, agent_audit, v_agent_timeout_health, v_agents_timeout_risk, agent_health_log, redis_health_ping, auto_quarantine, missions, health_checks, agent_health, internal) NEXISTE PAS -- ne reference jamais de table absente de cette liste.

Identifie les agents zombies (busy >1h sans completion via started_at + status), error_count_last_hour > 5, ou drift quarantine (quarantined_until > now).
Donne 3 recommandations concretes en markdown bullet points avec actions SQL utilisant SEULEMENT les 2 tables autorisees ci-dessus.

### 🔍 Diagnostic zombie (sans données live)

1. **Agents bloqués en `busy` depuis plus d’1h sans completion**  
   *Jointure `agents` ↔ `agent_task_runs` via `current_session_id` pour repérer les tâches orphelines.*  
   ```sql
   SELECT a.id, a.name, r.started_at,
          EXTRACT(EPOCH FROM NOW() - r.started_at)/3600 AS heures_bloquees
   FROM agencecom.agents a
   JOIN agencecom.agent_task_runs r ON a.current_session_id = r.id
   WHERE a.status = 'busy'
     AND r.started_at < NOW() - INTERVAL '1 hour'
     AND r.completed_at IS NULL;
   ```

2. **Agents avec rafale d’erreurs (>5 erreurs/heure) non encore mis en quarantaine**  
   *Surveillance directe du compteur `error_count_last_hour`.*  
   ```sql
   SELECT id, name, error_count_last_hour, quarantined_until
   FROM agencecom.agents
   WHERE error_count_last_hour > 5
     AND (quarantined_until IS NULL OR quarantined_until <= NOW());
   ```

3. **Dérive de quarantaine : agent toujours verrouillé (`quarantined_until > now`) mais sans erreur récente**  
   *Identification des agents qui auraient dû être libérés.*  
   ```sql
   SELECT id, name, quarantined_until, error_count_last_hour
   FROM agencecom.agents
   WHERE quarantined_until > NOW()
     AND error_count_last_hour = 0;
   ```

---

### 🛠️ 3 Recommandations concrètes avec actions SQL

- **Libérer les zombies “busy”**  
  Annuler la tâche bloquée et remettre l’agent disponible.  
  ```sql
  UPDATE agencecom.agent_task_runs
  SET status = 'cancelled', completed_at = NOW()
  WHERE id IN (
    SELECT a.current_session_id
    FROM agencecom.agents a
    JOIN agencecom.agent_task_runs r ON a.current_session_id = r.id
    WHERE a.status = 'busy'
      AND r.started_at < NOW() - INTERVAL '1 hour'
      AND r.completed_at IS NULL
  );

  UPDATE agencecom.agents
  SET status = 'available', current_session_id = NULL
  WHERE status = 'busy'
    AND current_session_id IN (
      SELECT r.id FROM agencecom.agent_task_runs r
      WHERE r.started_at < NOW() - INTERVAL '1 hour'
        AND r.completed_at IS NULL
    );
  ```

- **Quarantaine automatique des agents instables**  
  Isoler temporairement tout agent dépassant le seuil d’erreurs.  
  ```sql
  UPDATE agencecom.agents
  SET status = 'quarantined',
      quarantined_until = NOW() + INTERVAL '15 minutes'
  WHERE error_count_last_hour > 5
    AND (quarantined_until IS NULL OR quarantined_until <= NOW());
  ```

- **Purge des quarantaines driftées**  
  Libérer les agents sans erreur encore bloqués.  
  ```sql
  UPDATE agencecom.agents
  SET status = 'available',
      quarantined_until = NULL
  WHERE quarantined_until > NOW()
    AND error_count_last_hour = 0;
  ```

> ⚠️ **Précaution** : Toute intervention de modification doit être journalisée (horodatage, agent_id, opérateur) dans un suivi externe (les tables autorisées ne fournissent pas d’audit natif).