174 lines
10 KiB
Markdown
174 lines
10 KiB
Markdown
# 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.
|