# Bituach Leumi Income Report (ביטוח לאומי) — Implementation Plan

**Spec:** docs/specs/2026-06-01-bituach-leumi.md  ·  **Slug:** bituach-leumi  ·  **Wave:** 15
**Depends on:** expenses-module, financial-statements, foundation-auth-rbac, invoices-core, israeli-tax-reports

## Goal
Deliver the annual Bituach Leumi (National Insurance Institute / ביטוח לאומי) income report for Israeli self-employed users: a pre-calculated summary of gross revenue, deductible expenses, contractor payouts, and a manual depreciation adjustment, plus an estimated NII contribution computed via proper marginal banding on monthly income. The report is available to all tiers, supports tracking of monthly NII advance payments, and exports to a Hebrew/RTL-safe Excel workbook through the shared financial export writer (`financial-statements` spec 170). Rates and thresholds are read per-year from KV (`nii_rates:{year}`), never hard-coded.

## Architecture
The report aggregates from upstream tables: `invoices` (gross revenue net of VAT, using `total`/`vat_amount`/`status`/`tax_issue_date`), `expenses` (deductible expenses net of VAT, using `amount`/`vat_amount`/`vat_deductible`/`status`/`expense_date`), and `payout_bills` (contractor payouts via `net_amount`/`status`/`paid_at` from `contractor-payouts`). Depreciation is a per-request manual input — not stored. The NII estimate lives in `packages/reports/src/nii-estimate.ts` (`estimateNIIContributions`) consuming `NIIRates` loaded from KV. A new `nii_advance_payments` table (this spec's only new table) tracks monthly advances, surfaced through CRUD routes. The Excel export reuses the shared writer described in `financial-statements` spec 170 (`writeFinancialWorkbook` in `packages/reports/src/xlsx.ts`): every sheet sets `worksheet.views[0].rightToLeft = true` + a Hebrew-capable font, and CSV-formula-injection neutralization (`'`-prefix on cells starting with `= + - @ \t \r`). API routes are Hono handlers in `apps/zync-api` guarded by `authMiddleware` + `requirePermission('reports:read' | 'reports:export')` for report endpoints and `requirePermission` on advance CRUD, scoped by `tenantQuery`. The React report page lives in `apps/zync-app`, reading via a `useBituachLeumiReport` hook and an advances tab with `useNiiAdvances`. Reuses upstream exports: `createDb`/`tenantQuery`, `authMiddleware`, `requirePermission`, `buildPaginated`, design-system `Card`/`StatCard`/`Tabs`/`DataTable`/`Form`/`Input`/`Select`/`Button`/`Toast`, and i18n `LocaleProvider`/`useDirection`.

## Tech Stack
- **Packages:** `packages/reports` (new module `nii-estimate.ts`; consumes the `xlsx.ts` shared writer from financial-statements), `@zync/db` (Drizzle schema + migration), `@zync/types`.
- **Apps:** `apps/zync-api` (Hono routes, KV rate loader, xlsx route handler), `apps/zync-app` (Vite+React report page + advances tab).
- **Libraries:** Drizzle ORM, `exceljs` (via shared writer), `zod` (route validation), `@tanstack/react-query` (hooks).
- **Cloudflare bindings:** Neon Postgres via Hyperdrive (`DB`), `KV` (for `nii_rates:{year}`).

## Wave Plan
| Sub-wave | Tasks | Files touched | Parallelizable? |
|----------|-------|---------------|-----------------|
| 15a | 1, 2 | `packages/db` schema + migration, `packages/types` | Task 2 after 1 |
| 15b | 3, 4 | `packages/reports/src/nii-estimate.ts`, `apps/zync-api` rate loader + aggregation | Yes (3 ‖ 4 after types) |
| 15c | 5, 6, 7 | `apps/zync-api` routes (report, xlsx, advances CRUD) | 5 ‖ 7; 6 after 4+5 |
| 15d | 8, 9 | `apps/zync-app` report page + advances tab | After 5,6,7 |

## Tasks

### Task 1: `nii_advance_payments` table + migration
**Blocks:** 2, 7  ·  **Blocked by:** —
**Files:**
- Create: `packages/db/src/schema/nii-advance-payments.ts`
- Modify: `packages/db/src/schema/index.ts`
- Create: `packages/db/migrations/<timestamp>_nii_advance_payments.sql`
**Steps:**
- [ ] Define the Drizzle pgTable `niiAdvancePayments` mapping the DDL below; export it from the schema barrel.
- [ ] Write the raw SQL migration with the table, the `month BETWEEN 1 AND 12` CHECK, and the `UNIQUE (tenant_id, user_id, year, month)` constraint.
- [ ] Add a supporting index for the per-year listing query: `(tenant_id, user_id, year)`.
**Schema / Interfaces:**
```sql
CREATE TABLE nii_advance_payments (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id   UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  year        INTEGER NOT NULL,
  month       INTEGER NOT NULL CHECK (month BETWEEN 1 AND 12),
  amount      NUMERIC(12,2) NOT NULL,
  paid_at     DATE NOT NULL,
  notes       TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, user_id, year, month)
);
CREATE INDEX idx_nii_advances_lookup ON nii_advance_payments(tenant_id, user_id, year);
```
**Acceptance:**
- [ ] Migration applies cleanly on Neon; re-inserting an existing `(tenant_id, user_id, year, month)` row raises a unique-violation (mapped to HTTP 409 in Task 7).
- [ ] `month = 0` or `month = 13` is rejected by the CHECK.

### Task 2: Shared types (`@zync/types`)
**Blocks:** 3, 4, 5, 6, 7, 8, 9  ·  **Blocked by:** 1
**Files:**
- Create: `packages/types/src/bituach-leumi.ts`
- Modify: `packages/types/src/index.ts`
**Steps:**
- [ ] Define and export the interfaces below from the types barrel.
- [ ] Re-export `NiiAdvancePayment` row type inferred from the Drizzle table (or hand-write to match the DDL).
**Schema / Interfaces:**
```ts
export interface NIIRates {
  reduced_rate: number          // 0.0597
  full_rate: number             // 0.1783
  band1_monthly_ils: number     // ≈ 6_331 (60% of average wage)
  avg_wage_monthly_ils: number  // ≈ 10_551
  income_cap_monthly_ils: number// ≈ 45_075 (בסיס הכנסה המרבי)
  health_share?: number         // optional display-only split, default 0
}
export interface NIIEstimate {
  national_insurance: number
  health_insurance: number
  total: number
}
export interface BituachLeumiReport {
  year: number
  gross_revenue_net_vat: number
  deductible_expenses: number
  contractor_payouts: number
  depreciation_deduction: number
  net_income: number
  nii_contributions: NIIEstimate
  advances_paid: { total: number; by_month: { month: number; amount: number }[] }
}
export interface NiiAdvancePayment {
  id: string
  tenant_id: string
  user_id: string
  year: number
  month: number
  amount: number
  paid_at: string   // ISO date (YYYY-MM-DD)
  notes: string | null
  created_at: string
}
export interface CreateNiiAdvanceInput {
  year: number
  month: number      // 1..12
  amount: number
  paid_at: string    // YYYY-MM-DD
  notes?: string
}
```
**Acceptance:**
- [ ] `import { BituachLeumiReport, NIIRates, NIIEstimate, NiiAdvancePayment } from '@zync/types'` type-checks.

### Task 3: NII marginal-banding estimator (`packages/reports`)
**Blocks:** 6  ·  **Blocked by:** 2
**Files:**
- Create: `packages/reports/src/nii-estimate.ts`
- Modify: `packages/reports/src/index.ts`
**Steps:**
- [ ] Implement `estimateNIIContributions(annualNetIncome, rates)` using marginal banding on monthly income, per the spec.
- [ ] Clamp `annualNetIncome` at 0 (no negative contributions); annualise the monthly contribution by ×12 and round.
- [ ] Apply the optional `health_share` to derive the display-only national/health split; default split is 0 so `national_insurance = total`.
- [ ] Export `estimateNIIContributions` from the package index.
**Schema / Interfaces:**
```ts
// packages/reports/src/nii-estimate.ts
import type { NIIRates, NIIEstimate } from '@zync/types'

export function estimateNIIContributions(annualNetIncome: number, rates: NIIRates): NIIEstimate {
  const monthlyIncome = Math.max(0, annualNetIncome) / 12
  const band1 = rates.band1_monthly_ils       // ≈ 6_331 (60% avg wage)
  const cap   = rates.income_cap_monthly_ils  // ≈ 45_075 (בסיס הכנסה המרבי)
  const reducedRate = rates.reduced_rate      // 0.0597
  const fullRate    = rates.full_rate         // 0.1783

  const inReduced = Math.min(monthlyIncome, band1)
  const inFull    = Math.max(0, Math.min(monthlyIncome, cap) - band1)
  // Income above the cap is not charged.

  const monthlyContribution = inReduced * reducedRate + inFull * fullRate
  const total = Math.round(monthlyContribution * 12)

  const healthInsurance = Math.round(total * (rates.health_share ?? 0))
  return {
    national_insurance: total - healthInsurance,
    health_insurance: healthInsurance,
    total,
  }
}
```
**Acceptance:**
- [ ] Income within band1 only uses the reduced rate; income spanning band1→cap charges 5.97% on the first portion and 17.83% on the remainder.
- [ ] Income above the monthly cap adds no further contribution.
- [ ] `estimateNIIContributions(0, rates)` returns `{ national_insurance: 0, health_insurance: 0, total: 0 }`.

### Task 4: KV rate loader + report aggregation service (`apps/zync-api`)
**Blocks:** 5, 6  ·  **Blocked by:** 2
**Files:**
- Create: `apps/zync-api/src/lib/nii-rates.ts`
- Create: `apps/zync-api/src/services/bituach-leumi.ts`
**Steps:**
- [ ] `loadNIIRates(env, year)`: read `nii_rates:{year}` from the `KV` binding, JSON-parse into `NIIRates`; throw a typed error (mapped to a clear 4xx/5xx) if the year's rates are not configured — never fall back to hard-coded constants.
- [ ] `getBituachLeumiReport(db, tenantId, year, depreciationDeduction)`: run the three aggregation queries below via `tenantQuery`, compute `net_income`, call `estimateNIIContributions`, attach advances aggregation (Task 7 query), and return a `BituachLeumiReport`.
- [ ] `depreciation_deduction` defaults to 0 when absent; coerce/validate to a non-negative number at the route layer (Task 5).
**Schema / Interfaces:**
```sql
-- Gross revenue (issued invoices, net of VAT, in report year):
SELECT COALESCE(SUM(total - vat_amount), 0) AS gross_revenue_net_vat
FROM invoices
WHERE tenant_id = :tenantId
  AND status NOT IN ('DRAFT', 'SENT', 'VOID')
  AND date_part('year', tax_issue_date) = :year;

-- Deductible expenses (net of VAT, completed, deductible, in report year):
SELECT COALESCE(SUM(amount - COALESCE(vat_amount, 0)), 0) AS deductible_expenses
FROM expenses
WHERE tenant_id = :tenantId
  AND status = 'COMPLETED'
  AND vat_deductible = true
  AND date_part('year', expense_date) = :year;

-- Contractor payouts (net amount, paid, in report year):
SELECT COALESCE(SUM(net_amount), 0) AS contractor_payouts
FROM payout_bills
WHERE tenant_id = :tenantId
  AND status = 'PAID'
  AND date_part('year', paid_at) = :year;
```
```ts
// net_income = gross_revenue_net_vat - deductible_expenses - contractor_payouts - depreciation_deduction
export async function loadNIIRates(env: Env, year: number): Promise<NIIRates>
export async function getBituachLeumiReport(
  db: Db, tenantId: string, userId: string, year: number, depreciationDeduction: number, rates: NIIRates,
): Promise<BituachLeumiReport>
```
**Acceptance:**
- [ ] All three SUMs filter by `tenant_id` and the report year; revenue excludes DRAFT/SENT/VOID, expenses require `status='COMPLETED' AND vat_deductible=true`, payouts require `status='PAID'`.
- [ ] `net_income = gross_revenue_net_vat - deductible_expenses - contractor_payouts - depreciation_deduction`.
- [ ] Missing `nii_rates:{year}` produces a clear error, never a hard-coded-rate result.

### Task 5: Report JSON API route (`GET /api/reports/bituach-leumi`)
**Blocks:** 6, 8  ·  **Blocked by:** 4
**Files:**
- Create: `apps/zync-api/src/routes/reports/bituach-leumi.ts`
- Modify: `apps/zync-api/src/routes/index.ts` (mount route)
**Steps:**
- [ ] Add a Hono route guarded by `authMiddleware` + `requirePermission('reports:read')`.
- [ ] Validate query with zod: `{ year: coerce int (1900..2100), depreciation_deduction?: coerce non-negative number }`.
- [ ] Resolve `tenantId` and `userId` from the session; call `loadNIIRates` then `getBituachLeumiReport`; return the `BituachLeumiReport` JSON.
**Schema / Interfaces:**
```
GET /api/reports/bituach-leumi
  query: { year: number, depreciation_deduction?: number }
  Requires: reports:read
  Response: BituachLeumiReport
```
**Acceptance:**
- [ ] Request without `reports:read` is rejected by `requirePermission`.
- [ ] Response matches `BituachLeumiReport`; omitting `depreciation_deduction` treats it as 0.
- [ ] A negative `depreciation_deduction` is rejected by zod.

### Task 6: Excel export route (`GET /api/reports/bituach-leumi/xlsx`)
**Blocks:** 8  ·  **Blocked by:** 4, 5
**Files:**
- Create: `apps/zync-api/src/routes/reports/bituach-leumi-xlsx.ts`
- Modify: `apps/zync-api/src/routes/index.ts` (mount route)
**Steps:**
- [ ] Add a Hono route guarded by `authMiddleware` + `requirePermission('reports:export')`; same zod query as Task 5.
- [ ] Build the report via `getBituachLeumiReport`, plus fetch the supporting row lists for the year: qualifying invoices (number, date, customer, net-of-VAT amount), deductible expenses (date, category, vendor, net-of-VAT amount), contractor payouts, and recorded NII advances.
- [ ] Write all five worksheets through the shared `writeFinancialWorkbook` writer from `packages/reports/src/xlsx.ts` (financial-statements spec 170): Summary, Invoices, Expenses, Payouts, NII Advances.
- [ ] Confirm the shared writer sets `worksheet.views[0].rightToLeft = true` + a Hebrew-capable font (Arial/David) on header + data cells on every sheet, and `'`-prefixes any cell string beginning with `= + - @ \t \r` (formula-injection guard for free-text vendor/customer/notes).
- [ ] Stream the workbook buffer with `Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet` and a `Content-Disposition` filename `bituach-leumi-{year}.xlsx`.
**Schema / Interfaces:**
```
GET /api/reports/bituach-leumi/xlsx
  query: { year: number, depreciation_deduction?: number }
  Requires: reports:export
  Response: xlsx binary (5 tabs: Summary, Invoices, Expenses, Payouts, NII Advances)
```
**Acceptance:**
- [ ] Request without `reports:export` is rejected.
- [ ] Workbook has exactly the five named tabs, each with `rightToLeft = true` and a Hebrew-capable font.
- [ ] A vendor/customer/notes cell containing `=SUM(...)` is written as `'=SUM(...)` (no formula execution on open).

### Task 7: NII advances CRUD API (`/api/nii-advances`)
**Blocks:** 9  ·  **Blocked by:** 1, 2
**Files:**
- Create: `apps/zync-api/src/routes/nii-advances.ts`
- Modify: `apps/zync-api/src/routes/index.ts` (mount route)
**Steps:**
- [ ] `GET /api/nii-advances?year=` — list advances for the session user + tenant + year, ordered by `month`; include the YTD total. Guarded by `requirePermission('reports:read')`.
- [ ] `POST /api/nii-advances` — zod body `CreateNiiAdvanceInput`; insert scoped by `tenant_id`/`user_id`; on unique-violation `(tenant_id,user_id,year,month)` return HTTP 409 (so the UI switches that month to edit mode). Guarded by `requirePermission('reports:write')` (fallback `settings:write` if `reports:write` is not seeded).
- [ ] `PATCH /api/nii-advances/:id` — zod partial body `{ amount?, paid_at?, notes? }`; update only rows belonging to the session tenant+user via `tenantQuery`.
- [ ] `DELETE /api/nii-advances/:id` — delete the row scoped to tenant+user.
- [ ] All write routes validate with zod (`require-zod-validation-in-routes`) and access the DB only via `tenantQuery` (`no-raw-drizzle-from-routes`).
**Schema / Interfaces:**
```
GET    /api/nii-advances?year=number   → { advances: NiiAdvancePayment[], ytd_total: number }   (reports:read)
POST   /api/nii-advances               → NiiAdvancePayment  (body: CreateNiiAdvanceInput; 409 on dup month)  (reports:write)
PATCH  /api/nii-advances/:id           → NiiAdvancePayment  (body: { amount?, paid_at?, notes? })  (reports:write)
DELETE /api/nii-advances/:id           → 204  (reports:write)
```
```sql
-- listing + YTD aggregation for the report's advances_paid block:
SELECT month, amount FROM nii_advance_payments
WHERE tenant_id = :tenantId AND user_id = :userId AND year = :year
ORDER BY month ASC;
```
**Acceptance:**
- [ ] POST of a month already recorded for the year returns 409 (unique constraint).
- [ ] PATCH/DELETE on a row owned by another tenant or user affects 0 rows (404, not cross-tenant mutation).
- [ ] GET returns months ascending with a correct YTD total.

### Task 8: Report page UI (`/reports/bituach-leumi`)
**Blocks:** —  ·  **Blocked by:** 5, 6
**Files:**
- Create: `apps/zync-app/src/pages/reports/BituachLeumiReport.tsx`
- Create: `apps/zync-app/src/hooks/useBituachLeumiReport.ts`
- Modify: `apps/zync-app/src/routes.tsx` (register `/reports/bituach-leumi`)
**Steps:**
- [ ] `useBituachLeumiReport(year, depreciationDeduction)` — react-query hook hitting `GET /api/reports/bituach-leumi`.
- [ ] Render the Income block (gross revenue, less deductible expenses, less contractor payouts, less depreciation, net income, monthly average = net/12) and the Estimated NII Contributions block (national insurance, health insurance, total) using `Card`/`StatCard`.
- [ ] Add the **Depreciation adjustment** numeric input (`Input`, non-negative) that re-queries with `depreciation_deduction` on change (debounced).
- [ ] Render the estimate-disclaimer notice ("This is an estimate… consult your accountant") with `role="note"` / appropriate aria semantics.
- [ ] Add the **Export Excel** button hitting `GET /api/reports/bituach-leumi/xlsx?year&depreciation_deduction`, disabled unless the user has `reports:export`.
- [ ] Use `Tabs` to host the report ("Summary") and the NII Advances tab (Task 9); set direction via `useDirection`/`LocaleProvider` so Hebrew labels render RTL; honor `prefers-reduced-motion` for any transitions.
- [ ] No raw HTML in pages (`no-raw-html-in-pages`); use design-system components; no hardcoded colors/spacing/radius.
**Acceptance:**
- [ ] Page shows all report lines and updates net income + NII estimate live when depreciation changes.
- [ ] Export button is hidden/disabled without `reports:export`.
- [ ] Layout is RTL under Hebrew locale; estimate notice is announced to assistive tech.

### Task 9: NII Advances tab UI
**Blocks:** —  ·  **Blocked by:** 7, 8
**Files:**
- Create: `apps/zync-app/src/pages/reports/NiiAdvancesTab.tsx`
- Create: `apps/zync-app/src/hooks/useNiiAdvances.ts`
**Steps:**
- [ ] `useNiiAdvances(year)` — react-query hook for list + `create`/`update`/`delete` mutations against `/api/nii-advances`.
- [ ] Inline entry `Form`: Month `Select` (lists only months **not yet recorded** for the year; recorded months disabled), Amount `Input`, Paid-on date `Input`, Notes `Input`, "Add advance" `Button`.
- [ ] On POST 409, switch the conflicting month to **edit mode** (PATCH): pre-fill the form with that row's values.
- [ ] List recorded advances (`DataTable`): Month, Amount, Paid on, Notes, with **Edit** (loads row into form, submits PATCH) and **✕** (DELETE after a confirm `Dialog`).
- [ ] Show "Advances paid YTD: ₪X" total under the list.
- [ ] Surface success/error via `toast`/`Toaster`; RTL-aware; reduced-motion respected.
**Acceptance:**
- [ ] Month dropdown excludes (disables) already-recorded months; selecting Edit on a recorded month pre-fills and PATCHes.
- [ ] Adding a duplicate month surfaces the 409 by switching to edit mode rather than erroring out.
- [ ] Delete prompts a confirm and removes the row; YTD total recomputes.