serversdown
3c22f1d70c
Adds the recovery layer for the NL-43 TCP wedge failure mode (control listener on 2255 dies while device/FTP stay alive — see SLM-stress-test/NL43_RX55_TCP_Wedge_Investigation_2026-02-18.md). - Failure classification: poll failures probed raw and classified as wedged (refused = stack alive, listener gone) vs offline (timeout). FTP-port probe upgrades wedge confidence to definitive. - RecoveryManager state machine: confirm wedge -> trigger reset -> wait boot -> reconnect -> resume measurement via start_cycle (keeps overwrite protection) -> full incident trail in device logs. - Pluggable reset backends: manual (notify, default) and webhook (HTTP to relay controller: Pi GPIO, Shelly, Tasmota, etc.). - Safety: auto_recovery_enabled off by default, 2-failure confirmation threshold, windowed attempt limit (2 per 6h), one recovery per device. - Endpoints: GET recovery/status, POST recovery/probe (diagnostic), POST recovery/trigger (manual, bypasses threshold). - Migration: migrate_add_recovery_fields.py - Tests: test_wedge_recovery.py — fake wedge-able NL-43 + fake relay webhook; 23 assertions covering classification, end-to-end recovery with resume, and gating. All passing. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
108 lines
3.4 KiB
Python
108 lines
3.4 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
Migration script to add wedge-recovery fields to nl43_config and nl43_status tables.
|
|
|
|
Adds to nl43_config:
|
|
- auto_recovery_enabled (BOOLEAN, default 0/False)
|
|
- reset_backend (TEXT, default 'manual')
|
|
- reset_webhook_url (TEXT, nullable)
|
|
- reset_webhook_method (TEXT, default 'POST')
|
|
- recovery_boot_wait_seconds (INTEGER, default 90)
|
|
- recovery_reconnect_timeout (INTEGER, default 180)
|
|
- recovery_auto_resume (BOOLEAN, default 1/True)
|
|
- recovery_max_attempts (INTEGER, default 2)
|
|
- recovery_window_minutes (INTEGER, default 360)
|
|
|
|
Adds to nl43_status:
|
|
- connection_state (TEXT, default 'ok')
|
|
- last_wedge_at (DATETIME, nullable)
|
|
- wedge_count (INTEGER, default 0)
|
|
- recovery_state (TEXT, default 'idle')
|
|
- last_recovery_at (DATETIME, nullable)
|
|
- last_recovery_result (TEXT, nullable)
|
|
|
|
Usage:
|
|
python migrate_add_recovery_fields.py
|
|
"""
|
|
|
|
import sqlite3
|
|
import sys
|
|
from pathlib import Path
|
|
|
|
CONFIG_COLUMNS = [
|
|
("auto_recovery_enabled", "BOOLEAN DEFAULT 0"),
|
|
("reset_backend", "TEXT DEFAULT 'manual'"),
|
|
("reset_webhook_url", "TEXT"),
|
|
("reset_webhook_method", "TEXT DEFAULT 'POST'"),
|
|
("recovery_boot_wait_seconds", "INTEGER DEFAULT 90"),
|
|
("recovery_reconnect_timeout", "INTEGER DEFAULT 180"),
|
|
("recovery_auto_resume", "BOOLEAN DEFAULT 1"),
|
|
("recovery_max_attempts", "INTEGER DEFAULT 2"),
|
|
("recovery_window_minutes", "INTEGER DEFAULT 360"),
|
|
]
|
|
|
|
STATUS_COLUMNS = [
|
|
("connection_state", "TEXT DEFAULT 'ok'"),
|
|
("last_wedge_at", "DATETIME"),
|
|
("wedge_count", "INTEGER DEFAULT 0"),
|
|
("recovery_state", "TEXT DEFAULT 'idle'"),
|
|
("last_recovery_at", "DATETIME"),
|
|
("last_recovery_result", "TEXT"),
|
|
]
|
|
|
|
|
|
def migrate():
|
|
db_path = Path("data/slmm.db")
|
|
|
|
if not db_path.exists():
|
|
print(f"❌ Database not found at {db_path}")
|
|
print(" Run this script from the slmm directory")
|
|
return False
|
|
|
|
try:
|
|
conn = sqlite3.connect(db_path)
|
|
cursor = conn.cursor()
|
|
|
|
cursor.execute("PRAGMA table_info(nl43_config)")
|
|
config_columns = [row[1] for row in cursor.fetchall()]
|
|
|
|
cursor.execute("PRAGMA table_info(nl43_status)")
|
|
status_columns = [row[1] for row in cursor.fetchall()]
|
|
|
|
changes_made = False
|
|
|
|
for name, ddl in CONFIG_COLUMNS:
|
|
if name not in config_columns:
|
|
print(f"Adding {name} to nl43_config...")
|
|
cursor.execute(f"ALTER TABLE nl43_config ADD COLUMN {name} {ddl}")
|
|
changes_made = True
|
|
else:
|
|
print(f"✓ {name} already exists in nl43_config")
|
|
|
|
for name, ddl in STATUS_COLUMNS:
|
|
if name not in status_columns:
|
|
print(f"Adding {name} to nl43_status...")
|
|
cursor.execute(f"ALTER TABLE nl43_status ADD COLUMN {name} {ddl}")
|
|
changes_made = True
|
|
else:
|
|
print(f"✓ {name} already exists in nl43_status")
|
|
|
|
if changes_made:
|
|
conn.commit()
|
|
print("\n✓ Migration completed successfully")
|
|
print(" Added wedge-recovery fields to nl43_config and nl43_status")
|
|
else:
|
|
print("\n✓ All recovery fields already exist - no changes needed")
|
|
|
|
conn.close()
|
|
return True
|
|
|
|
except Exception as e:
|
|
print(f"❌ Migration failed: {e}")
|
|
return False
|
|
|
|
|
|
if __name__ == "__main__":
|
|
success = migrate()
|
|
sys.exit(0 if success else 1)
|