solelog/docs/superpowers/specs/2026-06-24-phase-3b2-reports-export-design.md

Phase 3b·2 — Reports + All-Users Export — Design

• Created: 2026-06-24
• Status: Approved (brainstorming) — ready for implementation plan
• Tracker: Plane (workspace solelog, project SoleLog)
• Cycle: Second of three Phase 3b cycles (after manual-sessions 3b·1, before user management)
• Touches: packages/shared, apps/api, apps/admin

Goal

An admin can open a Rapporten screen, pick a period (and optionally narrow by worker / insole type / activity), and see headline production totals plus three breakdowns — per medewerker, per handeling, per type — and export the underlying detail rows (all workers) to CSV. Today's /api/export is self-scoped to the logged-in user; this cycle adds the cross-user, filterable reporting + export the admin needs to review a week.

Done when: an admin can choose a date range (with Deze week / Deze maand / Alles presets) and optional worker/type/activity filters, see correct headline totals and three breakdown tables, and download a CSV of every matching completed session (all workers, with a Worker column) that respects the same filters.

Scope decisions (confirmed during brainstorming, 2026-06-24)

1. Both lenses on one screen — a period summary with breakdowns per worker AND per activity AND per type (same underlying query, grouped three ways), plus headline totals.
2. Filters: date range (from/to, the spine) + optional worker + optional insole type + optional activity. Default period on open = this week (Mon–today); presets Deze week / Deze maand / Alles.
3. What counts: only status='completed' sessions contribute to totals and export (active = still running, no duration; discarded = cancelled). Mirrors the current export.
4. Metrics (all four): gewerkte tijd (worked seconds, excl. paused), aantal zolen (sum of pair_count), aantal sessies (count), pauzetijd (paused seconds). Shown both as headline totals and in every breakdown row.
5. CSV = detail rows, all workers — every filtered completed session as a row, like today's export plus a leading Worker column. (Not the aggregated summary — that's visible on-screen.)
6. Presentation: tables only — headline totals card + three breakdown tables, numbers only. No chart library, no CSS bars — keeps the dependency-light ethos and is fastest to ship.

A. Backend (under the existing admin-gated /api/admin/* guard in routes/admin.ts)

Both new endpoints accept the same query params and share one filtered-query helper so report and export can never drift:

• from — ISO instant, inclusive lower bound on start_time.
• to — ISO instant, inclusive upper bound on start_time.
• user_id? — restrict to one worker.
• insole_type? — one of Kurk | Berk | 3D.
• activity_id? — restrict to one handeling.

All queries additionally force status='completed'. A shared helper buildSessionFilters({ from, to, user_id, insole_type, activity_id }) returns the Drizzle where condition array (completed + range + any provided optional filters), used by both endpoints.

GET /api/admin/report

Fetches the filtered rows once (joined to activities + user for names) and aggregates in JS in a single pass — small data, gives names for free, one code path. Returns ReportResponse:

range:       { from, to }                       // echoes the requested ISO instants
totals:      { worked_seconds, paused_seconds, pairs, sessions }
by_worker:   [{ user_id, user_name, worked_seconds, paused_seconds, pairs, sessions }]
by_activity: [{ activity_id, activity_name, worked_seconds, paused_seconds, pairs, sessions }]
by_type:     [{ insole_type, worked_seconds, paused_seconds, pairs, sessions }]

• worked_seconds sums duration_seconds (already excludes paused); paused_seconds sums paused_seconds; pairs sums pair_count; sessions counts rows.
• Headline totals equals the sum across any one breakdown (invariant worth a test).
• Breakdown arrays are sorted by worked_seconds descending. Empty range → all-zero totals and empty breakdown arrays.
• A row whose insole_type is null is bucketed under a 'Onbekend'-style key in by_type (defensive — manual edits allow null type). A row missing an activity/user name falls back to a readable label, never crashes the grouping.

GET /api/admin/export

Same filters; returns CSV detail rows (all workers). Columns: Worker, ID, Task, Insole Type, No. of Insoles, Date, Total Duration, Paused Duration, Start Time, End Time. Content-Type: text/csv; charset=utf-8; Content-Disposition: attachment; filename="solelog-report_<from-date>_<to-date>.csv" (dates as YYYY-MM-DD). Ordered by start_time ascending.

DRY refactor (light, in lib/csv.ts)

Extract the row/header builder currently inline in sessions.ts's /api/export into a shared buildSessionsCsv(rows, { includeWorker }):

• includeWorker: false → existing 9-column format, used by the self-scoped worker export (/api/export) — output byte-identical to today, so existing tests still pass.
• includeWorker: true → prepends a Worker column, used by /api/admin/export.

Reuses the existing quote + formatDuration helpers. One source of truth for the CSV format.

Validation / errors

from/to required and must parse as dates with to ≥ from → else 400. insole_type (if given) must be a valid InsoleType; activity_id/user_id (if given) are applied as filters (an unknown id simply yields an empty result, not an error). Admin guard already returns 401 (no session) / 403 (non-admin) for the whole /api/admin/* surface.

B. Shared contracts (@solelog/shared)

Add zod schemas + inferred types:

• ReportTotals — { worked_seconds, paused_seconds, pairs, sessions } (all int).
• ReportWorkerRow, ReportActivityRow, ReportTypeRow — ReportTotals plus the grouping key(s) (user_id+user_name; activity_id+activity_name; insole_type).
• ReportResponse — { range: { from, to }, totals, by_worker, by_activity, by_type }.

Query params are validated in the route (not a shared schema). The client is responsible for sending from/to as ISO instants spanning whole local days (start-of-from-day … end-of-to-day in the admin's browser tz), so server-side timezone handling is unnecessary.

C. Admin UI (apps/admin)

• components/Sidebar.tsx — move 'Rapporten' from soonItems into navItems ({ to: '/rapporten', label: 'Rapporten' }); soonItems becomes ['Gebruikers'].
• App.tsx — add <Route path="/rapporten" element={<Reports />} />.
• screens/Reports.tsx — the screen:
  • Filter bar: from/to date inputs; preset buttons Deze week (default on mount) / Deze maand / Alles; worker <select> (from useAdminUsers); insole-type <select> (Kurk/Berk/3D); activity <select> (from the admin activities hook). Changing any control updates the filter state → query refetches.
  • Headline card: "Totaal: {worked} gewerkt · {pairs} zolen · {sessions} sessies · {paused} pauze" for the chosen period.
  • Three tables: Per medewerker / Per handeling / Per type — each row shows the grouping label + the four metrics (durations via a shared formatTime). Empty state per table.
  • Exporteer CSV button → downloadExport(filters).
• api/reports.ts:
  • useReport(filters) — useQuery keyed ['admin','report', filters], calls GET /api/admin/report?… via apiFetch.
  • downloadExport(filters) — because the endpoint is bearer-auth'd, do a raw fetch with the Authorization header (token from the same place apiFetch reads it), read the Blob, create an object URL, click a transient <a download>, then revoke the URL. (A plain <a href> can't attach the bearer token.)
  • A small filtersToQuery(filters) builds the querystring (omits empty optional filters; maps the local-day date pickers to ISO instants).
• Reuses useAdminUsers (built in 3b·1) and the existing admin activities query hook for the dropdowns. No new shared UI library.

Error handling

• Report query error → the screen shows "Kon rapport niet laden." (mirrors other admin screens).
• Export failure (non-2xx) → a brief inline notice near the button; no download is triggered.
• Invalid/missing range is prevented client-side (presets always set a valid range; manual inputs are clamped so to ≥ from), and the API still rejects a bad range with 400 as a backstop.

Testing

• API (admin.test.ts / a new report.test.ts):
  • report totals correct and equal to the sum of each breakdown; completed-only (active/discarded excluded); date-range boundary (a session exactly at from/to included; just outside excluded); worker / type / activity filters each narrow correctly; empty range → zeros + empty arrays; null insole_type bucketed, not crashing.
  • export returns all-users rows with a leading Worker column; respects the same filters; filename carries the range; both endpoints 401 (no session) / 403 (non-admin).
  • buildSessionsCsv: includeWorker:false output identical to the pre-refactor worker export (regression guard); includeWorker:true prepends the Worker column.
• Admin (vitest + Testing Library):
  • Reports renders headline totals + the three tables from a mocked report payload.
  • changing a filter / clicking a preset refetches with the expected query params.
  • the Exporteer CSV button calls the download helper with the current filters (fetch mocked).

Out of scope (later cycle / deliberately excluded)

• The Gebruikers screen and any user create / role / deactivate (the third 3b cycle).
• Charts / visualization (tables-only chosen).
• Real-time/auto-refresh of the report (it's on-demand per filter; React Query refetch-on-focus is enough).
• Per-day time-series breakdown — the three chosen breakdowns (worker/activity/type) cover the MVP; a by-day table can be added later if wanted.

Build approach

spec → writing-plans → one Workflow (~6–7 TDD tasks, commit per task, final verify), sequential (dependent tasks share one working tree). Tracked as a Plane epic with one child task per workflow task.