# Vendors / Suppliers (ספקים) — Implementation Plan **Spec:** docs/specs/2026-06-01-vendors-suppliers.md · **Slug:** vendors-suppliers · **Wave:** 11 **Depends on:** contractor-payouts, expense-reports-ui, expenses-module, foundation-auth-rbac, reports-analytics, settings-module, unified-attachments ## Goal Replace free-text expense suppliers with a first-class `vendors` entity. Add vendor CRUD (list + detail with Overview/Withholding/Expenses/Activity tabs), a type-ahead vendor picker on the expense form with inline create, expense→vendor linkage, and the consolidated supplier withholding report (Form 856) that unions contractors + vendors. Withheld amounts reuse the statutory-default rule from `tax_rates.withholding_default` already used by `contractor-payouts`. A non-destructive data migration backfills `vendors` from existing `expenses.vendor_name`. ## Architecture - New table `vendors` (tenant-scoped, soft-archived) plus a FK column `expenses.vendor_id`. A guarded FK column `recurring_expenses.vendor_id` is added only if that table exists (spec 172 may build after this one). - Reads/writes go through tenant-scoped data-layer helpers (the `tenantQuery`/`systemQuery` convention from `@zync/db`); routes never issue raw Drizzle (`no-raw-drizzle-from-routes`) and validate every body with Zod (`require-zod-validation-in-routes`). - Withholding-certificate files are stored via `unified-attachments` (`attachments` table, `entity_type = 'vendor'`, `POST /api/attachments`). The `vendors.withholding_cert_r2_key` column mirrors the contractor pattern (`contractors.withholding_certificate_r2_key`) and points at the latest uploaded cert's `r2_key`. - The withholding resolver reads `tax_rates` where `country_code = tenants.country_code` (IL), `tax_type = 'withholding_default'`, latest `effective_from <= expense_date` — identical to `contractor-payouts` (currently 0.30). This logic is centralized so both contractor and vendor reports call one function. - The 856 report endpoint extends the existing `/reports/withholding` (contractor) flow by unioning contractor rows with vendor-derived rows; per row a Form 857 certificate artifact is downloadable. - Permissions: CRUD requires `expenses:write`; the withholding report requires `reports:read`. Both via `requirePermission` + `authMiddleware`. Report is gated Business+ tier via `requireTier`. Consumed upstream tables/exports: `tenants` (`tenants.country_code`), `expenses`, `attachments`, `vat_rates`/`tax_rates`, `users`; helpers `authMiddleware`, `requirePermission`, `requireTier`, `tenantQuery`, `buildPaginated`, `clampLimit`, `DataTable`, `EmptyState`, `Sheet`, `Tabs`, `useDirection`, `toast`. ## Tech Stack - **API:** `apps/zync-api` (Hono on Cloudflare Workers), Drizzle against Neon Postgres via Hyperdrive binding `DB`. - **App:** `apps/zync-app` (Vite + React), TanStack Query hooks, `@zync/ui` components, RTL via `useDirection`. - **Packages:** `@zync/db` (schema + data layer), `@zync/types` (shared types), `@zync/ui`. - **Bindings:** `DB` (Hyperdrive→Neon), `STORAGE` (R2, via unified-attachments), `RATELIMIT_KV`. - **Libs:** `zod`, `drizzle-orm`, `exceljs` (or existing Excel helper used by contractor report) for 856 XLSX. ## Wave Plan | Sub-wave | Tasks | Files touched | Parallelizable? | |----------|-------|---------------|-----------------| | A — schema | 1, 2 | `packages/db/src/schema/vendors.ts`, expenses schema, migration SQL | No (2 after 1) | | B — data layer | 3, 4, 5 | `packages/db/src/vendors.ts`, withholding resolver | Yes (after A) | | C — API | 6, 7, 8 | `apps/zync-api/src/routes/vendors.ts`, attachments wiring, reports route | 6,7 parallel; 8 after 5 | | D — types | 9 | `packages/types/src/vendors.ts` | After 3 | | E — UI | 10, 11, 12, 13 | app pages/components/hooks | 10–13 parallel after C+D | | F — migration/backfill | 14 | data-migration script | After 1,2 | | G — cert expiry cron | 15 | cron route | After 6 | ## Tasks ### Task 1: `vendors` table + Drizzle schema **Blocks:** 2, 3, 6, 9, 14 · **Blocked by:** — **Files:** - Create: `packages/db/src/schema/vendors.ts` - Create: `packages/db/migrations/0XXX_vendors.sql` - Modify: `packages/db/src/schema/index.ts` (export `vendors`) **Steps:** - [ ] Write the canonical Postgres DDL (below) into the migration file. - [ ] Add the partial UNIQUE index on `(tenant_id, tax_id) WHERE tax_id IS NOT NULL`. - [ ] Add the `idx_vendors_tenant` index. - [ ] Mirror the table in Drizzle `pgTable` form; export `vendors`, `Vendor`, `NewVendor`. **Schema / Interfaces:** ```sql CREATE TABLE vendors ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE, name TEXT NOT NULL, tax_id TEXT, -- ח.פ. / ע.מ. withholding_rate NUMERIC(5,4), -- NULL = statutory default; 0 = exempt w/ valid cert withholding_cert_number TEXT, withholding_cert_expiry DATE, withholding_cert_r2_key TEXT, -- latest cert via unified-attachments default_category TEXT, -- prefills expense_category default_vat_deductible BOOLEAN NOT NULL DEFAULT true, payment_terms_days INTEGER NOT NULL DEFAULT 30, email TEXT, phone TEXT, address TEXT, notes TEXT, is_archived BOOLEAN NOT NULL DEFAULT false, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE UNIQUE INDEX uq_vendors_tenant_tax_id ON vendors (tenant_id, tax_id) WHERE tax_id IS NOT NULL; CREATE INDEX idx_vendors_tenant ON vendors (tenant_id, is_archived); ``` ```ts export const vendors = pgTable('vendors', { id: uuid('id').primaryKey().defaultRandom(), tenantId: uuid('tenant_id').notNull(), name: text('name').notNull(), taxId: text('tax_id'), withholdingRate: numeric('withholding_rate', { precision: 5, scale: 4 }), withholdingCertNumber: text('withholding_cert_number'), withholdingCertExpiry: date('withholding_cert_expiry'), withholdingCertR2Key: text('withholding_cert_r2_key'), defaultCategory: text('default_category'), defaultVatDeductible: boolean('default_vat_deductible').notNull().default(true), paymentTermsDays: integer('payment_terms_days').notNull().default(30), email: text('email'), phone: text('phone'), address: text('address'), notes: text('notes'), isArchived: boolean('is_archived').notNull().default(false), createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(), updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(), }); export type Vendor = typeof vendors.$inferSelect; export type NewVendor = typeof vendors.$inferInsert; ``` **Acceptance:** - [ ] Migration applies on Neon; `vendors` exists with the partial unique + tenant index. - [ ] Drizzle `vendors` import compiles and is re-exported from the schema barrel. ### Task 2: `expenses.vendor_id` FK + guarded `recurring_expenses.vendor_id` **Blocks:** 5, 8, 10, 14 · **Blocked by:** 1 **Files:** - Create: `packages/db/migrations/0XXX_expenses_vendor_id.sql` - Modify: `packages/db/src/schema/expenses.ts` (add `vendorId`) - Modify: `packages/db/src/schema/recurring_expenses.ts` (add `vendorId`, only if file exists) **Steps:** - [ ] Add `vendor_id` to `expenses` with `ON DELETE SET NULL`; keep `vendor_name`/`vendor_tax_id` (OCR capture + legacy). - [ ] Add `vendor_id` to `recurring_expenses` guarded by `IF EXISTS` table check (spec 172 may not be built yet). - [ ] Add the matching Drizzle columns; if `recurring_expenses` schema file is absent, skip its Drizzle edit and leave the SQL guard to handle it at migration time. **Schema / Interfaces:** ```sql ALTER TABLE expenses ADD COLUMN vendor_id UUID REFERENCES vendors(id) ON DELETE SET NULL; CREATE INDEX idx_expenses_vendor ON expenses (tenant_id, vendor_id) WHERE vendor_id IS NOT NULL; DO $$ BEGIN IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'recurring_expenses') THEN ALTER TABLE recurring_expenses ADD COLUMN IF NOT EXISTS vendor_id UUID REFERENCES vendors(id) ON DELETE SET NULL; END IF; END $$; ``` **Acceptance:** - [ ] `expenses.vendor_id` FK references `vendors(id)`, nullable, SET NULL on delete. - [ ] Migration succeeds whether or not `recurring_expenses` exists. ### Task 3: Vendor data-layer (CRUD + YTD spend) **Blocks:** 6, 8 · **Blocked by:** 1 **Files:** - Create: `packages/db/src/vendors.ts` - Modify: `packages/db/src/index.ts` (export functions) **Steps:** - [ ] Implement tenant-scoped functions using the `tenantQuery` helper (no raw Drizzle in routes). - [ ] `listVendors`: search by `name`/`tax_id` ILIKE, `archived` filter (default false), paginated via `clampLimit`/`buildPaginated`; LEFT JOIN-aggregate YTD spend = `SUM(expenses.amount)` for current calendar year linked by `vendor_id`. - [ ] `getVendorWithStats`: vendor row + linked-expense summary (count, total `amount`, total VAT). - [ ] `suggestVendors`: top 10 non-archived vendors matching `q` against `name` (prefix-weighted), returns `{ id, name, taxId, defaultCategory, defaultVatDeductible }`. - [ ] `createVendor`, `updateVendor`, `archiveVendor` (sets `is_archived = true`, bumps `updated_at`). - [ ] Enforce the `(tenant_id, tax_id)` uniqueness at the app layer with a friendly conflict before insert/update. **Schema / Interfaces:** ```ts export function listVendors(db: Db, tenantId: string, opts: { q?: string; archived?: boolean; limit?: number; cursor?: string }): Promise>; export function getVendorWithStats(db: Db, tenantId: string, vendorId: string): Promise; export function suggestVendors(db: Db, tenantId: string, q: string): Promise; export function createVendor(db: Db, tenantId: string, input: NewVendor): Promise; export function updateVendor(db: Db, tenantId: string, vendorId: string, patch: Partial): Promise; export function archiveVendor(db: Db, tenantId: string, vendorId: string): Promise; ``` **Acceptance:** - [ ] `listVendors` returns YTD spend computed from linked expenses for the current year. - [ ] Duplicate `(tenant_id, tax_id)` insert is rejected with a typed conflict, not a raw DB error. ### Task 4: Centralized withholding-default resolver **Blocks:** 5, 8 · **Blocked by:** — **Files:** - Create or Modify: `packages/db/src/withholding.ts` **Steps:** - [ ] Implement `resolveWithholdingDefault(db, countryCode, asOf)` reading `tax_rates` where `tax_type = 'withholding_default'`, `country_code = countryCode`, `effective_from <= asOf`, ordered `effective_from DESC LIMIT 1`; returns the `rate` (currently 0.30 for IL). - [ ] Implement `effectiveWithholdingRate(vendorOrContractorRate, hasValidCert, statutoryDefault)`: returns `0` when exempt with a valid (non-expired) cert and `rate = 0`; returns the configured rate when non-null; else returns `statutoryDefault` when rate is NULL. - [ ] Implement `computeWithheld(amount, rate)` = `ROUND(amount * rate, 2)` (mirrors `contractor-payouts`). - [ ] If `contractor-payouts` already exports an equivalent resolver, re-export it here instead of duplicating, to keep one code path. **Schema / Interfaces:** ```ts export function resolveWithholdingDefault(db: Db, countryCode: string, asOf: Date): Promise; export function effectiveWithholdingRate(configuredRate: number | null, hasValidCert: boolean, statutoryDefault: number): number; export function computeWithheld(amount: number, rate: number): number; ``` **Acceptance:** - [ ] For IL with `asOf >= 2012-01-01` the default resolves to 0.30. - [ ] A vendor with `withholding_rate = 0` and a valid cert yields effective rate 0; with NULL rate yields the statutory default. ### Task 5: Per-expense withheld computation at payment **Blocks:** 8 · **Blocked by:** 2, 4 **Files:** - Modify: `packages/db/src/expenses.ts` (or wherever expense payment is recorded) **Steps:** - [ ] When an expense linked to a vendor is marked paid, compute the withheld amount using `effectiveWithholdingRate(vendor.withholding_rate, validCert, resolveWithholdingDefault(db, tenant.country_code, expense.expense_date))` and `computeWithheld(expense.amount, rate)`. - [ ] Treat a cert as valid when `withholding_cert_expiry IS NULL OR withholding_cert_expiry >= expense_date`. - [ ] Surface the withheld amount through the expense read model so the 856 report can aggregate it (no new column required if computed on read; if the project records snapshots, store `withheld_amount` on the expense — follow the contractor-payouts snapshot convention). **Acceptance:** - [ ] A paid vendor expense reports a withheld amount consistent with the resolver and the vendor's rate/cert state. ### Task 6: Vendor CRUD API routes **Blocks:** 10, 11, 12, 13, 15 · **Blocked by:** 3 **Files:** - Create: `apps/zync-api/src/routes/vendors.ts` - Modify: `apps/zync-api/src/index.ts` (mount router) - Create: `apps/zync-api/src/schemas/vendors.ts` (Zod) **Steps:** - [ ] Mount under `authMiddleware`; each mutation `requirePermission('expenses:write')`, reads `requirePermission('expenses:read')` (list/detail/suggest readable to expenses readers). - [ ] Implement the routes below, all delegating to Task 3 data-layer functions. - [ ] Validate bodies/queries with Zod (`createVendorSchema`, `updateVendorSchema`); reject extra fields. - [ ] Serialize vendors via `serializeVendor`; never leak `tenant_id`. - [ ] Apply `rateLimit` on `POST /api/vendors`. **Schema / Interfaces:** ``` GET /api/vendors → PaginatedResponse (q, archived, cursor, limit); includes ytdSpend [expenses:read] POST /api/vendors → Vendor [expenses:write] GET /api/vendors/:id → VendorWithStats (vendor + linked-expense summary) [expenses:read] PATCH /api/vendors/:id → Vendor [expenses:write] POST /api/vendors/:id/archive→ Vendor (is_archived=true) [expenses:write] GET /api/vendors/suggest?q= → VendorSuggestion[] (type-ahead, max 10) [expenses:read] ``` ```ts export const createVendorSchema = z.object({ name: z.string().min(1).max(200), taxId: z.string().max(20).optional(), withholdingRate: z.number().min(0).max(1).nullable().optional(), withholdingCertNumber: z.string().max(60).optional(), withholdingCertExpiry: z.string().date().optional(), defaultCategory: z.string().max(40).optional(), defaultVatDeductible: z.boolean().optional(), paymentTermsDays: z.number().int().min(0).max(365).optional(), email: z.string().email().max(254).optional(), phone: z.string().max(40).optional(), address: z.string().max(500).optional(), notes: z.string().max(2000).optional(), }); export const updateVendorSchema = createVendorSchema.partial(); ``` **Acceptance:** - [ ] All six routes return correct status codes and enforce the listed permissions. - [ ] `GET /api/vendors/suggest?q=aws` returns matching non-archived vendors only. ### Task 7: Vendor certificate upload wiring (unified-attachments) **Blocks:** 11 · **Blocked by:** 6 **Files:** - Modify: `apps/zync-api/src/routes/vendors.ts` (cert hook) - Modify: `apps/zync-api/src/routes/attachments.ts` (allow `entity_type = 'vendor'` access check) **Steps:** - [ ] Reuse `POST /api/attachments` with `entity_type='vendor'`, `entity_id=`; the attachments access check must confirm the vendor belongs to the caller's tenant (`expenses:write`). - [ ] After a successful cert upload, PATCH the vendor's `withholding_cert_r2_key` to the new `r2_key` (latest cert wins), mirroring `contractors.withholding_certificate_r2_key`. - [ ] Validate allowed MIME (pdf/jpg/png) per the attachments per-entity matrix for `vendor`. **Acceptance:** - [ ] Uploading a cert to a vendor stores the file in R2 and updates `vendors.withholding_cert_r2_key`. - [ ] Cross-tenant vendor upload is rejected `404 ENTITY_NOT_FOUND`. ### Task 8: Supplier withholding report (Form 856) endpoint **Blocks:** 13 · **Blocked by:** 3, 4, 5, 6 **Files:** - Modify: `apps/zync-api/src/routes/reports.ts` (or `apps/zync-api/src/routes/withholding.ts`) **Steps:** - [ ] Implement `GET /api/reports/withholding?year=YYYY` requiring `requirePermission('reports:read')` and `requireTier('business')` (Business+). - [ ] Union contractor withholding rows (from `contractor-payouts`) with vendor rows: include any vendor with `withholding_rate IS NOT NULL OR withheld amount > 0` over the year, grouped by vendor. - [ ] Per vendor compute `gross_paid` (= SUM linked-expense `amount` for the year), `withholding_rate` (effective), `withheld_amount` (via Task 4/5), plus `certificate_number`, `certificate_expiry`. - [ ] Return totals `total_gross`, `total_withheld` across contractors + vendors. - [ ] Offer per-row Form 857 certificate artifact download (`GET /api/reports/withholding/857/:type/:id?year=`), and the existing XLSX export with Hebrew headers for ITA filing. **Schema / Interfaces:** ```ts // GET /api/reports/withholding?year=2026 [reports:read, Business+] type WithholdingReport = { year: number; total_gross: number; total_withheld: number; rows: Array<{ source: 'contractor' | 'vendor'; entity_id: string; name: string; tax_id: string | null; gross_paid: number; withholding_rate: number; withheld_amount: number; certificate_number: string | null; certificate_expiry: string | null; // ISO date }>; }; ``` **Acceptance:** - [ ] Report unions contractors and qualifying vendors with correct withheld totals. - [ ] Non-Business tier receives a tier-gate error; missing `reports:read` receives 403. ### Task 9: Shared types **Blocks:** 10, 11, 12, 13 · **Blocked by:** 3 **Files:** - Create: `packages/types/src/vendors.ts` - Modify: `packages/types/src/index.ts` **Steps:** - [ ] Export `VendorListRow`, `VendorWithStats`, `VendorSuggestion`, `WithholdingReportRow`, `WithholdingReport`. **Schema / Interfaces:** ```ts export interface VendorListRow { id: string; name: string; taxId: string | null; withholdingRate: number | null; withholdingCertExpiry: string | null; ytdSpend: number; } export interface VendorWithStats extends VendorListRow { withholdingCertNumber: string | null; withholdingCertR2Key: string | null; defaultCategory: string | null; defaultVatDeductible: boolean; paymentTermsDays: number; email: string | null; phone: string | null; address: string | null; notes: string | null; isArchived: boolean; expenseCount: number; expenseTotal: number; vatTotal: number; } export interface VendorSuggestion { id: string; name: string; taxId: string | null; defaultCategory: string | null; defaultVatDeductible: boolean; } ``` **Acceptance:** - [ ] Types compile and are imported by both API and app without circular deps. ### Task 10: Vendor list page `/vendors` **Blocks:** — · **Blocked by:** 6, 9 **Files:** - Create: `apps/zync-app/src/pages/vendors/VendorsListPage.tsx` - Create: `apps/zync-app/src/hooks/useVendorList.ts` - Modify: app router + `/expenses` header (add "Vendors" link) **Steps:** - [ ] `useVendorList` wraps `GET /api/vendors` with TanStack Query (search debounce, archived toggle, cursor pagination). - [ ] Render `DataTable` columns: Name, Tax ID, Withholding, YTD spend. Withholding cell: `—` when none, `X% (cert✓)` when rate set with valid cert, `statutory default (30%)` when rate NULL. - [ ] Show ⚠ badge when `withholdingCertExpiry < now()+30d`; footer summary "N vendors · M certificate(s) expiring soon". - [ ] `[+ New vendor]` opens the create `Sheet` (Task 11); empty state via `EmptyState`. - [ ] RTL-aware via `useDirection`; numbers/currency formatted ILS; respect `prefers-reduced-motion` on any transitions. **Acceptance:** - [ ] List renders YTD spend, withholding column states, and the expiring-cert badge per the spec mock. - [ ] Reachable from both `/vendors` and the `/expenses` header. ### Task 11: Vendor detail page `/vendors/:id` (tabs) **Blocks:** — · **Blocked by:** 6, 7, 9 **Files:** - Create: `apps/zync-app/src/pages/vendors/VendorDetailPage.tsx` - Create: `apps/zync-app/src/hooks/useVendor.ts` - Create: `apps/zync-app/src/components/vendors/VendorWithholdingTab.tsx` **Steps:** - [ ] `useVendor` wraps `GET /api/vendors/:id`; mutation hook for PATCH + archive. - [ ] `Tabs`: **Overview** (contact + defaults editable form, Zod-validated), **Withholding** (rate, cert number, expiry, cert upload via `POST /api/attachments` entity_type `vendor`, expiry warning), **Expenses** (linked expenses list with totals), **Activity** (activity-timeline). - [ ] Archive action (confirm `Dialog`) calls `POST /api/vendors/:id/archive`; archived vendors become unlinkable in the picker. - [ ] Forms use `aria` labels; cert upload announces success via `toast`. **Acceptance:** - [ ] All four tabs render; cert upload persists and updates the expiry display. - [ ] Editing defaults and archiving work and reflect in the list. ### Task 12: Expense form vendor type-ahead + inline create **Blocks:** — · **Blocked by:** 6, 9 **Files:** - Modify: `apps/zync-app/src/pages/expenses/ExpenseForm.tsx` - Modify: OCR review component (`expense-ocr-correction-ux`) - Create: `apps/zync-app/src/components/vendors/VendorTypeahead.tsx` **Steps:** - [ ] `VendorTypeahead` queries `GET /api/vendors/suggest?q=` (debounced); selecting a vendor sets `vendor_id` and inherits `defaultCategory` → `expense_category` and `defaultVatDeductible` → `vat_deductible`. - [ ] On no match show "Create vendor 'X'" inline; create via `POST /api/vendors` prefilling `name` from query and `tax_id` from OCR `vendor_tax_id`, then link. - [ ] Keep `vendor_name`/`vendor_tax_id` free-text as raw OCR capture; `vendor_id` is the linked entity when matched. - [ ] Keyboard-navigable combobox with `role="combobox"`/`aria-expanded`; RTL aware. **Acceptance:** - [ ] Selecting a vendor sets `vendor_id` and prefills category + VAT-deductible defaults. - [ ] Inline create makes a vendor (prefilled tax_id from OCR) and links it without leaving the form. ### Task 13: Withholding report UI `/reports/withholding` **Blocks:** — · **Blocked by:** 8, 9 **Files:** - Modify: `apps/zync-app/src/pages/reports/WithholdingReportPage.tsx` (extend contractor report) - Create: `apps/zync-app/src/hooks/useWithholdingReport.ts` **Steps:** - [ ] Add a year selector; fetch `GET /api/reports/withholding?year=`. Table unions contractor + vendor rows with `source` badge, gross paid, rate, withheld, cert number/expiry. - [ ] Show `total_gross` / `total_withheld` summary; nav label "Mas 856". - [ ] Per-row "Download 857" link to the certificate artifact endpoint; "Export 856 (Excel)" button. - [ ] Gate the page behind Business+ (`useTierGate`); show upsell when below tier. Numbers formatted ILS, RTL aware. **Acceptance:** - [ ] Report shows contractors and vendors together with correct totals. - [ ] 857 per-row download and 856 XLSX export work; page is Business+ gated. ### Task 14: Backfill data migration (vendor_name → vendors) **Blocks:** — · **Blocked by:** 1, 2 **Files:** - Create: `packages/db/migrations/0XXX_backfill_vendors.sql` (or `apps/zync-api/src/scripts/backfill-vendors.ts`) **Steps:** - [ ] Group existing `expenses` per tenant by normalized `vendor_name` (trim, collapse whitespace, lowercase for matching; keep first-seen original casing as `name`). - [ ] For each non-blank group, insert a `vendors` row (carry `vendor_tax_id` → `tax_id` when consistent within the group), then `UPDATE expenses SET vendor_id = WHERE` normalized name matches and `vendor_id IS NULL`. - [ ] Leave ambiguous/blank names unlinked (free text preserved). Non-destructive — never clear `vendor_name`/`vendor_tax_id`. - [ ] Idempotent: re-running does not create duplicate vendors (guard on `(tenant_id, lower(name))` / existing `vendor_id`). **Acceptance:** - [ ] After backfill, distinct vendor names become `vendors` rows and matching expenses carry `vendor_id`. - [ ] Blank/ambiguous expenses remain unlinked with free text intact; re-run is a no-op. ### Task 15: Vendor certificate-expiry warning cron **Blocks:** — · **Blocked by:** 6 **Files:** - Modify: `apps/zync-api/src/routes/cron.ts` (or existing weekly cron handler) **Steps:** - [ ] Weekly, `CRON_SECRET`-protected handler selects `vendors WHERE withholding_cert_expiry IS NOT NULL AND withholding_cert_expiry < now() + INTERVAL '30 days' AND is_archived = false`. - [ ] For each, notify the relevant manager via `createNotification` (reuse an operational alert type) with body "Vendor {name}'s withholding certificate expires on {date}. Upload a new one." (Hebrew + English per i18n). - [ ] Mirror the contractor-payouts certificate-expiry cron behavior; share the helper if one exists. **Acceptance:** - [ ] Cron emits one notification per expiring-cert vendor and is rejected without the correct `CRON_SECRET`.