Scope: Per-facility Reconciliation Report

0. Corrections to the brief (found by reading the code)

• Wise is NOT facility money-in. src/server/services/wise/reconcile.ts reconciles contractor payouts (payments table, PHP centavos, money OUT). The only facility money-in trails are: check_payment_applications rows, and the untracked manual delta on invoices.amount_paid_cents (recordInvoicePayment in src/server/actions/invoices.ts:132 bumps amount_paid_cents + sets paid_manually but writes no payment row — the manual amount is only recoverable as amount_paid_cents − Σ applications). The report must treat "manual" as a derived residual, not a ledger.
• Partial invoices CAN be voided (void_invoice in 0097_decouple_billing_lock.sql only blocks paid), and amount_paid_cents is preserved on void. deleteInvoice (src/server/actions/invoiceManage.ts:85) soft-deletes any status including paid. Both are exactly how "reconciled-then-erased" money happens.
• There is no facility login. Facility-facing surfaces are tokenized magic links (src/app/approve/[token]/page.tsx, approval_requests.token_hash via hashToken in src/lib/crypto/tokens). The facility view must be a tokenized read-only statement, or nothing. It is PR-4 below, gated on owner + security-reviewer sign-off (auth-adjacent, hard compliance gate in CLAUDE.md).

1. Reconciliation identity (one facility F, range [from, to], all integer cents)

Definitions:

liveInvoices(F)   = invoices where facility_id=F AND deleted_at IS NULL AND status <> 'void'
deadInvoices(F)   = invoices where facility_id=F AND (deleted_at IS NOT NULL OR status='void')
applied(i)        = Σ check_payment_applications.amount_applied_cents WHERE invoice_id = i
manualResidual(i) = amount_paid_cents(i) − applied(i)        -- derived; the only record of manual payments
leftover(c)       = check_payments.amount_cents(c) − Σ applications(c)   -- derived; gap (c) never persisted it

Per-invoice identity (must hold; a violation is itself a report row, badge "data issue"):

total_amount_cents(i) = amount_paid_cents(i) + outstanding(i),   outstanding(i) ≥ 0
amount_paid_cents(i)  = applied(i) + manualResidual(i),          manualResidual(i) ≥ 0

Per-check identity: amount_cents(c) = Σ applications(c) + leftover(c), leftover(c) ≥ 0 (a negative leftover = data issue).

Headline balance for the page banner:

BILLED    = Σ total_amount_cents   over liveInvoices(F) with sent_at::date in range, status <> 'draft'
RECEIVED  = Σ applied(i) + manualResidual(i)  over ALL invoices(F) — live AND dead (dead money is still received money)
MISSING   = Σ outstanding(i)  over liveInvoices(F), status in (sent, partial, overdue effective)
DEAD      = Σ amount_paid_cents over deadInvoices(F) where amount_paid_cents > 0
UNAPPLIED = Σ leftover(c) over confirmed checks touching F  +  Σ coalesce(amount_cents, ocr_extracted_cents, 0) over unconfirmed checks with facility_id = F

BILLED = RECEIVED_on_live + MISSING holds by construction; DEAD and UNAPPLIED are the leak buckets outside it.

Date-range predicate: an invoice is "in range" when coalesce(sent_at::date, period_end, created_at::date) is in range, but any invoice with outstanding > 0 is ALWAYS included regardless of range — a missing payment must never hide behind the date filter. Checks are in range by coalesce(check_date, submitted_at::date).

2. The four buckets — precise predicates

All company-scoped (company_id = $1) and facility-scoped as noted. Statuses use the effective status (effectiveInvoiceStatus in src/lib/invoice/status.ts) for display, stored status for predicates.

B1 — Sent, still owed ("still missing payment")

SELECT ... FROM invoices
WHERE facility_id = $2 AND deleted_at IS NULL
  AND status IN ('sent','partial','overdue')
  AND total_amount_cents - amount_paid_cents > 0

Note this deliberately differs from outstandingInvoicesForFacility (src/server/services/checks.ts:35) by adding deleted_at IS NULL — do NOT reuse that helper (gap a lives in it); the report must not inherit the bug.

B2 — Payments applied to a dead invoice (payment-side view)

SELECT a.*, c.check_number, c.check_date, c.confirmed_at, i.invoice_number, i.status, i.deleted_at
FROM check_payment_applications a
JOIN invoices i ON i.id = a.invoice_id
JOIN check_payments c ON c.id = a.check_payment_id
WHERE i.facility_id = $2 AND (i.deleted_at IS NOT NULL OR i.status = 'void')

Plus manual money on dead invoices: dead invoices where amount_paid_cents > applied(i) (manual residual with no application row at all).

B3 — Received but not (fully) applied Three sub-rows, distinguished in the UI:

-- (i) leftover on confirmed checks (gap c made this invisible):
SELECT c.id, c.amount_cents - COALESCE(SUM(a.amount_applied_cents),0) AS leftover_cents
FROM check_payments c LEFT JOIN check_payment_applications a ON a.check_payment_id = c.id
WHERE c.status = 'confirmed'
GROUP BY c.id HAVING c.amount_cents > COALESCE(SUM(a.amount_applied_cents),0)
-- facility attribution: c.facility_id = $2, OR (multi-facility) EXISTS an application to an F invoice
--   → then label the leftover "shared across payer group — needs review", don't claim it for F alone.

-- (ii) checks received but never applied:
SELECT ... FROM check_payments
WHERE facility_id = $2 AND status IN ('processing','needs_review','matched','partial','unmatched')

(iii) is (i) restricted to multi-facility payers = the gap-(d) held remainders that got confirmed anyway; same query, different label.

B4 — Reconciled-then-erased (invoice-side view of the same money)

SELECT ... FROM invoices i
WHERE i.facility_id = $2
  AND (i.deleted_at IS NOT NULL OR i.status = 'void')
  AND (i.amount_paid_cents > 0
       OR EXISTS (SELECT 1 FROM check_payment_applications a WHERE a.invoice_id = i.id))

B2 and B4 are the same money seen from the two sides; the page shows B4 in the invoice ledger (rows badged Void — had $X paid / Deleted — had $X paid) and B2 in the payments panel (rows badged applied to a dead invoice). Sum once (as DEAD), never twice. Also surface the supersession chain: replaces_invoice_id / replaces_invoice_number (0073_invoice_reissue_link.sql) so a payment sitting on a voided original links to its live replacement.

3. Why an invoice can be missing payment — causes the report distinguishes

Each B1 row gets one "likely cause" chip, derived from data the schema already has:

1. Never sent — status='draft' (listed separately, not in MISSING; it's not owed yet). "Never generated" (billing pause 0088 / weekly-run miss) is out of scope — the invoicing cockpit already surfaces facility-weeks without invoices.
2. Sent but email never delivered — email_status IN ('failed','skipped') (0086); the facility may never have seen it. Chip: "email failed/not sent".
3. Payer unmapped — an unconfirmed check exists whose payer resolves to nothing (needs_review + needsFacilityPick); cash is sitting in B3(ii). Chip on the B3 row, with link to /admin/checks/payers.
4. Check unreadable / awaiting review — check_payments.status IN ('needs_review','unmatched','processing'), ocr_extracted_cents IS NULL or low ocr_confidence. B3(ii) row.
5. Confirmed but likely misallocated to a sibling — pre-8a39d34 greedy matcher. Detectable: a check_payment_applications row whose check's stored remittance_lines (post-confirm they carry matched_invoice_id + match_basis, see mergeLineMatches in src/server/actions/checks.ts) contain a line whose invoice_ref normalizes to a DIFFERENT invoice number than the one the application hit — or historical rows with no match_basis at all confirmed before the fix date. Chip: "allocation predates ref-first matcher — verify". (This is the report-only surfacing of the open rematch-backfill item; no auto-correct.)
6. Reissued / superseded — the payment sits on the voided original (replaces_invoice_id chain) while the replacement invoice shows unpaid, or vice versa. Chip: "superseded by INV-XXXX" / "replaces INV-XXXX".
7. Voided after partial payment — B4, status='void' AND amount_paid_cents>0.
8. Soft-deleted with payments — B4, deleted_at IS NOT NULL (gap b orphans).
9. Overpayment swallowed — the check that paid it had leftover > 0 (gap c); the facility believes it paid more than we applied. B3(i).

4. Page design

Route: src/app/admin/facilities/[id]/reconciliation/page.tsx (server component, requireAccess('billing')). Linked from: Facility 360 header actions ("Reconciliation report →"), the invoices page facility filter, and /admin/checks. Not a tab inside Facility 360 — that page already eager-loads 10 reads into client Tabs; this is heavy, standalone, and deep-linkable (?from=&to=).

Default range: last 6 months (<input type="date"> GET form, exactly the /admin/reports hours-card pattern) — plus the always-include-owed rule from §1, stated in the UI ("open invoices are always shown").

Reused components: PageHeader, Section, Card, Table (with default stackAt='sm' — src/components/ui/Table.tsx already implements the mobile card-stack the guidelines require; the invoices page opts out with stackAt="none", this page must NOT), Badge, EmptyState, Alert, formatUsd/cents (src/lib/money), effectiveInvoiceStatus, InvoicePdfModal, FilePreview (check images).

┌─ Facility 360 › Reconciliation ────────────────────────────────────────────┐
│ Sunrise Care Center — Reconciliation report                                │
│ From [2026-02-05] To [2026-08-05] [Apply]      Data as of 2026-08-05 14:02 │
├────────────────────────────────────────────────────────────────────────────┤
│ ┌─Billed────┐ ┌─Received──┐ ┌─Missing───┐ ┌─On dead invoices┐ ┌─Unapplied┐ │
│ │ $124,500  │ │ $98,200   │ │ $26,300 ! │ │ $4,100 !        │ │ $1,900 ! │ │
│ └───────────┘ └───────────┘ └───────────┘ └─────────────────┘ └──────────┘ │
│  (! = warning badge + count; zero renders quiet/neutral, no red)           │
├─ Invoices (ledger) ────────────────────────────────────────────────────────┤
│ Number    Period      Sent      Total     Received  Outstanding  Status    │
│ INV-0954  Jun 2–8     Jun 10    $4,200    $4,200 ▸  $0          Paid       │
│   ▸ Check #<placeholder> · $4,200 · confirmed Jun 20 by <admin>            │
│ INV-0961  Jun 9–15    Jun 17    $4,200    $2,000 ▸  $2,200      Partial    │
│   ▸ Recorded manually · Jun 30 (no check on file)                          │
│ INV-0967  Jun 16–22   Jun 24    $4,200    $0        $4,200      Overdue    │
│                                            chip: [email failed]           │
│ INV-0940  May 26–…    Jun 3     $4,100    $4,100 ▸  —      Void · had $4,100 paid │
│                                            chip: [superseded by INV-0971] │
├─ Payments received ────────────────────────────────────────────────────────┤
│ Date     Check      Amount    Applied to           Flags                   │
│ Jun 20   #<num>     $8,400    INV-0954, INV-0961   —                       │
│ Jun 3    #<num>     $4,100    INV-0940             ⚠ invoice was voided    │
│ Jul 1    #<num>     $2,000    (none)               ⚠ awaiting review       │
│ Jun 20   #<num>     $500 of $8,900 unapplied       ⚠ leftover not applied  │
├─ Export ───────────────────────────────────────────────────────────────────┤
│ Download CSV →                                                             │
└────────────────────────────────────────────────────────────────────────────┘

• Provenance/trust (guidelines Part 3): every Received cell expands (<details>, semantic HTML) to its source rows: check number + confirmed_at + confirming admin, or "Recorded manually (paid_manually)" with updated_at, or "OCR read, unconfirmed" with ocr_confidence. Page header shows server render time as "Data as of". Never a bare number.
• States: loading.tsx with skeleton tiles + table skeleton (>300ms rule); the admin-level error.tsx boundary covers errors — but the page itself must degrade per-section (a failed applications read renders an inline Alert, not a dead page). Empty range → EmptyState "No invoices in this range — open invoices are always shown above" / "No payments received in this range".
• Color: status only via existing Badge tones + text (STATUS_TONE map pattern from the invoices page); warnings always icon/text + tone, never color alone. Amounts right-aligned (Td numeric).
• Mobile: default Table stacking (each row becomes a labeled card). No horizontal shrinking.

5. Who sees it

• Nightingale PM staff: the admin route, gated by requireAccess('billing') in the page (first line) — matching the invoices page's domain, not insights. The CSV route re-gates with requireDomain('billing'). No new middleware work: /admin/** is already behind admin auth; the domain check is the second verification CLAUDE.md requires.
• Facility (PR-4, deferred): public tokenized page /statement/[token] on the approve/[token] pattern (hashed single-purpose token, createServiceClient, no login). A facility user must NOT see: any other facility's invoices or names (a management-company check's total reveals sibling payments — show only the amount applied to THIS facility, never check_payments.amount_cents for multi-facility checks); contractor names, hours, or rates (statement shows invoice number/period/total only — no line items); internal flags (ocr_confidence, payer-mapping state, variance_flagged, notes); admin identities (say "confirmed Jun 20", not by whom). Boundary enforced where the data is fetched: the statement service takes (companyId, facilityId) resolved from the token row and selects only the whitelisted columns — there is no client-side filtering to get wrong. Facilities DO see their void/deleted invoices that carry payments — that is the point of the report.

6. Queries — existing vs. new

Existing helpers do not cover this (all current queries exclude dead invoices by status filter, and checks.ts helpers carry gap a). New code:

• src/server/services/facilityReconciliation.ts (server-only, createServiceClient, matching checks.ts precedent — this codebase's services own their queries; src/db/queries/ is a minority pattern):
  • facilityLedger(companyId, facilityId, from, to) → runs 3 reads concurrently:
    1. invoices: all statuses including void, including deleted (select ... where facility_id and company_id, range OR outstanding>0 — two queries unioned in TS is fine),
    2. applications: B2 query above (all applications to this facility's invoices, joined to check_payments),
    3. checks: unconfirmed with facility_id = $2, plus confirmed checks referenced by (2) with their SUM(applications) for leftover derivation (one grouped query over the check ids from (2) ∪ facility-attributed confirmed checks).
• src/lib/reconcile/ledger.ts (pure, no I/O — same split as target.ts/index.ts): takes those three row sets, returns { tiles, invoiceRows, paymentRows } with bucket classification, cause chips, identity-violation flags, all in integer cents. This is where every predicate in §1–§3 lives, and where the tests live.
• CSV: add case 'reconciliation' to src/app/admin/reports/[report]/route.ts (+ a card on the reports page with a facility <select>), reusing facilityLedger — totals in the CSV and page can never diverge because they are the same function.

7. Build plan (PR-sized units)

PR-1 — feat/facility-recon-ledger: pure ledger + service. Files: src/lib/reconcile/ledger.ts, src/server/services/facilityReconciliation.ts, tests/lib/reconcile/ledger.test.ts. Key test: a void invoice with amount_paid_cents > 0 lands in DEAD (and B4), not in MISSING; a confirmed check with amount_cents > Σ applications yields the exact leftover in cents in UNAPPLIED; and BILLED = RECEIVED_live + MISSING holds on a fixture containing all four buckets.

PR-2 — feat/facility-recon-page: the page. Files: src/app/admin/facilities/[id]/reconciliation/page.tsx, loading.tsx, one presentational component src/components/admin/FacilityReconLedger.tsx (client-free if possible — <details> expansion needs no JS), link additions in src/app/admin/facilities/[id]/page.tsx header + /admin/checks + invoices facility-filter banner. Key test: page guard — a non-billing-domain admin is redirected (mirror of existing guard usage); plus a render test that the dead-invoice badge and cause chip appear for a B4 fixture.

PR-3 — feat/facility-recon-csv: export. Files: src/app/admin/reports/[report]/route.ts, src/app/admin/reports/page.tsx, small formatter in src/server/services/reports.ts. Key test: CSV totals row equals facilityLedger tile totals for the same fixture (single source of truth).

PR-4 — feat/facility-statement-token (DEFERRED — owner decision + security-reviewer + human approval required): tokenized read-only facility statement reusing PR-1's service with the §5 column whitelist. Files: migration (statement tokens table), /statement/[token]/page.tsx, issue-link button on the recon page. Key test: a multi-facility check renders only the amount applied to the token's facility — never the check total or sibling invoice numbers.

Explicitly NOT building: fixes for gaps a–d (separate bugfix PRs; the report reads around them and surfaces their symptoms — fixing them must not change this page's queries); an unapply/reversal path (open item, prerequisite for acting on B2/B4 findings but not for seeing them); the pre-#105 rematch diff job (memory says report-only — bucket B1 cause-chip 5 surfaces the suspects); charts/graphs (four numbers + two tables answer the owner's question); pagination (per-facility volume is bounded — revisit only if a facility exceeds ~1k invoices); "never generated" detection (cockpit owns it).