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 `balance_due`

balance_due on an invoice is the amount still owed. It is computed and written exclusively by the database trigger fn_recalculate_invoice_payment_state:

balance_due = total - SUM(invoice_payment_applications.amount)

The Stripe sync must not write balance_due directly. This rule exists because Stripe and Financica's 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 the sync used to write balance_due = stripeInvoice.amount_remaining, it 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 balance sheet was silently wrong.

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

Stripe payment types and how each is handled

The Stripe InvoicePayments API (stripe.invoicePayments.list) returns one record per payment event applied to an invoice. Each record has a payment.type field:

`charge` / `payment_intent` -- real Stripe-collected payments

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 will be 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.

`out_of_band` -- payments recorded outside Stripe

These payments were marked as paid in Stripe's dashboard or via invoice.pay({ paid_out_of_band: true }) in the API. No Stripe charge occurred. No payout will arrive. No bank transaction will be imported.

Examples of real-world scenarios:

The customer paid by cash or cheque and the user clicked "Mark as paid" in Stripe.
The user recorded an external bank transfer in Stripe without going through Stripe's payment processing.
The user wanted to close the invoice in Stripe for workflow reasons, even if no payment has been or will be received.

The problem: there is no bank transaction to import, so the regular auto-link path does not apply. Yet the invoice obligation must be cleared in the ledger.

Reconciliation path: The sync detects out_of_band → creates a synthetic clearing transaction → creates an invoice_payment_application → trigger fires → balance_due is correct. See the clearing account section below.

The Stripe External Payments clearing account

When an out-of-band payment is detected, the sync creates a synthetic transaction using 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 out-of-band payment that has not yet been reconciled with a real bank transaction.

Non-zero balance = unreconciled money: the books say money was exchanged via Stripe's out-of-band 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 has not been imported yet -- the balance will clear when it is.
2. The user marked the invoice as paid informally without collecting any money -- the balance will not clear unless the user writes it off properly.
Zero balance = fully reconciled: every out-of-band entry has a matching bank transaction. Books are clean.

Reconciling the clearing account

When the bank deposit that corresponds to an out-of-band Stripe payment arrives and is imported, the user:

Opens the bank transaction in Financica.
Categorises the relevant leg to "Stripe External Payments" instead of a revenue or expense account.
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.

Transaction status: `pending`

Synthetic clearing transactions created for out-of-band payments are created with status = "pending". This is intentional: they represent a known payment 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.

The auto-link guard

maybeAutoLinkStripeInvoicePayment has a guard that prevents running if the invoice is already covered. The guard was historically:

if ((existingApplications || []).length > 0) return;

This was wrong. It bails out if any application exists, including a credit note netting application that covers only part of the invoice. The correct check is:

const alreadyApplied = existingApplications.reduce((s, a) => s + Number(a.amount), 0);
if (alreadyApplied >= invoiceTotal - AMOUNT_MATCH_EPSILON) return;

Bail only when the invoice is already fully covered. If a credit note netting covers €1,000 of a €3,000 invoice, the auto-link should still run and link the €2,000 bank transaction.

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 out-of-band payments, idempotency is achieved via the source_reference field on transaction legs:

source = "stripe_out_of_band"
source_reference = stripePaymentId (the InvoicePayment.id from Stripe)

Before creating a clearing transaction, the sync checks whether an invoice_payment_application already exists whose leg has these values. If yes, the payment has already been processed and the step is skipped.

For the auto-link guard, idempotency comes from the sum check: once the invoice is fully covered, the guard triggers and subsequent runs are no-ops.

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)
2. Stripe sync: upserts invoice metadata (total, line items, counterparty).
   Does NOT write balance_due.
   fn_recalculate fires: balance_due = 3,000 (no applications yet).

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

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

5. maybeAutoApplyOutOfBandStripeInvoicePayment:
   - Detects out_of_band payment of €2,000
   - No existing application for this stripePaymentId → proceed
   - 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 = 2,000 - 2,000 = 0

6. Invoice now shows:
   - Linked €3,000 (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 out-of-band sync

If the bank import runs and the user manually links the bank transaction to the invoice (before the Stripe sync has processed the out-of-band event), then when the sync runs it will find alreadyApplied >= invoiceTotal and skip creating 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 problem

Removing balance_due from the Stripe upsert means existing invoices where balance_due = 0 was only set by the direct write (no invoice_payment_applications) will now show as unpaid. The magnitude of this depends on how many invoices fall into this category.

A backfill is needed but is not run automatically by the migration. It should be run manually after validating the new sync behaviour in production. The backfill:

Finds all Stripe invoices where balance_due = 0 (or near zero) and SUM(invoice_payment_applications.amount) < total.
For each, runs maybeAutoApplyExactStripeInvoicePayment (if a matching bank transaction exists) or maybeAutoApplyOutOfBandStripeInvoicePayment (if the Stripe invoice shows paid_out_of_band = true).
Any remaining gaps are reported for manual review.

Until the backfill is run, affected invoices will show a non-zero balance_due even though Stripe considers them paid. This is the honest state -- it shows that the payment is unreconciled in the ledger.

The fundamental rule: the ledger owns balance_due