# Payment Reconciliation **Date:** 2026-06-01 **Status:** Draft **Spec:** 157 **Tier:** All tiers **Depends on:** `invoices-core`, `partial-payment-recording`, `multi-currency`, `foundation-auth-rbac` **Referenced by:** `invoices-core`, `partial-payment-recording` --- ## Overview Spec 80 (`partial-payment-recording`) defines `invoice_payments` table for recording payments. Spec 53 (`invoice-payment-ux`) covers the customer-facing online payment page. Neither defines a reconciliation workflow: the process of matching bank-received payments to outstanding invoices when payment arrives outside the Zync payment flow (bank transfer, check, cash, non-integrated gateway). This spec defines: 1. **Payment recording UI** — staff records a received payment against one invoice 2. **Bulk reconciliation view** — `/invoices/reconcile` lists outstanding invoices alongside unmatched payments for matching 3. **Unmatched payments log** — staging area for bank receipts not yet matched to an invoice. Rows enter `unmatched_payments` from two sources: (a) staff manually via **[Add unmatched payment]**, and (b) `bank-statement-import` (spec 167) when a staff member clicks **"Send to reconcile"** on an unmatched incoming credit. Both surface identically in this view, so a bank-imported credit no longer dead-ends in the import screen. **Depends on / Referenced by:** also `bank-statement-import` (spec 167) — shared `unmatched_payments` table. --- ## Route `/invoices/reconcile` — requires `invoices:write`. --- ## Page Layout ``` ┌──────────────────────────────────────────────────────────────┐ │ Payments > Reconciliation │ │ │ │ [+ Record payment] [Import bank statement] │ │ │ │ ┌── Outstanding Invoices ──────────────────────────────┐ │ │ │ [Search customer...] [Sort: Oldest first ▾] │ │ │ │ │ │ │ │ INV-0042 Acme Corp ₪12,500 Due 5d ago SENT │ │ │ │ INV-0038 Beta Ltd ₪4,200 Due today SENT │ │ │ │ INV-0031 Gamma Inc ₪8,900 Due in 3d SENT │ │ │ │ INV-0029 Acme Corp ₪3,100 Due in 7d TAX_ISS │ │ │ │ │ │ │ │ [Record payment] per row │ │ │ └───────────────────────────────────────────────────────┘ │ │ │ │ ┌── Unmatched Bank Receipts ───────────────────────────┐ │ │ │ [+ Add unmatched payment] │ │ │ │ │ │ │ │ ₪12,500 from Acme Corp 2026-05-28 Bank transfer │ │ │ │ ₪4,200 unknown 2026-05-27 Bank transfer │ │ │ │ │ │ │ │ [Match to invoice] per row │ │ │ └───────────────────────────────────────────────────────┘ │ │ │ └──────────────────────────────────────────────────────────────┘ ``` **Outstanding invoices:** invoices with status `SENT`, `APPROVED`, `TAX_ISSUED`, or `PARTIALLY_PAID` that have `amount_paid < total`. --- ## Record Payment (from invoice row) **[Record payment]** → slide-in sheet: ``` ┌──────────────────────────────────────────────────────────────┐ │ Record Payment — INV-0042 Acme Corp │ │ Outstanding: ₪12,500 │ │ │ │ Amount received │ │ [₪ 12,500.00__________] │ │ │ │ Payment date │ │ [2026-05-28] │ │ │ │ Payment method │ │ [Bank transfer ▾] │ │ Bank transfer · Credit card · Check · Cash · Other │ │ │ │ Reference / transaction ID │ │ [TXN-20260528-001________] │ │ │ │ Notes │ │ [________________________] │ │ │ │ [Cancel] [Record payment] │ └──────────────────────────────────────────────────────────────┘ ``` On confirm: `POST /api/invoices/:id/payments` (spec 80). If `amount == outstanding balance` → status → `PAID`. If partial → status → `PARTIALLY_PAID`. --- ## Unmatched Payments An **unmatched payment** is a bank receipt recorded in Zync before the invoice is known (or when the payer's reference doesn't match any invoice). Stored in a new `unmatched_payments` table. ```sql CREATE TABLE unmatched_payments ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE, amount NUMERIC(12,2) NOT NULL, currency TEXT NOT NULL DEFAULT 'ILS', paid_at DATE NOT NULL, payment_method TEXT NOT NULL DEFAULT 'bank_transfer', reference TEXT, notes TEXT, payer_name TEXT, matched_to_invoice_id UUID REFERENCES invoices(id) ON DELETE SET NULL, matched_at TIMESTAMPTZ, matched_by UUID REFERENCES users(id) ON DELETE SET NULL, created_at TIMESTAMPTZ DEFAULT now() ); CREATE INDEX idx_unmatched_payments_tenant ON unmatched_payments(tenant_id, paid_at DESC); CREATE INDEX idx_unmatched_payments_unmatched ON unmatched_payments(tenant_id) WHERE matched_to_invoice_id IS NULL; ``` **[Add unmatched payment]** → same sheet as "Record payment" but without an invoice pre-selected. --- ## Match Unmatched Payment to Invoice **[Match to invoice]** on an unmatched payment → search modal: ``` ┌──────────────────────────────────────────────────────────────┐ │ Match ₪12,500 from "Acme Corp" to Invoice │ │ │ │ [Search invoice number or customer...] │ │ │ │ INV-0042 Acme Corp ₪12,500 SENT (exact match ★) │ │ INV-0038 Acme Corp ₪4,200 SENT │ │ │ │ [Cancel] [Match & record payment] │ └──────────────────────────────────────────────────────────────┘ ``` "Exact match ★" highlighted when `unmatched_payments.amount == invoice outstanding balance`. On confirm: 1. `POST /api/invoices/:id/payments` — records the payment 2. `PATCH /api/reconcile/unmatched/:id/match` — sets `matched_to_invoice_id`, `matched_at`, `matched_by` --- ## Sidebar Navigation Under Invoices (same sidebar section as spec 155): ``` Invoices All invoices Approvals (12) Reconcile ← this spec Receipts ← /receipts (spec 179) Drafts & Templates Recurring ``` **Receipts** links to `/receipts` (owned by `invoice-receipt-document`, spec 179). Visible to `invoices:read`. --- ## API ``` GET /api/invoices/outstanding → list invoices with outstanding balance (status in SENT|APPROVED|TAX_ISSUED|PARTIALLY_PAID AND amount_paid < total) Query: search, sort (oldest_due|newest_due|amount|customer), page, per_page Returns: [{ id, invoice_number, proforma_number, customer_name, total, amount_paid, outstanding, currency, due_date, status }] Requires: invoices:write GET /api/reconcile/unmatched → list unmatched payments (matched_to_invoice_id IS NULL) Returns: [{ id, amount, currency, paid_at, payment_method, reference, payer_name, notes }] Requires: invoices:write POST /api/reconcile/unmatched → record unmatched payment body: { amount, currency?, paid_at, payment_method, reference?, payer_name?, notes? } Requires: invoices:write PATCH /api/reconcile/unmatched/:id/match → match unmatched payment to invoice + record payment body: { invoice_id: string } Requires: invoices:write DELETE /api/reconcile/unmatched/:id → delete unmatched payment (only if not matched) Requires: invoices:write ``` Payment recording against a known invoice uses the existing `POST /api/invoices/:id/payments` from spec 80. --- ## Architecture Decisions | Decision | Choice | Reason | |----------|--------|--------| | `unmatched_payments` table | Not a flag on `invoice_payments` | Unmatched payments have no invoice FK yet; forcing a NULL `invoice_id` on `invoice_payments` would invalidate its index and business logic | | Reconcile as separate sub-route | Not a filter on `/invoices` | Reconciliation is a distinct workflow (bank-to-invoice matching); it needs a two-pane layout that doesn't fit the invoice list pattern | | Match → records `invoice_payments` row | Not a reference-only link | The matched payment must enter the spec 80 flow to trigger `PAID`/`PARTIALLY_PAID` status transitions and amount_paid denorm | | `payment_method` text enum | Not a FK table | Five methods (bank transfer, card, check, cash, other) are stable; a separate table adds a join without flexibility benefit |