# Reports & Analytics — Implementation Plan **Spec:** docs/specs/2026-05-30-reports-analytics.md · **Slug:** reports-analytics · **Wave:** 10 **Depends on:** customers-module, expenses-module, foundation-auth-rbac, foundation-monorepo, invoices-core, marketing-catalogs-campaigns, projects-module, time-management ## Goal Deliver the tenant Reports & Analytics surface: (1) Israeli statutory **Tax & Compliance Reports** (PCN874 VAT return, Revenue Ledger, Invoice Report, Payment Report, Expense Deductibility, Contractor Withholding Summary, Income-Tax Prepayment Estimate) generated on-demand from live financial data and exportable as RTL-Hebrew Excel / CSV; and (2) **Custom Analytics Dashboards** — per-user, widget-based dashboards on a 12-column grid with shared date-range controls and Recharts visualizations. All reports are tenant-scoped and `reports:read`/`reports:write` gated. Two new tables (`dashboards`, `dashboard_widgets`); everything else is consumed read-only. ## Architecture - **New tables (this spec):** `dashboards`, `dashboard_widgets` in `packages/db`. Both tenant-scoped; dashboards are per-user. Plus one delta column `customers.tax_id` (the Revenue Ledger and PCN874 detail sheet require a customer tax ID that no upstream table currently defines). - **Read-only upstream tables consumed (reference by exact name, never re-emit DDL):** `invoices` (invoices-core: `invoice_number`, `subtotal`, `vat_amount`, `total`, `vat_rate`, `currency`, `status`, `issue_date`, `tax_issue_date`, `due_date`, `customer_id`, `project_id`), `invoice_lines` (`taxable`, `line_total`), `invoice_payments` (partial-payment-recording: `amount`, `currency`, `paid_at`, `source`, `reference`, `receipt_id`), `invoice_sequences`, `expenses` (expenses-module: `amount`, `vat_amount`, `vat_deductible`, `deduction_pct`, `expense_category`, `expense_date`, `status`, `approval_status`, `vendor_name`, `vendor_tax_id`, `invoice_number`), `time_entries` (time-management: `duration_seconds`, `billable`, `project_id`, `user_id`, `started_at`), `payout_bills` (contractor-payouts: `status`, `paid_at`, `amount`, `payment_method`, `contractor_id`, `period_start`, `period_end`), `contractors` (`name`, `tax_id`), `leads` (marketing-leads-pipeline: `stage`), `tickets` (crm-support-center: `status`, `category`, `created_at`, `resolved_at`), `customers` (`name`, `tax_id`), `tax_rates` + `getTaxRate(db, countryCode, taxType, date)` (admin-dashboard). - **Permissions:** routes gate on `requirePermission('reports:read')` (view + export) and `requirePermission('reports:write')` (dashboard create/edit). `reports:read` is already seeded upstream; `reports:write` is NOT — this plan adds it to the permission seed + grants it to OWNER/ADMIN roles. - **AE funnel reads:** the Leads-Funnel widget reads Cloudflare Analytics Engine via the **AE SQL HTTP API** using the new `CF_ANALYTICS_READ_TOKEN` secret — NOT the `ANALYTICS_ENGINE` binding (write-only). Funnel event names (`catalog_view`, `lead_captured`, `proposal_accepted`, `invoice_paid`) and the `{tenantId, utmSource, utmMedium, utmCampaign}` blob layout are defined by marketing-catalogs-campaigns. - **Caching:** `cache.default` 5-min TTL, key `{tenantId}:{reportType}:{period}:v{financials_version}` where `financials_version` is a KV counter (`financials_version:{tenantId}`) bumped on each invoice/expense write (the write-side increment is owned by invoices-core / expenses-module; this plan owns the read helper and the cache-key contract). - **Data flow:** React pages (`apps/zync-app`) call Hono routes (`apps/zync-api`) then `tenantQuery`-bound query helpers in `packages/db`, which aggregate and serialize raw rows; charts render client-side (Recharts); Excel is built server-side (`xlsx`). ## Tech Stack - **DB (`packages/db`):** Drizzle ORM, drizzle-kit, `@neondatabase/serverless` over Hyperdrive binding `DB`. New schema file `schema/dashboards.ts`, delta migration on `customers`, query helpers `queries/reports.ts` + `queries/dashboards.ts`. - **Types (`packages/types`):** new `DashboardObject`, `DashboardWidgetObject`, `WidgetType`, `ReportPeriod`, report row types. - **API (`apps/zync-api`):** Hono route groups `/api/reports/*`, `/api/dashboards/*`, `/api/analytics/query`; middleware `authMiddleware` then `requireModuleEnabled('reports')` then `requirePermission(perm)`; Zod validation (`require-zod-validation-in-routes`); no raw Drizzle in routes (`no-raw-drizzle-from-routes`). - **App (`apps/zync-app`):** React + Vite, Recharts for charts, react-query hooks, `DataTable`/`StatCard`/`Tabs`/`Select`/`Card` from `@zync/ui`, `LocaleProvider`/`useDirection` for RTL. - **Excel:** `xlsx` npm (pure-JS, Workers-safe) — server-side generation in `apps/zync-api`. - **Bindings/secrets:** `DB` (Hyperdrive), `KV` (financials_version + cache helper), `cache.default`, new secret `CF_ANALYTICS_READ_TOKEN`. ## Wave Plan | Sub-wave | Tasks | Files touched | Parallelizable? | |----------|-------|---------------|-----------------| | A — schema + perms | 1, 2, 3 | `packages/db/src/schema/dashboards.ts`, customers delta migration, `packages/types`, auth permission seed | No (2,3 after 1) | | B — infra helpers | 4, 5 | `packages/db/src/reports/cache.ts`, `apps/zync-api/src/reports/xlsx.ts`, `apps/zync-api/src/reports/ae.ts` | Yes (parallel) | | C — report queries | 6, 7, 8, 9, 10, 11 | `packages/db/src/queries/reports.ts` | Yes (each report independent, after A+B) | | D — dashboard queries | 12 | `packages/db/src/queries/dashboards.ts` | Yes (after A) | | E — analytics widget queries | 13 | `packages/db/src/queries/analytics.ts` | Yes (after C,D) | | F — API routes | 14, 15, 16 | `apps/zync-api/src/reports/*`, `apps/zync-api/src/dashboards/*` | No (after C,D,E) | | G — UI: tax reports | 17, 18, 19 | `apps/zync-app/src/reports/*` | Yes (after F) | | H — UI: analytics | 20, 21, 22 | `apps/zync-app/src/analytics/*` | Yes (after F) | | I — seed + tests | 23, 24 | seed hook, tests | No (last) | ## Tasks ### Task 1: New tables + customer tax-ID delta (Drizzle schema) **Blocks:** 2, 3, 4, 12, 14, 23 · **Blocked by:** — **Files:** - Create: `packages/db/src/schema/dashboards.ts` - Create: `packages/db/migrations/_reports_analytics.sql` (drizzle-kit generated) - Modify: `packages/db/src/schema/index.ts` (export new tables), `packages/db/src/schema/customers.ts` (add `tax_id` column) **Steps:** - [ ] Define `dashboards` and `dashboard_widgets` Drizzle tables in canonical Postgres (DDL below). `id` uses `uuid().primaryKey().defaultRandom()`; all FKs `uuid().references(() => parent.id)` UUID against UUID. - [ ] `widget_type` is `TEXT NOT NULL` with a CHECK constraint listing the 12 widget-library keys. - [ ] Add nullable `tax_id TEXT` to `customers` (Revenue Ledger / PCN874 detail require Customer tax ID; no upstream table defines it). This is an additive `ALTER TABLE customers ADD COLUMN tax_id TEXT` — nullable, no backfill, no behavior change to customers-module. - [ ] `drizzle-kit generate` emits Postgres DDL only (UUID PK, TIMESTAMPTZ, BOOLEAN, JSONB, INTEGER) — no SQLite types. **Schema / Interfaces:** ```sql CREATE TABLE dashboards ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES tenants(id), user_id UUID NOT NULL REFERENCES users(id), -- dashboards are per-user name TEXT NOT NULL, is_default BOOLEAN NOT NULL DEFAULT false, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX idx_dashboards_tenant_user ON dashboards(tenant_id, user_id); CREATE TABLE dashboard_widgets ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), dashboard_id UUID NOT NULL REFERENCES dashboards(id) ON DELETE CASCADE, tenant_id UUID NOT NULL REFERENCES tenants(id), widget_type TEXT NOT NULL CHECK (widget_type IN ( 'revenue_kpi','invoice_status_pipeline','open_invoices_aging', 'time_by_project','time_by_team_member','billable_vs_nonbillable', 'customer_revenue','expense_breakdown','leads_funnel', 'lead_pipeline_by_stage','ticket_resolution_time','ticket_volume_by_category')), position_x INTEGER NOT NULL, -- grid column (0-based, 12-col grid) 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, chart-type toggle created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX idx_dashboard_widgets_dashboard ON dashboard_widgets(dashboard_id); ALTER TABLE customers ADD COLUMN tax_id TEXT; -- customer tax id; nullable ``` **Acceptance:** - [ ] Migration applies cleanly to Neon; `dashboards`, `dashboard_widgets` exist with UUID PKs and FK constraints; `customers.tax_id` present and nullable. - [ ] `widget_type` CHECK rejects an unknown key. ### Task 2: Types for dashboards, widgets, report rows **Blocks:** 6, 12, 13, 14 · **Blocked by:** 1 **Files:** - Create: `packages/types/src/reports.ts` - Modify: `packages/types/src/index.ts` **Steps:** - [ ] Define `WidgetType` as a union of the 12 widget-library keys (same set as the CHECK in Task 1). - [ ] Define `DashboardObject`, `DashboardWidgetObject` (with typed `config`), `ReportPeriod`, and row types for each report (`Pcn874Report`, `RevenueLedgerRow`, `InvoiceReportRow`, `PaymentReportRow`, `ExpenseDeductibilityRow`, `ContractorWithholdingRow`, `TaxEstimateResult`). **Schema / Interfaces:** ```ts export type WidgetType = | 'revenue_kpi' | 'invoice_status_pipeline' | 'open_invoices_aging' | 'time_by_project' | 'time_by_team_member' | 'billable_vs_nonbillable' | 'customer_revenue' | 'expense_breakdown' | 'leads_funnel' | 'lead_pipeline_by_stage' | 'ticket_resolution_time' | 'ticket_volume_by_category'; export interface DashboardObject { id: string; tenantId: string; userId: string; name: string; isDefault: boolean; createdAt: string; updatedAt: string; widgets?: DashboardWidgetObject[]; } export interface DashboardWidgetObject { id: string; dashboardId: string; tenantId: string; widgetType: WidgetType; positionX: number; positionY: number; width: number; height: number; config: { title?: string; dateOverride?: ReportPeriod; filter?: Record; chartType?: string }; } export interface ReportPeriod { from: string; to: string } // ISO dates export interface InvoiceReportRow { invoiceNumber: string | null; issueDate: string; dueDate: string | null; customerName: string; status: string; subtotal: string; vatAmount: string; total: string; currency: string; balanceDue: string; daysOverdue: number; } export interface PaymentReportRow { paidAt: string; customerName: string; invoiceNumber: string | null; amount: string; currency: string; source: string; reference: string | null; receiptId: string | null; } ``` **Acceptance:** - [ ] `tsc` clean across `packages/types`; `WidgetType` exported and matches Task 1 CHECK set exactly. ### Task 3: Seed `reports:write` permission + role grants **Blocks:** 14, 16 · **Blocked by:** 1 **Files:** - Modify: `packages/auth/src/seed/permissions.ts` (the `seedPermissions` list under "Reports & Analytics") - Modify: `packages/auth/src/seed/roles.ts` (the `seedSystemRoles` / `role_permissions` grants) **Steps:** - [ ] Add `reports:write` to the permission catalog under the "Reports & Analytics" group (foundation seeds `reports:read` and `reports:export` but NOT `reports:write`, which this spec requires for dashboard create/edit). - [ ] Grant `reports:read` and `reports:write` to OWNER and ADMIN system roles; grant `reports:read` to MEMBER (view-only). Idempotent upsert into `permissions` + `role_permissions` via `seedPermissions`/`seedSystemRoles`. - [ ] Honor the spec permission table verbatim: View tax reports / Export reports / View analytics map to `reports:read`; Create/edit dashboards maps to `reports:write`. **Acceptance:** - [ ] After seed run, `permissions` contains `reports:write`; OWNER and ADMIN `role_permissions` include both `reports:read` and `reports:write`. - [ ] Re-running the seed is idempotent (no duplicate rows). ### Task 4: Cache key + financials_version read helper **Blocks:** 14, 15 · **Blocked by:** 1 **Files:** - Create: `packages/db/src/reports/cache.ts` - Modify: `packages/db/src/index.ts` (export) **Steps:** - [ ] Implement `getFinancialsVersion(kv, tenantId)` reading KV key `financials_version:{tenantId}` (default `0` when unset). - [ ] Implement `reportCacheKey(tenantId, reportType, period, version)` producing `{tenantId}:{reportType}:{period}:v{version}`. - [ ] Implement `withReportCache(cache, kv, tenantId, reportType, period, producer)` wrapping a producer in `cache.default` with 5-minute TTL, building the URL-keyed cache request from `reportCacheKey`. Document that the increment of `financials_version` is owned by invoices-core / expenses-module write paths; this helper only reads it so new versions naturally bypass stale entries (no cache-tag API on Workers). **Schema / Interfaces:** ```ts export async function getFinancialsVersion(kv: KVNamespace, tenantId: string): Promise; export function reportCacheKey(tenantId: string, reportType: string, period: string, version: number): string; export async function withReportCache( cache: Cache, kv: KVNamespace, tenantId: string, reportType: string, period: string, producer: () => Promise ): Promise; // TTL 300s ``` **Acceptance:** - [ ] Cache key string equals `{tenantId}:{reportType}:{period}:v{version}`. - [ ] When KV has no `financials_version:{tenantId}` key, version resolves to `0`. ### Task 5: Excel + AE-SQL infrastructure helpers **Blocks:** 14, 15 · **Blocked by:** — **Files:** - Create: `apps/zync-api/src/reports/xlsx.ts` - Create: `apps/zync-api/src/reports/ae.ts` - Modify: `apps/zync-api/package.json` (add `xlsx` dependency), `apps/zync-api/wrangler.toml` (document `CF_ANALYTICS_READ_TOKEN` secret), `apps/zync-api/src/env.ts` (`Env` type) **Steps:** - [ ] `xlsx.ts`: implement `buildSheet({ headers, rows, columnFormats })` and `workbookToResponse(sheets)` using the `xlsx` npm package. Set `Views: [{ RTL: true }]` on every sheet; Hebrew column headers passed by callers; date cells formatted `DD/MM/YYYY`; currency cells `₪#,##0.00`. Return a `Response` with `Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet` and a `Content-Disposition` filename. - [ ] `ae.ts`: implement `queryAnalyticsEngine(env, sql, params)` POSTing to the AE SQL HTTP API endpoint with `Authorization: Bearer ${env.CF_ANALYTICS_READ_TOKEN}`. This is the read path; never use the `ANALYTICS_ENGINE` binding for reads (write-only). Parse the JSON result rows. - [ ] Add `CF_ANALYTICS_READ_TOKEN` to wrangler secret docs and the `Env` type (must be created manually in the CF dashboard with `Account Analytics: Read`; not in wrangler OAuth scope). **Acceptance:** - [ ] Generated `.xlsx` opens with RTL sheet view and Hebrew headers; date cells render `DD/MM/YYYY`, currency `₪#,##0.00`. - [ ] `queryAnalyticsEngine` sends the bearer token from `CF_ANALYTICS_READ_TOKEN` and does not touch the `ANALYTICS_ENGINE` binding. ### Task 6: PCN874 VAT return query **Blocks:** 14, 17 · **Blocked by:** 1, 2 **Files:** - Create/Modify: `packages/db/src/queries/reports.ts` - Modify: `packages/db/src/queries/index.ts` **Steps:** - [ ] Implement `getPcn874(db, tenantId, period)` returning the five ITA fields plus drill-down detail. All functions take a `tenantQuery`-bound handle; no statement omits `tenant_id`. - [ ] **Output VAT (A,B):** `invoices` WHERE `status IN ('TAX_ISSUED','PAID')` AND `tax_issue_date` in period. A = `SUM(subtotal)`, B = `SUM(vat_amount)`. - [ ] **Exempt turnover (C):** `SUM(invoice_lines.line_total)` WHERE `invoice_lines.taxable = false` joined to in-period invoices. - [ ] **Input VAT (D):** `expenses` WHERE `status = 'COMPLETED'` AND `approval_status IN ('approved','not_required')` AND `vat_deductible = true` AND `expense_date` in period; D = `SUM(vat_amount * deduction_pct / 100.0)`. - [ ] **VAT payable (E):** `B - D` (computed in code). - [ ] Period resolver: accept `?period=YYYY-MM` (month) or `?quarter=YYYY-Qn`; default to most recently closed period. - [ ] Detail rows for drill-down: per contributing invoice (number, date, customer, amount, vat) and per contributing expense. **Acceptance:** - [ ] Returns `{ A, B, C, D, E, detail }` with invoice and expense arrays; `E === B - D`. - [ ] Pending/rejected expenses (`approval_status` not in approved/not_required) are excluded from D. ### Task 7: Revenue Ledger query **Blocks:** 14, 17 · **Blocked by:** 1, 2 **Files:** - Modify: `packages/db/src/queries/reports.ts` **Steps:** - [ ] Implement `getRevenueLedger(db, tenantId, { from, to, customerId, status })` over issued tax invoices. Columns: invoice number, date, customer name, customer tax ID (`customers.tax_id`), amount excl VAT (`subtotal`), VAT amount, total incl VAT, VAT rate (`vat_rate`). - [ ] Filter `status IN ('TAX_ISSUED','PAID')` (filterable to a single status). - [ ] **Sort by `invoice_number`** (sequential, gap-free — IL audit requirement), not by date. - [ ] Compute a totals row (sum of each numeric column) in code. **Acceptance:** - [ ] Rows sorted ascending by `invoice_number`; customer tax ID populated from `customers.tax_id`. - [ ] Totals row equals column sums. ### Task 8: Invoice Report + Payment Report queries **Blocks:** 14, 18 · **Blocked by:** 1, 2 **Files:** - Modify: `packages/db/src/queries/reports.ts` **Steps:** - [ ] Implement `getInvoiceReport(db, tenantId, { from, to, customerId, status })` covering **all** invoices in the period across all statuses (collections-oriented), per the spec SQL: ```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; ``` - [ ] Support the "Overdue" quick-filter as a derived predicate: `status NOT IN ('PAID','VOID') AND due_date < CURRENT_DATE`. Status filter accepts any of `DRAFT/SENT/APPROVED/TAX_ISSUED/PARTIALLY_PAID/PAID/WRITTEN_OFF/BAD_DEBT/VOID`. - [ ] Summary aggregates: count sent, count overdue, total billed, total outstanding (`SUM(balance_due)`); plus totals row (amount, VAT, total, balance due). - [ ] Implement `getPaymentReport(db, tenantId, { from, to, customerId, source, receiptIssued })` per the spec SQL: ```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; ``` - [ ] Map `source` (`manual`/`gateway`/`bank_transfer`/`auto_billing`) to the "Method" column. `receipt_issued = (receipt_id IS NOT NULL)`. Summary: total received, payment count, count pending receipt (`receipt_id IS NULL`); totals row sums Amount and subtotals by source. - [ ] **Ordering note:** `invoice_payments.receipt_id` and the `/receipts/:id` link are owned by spec 179 (receipt-document), not in this spec's `depends_on`. Reference `receipt_id` by name as the spec dictates; if the column is absent in the deployed schema at build time, the "Receipt issued" column degrades gracefully to always-empty (treat the missing column as `NULL`). **Acceptance:** - [ ] Invoice Report returns every status in range; `days_overdue` and `balance_due` computed; overdue quick-filter matches `status NOT IN ('PAID','VOID') AND due_date < today`. - [ ] Payment Report Method column maps from `source`; receipt-issued flag derived from `receipt_id`. ### Task 9: Expense Deductibility report query **Blocks:** 14, 18 · **Blocked by:** 1, 2 **Files:** - Modify: `packages/db/src/queries/reports.ts` **Steps:** - [ ] Implement `getExpenseDeductibility(db, tenantId, { from, to })` over approved expenses (`status='COMPLETED'` AND `approval_status IN ('approved','not_required')`, `expense_date` in range). - [ ] Detail columns: date, vendor (`vendor_name`), invoice number (`invoice_number`), category (`expense_category`), total (`amount`), VAT (`vat_amount`), deductible % (`deduction_pct`), deductible amount (`amount * deduction_pct / 100`), reasoning (`deduction_reasoning_he`). - [ ] Group by `expense_category` (the 8 fixed IL categories: `office`, `marketing`, `professional`, `vehicle`, `equipment`, `finance`, `welfare`, `exceptional` from `packages/types/src/expense-categories.ts`). Per-group summary: Total, Deductible (`SUM(amount * deduction_pct/100)`), Deductible VAT (`SUM(vat_amount * deduction_pct/100)`), plus a grand-total row. **Acceptance:** - [ ] Output grouped by the 8 categories with per-category Total / Deductible / Deductible-VAT and a grand total. - [ ] `rejected`/`pending` expenses excluded. ### Task 10: Contractor Withholding Summary query **Blocks:** 14, 19 · **Blocked by:** 1, 2 **Files:** - Modify: `packages/db/src/queries/reports.ts` **Steps:** - [ ] Implement `getContractorWithholding(db, tenantId, year)` over `payout_bills` WHERE `status='PAID'` AND `paid_at` within the calendar year, joined to `contractors`. - [ ] Per contractor: name, tax ID (`contractors.tax_id`), total amount paid (`SUM(amount)`), period covered (min `period_start` through max `period_end`), payment method (`payment_method`). **Acceptance:** - [ ] One row per contractor with total paid and period span for the given year; only `PAID` bills counted. ### Task 11: Income-Tax Prepayment Estimate query **Blocks:** 14, 19 · **Blocked by:** 1, 2 **Files:** - Modify: `packages/db/src/queries/reports.ts` **Steps:** - [ ] Implement `getTaxEstimate(db, tenantId, fiscalYear)` (read-only, **not a filing**). Compute: gross revenue = `SUM(invoices.total)` for tax-issued invoices in fiscal year; less deductible expenses (`SUM(amount * deduction_pct/100)` for approved expenses); less contractor payouts (`SUM(payout_bills.amount)` where PAID in year); estimated taxable income = gross minus deductible minus payouts. - [ ] Fiscal-year window: Jan–Dec or Apr–Mar per `tenants.settings` (configurable). - [ ] Apply tax rate from `tax_rates` via `getTaxRate(db, 'IL', taxType, fiscalYearEnd)` — **no hardcoded percentages**; read business type from `tenants.settings.business_type`: - corporate (חברה בע"מ): `getTaxRate(db,'IL','corporate_income', fiscalYearEnd)` single flat rate; estimated tax = taxable times rate. - personal (עוסק מורשה / עוסק פטור): query all `personal_bracket_1` through `personal_bracket_6` rows effective for the period (`tax_type LIKE 'personal_bracket_%'`, latest `effective_from <= date`, with `threshold_ils` ceilings); apply progressive brackets to estimated income; return an effective-rate band "~X%–Y%", not a single figure. - [ ] Return a read-only card payload plus a `disclaimer` flag (not filing advice). **Acceptance:** - [ ] No hardcoded tax rate; corporate path uses a single rate, personal path returns a bracketed effective-rate band. - [ ] Result carries a disclaimer marker. ### Task 12: Dashboard CRUD queries **Blocks:** 16, 23 · **Blocked by:** 1, 2 **Files:** - Create: `packages/db/src/queries/dashboards.ts` - Modify: `packages/db/src/queries/index.ts` **Steps:** - [ ] Implement `listDashboards(db, tenantId, userId)`, `createDashboard(db, tenantId, userId, { name, isDefault })`, `getDashboardWithWidgets(db, tenantId, userId, id)`, `updateDashboard(db, tenantId, userId, id, { name, isDefault })` (setting default unsets sibling defaults in a transaction), `deleteDashboard(db, tenantId, userId, id)`. - [ ] Implement `addWidget(db, tenantId, dashboardId, { widgetType, positionX, positionY, width, height, config })`, `updateWidget(db, tenantId, dashboardId, widgetId, patch)`, `removeWidget(db, tenantId, dashboardId, widgetId)`. - [ ] All scope by `tenant_id` AND `user_id` (dashboards are per-user); reject cross-user access (`no-raw-drizzle-from-routes`). **Acceptance:** - [ ] A user cannot read or mutate another user's dashboard (queries filter `user_id`). - [ ] Setting `is_default=true` clears any prior default for that user in the same transaction. ### Task 13: Widget data-query resolver (analytics) **Blocks:** 15 · **Blocked by:** 6, 7, 8, 9, 12 **Files:** - Create: `packages/db/src/queries/analytics.ts` - Modify: `packages/db/src/queries/index.ts` **Steps:** - [ ] Implement `resolveWidget(db, env, tenantId, widgetType, period, filters)` returning raw aggregated data per the widget library: - `revenue_kpi`: `invoices` total paid + trend sparkline. - `invoice_status_pipeline`: `invoices` grouped by `status` (horizontal stacked bar buckets). - `open_invoices_aging`: `invoices` bucketed by days outstanding (0-30, 30-60, 60-90, 90+) from `balance_due`/`due_date`. - `time_by_project`: `SUM(time_entries.duration_seconds)` grouped by `project_id`. - `time_by_team_member`: grouped by `user_id`. - `billable_vs_nonbillable`: grouped by `time_entries.billable` (donut). - `customer_revenue`: `invoices` grouped by `customer_id` (bar + table). - `expense_breakdown`: `expenses` grouped by `expense_category` (donut). - `leads_funnel`: AE SQL read via `queryAnalyticsEngine` (Task 5) for the 4 funnel steps (`catalog_view`, `lead_captured`, `proposal_accepted`, `invoice_paid`); conversion per step is `step_N/step_(N-1)`, never divided by step_1. - `lead_pipeline_by_stage`: `leads` grouped by `stage`. - `ticket_resolution_time`: `tickets` histogram of `resolved_at - created_at` (avg days open). - `ticket_volume_by_category`: `tickets` grouped by `category`. - [ ] Apply `period` (from/to) to each widget; honor per-widget `filters` (project/customer/team-member). - [ ] The KB-article-views widget is deferred per spec — not implemented. **Acceptance:** - [ ] Each of the 12 widget keys returns a typed aggregate payload; `leads_funnel` uses the AE SQL HTTP read path and step-relative denominators. - [ ] Date-range filter applied to every widget. ### Task 14: Tax-report API routes (JSON + xlsx) **Blocks:** 17, 18, 19 · **Blocked by:** 3, 4, 5, 6, 7, 8, 9, 10, 11 **Files:** - Create: `apps/zync-api/src/reports/index.ts` (Hono group) - Create: `apps/zync-api/src/reports/exports.ts` (xlsx builders per report) - Modify: `apps/zync-api/src/index.ts` (mount group) **Steps:** - [ ] Mount the group at `/api/reports` behind `authMiddleware` then `requireModuleEnabled('reports')`; each route runs `requirePermission('reports:read')`. - [ ] JSON routes (wrap each in `withReportCache`): `GET /api/reports/vat` (`?period=`/`?quarter=`), `GET /api/reports/revenue` (`?from=&to=&customer=&status=`), `GET /api/reports/invoices` (`?from=&to=&customer=&status=`), `GET /api/reports/payments` (`?from=&to=&customer=&source=&receipt=`), `GET /api/reports/expenses` (`?from=&to=`), `GET /api/reports/contractors` (`?year=`), `GET /api/reports/tax-estimate` (`?year=`). - [ ] XLSX routes: `GET /api/reports/vat/xlsx`, `/revenue/xlsx`, `/invoices/xlsx`, `/payments/xlsx`, `/expenses/xlsx`, `/contractors/xlsx` — build via `xlsx.ts`. PCN874 xlsx: official ITA layout sheet 1 (RTL, Hebrew headers) + sheet 2 transaction detail. Contractor xlsx: one sheet per contractor (Mas 856 confirmation-letter layout). - [ ] All routes Zod-validate query params (`require-zod-validation-in-routes`); no raw Drizzle in routes (`no-raw-drizzle-from-routes`). **Schema / Interfaces:** ``` GET /api/reports/vat GET /api/reports/vat/xlsx GET /api/reports/revenue GET /api/reports/revenue/xlsx GET /api/reports/invoices GET /api/reports/invoices/xlsx GET /api/reports/payments GET /api/reports/payments/xlsx GET /api/reports/expenses GET /api/reports/expenses/xlsx GET /api/reports/contractors GET /api/reports/contractors/xlsx GET /api/reports/tax-estimate ``` **Acceptance:** - [ ] All 7 JSON + 6 xlsx endpoints respond; each gated on `reports:read`; cache key includes `financials_version`. - [ ] xlsx downloads are RTL with Hebrew headers, `DD/MM/YYYY` dates, `₪#,##0.00` currency. ### Task 15: Analytics query API route **Blocks:** 20, 21 · **Blocked by:** 4, 5, 13 **Files:** - Create: `apps/zync-api/src/analytics/index.ts` - Modify: `apps/zync-api/src/index.ts` **Steps:** - [ ] Mount `GET /api/analytics/query` behind `authMiddleware` then `requireModuleEnabled('reports')` then `requirePermission('reports:read')`. - [ ] Validate `?widget=&from=&to=&filters=` with Zod; dispatch to `resolveWidget` (Task 13); wrap in `withReportCache` keyed by widget+period. - [ ] Return raw aggregated data only (charts render client-side). **Acceptance:** - [ ] Returns aggregated data for any valid `widget` key; rejects an unknown widget with 400; gated on `reports:read`. ### Task 16: Dashboard CRUD API routes **Blocks:** 22 · **Blocked by:** 3, 12 **Files:** - Create: `apps/zync-api/src/dashboards/index.ts` - Modify: `apps/zync-api/src/index.ts` **Steps:** - [ ] Mount `/api/dashboards` behind `authMiddleware` then `requireModuleEnabled('reports')`. Read routes gate on `reports:read`; mutating routes gate on `reports:write`. - [ ] Routes: `GET /api/dashboards`, `POST /api/dashboards`, `GET /api/dashboards/:id`, `PATCH /api/dashboards/:id`, `DELETE /api/dashboards/:id`, `POST /api/dashboards/:id/widgets`, `PATCH /api/dashboards/:id/widgets/:wid`, `DELETE /api/dashboards/:id/widgets/:wid`. - [ ] Zod-validate bodies (`widget_type` against `WidgetType`, grid ints, config shape); enforce per-user ownership in the query layer. **Schema / Interfaces:** ``` GET /api/dashboards POST /api/dashboards GET /api/dashboards/:id PATCH /api/dashboards/:id DELETE /api/dashboards/:id POST /api/dashboards/:id/widgets PATCH /api/dashboards/:id/widgets/:wid DELETE /api/dashboards/:id/widgets/:wid ``` **Acceptance:** - [ ] Create/edit/delete dashboard + widgets require `reports:write`; list/get require `reports:read`. - [ ] Cross-user dashboard access returns 404. ### Task 17: PCN874 + Revenue Ledger UI **Blocks:** — · **Blocked by:** 14 **Files:** - Create: `apps/zync-app/src/reports/TaxReportsPage.tsx`, `apps/zync-app/src/reports/Pcn874Tab.tsx`, `apps/zync-app/src/reports/RevenueLedgerTab.tsx`, `apps/zync-app/src/reports/hooks.ts` - Modify: `apps/zync-app/src/router.tsx` (route `/reports/tax`) **Steps:** - [ ] Build `/reports/tax` with `Tabs` for the statutory reports. PCN874 tab: month/quarter `Select` (default most-recently-closed period); 5 ITA field cards (A–E) with Hebrew labels; click any field total to open an inline drill-down `DataTable` of contributing invoices/expenses. "Export PCN874" button hits `/api/reports/vat/xlsx`. - [ ] Revenue Ledger tab: date-range + customer + status filters; `DataTable` sorted by invoice number; totals row; Excel + CSV export buttons. - [ ] Use `useDirection`/`LocaleProvider` for RTL; numbers via `Intl.NumberFormat(locale,{style:'currency',currency:'ILS'})`; tables use logical column order with `dir` handling visual reversal. **Acceptance:** - [ ] PCN874 cards show A–E; drill-down opens contributing rows; export downloads RTL Hebrew xlsx. - [ ] Revenue Ledger sorted by invoice number with totals row; RTL layout for `he-IL`. ### Task 18: Invoice Report + Payment Report + Expense Deductibility UI **Blocks:** — · **Blocked by:** 14 **Files:** - Create: `apps/zync-app/src/reports/InvoiceReportPage.tsx`, `apps/zync-app/src/reports/PaymentReportPage.tsx`, `apps/zync-app/src/reports/ExpenseDeductibilityTab.tsx` - Modify: `apps/zync-app/src/router.tsx` (routes `/reports/invoices`, `/reports/payments`) **Steps:** - [ ] `/reports/invoices`: summary `StatCard`s (count sent, count overdue, total billed, total outstanding); filters (date range, customer, status) + "Overdue" quick-filter chip; `DataTable` columns Invoice-number/Issue/Due/Customer/Status/Amount/VAT/Total/Balance/Days-overdue; default sort issue-date desc, column sort on headers; totals row; row click navigates to `/invoices/:id`; Excel + CSV export. - [ ] `/reports/payments`: summary `StatCard`s (total received, payment count, count pending receipt); filters (date range, customer, source, receipt-issued); `DataTable` columns Date/Customer/Invoice-number/Amount/Method(source)/Reference/Receipt(check); totals row + per-source subtotals; row click navigates to parent `/invoices/:id`; receipt link `/receipts/:id` when `receipt_id` set (degrade gracefully if the column is absent); Excel + CSV export. - [ ] Expense Deductibility tab (on `/reports/tax`): category-grouped `DataTable` + summary table (Total / Deductible / Deductible VAT per category + grand total); Excel export (Virtuac layout). **Acceptance:** - [ ] Invoice Report shows all statuses with overdue chip; Payment Report Method maps from source and receipt check from receipt_id; both export RTL xlsx + CSV. - [ ] Expense Deductibility grouped by the 8 categories with summary totals. ### Task 19: Contractor Withholding + Tax Estimate UI **Blocks:** — · **Blocked by:** 14 **Files:** - Create: `apps/zync-app/src/reports/ContractorWithholdingTab.tsx`, `apps/zync-app/src/reports/TaxEstimateTab.tsx` **Steps:** - [ ] Contractor Withholding tab: year `Select`; per-contractor cards/table (name, tax ID, total paid, period covered, payment method); "Export per contractor" hits `/api/reports/contractors/xlsx`. - [ ] Tax Estimate tab: read-only cards (gross revenue, less deductible expenses, less contractor payouts, estimated taxable income, estimated tax). For personal business types show the effective-rate band "~X%–Y%". Show a prominent **"Not filing advice"** disclaimer. **Acceptance:** - [ ] Contractor export produces one sheet per contractor (Mas 856 layout). - [ ] Tax Estimate cards are read-only; personal types show a rate band; disclaimer visible. ### Task 20: Recharts accessible chart components (a11y + RTL) **Blocks:** 22 · **Blocked by:** 15 **Files:** - Create: `apps/zync-app/src/analytics/charts/AccessibleChart.tsx`, `apps/zync-app/src/analytics/charts/patterns.tsx`, `apps/zync-app/src/analytics/charts/index.ts` **Steps:** - [ ] Build reusable Recharts wrappers (bar, stacked-bar, donut, funnel, histogram, KPI sparkline). Each chart container has `role="img"` and an `aria-label` generated from the top-5 data points with a trailing "and N more values"; sparkline/KPI charts use a single-sentence label ("Revenue trend: up 18% over last 30 days"). - [ ] Each widget renders a visually-hidden `
` with `View data as table` and an accessible `` fallback (caption = chart title); CSS collapses to a "View data as table" toggle for sighted keyboard users and expands fully for screen readers. - [ ] **Color-only prohibition (WCAG 1.4.1):** series distinguished by SVG `` hatching and/or on-bar/on-slice labels in addition to legend color — never color alone. - [ ] RTL: `isRtl = locale === 'he-IL'` then `YAxis orientation={isRtl?'right':'left'}`, `XAxis orientation="bottom"`, `Tooltip position={{x: isRtl?'left':'right'}}`; `stackOffset="expand"` for stacked bars (direction-agnostic); line-chart dots are coordinate-based (no RTL adjustment). - [ ] Honor `prefers-reduced-motion`: disable chart entrance/transition animations when set. **Acceptance:** - [ ] Every chart exposes `role="img"` + generated `aria-label` and a keyboard-reachable visually-hidden data-table fallback. - [ ] Series remain distinguishable in grayscale (pattern fills / labels); RTL axis flip applied for `he-IL`; animations suppressed under `prefers-reduced-motion`. ### Task 21: Widget components wired to analytics API **Blocks:** 22 · **Blocked by:** 15, 20 **Files:** - Create: `apps/zync-app/src/analytics/widgets/` (one component per widget key), `apps/zync-app/src/analytics/widgets/registry.ts`, `apps/zync-app/src/analytics/hooks.ts` **Steps:** - [ ] Implement the 12 widget components, each fetching `GET /api/analytics/query?widget=&from=&to=&filters=` via react-query and rendering through the `AccessibleChart` wrappers (Task 20). `revenue_kpi` and open-invoices are metric cards; pipeline/aging/time/customer are bars; billable/expense are donuts; `leads_funnel` is a funnel with step-relative conversion %; `lead_pipeline_by_stage` is kanban-style counts; ticket widgets are histogram + bar. - [ ] `registry.ts` maps `WidgetType` to component + default size + available chart types. - [ ] KB article views widget intentionally omitted (deferred). **Acceptance:** - [ ] Each widget renders its chart from the analytics API; funnel shows `step_N/step_(N-1)` percentages. ### Task 22: Dashboard grid page + widget config drawer **Blocks:** — · **Blocked by:** 16, 21 **Files:** - Create: `apps/zync-app/src/analytics/DashboardPage.tsx`, `apps/zync-app/src/analytics/DashboardGrid.tsx`, `apps/zync-app/src/analytics/WidgetConfigDrawer.tsx`, `apps/zync-app/src/analytics/AddWidgetDialog.tsx` - Modify: `apps/zync-app/src/router.tsx` (route `/reports/analytics`) **Steps:** - [ ] Build `/reports/analytics`: dashboard `Select` (switch between the user's dashboards), shared period control (Last 7d/30d/90d/This month/This quarter/Custom) applied to all widgets, an "Add widget" button, and an overflow menu (rename, set default, delete). - [ ] 12-column grid; widgets snap to integer grid positions; persist position/size via `PATCH /api/dashboards/:id/widgets/:wid`. - [ ] `AddWidgetDialog` lists the widget library; `WidgetConfigDrawer` (click widget to open settings) edits title override, per-widget date-range override, filter (project/customer/team-member), chart-type toggle where multiple types exist; persists to widget `config`. - [ ] On first load with no dashboards, fall back to the seeded "Overview" default (Task 23). **Acceptance:** - [ ] User can create/rename/delete dashboards, add/configure/remove/reposition widgets; period control updates all widgets; layout persists per user. ### Task 23: Default "Overview" dashboard seed on tenant setup **Blocks:** — · **Blocked by:** 12 **Files:** - Create: `packages/db/src/seed/default-dashboard.ts` - Modify: tenant-provisioning hook (the tenant-setup path that runs `seedTenantModules`) to call it **Steps:** - [ ] Implement `seedDefaultDashboard(db, tenantId, ownerUserId)` creating one `dashboards` row `{ name: 'Overview', is_default: true }` for the tenant owner plus a starter widget set (`revenue_kpi`, `open_invoices_aging`, `time_by_project`, `invoice_status_pipeline`, `leads_funnel`) at default grid positions. - [ ] Wire it into the tenant-setup flow so every new tenant's owner gets an Overview dashboard. Idempotent (skip if owner already has a default). **Acceptance:** - [ ] A newly provisioned tenant's owner has exactly one default "Overview" dashboard with the starter widgets. ### Task 24: Query + route tests **Blocks:** — · **Blocked by:** 14, 15, 16 **Files:** - Create: `packages/db/src/queries/reports.test.ts`, `packages/db/src/queries/dashboards.test.ts`, `apps/zync-api/src/reports/reports.routes.test.ts` **Steps:** - [ ] PCN874: assert `E = B - D`, and that pending/rejected expenses are excluded from D. - [ ] Revenue Ledger: assert ascending sort by invoice number and totals-row sums. - [ ] Invoice Report: assert `days_overdue`/`balance_due` math and the overdue predicate; Payment Report: assert source-to-Method mapping and receipt-issued flag. - [ ] Tax estimate: assert no hardcoded rate (rate sourced via `getTaxRate`/`personal_bracket_*`) and corporate vs personal branching. - [ ] Dashboards: assert per-user isolation (cross-user access denied) and single-default invariant. - [ ] Routes: assert `reports:read` gating on read/export, `reports:write` gating on dashboard mutation, and that cache keys include `financials_version`. **Acceptance:** - [ ] All tests pass; `tsc`/lint clean across `packages/db`, `packages/types`, `apps/zync-api`, `apps/zync-app` (`no-raw-drizzle-from-routes`, `require-zod-validation-in-routes`, `no-hardcoded-colors`, `no-hardcoded-spacing`).