Skip to main content

23. Check reconciliation

Date: 2026-06-06

Status​

Accepted

Context​

Facilities pay by mailed check. Step 13 ingests a check image, reads its amount, suggests how it clears the facility's outstanding invoices, and — only after the admin confirms — applies the payment. The build plan §3 flagged bugs in the spec's matcher. The schema already had check_payments (with a unique file_fingerprint for dedup and an ocr_extracted_cents field) and check_payment_applications.

Decision​

  • Pure matcher (src/lib/reconcile, tested): allocateCheck(checkCents, invoices) allocates the check across invoices in the given order (caller passes oldest-first), fully paying each until exhausted. Fixes §3: an invoice is fullyPaid iff the applied amount equals its outstanding balance (so a check that fully pays #1 and partially pays #2 marks #1 paid, #2 partial); all integer cents (no float compares); empty-list and already-paid invoices handled; the unapplied remainder is reported as leftover/credit.
  • Dedup: the uploaded file's SHA-256 is stored as file_fingerprint; a re-upload of the same bytes is rejected (pre-check + the unique constraint) so a payment can't be double-credited.
  • OCR (Claude, key already on file): extractCheckDetails reads amount + check number + payer + date + a self-rated confidence from the image/PDF, and is instructed to omit the routing and account numbers — we never store them (only the amount + metadata). Tolerant parse + Zod + one retry. OCR is best-effort and only pre-fills the amount; the admin confirms/corrects it.
  • Suggest, then confirm: the /admin/checks reconcile panel previews the allocation live in the browser (the same pure matcher) as the admin edits the amount. Confirm is owner-only; the action re-reads the check's facility, recomputes the allocation authoritatively server-side, and applies it via the confirm_check_reconciliation RPC.
  • Atomic apply: the SECURITY DEFINER RPC (service-role only, actor-attributed) inserts each check_payment_applications row and advances the invoice's amount_paid_cents + status (paid when cleared, else partial) under row locks, then marks the check confirmed — refusing an already-confirmed check.

Consequences​

  • Reconciliation reuses the invoice payment lifecycle (ADR-0022): a check applied here flips invoices to partial/paid exactly like a manually-recorded payment, and the dashboard outstanding updates.
  • The check image is sent to Claude (the accepted AI-processing posture, ADR-0008); routing/account numbers are omitted by the prompt and never persisted, satisfying the bank-data-minimization rule.
  • Deferred: the Gmail inbound check-ingest (DKIM/SPF/DMARC + human-review queue) — Step 12; the OCR prompt should be tuned against a real sample check (it gracefully falls back to manual amount entry until then). Overpayment leftover is surfaced but not auto-recorded as a credit.