# Revenue Forecasting — Implementation Plan

**Spec:** docs/specs/2026-05-31-revenue-forecasting.md  ·  **Slug:** revenue-forecasting  ·  **Wave:** 8
**Depends on:** foundation-auth-rbac, invoices-core, marketing-leads-pipeline, projects-module, recurring-invoices

## Goal
Deliver a Business+ revenue forecasting report at `/reports/revenue-forecast` that aggregates and visualizes projected monthly revenue for the next 3/6/12/24 months across three confidence-tiered sources: **Committed** (sent/issued invoices, with a configurable recovery-rate discount on past-due amounts), **Scheduled** (recurring-invoice templates expanded server-side over the forecast window), and **Projected** (active leads, probability-weighted by pipeline stage). The feature ships one read-only API endpoint, a stacked-bar Recharts visualization with a full WCAG-AA accessible-chart pattern and RTL support, a per-month data table with CSV export, and a single `tenant_settings` schema delta storing the configurable stage→probability map.

## Architecture
The report is **read-only and server-computed**. A new Hono route `GET /api/reports/revenue-forecast` lives in the API worker and returns a fully pre-computed `{ summary, monthly[] }` payload — the client never expands RRULEs or computes weights. The endpoint is gated by `requireTier('business')` and `requirePermission('reports:read')` (cross-spec permission convention; reference, do not create) and scoped via `tenantQuery`.

Aggregation reads three upstream sources, all by `tenant_id`:
- **Committed** — `invoices` (invoices-core): rows with `status IN ('SENT','TAX_ISSUED','PARTIALLY_PAID')`, bucketed by month of `due_date` (fallback `issue_date`). Past-due amounts (`due_date < today`) are discounted by the configurable recovery rate (default 0.80). For `PARTIALLY_PAID` the spec-80 column `invoices.amount_paid` is subtracted from `total` to forecast only the outstanding remainder.
- **Scheduled** — `recurring_invoice_templates` (recurring-invoices): `status = 'active'`, expanded forward from `next_generation_date` using the same `rrule.js`/frequency logic as recurring-invoices (`computeNextDate`), per occurrence inside the window. Per-occurrence amount is computed server-side from the template's `line_items JSONB` and `vat_rate`.
- **Projected** — `leads` (marketing-leads-pipeline): `stage NOT IN ('LOST','WON')`, `archived_at IS NULL`, `estimated_value IS NOT NULL`. Each lead contributes `estimated_value * stage_probability` (probability from the tenant's `lead_stage_probabilities` map), spread evenly across the next 3 forecast months (see Task 3 for the resolved bucketing rule).

The tenant's configurable stage probabilities are stored in the existing **`tenant_settings`** table (spec-17 / settings-module owned, assumed-existing upstream — this plan ALTERs it, never CREATEs it) via a new `lead_stage_probabilities JSONB` column.

The UI is a Vite+React route in the app, consuming the endpoint via react-query, rendering a Recharts stacked bar chart (committed/scheduled/projected) with the spec-24 accessible-chart pattern, a period selector, a per-month data table, and client-side CSV export.

**Consumed upstream tables:** `invoices`, `recurring_invoice_templates`, `leads`, `tenant_settings`.
**Consumed upstream exports:** `requireTier`, `requirePermission`, `tenantQuery`, `authMiddleware`, `createDb` / `DB`, `Env`, `useDirection`, `useTheme`, `Card`, `StatCard`, `Select`, `Button`, `Table`, `Spinner`, `EmptyState`, `ErrorState`, `Skeleton`.

## Tech Stack
- **API:** `apps/zync-api` (Hono on Cloudflare Workers), Drizzle ORM over Neon Postgres via Hyperdrive binding `DB`. Zod request validation. `rrule`/date math shared with recurring-invoices.
- **DB:** Neon Postgres (UUID PKs, TIMESTAMPTZ, JSONB, CHECK enums). One `ALTER TABLE` migration in `packages/db`.
- **App:** `apps/zync-app` (Vite + React), `recharts` for the stacked bar chart, `@zync/ui` primitives, `@tanstack/react-query` for fetching, `@zync/i18n` translations for labels and currency formatting.
- **Cross-cutting:** WCAG-AA accessible-chart pattern (spec 24), RTL chart axis mirroring for `he-IL`, `prefers-reduced-motion` honored by disabling Recharts animation, CSP-safe (no inline chart scripts).

## Wave Plan
| Sub-wave | Tasks | Files touched | Parallelizable? |
|----------|-------|---------------|-----------------|
| 8a — schema | Task 1 | `packages/db/migrations`, `packages/db/src/schema` | No (blocks all) |
| 8b — server compute | Tasks 2, 3, 4 | `apps/zync-api/src/lib/revenue-forecast/*` | Tasks 2–4 parallel after Task 1 |
| 8c — API route | Task 5 | `apps/zync-api/src/routes/reports`, `packages/types` | After Tasks 2–4 |
| 8d — UI | Tasks 6, 7, 8, 9 | `apps/zync-app/src/features/revenue-forecast/*` | Tasks 7–9 parallel after Task 6 |

## Tasks

### Task 1: Schema delta — `tenant_settings.lead_stage_probabilities`
**Blocks:** 2, 3, 4, 5  ·  **Blocked by:** —
**Files:**
- Create: `packages/db/migrations/0XXX_revenue_forecast_lead_stage_probabilities.sql`
- Modify: `packages/db/src/schema/tenant-settings.ts` (add Drizzle column to the existing `tenantSettings` table definition)
**Steps:**
- [ ] Add the `lead_stage_probabilities` JSONB column to the existing `tenant_settings` table (ALTER — `tenant_settings` is owned upstream by settings-module / spec 17; do NOT create the table).
- [ ] Mirror the column in the Drizzle schema as `leadStageProbabilities: jsonb('lead_stage_probabilities').default({ NEW: 5, CONTACTED: 15, QUALIFIED: 30, PROPOSAL: 60 })`.
- [ ] Ensure the default literal exactly matches the spec stage→percent map.
**Schema / Interfaces:**
```sql
-- ALTER existing tenant_settings (NOT a new table)
ALTER TABLE tenant_settings ADD COLUMN lead_stage_probabilities JSONB DEFAULT
  '{"NEW": 5, "CONTACTED": 15, "QUALIFIED": 30, "PROPOSAL": 60}';
-- Stage probability % used for lead weighted revenue forecast.
```
```ts
// packages/types — exported config shape
export interface LeadStageProbabilities {
  NEW: number; CONTACTED: number; QUALIFIED: number; PROPOSAL: number;
}
export const DEFAULT_LEAD_STAGE_PROBABILITIES: LeadStageProbabilities =
  { NEW: 5, CONTACTED: 15, QUALIFIED: 30, PROPOSAL: 60 };
```
**Acceptance:**
- [ ] Migration applies cleanly on Neon; `tenant_settings` gains a JSONB column with the exact default JSON above.
- [ ] No new table is created by this plan (`tables: []`).
- [ ] Drizzle `tenantSettings` type exposes `leadStageProbabilities`.

### Task 2: Committed-revenue aggregator
**Blocks:** 5  ·  **Blocked by:** 1
**Files:**
- Create: `apps/zync-api/src/lib/revenue-forecast/committed.ts`
**Steps:**
- [ ] Implement `computeCommitted(db, tenantId, window)` returning `Map<YYYY-MM, number>` over the forecast window.
- [ ] Query `invoices` for the tenant where `status IN ('SENT','TAX_ISSUED','PARTIALLY_PAID')`.
- [ ] Determine the forecastable amount per invoice: for `PARTIALLY_PAID` use `total - COALESCE(amount_paid, 0)`; otherwise `total`.
- [ ] Bucket each invoice into the month of `COALESCE(due_date, issue_date)`. Drop invoices whose bucket month falls outside the forecast window.
- [ ] Apply the configurable recovery-rate discount to **past-due** amounts only: if `due_date < CURRENT_DATE`, multiply the forecast amount by `recoveryRate` (default `0.80`, passed in from the route config).
- [ ] Sum per month; round to 2 decimals (ILS minor units not split — values are NUMERIC(12,2) sourced).
**Schema / Interfaces:**
```ts
interface ForecastWindow { startMonth: string; months: number; today: string; } // YYYY-MM, count, YYYY-MM-DD
export async function computeCommitted(
  db: DB, tenantId: string, window: ForecastWindow, recoveryRate: number
): Promise<Map<string, number>>; // key: "YYYY-MM"
```
Reads upstream columns: `invoices.status`, `invoices.total`, `invoices.amount_paid`, `invoices.due_date`, `invoices.issue_date`, `invoices.tenant_id`.
**Acceptance:**
- [ ] Only `SENT | TAX_ISSUED | PARTIALLY_PAID` invoices counted.
- [ ] Past-due invoices contribute exactly `amount * recoveryRate`; not-yet-due contribute full amount.
- [ ] `PARTIALLY_PAID` invoices contribute only the outstanding remainder (`total - amount_paid`).
- [ ] Buckets outside the window are excluded.

### Task 3: Projected (lead-weighted) aggregator
**Blocks:** 5  ·  **Blocked by:** 1
**Files:**
- Create: `apps/zync-api/src/lib/revenue-forecast/projected.ts`
**Steps:**
- [ ] Implement `computeProjected(db, tenantId, window, stageProbabilities)` returning `Map<YYYY-MM, number>`.
- [ ] Query `leads` for the tenant where `stage NOT IN ('LOST','WON')`, `archived_at IS NULL`, `estimated_value IS NOT NULL`.
- [ ] For each lead compute weighted value `estimated_value * (stageProbabilities[stage] / 100)`. If a stage has no probability entry, treat probability as 0 (lead contributes nothing) — only `NEW|CONTACTED|QUALIFIED|PROPOSAL` map to non-zero defaults.
- [ ] **Bucketing rule (resolved):** spread each lead's weighted value **evenly across the next 3 forecast months** starting at the window's first month (`startMonth`, `startMonth+1`, `startMonth+2`). Each of those 3 months receives `weighted / 3`. This is the spec's "no explicit close date → spread evenly over next 3 months for their stage" rule, applied uniformly. Do NOT bucket by `leads.created_at` (a past date cannot map to a future forecast month). The `reengagement_at` proxy from the spec is intentionally omitted: that column does not exist on `leads` upstream at this wave; defer it until it is added.
- [ ] If the forecast window is shorter than 3 months (i.e. `months === 3` is fine; never <3 per selector), no special-casing needed — selector minimum is 3.
- [ ] Sum per month; round to 2 decimals.
**Schema / Interfaces:**
```ts
export async function computeProjected(
  db: DB, tenantId: string, window: ForecastWindow,
  stageProbabilities: LeadStageProbabilities
): Promise<Map<string, number>>;
```
Reads upstream columns: `leads.stage`, `leads.estimated_value`, `leads.archived_at`, `leads.tenant_id`.
**Acceptance:**
- [ ] Only active (`stage NOT IN ('LOST','WON')`, not archived) leads with non-null `estimated_value` counted.
- [ ] Weighted value = `estimated_value * probability%/100`, using tenant overrides when present else defaults.
- [ ] Each lead's weighted value is split evenly across exactly the first 3 forecast months.
- [ ] No reference to a non-existent `reengagement_at` column anywhere.

### Task 4: Scheduled (recurring-invoice) aggregator with server-side RRULE expansion
**Blocks:** 5  ·  **Blocked by:** 1
**Files:**
- Create: `apps/zync-api/src/lib/revenue-forecast/scheduled.ts`
**Steps:**
- [ ] Implement `computeScheduled(db, tenantId, window)` returning `Map<YYYY-MM, number>`.
- [ ] Query `recurring_invoice_templates` where `status = 'active'` for the tenant.
- [ ] For each template, compute the per-occurrence gross amount server-side from `line_items JSONB` and `vat_rate`: `subtotal = Σ(quantity * unit_price * (1 - discount_pct/100))` over line items, then `total = subtotal * (1 + vat_rate)` (apply VAT only to taxable lines; non-taxable lines contribute their `line_total` without VAT).
- [ ] Expand occurrences forward from `next_generation_date` across the forecast window using the same frequency advancement as recurring-invoices `computeNextDate` (`weekly` / `monthly` / `quarterly` / `yearly`, honoring `frequency_day`). Stop expansion at the earlier of: window end, or `end_date` when set.
- [ ] Bucket each occurrence's `total` into the month of its generation date.
- [ ] Sum per month; round to 2 decimals.
**Schema / Interfaces:**
```ts
export async function computeScheduled(
  db: DB, tenantId: string, window: ForecastWindow
): Promise<Map<string, number>>;
// Occurrence expansion mirrors recurring-invoices.computeNextDate:
//   weekly    → +7 days
//   monthly   → +1 month on frequency_day (1–28)
//   quarterly → +3 months on frequency_day
//   yearly    → +1 year on frequency_day
```
Reads upstream columns: `recurring_invoice_templates.status`, `.next_generation_date`, `.frequency`, `.frequency_day`, `.end_date`, `.line_items`, `.vat_rate`, `.tenant_id`.
**Acceptance:**
- [ ] Only `status = 'active'` templates counted.
- [ ] RRULE/frequency expansion happens server-side; occurrences beyond `end_date` or the window are excluded.
- [ ] Per-occurrence amount computed from `line_items` + `vat_rate`, not hard-coded.
- [ ] A monthly template with `next_generation_date` inside a 12-month window contributes 12 occurrences (one per month).

### Task 5: API route `GET /api/reports/revenue-forecast`
**Blocks:** 6  ·  **Blocked by:** 2, 3, 4
**Files:**
- Create: `apps/zync-api/src/routes/reports/revenue-forecast.ts`
- Modify: `apps/zync-api/src/routes/reports/index.ts` (register route)
- Modify: `packages/types/src/reports.ts` (export `RevenueForecastResponse`)
**Steps:**
- [ ] Define the Hono handler under `authMiddleware`, `requireTier('business')`, and `requirePermission('reports:read')`.
- [ ] Validate query with Zod: `months` ∈ `{3,6,12,24}`, default `12`.
- [ ] Build the `ForecastWindow`: `startMonth` = current month (`YYYY-MM`), `months` from query, `today` = current date.
- [ ] Load `tenant_settings.lead_stage_probabilities` for the tenant (fall back to `DEFAULT_LEAD_STAGE_PROBABILITIES` if NULL); load the recovery rate (default `0.80` — sourced from the same tenant config map or constant if no column exists yet).
- [ ] Call `computeCommitted`, `computeScheduled`, `computeProjected` (independent; run concurrently with `Promise.all`).
- [ ] Merge the three maps into an ordered `monthly[]` array, one entry per window month (zero-filled), each `{ month, committed, scheduled, projected, total }` with `total = committed + scheduled + projected`.
- [ ] Compute `summary` as column sums across all months.
- [ ] Use `tenantQuery` for all reads so tenant isolation is enforced; never raw-drizzle from the route body (delegate to the lib aggregators).
**Schema / Interfaces:**
```ts
// packages/types
export interface RevenueForecastMonth {
  month: string;      // "YYYY-MM"
  committed: number;
  scheduled: number;
  projected: number;
  total: number;
}
export interface RevenueForecastSummary {
  committed: number; scheduled: number; projected: number; total: number;
}
export interface RevenueForecastResponse {
  summary: RevenueForecastSummary;
  monthly: RevenueForecastMonth[];
}
// GET /api/reports/revenue-forecast?months=3|6|12|24  (default 12)
//   guards: authMiddleware + requireTier('business') + requirePermission('reports:read')
//   200 → RevenueForecastResponse
```
**Acceptance:**
- [ ] Request without Business+ tier → 403 (via `requireTier`); without `reports:read` → 403 (via `requirePermission`).
- [ ] `months` outside `{3,6,12,24}` → 400.
- [ ] Response contains exactly `months` monthly entries, contiguous from current month, with correct `total` per row and `summary` equal to column sums.
- [ ] Cross-tenant data never appears (verified via `tenantQuery`).

### Task 6: App route, data fetching, and period selector
**Blocks:** 7, 8, 9  ·  **Blocked by:** 5
**Files:**
- Create: `apps/zync-app/src/features/revenue-forecast/RevenueForecastPage.tsx`
- Create: `apps/zync-app/src/features/revenue-forecast/useRevenueForecast.ts`
- Modify: `apps/zync-app/src/router.tsx` (register `/reports/revenue-forecast`, Business+ guard)
**Steps:**
- [ ] Register the route `/reports/revenue-forecast` behind the app's Business+ tier guard (mirror existing report routes; uses `useTierGate`).
- [ ] Implement `useRevenueForecast(months)` react-query hook calling `GET /api/reports/revenue-forecast?months=`.
- [ ] Render page scaffold: heading, period `Select` (`3 months`, `6 months`, `12 months` default, `24 months`), and slots for summary bar, chart, and data table.
- [ ] Handle loading (`Skeleton`/`Spinner`), error (`ErrorState`), and empty (`EmptyState` when all totals are 0) states.
- [ ] Sync the selected period to the URL query string.
**Schema / Interfaces:**
```ts
export function useRevenueForecast(months: 3 | 6 | 12 | 24):
  UseQueryResult<RevenueForecastResponse>;
```
**Acceptance:**
- [ ] Navigating to `/reports/revenue-forecast` on a Business+ tenant renders the page; lower tiers are gated.
- [ ] Changing the period selector refetches with the new `months` and updates URL.
- [ ] Loading/error/empty states render via shared `@zync/ui` primitives.

### Task 7: Summary bar
**Blocks:** —  ·  **Blocked by:** 6
**Files:**
- Create: `apps/zync-app/src/features/revenue-forecast/SummaryBar.tsx`
**Steps:**
- [ ] Render four `StatCard`s: **Committed** (label "sent/issued"), **Scheduled** ("recurring"), **Projected** ("leads"), **Total**, from `response.summary`.
- [ ] Format values as localized currency (ILS, `₪`), respecting `he-IL` / `en-US` via the i18n formatter.
- [ ] Lay out horizontally on desktop, stacking on mobile; logical-property spacing (no hardcoded RTL/LTR margins).
**Acceptance:**
- [ ] Four totals render and equal the API `summary` values.
- [ ] Currency formatting is locale-correct and RTL-safe.

### Task 8: Accessible RTL stacked-bar chart (spec-24 pattern)
**Blocks:** —  ·  **Blocked by:** 6
**Files:**
- Create: `apps/zync-app/src/features/revenue-forecast/ForecastChart.tsx`
**Steps:**
- [ ] Render a Recharts stacked bar chart with three stacked series — Committed, Scheduled, Projected — one stacked bar per forecast month (X axis = month labels, Y axis = ILS).
- [ ] Wrap the chart in `role="figure"` with `aria-labelledby="{chartId}-title"`.
- [ ] Add a `<figcaption id="{chartId}-title">` whose text matches the visible chart heading ("Monthly Revenue Forecast").
- [ ] Add a visually-hidden `<table>` sibling containing the same per-month committed/scheduled/projected/total data, plus a "Show data table" toggle button adjacent to the chart that reveals it.
- [ ] Set the SVG root `<title>` (e.g. `"Revenue forecast: {firstMonth}–{lastMonth}"`) and `<desc>` summarizing the trend (e.g. `"Projected total grows from ₪X to ₪Y over N months"`).
- [ ] Add an `aria-live="polite"` region echoing the active tooltip's per-category breakdown on hover/focus.
- [ ] Make stacked segments keyboard-focusable (Tab) and activatable (Enter/Space) where interactive.
- [ ] **RTL config:** when `locale === 'he-IL'`, set `<YAxis orientation="right">` and `<Tooltip position={{ x: 'left' }}>`; LTR uses left/right respectively. `<XAxis orientation="bottom">` in both. Read direction from `useDirection`/locale.
- [ ] Honor `prefers-reduced-motion`: disable Recharts animation (`isAnimationActive={false}`) when the media query matches.
- [ ] Use design-token colors for the three series (no hardcoded hex); distinguishable for color-blind users (rely on legend + data table, not color alone).
**Schema / Interfaces:**
```tsx
function ForecastChart({ data, locale }: {
  data: RevenueForecastMonth[]; locale: string;
}) {
  const isRtl = locale === 'he-IL';
  // <ResponsiveContainer><ComposedChart>
  //   <YAxis orientation={isRtl ? 'right' : 'left'} />
  //   <XAxis orientation="bottom" />
  //   <Tooltip position={{ x: isRtl ? 'left' : 'right' }} />
  // Bars: committed/scheduled/projected stacked (stackId shared)
}
```
**Acceptance:**
- [ ] Chart exposes `role="figure"`, matching `figcaption`, SVG `<title>`+`<desc>`, an `aria-live` tooltip region, and a toggleable visually-hidden data table.
- [ ] Y axis mirrors to the right and tooltip to the left under `he-IL`.
- [ ] Animation is suppressed under `prefers-reduced-motion`.
- [ ] Segments reachable by keyboard; no color-only encoding.

### Task 9: Per-month data table + CSV export
**Blocks:** —  ·  **Blocked by:** 6
**Files:**
- Create: `apps/zync-app/src/features/revenue-forecast/ForecastTable.tsx`
- Create: `apps/zync-app/src/features/revenue-forecast/exportForecastCsv.ts`
**Steps:**
- [ ] Render a `Table` with columns Month, Committed, Scheduled, Projected, Total — one row per `monthly[]` entry, localized month labels and currency.
- [ ] Add an `[Export CSV]` `Button` that serializes the monthly table to CSV and triggers a client-side download.
- [ ] CSV columns: `Month,Committed,Scheduled,Projected,Total`; numeric values unformatted (raw numbers, no currency symbol) for spreadsheet use; UTF-8 BOM prefix so Hebrew headers render in Excel.
- [ ] Table is a real semantic `<table>` with header `<th scope="col">` cells (this table is the visible counterpart and may be reused as the chart's hidden data-table source).
**Schema / Interfaces:**
```ts
export function exportForecastCsv(monthly: RevenueForecastMonth[], filename?: string): void;
```
**Acceptance:**
- [ ] Table shows one row per forecast month with a correct per-row `Total`.
- [ ] CSV export downloads a well-formed file with header row, raw numeric values, and UTF-8 BOM.
- [ ] Table uses semantic markup with scoped headers.