Skip to content

Payments Import Flow

This is the canonical spec for how the payments CSV-import worker turns a parsed statement row into a payment + (optionally) a reconciliation against an existing transaction. It is the source of truth — anything in older notes or per-ticket docs that disagrees is stale.

Code entry point: packages/app/src/services/payment/server.ts:processPaymentImportRun (commit phase). The wizard's parse / resolve / review stages are upstream of this and out of scope here.

Pipeline overview

upload → parse → resolve → review → COMMIT (this doc)
                                    │
                                    ├── runImport (framework)
                                    │     ├── upload attachment
                                    │     ├── open import_runs row
                                    │     └── call processPaymentImportRows ←┐
                                    │                                        │
                                    └── finalise import_runs row + return    │
                                                                             │
processPaymentImportRows owns the per-row decisions described below. ────────┘

The framework owns: attachment upload, import_runs lifecycle, success / partial / failed status. The payments domain owns: per-row dispatch, summary roll-up, fee-row second pass.

Row inputs

Every row carries:

  • source_row_index — 1-based line number from the source file, for audit.
  • scheduled_date, amount, currency_code — the movement.
  • external_id — the provider's transaction id. Used as the dedupe key on re-imports AND as reference_number for the bank-flow transaction match.
  • card_last_four — present on card statements, used to route through the resolutions map.
  • description — mapped from the file; otherwise the worker derives "Imported from <file> — Line #<n>".
  • parent_external_id — set on fee rows; routes them through the second-pass loop.
  • kind — card or cash (the latter only on card-flow imports that include cash-withdrawal rows).

The Resolve step has already produced a resolutions map that maps (side, card_last_four) to either a payment method id (+ optional project_relationship) OR a skip decision.

Per-row decision table

For each parsed main row, after Resolve has decided the row's payment_method_id:

A. Cash-withdrawal rows (row.kind = CASH)

Always create a fresh Paid payment against a petty-cash float:

  • is_cash_float_bump = true
  • is_imported_match = true
  • source = PaymentSource.CSV_IMPORT
  • reconciled_at = paidAt, reconciled_by_user_id = userId (self-reconciling — no transaction or allocation needed; the float assignment + the bump IS the audit)
  • payment_methods.amount is bumped by row.amount on the float.

Outcome: PaymentsImportOutcomeAction.CASH_BUMP.

B / C. Card-statement (kind=CARD card flow) and Bank-statement rows (bank flow)

Both follow a transaction-first algorithm. They differ only in WHICH transaction match helper they call:

  • Card → findTransactionMatchCandidatesForPaymentImportCard({ projectId, paymentMethodId, transactionDate, amount, currencyCode, dateWindowDays }). Keys off payment_method_id (resolved at Resolve from card_last_four).
  • Bank → findTransactionMatchCandidatesForPaymentImportBank({ projectId, referenceNumber, transactionDate, amount, currencyCode, dateWindowDays }). Keys off reference_number = row.external_id. Deliberately omits payment_method_id because the bank import worker doesn't always know which PM a transaction was booked on.

Both helpers require non-terminal status (status NOT IN (Cancelled, Rejected)) and balance > 0 (the transaction still has remaining capacity to reconcile against).

Date matching is windowed, not exact. Card movements settle a day or two after the purchase the transaction records, so the worker passes dateWindowDays: PAYMENT_DATE_MATCH_WINDOW_DAYS (±1 day) and the helpers return EVERY candidate in the window (up to PAYMENT_IMPORT_WINDOW_FETCH_LIMIT, ordered by date). selectImportDateMatch (utils/payment.ts) then picks the winner:

  • ranked by whole-day distance from the statement date — a same-day candidate always beats a neighbour-day one;
  • the winner must be ALONE at the best distance — two candidates equally close is a tie, and ties never auto-match;
  • a candidate further than the window bound, or an undated row, is never picked;
  • a full page (rows.length >= PAYMENT_IMPORT_WINDOW_FETCH_LIMIT) is treated as possibly truncated → no match.

Every OTHER caller of these finders omits dateWindowDays and gets the original exact-date, two-row query — in particular processPaymentCreation (the transaction processor's payment auto-creation), which must never window because both of its dates come from the same record.

If a single transaction wins → §D. Tie / nothing / truncated → §E.

D. Transaction match found

D-1. Look up the matched transaction's existing reconciliation

findReconciliationByTransactionId(tx.id) returns at most one row — reconciliations are 1:1 with transactions.

D-2. Existing allocation → promote / stamp the existing payment

When a payment_reconciliations row exists pointing at tx:

  1. Load the payment via getPaymentById(pr.payment_id).
  2. Sum the payment's allocations via findReconciliationsForPayment(pr.payment_id).
  3. Update the payment with:
  4. is_imported_match = true
  5. external_id = row.external_id ?? existing (the partial unique index covers source = 'csv_import')
  6. If status was SCHEDULED → status = PAID, paid_at = paidAt, paid_by_user_id = userId. Action becomes MATCHED_SCHEDULED_PROMOTED. Otherwise (status=PAID): no status change; action = MATCHED_PAID.
  7. If allocatedSum === payment.total_amount AND payment.reconciled_at is still null → stamp reconciled_at = paidAt, reconciled_by_user_id = userId.

Outcome carries payment_id AND transaction_id.

D-3. No existing allocation → reuse a matching payment, else mint a fresh one

When no payment_reconciliations row points at tx, the worker first looks for an existing payment to reuse instead of minting a duplicate: findPaymentMatchCandidates runs with the same dateWindowDays window (the user may have dated a manual payment the day they expected the money to move, not the day it settled), and selectImportDateMatch picks the single closest candidate. A payee-mismatched candidate is rejected (SKIPPED / payment_payee_mismatch — the worker declines the row on purpose so the user resolves the suspicious near-twin manually). A tie, or no candidate, mints a fresh payment:

  1. createPayment with:
  2. project_relationship_id = getTransactionRelationshipId(tx) (errors with transaction_missing_project_relationship if null)
  3. entity_id = tx.entity_id ?? tx.customer_entity_id ?? null
  4. payment_method_id = paymentMethodId
  5. status = PaymentStatus.PAID, paid_at = paidAt, paid_by_user_id = userId
  6. source = PaymentSource.CSV_IMPORT, is_imported_match = true
  7. reconciled_at = paidAt, reconciled_by_user_id = userId (balanced by construction — the allocation written below covers the full total)
  8. createPaymentReconciliation with { payment_id: new.id, transaction_id: tx.id, amount: row.amount, approval_instance_id: null }.

Outcome: action = NEW_PAYMENT with payment_id + transaction_id.

If row.external_id is set, register the new payment in parentByExternalId so fee rows in the second pass can link.

E. No transaction match → fall back to payment match

E-1. Look up a payment match over the date window

findPaymentMatchCandidates({ projectId, paymentMethodId, scheduledDate, amount, currencyCode, dateWindowDays }) — same project + PM + amount + currency, status in (Scheduled, Paid), scheduled_date within ±PAYMENT_DATE_MATCH_WINDOW_DAYS days. selectImportDateMatch picks the single closest candidate under the same rules as §B/§C (same-day beats neighbour-day; ties, out-of-window and truncated pages never match). Note there is no payee guard on this path — a payment has no transaction to resolve a payee from — so the window's residual risk of promoting a similar-but-unrelated neighbour-day payment is accepted and bounded by the tie rule.

E-2. Payment match found

  1. Load the payment's existing allocations via findReconciliationsForPayment(payment.id).
  2. Update the payment with:
  3. is_imported_match = true
  4. external_id = row.external_id ?? existing
  5. If status was SCHEDULED → promote (same fields as §D-2). Action = MATCHED_SCHEDULED_PROMOTED. Otherwise: action = MATCHED_PAID.
  6. DO NOT stamp reconciled_at here — the existing allocations might not be what the bank statement is referring to. The user confirms at the Reconciliations UI.
  7. Push every existing allocation's transaction_id onto the outcome's suggestions array as { kind: 'transaction', id, score, reasons: ['existing_allocation_on_amount_matched_payment'] }.

Outcome carries payment_id and (when allocations existed) suggestions.

E-3. No payment match either → UNMATCHED real-movement payment

The statement movement is real, so we still write a payment row — just with no PR/entity (those are only knowable once the user picks a transaction at reconciliation):

  1. createPayment with:
  2. project_relationship_id = null, entity_id = null
  3. payment_method_id = paymentMethodId
  4. status = PaymentStatus.PAID, paid_at = paidAt, paid_by_user_id = userId
  5. source = PaymentSource.CSV_IMPORT, is_imported_match = true
  6. NO reconciled_at stamp.
  7. Run findSuggestedTransactionsForPaymentImport (±1 day, exact amount + currency + PM, non-terminal status, balance > 0) and stamp each candidate as { kind: 'transaction', id, score }.

Outcome: action = ImportRunOutcomeAction.UNMATCHED with payment_id + suggestions.

F. Fee-row second pass

Fee rows (row.parent_external_id != null) wait for the first pass to finish so their parent's payment_id is known. Each fee row creates a Paid payment with:

  • parent_payment_id = parent.id
  • is_fee_movement = true, is_imported_match = true, source = csv_import
  • project_relationship_id = null, entity_id = null (the counterparty lives on the parent payment; reads join through parent_payment_id)
  • NO reconciled_at stamp — fees carry no allocation of their own.

Outcome: action = PaymentsImportOutcomeAction.FEE_LINKED.

Card-flow fees ALWAYS resolve against the CARD-side resolution bucket, even when the fee row's kind is cash (a cash-withdrawal fee is charged by the bank to the card, not to the cash itself). Bank-flow fees take the Step-2 target PM.

Reconciliation model

A payment is "reconciled" when both hold:

  1. The payment's total balance is fully covered (allocations on payment_reconciliations sum to payment.total_amount), AND
  2. The reason for that balance is one of: imported via CSV (source = csv_import + is_imported_match), manually reconciled (is_manually_reconciled), or a cash-float bump (is_cash_float_bump).

The single observable signal is payments.reconciled_at IS NOT NULL. The view exposes that as is_reconciled and the import worker writes it explicitly — there are no database triggers stamping this column. Every reconciled_at write in the codebase is app-side, deliberate, and documented on the call site.

Why app-side and not trigger-driven: the "what counts as balanced" predicate is composite (allocations + flags + status) and conceptually tied to a specific user action (the import worker fired this row, the reconciliation UI confirmed that allocation, etc.). Triggers would have to re-derive it on every allocation INSERT and second-guess the worker's intent. Easier to write the timestamp where the action happens.

Outcomes vocabulary

row_outcomes[].action is a free-form string at the framework boundary; the payments domain narrows it to the union below.

Action When Writes
cash_bump §A. Cash withdrawal on a petty-cash float payment
matched_paid §D-2 with PAID payment, OR §E-2 with PAID payment update only
matched_scheduled_promoted §D-2 with SCHEDULED payment, OR §E-2 with SCHEDULED payment update only
new_payment §D-3. Transaction matched, no existing allocation payment + PR
fee_linked §F. Fee row attached to parent via parent_payment_id payment
unmatched §E-3. No tx, no payment match — real movement saved with suggestions for the UI payment
skipped Missing field, user-skipped at Resolve, already-imported external_id, etc. none
errored Per-row throw, missing PR on tx, missing PM, etc. Loop continues. none

new_payment, fee_linked, cash_bump, and unmatched all count toward import_runs.created_rows. matched_* actions count toward updated_rows.

Suggestion shape

row_outcomes[].suggestions[] entries follow paymentsImportRunSuggestionSchema:

{
  "kind": "transaction", // 'payment' is reserved but not currently emitted
  "id": "<uuid>",
  "score": 0.8, // PAYMENT_IMPORT_SUGGESTION_SCORE — static for now
  "reasons": ["existing_allocation_on_amount_matched_payment"], // optional
}

Surfaced in two places:

  • §E-2 — every pre-existing allocation on an amount-matched payment is emitted with the existing_allocation_on_amount_matched_payment reason.
  • §E-3 — every ±1d exact-amount transaction is emitted with no reason annotation.

The future Reconciliations UI sorts by score descending and renders the reason as a legend.

Service-layer call surface

Helper Location Used by
findTransactionMatchCandidatesForPaymentImportCard services/transaction/{base,server}.ts §B card-flow windowed match (±1 day)
findTransactionMatchCandidatesForPaymentImportBank services/transaction/{base,server}.ts §C bank-flow windowed match (±1 day)
selectImportDateMatch utils/payment.ts §B/§C/§D-3/§E-1 closest-date winner + tie rule
findSuggestedTransactionsForPaymentImport services/transaction/{base,server}.ts §E-3 unmatched suggestions
findPaymentMatchCandidates services/payment/{base,server}.ts §D-3 payment reuse + §E-1 payment fallback
findReconciliationByTransactionId services/payment/{base,server}.ts §D-1 1:1 transaction → reconciliation lookup
findReconciliationsForPayment services/payment/{base,server}.ts §D-2 balanced check, §E-2 suggestion gather
getPaymentById services/payment/{base,server}.ts §D-2 load existing payment for update decision
createPaymentReconciliation services/payment/{base,server}.ts §D-3 write allocation
findExistingExternalIdsForProject services/payment/{base,server}.ts Re-import dedupe (skipped:already_imported)

All read helpers return raw view rows; the worker stamps PAYMENT_IMPORT_SUGGESTION_SCORE itself rather than baking it into generic read paths.