""" scripts/backfill_overwatch.py ------------------------------ Backfill overwatch_events from the legacy audit_logs and learned_rule_logs tables. Maps: audit_logs action='upload_statement' → overwatch stage='upload' INFO audit_logs action='start_processing' → overwatch stage='upload' INFO (processing started) audit_logs action='finalise_statement' → overwatch stage='classification_completed' INFO audit_logs action='export_statement' → overwatch stage='classification_completed' INFO audit_logs response_status >= 400 → overwatch log_level='ERROR' (same stage) learned_rule_logs source='llm'|'ml' → overwatch stage='rule_learned' INFO Run with: python scripts/backfill_overwatch.py """ import json import os import sys import uuid from datetime import timezone from pathlib import Path # Make sure project root is on sys.path ROOT = Path(__file__).resolve().parent.parent sys.path.insert(0, str(ROOT)) from sqlalchemy import create_engine, text from sqlalchemy.orm import sessionmaker from overwatch.models import OverwatchBase, OverwatchEventORM # --------------------------------------------------------------------------- # Action → stage mapping # --------------------------------------------------------------------------- ACTION_TO_STAGE = { "upload_statement": "upload", "start_processing": "upload", "view_summary": "audit", "export_statement": "classification_completed", "finalise_statement": "classification_completed", "update_transaction": "transaction_classified", "delete_transaction": "transaction_classified", "approve_transaction": "transaction_classified", } SKIP_ACTIONS = { "login_attempt", "logout", "view_dashboard", "view_history", "view_flagged", "reopen_statement", "duplicate_statement", } def make_event_id() -> str: return str(uuid.uuid4()) def iso(dt) -> str: """Convert a naive or aware datetime to ISO 8601 UTC string.""" if dt is None: from datetime import datetime return datetime.now(timezone.utc).isoformat() if dt.tzinfo is None: # Assume UTC return dt.replace(tzinfo=timezone.utc).isoformat() return dt.astimezone(timezone.utc).isoformat() def main() -> None: db_url = os.environ.get("DATABASE_URL") if not db_url: print("ERROR: DATABASE_URL not set") sys.exit(1) engine = create_engine(db_url) Session = sessionmaker(bind=engine) # Ensure table exists OverwatchBase.metadata.create_all(engine) with engine.connect() as conn: # ── Check existing overwatch events ─────────────────────────────── existing_count = conn.execute( text("SELECT COUNT(*) FROM overwatch_events") ).scalar() print(f"Existing overwatch_events rows: {existing_count}") if existing_count > 0: ans = input("Table already has data. Re-run backfill anyway? [y/N] ").strip().lower() if ans != "y": print("Aborted.") return # ── Load audit_logs ─────────────────────────────────────────────── audit_rows = conn.execute(text( "SELECT id, username, action, resource_type, resource_id, " " response_status, occurred_at, endpoint, extra_data " "FROM audit_logs " "WHERE resource_type = 'statement' " "ORDER BY occurred_at" )).fetchall() print(f"audit_logs rows with resource_type='statement': {len(audit_rows)}") # ── Load learned_rule_logs ──────────────────────────────────────── rule_rows = conn.execute(text( "SELECT id, keyword, category, source, confidence, " " example_description, created_at " "FROM learned_rule_logs " "ORDER BY created_at" )).fetchall() print(f"learned_rule_logs rows: {len(rule_rows)}") # ── Build ORM rows ──────────────────────────────────────────────────── orm_rows: list[OverwatchEventORM] = [] # Track per-document_id upload names we've seen so we can fill document_name doc_names: dict[str, str] = {} for row in audit_rows: action = row.action or "" if action in SKIP_ACTIONS: continue stage = ACTION_TO_STAGE.get(action) if stage is None: # Unknown action — map to audit stage generically stage = "audit" doc_id = row.resource_id or "unknown" # Infer document_name from endpoint or use resource_id endpoint = row.endpoint or "" doc_name = doc_names.get(doc_id, f"statement_{doc_id}") if action == "upload_statement": # Try to extract filename from extra_data JSON try: extra = json.loads(row.extra_data or "{}") doc_name = extra.get("file_name") or extra.get("filename") or doc_name except (json.JSONDecodeError, TypeError): pass doc_names[doc_id] = doc_name status = row.response_status or 200 log_level = "ERROR" if status >= 400 else "INFO" message = f"{action.replace('_', ' ').title()} — {doc_name[:60]}" if status >= 400: message = f"FAILED: {message} (HTTP {status})" metadata = { "username": row.username, "http_status": status, "endpoint": endpoint, } orm_rows.append(OverwatchEventORM( event_id=make_event_id(), document_id=doc_id, document_name=doc_name, timestamp=iso(row.occurred_at), log_level=log_level, stage=stage, message=message[:500], event_metadata=json.dumps(metadata), )) # ── Learned rule events ─────────────────────────────────────────────── for row in rule_rows: metadata = { "learned_keyword": row.keyword, "assigned_category": row.category, "classification_engine": row.source or "llm", "confidence_score": round(float(row.confidence or 0), 4), "example_description": (row.example_description or "")[:200], } orm_rows.append(OverwatchEventORM( event_id=make_event_id(), document_id="backfill_rules", document_name="[Rule Learning Backfill]", timestamp=iso(row.created_at), log_level="INFO", stage="rule_learned", message=f"Rule learned: '{row.keyword}' → {row.category}"[:500], event_metadata=json.dumps(metadata), )) print(f"\nTotal overwatch events to insert: {len(orm_rows)}") if not orm_rows: print("Nothing to insert.") return # ── Insert in one transaction ───────────────────────────────────────── session = Session() try: session.bulk_save_objects(orm_rows) session.commit() print(f"✓ Inserted {len(orm_rows)} events into overwatch_events") except Exception as e: session.rollback() print(f"ERROR: {e}") raise finally: session.close() # ── Quick verification ───────────────────────────────────────────────── with engine.connect() as conn: total = conn.execute(text("SELECT COUNT(*) FROM overwatch_events")).scalar() by_stage = conn.execute(text( "SELECT stage, COUNT(*) FROM overwatch_events GROUP BY stage ORDER BY COUNT(*) DESC" )).fetchall() print(f"\nFinal overwatch_events count: {total}") print("By stage:") for stage, count in by_stage: print(f" {stage:35s} {count}") if __name__ == "__main__": main()