# Customer Statement (כרטסת לקוח) — Implementation Plan **Spec:** docs/specs/2026-06-01-customer-statement.md · **Slug:** customer-statement · **Wave:** 16 **Depends on:** ar-aging-report, customers-module, hebrew-locale-dates, invoice-credit-notes, invoice-receipt-document, invoices-core, multi-currency, partial-payment-recording, print-layouts ## Goal Deliver a customer **statement of account** (כרטסת לקוח): a read-only projection over a customer's existing invoices, credit notes, receipts, and payments for a date range `[from, to]`, with opening balance, chronological debit/credit rows, a running balance, closing balance, and a reused aging footer. It is exposed as the screen `/customers/:id/statement`, a signed PDF, and an email-to-customer action, plus a portal-JWT-authenticated read path. **No new tables** — all data already exists upstream; a stored statement would duplicate and drift. ## Architecture A new statement assembler lives in `@zync/db` (server-side query helpers, tenant-scoped via `tenantQuery`) and is consumed by three Hono routes in the API app. The assembler reads: - `invoices` (debit rows): rows with `status IN ('TAX_ISSUED','PARTIALLY_PAID','PAID')` and `source IN ('invoice','proforma')`; columns `id, invoice_number, customer_id, currency, tax_issue_date, issue_date, total`. Credit notes are `invoices` rows with `source = 'credit_note'` and **negative** `total` (credit rows). - `invoice_payments` (credit rows — the canonical money events): `id, invoice_id, amount, currency, paid_at/created_at, receipt_id`. Defined by `partial-payment-recording`. - `receipts` + `receipt_payment_lines` (display metadata only): joined via `invoice_payments.receipt_id → receipts.id` to surface `receipt_number` and `doc_type` for the credit row label. Defined by `invoice-receipt-document`. **Receipts are NOT a separate credit source** — including both payments and receipts would double-count credits and break the running balance. Customer identity/serialization reuses `getCustomerWithStats` and `serializeCustomer` (customers-module). Formatting reuses `formatCurrency` and `formatDate` from hebrew-locale-dates. Aging buckets are reimplemented inline (no exported helper exists in ar-aging-report) and must stay consistent with spec 140's bucket conditions. PDF rendering reuses the existing HTML-to-PDF Worker pattern from invoices-core (R2 HTML snapshot + Heebo font); `print-layouts` `window.print()` is the browser fallback. Email send reuses `sendEmail` and the tenant email adapter. The UI is a new React page in zync-app reached from the customer detail header ("Statement") and from ar-aging row drill-down. A `useCustomerStatement` react-query hook (mirroring `useCustomer*` naming) drives it. ## Tech Stack - **API:** `apps/zync-api` (Hono on Cloudflare Workers) — 3 new routes under `/api/customers/:id/statement`. - **DB/helpers:** `packages/db` (`@zync/db`, Drizzle) — statement assembler + types; tenant-scoped reads via `tenantQuery`. - **Types:** `packages/types` (`@zync/types`) — `CustomerStatement`, `StatementRow`, `StatementAgingSummary`, `StatementCurrencyGroup`, `StatementQuery`, `SendStatementBody`. - **UI:** `apps/zync-app` (Vite+React) — `/customers/:id/statement` page + `useCustomerStatement` hook; uses `@zync/ui` (DataTable, Button, Select, Card, Toast) and `formatCurrency`/`formatDate` from `@zync/i18n` hebrew-locale-dates. - **PDF:** existing HTML-to-PDF Worker pattern (invoices-core); R2 binding `STORAGE`; Heebo font in R2; RTL via ``. - **Email:** `sendEmail` + tenant email adapter (`getAdapter`/`CommsAdapter`). - **Bindings:** `DB`/Hyperdrive (Neon Postgres), `STORAGE` (R2), `KV` (signed-PDF token), email adapter. - **Auth:** `authMiddleware` + `requirePermission` for staff; portal JWT (`customer-portal-access-control` pattern) for the portal read path. ## Wave Plan | Sub-wave | Tasks | Files touched | Parallelizable? | |----------|-------|---------------|-----------------| | 16.1 | Task 1 (types) | `packages/types` | Yes (no deps) | | 16.2 | Task 2 (assembler), Task 3 (aging) | `packages/db` | Task 3 parallel with 2; both block routes | | 16.3 | Task 4 (JSON route), Task 5 (PDF route), Task 6 (send route) | `apps/zync-api` | Serial-ish: share router file; 5 & 6 depend on 2/4 | | 16.4 | Task 7 (PDF HTML template), Task 8 (email template) | `apps/zync-api` / `packages/db` | Yes (parallel) | | 16.5 | Task 9 (UI page + hook) | `apps/zync-app` | After Task 4 | | 16.6 | Task 10 (route registry + customer-detail/ar-aging links) | `apps/zync-app` | After Task 9 | ## Tasks ### Task 1: Statement types in `@zync/types` **Blocks:** 2, 4, 5, 6, 9 · **Blocked by:** — **Files:** - Create: `packages/types/src/customer-statement.ts` - Modify: `packages/types/src/index.ts` (re-export) **Steps:** - [ ] Define the row, currency-group, aging-summary, full-statement, query, and send-body types. - [ ] Re-export all from the package index so API + app share one definition. - [ ] Reference existing `InvoiceStatus` for the source-document status field; do not redefine it. **Schema / Interfaces:** ```typescript // All monetary values are numbers in the row's currency (face currency), 2-decimal. export type StatementRowKind = 'invoice' | 'credit_note' | 'payment'; export interface StatementRow { date: string; // ISO date (yyyy-mm-dd) used for ordering kind: StatementRowKind; documentType: string; // localized label key, e.g. 'invoice' | 'credit_note' | 'receipt' documentNumber: string | null;// invoice_number / credit-note number / receipt_number (null if none) reference: string | null; // payment reference / cheque / external ref sourceId: string; // invoices.id or invoice_payments.id (UUID) debit: number; // 0 when this row is a credit credit: number; // 0 when this row is a debit runningBalance: number; // computed cumulatively in API layer } export interface StatementAgingSummary { d0_30: number; d31_60: number; d61_90: number; d90plus: number; // amounts are ILS-snapshot values (see multi-currency) } export interface StatementCurrencyGroup { currency: string; // 'ILS' | 'USD' | 'EUR' openingBalance: number; rows: StatementRow[]; closingBalance: number; } export interface CustomerStatement { customerId: string; // UUID customerName: string; from: string; // ISO date to: string; // ISO date groups: StatementCurrencyGroup[]; // one per currency the customer was billed in aging: StatementAgingSummary; // single ILS-snapshot footer across all currencies } export interface StatementQuery { from?: string; // ISO date; defaults to tenant fiscal-year start or earliest doc to?: string; // ISO date; defaults to today currency?: string; // optional filter to a single currency group } export interface SendStatementBody { from: string; // ISO date to: string; // ISO date currency?: string; message?: string; // optional custom body appended to localized template } ``` **Acceptance:** - [ ] `import { CustomerStatement, StatementRow, SendStatementBody } from '@zync/types'` resolves. - [ ] No `CREATE TABLE` / Drizzle table is introduced by this task. ### Task 2: Statement assembler in `@zync/db` **Blocks:** 4, 5, 6, 8 · **Blocked by:** 1, 3 **Files:** - Create: `packages/db/src/queries/customer-statement.ts` - Modify: `packages/db/src/index.ts` (export `buildCustomerStatement`) **Steps:** - [ ] Implement `buildCustomerStatement(db, { tenantId, customerId, from, to, currency? }): Promise` using `tenantQuery` so every read is tenant-scoped. - [ ] **Opening balance per currency** = sum of debits − credits dated `< from`, restricted to that currency (see query below). Compute once per currency group. - [ ] **Debit rows** = `invoices` where `customer_id = :customerId`, `source IN ('invoice','proforma')`, `status IN ('TAX_ISSUED','PARTIALLY_PAID','PAID')`, document date `>= from AND <= to`; debit = `total`. Document date = `COALESCE(tax_issue_date, issue_date)`. - [ ] **Credit-note rows** = `invoices` where `source = 'credit_note'`, `status IN ('TAX_ISSUED','PARTIALLY_PAID','PAID')`, date in range; credit = `abs(total)` (stored total is negative). - [ ] **Payment credit rows** = `invoice_payments` joined to `invoices` (for `customer_id`/`currency`) and LEFT JOIN `receipts` via `invoice_payments.receipt_id`; credit = `amount`, dated by `COALESCE(paid_at, created_at)`. **This is the single canonical credit-money source** — do NOT also union `receipts`/`receipt_payment_lines` as separate rows (that double-counts). Receipts contribute only `receipt_number`/`doc_type` for the row label. - [ ] Group all rows by `currency` (invoice/payment currency) into per-currency sub-statements; the spec mandates a separate running balance per currency. - [ ] Order each group's rows by document date ascending, tiebreak by kind (debits before credits same-day) then by document number. - [ ] Compute the running balance in the API layer by folding over ordered rows: `running = opening; for each row: running += debit − credit; row.runningBalance = running`. Closing balance = final running value. - [ ] When `to = today`, the closing balance per ILS group must equal the customer's current A/R for ILS (assert in tests-as-code if a test harness exists; otherwise document the invariant). - [ ] Call `computeStatementAging` (Task 3) with the customer's open invoices as of `to` to fill the single aging footer (ILS-snapshot amounts). - [ ] If `currency` filter is supplied, return only that group (still compute aging across all currencies in ILS). **Schema / Interfaces:** ```sql -- UPSTREAM tables consumed (already created elsewhere — DO NOT re-create): -- invoices(id UUID PK, tenant_id UUID, customer_id UUID, source TEXT, status TEXT, -- invoice_number TEXT, currency TEXT, issue_date DATE, tax_issue_date DATE, -- total NUMERIC(12,2), parent_invoice_id UUID) -- invoices-core / credit-notes -- invoice_payments(id UUID PK, tenant_id UUID, invoice_id UUID, amount NUMERIC(12,2), -- currency TEXT, paid_at TIMESTAMPTZ?, created_at TIMESTAMPTZ, -- reference TEXT?, recorded_by UUID, receipt_id UUID) -- partial-payment-recording + receipt FK -- receipts(id UUID PK, tenant_id UUID, customer_id UUID, invoice_id UUID, -- receipt_number TEXT, doc_type TEXT, currency TEXT, ils_exchange_rate NUMERIC(10,4), -- status TEXT, issued_at TIMESTAMPTZ?) -- invoice-receipt-document -- Opening balance per currency (debits − credits dated < :from): -- SELECT COALESCE(SUM(d),0) FROM ( -- SELECT total AS d FROM invoices -- WHERE customer_id=:cid AND currency=:cur AND source IN ('invoice','proforma') -- AND status IN ('TAX_ISSUED','PARTIALLY_PAID','PAID') -- AND COALESCE(tax_issue_date, issue_date) < :from -- UNION ALL -- SELECT total AS d FROM invoices -- credit notes: total is negative -- WHERE customer_id=:cid AND currency=:cur AND source='credit_note' -- AND status IN ('TAX_ISSUED','PARTIALLY_PAID','PAID') -- AND COALESCE(tax_issue_date, issue_date) < :from -- UNION ALL -- SELECT -p.amount AS d FROM invoice_payments p -- JOIN invoices i ON i.id = p.invoice_id -- WHERE i.customer_id=:cid AND p.currency=:cur -- AND COALESCE(p.paid_at, p.created_at)::date < :from -- ) x; ``` ```typescript export function buildCustomerStatement( db: Db, args: { tenantId: string; customerId: string; from: string; to: string; currency?: string }, ): Promise; ``` **Acceptance:** - [ ] Returns one `StatementCurrencyGroup` per distinct billing currency; running balance never mixes currencies. - [ ] A customer with one ₪1,170 invoice fully receipted shows debit then credit and closing `0.00` (matches spec example). - [ ] Credits are not double-counted: a payment that has a linked receipt produces exactly one credit row. ### Task 3: Inline aging bucket computation in `@zync/db` **Blocks:** 2 · **Blocked by:** — **Files:** - Create: `packages/db/src/queries/statement-aging.ts` - Modify: `packages/db/src/index.ts` (export `computeStatementAging`) **Steps:** - [ ] Implement `computeStatementAging(openInvoices, asOf): StatementAgingSummary` reimplementing spec 140's bucket conditions inline (no shared exported helper exists upstream). - [ ] Age = `asOf - effectiveDueDate` in days, where `effectiveDueDate = COALESCE(due_date, sent_at + tenant default_payment_terms_days)`. - [ ] Bucket per open invoice: Current (`due_date >= asOf`, excluded from the 4-bucket footer / shown as 0-aged not overdue), 1–30 (`1 ≤ age ≤ 30`), 31–60, 61–90, 90+ (`age > 90`). - [ ] Included statuses: `SENT`, `APPROVED`, `TAX_ISSUED`, `PARTIALLY_PAID`. Exclude `DRAFT`, `PAID`, `VOID`, `REJECTED`. - [ ] Outstanding amount per invoice for `PARTIALLY_PAID` = `total − SUM(invoice_payments.amount)`; for others = `total`. - [ ] Use the **ILS-snapshot** amount (`total_ils` when present, else `total` for ILS invoices) so the footer is ILS regardless of billing currency. - [ ] The footer maps to spec UI buckets 0-30 / 31-60 / 61-90 / 90+ (Current folded into 0-30 display per the spec footer layout). **Schema / Interfaces:** ```typescript interface OpenInvoiceForAging { status: InvoiceStatus; dueDate: string | null; // invoices.due_date sentAt: string | null; // sent_at total: number; totalIls: number | null; // multi-currency snapshot; null => ILS invoice, use total amountPaid: number; // SUM(invoice_payments.amount) for that invoice } export function computeStatementAging( invoices: OpenInvoiceForAging[], asOf: string, // ISO date defaultPaymentTermsDays: number, ): StatementAgingSummary; ``` **Acceptance:** - [ ] Bucket boundaries match spec 140 exactly (31 → 31–60, 61 → 61–90, 91 → 90+). - [ ] A single ₪2,106 invoice due within 30 days yields `d0_30 = 2106`, others `0` (matches spec example footer). ### Task 4: `GET /api/customers/:id/statement` (JSON) **Blocks:** 9 · **Blocked by:** 1, 2 **Files:** - Create: `apps/zync-api/src/routes/customer-statement.ts` - Modify: `apps/zync-api/src/routes/index.ts` (mount router) **Steps:** - [ ] Add a Hono router; mount under `/api/customers/:id/statement`. - [ ] Apply `authMiddleware`, then `requirePermission('customers:read')` AND `requirePermission('invoices:read')` for the staff path. - [ ] Accept a **portal JWT** alternative auth (verify via the `customer-portal-access-control` portal-JWT verifier); when authenticated as a portal user, force `:id` to the JWT's customer and honor portal visibility — read-only, no other change. - [ ] Validate query with a Zod schema (`statementQuerySchema`: optional `from`/`to` ISO dates, optional `currency` in `['ILS','USD','EUR']`). Default `to = today`, `from = earliest document date or fiscal-year start`. - [ ] Resolve the customer via `getCustomerWithStats` (404 if not in tenant). - [ ] Call `buildCustomerStatement` and return the `CustomerStatement` JSON. - [ ] Enforce Zod validation in the route (no raw body access) per `require-zod-validation-in-routes`; never build Drizzle queries inline (`no-raw-drizzle-from-routes`). **Schema / Interfaces:** ```typescript // Route: GET /api/customers/:id/statement?from=&to=¤cy= → CustomerStatement (200) const statementQuerySchema = z.object({ from: z.string().date().optional(), to: z.string().date().optional(), currency: z.enum(['ILS', 'USD', 'EUR']).optional(), }); ``` **Acceptance:** - [ ] Staff without `invoices:read` get 403; with both perms get 200 JSON. - [ ] Portal JWT for customer A cannot read customer B's statement. - [ ] Response matches `CustomerStatement` shape (groups + single aging footer). ### Task 5: `GET /api/customers/:id/statement/pdf` (signed PDF) **Blocks:** 9 · **Blocked by:** 2, 4, 7 **Files:** - Modify: `apps/zync-api/src/routes/customer-statement.ts` **Steps:** - [ ] Add `GET /pdf` with the same auth + Zod query as Task 4 (`customers:read` + `invoices:read`, or portal JWT). - [ ] Build the statement via `buildCustomerStatement`, render to HTML via Task 7's template, convert through the existing HTML-to-PDF Worker pattern (invoices-core), and stream the PDF. - [ ] Produce a **signed** URL: mint a short-lived signed token (`signSignedToken` / `verifySignedToken`) so the PDF link is shareable in email without exposing session auth; store nothing new — regenerate on demand. - [ ] Hebrew RTL: emit ``; embed Heebo font from R2 (`STORAGE`) as in invoices-core; the document inherits direction. - [ ] Respect `prefers-reduced-motion` is N/A for PDF; ensure no animation/auto-print scripts beyond `window.print()` fallback handled by the UI page. **Acceptance:** - [ ] Returns `application/pdf` with `Content-Disposition: attachment`. - [ ] Hebrew tenant renders RTL with Heebo glyphs; amounts formatted via `formatCurrency`. - [ ] Signed-token link verifies with `verifySignedToken` and expires. ### Task 6: `POST /api/customers/:id/statement/send` (email PDF) **Blocks:** — · **Blocked by:** 2, 5, 8 **Files:** - Modify: `apps/zync-api/src/routes/customer-statement.ts` **Steps:** - [ ] Add `POST /send`; require `customers:write` (send is a write action) via `requirePermission('customers:write')`. (No portal access for send — staff-initiated only.) - [ ] Validate body with `sendStatementSchema` (`from`, `to` required ISO dates; `currency?`; `message?`). - [ ] Resolve the customer's billing email (primary contact via `customer_contacts`; 422 if none). - [ ] Generate the PDF (reuse Task 5 path) and the localized subject/body (Task 8 template; honor tenant locale he/en). - [ ] Send via `sendEmail` using the tenant email adapter (`getAdapter`); attach the PDF. - [ ] Record a system communication on the customer timeline via `recordSystemCommunication` / `appendCustomerCommunication` ("Statement sent"). - [ ] Return `{ sent: true }`; surface adapter failures as 502 with a clear message. **Schema / Interfaces:** ```typescript const sendStatementSchema = z.object({ from: z.string().date(), to: z.string().date(), currency: z.enum(['ILS', 'USD', 'EUR']).optional(), message: z.string().max(2000).optional(), }); // POST /api/customers/:id/statement/send → { sent: boolean } (200) | 422 no-email | 502 adapter-fail ``` **Acceptance:** - [ ] Without `customers:write` → 403. - [ ] Customer with no contact email → 422, no email attempted. - [ ] On success, PDF emailed to customer and a communication row is appended. ### Task 7: Statement PDF/HTML template **Blocks:** 5 · **Blocked by:** 1 **Files:** - Create: `apps/zync-api/src/templates/customer-statement-html.ts` **Steps:** - [ ] Implement `renderStatementHtml(statement, locale): string` producing the print document: header ("Statement of Account — {customerName}"), period line, per-currency sub-statement (opening balance, rows table Date/Document/Debit/Credit/Balance, closing balance), and the aging footer (0-30 / 31-60 / 61-90 / 90+). - [ ] Render one table block per `StatementCurrencyGroup` (multi-currency customers get stacked sub-statements). - [ ] Use `formatDate(date, locale, 'short')` and `formatCurrency(amount, group.currency, locale)` from hebrew-locale-dates for every date/amount. - [ ] Emit `` for he locale; reuse the Heebo `@font-face` block and R2 font URL convention from invoices-core; include `@media print` rules consistent with `print-layouts` so the same markup prints cleanly via the browser fallback. - [ ] No inline raw HTML in React pages (`no-raw-html-in-pages` applies to app, not this server template); keep document text escaped to prevent injection of customer-supplied fields. **Schema / Interfaces:** ```typescript export function renderStatementHtml(statement: CustomerStatement, locale: 'he' | 'en'): string; ``` **Acceptance:** - [ ] Single-currency statement renders one table; ILS+USD customer renders two stacked sub-statements. - [ ] Hebrew locale output is RTL and uses ₪ via `formatCurrency`. ### Task 8: Localized statement email subject/body **Blocks:** 6 · **Blocked by:** — **Files:** - Create: `apps/zync-api/src/templates/customer-statement-email.ts` **Steps:** - [ ] Implement `buildStatementEmail(args): { subject: string; body: string }` returning Hebrew or English copy per tenant locale. - [ ] Hebrew subject e.g. `כרטסת לקוח — {businessName}`; body summarizing period and closing balance, appending the optional `message`. - [ ] Keep copy locale-keyed (reuse `translations` keys where the i18n catalog exists; otherwise inline he/en strings). **Schema / Interfaces:** ```typescript export function buildStatementEmail(args: { locale: 'he' | 'en'; businessName: string; customerName: string; from: string; to: string; closingBalance: number; currency: string; message?: string; }): { subject: string; body: string }; ``` **Acceptance:** - [ ] he locale yields Hebrew subject/body; en locale yields English. - [ ] Optional `message` is appended when provided. ### Task 9: `/customers/:id/statement` UI page + `useCustomerStatement` hook **Blocks:** 10 · **Blocked by:** 4 **Files:** - Create: `apps/zync-app/src/pages/customers/CustomerStatementPage.tsx` - Create: `apps/zync-app/src/hooks/useCustomerStatement.ts` - Modify: `apps/zync-app/src/router.tsx` (or route table) to add the route **Steps:** - [ ] Implement `useCustomerStatement(customerId, { from, to, currency })` react-query hook calling `GET /api/customers/:id/statement` (mirror existing `useCustomer*` hooks). - [ ] Build the page per the spec wireframe: header with customer name and `[⬇ PDF]` + `[✉ Send]` actions; period date-range pickers; currency `Select`; opening balance; rows `DataTable` (Date, Document, Debit, Credit, Balance); closing balance; aging footer line. - [ ] Render one sub-statement section per currency group; switching the currency `Select` filters to one group. - [ ] `[⬇ PDF]` → open `GET /pdf` signed URL; provide `window.print()` fallback button per `print-layouts`. - [ ] `[✉ Send]` → confirm dialog (optional message field) → `POST /send`; show `Toast` on success/failure. - [ ] Format all dates/amounts with `formatDate`/`formatCurrency`; respect direction via `useDirection`/`useLocale` so Hebrew renders RTL. - [ ] A11y: table has `role`/caption, action buttons have aria-labels, date pickers labelled; honor `prefers-reduced-motion` for any transitions. - [ ] No hardcoded colors/spacing/radius (`no-hardcoded-colors`, `no-hardcoded-spacing`, `no-radius-ladder`); use design tokens from `@zync/ui`. **Schema / Interfaces:** ```typescript export function useCustomerStatement( customerId: string, params: { from?: string; to?: string; currency?: string }, ): UseQueryResult; ``` **Acceptance:** - [ ] Page loads statement, renders rows + running balance + aging footer matching the API. - [ ] PDF and Send actions work; Send shows a Toast. - [ ] Hebrew locale renders RTL; no raw hex colors or px spacing in the component. ### Task 10: Route registry + entry points (customer detail header, AR-aging drill-down) **Blocks:** — · **Blocked by:** 9 **Files:** - Modify: `apps/zync-app/src/router.tsx` / route registry (register `/customers/:id/statement`) - Modify: customer detail header component (add "Statement" action) - Modify: AR-aging report row component (`[Statement]` link → `/customers/:id/statement`) **Steps:** - [ ] Register the new route in the app route registry so navigation/search resolves it. - [ ] Add a "Statement" action to the customer detail header linking to `/customers/:id/statement`. - [ ] Wire the ar-aging report customer-row `[Statement]` drill-down to navigate to `/customers/:id/statement` (it currently delegates this to spec 183). - [ ] Do NOT build the customer-portal "Account" screen — that is owned downstream by `tenant-portals`; only the JWT-authed read path (Task 4) is in scope here. **Acceptance:** - [ ] Customer detail header "Statement" navigates to the page. - [ ] AR-aging row `[Statement]` opens the correct customer's statement. - [ ] Route is discoverable via the global route registry/search.