# Profitability Reports — Implementation Plan

**Spec:** docs/specs/2026-05-31-profitability-reports.md  ·  **Slug:** profitability-reports  ·  **Wave:** 8
**Depends on:** contractor-payouts, expenses-module, foundation-auth-rbac, invoices-core, projects-module, time-management

## Goal
Deliver cross-module profitability analysis: revenue (invoiced amounts, credit notes folded in) minus costs (staff time × internal hourly cost + contractor payouts + project expenses), computed on-read per project and per customer. Gives Business+ tenants visibility into which clients and projects are actually profitable. No new tables — a single `ALTER TABLE users ADD COLUMN hourly_cost`, a profitability query package, three read-only API routes, an OWNER-only hourly-cost editor, and an accessible/RTL-aware report UI with CSV export.

## Architecture
On-read computation (no materialized view). A `@zync/profitability` query module aggregates from upstream tables already defined: `invoices` (revenue), `time_entries` + `users.hourly_cost` (staff cost), `payout_bills` + `payout_bill_lines` (contractor cost), `expenses` (expense cost), `projects` (project list + `customer_id` for customer rollup), `customers` (names). All reads go through `tenantQuery` for tenant isolation. Three Hono routes under `/api/reports/profitability` return tenant summary, per-project breakdown, and per-customer breakdown; each is gated by `requirePermission` (OWNER/ADMIN) and `requireTier`/`meetsMinimumTier` (Business+). The React report page lives in the app, consuming `DataTable`, `StatCard`, and the shared chart accessibility pattern. `users.hourly_cost` is OWNER-set, never surfaced to the user themselves or to customers; only aggregated cost leaves the API.

**Reconciliations against upstream specs (authoritative over the profitability spec's illustrative SQL):**
- Time duration column is `time_entries.duration_seconds` (time-management) → staff cost = `duration_seconds / 3600.0 * users.hourly_cost`. The spec's `duration_min / 60` is wrong.
- Contractor line table is `payout_bill_lines` with FK `bill_id` (→ `payout_bills.id`) and a direct `project_id` column → filter `pbl.project_id` and join `payout_bills pb ON pb.id = pbl.bill_id`; no `time_entries` join needed. The spec's `payout_bill_id` / time_entries join is wrong.
- Expense cost column is the canonical `expenses.amount` (ILS gross), not `invoice_total` (expenses-module Schema Reconciliation). Filter `status = 'COMPLETED'` and `project_id IS NOT NULL`.
- Contractor cost counts only realized/committed bills: `payout_bills.status IN ('APPROVED','PAID')` (honors data-source "approved payout bills"; excludes DRAFT/SENT/VOID per contractor-payouts void rule).
- Revenue is one `SUM(total)` over `status IN ('TAX_ISSUED','PARTIALLY_PAID','PAID')`; credit notes (`source='credit_note'`, already-negative totals) are inside that SUM — no separate subtraction.

## Tech Stack
- **App (React, Vite, CF Workers):** `apps/zync-app` — report pages + hourly-cost editor, TanStack Query hooks, Recharts for the margin chart.
- **API (Hono, CF Workers):** `apps/zync-api` — three profitability routes + one `users.hourly_cost` mutation on the existing user-settings surface.
- **Package:** new `@zync/profitability` (query/aggregation logic, types, CSV serializer) under `packages/profitability`.
- **DB:** Drizzle migration adding `users.hourly_cost`; queries via `@zync/db` `tenantQuery`.
- **Shared:** `@zync/auth` (`requirePermission`, `requireTier`, `meetsMinimumTier`, `authMiddleware`), `@zync/ui` (`DataTable`, `StatCard`, `Tabs`, `Card`, `Input`), `@zync/types`, `@zync/config`. Zod for request validation.
- **Bindings:** Hyperdrive (Neon Postgres) only. No new CF bindings.

## Wave Plan
| Sub-wave | Tasks | Files touched | Parallelizable? |
|----------|-------|---------------|-----------------|
| 8a | 1 (schema delta) | `packages/db` migration + schema | No (blocks all) |
| 8b | 2 (types), 3 (query module), 4 (CSV serializer) | `packages/profitability` | 3 & 4 after 2; 3 ∥ 4 |
| 8c | 5 (API routes), 6 (hourly-cost API) | `apps/zync-api` | 5 ∥ 6 |
| 8d | 7 (hooks), 8 (report page + cards), 9 (project/customer breakdown), 10 (hourly-cost editor), 11 (chart a11y+RTL) | `apps/zync-app` | 8–11 after 7; mostly ∥ |

## Tasks

### Task 1: Schema delta — `users.hourly_cost`
**Blocks:** 3, 6  ·  **Blocked by:** —
**Files:**
- Modify: `packages/db/src/schema/auth.ts` (the `users` table definition)
- Create: `packages/db/migrations/<timestamp>_users_hourly_cost.sql`
**Steps:**
- [ ] Add `hourly_cost` to the Drizzle `users` schema as `numeric('hourly_cost', { precision: 8, scale: 2 })` (nullable).
- [ ] Write the migration SQL (below). NULL means "excluded from cost calculations" — never default to 0.
- [ ] Confirm no FK/enum change; this is a single nullable numeric column on the existing `users` table.
**Schema / Interfaces:**
```sql
-- Internal cost per hour for profitability calculations.
-- NULL = excluded from cost calculations. Set by OWNER/ADMIN only;
-- never exposed to the user themselves or to customers.
ALTER TABLE users ADD COLUMN hourly_cost NUMERIC(8,2);
```
**Acceptance:**
- [ ] Migration applies cleanly on Neon Postgres; `users.hourly_cost` exists, is nullable, type `numeric(8,2)`.
- [ ] Drizzle types expose `hourlyCost: number | null` on the user row.

### Task 2: Profitability types (`@zync/profitability`)
**Blocks:** 3, 4, 7  ·  **Blocked by:** —
**Files:**
- Create: `packages/profitability/package.json` (name `@zync/profitability`, deps `@zync/db`, `@zync/types`, `zod`)
- Create: `packages/profitability/src/types.ts`
- Create: `packages/profitability/src/index.ts` (re-exports)
**Steps:**
- [ ] Define the row/summary types used by queries, API, and UI (below).
- [ ] Define the Zod query-param schema for date range + currency.
- [ ] Export everything from `index.ts`.
**Schema / Interfaces:**
```ts
export interface ProfitabilityCostBreakdown {
  staffCost: number;       // ILS; SUM(duration_seconds/3600 * hourly_cost)
  contractorCost: number;  // ILS; SUM(payout_bill_lines.line_total) on APPROVED|PAID bills
  expenseCost: number;     // ILS; SUM(expenses.amount) COMPLETED, project-linked
  totalCost: number;       // staffCost + contractorCost + expenseCost
}

export interface ProfitabilityRow {
  id: string;              // projectId or customerId
  name: string;            // project name or customer name
  revenue: number;         // SUM(invoices.total) incl. negative credit notes
  cost: number;            // totalCost
  profit: number;          // revenue - cost
  marginPct: number;       // profit / revenue * 100, 0 when revenue = 0
}

export interface ProfitabilitySummary {
  revenue: number;
  cost: number;
  profit: number;
  marginPct: number;
  from: string | null;     // ISO date applied
  to: string | null;
  currency: string;        // default tenant currency
}

export interface ProfitabilityReport {
  summary: ProfitabilitySummary;
  byProject: ProfitabilityRow[];
  byCustomer: ProfitabilityRow[];
}

export interface ProjectProfitabilityDetail {
  projectId: string;
  projectName: string;
  customerId: string | null;
  customerName: string | null;
  revenueLines: Array<{
    invoiceId: string;
    number: string | null;      // invoice_number ?? proforma_number
    issuedAt: string | null;    // tax_issue_date ?? issue_date
    amount: number;             // invoices.total (negative for credit notes)
    status: string;             // InvoiceStatus
    isCreditNote: boolean;      // source === 'credit_note'
  }>;
  revenueTotal: number;
  cost: ProfitabilityCostBreakdown;
  staffHours: number;           // SUM(duration_seconds)/3600 over costed entries
  profit: number;
  marginPct: number;
}

export interface CustomerProfitabilityDetail {
  customerId: string;
  customerName: string;
  projects: ProfitabilityRow[];  // one row per project for this customer
  summary: ProfitabilitySummary;
}

import { z } from 'zod';
export const profitabilityQuerySchema = z.object({
  from: z.string().date().optional(),
  to: z.string().date().optional(),
  currency: z.string().length(3).optional(),
});
export type ProfitabilityQuery = z.infer<typeof profitabilityQuerySchema>;
```
**Acceptance:**
- [ ] `@zync/profitability` builds; types importable from API and app.

### Task 3: Aggregation query module
**Blocks:** 5  ·  **Blocked by:** 1, 2
**Files:**
- Create: `packages/profitability/src/queries.ts`
**Steps:**
- [ ] Implement `getProfitabilityReport(db, tenantId, q)` → `ProfitabilityReport`. Computes per-project revenue and cost, joins `projects` (name) and `customers` (name via `projects.customer_id`), rolls up to customer level, and produces the tenant summary by summing project rows.
- [ ] Implement `getProjectProfitability(db, tenantId, projectId)` → `ProjectProfitabilityDetail` (revenue line list + cost breakdown + staff hours).
- [ ] Implement `getCustomerProfitability(db, tenantId, customerId)` → `CustomerProfitabilityDetail` (all projects for the customer + summary).
- [ ] All queries run through `tenantQuery` (tenant isolation) and accept optional `from`/`to` date bounds applied to: invoices on `COALESCE(tax_issue_date, issue_date)`, time_entries on `started_at::date`, payout_bill_lines via `payout_bills.period_end`, expenses on `expense_date`.
- [ ] Compute `marginPct` as `revenue === 0 ? 0 : profit / revenue * 100`. Currency defaults to `tenants.default_currency` when `q.currency` unset.
- [ ] Staff-cost query uses an INNER JOIN on `users` and `u.hourly_cost IS NOT NULL`, which inherently excludes contractor entries (`time_entries.user_id IS NULL`) — preventing double-counting against `payout_bill_lines`.
**Schema / Interfaces:**
```sql
-- Per-project revenue (credit notes already negative, captured by status filter):
SELECT i.project_id,
       COALESCE(SUM(i.total), 0) AS revenue
FROM invoices i
WHERE i.tenant_id = :tenantId
  AND i.project_id IS NOT NULL
  AND i.status IN ('TAX_ISSUED', 'PARTIALLY_PAID', 'PAID')
  AND (:from IS NULL OR COALESCE(i.tax_issue_date, i.issue_date) >= :from)
  AND (:to   IS NULL OR COALESCE(i.tax_issue_date, i.issue_date) <= :to)
GROUP BY i.project_id;

-- Staff cost (INNER JOIN users excludes contractor entries; hourly_cost NULL excluded):
SELECT te.project_id,
       COALESCE(SUM(te.duration_seconds / 3600.0 * u.hourly_cost), 0) AS staff_cost,
       COALESCE(SUM(te.duration_seconds), 0) / 3600.0                 AS staff_hours
FROM time_entries te
JOIN users u ON u.id = te.user_id
WHERE te.tenant_id = :tenantId
  AND u.hourly_cost IS NOT NULL
  AND (:from IS NULL OR te.started_at::date >= :from)
  AND (:to   IS NULL OR te.started_at::date <= :to)
GROUP BY te.project_id;

-- Contractor cost (direct project_id on the line; only committed bills):
SELECT pbl.project_id,
       COALESCE(SUM(pbl.line_total), 0) AS contractor_cost
FROM payout_bill_lines pbl
JOIN payout_bills pb ON pb.id = pbl.bill_id
WHERE pbl.tenant_id = :tenantId
  AND pbl.project_id IS NOT NULL
  AND pb.status IN ('APPROVED', 'PAID')
  AND (:from IS NULL OR pb.period_end >= :from)
  AND (:to   IS NULL OR pb.period_end <= :to)
GROUP BY pbl.project_id;

-- Expense cost (canonical `amount`, COMPLETED, project-linked):
SELECT e.project_id,
       COALESCE(SUM(e.amount), 0) AS expense_cost
FROM expenses e
WHERE e.tenant_id = :tenantId
  AND e.project_id IS NOT NULL
  AND e.status = 'COMPLETED'
  AND (:from IS NULL OR e.expense_date >= :from)
  AND (:to   IS NULL OR e.expense_date <= :to)
GROUP BY e.project_id;

-- Project + customer names for rollup:
SELECT p.id AS project_id, p.name AS project_name,
       p.customer_id, c.name AS customer_name
FROM projects p
LEFT JOIN customers c ON c.id = p.customer_id
WHERE p.tenant_id = :tenantId;
```
```sql
-- Project breakdown revenue line list (getProjectProfitability):
SELECT i.id,
       COALESCE(i.invoice_number, i.proforma_number) AS number,
       COALESCE(i.tax_issue_date, i.issue_date)       AS issued_at,
       i.total                                        AS amount,
       i.status,
       (i.source = 'credit_note')                     AS is_credit_note
FROM invoices i
WHERE i.tenant_id = :tenantId
  AND i.project_id = :projectId
  AND i.status IN ('TAX_ISSUED', 'PARTIALLY_PAID', 'PAID')
ORDER BY COALESCE(i.tax_issue_date, i.issue_date) ASC;
```
```ts
export function getProfitabilityReport(
  db: Db, tenantId: string, q: ProfitabilityQuery
): Promise<ProfitabilityReport>;

export function getProjectProfitability(
  db: Db, tenantId: string, projectId: string
): Promise<ProjectProfitabilityDetail | null>;

export function getCustomerProfitability(
  db: Db, tenantId: string, customerId: string
): Promise<CustomerProfitabilityDetail | null>;
```
**Acceptance:**
- [ ] Revenue includes negative credit-note totals via SUM (no separate subtraction path exists).
- [ ] Contractor cost excludes DRAFT/SENT/VOID bills; counts APPROVED + PAID only.
- [ ] Staff cost ignores users with `hourly_cost IS NULL` and never sums contractor (`user_id IS NULL`) entries.
- [ ] Customer rollup sums its projects' revenue/cost; `marginPct` is 0 when revenue is 0 (no divide-by-zero).
- [ ] All queries scoped by `tenant_id` via `tenantQuery`.

### Task 4: CSV serializer
**Blocks:** 5  ·  **Blocked by:** 2
**Files:**
- Create: `packages/profitability/src/csv.ts`
**Steps:**
- [ ] Implement `profitabilityToCsv(report, scope)` where `scope` is `'project' | 'customer'` — emits a CSV with header row `Name,Revenue,Cost,Profit,Margin %` and one row per `ProfitabilityRow`, followed by a totals row from `summary`.
- [ ] Quote/escape fields containing commas/quotes/newlines per RFC 4180. Numbers formatted to 2 decimals; margin to 1 decimal.
- [ ] Return `{ filename, body }` where filename is `profitability-${scope}-${from}_${to}.csv`.
**Schema / Interfaces:**
```ts
export function profitabilityToCsv(
  report: ProfitabilityReport, scope: 'project' | 'customer'
): { filename: string; body: string };
```
**Acceptance:**
- [ ] CSV opens correctly in Excel; commas inside names are escaped; totals row present.

### Task 5: Profitability API routes
**Blocks:** 7  ·  **Blocked by:** 3, 4
**Files:**
- Create: `apps/zync-api/src/routes/reports/profitability.ts`
- Modify: `apps/zync-api/src/routes/reports/index.ts` (or the API router root) to mount the routes
**Steps:**
- [ ] Mount under `authMiddleware`. On every route apply `requirePermission` for OWNER/ADMIN and `requireTier`/`meetsMinimumTier('business')` (Business+ gate). Return 403 with a tier-upgrade payload when the tenant is below Business.
- [ ] `GET /api/reports/profitability` → validate query via `profitabilityQuerySchema`, call `getProfitabilityReport`, return `ProfitabilityReport`. If `?format=csv`, call `profitabilityToCsv` (scope defaults to `project`, or `?scope=customer`) and respond `text/csv` with `Content-Disposition: attachment; filename="..."`. This serves the "Export CSV" UI button — no separate export route.
- [ ] `GET /api/reports/profitability/projects/:id` → `getProjectProfitability`; 404 if null/not in tenant.
- [ ] `GET /api/reports/profitability/customers/:id` → `getCustomerProfitability`; 404 if null/not in tenant.
- [ ] Use `createDb(env)` + `tenantQuery`; resolve `tenantId` from session.
- [ ] Assert no per-user `hourly_cost` value is ever included in any response — only aggregated `staffCost`/`cost`.
**Schema / Interfaces:**
```
GET /api/reports/profitability
    query: from?, to?, currency?, format=csv?, scope=project|customer?
    → ProfitabilityReport  (or text/csv when format=csv)
    Requires: OWNER|ADMIN, Business+

GET /api/reports/profitability/projects/:id
    → ProjectProfitabilityDetail
    Requires: OWNER|ADMIN, Business+

GET /api/reports/profitability/customers/:id
    → CustomerProfitabilityDetail
    Requires: OWNER|ADMIN, Business+
```
**Acceptance:**
- [ ] Below-Business tenants get 403 upgrade response on all three routes.
- [ ] Non-OWNER/ADMIN roles get 403 (permission) on all three routes.
- [ ] `?format=csv` downloads a CSV with the correct `Content-Disposition`.
- [ ] No response body contains an individual user's `hourly_cost`.
- [ ] Cross-tenant project/customer IDs return 404.

### Task 6: Hourly-cost editor API (`users.hourly_cost`)
**Blocks:** 10  ·  **Blocked by:** 1
**Files:**
- Create/Modify: `apps/zync-api/src/routes/settings/users.ts` (team list + hourly-cost mutation)
**Steps:**
- [ ] `GET /api/settings/users` → list tenant members with `{ id, name, role, hourlyCost }`. Gate: OWNER only (hourly cost is OWNER-managed and private). Return `hourlyCost` only to OWNER.
- [ ] `PATCH /api/settings/users/:id/hourly-cost` → body `{ hourlyCost: number | null }` validated by Zod (`z.number().nonnegative().nullable()`). OWNER-only. Updates `users.hourly_cost` for the target member scoped to the tenant via `tenant_memberships`.
- [ ] Never expose a user's own `hourly_cost` to that user through any non-owner-readable endpoint (e.g. `GET /api/auth/me`, `GET /api/user/preferences` must not include it).
**Schema / Interfaces:**
```
GET   /api/settings/users
      → { members: Array<{ id, name, role, hourlyCost: number | null }> }
      Requires: OWNER

PATCH /api/settings/users/:id/hourly-cost
      body: { hourlyCost: number | null }   // null clears (excludes from cost calc)
      → { id, hourlyCost }
      Requires: OWNER
```
**Acceptance:**
- [ ] Only OWNER can read or write `hourly_cost`; ADMIN/MEMBER get 403.
- [ ] Setting `null` clears the cost (member excluded from staff-cost sums).
- [ ] `hourly_cost` absent from `GET /api/auth/me` and any member-visible profile API.

### Task 7: Data hooks (`apps/zync-app`)
**Blocks:** 8, 9, 10  ·  **Blocked by:** 5, 6, 2
**Files:**
- Create: `apps/zync-app/src/features/profitability/hooks.ts`
**Steps:**
- [ ] `useProfitabilityReport(params)` — TanStack Query against `GET /api/reports/profitability`.
- [ ] `useProjectProfitability(id)` and `useCustomerProfitability(id)` for breakdowns.
- [ ] `useTeamHourlyCosts()` + `useSetHourlyCost()` mutation against the Task 6 endpoints (invalidate team query on success).
- [ ] `exportProfitabilityCsv(params, scope)` — triggers `format=csv` download via the report endpoint.
**Acceptance:**
- [ ] Hooks typed via `@zync/profitability` types; loading/error states surfaced for `ErrorState`/`Skeleton`.

### Task 8: Report page + overview cards (`/reports/profitability`)
**Blocks:** —  ·  **Blocked by:** 7
**Files:**
- Create: `apps/zync-app/src/features/profitability/ProfitabilityPage.tsx`
- Modify: app router to register `/reports/profitability` (Business+ guarded)
**Steps:**
- [ ] Header: title, period selector (quarter/range dropdown driving `from`/`to`), and "Export CSV" button calling `exportProfitabilityCsv`.
- [ ] Three `StatCard`s: Revenue, Total Cost, Gross Profit (with margin % subtitle on profit).
- [ ] `Tabs`: "By Project" and "By Customer", each a `DataTable` (Name, Revenue, Cost, Profit, Margin) with a margin bar cell.
- [ ] Margin bar color: red `<20%`, amber `20–40%`, green `>40%` (use design tokens, not hardcoded colors).
- [ ] By-Project rows link to `/reports/profitability/project/:id`; By-Customer rows link to the customer breakdown.
- [ ] Empty/error states via `EmptyState`/`ErrorState`; below-Business users see the upgrade modal (`useUpgradeModal`) instead of data.
- [ ] All currency rendered via locale-aware `Intl.NumberFormat` (ILS).
**Acceptance:**
- [ ] Cards show tenant summary; margin computed and color-banded correctly.
- [ ] Tab switch loads project vs customer rollups; rows navigate to breakdowns.
- [ ] Export CSV downloads the current period's data.
- [ ] No hardcoded colors/spacing (design-token lint passes).

### Task 9: Project & customer breakdown views
**Blocks:** —  ·  **Blocked by:** 7
**Files:**
- Create: `apps/zync-app/src/features/profitability/ProjectBreakdown.tsx` (`/reports/profitability/project/:id`)
- Create: `apps/zync-app/src/features/profitability/CustomerBreakdown.tsx` (`/reports/profitability/customer/:id`)
- Modify: app router to register both routes (Business+ guarded)
**Steps:**
- [ ] Project breakdown: header `"{project} — {customer}"`; Revenue section lists each invoice/credit-note line (number, date, amount, status, credit flag) + revenue total; Costs section shows Staff time (`{hours}h × avg ₪/h` → cost), Contractor cost, Expense cost, total cost; footer Gross profit + margin.
- [ ] Customer breakdown: header `"{customer}"`; one row per project (`ProfitabilityRow`) + summary cards; project rows link to the project breakdown.
- [ ] Negative credit-note amounts render with a minus sign and a "credit" badge.
- [ ] Loading `Skeleton`, 404 → `ErrorPage`.
**Acceptance:**
- [ ] Project breakdown numbers reconcile: revenueTotal − totalCost = profit; staff line shows hours and derived avg rate.
- [ ] Customer breakdown lists all of that customer's projects and links back to project breakdowns.

### Task 10: OWNER-only hourly-cost editor (`/settings/users`)
**Blocks:** —  ·  **Blocked by:** 7
**Files:**
- Create/Modify: `apps/zync-app/src/features/settings/TeamHourlyCost.tsx`
- Modify: settings router/section to surface the editor (OWNER-only)
**Steps:**
- [ ] Render team table: Name, Role, "Hourly cost (internal)" editable `Input` (numeric, ILS prefix) bound to `useSetHourlyCost`.
- [ ] Show the privacy note: "Hourly cost is internal only — never shown to customers." Render the editor only when the current user is OWNER; otherwise hide the column entirely.
- [ ] Empty input clears the value (sends `null`).
- [ ] Optimistic update + toast on save; revert + error toast on failure.
**Acceptance:**
- [ ] Visible only to OWNER; ADMIN/MEMBER never see hourly-cost values.
- [ ] Editing persists via `PATCH /api/settings/users/:id/hourly-cost`; clearing sends `null`.

### Task 11: Chart accessibility + RTL configuration
**Blocks:** —  ·  **Blocked by:** 7
**Files:**
- Create: `apps/zync-app/src/features/profitability/ProfitabilityChart.tsx`
- Modify: `ProfitabilityPage.tsx` to embed the chart
**Steps:**
- [ ] Wrap chart in `role="figure"` `aria-labelledby="{chart-id}-title"` with a `<figcaption id="{chart-id}-title">` matching the visual heading.
- [ ] Render a visually-hidden `<table>` sibling with the same data and a "Show data table" toggle button adjacent to the chart.
- [ ] SVG root: `<title>` (e.g. "Profitability by project: {range}") + `<desc>` (trend statement).
- [ ] Tooltip content mirrored into an `aria-live="polite"` region on hover/focus.
- [ ] Clickable segments focusable via Tab, activated via Enter/Space.
- [ ] RTL: when `locale === 'he-IL'` set `<YAxis orientation="right">` and `<Tooltip position={{ x: 'left' }}>`; XAxis stays `orientation="bottom"`; `stackOffset="expand"` needs no RTL change. Use `useDirection`/`useTheme` for locale.
- [ ] Respect `prefers-reduced-motion`: disable Recharts entry animations when set.
**Acceptance:**
- [ ] Screen reader can reach the equivalent data table; figure is labeled.
- [ ] Hebrew locale flips Y-axis to the right and tooltip to the left.
- [ ] Reduced-motion users get no chart animation; segments are keyboard-operable.