bank-statement-api / scripts /backfill_overwatch.py
3v324v23's picture
Merge Conflict solved | Build error solved
205f25f
Raw History Blame Contribute Delete
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()