Download app/admin_stats.py from CJR61/drpig-web: direct link, hf CLI and curl.
- Browser
- Download file 14.7 kB
-
https://huggingface.co/spaces/CJR61/drpig-web/resolve/main/app/admin_stats.py
- Command line
-
hf download hf://spaces/CJR61/drpig-web/app/admin_stats.py
-
curl -L -o admin_stats.py https://huggingface.co/spaces/CJR61/drpig-web/resolve/main/app/admin_stats.py
14.7 kB
| """ | |
| admin_stats.py โ Adminใ่ฃ็ฝฎใๅ้ + ๅฐ่ฉฑ็ฏฉ้ธ๏ผP2-A, docs/11 ยง8.7; FR-TELE-04/10/12). | |
| No device_profiles / inference_log tables: everything is computed from telemetry_events | |
| (latest device_caps per device, chat_turn, model_download). Volumes are small (one row per app start / | |
| chat turn), so plain Python over the rows is fine. | |
| """ | |
| from __future__ import annotations | |
| import json | |
| import math | |
| from collections import Counter | |
| from datetime import datetime, timedelta, timezone | |
| from sqlalchemy import func, select | |
| from app import db | |
| from app.db import cases, devices, messages, telemetry_events | |
| TTFT_BUCKETS = (("<5s", 5_000), ("5โ10s", 10_000), ("10โ20s", 20_000), ("20โ40s", 40_000), ("โฅ40s", None)) | |
| def _str(v) -> str | None: | |
| return v[:120] if isinstance(v, str) and v else None | |
| LOCAL_KINDS = ("full", "lite", "manual", "none") | |
| def _props(raw) -> dict: | |
| """Telemetry comes from any registered device: keep only the fields we read, with the types we expect | |
| (a list reason, an Infinity TTFT or a string tier must not 500 the page โ review 2026-10-20 #3).""" | |
| try: | |
| p = json.loads(raw or "{}") | |
| except ValueError: | |
| return {} | |
| if not isinstance(p, dict): | |
| return {} | |
| static = p.get("static") if isinstance(p.get("static"), dict) else {} | |
| ttft, mem, tier = p.get("ttft_ms"), static.get("total_mem_gb"), p.get("tier") | |
| num = lambda v: isinstance(v, (int, float)) and not isinstance(v, bool) and math.isfinite(v) # noqa: E731 | |
| return { | |
| "route": p.get("route") if p.get("route") in ("LOCAL", "API") else None, | |
| "ttft_ms": int(ttft) if num(ttft) and 0 <= ttft < 3_600_000 else None, | |
| "tier": tier if isinstance(tier, int) and not isinstance(tier, bool) and 0 <= tier <= 3 else None, | |
| "downgrade_reason": _str(p.get("downgrade_reason")), | |
| "app_version": _str(p.get("app_version")), | |
| "action": _str(p.get("action")), "role": _str(p.get("role")), | |
| "model": _str(static.get("model")), | |
| "total_mem_gb": float(mem) if num(mem) else None, | |
| # P5 (FR-LITE-12): which on-device mode โ device_caps carries the phone's, chat_turn the turn's | |
| "local_kind": p.get("local_kind") if p.get("local_kind") in LOCAL_KINDS else None, | |
| "lite_guard_tripped": isinstance(p.get("lite_guard"), dict) and p["lite_guard"].get("tripped") is True, | |
| "last_resort": p.get("last_resort") is True, | |
| } | |
| def _events(conn, names: tuple[str, ...], since: str | None = None) -> list[dict]: | |
| stmt = (select(telemetry_events.c.device_id, telemetry_events.c.event, telemetry_events.c.props, | |
| telemetry_events.c.received_at) | |
| .where(telemetry_events.c.event.in_(names)).order_by(telemetry_events.c.id)) | |
| if since: | |
| stmt = stmt.where(telemetry_events.c.received_at >= since) | |
| return [{"device_id": r.device_id, "event": r.event, "props": _props(r.props), "at": r.received_at} | |
| for r in conn.execute(stmt)] | |
| def _since(days: int, now: datetime | None) -> str: | |
| now = (now or datetime.now(timezone.utc)).replace(tzinfo=None) | |
| return (now - timedelta(days=days)).isoformat() | |
| def _pct(sorted_vals: list[int], q: float) -> int | None: | |
| if not sorted_vals: | |
| return None | |
| return sorted_vals[min(len(sorted_vals) - 1, int(q * len(sorted_vals)))] | |
| def _latest_caps(conn) -> dict[str, dict]: | |
| """device_id โ props of its newest device_caps (all time: a device's spec does not expire).""" | |
| latest: dict[str, dict] = {} | |
| for e in _events(conn, ("device_caps",)): | |
| if e["device_id"]: | |
| latest[e["device_id"]] = e["props"] | {"_at": e["at"]} | |
| return latest | |
| def _tier_key(caps: dict | None) -> str: | |
| t = (caps or {}).get("tier") | |
| return str(t) if isinstance(t, int) else "?" | |
| def device_summary(days: int = 30, now: datetime | None = None) -> dict: | |
| """D-25 five aggregates over the last `days` (tier distribution covers devices seen in the window).""" | |
| since = _since(days, now) | |
| with db.read_connection() as conn: | |
| caps = _latest_caps(conn) | |
| turns = _events(conn, ("chat_turn",), since) | |
| downloads = _events(conn, ("model_download",), since) | |
| recent_caps = _events(conn, ("device_caps",), since) | |
| active = {e["device_id"] for e in recent_caps + turns if e["device_id"]} | |
| tiers = Counter(_tier_key(caps.get(d)) for d in active) | |
| routes = Counter(e["props"].get("route") for e in turns if e["props"].get("route") in ("LOCAL", "API")) | |
| ttft = sorted(e["props"]["ttft_ms"] for e in turns | |
| if e["props"]["route"] == "LOCAL" and e["props"]["ttft_ms"] is not None) | |
| buckets = {name: 0 for name, _ in TTFT_BUCKETS} | |
| for v in ttft: | |
| buckets[next(name for name, hi in TTFT_BUCKETS if hi is None or v < hi)] += 1 | |
| by_tier: dict[str, list[int]] = {} | |
| for e in turns: | |
| p = e["props"] | |
| if p["route"] == "LOCAL" and p["ttft_ms"] is not None: | |
| by_tier.setdefault(_tier_key(caps.get(e["device_id"])), []).append(p["ttft_ms"]) | |
| reasons = Counter(e["props"]["downgrade_reason"] for e in turns + recent_caps if e["props"].get("downgrade_reason")) | |
| gen = [e for e in downloads if (e["props"]["role"] or "generator") == "generator"] | |
| started = {e["device_id"] for e in gen if e["props"].get("action") == "start"} | |
| done = {e["device_id"] for e in gen if e["props"].get("action") == "done"} & started | |
| n_routes = routes["LOCAL"] + routes["API"] | |
| kinds = Counter((caps.get(d) or {}).get("local_kind") or "?" for d in active) | |
| local_turns = [e["props"] for e in turns if e["props"].get("route") == "LOCAL"] | |
| return { | |
| "days": days, "devices": len(active), | |
| "tiers": dict(sorted(tiers.items())), | |
| "routes": {"LOCAL": routes["LOCAL"], "API": routes["API"]}, | |
| "local_ratio": round(routes["LOCAL"] / n_routes, 3) if n_routes else None, | |
| # P5 (FR-LITE-12): devices by on-device mode; LOCAL turns by mode; LITE number-guard trips; handbook last layer | |
| "local_kinds": dict(sorted(kinds.items())), | |
| "local_turns_by_kind": dict(sorted(Counter(p.get("local_kind") or "full" for p in local_turns).items())), | |
| "lite_guard_trips": sum(1 for p in local_turns if p.get("lite_guard_tripped")), | |
| "last_resort": sum(1 for p in local_turns if p.get("last_resort")), | |
| "ttft": {"n": len(ttft), "p50": _pct(ttft, 0.5), "p90": _pct(ttft, 0.9), "buckets": buckets}, | |
| "ttft_by_tier": {t: {"n": len(v), "p50": _pct(sorted(v), 0.5)} for t, v in sorted(by_tier.items())}, | |
| "downgrade_top": [{"reason": r, "n": n} for r, n in reasons.most_common(5)], | |
| "download": {"started": len(started), "done": len(done), | |
| "rate": round(len(done) / len(started), 3) if started else None}, | |
| } | |
| def device_list(q: str = "", tier: str = "") -> list[dict]: | |
| """One row per device that ever sent telemetry or registered (FR-TELE-10).""" | |
| with db.read_connection() as conn: | |
| caps = _latest_caps(conn) | |
| turns = _events(conn, ("chat_turn",)) | |
| last_tel = dict(conn.execute(select(telemetry_events.c.device_id, func.max(telemetry_events.c.received_at)) | |
| .group_by(telemetry_events.c.device_id)).all()) | |
| registered = dict(conn.execute(select(devices.c.device_id, devices.c.last_seen)).all()) | |
| n_cases = dict(conn.execute(select(cases.c.device_id, func.count()).where(cases.c.hidden == 0) | |
| .group_by(cases.c.device_id)).all()) | |
| per: dict[str, dict] = {} | |
| for e in turns: | |
| d = per.setdefault(e["device_id"], {"turns": 0, "local": 0, "down": None, "down_at": None}) | |
| d["turns"] += 1 | |
| d["local"] += e["props"].get("route") == "LOCAL" | |
| if e["props"].get("downgrade_reason"): | |
| d["down"], d["down_at"] = e["props"]["downgrade_reason"], e["at"] | |
| from app.account import masked_accounts | |
| acc = masked_accounts() | |
| rows = [] | |
| for did in set(caps) | set(registered) | set(last_tel): | |
| if not did: | |
| continue | |
| c = caps.get(did, {}) | |
| t = per.get(did, {"turns": 0, "local": 0, "down": None, "down_at": None}) | |
| down, down_at = t["down"], t["down_at"] | |
| if c.get("downgrade_reason") and (down_at is None or c["_at"] > down_at): | |
| down, down_at = c["downgrade_reason"], c["_at"] | |
| rows.append({ | |
| "device_id": did, "short": did[:8], "model": c.get("model"), | |
| "total_mem_gb": c.get("total_mem_gb"), "tier": c.get("tier"), "app_version": c.get("app_version"), | |
| "last_seen": max(filter(None, (last_tel.get(did), registered.get(did))), default=None), | |
| "cases": n_cases.get(did, 0), "turns": t["turns"], | |
| "local_ratio": round(t["local"] / t["turns"], 3) if t["turns"] else None, | |
| "last_downgrade": down, "last_downgrade_at": down_at, | |
| "account": acc.get(did), # {email_masked, devices} | None (FR-ACC-04: never the full email) | |
| }) | |
| ql = q.strip().lower() | |
| if ql: | |
| rows = [r for r in rows if r["device_id"].lower().startswith(ql) or ql in (r["model"] or "").lower()] | |
| if tier != "": | |
| rows = [r for r in rows if str(r["tier"]) == tier] | |
| return sorted(rows, key=lambda r: r["last_seen"] or "", reverse=True) | |
| def conv_meta() -> dict[tuple, dict]: | |
| """(session_id, conv_id) โ {origin of the first row, sorted assistant routes} for the sessions filter (FR-TELE-12).""" | |
| meta: dict[tuple, dict] = {} | |
| with db.read_connection() as conn: | |
| for r in conn.execute(select(messages.c.session_id, messages.c.conv_id, messages.c.origin, | |
| messages.c.role, messages.c.route).order_by(messages.c.ts)): | |
| m = meta.setdefault((r.session_id, r.conv_id), {"origin": r.origin or "web", "routes": set()}) | |
| if r.role == "assistant": | |
| m["routes"].add(r.route or "API") | |
| return {k: {"origin": v["origin"], "routes": sorted(v["routes"])} for k, v in meta.items()} | |
| # โโ P3-A: case page, last 7 days, ๐ handling (docs/11 ยง8.7; TEL-05/07/08) โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ | |
| TPE = timezone(timedelta(hours=8)) | |
| def _json_field(v): | |
| try: | |
| return json.loads(v) if isinstance(v, str) and v else v | |
| except ValueError: | |
| return v | |
| def case_detail(case_id: str) -> dict | None: | |
| """Everything about one App case for the admin (TEL-05). Exact GPS never leaves the DB (TEL-01).""" | |
| from app.db import feedback, images | |
| with db.read_connection() as conn: | |
| row = conn.execute(select(cases).where(cases.c.case_id == case_id)).mappings().first() | |
| if row is None: | |
| return None | |
| imgs = conn.execute(select(*[c for c in images.c if c.name not in ("lat", "lon")]) | |
| .where(images.c.case_id == case_id).order_by(images.c.ts)).mappings().all() | |
| msgs = conn.execute(select(messages).where(messages.c.case_id == case_id).order_by(messages.c.ts)).mappings().all() | |
| fbs = conn.execute(select(feedback).where(feedback.c.case_id == case_id)).mappings().all() | |
| c = dict(row) | |
| for k in ("questionnaire_answers", "rapid_test"): | |
| c[k] = _json_field(c.get(k)) | |
| dev = next((d for d in device_list() if d["device_id"] == c["device_id"]), None) | |
| return {"case": c, "images": [dict(i) for i in imgs], | |
| "messages": [dict(m) for m in msgs], "feedback": [dict(f) for f in fbs], "device": dev} | |
| def daily_stats(days: int = 7, now: datetime | None = None) -> dict: | |
| """Per Taipei day, oldest first, missing days as 0 (TEL-07) + the unreviewed ๐ count (TEL-08).""" | |
| from app.db import feedback, images | |
| now = now or datetime.now(timezone.utc) | |
| today = now.astimezone(TPE).replace(hour=0, minute=0, second=0, microsecond=0) | |
| start = today - timedelta(days=days - 1) | |
| utc = lambda d: d.astimezone(timezone.utc).replace(tzinfo=None).isoformat() # noqa: E731 | |
| def bucket(conn, col, *where) -> Counter: | |
| out: Counter = Counter() | |
| for (ts,) in conn.execute(select(col).where(col >= utc(start), *where)): | |
| try: | |
| t = datetime.fromisoformat(str(ts).replace("Z", "+00:00")) | |
| except ValueError: | |
| continue | |
| t = t if t.tzinfo else t.replace(tzinfo=timezone.utc) | |
| out[t.astimezone(TPE).date().isoformat()] += 1 | |
| return out | |
| with db.read_connection() as conn: | |
| n_cases = bucket(conn, cases.c.created_at, cases.c.hidden == 0) | |
| n_msgs = bucket(conn, messages.c.ts) | |
| n_imgs = bucket(conn, images.c.ts) | |
| n_down = bucket(conn, feedback.c.ts, feedback.c.rating == "down") | |
| unreviewed = conn.scalar(select(func.count()).select_from(feedback).where( | |
| feedback.c.rating == "down", feedback.c.reviewed_at.is_(None))) or 0 | |
| rows = [] | |
| for i in range(days): | |
| d = (start + timedelta(days=i)).date().isoformat() | |
| rows.append({"day": d, "cases": n_cases[d], "messages": n_msgs[d], "images": n_imgs[d], "down": n_down[d]}) | |
| return {"days": rows, "unreviewed_down": unreviewed} | |
| def feedback_rows() -> list[dict]: | |
| """Legacy feedback rows + the rated answer and the question before it (TEL-08).""" | |
| from app.db import feedback | |
| with db.read_connection() as conn: | |
| fbs = [dict(r) for r in conn.execute(select(feedback).order_by(feedback.c.ts.desc())).mappings()] | |
| ids = [f["msg_id"] for f in fbs if f.get("msg_id")] | |
| answers = {r.msg_id: r for r in conn.execute( | |
| select(messages.c.msg_id, messages.c.conv_id, messages.c.content, messages.c.ts).where(messages.c.msg_id.in_(ids)))} | |
| out = [] | |
| for f in fbs: | |
| a = answers.get(f.get("msg_id")) | |
| q = None | |
| if a is not None: | |
| q = conn.execute(select(messages.c.content).where( | |
| messages.c.conv_id == a.conv_id, messages.c.role == "user", messages.c.ts <= a.ts) | |
| .order_by(messages.c.ts.desc()).limit(1)).scalar() | |
| out.append(f | {"answer": a.content[:300] if a is not None else None, "question": q[:200] if q else None}) | |
| return out | |
| def review_feedback(fb_id: str, note: str | None, unreview: bool = False) -> bool: | |
| from app.db import feedback, now_iso | |
| from sqlalchemy import update | |
| vals = {"reviewed_at": None, "review_note": None} if unreview else {"reviewed_at": now_iso(), "review_note": (note or "")[:500]} | |
| with db.engine().begin() as conn: | |
| return conn.execute(update(feedback).where(feedback.c.fb_id == fb_id).values(**vals)).rowcount == 1 | |