# Project Analytics — Implementation Plan

**Spec:** docs/specs/2026-05-31-project-analytics.md  ·  **Slug:** project-analytics  ·  **Wave:** 7
**Depends on:** expenses-module, foundation-auth-rbac, invoices-core, projects-module, time-management

## Goal
Add an **Analytics** tab to the project detail view (`/projects/:id`) that presents the per-project financial and operational picture: time burn (total / this-week / last-week, by team member, 8-week weekly trend), task completion, optional hourly-budget bar, and — for Business+ tenants only — a revenue & profitability overlay (invoiced / collected / outstanding, expenses, team cost, gross margin). All data is computed read-only by aggregating existing upstream tables; this spec introduces **no new tables**. It complements the operational Overview tab (`projects-module`) and the cross-project `/reports/profitability` rollup (`profitability-reports`).

## Architecture
- **One read-only API route** `GET /api/projects/:id/analytics` in the API worker (Hono). It aggregates over upstream tables and returns a single JSON payload with `time`, `tasks`, optional `budget`, and optional `financials` blocks.
- **Operational data (always returned)** is computed from:
  - `time_entries` (time-management): `duration_seconds`, `started_at`, `user_id`, `contractor_id`, `project_id`, `tenant_id`.
  - `tasks` + `task_statuses` (tasks-board-engine, wave 4 — present at build time): task counts; "done" = task whose `status_id` points at a `task_statuses` row with `is_terminal = true`.
  - `users` (foundation): display names for the by-member breakdown.
  - `projects.billing_config` JSONB (projects-module): `budget_hours` / `budget_alert_pct` for the budget bar, and `rate_per_hour` as the fallback team-cost rate.
- **Financial overlay (Business+ only, returned only when tier+permission allow)** is computed from:
  - `invoices` (invoices-core): `status`, `total`, `amount_paid`, `project_id`. `amount_paid` is a base column owned by `invoices-core` (wave 6, present before this wave); `partial-payment-recording` (spec 80) maintains its value. Use `COALESCE(amount_paid, 0)` for null-safety.
  - `expenses` (expenses-module): `amount` (ILS-normalized gross), `project_id`.
  - `contractor_assignments` / `contractors` (contractor-payouts, spec 51 — **soft runtime dependency**): contractor rate sources for the team-cost formula. `time_entries.contractor_id` is already a forward reference into `contractors` (the codebase tolerates this cross-wave reference), so the LEFT JOIN against these tables is built verbatim from the spec. Team cost effective-rate chain: `COALESCE(contractor_assignments.rate_override, contractors.hourly_rate, projects.billing_config->>'rate_per_hour')`.
- **Tier + permission gating:** operational blocks require `projects:read`. The `financials` block requires BOTH `invoices:read` permission AND Business+ tier (`meetsMinimumTier` / `requireTier`). When the caller is below Business+ or lacks `invoices:read`, the route omits `financials` entirely and the UI shows an upgrade/locked card.
- **UI:** a new lazy-loaded `Analytics` tab component inside the existing project-detail tab set (`projects-module`), consuming the route via a `useProjectAnalytics` react-query hook. Charts are rendered with accessible primitives (bars have text labels + `aria` values; respects `prefers-reduced-motion`).

## Tech Stack
- **API worker** (`apps/zync-api`, Hono on Cloudflare Workers): new route file, Drizzle SQL via `tenantQuery`, Neon Postgres over Hyperdrive (`DB` binding).
- **App** (`apps/zync-app`, Vite + React): new tab component + react-query hook, using `@zync/ui` primitives (`Card`, `StatCard`, `Progress`, `Tabs`, `EmptyState`, `Skeleton`) and `@zync/types`.
- **Packages:** `@zync/db` (Drizzle table refs), `@zync/auth` (`requirePermission`, `requireTier`/`meetsMinimumTier`, `authMiddleware`), `@zync/types` (shared response types), `@zync/ui`.
- **Validation:** Zod query schema (`weeks` int, default 8).
- **Bindings:** `DB` (Hyperdrive→Neon). No new bindings, no new migrations.

## Wave Plan
| Sub-wave | Tasks | Files touched | Parallelizable? |
|----------|-------|---------------|-----------------|
| A — types & query schema | 1 | `packages/types/src/project-analytics.ts`, `apps/zync-api/src/routes/projects/analytics.schema.ts` | Yes |
| B — aggregation service | 2 | `apps/zync-api/src/services/project-analytics.ts` | After A |
| C — API route + gating | 3 | `apps/zync-api/src/routes/projects/analytics.ts`, route registration | After B |
| D — react-query hook | 4 | `apps/zync-app/src/features/projects/hooks/useProjectAnalytics.ts` | After A (parallel with B/C) |
| E — Analytics tab UI | 5, 6, 7 | `apps/zync-app/src/features/projects/analytics/*`, project-detail tab registration | After C, D |

## Tasks

### Task 1: Shared types + query schema
**Blocks:** 2, 3, 4  ·  **Blocked by:** —
**Files:**
- Create: `packages/types/src/project-analytics.ts`
- Create: `apps/zync-api/src/routes/projects/analytics.schema.ts`
- Modify: `packages/types/src/index.ts` (re-export)
**Steps:**
- [ ] Define the response TypeScript types (below) and export them from `@zync/types`.
- [ ] Define the Zod query schema validating `weeks` (positive integer, default 8, max 52 to bound the trend query).
**Schema / Interfaces:**
```ts
// packages/types/src/project-analytics.ts
export interface ProjectAnalyticsMember {
  user_id: string;          // users.id (UUID) or contractor_id; null-name fallback "Unknown"
  name: string;
  hours: number;            // rounded to 1 decimal
  pct: number;              // 0–100, share of total_hours
}
export interface ProjectAnalyticsWeekPoint {
  week_start: string;       // ISO date (Monday of the week)
  hours: number;
}
export interface ProjectAnalyticsTime {
  total_hours: number;
  this_week_hours: number;
  last_week_hours: number;
  by_member: ProjectAnalyticsMember[];
  weekly_trend: ProjectAnalyticsWeekPoint[];
}
export interface ProjectAnalyticsTasks {
  total: number;
  done: number;             // tasks in an is_terminal status
  open: number;             // total - done
}
export interface ProjectAnalyticsBudget {
  budget_hours: number;
  logged_hours: number;
  over_budget: boolean;     // logged_hours > budget_hours
}
export interface ProjectAnalyticsFinancials {
  invoiced_total: number;
  collected_total: number;
  outstanding_total: number;
  invoice_count: number;
  expenses_total: number;
  expense_count: number;
  team_cost: number;
  gross_margin: number;     // invoiced/collected basis per Task 2 formula
  gross_margin_pct: number; // 0–100, rounded
}
export interface ProjectAnalyticsResponse {
  time: ProjectAnalyticsTime;
  tasks: ProjectAnalyticsTasks;
  budget?: ProjectAnalyticsBudget;        // present only if budget_hours set
  financials?: ProjectAnalyticsFinancials; // present only if Business+ AND invoices:read
}
```
```ts
// apps/zync-api/src/routes/projects/analytics.schema.ts
import { z } from 'zod';
export const projectAnalyticsQuerySchema = z.object({
  weeks: z.coerce.number().int().positive().max(52).default(8),
});
export type ProjectAnalyticsQuery = z.infer<typeof projectAnalyticsQuerySchema>;
```
**Acceptance:**
- [ ] `@zync/types` exports `ProjectAnalyticsResponse` and all sub-interfaces.
- [ ] `projectAnalyticsQuerySchema` defaults `weeks` to 8 and rejects non-positive / >52 values.

### Task 2: Aggregation service (`getProjectAnalytics`)
**Blocks:** 3  ·  **Blocked by:** 1
**Files:**
- Create: `apps/zync-api/src/services/project-analytics.ts`
**Steps:**
- [ ] Implement `getProjectAnalytics(db, { tenantId, projectId, weeks, includeFinancials })` returning `ProjectAnalyticsResponse`.
- [ ] All queries are tenant-scoped via `tenantQuery` (never raw Drizzle from routes — obey `no-raw-drizzle-from-routes`): every query filters `tenant_id = :tenantId AND project_id = :projectId`.
- [ ] **Time totals:** `total_hours = SUM(duration_seconds)/3600` over `time_entries` where `project_id` matches and `duration_seconds IS NOT NULL` (exclude running timers). `this_week_hours` / `last_week_hours` bucket by ISO week against `started_at` using the tenant timezone (`tenants.default_timezone`) — current week = Monday 00:00 of the current week; last week = the prior Monday..Sunday window.
- [ ] **By-member:** group time by `time_entries.user_id` LEFT JOIN `users` (name); for entries where `user_id IS NULL AND contractor_id IS NOT NULL`, group by `contractor_id` LEFT JOIN `contractors` (name). Compute `hours` and `pct = hours / total_hours * 100` (guard divide-by-zero → 0). Sort descending by hours. Fallback name `'Unknown'`.
- [ ] **Weekly trend:** generate the last `weeks` ISO-week buckets ending with the current (in-progress) week; for each, `SUM(duration_seconds)/3600` of entries whose `started_at` falls in that Monday..Sunday window. Emit `week_start` as the Monday ISO date. Zero-fill empty weeks so the array length equals `weeks`.
- [ ] **Tasks:** `total = COUNT(tasks)` for the project; `done = COUNT` of tasks whose `status_id` joins to a `task_statuses` row with `is_terminal = true`; `open = total - done`.
- [ ] **Budget:** read `projects.billing_config->>'budget_hours'`. If non-null, set `budget = { budget_hours, logged_hours: total_hours, over_budget: total_hours > budget_hours }`; otherwise omit `budget`.
- [ ] **Financials (only when `includeFinancials`):** compute the block below; otherwise leave `financials` undefined.
- [ ] Round hours to 1 decimal and currency to 2 decimals on output.
**Schema / Interfaces:**
```ts
// Effective-rate team cost — transcribed verbatim from the spec.
// LEFT JOINs tolerate NULL contractor linkage; rate chain falls back to billing_config.
//   team_cost = SUM(
//     time_entries.duration_seconds / 3600.0
//     * COALESCE(
//         contractor_assignments.rate_override,   -- contractor_assignments(contractor_id, project_id, rate_override)
//         contractors.hourly_rate,                -- contractors(id, hourly_rate)
//         (projects.billing_config->>'rate_per_hour')::numeric
//       )
//   )
// JOIN path:
//   time_entries te
//   LEFT JOIN contractor_assignments ca
//     ON ca.contractor_id = te.contractor_id AND ca.project_id = te.project_id AND ca.tenant_id = te.tenant_id
//   LEFT JOIN contractors c ON c.id = te.contractor_id AND c.tenant_id = te.tenant_id
//   JOIN projects p ON p.id = te.project_id
//   WHERE te.tenant_id = :tenantId AND te.project_id = :projectId AND te.duration_seconds IS NOT NULL
//
// Financials from invoices (status enum: DRAFT|SENT|APPROVED|TAX_ISSUED|PAID|PARTIALLY_PAID|VOID|...):
//   invoiced_total    = SUM(invoices.total)        WHERE status NOT IN ('DRAFT','VOID')
//   invoice_count     = COUNT(*)                    WHERE status NOT IN ('DRAFT','VOID')
//   collected_total   = SUM(COALESCE(invoices.amount_paid, 0))  -- amount_paid owned by invoices-core (base)
//   outstanding_total = invoiced_total - collected_total        (clamp >= 0)
//
// Expenses (expenses.amount is ILS-normalized GROSS):
//   expenses_total = SUM(expenses.amount)  WHERE project_id matches AND status = 'COMPLETED'
//   expense_count  = COUNT(*)              same filter
//
// gross_margin     = invoiced_total - expenses_total - team_cost
// gross_margin_pct = invoiced_total > 0 ? ROUND(gross_margin / invoiced_total * 100) : 0
```
**Acceptance:**
- [ ] Running timers (`duration_seconds IS NULL`) are excluded from all hour sums.
- [ ] `by_member` percentages sum to ~100 (±rounding) when `total_hours > 0`; service returns empty array when no entries.
- [ ] `weekly_trend.length === weeks` with zero-filled gaps; last bucket is the current week.
- [ ] `tasks.done` counts only `is_terminal = true` statuses; `open === total - done`.
- [ ] `team_cost` uses the COALESCE rate chain verbatim; entries with no contractor linkage fall back to `billing_config->>'rate_per_hour'`.
- [ ] `financials` is undefined when `includeFinancials` is false.

### Task 3: API route + auth/tier gating
**Blocks:** 4, 5  ·  **Blocked by:** 2
**Files:**
- Create: `apps/zync-api/src/routes/projects/analytics.ts`
- Modify: `apps/zync-api/src/routes/projects/index.ts` (register route)
**Steps:**
- [ ] Define `GET /api/projects/:id/analytics` behind `authMiddleware` and `requireModuleEnabled('projects')`.
- [ ] Enforce `requirePermission('projects:read')` for the operational payload (obey `require-zod-validation-in-routes`: parse query with `projectAnalyticsQuerySchema`).
- [ ] Verify the project exists and belongs to the session tenant (404 otherwise) before aggregating.
- [ ] Compute `includeFinancials = meetsMinimumTier(session.tier, 'business') && hasScope(session, 'invoices:read')` (i.e. Business+ tier AND `invoices:read` permission). Pass into `getProjectAnalytics`.
- [ ] Return `200` with `ProjectAnalyticsResponse`. The `financials` key is present only when `includeFinancials` is true.
- [ ] Set/inherit CSP and standard security headers from the app middleware; never echo raw SQL or internal errors.
**Schema / Interfaces:**
```ts
// GET /api/projects/:id/analytics
// query: ?weeks=8
// auth: authMiddleware + requirePermission('projects:read')
// financials gate: meetsMinimumTier(tier,'business') && hasScope(session,'invoices:read')
// 200 -> ProjectAnalyticsResponse
// 404 -> project not found in tenant
```
**Acceptance:**
- [ ] Caller without `projects:read` gets 403; unknown/foreign project id gets 404.
- [ ] Below-Business-tier caller (or one lacking `invoices:read`) receives a body with NO `financials` key.
- [ ] Business+ caller with `invoices:read` receives `financials` populated.
- [ ] `weeks` query param is validated; invalid values are rejected with 400.

### Task 4: React-query hook
**Blocks:** 5, 6, 7  ·  **Blocked by:** 1, 3
**Files:**
- Create: `apps/zync-app/src/features/projects/hooks/useProjectAnalytics.ts`
**Steps:**
- [ ] Implement `useProjectAnalytics(projectId: string, weeks = 8)` calling `GET /api/projects/:id/analytics?weeks=`, typed `ProjectAnalyticsResponse`.
- [ ] Query key `['project-analytics', projectId, weeks]`; `enabled: !!projectId`; sensible `staleTime` (e.g. 60s).
- [ ] Surface loading / error states for the UI (return `isLoading`, `isError`, `data`).
**Schema / Interfaces:**
```ts
export function useProjectAnalytics(
  projectId: string,
  weeks?: number,
): { data?: ProjectAnalyticsResponse; isLoading: boolean; isError: boolean };
```
**Acceptance:**
- [ ] Hook returns typed `ProjectAnalyticsResponse`; disabled when `projectId` is empty.

### Task 5: Analytics tab — operational view (all tiers)
**Blocks:** —  ·  **Blocked by:** 4
**Files:**
- Create: `apps/zync-app/src/features/projects/analytics/ProjectAnalyticsTab.tsx`
- Create: `apps/zync-app/src/features/projects/analytics/TimeSummary.tsx`
- Create: `apps/zync-app/src/features/projects/analytics/TaskCompletion.tsx`
- Create: `apps/zync-app/src/features/projects/analytics/WeeklyTrendChart.tsx`
- Modify: project-detail tab registration (the `Tabs` set in `projects-module`'s project detail page) to add an **Analytics** tab between **Time** and **Files**.
**Steps:**
- [ ] `ProjectAnalyticsTab` consumes `useProjectAnalytics`, renders `Skeleton` while loading and `ErrorState`/`EmptyState` (`@zync/ui`, from `error-empty-states`) on error / no data.
- [ ] **TimeSummary:** show Total logged / This week / Last week as `StatCard`s. Render the **by-member** list as labeled bars (`Progress`) with `name`, `hours`, and `pct`. If `data.budget` is present, replace the plain "Total logged" line with the budget bar: `Budget: {logged_hours}h of {budget_hours}h … {pct}% {over_budget ? 'Over budget' : ''}` using `Progress` (clamp visual fill at 100% but show the true percentage and an `⚠`/warning state when `over_budget`).
- [ ] **TaskCompletion:** show `total` / `done` (with %) / `open` and a `Progress` bar for done% (`done/total*100`, guard divide-by-zero).
- [ ] **WeeklyTrendChart:** horizontal bars for `weekly_trend`, one row per week (`week_start` label localized via the tenant locale/`hebrew-locale-dates` formatter), bar width ∝ hours, numeric hours label on each row; mark the last bucket "(in progress)".
- [ ] **A11y:** every bar exposes its value to assistive tech (`role="img"` + `aria-label`, or visible text label + `aria-valuenow`/`aria-valuemax` on `Progress`). Honor `prefers-reduced-motion` — no bar-grow animation when reduced motion is set. Charts must remain legible in RTL/Hebrew (mirror bar direction; numerals stay LTR).
**Acceptance:**
- [ ] Analytics tab appears in the project detail tab strip (order: Overview, Tasks, Time, Analytics, Files) and is reachable by keyboard.
- [ ] By-member bars, task-completion bar, and weekly-trend bars all render with text + accessible labels.
- [ ] When `budget` is present, the budget bar replaces "Total logged" and shows an over-budget warning when `over_budget` is true.
- [ ] Bars do not animate under `prefers-reduced-motion`; layout is correct in RTL.

### Task 6: Financial overlay (Business+)
**Blocks:** —  ·  **Blocked by:** 4, 5
**Files:**
- Create: `apps/zync-app/src/features/projects/analytics/FinancialOverlay.tsx`
- Modify: `apps/zync-app/src/features/projects/analytics/ProjectAnalyticsTab.tsx` (mount overlay / lock card)
**Steps:**
- [ ] When `data.financials` is present, render the **Revenue & Profitability** section: Invoiced (`invoiced_total`, `invoice_count`), Collected (`collected_total` + collected% = collected/invoiced), Outstanding (`outstanding_total`), Expenses (`expenses_total`, `expense_count`), Team cost (`team_cost`), and a divider then Gross margin (`gross_margin`, `gross_margin_pct`) with the caption "(Revenue − expenses − team cost)".
- [ ] When `data.financials` is **absent**, render the locked card: "⚠ Business+ only — upgrade to see profitability details" with an upgrade CTA wired to the upgrade/upsell modal (`useUpgradeModal` from `upgrade-upsell-modal`, if available) or a link to billing.
- [ ] Format all currency with the project currency (`projects.currency`, e.g. ₪/ILS) via the shared money formatter; numerals LTR even in RTL layout.
**Acceptance:**
- [ ] Business+ tenants with `invoices:read` see populated financial figures matching the API.
- [ ] Non-Business+ (or no `invoices:read`) tenants see the locked upgrade card and never see figures (data is absent client-side, not merely hidden).
- [ ] Currency renders with the project currency symbol; gross margin shows both amount and percentage.

### Task 7: Loading / empty / error states + wiring verification
**Blocks:** —  ·  **Blocked by:** 5, 6
**Files:**
- Modify: `apps/zync-app/src/features/projects/analytics/ProjectAnalyticsTab.tsx`
**Steps:**
- [ ] Loading: `Skeleton` placeholders for each section.
- [ ] Empty project (no time, no tasks): show `EmptyState` ("No analytics yet — log time or add tasks to see this project's analytics") instead of zeroed bars where appropriate, while still showing financials if Business+.
- [ ] Error: `ErrorState` with retry that re-runs the query.
- [ ] Verify the tab lazy-loads (code-split) so the project detail bundle is not bloated for users who never open Analytics.
**Acceptance:**
- [ ] Loading, empty, and error states each render distinctly.
- [ ] The Analytics tab chunk is loaded only when the tab is opened.