# Time Reports (`/reports/time`) — Implementation Plan

**Spec:** docs/specs/2026-05-31-time-reports.md  ·  **Slug:** time-reports  ·  **Wave:** 6
**Depends on:** foundation-auth-rbac, projects-module, time-management

## Goal
Deliver aggregated time reporting for staff and managers at `/reports/time` in `zync-app`, with three views (By Person, By Project, By Task), shared period/user/project filters, inline drill-downs, and CSV/XLSX export. This spec owns the reports UI and a read-only aggregation API; it consumes (never mutates) the `time_entries` model owned by `time-management`. All aggregation is on-demand `GROUP BY` over `time_entries` — no pre-aggregation table is created.

## Architecture
The feature is purely read-side. Two new Hono routes (`GET /api/time/reports`, `GET /api/time/reports/export`) live in the existing time router in `apps/zync-api`. Both gate with `requirePermission('time:read')` and run all queries through the `tenantQuery(tenantId)` helper from `@zync/db` (never raw Drizzle from routes — lint `no-raw-drizzle-from-routes`). A single aggregation helper in `@zync/db` builds the `GROUP BY` SQL keyed by dimension (`person | project | task`), joining:
- `time_entries.user_id → users.id` (staff rows; `users.display_name`)
- `time_entries.contractor_id → contractors.id` (contractor rows; `contractors.name`) — `user_id` is NULL for these
- `time_entries.project_id → projects.id` (`projects.name`, `projects.customer_id → customers.id` for `customers.name`)
- `time_entries.task_id → tasks.id` (`tasks.title`; NULL grouped as "[No task]")

Authorization scope reconciliation (single rule applied identically in both endpoints): `requirePermission('time:read')` is required to enter. The caller may see all users' entries only if they hold `reports:read`; otherwise the query is force-scoped to `userId = currentUser.id` (MEMBER scope-lock) and the user filter is hidden in the UI. `time:read` and `reports:read` are seeded upstream by `foundation-auth-rbac` — referenced here, never redefined.

The UI is a React page in `apps/zync-app` using `Tabs`, `DataTable`, and `EmptyState` from `@zync/ui`. Drill-downs are implemented as a second filtered call to `GET /api/time/reports` (By Person row → `groupBy=project&userId=<row>`; By Project row → `groupBy=person&projectId=<row>`). Export reuses the same aggregation and streams a UTF-8 (BOM-prefixed) CSV or an XLSX workbook.

## Tech Stack
- **apps/zync-api** (Hono on Cloudflare Workers): two GET routes added to the time router; `requirePermission`, `requireModuleEnabled`, zod validation, `buildPaginated` not needed (full result set), `tenantQuery`.
- **packages/db** (`@zync/db`, Drizzle): aggregation query helper `getTimeReport`, exported types.
- **apps/zync-app** (Vite + React): `/reports/time` route, page, shared controls, three tab components, export dropdown; TanStack Query hook `useTimeReport`.
- **packages/ui** (`@zync/ui`): consumes existing `Tabs`, `DataTable`, `EmptyState`, `Select`, `Button`, `DropdownMenu`.
- Export: `xlsx` (SheetJS) for the XLSX path; CSV hand-built with BOM. No new Cloudflare bindings.
- Cross-cutting: design tokens only (lint `no-hardcoded-colors` / `no-hardcoded-spacing`); zod in routes (lint `require-zod-validation-in-routes`); ARIA tablist/tab/tabpanel via `Tabs`; `prefers-reduced-motion` respected by shared UI.

## Wave Plan
| Sub-wave | Tasks | Files touched | Parallelizable? |
|----------|-------|---------------|-----------------|
| A | 1, 2 | `packages/db` (aggregation helper + types), shared format util | Yes (1 and 2 independent) |
| B | 3, 4 | `apps/zync-api` time router routes (aggregate + export) | After A; 3 before 4 |
| C | 5 | `apps/zync-app` query hook | After B |
| D | 6, 7, 8 | `apps/zync-app` page, controls, tabs | After C; 6→7→8 sequential within page |
| E | 9 | export dropdown wiring | After 4 and 8 |

## Tasks

### Task 1: Time-formatting utility (`formatHoursMinutes`)
**Blocks:** 6, 7, 8  ·  **Blocked by:** —
**Files:**
- Create: `packages/ui/src/lib/format-duration.ts`
- Modify: `packages/ui/src/index.ts` (export `formatHoursMinutes`)
**Steps:**
- [ ] Implement `formatHoursMinutes(seconds: number): string` returning `"{H}h {MM}m"` (e.g. `34h 20m`); minutes zero-padded to 2 digits; floor to whole minutes.
- [ ] Implement `secondsToDecimalHours(seconds: number): number` returning hours rounded to 2 decimals (used by export quantity columns).
- [ ] No locale-specific separators; pure numeric formatting (RTL-safe — digits render LTR inside RTL via `Tabs`/`DataTable` bidi handling).
**Schema / Interfaces:**
```ts
export function formatHoursMinutes(totalSeconds: number): string;
export function secondsToDecimalHours(totalSeconds: number): number;
```
**Acceptance:**
- [ ] `formatHoursMinutes(123620)` returns `"34h 20m"`; `formatHoursMinutes(0)` returns `"0h 00m"`.
- [ ] `secondsToDecimalHours(5400)` returns `1.5`.

### Task 2: Aggregation query helper `getTimeReport` in `@zync/db`
**Blocks:** 3  ·  **Blocked by:** —
**Files:**
- Create: `packages/db/src/queries/time-reports.ts`
- Modify: `packages/db/src/index.ts` (export `getTimeReport`, `TimeReportRow`, `TimeReportResult`, `TimeReportGroupBy`, `getTimeReportParamsSchema`)
**Steps:**
- [ ] Define `TimeReportGroupBy = 'person' | 'project' | 'task'`.
- [ ] Implement `getTimeReport(db, params)` running all queries through `tenantQuery(params.tenantId)` (no raw `db.select` from routes; this helper is the DB-layer boundary).
- [ ] Apply common WHERE: `tenant_id = :tenantId`, `started_at >= :from`, `started_at < :toExclusive` (caller passes `to` as inclusive ISO date; helper adds one day for exclusive upper bound), and `stopped_at IS NOT NULL` (exclude running timers from reports). Only count `duration_seconds` that is NOT NULL.
- [ ] If `params.scopedUserId` is set (MEMBER scope-lock), add `user_id = :scopedUserId`. Else apply optional `userId` / `projectId` filters when present.
- [ ] **groupBy = 'person':** aggregate over BOTH staff and contractor entries. Build a unified subject key: `COALESCE(user_id::text, 'c:' || contractor_id::text)`. Return per subject: `subjectId` (user or contractor UUID), `subjectType` (`'user' | 'contractor'`), `name` (`users.display_name` for staff, `contractors.name` for contractors via LEFT JOINs), `roleName` (`roles.name` via `tenant_memberships`→`roles` for staff; literal `'Contractor'` for contractor rows), `hoursSeconds` = `SUM(duration_seconds)`, `billableSeconds` = `SUM(duration_seconds) FILTER (WHERE billable = true)`, `entries` = `COUNT(*)`. Order: staff first then contractors, each by `name`.
- [ ] **groupBy = 'project':** GROUP BY `project_id`; return `projectId`, `projectName` (`projects.name`), `customerName` (`customers.name` via `projects.customer_id`, NULL → `null`), `hoursSeconds`, `billableSeconds`, `entries`, and `members` = `array_agg(DISTINCT COALESCE(users.display_name, contractors.name))`. Order by `projectName`.
- [ ] **groupBy = 'task':** GROUP BY `task_id, project_id, COALESCE(user_id,contractor_id)`; return `taskId` (nullable), `taskTitle` (`tasks.title`, NULL → render label "[No task]" handled in UI; helper returns `null`), `projectId`, `projectName`, `subjectName` (display_name or contractor name), `hoursSeconds`, `billableSeconds`, `entries`. Order by `projectName`, then `taskTitle` NULLS LAST, then `subjectName`.
- [ ] Compute `totals`: `hours` = SUM of all `hoursSeconds`, `billableHours` = SUM of all `billableSeconds`, `entries` = SUM of all `entries`, computed in the same query via a separate aggregate (or summed in JS from rows). Return seconds (UI formats).
- [ ] Define and export `getTimeReportParamsSchema` (zod) describing the validated param shape so the route can reuse it.
**Schema / Interfaces:**
```ts
export type TimeReportGroupBy = 'person' | 'project' | 'task';

export interface TimeReportParams {
  tenantId: string;
  groupBy: TimeReportGroupBy;
  from: string;            // inclusive ISO date YYYY-MM-DD
  to: string;              // inclusive ISO date YYYY-MM-DD
  userId?: string;         // optional filter (ignored when scopedUserId set)
  projectId?: string;      // optional filter
  scopedUserId?: string;   // when set, forces user_id = scopedUserId (MEMBER lock)
}

export interface TimeReportRow {
  // person rows
  subjectId?: string;
  subjectType?: 'user' | 'contractor';
  name?: string;
  roleName?: string;
  // project rows
  projectId?: string;
  projectName?: string;
  customerName?: string | null;
  members?: string[];
  // task rows
  taskId?: string | null;
  taskTitle?: string | null;
  subjectName?: string;
  // shared metrics (seconds)
  hoursSeconds: number;
  billableSeconds: number;
  entries: number;
}

export interface TimeReportResult {
  rows: TimeReportRow[];
  totals: { hours: number; billableHours: number; entries: number }; // seconds for hours/billableHours
}

export const getTimeReportParamsSchema: import('zod').ZodType<Omit<TimeReportParams, 'tenantId' | 'scopedUserId'>>;

export function getTimeReport(
  db: import('./client').Db,
  params: TimeReportParams,
): Promise<TimeReportResult>;
```
**Acceptance:**
- [ ] Helper uses `tenantQuery(tenantId)` for every query; no bare `db.select(...)` against tenant tables.
- [ ] Running timers (`stopped_at IS NULL`) and NULL-duration entries are excluded from all dimensions and totals.
- [ ] Contractor entries (`user_id IS NULL, contractor_id` set) appear in `person` results with `subjectType='contractor'`, `roleName='Contractor'`, name from `contractors.name`.
- [ ] `billableSeconds` counts only rows where `billable = true`.
- [ ] `taskTitle` is `null` for entries with no `task_id`.

### Task 3: Route `GET /api/time/reports`
**Blocks:** 5, 9  ·  **Blocked by:** 2
**Files:**
- Modify: `apps/zync-api/src/routes/time.ts` (add aggregation route to existing time router)
**Steps:**
- [ ] Add `GET /reports` under the time router (full path `/api/time/reports`), wrapped by `authMiddleware`, `requireModuleEnabled('time')` (consistent with existing time routes), and `requirePermission('time:read')`.
- [ ] Validate query with zod (lint `require-zod-validation-in-routes`): `groupBy ∈ {person,project,task}` (required), `from`/`to` ISO date `YYYY-MM-DD` (required; reject if `from > to`), optional `userId` (uuid), optional `projectId` (uuid). Reuse `getTimeReportParamsSchema` from `@zync/db`.
- [ ] Determine scope: read caller permissions from session; if caller lacks `reports:read`, set `scopedUserId = currentUser.id` and ignore any incoming `userId`. If caller has `reports:read`, pass through optional `userId`.
- [ ] Call `getTimeReport(db, { tenantId, groupBy, from, to, userId, projectId, scopedUserId })`.
- [ ] Respond `200 { rows, totals }` exactly matching the spec's contract `{ rows: [...], totals: { hours, billableHours, entries } }`. Return `hours`/`billableHours` as decimal hours (2dp) in the API totals using `secondsToDecimalHours`, and include raw `*Seconds` on each row for the UI to format.
**Acceptance:**
- [ ] Request without `time:read` → `403`.
- [ ] Caller lacking `reports:read` cannot retrieve other users' rows: passing `userId=<other>` is ignored and results are locked to self.
- [ ] Invalid `groupBy` or malformed date → `400` from zod.
- [ ] Query path uses `tenantQuery` via the `@zync/db` helper (no raw Drizzle in the route).

### Task 4: Route `GET /api/time/reports/export` (CSV / XLSX)
**Blocks:** 9  ·  **Blocked by:** 3
**Files:**
- Modify: `apps/zync-api/src/routes/time.ts`
- Create: `apps/zync-api/src/lib/time-report-export.ts` (row → CSV/XLSX serializers)
**Steps:**
- [ ] Add `GET /reports/export` (full path `/api/time/reports/export`) with the same middleware + permission gate (`time:read`) and the same scope reconciliation as Task 3.
- [ ] Validate query: same schema as Task 3 plus `format ∈ {csv,xlsx}` (required).
- [ ] Call `getTimeReport` with identical params so the export reflects the active tab + filters.
- [ ] Build column sets per `groupBy`:
  - person: `Name, Role, Hours, Billable Hours, Entries`
  - project: `Project, Customer, Hours, Members, Entries`
  - task: `Task, Project, Person, Hours, Billable Hours, Entries`
  - Hours columns rendered as `"{H}h {MM}m"` via `formatHoursMinutes`; `[No task]` substituted for null task titles; empty customer rendered as empty cell; `members` joined with `, `.
- [ ] **CSV:** prepend UTF-8 BOM (``) so Excel renders Hebrew names correctly; RFC-4180 quoting (escape `"`/`,`/newlines). `Content-Type: text/csv; charset=utf-8`, `Content-Disposition: attachment; filename="time-report-{groupBy}-{from}_{to}.csv"`.
- [ ] **XLSX:** build a single-sheet workbook with `xlsx` (SheetJS); first row = headers. `Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet`, attachment filename `.xlsx`.
**Acceptance:**
- [ ] CSV output begins with the UTF-8 BOM byte sequence; a Hebrew display name opens correctly in Excel without mojibake.
- [ ] XLSX downloads as a valid single-sheet workbook with header row matching the active dimension.
- [ ] Export honors all filters and the MEMBER scope-lock identically to the aggregate endpoint.
- [ ] `format` other than `csv`/`xlsx` → `400`.

### Task 5: Client data hook `useTimeReport`
**Blocks:** 6, 7, 8  ·  **Blocked by:** 3
**Files:**
- Create: `apps/zync-app/src/features/time-reports/useTimeReport.ts`
- Create: `apps/zync-app/src/features/time-reports/types.ts`
**Steps:**
- [ ] Define `TimeReportFilters { groupBy; period; from; to; userId?; projectId? }` and a `PERIOD_PRESETS` map resolving each preset (This Week, Last Week, This Month, Last Month, This Quarter, Custom) to concrete `{ from, to }` ISO dates at call time.
- [ ] Implement `useTimeReport(filters)` (TanStack Query) calling `GET /api/time/reports` with serialized query params; key includes all filter fields. Return `{ rows, totals, isLoading, error }`.
- [ ] Implement `buildExportUrl(filters, format)` returning the `/api/time/reports/export` URL with query params (used by the export dropdown to trigger a browser download).
**Schema / Interfaces:**
```ts
export type PeriodPreset =
  | 'this_week' | 'last_week' | 'this_month'
  | 'last_month' | 'this_quarter' | 'custom';

export interface TimeReportFilters {
  groupBy: 'person' | 'project' | 'task';
  period: PeriodPreset;
  from: string;   // ISO date
  to: string;     // ISO date
  userId?: string;
  projectId?: string;
}

export function useTimeReport(filters: TimeReportFilters): {
  rows: TimeReportRow[];
  totals: { hours: number; billableHours: number; entries: number };
  isLoading: boolean;
  error: unknown;
};

export function buildExportUrl(filters: TimeReportFilters, format: 'csv' | 'xlsx'): string;
```
**Acceptance:**
- [ ] Changing any filter refetches (query key reflects all fields).
- [ ] Preset selection resolves to concrete `from`/`to`; `custom` uses the user-picked dates.

### Task 6: `/reports/time` page shell + shared controls
**Blocks:** 7, 8, 9  ·  **Blocked by:** 1, 5
**Files:**
- Create: `apps/zync-app/src/features/time-reports/TimeReportsPage.tsx`
- Create: `apps/zync-app/src/features/time-reports/ReportControls.tsx`
- Modify: `apps/zync-app/src/router.tsx` (register `/reports/time` route, lazy-loaded; guarded by `requirePermission('time:read')` route guard)
**Steps:**
- [ ] Build `TimeReportsPage` with header title "Time Reports", an Export dropdown slot (filled in Task 9), and a `Tabs` (`@zync/ui`) with three tabs: By Person, By Project, By Task. `Tabs` supplies `role="tablist"/"tab"/"tabpanel"` semantics.
- [ ] Hold filter state (`TimeReportFilters`) at the page level so it is shared across tabs; switching tabs only changes `groupBy` and keeps period/user/project filters.
- [ ] Build `ReportControls`: Period `Select` (presets), Custom `from`/`to` date inputs (ISO date, no time; shown only when period = custom), User `Select`, Project `Select`. All `@zync/ui` components; design tokens only.
- [ ] **User filter visibility:** fetch caller capability; if caller lacks `reports:read` (MEMBER), hide the User `Select` entirely and lock to self (do not render the control). OWNER/ADMIN (or `reports:read` holders) see "All Users" + every user + contractors.
- [ ] Populate Project `Select` from existing projects list query; User `Select` from existing tenant members query (reuse upstream hooks; do not add new endpoints).
- [ ] Respect `prefers-reduced-motion` for any tab transition (inherited from `@zync/ui`).
**Acceptance:**
- [ ] Route `/reports/time` renders behind `time:read`; unauthorized users are blocked.
- [ ] MEMBER (no `reports:read`) sees no User filter; the report shows only their own entries.
- [ ] Tabs expose correct ARIA roles; filters persist across tab switches.

### Task 7: By Person & By Project tabs with drill-downs
**Blocks:** 9  ·  **Blocked by:** 6
**Files:**
- Create: `apps/zync-app/src/features/time-reports/ByPersonTab.tsx`
- Create: `apps/zync-app/src/features/time-reports/ByProjectTab.tsx`
**Steps:**
- [ ] **ByPersonTab:** render `DataTable` columns `Person | Role | Hours | Billable | Entries`, using `formatHoursMinutes` for Hours/Billable. Prefix contractor rows with a `[Contractor]` indicator and group them under a "Contractors" group header, separate from staff. Render a Total footer row summing `totals`.
- [ ] Row click expands an inline panel showing that person's project breakdown — fetch via `useTimeReport({ ...filters, groupBy: 'project', userId: row.subjectId })` (staff) or scoped contractor variant; render a nested mini-table.
- [ ] **ByProjectTab:** `DataTable` columns `Project | Customer | Hours | Members` (Members = `members[]` joined). Customer empty → render em dash. Total footer row.
- [ ] Row click expands inline per-person breakdown via `useTimeReport({ ...filters, groupBy: 'person', projectId: row.projectId })`.
- [ ] Use `EmptyState` (`@zync/ui`) when `rows.length === 0` for the active period.
**Acceptance:**
- [ ] Contractors appear in a distinct group under a "Contractors" header in By Person; staff above.
- [ ] By Person row expansion shows the same person's hours split per project for the active period.
- [ ] By Project row expansion shows per-person split; empty customer shows em dash.
- [ ] Empty period renders `EmptyState`, not a blank table.

### Task 8: By Task tab
**Blocks:** 9  ·  **Blocked by:** 6
**Files:**
- Create: `apps/zync-app/src/features/time-reports/ByTaskTab.tsx`
**Steps:**
- [ ] Render `DataTable` columns `Task | Project | Person | Hours`. Hours via `formatHoursMinutes`.
- [ ] Substitute the label `[No task]` for rows where `taskTitle` is null; sort null-task rows last within each project.
- [ ] Use `EmptyState` when no rows for the active period.
**Acceptance:**
- [ ] Entries with no linked task render under the `[No task]` label.
- [ ] Columns and ordering match the spec layout (Task, Project, Person, Hours).

### Task 9: Export dropdown wiring
**Blocks:** —  ·  **Blocked by:** 4, 6, 7, 8
**Files:**
- Create: `apps/zync-app/src/features/time-reports/ExportMenu.tsx`
- Modify: `apps/zync-app/src/features/time-reports/TimeReportsPage.tsx` (mount the dropdown in the header slot)
**Steps:**
- [ ] Build `ExportMenu` with `DropdownMenu` (`@zync/ui`): items **CSV** and **Excel (XLSX)**.
- [ ] On select, build the URL via `buildExportUrl(currentFilters, 'csv' | 'xlsx')` (filters include the active tab's `groupBy`) and trigger a browser download (anchor with `download` / `window.location.assign`).
- [ ] Ensure the export reflects the currently active tab and all applied filters (groupBy taken from the active tab, not a fixed value).
- [ ] Dropdown trigger has an accessible label ("Export"); menu items keyboard-navigable (provided by `DropdownMenu`).
**Acceptance:**
- [ ] Exporting from the By Project tab with a project filter applied downloads a file containing only that filtered, project-grouped data.
- [ ] CSV and XLSX options both work and respect the MEMBER scope-lock.