ppcm/edesk-stats repository overview

⁠edesk-stats

Standalone web app for eDesk ticket statistics: cross-tabs of received and handled (closed) ticket volumes, groupable by day / week / month, by service and by user, with Excel export. Two more tabs add an inbound/outbound message volume view and a flow graph of how tickets move between services.

⁠Business definitions

• Received ticket: created_at within the period.
• Handled ticket: status === "Closed". Close date and service:
  • if the history observed the closure → real close date and services at close (the ticket's tags at that moment);
  • otherwise → fallback: bucketed on last_updated_at with the current tags (the API exposes no dedicated close date). The fallback applies to tickets closed before history started.
• Service: different source per metric.
  • Received → the entry channel's service (channel_id), via config/channel-services.json (mirrors the channel's "Associated tags" in eDesk, which the API does not expose). One ticket = one channel = one service; unmapped channel → "Canal inconnu"; ignored channel → ticket excluded.
  • Handled → the ticket's own service tags ("Services" group): where it was actually treated. Several tags → counted in each; none → "Sans service".
• User: owner_user_id resolved to a name; null → "Non assigné".

⁠Tech stack

• Backend: Node.js (ES modules) + Express (serves the JSON API and the static frontend).
• Frontend: server-served pages + vanilla JavaScript (self-contained, no third-party dependency).
• Export: Excel (.xlsx) via exceljs — one sheet per displayed table.
• Single data source: a SQLite database (cache/history.sqlite), fed automatically (initial backfill + hourly node-cron poller). No JSON cache, no button.
• Containerisation: a single container (HTTP server + integrated scheduler).

⁠Docker install

Image published on Docker Hub: ppcm/edesk-stats (multi-arch amd64/arm64).

Minimal docker-compose.yml:

services:
  edesk-stats:
    image: ppcm/edesk-stats:latest
    ports:
      - "3000:3000"
    environment:
      EDESK_API_TOKEN: "your_read_only_token"
      BACKFILL_START_DATE: "2026-01-01"
      SNAPSHOT_ENABLED: "true"
      SNAPSHOT_CRON: "0 * * * *"
    volumes:
      # host folder ./cache  <->  /app/cache in the container (see Persistence)
      - ./cache:/app/cache
    restart: unless-stopped

docker compose up -d        # http://localhost:3000

On the first start (empty volume), the initial backfill automatically loads the tickets created since BACKFILL_START_DATE, then the poller keeps the database up to date.

The inbound/outbound message metric (the "Messages" tab) is built forward by the poller (bounded by MESSAGES_FETCH_BUDGET). To populate recent message history in one pass, run the bounded, resumable backfill (re-run to continue if it stops at the budget):

node scripts/backfill-messages.js --from 2026-06-01 [--to 2026-07-01] [--budget 100000]

⁠Environment variables

⁠Message collection & rate limiting

⁠How the poller stays current (high-water mark)

Each poller run re-scans the tickets updated (last_updated_at) in a window, re-snapshots them and caches any new messages. The window does not use a fixed lookback; it starts at a persisted high-water mark (poll_watermark, stored in the DB meta table):

• Steady state — the mark equals the last successful run's date, so a normal run re-scans only the recent day(s): minimal API load.
• After downtime — if runs were missed (container stopped, crash), the mark stays back and the window auto-extends to cover the whole gap. No data is lost and no lookback needs guessing.
• SNAPSHOT_LOOKBACK_DAYS is only a minimum overlap floor (safety for in-flight updates and timezone edges). The effective window start is the earlier of that floor and the mark.
• The mark advances only on a clean run — a failed run re-scans from the same point next time instead of skipping the gap.

⁠eDesk rate limit (important)

The eDesk API has no bulk/list endpoint for messages: each message is fetched individually via GET /v1/messages/{id}. It also enforces a strict rolling rate limit that returns 429 with no Retry-After header. Bursts of concurrent requests trip it within seconds and can throttle the whole account for a while; the sustainable rate observed is roughly ~2 req/s.

Consequences:

• The client proactively throttles every request to API_MAX_PER_MINUTE (default 60, rolling 60s window) so it stays under the limit instead of only reacting to 429s. Applied at the single request choke point, it covers ticket paging, tag/user lookups and message fetches alike. Set it to 0 to disable.
• Keep MESSAGES_FETCH_CONCURRENCY low (default 2). With the per-minute cap in place, higher concurrency does not speed things up — the limiter is the ceiling; concurrency only helps saturate it when individual calls are slow (> 1s).
• If a burst still trips the limit, the client rides out the throttle episode with capped, jittered exponential backoff (API_MAX_RETRIES, API_MAX_BACKOFF_MS) instead of aborting.
• Volume is large: a busy day is several thousand messages (all directions plus system rows), so a multi-day backfill is a multi-hour job. Run it low-and-slow and unattended, ideally during quiet hours.

⁠Running a message backfill

scripts/backfill-messages.js walks the window day by day and caches missing messages. It is resumable (already-cached ids are skipped) and fault-tolerant (a day that fails is skipped and retried on the next run):

MESSAGES_FETCH_CONCURRENCY=2 node scripts/backfill-messages.js \
  --from 2026-06-01 --to 2026-07-01 --budget 100000

• Set --to to today, not the last day you care about: a message's ticket may have been updated later, and the backfill only sees a ticket on the days it was updated.
• Re-run the same command until it stops printing "stopped … re-run to continue".

⁠Keeping the cache from falling behind

Size MESSAGES_FETCH_BUDGET (per hourly run) above the peak hourly message inflow so the poller keeps pace — the default 2000 suits a few-thousand-messages/day account. If a run risks overlapping the next one, lower the budget or space out SNAPSHOT_CRON (e.g. 0 */2 * * *).

⁠Persistence

• Mount point to persist: /app/cache.
• Empty volume → automatic initial backfill; existing volume → reused as-is.
• Backup: back up that volume. Reset: empty it.