# Profitability Reports **Date:** 2026-05-31 **Status:** Draft **Spec:** 92 **Tier:** Business+ **Depends on:** `invoices-core`, `time-management`, `expenses-module`, `contractor-payouts`, `projects-module`, `foundation-auth-rbac` **Referenced by:** `admin-reports-analytics` --- ## Overview Cross-module profitability analysis: revenue (invoiced) minus costs (time × hourly rate + expenses + contractor payouts) per project and per customer. Gives tenants visibility into which clients and projects are actually profitable. --- ## Data Sources | Revenue source | Table | Column | |----------------|-------|--------| | Invoice revenue | `invoices` | `total` where `status IN ('TAX_ISSUED', 'PARTIALLY_PAID', 'PAID')` and `source != 'credit_note'` | | Credit note offsets | `invoices` | `total` where `source = 'credit_note'` (already negative) | | Cost source | Table | Column | |-------------|-------|--------| | Staff time cost | `time_entries` | `duration_min / 60 × users.hourly_cost` | | Contractor cost | `payout_bill_lines` | `line_total` (from approved payout bills) | | Project expenses | `expenses` | `invoice_total` where `project_id IS NOT NULL AND status = 'COMPLETED'` | **Note:** `users.hourly_cost` is an internal cost rate (not billable rate). Added as schema delta below. --- ## Data Model ```sql -- Schema delta on users: ALTER TABLE users ADD COLUMN hourly_cost NUMERIC(8,2); -- Internal cost per hour for profitability calculations. NULL = excluded from cost calculations. -- Set by OWNER/ADMIN; not exposed to the user themselves. ``` --- ## Page: `/reports/profitability` ### Overview Cards ``` ┌──────────────────────────────────────────────────────────────┐ │ Profitability [Q2 2026 ▾] [Export CSV] │ │ │ │ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │ │ │ Revenue │ │ Total Cost │ │ Gross Profit │ │ │ │ ₪182,500 │ │ ₪94,300 │ │ ₪88,200 │ │ │ │ │ │ │ │ 48.3% margin │ │ │ └──────────────┘ └──────────────┘ └──────────────┘ │ └──────────────────────────────────────────────────────────────┘ ``` --- ### By Project Tab ``` ┌──────────────────────────────────────────────────────────────┐ │ Project Revenue Cost Profit Margin │ │ ────────────────────────────────────────────────────────── │ │ Website redesign ₪45,000 ₪18,200 ₪26,800 59.6% ██ │ │ ERP integration ₪32,000 ₪28,500 ₪3,500 10.9% █ │ │ Brand refresh ₪18,000 ₪8,100 ₪9,900 55.0% ██ │ │ Marketing retainer ₪12,500 ₪6,200 ₪6,300 50.4% ██ │ │ │ │ Click row → project profitability breakdown │ └──────────────────────────────────────────────────────────────┘ ``` Margin bar: colored red (<20%), amber (20–40%), green (>40%). --- ### By Customer Tab ``` ┌──────────────────────────────────────────────────────────────┐ │ Customer Revenue Cost Profit Margin │ │ ──────────────────────────────────────────────────────── │ │ Acme Corp ₪77,000 ₪46,700 ₪30,300 39.4% ▒▒ │ │ Beta Ltd ₪54,500 ₪22,100 ₪32,400 59.4% ████ │ │ Gama Inc ₪51,000 ₪25,500 ₪25,500 50.0% ███ │ │ │ │ Click row → all projects for this customer │ └──────────────────────────────────────────────────────────────┘ ``` --- ### Project Profitability Breakdown `/reports/profitability/project/:id`: ``` ┌──────────────────────────────────────────────────────────────┐ │ Website redesign — Acme Corp │ │ │ │ ── Revenue ────────────────────────────────────────────── │ │ INV-0042 May 2026 ₪18,000 PAID │ │ INV-0051 Jun 2026 ₪27,000 TAX_ISSUED │ │ CN-00001 May 2026 −₪3,500 TAX_ISSUED (credit) │ │ Total revenue: ₪41,500 │ │ │ │ ── Costs ───────────────────────────────────────────────── │ │ Staff time (112h × avg ₪95/h): ₪10,640 │ │ Contractor — Dana: ₪4,800 │ │ Expenses (office, software): ₪2,760 │ │ Total cost: ₪18,200 │ │ │ │ Gross profit: ₪23,300 Margin: 56.1% │ └──────────────────────────────────────────────────────────────┘ ``` --- ## Chart Accessibility and RTL ### Accessibility (adopt spec 24 pattern) 1. Wrapper: `role="figure"` `aria-labelledby="{chart-id}-title"` 2. `
` text matches the visual chart heading 3. Visually-hidden `` sibling containing the same data in tabular form; "Show data table" toggle button adjacent to chart 4. SVG root: `` (e.g., `"Profitability by project: Jan–Dec 2025"`) + `<desc>` (trend statement) 5. Tooltip: `aria-live="polite"` region echoing tooltip content on hover/focus 6. Interactive segments (clickable): focusable via Tab, activated via Enter/Space ### RTL Chart Configuration ```tsx function ProfitabilityChart({ locale }: { locale: string }) { const isRtl = locale === 'he-IL' return ( <ResponsiveContainer> <BarChart> <YAxis orientation={isRtl ? 'right' : 'left'} /> <XAxis orientation="bottom" /> <Tooltip position={{ x: isRtl ? 'left' : 'right' }} /> </BarChart> </ResponsiveContainer> ) } // stackOffset="expand" for stacked bars is direction-agnostic — no RTL change needed ``` --- ## Computation Pattern Profitability is computed on-read (not materialized). Query structure for a project: ```sql -- Revenue SELECT COALESCE(SUM(i.total), 0) AS revenue FROM invoices i WHERE i.project_id = :projectId AND i.status IN ('TAX_ISSUED', 'PARTIALLY_PAID', 'PAID') AND i.tenant_id = :tenantId; -- includes credit notes (negative totals) via SUM -- Staff cost SELECT COALESCE(SUM(te.duration_min / 60.0 * u.hourly_cost), 0) AS staff_cost FROM time_entries te JOIN users u ON u.id = te.user_id WHERE te.project_id = :projectId AND te.tenant_id = :tenantId AND u.hourly_cost IS NOT NULL; -- Contractor cost SELECT COALESCE(SUM(pbl.line_total), 0) AS contractor_cost FROM payout_bill_lines pbl JOIN payout_bills pb ON pb.id = pbl.payout_bill_id JOIN time_entries te ON te.id = pbl.time_entry_id WHERE te.project_id = :projectId AND pb.tenant_id = :tenantId; -- Expenses SELECT COALESCE(SUM(e.invoice_total), 0) AS expense_cost FROM expenses e WHERE e.project_id = :projectId AND e.tenant_id = :tenantId AND e.status = 'COMPLETED'; ``` --- ## Hourly Cost Configuration Staff costs require `users.hourly_cost`. Configured in `/settings/users` (OWNER only): ``` ┌──────────────────────────────────────────────────────────────┐ │ Team │ │ │ │ Name Role Hourly cost (internal) │ │ Alex Katz Owner ₪ [200___] │ │ Dana Levi Admin ₪ [150___] │ │ Ronen Bar Member ₪ [120___] │ │ │ │ ⓘ Hourly cost is internal only — never shown to customers. │ └──────────────────────────────────────────────────────────────┘ ``` --- ## API ``` GET /api/reports/profitability → tenant-level summary + by-project + by-customer lists query: from?, to?, currency? Requires: OWNER, ADMIN (Business+) GET /api/reports/profitability/projects/:id → project-level breakdown (revenue, staff/contractor/expense costs) Requires: OWNER, ADMIN (Business+) GET /api/reports/profitability/customers/:id → customer-level breakdown across all projects Requires: OWNER, ADMIN (Business+) ``` --- ## Architecture Decisions | Decision | Choice | Reason | |----------|--------|--------| | On-read computation | Not materialized view | Report is on-demand; materialized view needs refresh triggers on 5 tables; on-read is simpler and fast enough for typical tenant scale | | `hourly_cost` on users | Not time_entries | Cost rate is per-person, not per-entry; storing on users allows retroactive rate changes without backfilling entries | | Credit notes included via SUM | Not filtered out | CN total is already negative; SUM naturally deducts them from revenue — no special handling needed | | Business+ gate | Not all tiers | Profitability requires `hourly_cost` setup and cross-module data; Freelancer sole proprietors don't need this |