Spaces:
Sleeping
Sleeping
File size: 8,614 Bytes
d084ead | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 | """
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()
|