Spaces:
Sleeping
Sleeping
Download scripts/backfill_overwatch.py from Coeinterns/bank-statement-api: direct link, hf CLI and curl.
- Browser
- Download file 8.61 kB
-
https://huggingface.co/spaces/Coeinterns/bank-statement-api/resolve/main/scripts/backfill_overwatch.py
- Command line
-
hf download hf://spaces/Coeinterns/bank-statement-api/scripts/backfill_overwatch.py
-
curl -L -o backfill_overwatch.py https://huggingface.co/spaces/Coeinterns/bank-statement-api/resolve/main/scripts/backfill_overwatch.py
8.61 kB
| """ | |
| 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() | |