Skip to main content

37. Invoice backfill (Wise history) + check-reconciliation invoice targeting

Date: 2026-06-16

Status​

Accepted

Context​

Invoice numbering was seeded at INV-0992 (migration 0015) because INV-0990/0991 were the last two manual Wise invoices at build time. That seed was a point-in-time snapshot: manual Wise issuance continued past it. The entire pre-app Wise catalogue is real, mostly-unpaid AR that isn't in the system. The owner wants it loaded as outstanding and paid down through the existing check-reconciliation pipeline (migration 0018) — not marked paid up front.

create_facility_invoice (0015/0016) is the forward path: it allocates the next sequential number, lands the invoice draft, and is built around live assignments + variance. It is the wrong tool for known-number, terminal-state history. The facilities and contractors on the historical invoices already exist in the app; per-contractor line detail comes from the Wise PDFs (+ a CSV export).

Decision​

  • Dedicated backfill RPC, not the forward path. backfill_invoice (SECURITY DEFINER, service-role, actor-attributed; migration 0046) inserts one historical invoice + its reconstructed lines in a single transaction. It takes the invoice's own number, lands it sent (outstanding) with the historical issue date (sent_at) and due date, amount_paid_cents = 0. It keeps the 0016 total = sum(lines) assertion and the per-facility+period active-invoice guard, and is idempotent on (company_id, invoice_number) (backed by the existing invoices_invoice_number_key unique index) so the import batch can be re-run safely — an already-loaded number is a no-op.
  • Backfill carries the live watermark up, it doesn't fight it. Each call advances settings.next_invoice_number to greatest(current, number + 1) — upward-only and order-independent, so forward generation resumes cleanly above the whole loaded catalogue. No magic constant is baked in; the watermark follows the data.
  • Provenance via invoices.origin ('app' default | 'backfill'). Existing rows and forward invoices stay 'app' (create_facility_invoice is unchanged — its inserts fall to the default); backfill sets 'backfill'.
  • No retroactive interest. Backfilled invoices are excluded from late-payment interest: the cron accrueOverdueInterest (src/server/services/cron.ts) filters origin <> 'backfill', and the on-demand accrueInvoiceInterest (src/server/actions/invoices.ts) refuses a 'backfill' invoice. The All-invoices tab hides the "Refresh interest" control and tags these rows Historical. They still read as outstanding/overdue in the UI (that is the AR picture) but never accrue interest that predates the app.
  • Reconstructed lines link to real entities. Each line carries the literal historical hours/rate/amount from the Wise invoice (the source of truth for the figure), links contractor_id and best-effort assignment_id (the version effective for that week; null if none). variance_flagged is always false; backfilled invoices are historical (old periods) so they never surface in the current weekly_billing_run window.
  • Originals preserved, not re-rendered. The import uploads each original Wise PDF verbatim to the private invoices bucket at <facility_id>/<number>.pdf and sets pdf_storage_path (same post-RPC step as generation).
  • Loaded directly from the already-extracted CSV — no AI re-extraction. The owner provided a Nightingale_Invoices_Summary.csv (clean number/dates/client/total) plus the original PDFs, so a one-time loader (scripts/backfill-invoices.mjs, run with --env-file=.env.local) parses the CSV, maps each client to its facility (alias match — the CSV "billed to" names don't string-match the facility names), and calls backfill_invoice per row with one summary line = the CSV total; the original PDF is the line-level detail. Each PDF is uploaded to the invoices bucket so it is viewable in-app. The loader skips the one row the CSV self-labels non-Nightingale (a personal Healthspan membership). Review and correction happen after the load, in the invoices tab (below), rather than in a pre-load wizard.
  • Check reconciliation gains invoice-number targeting (migration 0047 adds check_payments.ocr_invoice_refs). The check extraction additionally captures any invoice number(s) named on the check/memo/remittance, persisted to ocr_invoice_refs. Allocation rule: if a referenced number matches an outstanding invoice for the check's facility, move it to the front of the oldest-first list, then run the existing pure allocateCheck unchanged; any remainder spills oldest-first. Targeting is just list ordering — the tested matcher is untouched.

Status of this change​

  • Done: migrations 0046 + 0047 (applied), db/types.ts updates, interest-exclusion wiring (cron + on-demand + UI), and the catalogue load itself — 77 invoices loaded (INV-002 → INV-1005), 77 PDFs stored/viewable, $79,894.06 outstanding AR across 5 facilities, watermark advanced to 1006. The invoices tab gained Correct / Resend / Void / Delete row actions (InvoiceActions) for post-load management. One duplicate-number CSV row (a second INV-0985) didn't load (number already used) and is flagged for the owner to renumber/re-add via Correct.
  • Pending: the check-targeting wiring (extend the check-extract schema to populate the check_payments.ocr_invoice_refs column added by 0047, then front-load matched refs before allocateCheck) — the column exists; the wiring lands when check remittances motivate it.

Consequences​

  • Run the backfill before relying on forward numbering for the historical range. The watermark sits at 993 today; the import advances it above the loaded catalogue as it runs.
  • A pre-existing app-issued number that collides with the historical catalogue must be cleared first. The lone test draft INV-0992 (origin app) occupies a number in the Wise range; because invoice_number is uniquely indexed, the genuine historical INV-0992 would no-op. It is discarded immediately before the load (owner-confirmed) so the real one lands.
  • Backfilled invoices flow through the existing payment lifecycle: a reconciled check flips them partial/paid and writes check_payment_applications exactly like a native invoice — full audit trail.
  • due_date and sent_at are required on every backfilled row: outstanding-invoice ordering is by due date (nulls last), so a missing due date would break oldest-first.
  • Assumes each historical Wise invoice is one facility / one week / multiple per-contractor lines. If any billed a facility twice in a week, the period guard rejects the duplicate for manual handling.
  • src/db/types.ts is hand-maintained in this repo (no supabase gen types pipeline is wired), so the new column/function types were edited in directly.

Migrations: 0046 (backfill RPC + invoices.origin), 0047 (check_payments.ocr_invoice_refs).