# Expenses Module

**Date:** 2026-05-30  
**Status:** Draft  
**Depends on:** `foundation-auth-rbac`, `system-i18n`, `ai-assistant`, `projects-module`  
**Referenced by:** `invoices-core`, `reports-analytics`, `contractor-payouts`, `vendors-suppliers`

---

## Overview

Expense tracking with receipt OCR and AI-powered Israeli tax deductibility evaluation. Staff upload receipts (or forward via email/WhatsApp/Telegram); the system extracts fields via Claude Vision and evaluates each expense's deductibility percentage under Israeli tax law. Generates VAT summary (PCN874) and expense reports.

Ported from Virtuac. Key differences: Claude Vision replaces Azure/Google Vision; Cloudflare Queues replace background jobs; multi-tenant via `tenantQuery`.

---

## Data Model

```sql
expenses (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  project_id UUID,                    -- optional link to project
  created_by UUID NOT NULL,           -- user who created (upload, email intake, or recurring cron)

  -- File
  r2_key TEXT NOT NULL,               -- R2 key for original file
  file_name TEXT NOT NULL,
  file_type TEXT NOT NULL,            -- 'pdf' | 'jpg' | 'png' | 'heic'
  file_size_bytes INTEGER NOT NULL,

  -- OCR extracted fields (nullable until processed)
  vendor_name TEXT,
  vendor_tax_id TEXT,                 -- ח.פ. / ע.מ. of vendor
  invoice_number TEXT,
  invoice_total NUMERIC(12,2),        -- raw OCR total in `currency` (may be foreign)
  vat_amount NUMERIC(12,2),
  currency TEXT DEFAULT 'ILS',
  allocation_number TEXT,             -- מספר הקצאה (Israeli specific)
  raw_ocr_text TEXT,                  -- full OCR output for reprocessing

  -- Accounting fields (canonical — read by all financial reports; see Schema Reconciliation)
  expense_date DATE,                  -- accounting/posting date (seeded from OCR receipt date)
  amount NUMERIC(12,2),               -- ILS-normalized GROSS total (incl VAT); = invoice_total when currency='ILS', else converted via multi-currency rate
  vat_deductible BOOLEAN NOT NULL DEFAULT true,  -- input-VAT eligible; false for per-diem & non-deductible items

  -- Processing status
  status TEXT DEFAULT 'PENDING',      -- PENDING | PROCESSING | COMPLETED | FAILED | NEEDS_REVIEW
  ocr_confidence NUMERIC(3,2),        -- 0.00–1.00
  processing_started_at TIMESTAMPTZ,
  processed_at TIMESTAMPTZ,
  processing_error TEXT,

  -- AI tax evaluation (nullable until evaluated)
  expense_category TEXT,              -- one of 8 categories (see below)
  deduction_pct INTEGER,              -- 0 | 25 | 45 | 66 | 100
  deduction_confidence NUMERIC(3,2),  -- 0.50–1.00
  deduction_reasoning_he TEXT,        -- explanation in Hebrew
  deduction_reasoning_en TEXT,        -- explanation in English
  evaluated_at TIMESTAMPTZ,

  -- Source tracking
  source TEXT DEFAULT 'upload',       -- 'upload' | 'email' | 'whatsapp' | 'telegram'
  source_metadata JSONB,              -- original message ID, sender, etc.

  -- User notes
  notes TEXT,

  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
)

expense_corrections (
  id UUID PRIMARY KEY,
  expense_id UUID NOT NULL,
  user_id UUID NOT NULL,
  field_name TEXT NOT NULL,           -- 'vendor_name' | 'invoice_total' | 'deduction_pct' | etc.
  original_value TEXT,
  corrected_value TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
)
```

### Schema Reconciliation (canonical columns)

This table is the **single source of truth** for the `expenses` entity. Financial consumers (`financial-statements`, `israeli-tax-reports`, `bituach-leumi`, `recurring-expenses`, `reports-analytics`) read these canonical names:

| Canonical column | Meaning | Notes |
|------------------|---------|-------|
| `amount` | ILS-normalized **gross** total (incl VAT) | What all reports `SUM()`. `= invoice_total` for ILS receipts; foreign receipts store the ILS equivalent. Net expense = `amount − vat_amount`. |
| `expense_date` | Accounting/posting date | Seeded from the OCR receipt date; reports filter and index on this. |
| `created_by` | Creating user | Set by upload, email intake, or the `recurring-expenses` cron. |
| `vat_deductible` | Input-VAT eligible | `true` by default; `false` for per-diem and non-deductible items. Input-VAT (PCN874) sums `vat_amount` only where this is `true`. |
| `expense_category` | Tax category (8 fixed) | Reports group by this column; do **not** reference a bare `category`. |

`invoice_total` + `currency` are retained as the **raw OCR capture**; `amount` is the derived ILS accounting value. `CREATE INDEX idx_expenses_report ON expenses(tenant_id, status, expense_date)` (defined in `financial-statements`) backs the report queries.

### Expense Categories (8 categories, Hebrew/English)

Stored as constants in `packages/types/src/expense-categories.ts` (not a DB table — fixed by Israeli tax law):

| ID | Hebrew | English | Typical deduction |
|----|--------|---------|------------------|
| `office` | אחזקת משרד | Office Maintenance | 100% |
| `marketing` | שיווק ופרסום | Marketing & Advertising | 100% |
| `professional` | שירותים מקצועיים | Professional Services | 100% |
| `vehicle` | רכב ונסיעות | Vehicle & Travel | 45% (some 100%) |
| `equipment` | ציוד ותוכנה | Equipment & Software | 100% |
| `finance` | פיננסיות וביטוח | Finance & Insurance | 100% |
| `welfare` | פיתוח ורווחה | Development & Welfare | 100% (food: 0%) |
| `exceptional` | הוצאות חריגות | Exceptional Expenses | 0–100% |

---

## Israeli Tax Deductibility Rules

AI evaluation applies these rules (encoded in Claude system prompt). Three-step algorithm:

### Step 1: Blacklist (deduction = 0%)

Non-deductible regardless of category: personal clothing, traffic fines, private meals/entertainment (unless client entertainment with explicit context), diapers, personal items not related to business.

### Step 2: Base deduction ceiling

| Expense type | Ceiling |
|-------------|---------|
| Office supplies, marketing, professional services, software, internet | 100% |
| Mobile phone (if central to business operations) | 66% |
| Vehicle fuel, repairs, insurance, testing (under 3.5t) | 45% |
| Taxis, driving schools, public transit | 100% |
| Foreign travel (hotel + food abroad) | 25% |

### Step 3: Relevance multiplier

- **1.0** — item essential to the tenant's business type (wood for carpenter, servers for software company)
- **1.0** — standard administrative overhead (stationery, electricity, rent)
- **0.0** — weak or implausible link (cement for a lawyer)

**Final:** `deduction_pct = ceiling × multiplier`

Confidence scoring:
- 0.90–1.00: exact match to known vendor/keyword or blacklist hit
- 0.70–0.89: category keyword match with supporting context
- 0.50–0.69: ambiguous; limited description

Reasoning stored in both Hebrew and English; Hebrew shown in UI.

---

## Processing Pipeline

Async via Cloudflare Queue `expense-process` (`EXPENSE_QUEUE` binding; message body `{ type: 'expense.process', ... }`). *Rationale: CF queue names are hyphenated; `expense.process` is the message type, not the queue name.*

```
Upload → R2 → enqueue expense.process (on expense-process queue)
  → Consumer:
      1. Fetch file from R2
      2. Claude Vision (claude-sonnet): extract fields from receipt image
         → vendor_name, vendor_tax_id, invoice_number, receipt date,
            invoice_total (raw, in `currency`), vat_amount, allocation_number, raw_ocr_text
         → seed canonical accounting fields: expense_date := receipt date;
            amount := invoice_total normalized to ILS (multi-currency rate; identity when currency='ILS')
      3. Compute ocr_confidence
      4. Claude text (claude-haiku): evaluate deductibility
         → expense_category, deduction_pct, deduction_confidence,
            deduction_reasoning_he/en
      5. UPDATE expenses SET status = 'COMPLETED', ...
      6. Fire webhook expense.processed
      7. Realtime push via DO: { op: 'expense.updated', expenseId }
```

On processing error: set `status = 'FAILED'`, store `processing_error`, fire `expense.failed` webhook.

Tenant staff can manually trigger re-evaluation: `POST /api/expenses/:id/evaluate`.

---

## Features

### Page Tabs

The `/expenses` page has tabs: **All** | **Needs Review** | **Recurring** | **Mileage**. The first three filter the expense list; **Mileage** switches to the mileage logbook view (see "Mileage Tab" below). A "Mileage logbook" link is also present in the expenses secondary nav (sidebar).

### Expense List (`/expenses`)

Table with columns: Receipt thumbnail, Vendor, Invoice date, Total (ILS), VAT, Category, Deduction %, Confidence, Status, Source.

Status badge: PENDING (grey), PROCESSING (yellow spinner), COMPLETED (green), FAILED (red), NEEDS_REVIEW (orange).

Confidence badge: ≥0.9 green, ≥0.7 yellow, <0.7 red.

Filters (URL-synced): date range, category, deduction %, status, source, project.

Bulk actions: "Evaluate all pending", "Export selected", "Delete selected".

#### Mobile route behavior

At phone widths, approval and OCR-review route content uses 16px inline padding. Header actions wrap without horizontal overflow; review rows retain vendor and confidence while date and amount remain available at wider widths.

Rationale: preserve primary review actions and readable row targets inside the mobile app frame.

#### List Performance

Expense list uses **cursor-based pagination**:

```ts
// API: GET /api/expenses?cursor={encodedCursor}&limit=50
interface ExpenseListResponse {
  items: Expense[]
  nextCursor: string | null
  total: number
}
```

**Virtual scroll (TanStack Virtual):** when list > 200 rows, activate. Row height: 64px (with receipt thumbnail). Overscan: 5 rows.

**Invariant:** list API max 100 rows per request.

### Upload

Drag-and-drop zone + file picker. Accepts: JPG, PNG, HEIC, PDF. Max 10 MB per file. Batch upload: up to 20 files.

Rate limit: `RATE_LIMITER_EXPENSE_UPLOAD` — 10 uploads/minute per user (protects OCR queue).

#### Upload Zone Accessibility

- Zone element: `role="button"` `tabindex="0"` `aria-label="Upload receipts. Accepts JPG, PNG, HEIC, PDF up to 10 MB."`
- `Enter`/`Space` on focused zone: opens file picker (same as click)
- Drag-active state: `aria-describedby` pointing to a `role="status"` region that reads `"Drop files here"`
- Per-file upload progress: `role="progressbar"` `aria-valuenow="{pct}"` `aria-valuemin="0"` `aria-valuemax="100"` `aria-label="Uploading {filename}: {pct}% complete"`
- Upload success: `role="status"` region announces `"{filename} uploaded successfully"`
- Upload failure: `role="alert"` region announces `"Upload failed for {filename}: {error reason}"`
- Batch queue: `role="list"` container; each file item: `role="listitem"` with status chip readable by assistive technology

On upload:
1. Validate file type + size
2. Upload to R2: `{tenantId}/expenses/{expenseId}/{filename}`
3. Create `expenses` record with `status = 'PENDING'`
4. Enqueue `expense.process` job

### Expense Detail Sheet (side-panel)

Opens on row click. Two tabs: **Details** and **Evaluation**.

**Details tab:**
- Receipt image viewer (PDF rendered via `<object>` or `<iframe>`; images via `<img>`)
- All extracted fields (editable if `COMPLETED` or `NEEDS_REVIEW`)
- Save corrections → stored in `expense_corrections`, field updated on expense record
- Notes field (free text)
- Source metadata chip (email sender, Telegram username, etc.)

**Evaluation tab:**
- Category selector (override AI choice)
- Deduction % (override AI result)
- Reasoning text (Hebrew, read-only; edit triggers re-evaluation prompt)
- Confidence bar
- "Re-evaluate" button → `POST /api/expenses/:id/evaluate`

### Corrections

When staff edits an extracted field, original value is preserved in `expense_corrections` for audit trail and potential future ML fine-tuning.

### Inbound Channels

**Email forwarding:** Staff forwards receipt email to `expenses@{tenantSlug}.zync.is`. CF Email Routing → Worker → Queue. Worker extracts attachments (PDF/image), creates expense records.

**WhatsApp / Telegram:** Staff sends photo of receipt to tenant's bot → creates expense record with `source = 'telegram'` or `'whatsapp'`. Metadata: sender user ID.

### Mileage Tab (`/expenses/mileage`)

The **Mileage** tab is the in-app home for the mileage logbook (spec 166, `mileage-logbook`). It is a tab within the expenses module — there is no standalone top-level `/mileage` page.

- Route: `/expenses/mileage`
- Shows the mileage trip list from the `mileage_trips` table (spec 166)
- Columns: Date, Vehicle, From → To, Distance (km), Purpose, Deduction (ILS), Actions
- Quick-add button: **"Log Trip"** → opens a slide-over form (not a full page) that POSTs to `POST /api/mileage`
- Filters (URL-synced): date range, vehicle (dropdown)
- A "Mileage logbook" link appears in the expenses secondary nav / sidebar
- Annual report export reachable from the logbook header (spec 166)

Data, calculations, vehicle management, and the annual report are owned by spec 166; the expenses module hosts the navigation surface and the trip list/slide-over.

---

## Tenant Configuration (`/settings/expenses`)

Per-tenant expense settings in `tenant_settings`:

```ts
interface ExpenseSettings {
  filingCadence: 'monthly' | 'bimonthly'       // VAT filing period (the /settings/expenses VAT control)
  taxBasis: 'cash' | 'accrual'                  // basis for income tax
  businessCategory: string                       // one of 17 industries (for AI context)
  // ...plus the /settings/expenses config columns (expense-settings-ui, spec 62):
  // expenseDefaultCategory, expenseAutoApproveThresholdIls, expenseReceiptReminderEnabled,
  // expenseReceiptReminderDays, expenseApprovalThresholdIls, expenseApproverRole, perDiemRates.
}
// Currency is NOT here — it is the tenants.default_currency scalar (system-i18n), shared tenant-wide.
```

> The advance-tax rate is **not** stored here. It is the single canonical column
> `tenant_settings.advance_tax_rate_pct` (owned by `israeli-tax-reports`, spec 171),
> read/written via `PATCH /api/settings/tax`. Expense settings reference that column
> rather than duplicating it.

`businessCategory` is passed to Claude in the system prompt to improve deduction relevance scoring.

---

## Reports

### Expense Report

Detailed list: Date, Vendor, Invoice #, Vendor Tax ID, Total, VAT, Net, Category, Deduction %, Allocation #, Notes.

Filterable by date range, vendor, category, status. Export: Excel with RTL Hebrew column headers.

### VAT Summary (PCN874)

Israeli VAT periodic reporting format:
- Period: monthly or bimonthly (per `filingCadence`)
- Input VAT (מע"מ תשומות): sum of `vat_amount` for `deduction_pct > 0` expenses
- Partial input VAT: expenses where 0 < deduction_pct < 100 → `vat_amount × deduction_pct / 100`
- Output VAT (מע"מ עסקאות): from `invoices` table (issued invoice VAT)
- Net VAT due/refund

Rendered as: on-screen table + Excel export.

### Vendor Analysis

Group expenses by `vendor_id` (the linked `vendors` entity, per `vendors-suppliers`), falling back to a normalized `vendor_name` when `vendor_id IS NULL` (legacy / unmatched OCR rows). Display the vendor's canonical `vendors.name` for linked rows, the raw `vendor_name` for unlinked ones. Sum `amount` + VAT per vendor. Sort by total desc. Date range filter.

---

## Permissions

| Action | Required permission |
|--------|-------------------|
| View expenses | `expenses:read` |
| Upload receipts | `expenses:write` |
| Edit / correct fields | `expenses:write` |
| Delete expense | `expenses:delete` |
| Trigger re-evaluation | `expenses:write` |
| View reports | `reports:read` |
| Export reports | `reports:read` |
| Configure settings | `settings:write` |

---

## Per-Diem Rules

Per-diem (יומדמי) is a daily flat-rate expense allowance for travel and meals. Unlike regular receipt-based expenses, per-diem entries are not backed by physical receipts — the amount is computed from a tenant-configured daily rate multiplied by the number of travel days or partial days.

### Per-diem expense category

Per-diem expenses use category `'travel'` with a sub-type flag `is_per_diem = true`. This allows them to appear in expense reports alongside regular travel expenses while being distinguishable for tax purposes.

### Schema delta

```sql
ALTER TABLE expenses ADD COLUMN is_per_diem        BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE expenses ADD COLUMN per_diem_days       NUMERIC(5,2);  -- e.g. 1.5 for partial day
ALTER TABLE expenses ADD COLUMN per_diem_rate_ils   NUMERIC(10,2); -- daily rate at time of entry

-- Per-diem rates configuration (per tenant, in tenant_settings):
ALTER TABLE tenant_settings ADD COLUMN per_diem_rates JSONB NOT NULL DEFAULT '{
  "domestic_full_day": 100,
  "domestic_half_day": 50,
  "international_full_day": 250,
  "international_half_day": 125
}'::jsonb;
-- Amounts in ILS. Defaults reflect typical Israeli freelancer guidelines.
-- Tenants can adjust these to match their accountant's recommendation or company policy.
```

### Per-diem entry form

"Log per-diem" button on the expense list page (separate from receipt upload):

```
┌────────────────────────────────────────────────────────────┐
│  Log per-diem expense                                      │
│                                                            │
│  Travel type    ● Domestic  ○ International               │
│  Duration       ● Full day  ○ Half day   ○ Custom          │
│                 Custom days: [1.5__]                       │
│  Date           [2026-06-01__]                             │
│  Project        [Acme Corp redesign ▾]  (optional)        │
│  Notes          [Business trip to Tel Aviv__________]     │
│                                                            │
│  Calculated amount: ₪100.00  (1 × ₪100/day)              │
│  (based on workspace per-diem rates — /settings/expenses)  │
│                                                            │
│  [Cancel]                              [Log expense]       │
└────────────────────────────────────────────────────────────┘
```

Amount is auto-calculated from `tenant_settings.per_diem_rates` by duration:
- **Full day** — full-day rate × number of full days (e.g. `domestic_full_day × 1`)
- **Half day** — the configured flat `*_half_day` rate (NOT `full_day_rate × 0.5`)
- **Custom** — applicable rate × `per_diem_days` (e.g. `domestic_full_day × 1.5`)

Rationale: `per_diem_rates` defines `*_half_day` as a flat allowance, not a proportional fraction of the full-day rate.

### API extension

```
POST /api/expenses/per-diem
     Auth: expenses:write
     body: {
       travel_type: 'domestic' | 'international',
       days: number,                // 0.5, 1, 1.5, etc.
       date: string,               // YYYY-MM-DD
       project_id?: string,
       notes?: string
     }
     Action:
       1. Read per_diem_rates from tenant_settings
       2. Compute amount = rate × days
       3. Create expense with is_per_diem = true, per_diem_days = days, per_diem_rate_ils = rate
       4. No OCR triggered; status = 'COMPLETED'; approval follows normal flow
     Response: { expenseId }
```

### Tax handling

Per-diem expenses are **not VAT-deductible** in Israel (no receipt, no input VAT). `vat_amount = 0`, `vat_deductible = false`. The PCN874 export (spec 17's VAT report) excludes per-diem rows from input VAT totals.

---

## API Endpoints

```
GET    /api/expenses                        → list (paginated, filterable)
POST   /api/expenses/upload                 → upload receipt file(s) → returns expense record(s)
GET    /api/expenses/:id                    → detail + corrections
PATCH  /api/expenses/:id                   → update fields (manual correction)
DELETE /api/expenses/:id                   → delete (soft)
POST   /api/expenses/:id/evaluate          → trigger re-evaluation via Claude
GET    /api/expenses/:id/file              → signed R2 URL for receipt (30min TTL)

GET    /api/expenses/reports/expense       → expense report data (filterable)
GET    /api/expenses/reports/vat           → PCN874 VAT summary data
GET    /api/expenses/reports/vendors       → vendor analysis
GET    /api/expenses/reports/expense/xlsx  → download Excel
GET    /api/expenses/reports/vat/xlsx      → download PCN874 Excel
```

---

## Webhooks

| Event | Payload |
|-------|---------|
| `expense.uploaded` | `{ expenseId, fileName, source }` |
| `expense.processed` | `{ expenseId, status, category, deductionPct, confidence }` |
| `expense.failed` | `{ expenseId, error }` |

---

## New Bindings / Infra

| Binding | Type | Purpose |
|---------|------|---------|
| `EXPENSE_QUEUE` | CF Queue producer → `expense-process` | Enqueue `{ type: 'expense.process', ... }` OCR pipeline jobs |
| `RATE_LIMITER_EXPENSE_UPLOAD` | CF native RateLimiter | 10 uploads/min per user |

> Add to Foundation Deltas.

---

## Architecture Decisions

| Decision | Choice | Reason |
|----------|--------|--------|
| Claude Vision for OCR | Not Azure/Google Vision | ANTHROPIC_API_KEY already required; Claude handles handwritten + printed receipts; single vendor |
| Claude Haiku for tax eval | Not Sonnet | Tax evaluation is rule-following, not reasoning; Haiku is faster + cheaper for high-volume |
| Async queue pipeline | Not sync in upload handler | OCR + LLM takes 3–10s; upload handler returns immediately; queue handles retries |
| 8 categories as types constant | Not DB table | Fixed by Israeli tax law; no tenant customization needed; simpler queries |
| Corrections table | Not just overwrite | Audit trail for tax purposes; future ML fine-tuning signal |
| Expense Report in Excel | Not PDF | Accountants work in Excel; RTL Hebrew support needed; Excel is standard for IL tax advisors |
| VAT on receipt = input VAT | Stored separately from totals | PCN874 requires separate input/output VAT figures; extract at OCR time |