# Reports & Analytics

**Date:** 2026-05-30  
**Status:** Draft  
**Depends on:** `foundation-auth-rbac`, `invoices-core`, `expenses-module`, `time-management`, `projects-module`, `customers-module`, `marketing-catalogs-campaigns`, `foundation-monorepo`  
**Referenced by:** `settings-module`, `white-label-api`, `vendors-suppliers`, `accountant-export`

---

## Overview

Two sub-modules: (1) **Tax & Compliance Reports** — the `/reports` navigation hub (spec 103) links to Israeli statutory reports owned by sibling specs (PCN874 VAT, advance-tax estimate, expense deductibility, contractor/supplier withholding); this spec cross-references those below and does **not** duplicate their contracts; (2) **Custom Analytics Dashboards** — configurable widget-based dashboards with per-user saved layouts and date range controls. All reports exportable as Excel (RTL Hebrew) or CSV.

This spec owns the tenant **financial report views** linked from the `/reports` navigation hub (spec 103): the Revenue Ledger (`/reports/revenue`), the **Invoice Report** (`/reports/invoices`), and the **Payment Report** (`/reports/payments`) — all defined below. These are tenant-scoped (not the `SUPER_ADMIN`-only platform reports in spec 59).

---

## Tax & Compliance Reports (`/reports/tax`)

### PCN874 — VAT Return (דוח תקופתי)

PCN874 VAT report is owned by **`docs/specs/2026-06-01-israeli-tax-reports.md`** — see there for data sources, ITA field mapping, period selector, CSV/Excel export, and API. This hub only links to it at **`/reports/vat`** (spec 103 `reports-navigation-hub`, Business+ tier).

---

### Revenue Ledger (פנקס הכנסות)

All issued tax invoices for a period. Required for annual income tax.

**Columns:** Invoice number, Date, Customer name, Customer tax ID, Amount excl. VAT, VAT amount, Total incl. VAT, VAT rate.

**Filters:** date range, customer, status (optional — default is ALL issued tax invoices, i.e. every invoice with an assigned invoice number; income is recognized at tax-invoice issuance per IL income-tax law regardless of payment state).
<!-- Rationale: line-28 purpose is all issued tax invoices; prior TAX_ISSUED/PAID-only default contradicted that. -->

**Sort:** by invoice number (sequential, gap-free — compliant with IL numbering requirement).

**Totals row:** sum of each numeric column.

**Export:** Excel (RTL + Hebrew), CSV.

---

### Invoice Report (`/reports/invoices`)

Tenant-facing report of all invoices in a period — the destination of the **Invoices** tile on the `/reports` hub (spec 103). Unlike the Revenue Ledger (issued tax invoices only, sorted by number for audit), this report covers **every** invoice in the period across all statuses, oriented toward collections and pipeline.

**Data source:**
- `invoices` WHERE `tenant_id = $tenantId` AND `issue_date` BETWEEN `$from` AND `$to`
- Customer name joined from `customers c ON c.id = invoices.customer_id`

```sql
SELECT i.invoice_number, i.issue_date, i.due_date, c.name AS customer_name,
       i.status, i.subtotal, i.vat_amount, i.total, i.currency,
       (i.total - COALESCE(p.paid, 0))        AS balance_due,
       CASE WHEN i.status != 'PAID' AND i.due_date < CURRENT_DATE
            THEN CURRENT_DATE - i.due_date ELSE 0 END AS days_overdue
FROM invoices i
JOIN customers c ON c.id = i.customer_id
LEFT JOIN (
  SELECT invoice_id, SUM(amount) AS paid
  FROM invoice_payments GROUP BY invoice_id
) p ON p.invoice_id = i.id
WHERE i.tenant_id = $tenantId
  AND i.issue_date BETWEEN $from AND $to
ORDER BY i.issue_date DESC, i.invoice_number DESC;
```

**Columns:** Invoice number, Issue date, Due date, Customer name, Status, Amount excl. VAT, VAT amount, Total incl. VAT, Balance due, Days overdue.

**Filters:** date range, customer, status (DRAFT / SENT / APPROVED / TAX_ISSUED / PARTIALLY_PAID / PAID / WRITTEN_OFF / BAD_DEBT / VOID). "Overdue" is a derived view (status not PAID/VOID AND `due_date < today`), offered as a quick-filter chip.

**Sort:** by issue date descending (default); column sort on any header.

**Summary cards (top):** count sent, count overdue, total billed, total outstanding (sum of `balance_due`).

**Totals row:** sum of Amount, VAT, Total, Balance due.

**Drill-down:** click a row → invoice detail (`/invoices/:id`).

**Export:** Excel (RTL + Hebrew), CSV.

---

### Payment Report (`/reports/payments`)

Tenant-facing report of all payments received in a period — the destination of the **Payments** tile on the `/reports` hub (spec 103).

**Data source:**
- `invoice_payments` WHERE `tenant_id = $tenantId` AND `paid_at` BETWEEN `$from` AND `$to`
- Invoice + customer joined for context

```sql
SELECT ip.paid_at, ip.amount, ip.currency, ip.source, ip.reference,
       i.invoice_number, c.name AS customer_name, ip.receipt_id
FROM invoice_payments ip
JOIN invoices i  ON i.id = ip.invoice_id
JOIN customers c ON c.id = i.customer_id
WHERE ip.tenant_id = $tenantId
  AND ip.paid_at BETWEEN $from AND $to
ORDER BY ip.paid_at DESC;
```

`invoice_payments` carries `source` (`manual` / `gateway` / `bank_transfer` / `auto_billing`) — the report's "Method" column maps from `source`. Per-line payment-method detail (cash/cheque/card breakdown, IL-law) lives on `receipt_payment_lines` (spec 179) and is shown only on the receipt document, not aggregated here.

**Columns:** Payment date, Customer name, Invoice number, Amount, Source (manual / gateway / bank transfer / auto-billing), Reference, Receipt issued (✓ when `receipt_id` is set).

**Filters:** date range, customer, source, receipt-issued (yes/no).

**Sort:** by payment date descending (default).

**Summary cards (top):** total received, payment count, count pending receipt (`receipt_id IS NULL`).

**Totals row:** sum of Amount, grouped subtotal by source.

**Drill-down:** click a row → parent invoice detail (`/invoices/:id`); receipt link when `receipt_id` set (`/receipts/:id`, spec 179).

**Export:** Excel (RTL + Hebrew), CSV.

---

### Expense Deductibility Report (דוח הוצאות מוכרות)

Expense deductibility report is owned by **`docs/specs/2026-05-31-expense-reports-ui.md`** (Expense Detail tab) — see there for columns, category grouping, export, and API (`GET /api/expenses/reports/expense/xlsx`). This hub only links to it at **`/expenses/reports`** (spec 103 `reports-navigation-hub`).

---

### Contractor Withholding Summary (אישור ניכוי מס במקור)

Contractor withholding (Form 856 / Mas 856) is owned by **`docs/specs/2026-05-30-contractor-payouts.md`** — see there for per-contractor totals, Form 857 certificates, API, and export. Vendor/supplier rows on the combined report are extended in **`docs/specs/2026-06-01-vendors-suppliers.md`**. This hub only links to it at **`/reports/withholding`** (nav label "Mas 856", spec 103 `reports-navigation-hub`, Business+ tier).

---

### Income Tax Prepayment Estimate (הכנסה חייבת — אומדן)

Advance tax estimate (מקדמות מס הכנסה) is owned by **`docs/specs/2026-06-01-israeli-tax-reports.md`** § Advance Tax Estimate — see there for calculation, tax-bracket sourcing, disclaimer, and API. This hub only links to it at **`/reports/advance-tax`** (spec 103 `reports-navigation-hub`, Business+ tier).

---

## Custom Analytics Dashboards (`/reports/analytics`)

### Dashboard Layout

```
┌──────────────────────────────────────────────────────────────────┐
│  [Dashboard name ▾]  Period: [Last 30d ▾]  [+ Add widget]  [⋮]  │
├──────────────────────────────────────────────────────────────────┤
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────────────┐   │
│  │  Revenue KPI │  │  Open Inv.   │  │  Time by Project     │   │
│  │  ₪48,200     │  │  ₪12,400     │  │  [bar chart]         │   │
│  └──────────────┘  └──────────────┘  └──────────────────────┘   │
│  ┌──────────────────────────────────────┐  ┌──────────────────┐ │
│  │  Invoice Status Pipeline             │  │  Leads Funnel    │ │
│  │  [stacked bar]                       │  │  [funnel chart]  │ │
│  └──────────────────────────────────────┘  └──────────────────┘ │
└──────────────────────────────────────────────────────────────────┘
```

**Date range control** (top): Last 7d / 30d / 90d / This month / This quarter / Custom range. Applied to all widgets on the dashboard.

**Multiple dashboards:** tenant can create N named dashboards. Default "Overview" created on tenant setup.

### Widget Library

| Widget | Data source | Chart type |
|--------|-------------|-----------|
| Revenue KPI | `invoices` total paid | Metric card + sparkline |
| Invoice status pipeline | `invoices` by status | Horizontal stacked bar |
| Open invoices aging | `invoices` by days outstanding | Bar (0-30, 30-60, 60-90, 90+) |
| Time by project | `time_entries` | Bar or pie |
| Time by team member | `time_entries` | Bar |
| Billable vs non-billable | `time_entries` | Donut |
| Customer revenue | `invoices` by customer | Bar + table |
| Expense breakdown | `expenses` by category | Donut |
| Leads funnel | AE events | Funnel chart (4 steps) |
| Lead pipeline by stage | `leads` by stage | Kanban-style count |
| Ticket resolution time | `tickets` | Histogram (avg days open) |
| Ticket volume by category | `tickets` | Bar |
| KB article views | Future — deferred | — |

### Widget Configuration

Click widget → settings drawer:
- Title override
- Date range override (if widget should use different period)
- Filter (e.g. specific project, customer, team member)
- Chart type toggle (where multiple types available)

### Saved Layouts

Each dashboard saves:
- Widget list (type, position, size, config)
- Last-used date range

Stored in `dashboard_layouts` table per user per tenant. Users can have independent layouts from colleagues.

---

## Data Model

```sql
dashboards (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  user_id UUID NOT NULL,                -- dashboards are per-user
  name TEXT NOT NULL,
  is_default BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
)

dashboard_widgets (
  id UUID PRIMARY KEY,
  dashboard_id UUID NOT NULL,
  tenant_id UUID NOT NULL,
  widget_type TEXT NOT NULL,            -- matches widget library keys
  position_x INTEGER NOT NULL,          -- grid column (0-based)
  position_y INTEGER NOT NULL,          -- grid row
  width INTEGER NOT NULL DEFAULT 1,     -- grid units
  height INTEGER NOT NULL DEFAULT 1,
  config JSONB NOT NULL DEFAULT '{}',   -- title override, filters, date override
  created_at TIMESTAMPTZ DEFAULT now()
)
```

Dashboard layout uses a fixed 12-column grid. Widgets snap to grid. Positions stored as integers (no fractional index needed — integer grid suffices).

---

## Report Infrastructure

### Excel Generation

All Excel exports generated server-side using the `xlsx` npm package (runs in Workers — no native binary).

RTL support: set `Views: [{ RTL: true }]` on each sheet. Hebrew column headers. RTL text direction for all string cells.

Date format: `DD/MM/YYYY` (Israeli standard). Currency: `₪#,##0.00`.

Reports generated on-demand (not pre-computed). For large date ranges (>1 year), generation may take 2-5s — acceptable; no async queue needed.

### Chart Rendering

All charts rendered client-side using Recharts (React). API returns raw aggregated data. Client renders charts. No server-side chart image generation.

#### Chart Accessibility (WCAG 2.1 SC 1.1.1, SC 1.3.1)

Every chart must be accessible to screen readers. Two-layer approach:

**1. ARIA label on the chart container:**

```tsx
<ResponsiveContainer>
  <BarChart
    data={data}
    role="img"
    aria-label="Revenue by month: January ₪12,400, February ₪15,200, ..."  // generated from data
  >
    {/* ... */}
  </BarChart>
</ResponsiveContainer>
```

For dynamic charts, generate the `aria-label` from the top-5 data points with a trailing "and N more values". For sparklines and KPI-card charts, a single sentence suffices ("Revenue trend: up 18% over last 30 days").

**2. Accessible data table fallback (visually hidden):**

Each chart widget includes a `<details>` summary beneath the chart:

```tsx
<details className="sr-only-details">
  <summary>View data as table</summary>
  <table>
    <caption>{chartTitle}</caption>
    <thead><tr>{columnHeaders}</tr></thead>
    <tbody>{rows}</tbody>
  </table>
</details>
```

`sr-only-details` collapses to `[▶ View data as table]` for sighted users (keyboard-reachable) and is fully expanded for screen readers via CSS.

**Color-only prohibition:** Chart series must never be distinguished by color alone. Use distinct fill patterns (hatching via SVG `<pattern>`) or labels on bars/slices in addition to legend color. This satisfies WCAG 1.4.1 for color-blind users.

#### RTL Chart Rendering

The `Views: [{ RTL: true }]` configuration above applies to **Excel exports only**. Recharts chart rendering in RTL mode requires separate axis configuration:

```tsx
function AnalyticsChart({ 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 — direction-agnostic
// Line chart dots: coordinate-based — no RTL adjustment needed for dots
// Document the per-chart-type RTL behavior inline in each widget definition
```

### Caching

Report queries are read-heavy and read historical data. Cache strategy: `cache.default` with 5-minute TTL.

Cache key: `{tenantId}:{reportType}:{period}:v{financials_version}`. Version invalidation: each invoice/expense write increments `financials_version` counter stored in KV (`financials_version:{tenantId}`). New version in cache key = old entries are naturally bypassed (no cache-tag API needed — Workers `cache.default` is URL-keyed only; Cache-Tag purge is Enterprise CDN only).

---

## Permissions

| Action | Required permission |
|--------|-------------------|
| View tax reports | `reports:read` |
| Export reports | `reports:export` |
<!-- Rationale: exports gated separately from view, consistent with all sibling tax-report exports. -->
| View analytics | `reports:read` |
| Create/edit dashboards | `reports:write` |

---

## API Endpoints

```
-- Tax reports (owned by this spec)
GET    /api/reports/revenue          → revenue ledger data (?from=&to=)
GET    /api/reports/revenue/xlsx     → revenue ledger Excel
GET    /api/reports/invoices         → invoice report data (?from=&to=&customer=&status=)
GET    /api/reports/invoices/xlsx    → invoice report Excel
GET    /api/reports/payments         → payment report data (?from=&to=&customer=&source=)
GET    /api/reports/payments/xlsx    → payment report Excel

-- Sibling-owned tax/compliance endpoints (see cross-references above; not defined here):
--   PCN874 VAT          → docs/specs/2026-06-01-israeli-tax-reports.md
--   Advance tax         → docs/specs/2026-06-01-israeli-tax-reports.md
--   Expense deductibility → docs/specs/2026-05-31-expense-reports-ui.md
--   Withholding (856)   → docs/specs/2026-05-30-contractor-payouts.md

-- Analytics
GET    /api/dashboards               → list user's dashboards
POST   /api/dashboards               → create dashboard
GET    /api/dashboards/:id           → dashboard + widgets
PATCH  /api/dashboards/:id           → rename / set default
DELETE /api/dashboards/:id           → delete

POST   /api/dashboards/:id/widgets   → add widget
PATCH  /api/dashboards/:id/widgets/:wid → update config / position
DELETE /api/dashboards/:id/widgets/:wid → remove widget

GET    /api/analytics/query          → generic widget data query (?widget=revenue_kpi&from=&to=&filters=)
```

---

## Foundation Deltas

**New secret:** `CF_ANALYTICS_READ_TOKEN` — Cloudflare Analytics Engine SQL HTTP API token (`Account Analytics: Read` permission). AE *writes* use the `ANALYTICS_ENGINE` binding; AE *reads* (funnel dashboards, custom analytics widgets) use the AE SQL HTTP API endpoint with a separate API token. Not in wrangler OAuth scope — must be created manually in CF dashboard and stored as Worker secret.

**New tables:** `dashboards`, `dashboard_widgets` — per-user dashboard config.

---

## Architecture Decisions

| Decision | Choice | Reason |
|----------|--------|--------|
| Tax reports on-demand | Not pre-computed | Report data is live; period-scoped queries complete in <2s for typical tenant volume; pre-computation adds staleness risk |
| Excel via xlsx npm | Not CF Browser Rendering | xlsx runs in Workers (WASM-free, pure JS); no extra bindings; supports RTL + Hebrew natively |
| Charts client-side | Not server-side image | Recharts renders SVG/Canvas; no server dependency; interactive (hover, zoom); no CF Browser Rendering needed |
| Dashboard per user | Not per tenant | Each accountant/manager wants their own layout; shared view = add "shared dashboards" later |
| Revenue report sorted by invoice number | Not by date | IL law requires sequential gap-free numbers; sort by number is the natural audit view |
| AE read via HTTP API | Not binding | AE `ANALYTICS_ENGINE` binding is write-only; SQL reads require the separate AE SQL HTTP endpoint with `CF_ANALYTICS_READ_TOKEN`; spec 23 (funnel) and spec 24 (dashboards) both use this read path |
| Cache invalidation via version key | Not cache-tag | Workers `cache.default` is keyed by URL only; Cache-Tag purge is Cloudflare Enterprise CDN only — not available on Workers cache API. Version counter in KV, bumped on invoice/expense write, included in cache key. Old entries naturally bypassed. |