# Expenses Module — Implementation Plan **Spec:** docs/specs/2026-05-30-expenses-module.md · **Slug:** expenses-module · **Wave:** 4 **Depends on:** ai-assistant, foundation-auth-rbac, projects-module, system-i18n ## Goal Receipt-based expense tracking with Claude Vision OCR and AI-driven Israeli tax-deductibility evaluation. Staff upload receipts (or forward via email/WhatsApp/Telegram); an async Cloudflare Queue pipeline extracts vendor/amount/VAT fields, normalizes to ILS, classifies into one of 8 fixed Israeli tax categories, and computes a deduction percentage with bilingual reasoning. The module also delivers per-diem logging, expense/VAT(PCN874)/vendor reports with RTL Hebrew Excel export, a corrections audit trail, and hosts the mileage-logbook navigation surface. ## Architecture - **DB (`@zync/db`):** new `expenses` and `expense_corrections` tables (Drizzle schema), plus a new `tenant_settings` table (this module is the earliest consumer in build order; downstream specs `israeli-tax-reports` and `settings-module` ALTER it). All access is tenant-scoped via the upstream `tenantQuery(db, tenantId)` helper and `createDb(env)` / `DB` type. FK `project_id → projects(id)`, `created_by → users(id)`, `tenant_id → tenants(id)`. - **API (`zync-api`, Hono):** `/api/expenses/*` routes guarded by `authMiddleware`, `requireModuleEnabled('expenses')`, and `requirePermission(...)`. Zod validation on every body (`require-zod-validation-in-routes`). Routes call service functions in `@zync/expenses`; no raw Drizzle in route bodies (`no-raw-drizzle-from-routes`). - **AI:** OCR + tax evaluation go through `callAI({ tenantId, userId, useCase, messages, entityType:'expense', entityId })` from `@zync/ai`, using use cases `expense_ocr` (vision, model must be `is_vision_capable`) and `expense_tax_eval`. Image is passed as an `AIContentBlock` of `type:'image'` (base64). `callAI` handles model routing, fallback, credit accounting, and `ai_usage_log` writes internally. - **Queue:** Upload handler enqueues `expense.process` onto the shared `QUEUE` binding; a consumer runs the OCR→eval→persist pipeline, fires `expense.processed`/`expense.failed` webhook events onto the `webhook.deliver` queue, and pushes a realtime `{op:'expense.updated'}` over `TenantRealtimeDO` via `pushOverWebSocket`. - **Storage:** Original receipt files in R2 (`STORAGE` binding) at key `{tenantId}/expenses/{expenseId}/{filename}`. Signed GET URLs (30-min TTL) served via `/api/expenses/:id/file`. - **i18n:** All UI strings via `t('...')` (`@zync/types` / i18n `translations`, `supportedLocales`); RTL via `useDirection`. Tax category labels come from the fixed `EXPENSE_CATEGORIES` constant (Hebrew + English). - **App (`zync-app`, Vite+React):** `/expenses` page with tabs (All | Needs Review | Recurring | Mileage), virtualized list (TanStack Virtual), upload dropzone, detail Sheet, per-diem slide-over, and reports views. Consumes `@zync/ui` primitives (`DataTable`, `Sheet`, `Badge`, `StatCard`, `Button`, `Dialog`, `Progress`, `EmptyState`, `Toaster`/`toast`). - **Rate limiting:** `RATE_LIMITER_EXPENSE_UPLOAD` (CF native RateLimiter) — 10 uploads/min per user, enforced via `rateLimit(...)`. - **Out of scope (owned downstream, referenced only):** `vendors`/`vendor_id` (vendors-suppliers), `mileage_trips` + `POST /api/mileage` (mileage-logbook spec 166), `tenant_settings.advance_tax_rate_pct` (israeli-tax-reports spec 171). Vendor analysis groups by normalized `vendor_name` until `vendor_id` linkage lands. The Mileage tab renders the trip list when the table exists and otherwise shows an empty state. ## Tech Stack - **Packages:** `@zync/db` (Drizzle schema + migration), new `@zync/expenses` (service layer + types re-export), `@zync/types` (EXPENSE_CATEGORIES constant, Expense types), `@zync/ai` (consumed), `@zync/ui` (consumed), `@zync/auth` (middleware/permissions), `@zync/notifications` (webhook emit helper). - **Apps:** `zync-api` (Hono routes + queue consumer + email-intake Worker handler), `zync-app` (React pages/components). - **Libraries:** Drizzle ORM, Zod, TanStack Query + TanStack Virtual, `exceljs` (RTL Excel export), `@anthropic-ai/sdk` (via `@zync/ai` only). - **Cloudflare bindings:** `DB`/Hyperdrive (Neon Postgres), `STORAGE` (R2), `QUEUE` (Queues), `RATE_LIMITER_EXPENSE_UPLOAD` (RateLimiter), `DO_REALTIME` (TenantRealtimeDO), `ANALYTICS_ENGINE` (optional metrics). ## Wave Plan | Sub-wave | Tasks | Files touched | Parallelizable? | |----------|-------|---------------|-----------------| | E1 — Schema & constants | 1, 2 | `@zync/db` schema + migration, `@zync/types` | Task 2 parallel with Task 1 | | E2 — Service layer | 3, 4, 5 | `@zync/expenses` services | After E1; 3→4→5 sequential on shared module | | E3 — Pipeline & intake | 6, 7, 8 | queue consumer, webhook emit, email/IM intake | After E2; parallel among themselves | | E4 — API routes | 9, 10, 11, 12 | `zync-api` routes | After E2/E3; 9 first, then 10/11/12 parallel | | E5 — Reports | 13, 14 | report services + Excel | After E2; parallel with E4 | | E6 — Frontend | 15, 16, 17, 18, 19, 20 | `zync-app` | After E4; 15 first, rest parallel | | E7 — Wiring & config | 21, 22 | wrangler config, module manifest, i18n strings | After E4/E6 | ## Tasks ### Task 1: Database schema — `expenses`, `expense_corrections`, `tenant_settings` **Blocks:** 3, 4, 5, 6, 13, 14 · **Blocked by:** — **Files:** - Modify: `packages/db/src/schema/expenses.ts` (create) - Modify: `packages/db/src/schema/tenant-settings.ts` (create) - Modify: `packages/db/src/schema/index.ts` (export new tables) - Create: `packages/db/migrations/####_expenses_module.sql` **Steps:** - [ ] Define `expenses` and `expense_corrections` Drizzle tables matching the DDL below (including the per-diem columns folded into the base table — do not emit them as a later ALTER). - [ ] `ALTER TABLE tenant_settings ADD COLUMN IF NOT EXISTS` the expense-config columns (base table owned by `foundation-auth-rbac` — never re-create it here); add a header comment noting `israeli-tax-reports` adds `advance_tax_rate_pct` and other settings specs extend it further. - [ ] Add indexes: `idx_expenses_report` per spec, plus a cursor-pagination index and a corrections lookup index. - [ ] Export `expenses`, `expenseCorrections`, `tenantSettings` from the db package barrel and register in the Drizzle schema object. - [ ] Hand-write the SQL migration (canonical Postgres) so it can run against Neon via Hyperdrive. **Schema / Interfaces:** ```sql CREATE TABLE expenses ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE, project_id UUID REFERENCES projects(id) ON DELETE SET NULL, created_by UUID NOT NULL REFERENCES users(id), -- File r2_key TEXT NOT NULL, file_name TEXT NOT NULL, file_type TEXT NOT NULL CHECK (file_type IN ('pdf','jpg','png','heic')), file_size_bytes INTEGER NOT NULL, -- OCR raw capture (nullable until processed) vendor_name TEXT, vendor_tax_id TEXT, invoice_number TEXT, invoice_total NUMERIC(12,2), vat_amount NUMERIC(12,2), currency TEXT NOT NULL DEFAULT 'ILS', allocation_number TEXT, raw_ocr_text TEXT, -- Canonical accounting fields expense_date DATE, amount NUMERIC(12,2), vat_deductible BOOLEAN NOT NULL DEFAULT true, -- Processing status status TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','PROCESSING','COMPLETED','FAILED','NEEDS_REVIEW')), ocr_confidence NUMERIC(3,2), processing_started_at TIMESTAMPTZ, processed_at TIMESTAMPTZ, processing_error TEXT, -- AI tax evaluation (nullable until evaluated) expense_category TEXT CHECK (expense_category IN ('office','marketing','professional','vehicle', 'equipment','finance','welfare','exceptional','travel')), deduction_pct INTEGER CHECK (deduction_pct BETWEEN 0 AND 100), deduction_confidence NUMERIC(3,2), deduction_reasoning_he TEXT, deduction_reasoning_en TEXT, evaluated_at TIMESTAMPTZ, -- Per-diem (receipt-less daily allowance) is_per_diem BOOLEAN NOT NULL DEFAULT false, per_diem_days NUMERIC(5,2), per_diem_rate_ils NUMERIC(10,2), -- Source tracking source TEXT NOT NULL DEFAULT 'upload' CHECK (source IN ('upload','email','whatsapp','telegram')), source_metadata JSONB, notes TEXT, deleted_at TIMESTAMPTZ, -- soft delete created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE TABLE expense_corrections ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), expense_id UUID NOT NULL REFERENCES expenses(id) ON DELETE CASCADE, user_id UUID NOT NULL REFERENCES users(id), field_name TEXT NOT NULL, original_value TEXT, corrected_value TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- tenant_settings base table (id + unique tenant_id FK + timestamps) is owned by -- foundation-auth-rbac. This module owns ALL expense-config columns (the canonical store -- for the whole expense module — read by the AI deductibility eval, per-diem form, VAT -- report, the approval workflow (spec 61), and surfaced by /settings/expenses). default_currency -- is NOT here — it is the tenants.default_currency scalar (system-i18n). ALTER TABLE tenant_settings -- VAT filing period (monthly/bimonthly) — the /settings/expenses "VAT Reporting" control writes this: ADD COLUMN IF NOT EXISTS filing_cadence TEXT NOT NULL DEFAULT 'monthly' CHECK (filing_cadence IN ('monthly','bimonthly')), ADD COLUMN IF NOT EXISTS tax_basis TEXT NOT NULL DEFAULT 'cash' CHECK (tax_basis IN ('cash','accrual')), ADD COLUMN IF NOT EXISTS business_category TEXT, -- one of 17 industries (AI context) ADD COLUMN IF NOT EXISTS per_diem_rates JSONB NOT NULL DEFAULT '{ "domestic_full_day": 100, "domestic_half_day": 50, "international_full_day": 250, "international_half_day": 125 }'::jsonb, -- /settings/expenses configuration columns (surfaced by expense-settings-ui, spec 62): ADD COLUMN IF NOT EXISTS expense_default_category TEXT NOT NULL DEFAULT 'other', ADD COLUMN IF NOT EXISTS expense_auto_approve_threshold_ils INTEGER NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS expense_receipt_reminder_enabled BOOLEAN NOT NULL DEFAULT false, ADD COLUMN IF NOT EXISTS expense_receipt_reminder_days INTEGER NOT NULL DEFAULT 7, -- Approval-workflow columns (spec 61 reads these; Business+ gate): ADD COLUMN IF NOT EXISTS expense_approval_threshold_ils INTEGER NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS expense_approver_role TEXT NOT NULL DEFAULT 'any_admin'; -- 'any_admin' | CREATE INDEX idx_expenses_report ON expenses (tenant_id, status, expense_date); CREATE INDEX idx_expenses_cursor ON expenses (tenant_id, created_at DESC, id DESC); CREATE INDEX idx_expenses_project ON expenses (project_id); CREATE INDEX idx_expense_corrections_expense ON expense_corrections (expense_id); ``` **Acceptance:** - [ ] Migration applies cleanly to a Neon branch; `\d expenses` shows all columns/constraints/indexes above. - [ ] Every FK is UUID→UUID; every enum is an inline `CHECK`; booleans are `BOOLEAN`; `source_metadata`/`per_diem_rates` are `JSONB`. - [ ] `tenant_settings` base table is NOT created here (owned by foundation-auth-rbac); this module only `ALTER ... ADD COLUMN IF NOT EXISTS` its expense-config columns. The Drizzle table extension is exported from `@zync/db`. ### Task 2: Expense category constants & shared types **Blocks:** 3, 4, 6, 16 · **Blocked by:** — **Files:** - Create: `packages/types/src/expense-categories.ts` - Create: `packages/types/src/expenses.ts` - Create: `packages/types/src/expense-settings.ts` - Modify: `packages/types/src/index.ts` (re-export) **Steps:** - [ ] Define `EXPENSE_CATEGORIES` as a readonly array of `{ id, he, en, typicalDeduction }` for all 8 categories plus `travel` (per-diem). Derive `ExpenseCategoryId` union type from it. Also export `EXPENSE_CATEGORY_IDS` = the 8 settings-selectable ids (`EXPENSE_CATEGORIES.map(c => c.id)`, excludes `travel`) for the settings schema. - [ ] Define `Expense`, `ExpenseCorrection`, `ExpenseStatus`, `ExpenseSource`, `ExpenseListResponse`, `ExpenseSettings`, `PerDiemRates` TypeScript types. - [ ] In `expense-settings.ts`, author the **single canonical** settings contract consumed by both the wave-4 settings route (Task 11) and the wave-10 `expense-settings-ui` page: `FilingCadence`, `EXPENSE_SETTINGS_DEFAULTS`, `expenseSettingsSchema` (Zod, `.strict()`), `mergeExpenseSettings`. expense-settings-ui imports these — it does NOT redefine them (no duplicate `ExpenseSettings`/schema export in `@zync/types`). - [ ] Provide `EXPENSE_STATUS_BADGES` and `confidenceTier(c: number)` helper mapping to badge color tiers (≥0.9 green, ≥0.7 yellow, else red) used by both API serializers and UI. **Schema / Interfaces:** ```ts export const EXPENSE_CATEGORIES = [ { id: 'office', he: 'אחזקת משרד', en: 'Office Maintenance', typicalDeduction: 100 }, { id: 'marketing', he: 'שיווק ופרסום', en: 'Marketing & Advertising', typicalDeduction: 100 }, { id: 'professional', he: 'שירותים מקצועיים', en: 'Professional Services', typicalDeduction: 100 }, { id: 'vehicle', he: 'רכב ונסיעות', en: 'Vehicle & Travel', typicalDeduction: 45 }, { id: 'equipment', he: 'ציוד ותוכנה', en: 'Equipment & Software', typicalDeduction: 100 }, { id: 'finance', he: 'פיננסיות וביטוח', en: 'Finance & Insurance', typicalDeduction: 100 }, { id: 'welfare', he: 'פיתוח ורווחה', en: 'Development & Welfare', typicalDeduction: 100 }, { id: 'exceptional', he: 'הוצאות חריגות', en: 'Exceptional Expenses', typicalDeduction: 0 }, ] as const; export type ExpenseCategoryId = | (typeof EXPENSE_CATEGORIES)[number]['id'] | 'travel'; export type ExpenseStatus = 'PENDING'|'PROCESSING'|'COMPLETED'|'FAILED'|'NEEDS_REVIEW'; export type ExpenseSource = 'upload'|'email'|'whatsapp'|'telegram'; export interface PerDiemRates { domestic_full_day: number; domestic_half_day: number; international_full_day: number; international_half_day: number; } // Canonical @zync/types shape for tenant_settings expense config columns. // snake_case to match the column names + sibling settings contracts // (invoices-core default_payment_terms_days). Currency is NOT here — // it lives on tenants.default_currency (scalar). expense-settings-ui imports // this type; it does not redefine it. export interface ExpenseSettings { filing_cadence: 'monthly'|'bimonthly'; tax_basis: 'cash'|'accrual'; business_category: string|null; per_diem_rates: PerDiemRates; expense_default_category: string; expense_auto_approve_threshold_ils: number; expense_receipt_reminder_enabled: boolean; expense_receipt_reminder_days: number; expense_approval_threshold_ils: number; expense_approver_role: string; } // ── packages/types/src/expense-settings.ts ────────────────────────────── // Canonical settings contract. Imported by the wave-4 route (Task 11) AND // the wave-10 expense-settings-ui page. No duplicate ExpenseSettings/schema // is defined anywhere else in @zync/types. // import { z } from 'zod'; // import { EXPENSE_CATEGORY_IDS } from './expense-categories'; // import type { ExpenseSettings, PerDiemRates } from './expenses'; export type FilingCadence = 'monthly'|'bimonthly'; // Full-row defaults (the tenant_settings column DEFAULTs) — what // getExpenseSettings returns for a fresh tenant. Typed as the full ExpenseSettings. export const EXPENSE_SETTINGS_DEFAULTS: ExpenseSettings = { filing_cadence: 'monthly', tax_basis: 'cash', business_category: null, per_diem_rates: { domestic_full_day: 100, domestic_half_day: 50, international_full_day: 250, international_half_day: 125, }, expense_default_category: 'other', expense_auto_approve_threshold_ils: 0, expense_receipt_reminder_enabled: false, expense_receipt_reminder_days: 7, expense_approval_threshold_ils: 0, expense_approver_role: 'any_admin', }; // Page-editable PATCH subset submitted by /settings/expenses. `.strict()` rejects // unknown keys. tax_basis + business_category are NOT page-editable (set at // onboarding / AI context), so they are intentionally absent here. export const expenseSettingsSchema = z.object({ expense_default_category: z.enum([...EXPENSE_CATEGORY_IDS, 'other'] as [string, ...string[]]), expense_auto_approve_threshold_ils: z.number().int().min(0), filing_cadence: z.enum(['monthly', 'bimonthly']), expense_receipt_reminder_enabled: z.boolean(), expense_receipt_reminder_days: z.number().int().min(1).max(365), expense_approval_threshold_ils: z.number().int().min(0), expense_approver_role: z.union([z.literal('any_admin'), z.string().uuid()]), per_diem_rates: z.object({ domestic_full_day: z.number().min(0), domestic_half_day: z.number().min(0), international_full_day: z.number().min(0), international_half_day: z.number().min(0), }), }).strict(); export type ExpenseSettingsPatch = z.infer; // Partial-compatible // Merge a (possibly partial/null) stored row over the full defaults. export function mergeExpenseSettings(stored: Partial | null | undefined): ExpenseSettings { return { ...EXPENSE_SETTINGS_DEFAULTS, ...(stored ?? {}), per_diem_rates: { ...EXPENSE_SETTINGS_DEFAULTS.per_diem_rates, ...(stored?.per_diem_rates ?? {}) }, }; } // ──────────────────────────────────────────────────────────────────────── export interface Expense { id: string; tenantId: string; projectId: string|null; createdBy: string; r2Key: string; fileName: string; fileType: 'pdf'|'jpg'|'png'|'heic'; fileSizeBytes: number; vendorName: string|null; vendorTaxId: string|null; invoiceNumber: string|null; invoiceTotal: string|null; vatAmount: string|null; currency: string; allocationNumber: string|null; expenseDate: string|null; amount: string|null; vatDeductible: boolean; status: ExpenseStatus; ocrConfidence: string|null; expenseCategory: ExpenseCategoryId|null; deductionPct: number|null; deductionConfidence: string|null; deductionReasoningHe: string|null; deductionReasoningEn: string|null; isPerDiem: boolean; perDiemDays: string|null; perDiemRateIls: string|null; source: ExpenseSource; sourceMetadata: Record|null; notes: string|null; createdAt: string; updatedAt: string; } export interface ExpenseListResponse { items: Expense[]; nextCursor: string|null; total: number } ``` **Acceptance:** - [ ] `EXPENSE_CATEGORIES` exported with exactly 8 entries; `ExpenseCategoryId` includes `'travel'`. - [ ] Types build with no `any`; consumed by both API and app packages. ### Task 3: Core expense service — CRUD, cursor pagination, soft delete, serialization **Blocks:** 9, 10, 11, 12 · **Blocked by:** 1, 2 **Files:** - Create: `packages/expenses/src/service.ts` - Create: `packages/expenses/src/serialize.ts` - Create: `packages/expenses/src/index.ts` - Create: `packages/expenses/package.json`, `tsconfig.json` **Steps:** - [ ] Implement `listExpenses(db, tenantId, filters)` using cursor pagination (`encodeCursor`/`decodeCursor` from `@zync/db`), clamping `limit` via `clampLimit` to max 100; return `{ items, nextCursor, total }`. - [ ] Implement filters: date range (`expense_date`), category, deduction %, status, source, project; all optional and AND-combined. Tab semantics: `All` (no status filter, excludes soft-deleted), `Needs Review` (`status='NEEDS_REVIEW'`), `Recurring` (rows created by recurring source — filter `source IN ('email')` is NOT recurring; use `is_per_diem=false AND source='upload'` is NOT recurring either — implement `Recurring` as expenses linked to a recurring template via `source_metadata->>'recurring'` flag, empty until recurring-expenses wires it). - [ ] Implement `getExpense(db, tenantId, id)` returning expense + its `expense_corrections` rows. - [ ] Implement `createExpense`, `updateExpense` (records each changed field into `expense_corrections` within the same transaction; honor `require-audit-in-transaction`), `softDeleteExpense` (sets `deleted_at`). - [ ] Implement `serializeExpense(row)` converting NUMERIC→string and snake→camel per the `Expense` type. - [ ] All queries go through `tenantQuery(db, tenantId)`; no cross-tenant leakage. **Schema / Interfaces:** ```ts export interface ExpenseFilters { cursor?: string; limit?: number; dateFrom?: string; dateTo?: string; category?: ExpenseCategoryId; deductionPct?: number; status?: ExpenseStatus; source?: ExpenseSource; projectId?: string; tab?: 'all'|'needs_review'|'recurring'; } export async function listExpenses(db: DB, tenantId: string, f: ExpenseFilters): Promise; export async function getExpense(db: DB, tenantId: string, id: string): Promise<{ expense: Expense; corrections: ExpenseCorrection[] } | null>; export async function createExpense(db: DB, tenantId: string, input: NewExpenseInput): Promise; export async function updateExpense(db: DB, tenantId: string, userId: string, id: string, patch: Partial): Promise; export async function softDeleteExpense(db: DB, tenantId: string, id: string): Promise; export function serializeExpense(row: ExpenseRow): Expense; ``` **Acceptance:** - [ ] List returns ≤100 rows, correct `nextCursor`, and a `total` count; soft-deleted rows excluded. - [ ] Editing a field writes an `expense_corrections` row with `original_value`/`corrected_value` in the same transaction. - [ ] Two tenants cannot read each other's expenses. ### Task 4: AI pipeline service — OCR extraction + tax evaluation **Blocks:** 6, 12 · **Blocked by:** 1, 2 **Files:** - Create: `packages/expenses/src/ai/ocr.ts` - Create: `packages/expenses/src/ai/evaluate.ts` - Create: `packages/expenses/src/ai/prompts.ts` **Steps:** - [ ] `runOcr(env, expense, fileBytes, mediaType)`: build `AIMessage[]` with an `AIContentBlock` image (base64) + text instruction; call `callAI({ tenantId, userId: expense.createdBy, useCase: 'expense_ocr', messages, maxTokens, entityType:'expense', entityId: expense.id })`. Parse JSON response into `{ vendorName, vendorTaxId, invoiceNumber, receiptDate, invoiceTotal, vatAmount, currency, allocationNumber, rawOcrText }`. Compute `ocrConfidence` from parse completeness + model-returned confidence. - [ ] Seed canonical fields: `expenseDate := receiptDate`; `amount := invoiceTotal` when `currency='ILS'`, else convert to ILS via the country adapter exchange rate (`getTenantCountryAdapter` → rate; identity for ILS). - [ ] `evaluateDeductibility(env, expense)`: call `callAI({ useCase: 'expense_tax_eval', ... })` with the 3-step Israeli rule prompt (blacklist → ceiling → relevance multiplier) and the tenant's `business_category` from `tenant_settings`. Parse `{ expenseCategory, deductionPct (0|25|45|66|100), deductionConfidence (0.50–1.00), reasoningHe, reasoningEn }`. - [ ] `prompts.ts`: encode the blacklist, ceiling table, relevance multiplier, and confidence scale verbatim from the spec; these are the fallback seed prompts (admin-configurable copies live in `ai_global_config.use_case_prompts`). - [ ] On any parse/validation failure, throw a typed `ExpenseProcessingError` so the consumer can mark `FAILED`. If OCR confidence < threshold (0.7) mark for `NEEDS_REVIEW` rather than `COMPLETED`. **Schema / Interfaces:** ```ts export interface OcrResult { vendorName?: string; vendorTaxId?: string; invoiceNumber?: string; receiptDate?: string; invoiceTotal?: number; vatAmount?: number; currency?: string; allocationNumber?: string; rawOcrText: string; ocrConfidence: number; } export interface TaxEvalResult { expenseCategory: ExpenseCategoryId; deductionPct: 0|25|45|66|100; deductionConfidence: number; reasoningHe: string; reasoningEn: string; } export async function runOcr(env: Env, expense: Expense, file: ArrayBuffer, mediaType: string): Promise; export async function evaluateDeductibility(env: Env, expense: Expense, businessCategory: string|null): Promise; ``` **Acceptance:** - [ ] OCR call uses a `is_vision_capable` model path (asserted by `callAI` routing) and passes the image as a base64 `AIContentBlock`. - [ ] `deductionPct` constrained to {0,25,45,66,100}; `deductionConfidence` in [0.50,1.00]; both reasoning languages populated. - [ ] ILS receipts set `amount = invoiceTotal`; foreign receipts store the converted ILS value. ### Task 5: Per-diem service & settings service **Blocks:** 11, 12 · **Blocked by:** 1, 2 **Files:** - Create: `packages/expenses/src/per-diem.ts` - Create: `packages/expenses/src/settings.ts` **Steps:** - [ ] `getExpenseSettings(db, tenantId)` / `updateExpenseSettings(db, tenantId, patch)`: the **canonical** expense-config accessor on the `tenant_settings` columns — read/write `filing_cadence`, `tax_basis`, `business_category`, `per_diem_rates`, `expense_default_category`, `expense_auto_approve_threshold_ils`, `expense_receipt_reminder_enabled`, `expense_receipt_reminder_days`, `expense_approval_threshold_ils`, `expense_approver_role` via `tenantQuery` (the base row is seeded at signup; UPDATE in place). `getExpenseSettings` coalesces the read row through `mergeExpenseSettings` (`@zync/types`) so callers always get a complete `ExpenseSettings`. `expense-settings-ui` (spec 62) consumes this pair for `/settings/expenses` rather than defining its own. Never touch `advance_tax_rate_pct` (owned by israeli-tax-reports), `default_currency` (the `tenants.default_currency` scalar), or the AI-config `getTenantSettings`/`upsertTenantSettings` (`ai_tenant_settings`). - [ ] `createPerDiemExpense(db, tenantId, userId, input)`: read `per_diem_rates`, resolve rate from `travel_type` + duration (full/half/custom days), compute `amount = rate × days`, insert an expense with `is_per_diem=true`, `per_diem_days`, `per_diem_rate_ils`, `expense_category='travel'`, `vat_amount=0`, `vat_deductible=false`, `status='COMPLETED'`, `source='upload'`, no OCR. Return `{ expenseId }`. **Schema / Interfaces:** ```ts // ExpenseSettings is the single canonical shape in @zync/types (one field per tenant_settings // expense column: filing_cadence, tax_basis, business_category, per_diem_rates, // expense_default_category, expense_auto_approve_threshold_ils, expense_receipt_reminder_enabled, // expense_receipt_reminder_days, expense_approval_threshold_ils, expense_approver_role). // expense-settings-ui (spec 62) reuses this type + accessor; it does NOT define its own. export async function getExpenseSettings(db: DB, tenantId: string): Promise; export async function updateExpenseSettings(db: DB, tenantId: string, patch: Partial): Promise; export interface PerDiemInput { travel_type: 'domestic'|'international'; days: number; date: string; project_id?: string; notes?: string; } export async function createPerDiemExpense(db: DB, tenantId: string, userId: string, input: PerDiemInput): Promise<{ expenseId: string }>; ``` **Acceptance:** - [ ] Per-diem amount = `rate × days` with rate selected from `per_diem_rates` by type+duration. - [ ] Per-diem rows have `vat_amount=0`, `vat_deductible=false`, `status='COMPLETED'`, `is_per_diem=true`, and never enqueue OCR. ### Task 6: Queue consumer — `expense.process` pipeline **Blocks:** 9 · **Blocked by:** 1, 4 **Files:** - Create: `apps/zync-api/src/queues/expense-process.ts` - Modify: `apps/zync-api/src/queue.ts` (route `expense.process` messages to the consumer) **Steps:** - [ ] Consumer receives `{ type:'expense.process', tenantId, expenseId }`. Set `status='PROCESSING'`, `processing_started_at=now()`. - [ ] Fetch file from R2 (`STORAGE`) by `r2_key`; run `runOcr`; persist OCR + canonical fields; compute `ocr_confidence`. - [ ] Run `evaluateDeductibility`; persist category/pct/confidence/reasoning + `evaluated_at`. - [ ] Set final `status = COMPLETED` (or `NEEDS_REVIEW` when ocr_confidence < 0.7), `processed_at=now()`. - [ ] Emit `expense.processed` webhook (Task 7); push realtime `{ op:'expense.updated', expenseId }` via `pushOverWebSocket` to `TenantRealtimeDO`. - [ ] On any thrown error: set `status='FAILED'`, store `processing_error`, emit `expense.failed` webhook. Use queue retry semantics (max retries, then DLQ) for transient infra errors only — AI parse failures are terminal (no retry). **Acceptance:** - [ ] Happy path moves a `PENDING` expense to `COMPLETED`/`NEEDS_REVIEW` with all OCR + eval fields populated. - [ ] Failure path sets `FAILED` + `processing_error` and emits `expense.failed`. - [ ] A realtime `expense.updated` op is pushed on completion. ### Task 7: Webhook event emission **Blocks:** 6, 8 · **Blocked by:** — **Files:** - Create: `packages/expenses/src/webhooks.ts` **Steps:** - [ ] Implement `emitExpenseWebhook(env, tenantId, event, payload)` that enqueues onto the `webhook.deliver` queue using the shared `WebhookEvent` envelope. - [ ] Define the three events with exact payloads: `expense.uploaded {expenseId, fileName, source}`, `expense.processed {expenseId, status, category, deductionPct, confidence}`, `expense.failed {expenseId, error}`. **Schema / Interfaces:** ```ts export type ExpenseWebhookEvent = 'expense.uploaded'|'expense.processed'|'expense.failed'; export async function emitExpenseWebhook(env: Env, tenantId: string, event: ExpenseWebhookEvent, payload: Record): Promise; ``` **Acceptance:** - [ ] All three events emit with spec-exact payload shapes onto `webhook.deliver`. ### Task 8: Inbound channels — email forwarding & IM intake **Blocks:** — · **Blocked by:** 3, 7 **Files:** - Create: `apps/zync-api/src/intake/email-expense.ts` - Modify: `apps/zync-api/src/queue.ts` (handle `inbound_message_handler` entries tagged for expenses) **Steps:** - [ ] Email: CF Email Routing delivers to `expenses@{tenantSlug}.zync.is` → Worker handler resolves `tenantId` from the slug, extracts PDF/image attachments, uploads each to R2, creates an `expenses` row with `source='email'`, `source_metadata={ messageId, sender }`, emits `expense.uploaded`, enqueues `expense.process`. - [ ] WhatsApp/Telegram: in the shared inbound-message consumer (from `system-communications-notifications`), when a tenant routes a photo to expenses, download the media, upload to R2, create an `expenses` row with `source='telegram'|'whatsapp'`, `source_metadata={ senderUserId, messageId }`, emit `expense.uploaded`, enqueue `expense.process`. - [ ] Enforce the same file-type/size validation as the upload route. **Acceptance:** - [ ] A forwarded receipt email creates a `PENDING` expense with `source='email'` and triggers processing. - [ ] A Telegram/WhatsApp photo creates a `PENDING` expense with the correct `source` and sender metadata. ### Task 9: API — list, detail, file URL routes **Blocks:** 15 · **Blocked by:** 3, 6 **Files:** - Create: `apps/zync-api/src/routes/expenses/index.ts` - Modify: `apps/zync-api/src/routes/index.ts` (mount `/api/expenses`) **Steps:** - [ ] `GET /api/expenses` — `expenses:read`; parse + Zod-validate query (cursor, limit≤100, filters, tab); call `listExpenses`; return `ExpenseListResponse`. - [ ] `GET /api/expenses/:id` — `expenses:read`; return expense + corrections. - [ ] `GET /api/expenses/:id/file` — `expenses:read`; generate a 30-min signed R2 GET URL for `r2_key` (presigned via `STORAGE`); return `{ url }`. - [ ] All routes behind `authMiddleware` + `requireModuleEnabled('expenses')` + `requirePermission(...)`. **Schema / Interfaces:** ``` GET /api/expenses?cursor&limit&dateFrom&dateTo&category&deductionPct&status&source&projectId&tab → ExpenseListResponse GET /api/expenses/:id → { expense: Expense, corrections: ExpenseCorrection[] } GET /api/expenses/:id/file → { url: string } // 30-min TTL ``` **Acceptance:** - [ ] List enforces `limit ≤ 100` server-side regardless of client input. - [ ] File URL expires after 30 minutes and is scoped to the requesting tenant's object. ### Task 10: API — upload route **Blocks:** 15, 17 · **Blocked by:** 3, 7 **Files:** - Modify: `apps/zync-api/src/routes/expenses/index.ts` **Steps:** - [ ] `POST /api/expenses/upload` — `expenses:write`; multipart body, up to 20 files, each ≤10 MB, type in {jpg,png,heic,pdf}. - [ ] Apply `rateLimit` with `RATE_LIMITER_EXPENSE_UPLOAD` (10/min/user) before processing. - [ ] Per file: validate type+size → upload to R2 key `{tenantId}/expenses/{expenseId}/{filename}` → create `expenses` row `status='PENDING'`, `source='upload'` → emit `expense.uploaded` → enqueue `expense.process`. - [ ] Return the created expense record(s). **Schema / Interfaces:** ``` POST /api/expenses/upload (multipart, ≤20 files, ≤10MB each, jpg|png|heic|pdf) → { items: Expense[] } ``` **Acceptance:** - [ ] 11th upload within a minute by the same user is rejected by the rate limiter. - [ ] Invalid type or >10 MB file is rejected with a 4xx and a clear error; valid files create PENDING rows and enqueue processing. ### Task 11: API — update, delete, per-diem, settings routes **Blocks:** 16, 18 · **Blocked by:** 3, 5 **Files:** - Modify: `apps/zync-api/src/routes/expenses/index.ts` - Create: `apps/zync-api/src/routes/expenses/settings.ts` **Steps:** - [ ] `PATCH /api/expenses/:id` — `expenses:write`; Zod-validate editable fields; call `updateExpense` (writes corrections); only allowed when status is `COMPLETED` or `NEEDS_REVIEW`. - [ ] `DELETE /api/expenses/:id` — `expenses:delete`; soft delete. - [ ] `POST /api/expenses/per-diem` — `expenses:write`; Zod body `{travel_type, days, date, project_id?, notes?}`; call `createPerDiemExpense`; return `{ expenseId }`. - [ ] `GET/PATCH /api/settings/expenses` — `expenses:read` for GET, `settings:write` for PATCH; read/write expense settings via `getExpenseSettings`/`updateExpenseSettings` over the canonical snake_case `ExpenseSettings` (filing_cadence, tax_basis, business_category, per_diem_rates, expense_* columns). **Sole declaration of this route**; the per-diem form (Task 18) and expense-settings-ui (spec 62) both consume it — neither redeclares it. GET returns the full `ExpenseSettings`. PATCH Zod-validates the body with `expenseSettingsSchema` (`@zync/types`, the page-editable subset, `.strict()`); for non-Business/Enterprise tiers, reset `expense_approval_threshold_ils`/`expense_approver_role` to `EXPENSE_SETTINGS_DEFAULTS` server-side before persisting; call `updateExpenseSettings` and write an audit row (action `settings.expenses.update`) inside the same transaction. No currency field — tenants.default_currency is a separate scalar. **Schema / Interfaces:** ``` PATCH /api/expenses/:id body: Partial → { expense: Expense } DELETE /api/expenses/:id → { ok: true } (soft) POST /api/expenses/per-diem body: PerDiemInput → { expenseId: string } GET /api/settings/expenses → ExpenseSettings PATCH /api/settings/expenses body: Partial → ExpenseSettings ``` **Acceptance:** - [ ] PATCH on a `PENDING` expense is rejected; on `NEEDS_REVIEW`/`COMPLETED` it succeeds and logs corrections. - [ ] Per-diem route creates a COMPLETED non-VAT expense without OCR. ### Task 12: API — re-evaluate route **Blocks:** 16 · **Blocked by:** 3, 4 **Files:** - Modify: `apps/zync-api/src/routes/expenses/index.ts` **Steps:** - [ ] `POST /api/expenses/:id/evaluate` — `expenses:write`; re-run `evaluateDeductibility` synchronously (or enqueue a `expense.process` re-eval-only job for large volume), update category/pct/confidence/reasoning + `evaluated_at`, push realtime `expense.updated`. Returns the updated expense. **Schema / Interfaces:** ``` POST /api/expenses/:id/evaluate → { expense: Expense } ``` **Acceptance:** - [ ] Manual re-evaluation overwrites the AI tax fields and bumps `evaluated_at`. ### Task 13: Report services — Expense Report, VAT (PCN874), Vendor Analysis **Blocks:** 14, 19 · **Blocked by:** 1, 3 **Files:** - Create: `packages/expenses/src/reports.ts` **Steps:** - [ ] `expenseReport(db, tenantId, filters)`: rows of Date, Vendor, Invoice #, Vendor Tax ID, Total(`amount`), VAT(`vat_amount`), Net(`amount − vat_amount`), Category, Deduction %, Allocation #, Notes; filter by date range, vendor, category, status. - [ ] `vatSummaryPcn874(db, tenantId, period)`: period from `filing_cadence` (monthly/bimonthly). Input VAT = sum of `vat_amount` where `vat_deductible=true AND deduction_pct > 0`; partial input VAT for `0 < deduction_pct < 100` uses `vat_amount × deduction_pct/100`; per-diem rows excluded (`is_per_diem=true` ⇒ `vat_amount=0`, `vat_deductible=false`). Output VAT pulled from `invoices` (issued invoice VAT). Compute net VAT due/refund. - [ ] `vendorAnalysis(db, tenantId, dateRange)`: group by `vendor_id` when present else normalized `vendor_name`; display `vendors.name` for linked rows (vendor_id linkage lands with vendors-suppliers — until then group by `vendor_name`), sum `amount` + VAT per vendor, sort by total desc. - [ ] All aggregation queries filter `deleted_at IS NULL` and use the `idx_expenses_report` index. **Schema / Interfaces:** ```ts export interface ExpenseReportRow { date: string; vendor: string; invoiceNumber: string|null; vendorTaxId: string|null; total: string; vat: string; net: string; category: ExpenseCategoryId|null; deductionPct: number|null; allocationNumber: string|null; notes: string|null } export interface VatSummary { period: string; inputVat: string; partialInputVat: string; outputVat: string; netVatDue: string } export interface VendorAnalysisRow { vendor: string; vendorId: string|null; totalAmount: string; totalVat: string } export async function expenseReport(db: DB, tenantId: string, f: ReportFilters): Promise; export async function vatSummaryPcn874(db: DB, tenantId: string, period: { from: string; to: string }): Promise; export async function vendorAnalysis(db: DB, tenantId: string, range: { from: string; to: string }): Promise; ``` **Acceptance:** - [ ] Input VAT sums only `vat_deductible=true AND deduction_pct>0`; partial rows scaled by `deduction_pct/100`. - [ ] Per-diem rows never contribute to input VAT. - [ ] Vendor analysis groups unlinked rows by normalized `vendor_name`. ### Task 14: Report API routes + RTL Hebrew Excel export **Blocks:** 19 · **Blocked by:** 13 **Files:** - Create: `apps/zync-api/src/routes/expenses/reports.ts` - Create: `packages/expenses/src/excel.ts` **Steps:** - [ ] `GET /api/expenses/reports/expense|vat|vendors` — `reports:read`; return JSON from Task 13 services. - [ ] `GET /api/expenses/reports/expense/xlsx` and `/vat/xlsx` — `reports:read`; build XLSX via `exceljs` with RTL sheet view (`views: [{ rightToLeft: true }]`) and Hebrew column headers; stream as `application/vnd.openxmlformats-officedocument.spreadsheetml.sheet`. - [ ] Mount under `/api/expenses/reports`. **Schema / Interfaces:** ``` GET /api/expenses/reports/expense → ExpenseReportRow[] GET /api/expenses/reports/vat → VatSummary GET /api/expenses/reports/vendors → VendorAnalysisRow[] GET /api/expenses/reports/expense/xlsx → xlsx download (RTL, Hebrew headers) GET /api/expenses/reports/vat/xlsx → xlsx download (PCN874) ``` **Acceptance:** - [ ] Exported XLSX opens with right-to-left sheet orientation and Hebrew headers. - [ ] VAT xlsx matches PCN874 figures from the service. ### Task 15: Expenses page shell — tabs, filters, virtualized list **Blocks:** 16, 17 · **Blocked by:** 9 **Files:** - Create: `apps/zync-app/src/pages/expenses/ExpensesPage.tsx` - Create: `apps/zync-app/src/pages/expenses/ExpenseTable.tsx` - Create: `apps/zync-app/src/hooks/useExpenseList.ts` **Steps:** - [ ] Tabs: **All** | **Needs Review** | **Recurring** | **Mileage** (Mileage routes to Task 20 view). First three drive the `tab` query param. - [ ] `useExpenseList` (TanStack Query, cursor-infinite) hitting `GET /api/expenses`; URL-synced filters (date range, category, deduction %, status, source, project). - [ ] Table columns: Receipt thumbnail, Vendor, Invoice date, Total (ILS), VAT, Category, Deduction %, Confidence, Status, Source. Status + confidence badges colored per spec tiers using `@zync/ui` `Badge`. - [ ] Activate TanStack Virtual when rows > 200 (row height 64px, overscan 5). - [ ] Bulk actions toolbar: "Evaluate all pending", "Export selected", "Delete selected". - [ ] Use `t('...')` for all labels; honor `useDirection` for RTL; `prefers-reduced-motion` respected on the PROCESSING spinner. **Acceptance:** - [ ] Filters reflect in the URL and reload correctly on refresh. - [ ] Virtual scroll engages past 200 rows; list never requests >100 rows/page. - [ ] Badges match spec color tiers. ### Task 16: Expense detail Sheet — Details & Evaluation tabs **Blocks:** — · **Blocked by:** 11, 12, 15 **Files:** - Create: `apps/zync-app/src/pages/expenses/ExpenseDetailSheet.tsx` - Create: `apps/zync-app/src/hooks/useExpense.ts` **Steps:** - [ ] Open on row click (`@zync/ui` `Sheet`, right side). **Details tab:** receipt viewer (PDF via ``/`