drpig-web / app /admin_stats.py
CJR61's picture
deploy backend (prompt_source 23c933ee)
077cb41 verified
Raw History Blame Contribute Delete
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