Stripe invoice payment reconciliation

This document covers the internal accounting model for Stripe-originated invoice payments. It is aimed at developers working on the Stripe sync or invoice payment system. For the general invoice payment flow, see Invoice posting and reconciliation.

---

The fundamental rule: the ledger owns the derived columns

balance_due and payment_status on invoices are computed and written exclusively by the database trigger fn_recalculate_invoice_payment_state:

balance_due = total - SUM(applications) payment_status = derived
from
	balance_due / total / applications

A BEFORE INSERT/UPDATE guard on invoices rejects any other write. The recalc function flips a session-local variable for its own UPDATE; every other code path is a hard SQL error.

This rule exists because Stripe and the Financica ledger are two different systems that track money for different reasons:

• Stripe tracks what it has billed and collected through its own payment flow. It does not know about bank imports, credit note nettings, partial payments from other sources, or anything the user records manually in Financica.
• The Financica ledger tracks every economic event as a double-entry journal entry. balance_due is derived from those entries, not from any external source.

When earlier versions of the sync wrote balance_due = stripeInvoice.amount_remaining, they created a phantom payment: the sub-ledger showed the invoice as settled, but there was no asset entry (no bank leg, no clearing account balance) to balance it. The badge said paid; the books had no journal entry. The guard makes that class of bug structurally impossible.

The invariant: every reduction in balance_due must have a corresponding ledger entry. There are no exceptions.

---

How Stripe can settle an invoice

The Stripe InvoicePayments API (stripe.invoicePayments.list) returns one record per payment event. The sync also reads two top-level fields on the Invoice itself (amount_remaining and starting_balance) which can reduce the amount owed without a corresponding InvoicePayment. The combined "what Stripe considers settled" is just total - amount_remaining.

From the bookkeeping side, only two cases matter:

1. The settlement maps to a real ledger entry. A Stripe charge with a balance transaction that was bank-imported, or a credit note that was posted. The auto-link path links it.
2. The settlement happens "somewhere outside our ledger". A paid_out_of_band marker, a customer-balance offset (starting_balance < 0), or a non-charge InvoicePayment (e.g. external_account) recorded via the new InvoicePayments API. We have no way to trace it back to a journal entry, so the sync parks the amount on a clearing account.

Real Stripe-collected payments — charge / payment_intent

These payments went through Stripe's payment infrastructure. Stripe created a charge and a balance transaction. The balance transaction will eventually appear as a payout to the organization's bank account and is imported into Financica as a bank transaction.

Reconciliation path: The bank transaction is imported → maybeAutoApplyExactStripeInvoicePayment finds it by balance_transaction_id → creates an invoice_payment_application → trigger fires → balance_due is correct.

If the bank transaction hasn't arrived yet, the auto-link links what it can and defers the rest. The clearing fallback covers the gap until the bank import catches up.

Everything else — the clearing-account fallback

This includes:

• paid_out_of_band: true on the Stripe Invoice (legacy "Mark as paid" marker).
• Customer-balance offsets (starting_balance < 0) — Stripe applied prior credit the customer had with you, and we don't have a corresponding credit-note record yet.
• New-API InvoicePayments with non-charge payment.type values (external_account and friends) — Stripe was told about the payment but doesn't have a charge or balance transaction.

Reconciliation path: maybeApplyStripeSettledRemainder computes the gap between total - amount_remaining and the sum of existing applications. Any positive remainder gets a synthetic clearing transaction; the control leg is linked to the invoice via invoice_payment_applications; the trigger derives balance_due and payment_status.

The function doesn't care which Stripe mechanism caused the settlement. It just looks at the gap.

---

The Stripe External Payments clearing account

When the gap-fill fires, the synthetic transaction lands on a dedicated clearing account called Stripe External Payments. This is a system-managed current asset account.

The journal entry

For an outbound invoice (revenue, where you are owed money):

DR  Accounts Receivable    +amount   ← clears the AR obligation
CR  Stripe External Payments -amount  ← records where the money "is"

For an inbound invoice (expense, where you owe money):

DR  Stripe External Payments +amount  ← records that you paid via out-of-band
CR  Accounts Payable         -amount  ← clears the AP obligation

The control leg (AR or AP side) is linked to the invoice via invoice_payment_applications. This is what causes the trigger to fire and update balance_due.

What the clearing account balance means

The Stripe External Payments account accumulates a balance for every Stripe-side settlement that hasn't been matched against a real bank transaction.

• Non-zero balance = unreconciled settlement: the books say money was exchanged via Stripe's external mechanism, but no matching bank transaction has been seen yet. This might mean:
  1. The payment was by cash/cheque/bank transfer and the bank statement hasn't been imported yet -- the balance will clear when it is.
  2. The user marked the invoice as paid informally without collecting any money, or Stripe applied customer credit. The balance won't clear unless the user books it explicitly (write-off, journal entry, manual reconciliation).
• Zero balance = fully reconciled: every external settlement has a matching ledger entry. Books are clean.

Reconciling the clearing account

When the bank deposit corresponding to an external Stripe settlement arrives and is imported, the user:

1. Opens the bank transaction in Financica.
2. Categorises the relevant leg to "Stripe External Payments" instead of a revenue or expense account.
3. The clearing account balance returns to zero; the bank account is credited.

If the organization uses Stripe payouts (Stripe collects the money, then pays it out to the bank), the payout transaction would also eventually reconcile against the Stripe External Payments balance for that period.

For customer-balance offsets specifically, there's no bank deposit to reconcile; the offset reduces the invoice without a real cash movement. A future enrichment pass over customers.balanceTransactions could convert these into proper credit applications, at which point the clearing leg would be unlinked. Until then, the balance sits as a known, honest discrepancy.

Transaction status: pending

Synthetic clearing transactions are created with status = "pending". They represent a known settlement event with an unknown physical cash location. They are real entries — they affect balance_due and appear in the linked transactions list — but they are flagged as pending to signal that reconciliation is incomplete.

---

Idempotency

The sync runs multiple times (webhooks, polling, manual triggers). Every step must be idempotent — running it twice must produce the same result as running it once.

For the bank-charge auto-link, idempotency comes from the application sum: once a charge's leg has been linked, the next run sees alreadyApplied >= chargeAmount and skips. The auto-link does not bail on partial coverage; it links what it can.

For the clearing fallback, idempotency comes from the gap formula:

target = min(invoiceTotal, stripeAmountSettled)
gap    = target - SUM(applications)

After a clearing transaction is created, the application it inserts adds to SUM(applications). The next run sees gap = 0 and skips.

---

Sequence diagram for a typical mixed-payment invoice

Scenario: €3,000 invoice, €1,000 credit note applied, €2,000 paid by external bank transfer recorded in Stripe as out-of-band.

1. Invoice created in Stripe (total = €3,000).
   Sync upserts the invoice. payment_status / balance_due are not written by
   the sync; the BEFORE INSERT trigger forces balance_due=3,000,
   payment_status='unpaid' from the total.

2. Credit note created, posted, and netting applied:
   - invoice_payment_applications: CN netting for €1,000
   - fn_recalculate fires: balance_due = 2,000

3. User marks remaining €2,000 as paid out-of-band in Stripe.
   Stripe webhook fires → sync runs.

4. maybeAutoApplyExactStripeInvoicePayment:
   - Lists InvoicePayments. paid_out_of_band has no `payment_intent` or
     `charge`, so no balance transaction → no linkable leg → no-op.

5. maybeApplyStripeSettledRemainder:
   - target = min(3,000, 3,000) = 3,000.
   - gap = 3,000 - 1,000 (existing CN application) = 2,000.
   - Creates synthetic transaction (status: pending):
       DR  AR  +2,000
       CR  Stripe External Payments  -2,000
   - Creates invoice_payment_applications for the AR leg.
   - fn_recalculate fires: balance_due = 0, payment_status = 'paid'.

6. Invoice now shows:
   - balance_due = 0, all accounted for by real ledger entries.
   - Linked transactions: CN netting €1,000 + External payment (Stripe) €2,000.
   - Stripe External Payments account: balance = -€2,000 (awaiting reconciliation).

7. Bank statement imported, €2,000 deposit found:
   - User categorises it to Stripe External Payments.
   - Stripe External Payments balance returns to zero.
   - Books are fully reconciled.

---

What happens if the bank transaction arrives before the clearing fallback

If the bank import runs and the user manually links the bank transaction to the invoice (before the Stripe sync has processed the settlement), then when the sync runs it will see gap <= 0 and skip the clearing entry. The bank transaction is the definitive entry and no clearing account pollution occurs.

This is the happy path: user acts quickly, books are clean from day one.

---

Backward compatibility and the backfill

Existing invoices stamped payment_status='paid' by the old direct-write code keep that label until something nudges the trigger. The scripts/backfill-invoice-payment-state.ts script walks every invoice, computes the expected state from applications, and calls fn_recalculate_invoice_payment_state on rows that disagree. Defaults to dry-run; pass --apply to write.

Until the backfill runs, affected invoices keep their stale label. After the backfill, those that have no real ledger entry behind the supposed settlement flip to unpaid or partially_paid. This is the honest state — it shows that the payment is unreconciled in the ledger and asks the user to either link a real bank transaction or write the amount off.