I'm working on a Panoramax exporter for Prometheus to get some metrics out of a Panoramax instance. Some interesting ones are already possible via the public API. But others require direct SQL queries against the database, which is ugly and could be avoided with a few extra endpoints. Why the SQL path is undesirable: - Needs DB credentials + a dedicated read-only role. - Couples the exporter to Panoramax's internal schema — a migration can silently break metrics. - Bypasses the API's auth model entirely. Note: the API already exposes a Prometheus `/metrics` endpoint (actix / OpenTelemetry), but it only covers HTTP-level metrics (request counts, latency), not business/instance stats. So the instrumentation is already wired — business gauges would be an addition, not greenfield. ## SQL-only (no API path) | Metric | What it measures | Why it matters | Why no API | |---|---|---|---| | `panoramax_accounts_new_total{window}` | New accounts created within each window (1d/7d/30d), from `accounts.created_at`. | Signup rate / growth trend; spot spam-registration spikes. | API exposes no account-created timestamp. | | `panoramax_job_queue_depth{task}` | Pending items in `job_queue`, grouped by task type (e.g. prepare, blur). | Backlog per pipeline stage; rising depth = workers can't keep up. | Worker queue is an internal table, no endpoint. | | `panoramax_job_queue_oldest_seconds` | Age of the oldest due job (`to_do_after_ts` null or past). | Processing latency / stuck queue; high value = jobs starving. | Same internal queue; not exposed. | | `panoramax_pictures_by_status{status}` | Picture count grouped by `pictures.status` (ready / waiting / preparing / broken …). | Data-quality + pipeline health; catch broken or stuck uploads. | API serves only ready pictures; other states hidden. | | `panoramax_sequences_by_visibility{visibility}` | Sequence count grouped by visibility (public / hidden / …). | Moderation view; ratio of hidden vs public content. | API lists only public sequences. | | `panoramax_last_picture_inserted_seconds` | Seconds since the most recent `pictures.inserted_at` (−1 if none). | Ingest liveness; alert when no new pictures arrive = pipeline dead. | No picture insert-timestamp aggregate in API. | ## Partly available via API (SQL gives more) | Metric | What it measures | API fallback | What SQL adds | |---|---|---|---| | `panoramax_accounts_total` | Total registered accounts, from `count(*)` on `accounts`. | Count child links in `/api/users` STAC catalog. | Requires walking the full users catalog; SQL is one cheap `count(*)`. | | `panoramax_pictures_new_total{window}` | Pictures inserted within each window (1d/7d/30d), from `pictures.inserted_at`. | `/api/stats/by_contributor` → sum `history.last_7_days` / `last_30_days` `nb_items` across users. | Arbitrary windows incl. 1d; API only offers fixed 7d/30d buckets and only per-contributor (must be summed). | ## API, auth token required (privacy) Pulled via `GET /api/reports` with a CQL2 status filter; needs an admin/reviewer bearer token. Reports contain personal / sensitive data (reporter identity, reported content, reason text), so full read is correctly auth-gated and not available anonymously. The exporter only consumes **aggregate counts**, so it currently over-reads: it pulls full report objects just to `count()` them client-side. | Metric | What it measures | Why it matters | |---|---|---| | `panoramax_reports_total{status,issue}` | Reports grouped by status and issue type. | Moderation workload by category. | | `panoramax_reports_by_status_total{status}` | Reports grouped by status. | Open vs resolved distribution. | | `panoramax_reports_open_total` | Reports in an unresolved state (open / open_autofix / waiting). | Moderation backlog; alert when it grows. | ## Fully available via public API (no SQL, no auth) - `panoramax_sequences_total` - `panoramax_pictures_total` - `panoramax_length_km_total` - `panoramax_contributors_total` - `panoramax_user_sequences_total` - `panoramax_user_pictures_total` - `panoramax_user_length_km_total` ## Proposed endpoints (to drop the SQL dependency) - Extend `/api/stats` with `nb_sequences` and `nb_accounts` (and optionally account counts by creation window). - Add an admin-gated endpoint (or extend the API's Prometheus `/metrics`) with the operational/health metrics that have no public equivalent: - job queue depth per task + oldest-job age - pictures by status - sequences by visibility - last-picture-inserted freshness - new pictures / new accounts by window - **Reports:** an aggregate counts endpoint returning only `{status, issue → count}` (no report bodies). Still auth-gated, but privacy-safe and cheaper — the exporter needs nothing more than the counts. These are all operational/health signals (queue backlog, processing latency, failed/stuck content, moderation split, signup rate, ingest freshness). The public API only exposes finished, public content, so none are reachable without direct DB reads today.