# Expense Approval Workflow — Implementation Plan **Spec:** docs/specs/2026-05-31-expense-approval-workflow.md · **Slug:** expense-approval-workflow · **Wave:** 11 **Depends on:** expense-settings-ui, expenses-module, foundation-auth-rbac, system-communications-notifications ## Goal Add a multi-step approval gate for expenses whose ILS-normalized gross `amount` exceeds the tenant-configured threshold (`tenant_settings.expense_approval_threshold_ils`, from spec 61). Expenses above threshold enter `approval_status = 'pending'` on creation and are excluded from tax reports until approved. An OWNER or the configured approver works a dedicated queue at `/expenses/approvals`, approving or rejecting (rejection requires a reason). Approvers and submitters receive in-app and email notifications. The feature is gated to Business+ tier. ## Architecture This spec extends the existing `expenses` table (owned by `expenses-module`) with four inline approval columns — no separate table (one decision per expense). On expense creation/finalization, a hook evaluates `amount` against `tenant_settings.expense_approval_threshold_ils` and sets `approval_status` to `'pending'` (above threshold) or `'not_required'` (at/below threshold, or feature disabled by tier). The approval queue and decision endpoints live in the existing expenses route group in `apps/zync-api`. Decisions are gated by an `expenses:approve` permission plus an authorization check resolving `tenant_settings.expense_approver_role` (`any_admin` or a specific `user_id`). Notifications reuse `createNotification` + `deliverNotification` (in-app) and `sendEmail` (email) from `@zync/notifications`. Report consumers (spec 57 expense report, PCN874 VAT report) filter on `approval_status`. Upstream tables/columns consumed: - `expenses` (cols: `id`, `tenant_id`, `created_by`, `project_id`, `amount`, `vendor_name`, `expense_date`, `expense_category`, `deduction_pct`, `deduction_confidence`, `currency`, `status`, `r2_key`) — from `expenses-module`. - `tenant_settings.expense_approval_threshold_ils` (integer, default `0`) and `tenant_settings.expense_approver_role` (TEXT, `any_admin` | specific `user_id`) — from `expense-settings-ui` (spec 61). - `users(id)` — FK target for `approved_by`. - `tenant_memberships`, `roles`, `permissions`, `role_permissions` — for approver authorization (from `foundation-auth-rbac`). Upstream exports consumed: `authMiddleware`, `requirePermission`, `requireTier`, `requireModuleEnabled`, `tenantQuery`, `buildPaginated`, `createDb`, `createNotification`, `deliverNotification`, `sendEmail`, `seedPermissions`, `toast`, `Drawer`/`Sheet`, `DataTable`, `Button`, `Textarea`, `Badge`, `EmptyState`, `useDirection`, `LocaleProvider`. ## Tech Stack - **DB:** Neon Postgres via Cloudflare Hyperdrive; Drizzle ORM. Migration in `packages/db`. - **API:** `apps/zync-api` (Hono on Cloudflare Workers), Zod validation, existing expenses route group. - **App:** `apps/zync-app` (Vite + React), TanStack Query, `@zync/ui` components, i18n via `@zync/i18n` (`useDirection` for RTL). - **Packages:** `@zync/db` (schema + queries), `@zync/types` (shared types), `@zync/auth` (permission seed), `@zync/notifications` (delivery). - **Bindings:** Hyperdrive (Postgres), notification delivery bindings already wired by `system-communications-notifications`. ## Wave Plan | Sub-wave | Tasks | Files touched | Parallelizable? | |----------|-------|---------------|-----------------| | 11a | Task 1 (DB migration + Drizzle schema), Task 2 (permission seed) | `packages/db/migrations`, `packages/db/src/schema/expenses.ts`, `packages/auth/src/permissions.ts` | Task 1 & 2 parallel | | 11b | Task 3 (approval-status assignment hook + queries), Task 4 (types) | `packages/db/src/queries/expense-approvals.ts`, `packages/types/src/expense.ts` | After 11a; parallel with each other | | 11c | Task 5 (queue + decision API routes), Task 6 (notifications wiring) | `apps/zync-api/src/routes/expenses.ts` | After 11b; 6 depends on 5 | | 11d | Task 7 (report-exclusion filters) | `packages/db/src/queries/expense-reports.ts` | After 11b | | 11e | Task 8 (approval queue page), Task 9 (approval drawer) | `apps/zync-app/src/pages/expenses/approvals.tsx`, `apps/zync-app/src/components/expenses/ApprovalDrawer.tsx` | After 11c; 9 depends on 8 wiring | | 11f | Task 10 (settings tier-gate verification), Task 11 (i18n strings) | `apps/zync-app/src/pages/settings/expenses.tsx`, `packages/i18n/src/locales/*` | After 11e | ## Tasks ### Task 1: DB migration & Drizzle schema for approval columns **Blocks:** 3, 4, 5, 7 · **Blocked by:** — **Files:** - Create: `packages/db/migrations/_expense_approval_columns.sql` - Modify: `packages/db/src/schema/expenses.ts` **Steps:** - [ ] Add four columns to `expenses` via migration (canonical Postgres dialect below). - [ ] Add a partial index to back the pending-approval queue query (`tenant_id` + `approval_status = 'pending'`, ordered by `expense_date`). - [ ] Mirror the columns in the Drizzle `expenses` table definition (`text('approval_status')`, `uuid('approved_by')`, `timestamp('approved_at', { withTimezone: true })`, `text('approval_note')`), preserving the existing column set. - [ ] Export the updated `expenses` Drizzle table unchanged in name from `@zync/db`. **Schema / Interfaces:** ```sql ALTER TABLE expenses ADD COLUMN approval_status TEXT NOT NULL DEFAULT 'not_required' CHECK (approval_status IN ('not_required', 'pending', 'approved', 'rejected')); ALTER TABLE expenses ADD COLUMN approved_by UUID REFERENCES users(id); ALTER TABLE expenses ADD COLUMN approved_at TIMESTAMPTZ; ALTER TABLE expenses ADD COLUMN approval_note TEXT; -- rejection reason (required on reject) CREATE INDEX idx_expenses_approval_queue ON expenses (tenant_id, expense_date DESC) WHERE approval_status = 'pending'; ``` Drizzle (add to existing `expenses` pgTable, do not rename): ```typescript approvalStatus: text('approval_status').notNull().default('not_required'), approvedBy: uuid('approved_by').references(() => users.id), approvedAt: timestamp('approved_at', { withTimezone: true }), approvalNote: text('approval_note'), ``` **Acceptance:** - [ ] Migration applies cleanly on a Neon branch; `\d expenses` shows the four columns and the CHECK constraint. - [ ] `approval_status` is `NOT NULL DEFAULT 'not_required'` (never NULL) per the architecture decision. - [ ] Partial index `idx_expenses_approval_queue` exists. ### Task 2: Seed `expenses:approve` permission **Blocks:** 5 · **Blocked by:** — **Files:** - Modify: `packages/auth/src/permissions.ts` (the `seedPermissions` permission catalog) - Modify: role→permission mapping consumed by `seedSystemRoles` **Steps:** - [ ] Add permission key `expenses:approve` to the permission catalog seeded by `seedPermissions`. - [ ] Grant `expenses:approve` to the OWNER and ADMIN system roles in the `seedSystemRoles` role-permission map (final authorization still re-checks `expense_approver_role`; the permission is the coarse gate). - [ ] Ensure idempotent upsert (re-running the seed does not duplicate rows in `permissions` / `role_permissions`). **Schema / Interfaces:** Permission row shape (existing `permissions` table): `{ key: 'expenses:approve', description: 'Approve or reject expenses above the approval threshold' }`. **Acceptance:** - [ ] After seeding, `permissions` contains `expenses:approve`; `role_permissions` links it to OWNER and ADMIN roles. - [ ] `requirePermission('expenses:approve')` resolves for an OWNER session. ### Task 3: Approval-status assignment hook & approval queries **Blocks:** 5, 6, 7 · **Blocked by:** 1 **Files:** - Create: `packages/db/src/queries/expense-approvals.ts` - Modify: expense-creation/finalization query module in `packages/db/src/queries/expenses.ts` **Steps:** - [ ] Implement `resolveApprovalStatus({ tenantId, amount, tier, db })` → returns `'pending' | 'not_required'`. Logic: if tier is below Business+ → `'not_required'`. Else read `tenant_settings.expense_approval_threshold_ils`; if feature disabled (treat as not-configured row absent) → `'not_required'`; else if `amount > threshold` (note: threshold `0` means **all** expenses require approval, so `amount > 0`) → `'pending'`, else `'not_required'`. - [ ] Call `resolveApprovalStatus` from the expense-creation path (upload finalization, per-diem create, email/recurring intake) and persist the result into `expenses.approval_status`. For OCR-deferred expenses, set status at the point `amount` becomes known (after normalization); until then store `'not_required'` and re-evaluate when `amount` is finalized. - [ ] Implement `listPendingApprovals({ tenantId, status, cursor, limit, db })` returning a paginated expense list filtered by `approval_status = status` (default `'pending'`), ordered `expense_date DESC`, joined to `users` for submitter name. Use `tenantQuery` for tenant scoping and `buildPaginated` for the envelope. - [ ] Implement `approveExpense({ tenantId, expenseId, approverUserId, note, db })`: within a transaction, set `approval_status='approved'`, `approved_by=approverUserId`, `approved_at=now()`, `approval_note=note ?? NULL`; only transition from `'pending'`. Return the updated row. - [ ] Implement `rejectExpense({ tenantId, expenseId, approverUserId, reason, db })`: within a transaction, set `approval_status='rejected'`, `approved_by=approverUserId`, `approved_at=now()`, `approval_note=reason`; only transition from `'pending'`. Return the updated row. - [ ] Implement `isExpenseApprover({ tenantId, userId, db })`: returns true if user is OWNER, or `tenant_settings.expense_approver_role = 'any_admin'` and user is ADMIN, or `expense_approver_role` equals the user's `user_id`. **Schema / Interfaces:** ```typescript export type ApprovalStatus = 'not_required' | 'pending' | 'approved' | 'rejected'; export function resolveApprovalStatus(args: { tenantId: string; amount: number; tier: TenantTier; db: Db; }): Promise<'pending' | 'not_required'>; export function listPendingApprovals(args: { tenantId: string; status: ApprovalStatus; cursor?: string; limit: number; db: Db; }): Promise>; export function approveExpense(args: { tenantId: string; expenseId: string; approverUserId: string; note?: string; db: Db; }): Promise; export function rejectExpense(args: { tenantId: string; expenseId: string; approverUserId: string; reason: string; db: Db; }): Promise; export function isExpenseApprover(args: { tenantId: string; userId: string; db: Db; }): Promise; ``` **Acceptance:** - [ ] An expense created with `amount` above threshold on a Business+ tenant lands in `approval_status='pending'`; one at/below lands in `'not_required'`. - [ ] Threshold `0` forces every expense with `amount > 0` to `'pending'`. - [ ] On a non-Business+ tenant, `resolveApprovalStatus` always returns `'not_required'`. - [ ] `approveExpense`/`rejectExpense` are no-ops (no state change, surfaced as a conflict) when the row is not `'pending'`. ### Task 4: Shared types & serializer fields **Blocks:** 5, 8, 9 · **Blocked by:** 1 **Files:** - Modify: `packages/types/src/expense.ts` **Steps:** - [ ] Add `ApprovalStatus` union type and extend the `ExpenseObject` type with `approvalStatus`, `approvedBy`, `approvedAt`, `approvalNote`. - [ ] Add a `PendingApprovalRow` type (expense fields needed by the queue + submitter display name + AI confidence). - [ ] Extend the expense serializer so API responses include the approval fields (camelCase). **Schema / Interfaces:** ```typescript export type ApprovalStatus = 'not_required' | 'pending' | 'approved' | 'rejected'; export interface ExpenseApprovalFields { approvalStatus: ApprovalStatus; approvedBy: string | null; approvedAt: string | null; // ISO 8601 approvalNote: string | null; } export interface PendingApprovalRow { id: string; expenseDate: string | null; vendorName: string | null; amount: number | null; currency: string; expenseCategory: string | null; deductionPct: number | null; deductionConfidence: number | null; projectId: string | null; submittedById: string; submittedByName: string; approvalStatus: ApprovalStatus; isNew: boolean; // submitted within the last 24h, drives the ▲new badge } ``` **Acceptance:** - [ ] `ExpenseObject` exposes the four approval fields; all consuming packages type-check. - [ ] Serialized expense JSON includes `approvalStatus`, `approvedBy`, `approvedAt`, `approvalNote`. ### Task 5: Approval queue & decision API routes **Blocks:** 6, 8 · **Blocked by:** 2, 3, 4 **Files:** - Modify: `apps/zync-api/src/routes/expenses.ts` **Steps:** - [ ] `GET /api/expenses/approvals`: middleware chain `authMiddleware` → `requireModuleEnabled('expenses')` → `requireTier('business')` → `requirePermission('expenses:approve')`. Validate query (`status`, `cursor`, `limit`) with Zod. Authorize via `isExpenseApprover`; if false → 403. Call `listPendingApprovals`. Return `buildPaginated` envelope. - [ ] `POST /api/expenses/:id/approve`: same middleware chain. Zod body `{ note?: string }`. Authorize via `isExpenseApprover` → 403 if false. Call `approveExpense`; on non-pending row → 409. Return serialized expense. - [ ] `POST /api/expenses/:id/reject`: same middleware chain. Zod body `{ reason: string }` with `reason` required and non-empty (min length 1 after trim). Authorize via `isExpenseApprover`. Call `rejectExpense`; on non-pending row → 409. Return serialized expense. - [ ] Enforce tenant scoping on `:id` (expense must belong to the session tenant) before mutating. - [ ] Use timing-safe / parameterized queries only (no string interpolation of `:id`); all DB access through `@zync/db` query functions (no raw Drizzle in routes). **Schema / Interfaces:** ```typescript // Zod const approveBody = z.object({ note: z.string().max(2000).optional() }); const rejectBody = z.object({ reason: z.string().trim().min(1).max(2000) }); const approvalsQuery = z.object({ status: z.enum(['pending', 'approved', 'rejected']).default('pending'), cursor: z.string().optional(), limit: z.coerce.number().int().min(1).max(100).default(50), }); ``` Routes: `GET /api/expenses/approvals`, `POST /api/expenses/:id/approve`, `POST /api/expenses/:id/reject`. **Acceptance:** - [ ] Non-Business+ tenant gets 403/upgrade on all three routes (tier gate). - [ ] A user who is neither OWNER nor the configured approver gets 403. - [ ] `POST .../reject` with empty/missing `reason` returns 422 (Zod validation). - [ ] Approving a non-pending expense returns 409. - [ ] Cross-tenant `:id` returns 404. ### Task 6: Notification wiring for approval events **Blocks:** — · **Blocked by:** 5 **Files:** - Modify: `apps/zync-api/src/routes/expenses.ts` (or an `apps/zync-api/src/services/expense-notifications.ts` helper) **Steps:** - [ ] On expense entering `'pending'` (in the creation path, Task 3 hook): resolve the recipient approver(s) — the specific `expense_approver_role` user, or all ADMINs + OWNER when `any_admin`. Call `createNotification` (in-app) and `deliverNotification`; send email via `sendEmail` with subject/body: "Expense pending approval: ₪{amount} from {submitterName}". Respect `NotificationPreferences` (skip channels the recipient muted). - [ ] On approve: `createNotification` + `deliverNotification` to the submitter (`created_by`), in-app only: "Your expense of ₪{amount} was approved". - [ ] On reject: `createNotification` + `deliverNotification` (in-app) and `sendEmail` to the submitter: "Your expense of ₪{amount} was rejected: {reason}". - [ ] Format `{amount}` as ILS with thousands separators; localize strings (he/en) via the i18n catalog. - [ ] Notification dispatch must not block or fail the decision response — wrap in try/catch and log; the decision is already committed. **Schema / Interfaces:** Notification payloads use the upstream `createNotification` signature; `type` values: `expense_pending_approval`, `expense_approved`, `expense_rejected`. **Acceptance:** - [ ] Submitting an above-threshold expense delivers an in-app + email notification to the approver(s). - [ ] Approve delivers an in-app notification to the submitter (no email). - [ ] Reject delivers in-app + email to the submitter including the reason. - [ ] Muted channels per `NotificationPreferences` are skipped. ### Task 7: Report-exclusion filters **Blocks:** — · **Blocked by:** 3 **Files:** - Modify: expense-report query module (e.g. `packages/db/src/queries/expense-reports.ts`) used by `GET /api/expenses/reports/expense` and `GET /api/expenses/reports/vat` **Steps:** - [ ] Expense report (spec 57 Tab 1): exclude rows where `approval_status = 'rejected'`. Include `'pending'` rows but expose a flag so the UI can render the ⏳ indicator (`approval_status = 'pending'`). - [ ] PCN874 VAT report (spec 57 Tab 2): include only `approval_status IN ('approved', 'not_required')` — exclude `'pending'` and `'rejected'`. - [ ] Apply the same exclusion to the XLSX export queries (`/reports/expense/xlsx`, `/reports/vat/xlsx`). **Schema / Interfaces:** ```sql -- Expense report (Tab 1) WHERE expenses.tenant_id = $1 AND approval_status <> 'rejected' -- PCN874 VAT report (Tab 2) WHERE expenses.tenant_id = $1 AND approval_status IN ('approved', 'not_required') ``` **Acceptance:** - [ ] A `'rejected'` expense never appears in either report or its XLSX. - [ ] A `'pending'` expense appears in the expense report (flagged) but not in the PCN874 VAT report. - [ ] `'approved'` and `'not_required'` expenses appear in both reports. ### Task 8: Approval queue page (`/expenses/approvals`) **Blocks:** 9 · **Blocked by:** 5 **Files:** - Create: `apps/zync-app/src/pages/expenses/approvals.tsx` - Modify: expenses route registration + secondary nav (`apps/zync-app/src/pages/expenses/*` nav config) **Steps:** - [ ] Add route `/expenses/approvals`, visible in the expenses secondary nav only for Business+ tenants where the current user is an approver (OWNER or configured approver). Hide the nav entry otherwise. - [ ] Fetch via TanStack Query from `GET /api/expenses/approvals?status=pending`. Show a header "Expenses · Pending Approval" with a live `[{n} pending]` count badge. - [ ] Render a `DataTable` with columns: Date, Vendor, Amount (ILS-formatted), Submitted by. Show a `▲new` `Badge` on rows submitted within 24h (`isNew`). - [ ] Provide status tabs/filter (pending | approved | rejected) that re-query with the `status` param. - [ ] Row click opens the approval drawer (Task 9) for that expense. - [ ] Empty state via `EmptyState` ("No expenses pending approval"). - [ ] RTL: use `useDirection`; logical CSS properties; table header alignment follows direction. Honor `prefers-reduced-motion` for drawer/toast transitions. - [ ] a11y: table is a proper `role="table"` with header scope; the pending count is announced via a `role="status"` region; row activation works by keyboard (Enter/Space). **Acceptance:** - [ ] Approver sees the queue with live pending count; non-approver/non-Business+ user cannot see the nav entry or reach the route (redirect/403). - [ ] Status filter switches the list correctly. - [ ] Keyboard navigation opens the drawer; screen reader announces the count. ### Task 9: Approval drawer component **Blocks:** — · **Blocked by:** 8 **Files:** - Create: `apps/zync-app/src/components/expenses/ApprovalDrawer.tsx` **Steps:** - [ ] Build a `Sheet`/drawer showing: vendor + amount header, "Submitted by {name} · {date}", category (with deductible %), project name, AI confidence (`deductionConfidence` as %), and a "View receipt image" action that opens the signed R2 URL (`GET /api/expenses/:id/file`). - [ ] Optional notes `Textarea` (used for approval note or rejection reason). - [ ] `[Reject]` button: disabled until the notes field is non-empty; on click calls `POST /api/expenses/:id/reject` with `{ reason: notes }`. - [ ] `[Approve]` button: calls `POST /api/expenses/:id/approve` with `{ note: notes || undefined }`. - [ ] On success: show `toast`, invalidate the queue query, and auto-advance to the next pending item (close drawer if none remain). - [ ] On 409 (already decided by another approver): toast an explanatory message and refresh the queue. - [ ] RTL + reduced-motion honored on the drawer transition; focus is trapped in the drawer and returns to the triggering row on close. - [ ] a11y: drawer has `role="dialog"` + `aria-modal`, labelled by the expense title; Reject/Approve are real `