Cycling / activity-trip diagnostics

Read-only tooling to pull and decrypt stored cycling trips so you can investigate why one was rejected, missed, or flagged by user feedback.

The gamification_private.activity_trip columns locations, activity_distribution and user_feedback are Fernet-encrypted, so they can only be read inside the backend app env (needs the Secrets Manager key + a DB session). Two Flask commands expose that: - export_activity_trips — dumps decrypted trips as a JSON array to stdout (feed the native replay harness, or save for offline inspection). - diagnose_activity_trips — fetch + decrypt + analyze in one step, printing a readable report per trip (verdict, GPS speed/noise profile, missing-ride logs). Same filters; no file round-trip.

Two ways to read the data afterwards: - diagnose_activity_trips (above) or analyze_activity_trip.py (here, on an exported JSON) — diagnose one trip so a human or agent can see why it was rejected or missed. Both share one formatter, so the report is identical. - Native replay harness (frontend/.../tools/cycling-replay) — validate algorithm changes by re-running the real on-device code against past trips (better/same/worse vs stored + feedback). Its README has the end-to-end workflow (fetch, diagnose, validate).

Running it (against prod)

# Safest first check: counts + latest trip's plaintext fields, no decryption:
alan qovery run backend-fr--prod "flask gamification export_activity_trips --probe"

# Diagnose a trip in one step (fetch + decrypt + analyze, no file):
alan qovery run backend-fr--prod "flask gamification diagnose_activity_trips --external-id ABC"

# Export decrypted trips as JSON (e.g. to feed the replay harness or inspect offline):
alan qovery run backend-fr--prod "flask gamification export_activity_trips --comment not_bike" > not_bike.json

# Specific trips:
alan qovery run backend-fr--prod \
  "flask gamification export_activity_trips --external-id ABC --external-id DEF" > some.json

Both commands share the filters: --external-id (repeatable), --status, --review-status, --only-with-feedback, --comment <code>, --limit. export_activity_trips also has --probe.

Offline (on an already-exported JSON, no prod): python3 analyze_activity_trip.py trips.json.

Notes: - Read-only by construction: every query runs inside read_only_db_context() (read-only on the primary; writes can't commit). Don't pass a replica lag: the replica lacks grants on gamification_private. - Output goes to stdout (files written inside the qovery container aren't retrievable). - Decryption degrades gracefully: a blob that can't be decrypted comes back as null/[] with a logged warning.

Bundle shape (per trip)

{
  "externalId": "...", "platform": "ios",
  "status": "rejected", "reviewStatus": "pending", "rejectionReason": "unrealistic_avg_speed",
  "summary": { "distanceMeters": 1971, "startTime": "...", "endTime": "...",
               "durationSeconds": 381, "locationCount": 97, "activityType": "cycling" },
  "extraProperties": { "algorithm_version": "0.7.0", "recording_trigger": "..." },
  "activityDistribution": { "cycling": 0.8, "walking": 0.2 },  // CoreMotion ratios; empty {} on trips recorded before this change (not backfillable)
  "locations": [ { "latitude": 0, "longitude": 0, "speed": 0, "horizontal_accuracy": 0, "timestamp": 0, "activity": "cycling" } ],
  "feedback": [ { "submitted_at": "...", "comment": "missing_start_end", "payload": { /* incl. diagnostic */ } } ],
  "diagnostic": null  // the feedback's diagnostic bundle (missing_start_end / ride_not_detected / not_bike), or null
}

Privacy

Exported JSON contains raw GPS tracks and device info. Keep it local; do not commit sample exports.