# Bank Transactions `b2b.bank_transaction` is the central ledger of every debit and credit to CFX's bank accounts. Seven producers across five subsystems write into it; every deposit, withdrawal, redemption, ACH batch and internal-transfer reconciliation domain reads off it. There is no other record of "money moved at the bank" in this codebase. Schema: [`packages/db/src/drizzle/schema/bank_transaction.ts`](../packages/db/src/drizzle/schema/bank_transaction.ts). Service: [`packages/bank-transaction-services/src/BankTransactionService.ts`](../packages/bank-transaction-services/src/BankTransactionService.ts). --- ## 1. Identity and idempotency ``` unique(bankTransactionId, accountNumber, routingNumber) ``` This constraint is the linchpin. Victor and RouteFusion each run **two independent producers** — a webhook fast path and a scheduled poll backstop — that never coordinate, never lock, and never consult each other before writing. The unique constraint is the only thing standing between that and duplicate rows. `BankTransactionService.create` ([BankTransactionService.ts:552-625](../packages/bank-transaction-services/src/BankTransactionService.ts)) layers `onConflictDoNothing` on top of a read-then-insert pre-check, not either alone: 1. Search for an existing row on `(bankTransactionId, accountNumber, routingNumber)`. If found, return it — no insert attempted. 2. Insert with `.onConflictDoNothing({ target: [...] })`. 3. If the insert returned nothing, a concurrent writer won the race between step 1's read and step 2's insert (the webhook and the scheduled sync racing on the same key is expected traffic under READ COMMITTED, not an error) — re-read and return the winner's row. 4. Only a real insert publishes `backoffice.bankTransaction.created` and busts the search cache; a dedup hit does neither, since the row's original insert already did. Every producer in this document calls `create` with the same shape, so every producer gets this idempotency for free. None of them re-check for an existing row on their own. **Proposed:** [BANK_ACCOUNT_COLLAPSE.md § 3.5](proposals/BANK_ACCOUNT_COLLAPSE.md) adds `bank_account_id`, a `NOT NULL` FK resolved by a `BEFORE INSERT` trigger from the routing and account numbers a producer supplies, inserting a register row when none matches. It fires on `INSERT` only, and the `onConflictDoNothing` above is why that is safe: a duplicate insert does nothing, so nothing is resolved twice. The reconciliation `UPDATE`s of §3 must not re-resolve, which is the reason the trigger is not `AFTER UPDATE`. --- ## 2. Producers Seven producers across five subsystems, discriminated by `raw.source` (`BankTransactionRawSource` in the schema file). One, `metcap`, is retired: its rows remain but nothing writes new ones. | `raw.source` | Producer | Trigger | | ---------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------- | | `victor_webhook` | [`backoffice-api/src/controllers/victor/VictorWebhookEventConsumer.ts`](../packages/backoffice-api/src/controllers/victor/VictorWebhookEventConsumer.ts) | Victor webhook (via Hookdeck) | | `victor_sync` | [`bank-transaction-services/src/victorTransactionSyncActivities.ts`](../packages/bank-transaction-services/src/victorTransactionSyncActivities.ts) | Temporal schedule | | `route_fusion_webhook` | [`backoffice-api/src/controllers/routefusion/RouteFusionWebhookEventConsumer.ts`](../packages/backoffice-api/src/controllers/routefusion/RouteFusionWebhookEventConsumer.ts) | RouteFusion webhook (via Hookdeck) | | `route_fusion_sync` | [`bank-transaction-services/src/routeFusionTransactionSyncActivities.ts`](../packages/bank-transaction-services/src/routeFusionTransactionSyncActivities.ts) | Temporal schedule | | `metcap` | Retired. Written by the MetCap (Payware) sync in `metcap-services`, removed with the integration; existing rows keep this source | None (bank is FIB — First Independence Bank) | | `wire_email` | [`wire-email-services/src/WireEmailBankTransactionService.ts`](../packages/wire-email-services/src/WireEmailBankTransactionService.ts) | Inbound wire notification email, via SES | | `manual` | [`admin-dashboard/src/app/(private)/bank-transactions/_actions.ts`](<../packages/admin-dashboard/src/app/(private)/bank-transactions/_actions.ts>), [`internal-dashboard/src/routes/bank/transaction/create_manual_transaction.ts`](../packages/internal-dashboard/src/routes/bank/transaction/create_manual_transaction.ts) | Operator, dashboard form | There are no other callers of `BankTransactionService.create` / `BankTransactionAdminService.create` in the repo, and no other `raw.source` literal. --- ## 3. The two-writer pattern: which path reconciles what Every producer creates rows. Only some also drive downstream reconciliation (processing a deposit quote, returning a failed withdrawal). Reading the scheduled sync code and wondering why it never touches a deposit or withdrawal — the answer is below. | Path | Creates rows | Drives reconciliation | | --------------------- | :----------: | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Victor webhook | Yes | **Yes** — it starts `bankTransactionReconciliationWorkflow` for every row it writes (`startReconciliation`), and the workflow dispatches to `AchCreditDepositQuoteService` / `InstantDepositQuoteService` / `UsWireDepositQuoteService` / `SwiftWireDepositQuoteService`.`processFromBankTransaction` on inbound and to `BankInternalTransferReconciliationService` on a book transfer. Inline it still calls `*WithdrawalReconciliationService.return*Withdrawal` on a failed outbound ACH, wire or RTP send (`rtp_send` → `UsInstantWithdrawalReconciliationService.returnUsInstantWithdrawal`) | | Victor sync | Yes | **Yes, since #3591** — it starts `bankTransactionReconciliationWorkflow` for every row it newly creates ([victorTransactionSyncActivities.ts:340-350](../packages/bank-transaction-services/src/victorTransactionSyncActivities.ts)). It still calls no deposit or withdrawal service itself; the workflow owns the dispatch | | RouteFusion webhook | Yes | Yes, for USD credits. `persist()` writes the row, then starts `bankTransactionReconciliationWorkflow` for an `incoming_transfer` in USD ([RouteFusionWebhookEventConsumer.ts](../packages/backoffice-api/src/controllers/routefusion/RouteFusionWebhookEventConsumer.ts)). A `transfer` (a debit) or a non-USD credit stays POSTED. Its `entity` webhook reconciles onboarding state, not a `bank_transaction` row | | RouteFusion sync | Yes | Yes, for USD credits. `persistRouteFusionBankTransactions` starts the same workflow for each credit it persists, including a row a retry of its own persist finds already written ([routeFusionTransactionSyncActivities.ts](../packages/bank-transaction-services/src/routeFusionTransactionSyncActivities.ts)) | | metcap sync (retired) | No new rows | Retired with the MetCap integration. It wrote no reconciliation state; it linked each new row to a stored wire email (`backfillWireEmailForBankTransaction`) and started `wireEmailDepositWorkflow` | | wire_email | Yes | Yes, inline — `WireEmailBankTransactionService.reconcile` finds-or-creates in one call ([§4.4](#wire-email-and-manual)) | | manual | Yes | No | **Reconciliation is a workflow, and nothing in it reads `raw.source`.** Since #3591 both Victor paths start `bankTransactionReconciliationWorkflow`, which plans from `methodData.method` plus the row's direction and then calls exactly one reconciling activity. A `victor_sync` row is dispatched by the same rules as a `victor_webhook` one; there is no feed-source gate anywhere in the reconciliation path. The sync's remaining limit is that it starts the workflow only for rows it **newly creates** ([victorTransactionSyncActivities.ts:340-350](../packages/bank-transaction-services/src/victorTransactionSyncActivities.ts)), so every row already in the table when #3591 shipped was never offered to it. That is what the admin dashboard's reconcile queue exists to repair. **An inbound Victor ACH row can wait up to four hours before it reconciles.** Victor completes an inbound ACH entry before it has finished describing it: `GET /v2/transactions/{ref}` returns `addenda`, `company_id`, `company_entry_description`, `company_discretionary_data` and `settlement_date` as null right after completion and populated hours later, with no second webhook. Both writers read the detail once, when they insert the row, so both can hand the workflow a row without those fields. The deposit or withdrawal copies the row's entry fields at reconciliation and a partner reads that record when its event arrives, so the workflow holds the row until the detail is there rather than changing any event: - `planBankTransactionReconciliation` marks the plan with `awaitAchEntryDetailUntilEpochMs`, four hours after `postedAt`, for a `dart` row with a `victorTransactionId` that is an inbound ACH credit (`ACH`, `CREDIT`) or an inbound ACH debit (`ACH_DEBIT`, `DEBIT`) and holds neither `addenda` nor `settlementDate`. No other rail is held. - For a marked plan the workflow calls [`AchEntryDetailAdminService.refresh`](../packages/bank-transaction-reconciliation-services/src/AchEntryDetailAdminService.ts) at once, then after 30 seconds, doubling to a five-minute ceiling. Each call re-reads the detail and writes the entry fields the row lacks into `methodData`. It never overwrites a field the row holds, and `raw.payload` stays as it was read at insert. - The reconciling activity runs when `addenda` or `settlementDate` arrives or the deadline passes, whichever is first. A failed read counts as not yet. A row already past its deadline when the run starts is read once and not held. - A row released without the fields still reconciles, and the outcome's `reason` ends with a sentence saying so. The lag is not measured: one pair of entries was populated when read 7.5 hours after completion, and one report puts it near 2 hours. Diagnosis, the backfill for records written before the hold or released without the fields, and the hold's known limits are in [runbooks/ach-addenda-missing.md](runbooks/ach-addenda-missing.md). **A row that stays `POSTED` usually means a failed workflow, not a no-op** — unless it is an inbound ACH row inside the hold above, whose run is still `Running`. The only outcomes are `RECONCILED`, `ALREADY_RECONCILED` and `NOT_APPLICABLE`; every other path throws a typed `ApplicationFailure` — `..._UNSUPPORTED` (no `methodData.method`, or a FedNow debit), `..._MISSING_REFERENCE`, `..._RAIL_MISMATCH`, `..._MISSING_ACH_ID`, `..._UNRESOLVED`, or the internal-transfer service's own error code, non-retryable, when it rejects a book transfer's legs ([§6](#6-traps-and-invariants)). All of them leave `data -> 'reconciliation'` empty, so from SQL a clean `NOT_APPLICABLE`, a terminal failure, a still-retrying run and a held one are indistinguishable. **Read the outcome from the execution `bank-transaction-reconciliation-` in Temporal, never from the row** — and note the row is stamped asynchronously, so a marker query run seconds after a backfill undercounts what actually succeeded. **Reconciling an inbound ACH debit can create a withdrawal.** `INBOUND_ACH_DEBIT` matches an `ACH_DEBIT` withdrawal by `victorTransactionId`; where a virtual account owns the account number and no withdrawal matches, [`AchDebitWithdrawalReconciliationService.processInboundAchDebit`](../packages/withdrawal-services/src/ach-debit/AchDebitWithdrawalReconciliationService.ts) mints one through `maybeCreatePendingVirtualAccountAuthorization`, and the row ends up carrying both a `WITHDRAWAL` and a `REDEMPTION` marker. This direction therefore writes well beyond the reconciliation marker — treat a bulk run over settled ACH debits as a money-movement change, not a bookkeeping one. **An outbound RTP debit settles a US Instant withdrawal.** The plan routes an `RTP` DEBIT to `OUTBOUND_WITHDRAWAL`, which pairs `method` `RTP` with quote `methodId` `US_INSTANT` and calls [`UsInstantWithdrawalReconciliationService.processUsInstantWithdrawal`](../packages/withdrawal-services/src/us-instant/UsInstantWithdrawalReconciliationService.ts) with `victorReferenceId` as the withdrawal id. It commits the redemption and starts `finalizeLedgerAccountWithdrawalWorkflow`. An `RTP` debit whose withdrawal is on another rail fails `..._RAIL_MISMATCH`. The webhook is `instant_payment_send`: Victor retired its `rtp_*` webhooks. An `instant_payment_refund` still writes nothing and returns nothing. RouteFusion starts reconciliation for USD credits only. Both RouteFusion paths call `startRouteFusionCreditReconciliation` once they have written an incoming-transfer row, so a USD credit mints without an operator. A credit seen by both paths starts the workflow twice under one id, `bank-transaction-reconciliation-`. Three things keep that to one mint: Temporal refuses a second start while the first run is open, the plan reads a `RECONCILED` row as `ALREADY_RECONCILED` once it has closed, and each deposit service uses the bank transaction id as its idempotency key. - A credit in another currency, or one that names no currency, is written and left POSTED. The deposit services mint MOVEUSD at the row's `amount` and read no currency. - A RouteFusion debit from the outbound feed starts nothing. Completing a RouteFusion withdrawal from the feed is a separate change. - RouteFusion reports a third party's ACH pull on the incoming feed, with a negative `amount` (`credit: false` on the webhook). The mapper writes it as an `ACH_DEBIT` debit for the absolute amount, and the gate starts it, so it plans `INBOUND_ACH_DEBIT` and burns the customer's MOVEUSD the way a Victor pull does, keyed on `routeFusionTransferId`. - The sync starts only rows it writes in the run (or finds on a retry of that run). A row the webhook wrote whose start never succeeded, and every credit written before this shipped, are offered to the workflow by the reconciliation backfill, filtered by feed source. - `virtual_account.account_number` has no unique constraint. An inbound deposit whose account number matches more than one virtual account fails the run with `VIRTUAL_ACCOUNT_AMBIGUOUS` instead of crediting the first match. --- ## 4. Per-provider feed differences The polled feeds (Victor, RouteFusion, and the retired metcap feed, kept here because its rows remain) are shaped by their providers' APIs, not by a shared design. Where they differ, it's forced. | | Victor | metcap / FIB (retired) | RouteFusion | | -------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **Ownership resolves by** | account number, against `bank_account` (`provider = 'VICTOR'`) ∪ `virtual_account` (VICTOR-provider rows) | the account number the sync is configured for | `wallet_id` — the outbound payload carries no account number at all, and the one on an incoming transfer names the counterparty, not us | | **Status/state format** | Inconsistent casing across surfaces (`"Failed"` vs `"FAILED"`); the **list** endpoint alone serializes `status` as a numeric code (`VICTOR_NUMERIC_STATUS_CODES` in [`internal.ts`](../packages/bank-transaction-services/src/internal.ts)) | plain string | plain string enum | | **Per-row detail fetch** | Yes — a `GET /v2/transactions/{ref}` per new candidate, because the list endpoint's numeric status isn't trustworthy alone and `raw` needs the richer detail payload. Reconciliation reads it again for an inbound ACH row whose entry detail is late ([§3](#3-the-two-writer-pattern-which-path-reconciles-what)) | No | No — the GraphQL page query already returns every field the mapper needs | | **Concurrency of persist** | **Sequential.** Victor's session token is single-writer, so the per-row detail GETs cannot run in parallel; the activity is bound with a 15-minute timeout + 60s heartbeat to survive a burst ([victorTransactionSyncWorkflow.ts:28-38](../packages/bank-transaction-services/src/victorTransactionSyncWorkflow.ts)) | Parallel (`Promise.allSettled`) | Parallel-safe (no per-row provider call) | | **Paging direction** | Forward from page 0 over a completed-at time window | **Backward** — computes the page count from `metcapApiTxCount`, then pages `[N, N-1, ..., 1]` because metcap's feed is oldest-first, so the newest rows are on the last page | **Forward** from page 0 — verified newest-first by a live probe against the dev sandbox ([routeFusionTransactionSyncWorkflow.ts:101-131](../packages/bank-transaction-services/src/routeFusionTransactionSyncWorkflow.ts)); paging metcap's way (reversed) would start at the oldest page, find it already persisted, and break on page one every run — a permanent silent no-op | | **Incremental stop** | Short page (`< pageSize`), or a truncation error if the page budget runs out first | **Count gate**: compares the API's total transaction count to `BankTransactionQueryService.stat({ includeManual: false })`'s DB count; equal counts mean nothing to do | Can't use metcap's count gate — RouteFusion's `items_total` is organization-wide, but the sync only persists terminal-state transfers on wallets _we_ hold, so the two counts never converge. Instead: incoming stops on the first page yielding zero new candidates (valid because it reads newest-first); outbound has no count or cursor at all, so it advances a persisted high-water-mark (`newest stored DEBIT postedAt − 120min overlap`) and stops on a short page | | **Batching (2000-activity cap)** | Time-sliced by the caller (`victorTransactionBackfillWorkflow`), not by this workflow — the sync workflow itself has no child-batching, just a 500-page budget with a truncation-detecting probe read | Child workflows, `cluster(pages, 450)` | Child workflows, `cluster(pages, 600)` for incoming (per-page activity count differs from metcap's, hence the different constant — see the comment at [routeFusionTransactionSyncWorkflow.ts:17-39](../packages/bank-transaction-services/src/routeFusionTransactionSyncWorkflow.ts)); outbound has its own page budget (500) since it isn't batched from a known count | | **Backfill** | Dedicated `victorTransactionBackfillWorkflow` — slices an arbitrary range into parallel time windows, since Victor's list endpoint exposes no count to slice by instead | N/A (count gate self-bounds every run) | **None.** Neither feed accepts a date window, so the sync workflow takes no window argument, and the very first run is the backfill | | **Known historical bug** | Book transfers post as **two** Victor transactions (one per leg) linked by `parent_reference_id`; expanding either into a fabricated debit+credit pair produced four rows per transfer (see §6) | — | — | ### Victor instant payments Victor reports an inbound instant payment as `INSTANT_PAYMENT_INBOUND` whether it arrived over RTP or FedNow. - **The list item names our account on both sides.** `from_account_number` and `to_account_number` are the same account, so the sync expands this type, like every type that only credits (`VICTOR_CREDIT_ONLY_TYPES`), into the CREDIT on `to_account_number` alone. Expanding both sides writes the DEBIT first and loses the CREDIT to the unique key, which stores an inbound payment as a debit. - **Only the detail names the network.** `GET /v2/transactions/{ref}` carries `network_type` (`"FedNow"` or `"RTP"`); the list item and the webhook do not. Both writers already hold the detail and map the payment through one function, [`mapInboundInstantPayment`](../packages/bank-transaction-services/src/inboundInstantPayment.ts), so the row does not depend on which writer won: `FEDNOW` for FedNow, `RTP` for RTP, with the sender, the payment's network message ID and one description format. Victor types `network_type` optional; a payment without it is stored as a credit with no method (`Instant Payment Deposit []`), which nothing quotes until an operator sets its rail on the Reconcile panel. - **`tchInstitutionId` (RTP) and `institutionId` (FedNow) hold the payment's network message ID, not an institution.** Victor puts the same value in both its `tch_institution_id` and `institution_id` fields. An RTP one reads like `20260706021000021P1BRJPC04000010154`: the date, the sending participant (`021000021P1`) and a sequence. It is unique per payment, which is why the deposit quote's `reference` takes it. The keys keep their names on `bank_transaction` and `deposit_quote`; the admin dashboard and the Slack bank feed label them "message ID". - **Only the webhook names the sender.** The detail's sender fields (`originator_name`, `originator_account_number`, the originating and sending bank fields) are empty. The webhook carries `originator_name`, `originatorAccountNumber` and `originating_aba`, stored as `senderName`, `sourceAccountNumber` and `sourceRoutingNumber` (the names the ACH and FEDWIRE arms use). When the sync wrote the row first, the webhook's dedup hit PATCHes whichever of those three the row lacks and changes nothing else. A deposit already quoted off the row keeps the sender it had. The read and the PATCH are not under one lock, so a sender set in between is overwritten. - **The webhook is `instant_payment_inbound`**, which replaced the retired `rtp_inbound`. Its `description` is null and its ABA field is `originating_aba`. A type missing from that union is answered 400 and writes no row. - **A FedNow credit reconciles as a FedNow deposit** (`deposit.fednow.v1`, `deposit_quote.type` `FEDNOW`), never as an RTP one. Reconciliation plans a `FEDNOW` credit as `INBOUND_DEPOSIT` and dispatches it to [`InstantDepositQuoteService`](../packages/deposit-services/src/InstantDepositQuoteService.ts), the same service and `rtpDepositWorkflow` RTP uses. The service reads the network off the row, and it picks the product, the quote type and where the message ID lands: `tchInstitutionId` for RTP, `institutionId` for FedNow. - **FedNow has no memo of its own.** A customer sends to the RTP deposit instructions whichever network their bank uses, so the memo consumer takes an RTP memo on a FedNow credit to the instant service with the network from the credit's `methodData.method`, not the memo's product. - **A FedNow debit is not reconciled.** Only an inbound payment learns its network, so a `FEDNOW` debit is a misrecorded row; reconciliation plans it `UNSUPPORTED` with that reason. In the admin dashboard a FedNow credit offers **Reconcile as FedNow deposit**, and an operator can set `FEDNOW` as a credit's rail. - **The Dart monthly report files FedNow inbound deposits in `RTP_Inbound`**, beside RTP: the report has one instant-payment file, and Victor reports both networks alike. ### Wire email and manual Not polled feeds, but worth placing beside the three above: - **`wire_email`** — `WireEmailBankTransactionService.reconcile()` runs a find-or-create in one call: match by IMAD (exact — also what makes Burling's duplicate SES delivery a no-op), else by amount within a ±1-day window restricted to IMAD-less rows, else create. Two or more amount-matched candidates come back `ambiguous` rather than picking one — an unlinked email sits on a review queue; a wrongly-linked bank transaction silently double-counts money. See [WireEmailBankTransactionService.ts:70-86](../packages/wire-email-services/src/WireEmailBankTransactionService.ts) for the full ordering rationale. - **`manual`** — an operator-entered row via the admin dashboard (`/bank-transactions`) or internal-dashboard, always `isManual: true`. `isManual` rows are excluded from `BankTransactionQueryService.stat({ includeManual: false })`, which is what keeps hand-entered rows invisible to a count gate like the retired metcap feed's above. --- ## 5. The three JSONB columns ### `raw` `{ source: BankTransactionRawSource; payload: Record }` — the untouched provider payload (or manual form input) captured at insert time, for audit. Nullable: rows created before this column existed, and any producer without a payload, stay `NULL`. ### `data.methodData` Per-rail union, discriminated by `method`: `ACH`, `ACH_DEBIT`, `RTP`, `FEDNOW`, `SWIFT`, `FEDWIRE`, `BOOK_TRANSFER` — plus provider id fields (`victorTransactionId`, `victorReferenceId`, `routeFusionTransferId`, `routeFusionReference`) that ride alongside regardless of method. `{}` when a producer has nothing to report (e.g. an unmapped RouteFusion payment method, or a Victor wire with no IMAD). **On the wire rails `imad` is required and `omad` is optional.** The second writer of this column is a person: the admin dashboard's rail-classification form (`SetMethodForm`, for a row that arrived with `methodData = {}`) offers both references, since the bank's notification email carries both and the bank's web wire report leads with the OMAD under the heading "Fed Reference Number". Only the IMAD gates the submit — a notification can legitimately have no OMAD line, which is why the parser types it `string | null`. Both fields are prefilled from the matching `wire_email` row, joined on the wire sequence number the statement description carries (`parseStatementWireNumber`), scoped to the same bank and direction, and only when the match is unique. That prefill is what makes the required IMAD cheap: the transaction feed itself carries no reference, so before it an operator had to log into the bank for every wire. A blank is never stored — `buildMethodData` omits an empty OMAD rather than writing `""` — so guard on the value, not just `"omad" in methodData`. ### `data.reconciliation` Which domain claimed this row. A scalar key names the one record the row settles, and several rows can name the same record: every leg of an internal transfer carries its id. The plural `*_IDS` array keys are for batch settlements where one bank transaction covers many transactions. | Direction | Scalar keys | Array keys (batch) | | --------- | ------------------------------------------------------------------------------------ | ---------------------------------- | | CREDIT | `DEPOSIT`, `MANUAL_MINT`, `ACH_BATCH`, `BANK_INTERNAL_TRANSFER`, `OPERATING_ENTRY` | `DEPOSIT_IDS` | | DEBIT | `WITHDRAWAL`, `REDEMPTION`, `ACH_BATCH`, `BANK_INTERNAL_TRANSFER`, `OPERATING_ENTRY` | `WITHDRAWAL_IDS`, `REDEMPTION_IDS` | A DEBIT can also carry `DEPOSIT_RETURN`: the ACH debit deposit an operator cancelled on a MetCap return. It is not `DEPOSIT`, because cancelling a deposit strips every `DEPOSIT` marker that names it. `OPERATING_ENTRY` names a `b2b.operating_entry` row: an operator marked a row on a `CFX_OPERATING` account (never `CFX_TOKENIZED_OPERATING`, which is token float) as corporate activity. Nothing mints, burns or settles. The label follows the row's `type`: a DEBIT is an operating expense, a CREDIT an operating receipt. Writers, by key. All go through `BankTransactionService.update`, which merges into the existing `reconciliation` object with `||` — additive, so it can add or overwrite a key. It removes one only when the patch passes it `null`, which today only `depositReturnId` (`DEPOSIT_RETURN`) and `operatingEntryId` (`OPERATING_ENTRY`) accept. See [BankTransactionService.ts](../packages/bank-transaction-services/src/BankTransactionService.ts). `update`'s `expectedReconciliation` is a compare-and-set read under a row lock: it writes only while the row's `reconciliation` equals it, and returns `BANK_TRANSACTION_RECONCILIATION_CHANGED` with the current one otherwise. `AchDebitDepositReturnService.cancel` claims `DEPOSIT_RETURN` with it from `{}` and releases it from exactly `{ DEPOSIT_RETURN: }`. Removal is a separate operation with one implementation: `BankTransactionUnreconcileService.unreconcile({ key, railId })`. It strips a dead rail record's marker — the scalar key when it names that rail id, or that id's element of the array key, dropping the array key outright once it empties (a bare `[]` is still `<> '{}'` and would leave the row reading RECONCILED against nothing). `DepositAdminService.update` calls it whenever a deposit lands in `CANCELLED`, which is what returns the funding credit to the unreconciled queue. Two consequences for anything that writes `DEPOSIT` / `DEPOSIT_IDS`: - A writer that recomputes a whole array key must exclude cancelled records, or a retry re-adds exactly what `unreconcile` removed. `AchDebitDepositBatchReconciliationService` filters for this reason. - A path guarded on `bankTransaction.status === "POSTED"` will now run for a released row where it previously short-circuited. That guard was load-bearing for more than its stated purpose — see [§6](#6-traps-and-invariants). | Key | Written by | | ------------------------ | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `DEPOSIT` | `AchCreditDepositQuoteService`, `InstantDepositQuoteService`, `UsWireDepositQuoteService`, `SwiftWireDepositQuoteService` (all via Victor webhook's `processFromBankTransaction`); Victor webhook's ACH-debit auto-reconcile path directly | | `DEPOSIT_IDS` | `AchDebitDepositBatchReconciliationService` (paired with `ACH_BATCH`) | | `DEPOSIT_RETURN` | The admin bank-transaction sheet's **Reconcile** button, outcome ACH return (`reconcileBankTransaction`), after an operator picks the held deposit on a MetCap ACH return DEBIT | | `MANUAL_MINT` | `ManualMintReconciliationService` | | `WITHDRAWAL` | `AchCreditWithdrawalReconciliationService`, `AchDebitWithdrawalReconciliationService`, `UsWireWithdrawalReconciliationService`, `SwiftWireWithdrawalReconciliationService`, `UsWireDrawdownWithdrawalReconciliationService`, `UsInstantWithdrawalReconciliationService` | | `WITHDRAWAL_IDS` | `WithdrawalRedemptionReconciliationService.commitManyFromBankTransaction` (paired with `REDEMPTION_IDS`) | | `REDEMPTION` | `WithdrawalRedemptionReconciliationService.commitFromBankTransaction` | | `REDEMPTION_IDS` | `WithdrawalRedemptionReconciliationService.commitManyFromBankTransaction` | | `ACH_BATCH` | `AchCreditWithdrawalBatchReconciliationService`, `AchDebitDepositBatchReconciliationService` | | `BANK_INTERNAL_TRANSFER` | `BankInternalTransferReconciliationService`, on every leg of the transfer in one transaction ([§6](#6-traps-and-invariants)) | | `OPERATING_ENTRY` | `OperatingEntryAdminService.create`, from the admin dashboard; `OperatingEntryAdminService.delete` removes it | `BankTransactionQueryService.search`'s `status` filter (`POSTED` vs `RECONCILED`) is derived straight from whether this object is empty — see [BankTransactionService.ts:364-378](../packages/bank-transaction-services/src/BankTransactionService.ts). ```mermaid stateDiagram-v2 direction LR [*] --> POSTED: create POSTED --> RECONCILED: a rail stamps its key RECONCILED --> RECONCILED: a second domain stamps another key RECONCILED --> POSTED: its last key is removed state "reads RECONCILED, claims nothing" as Orphan RECONCILED --> Orphan: a removal that empties an array key note left of POSTED reconciliation is empty. Sits in the unreconciled queue. end note note right of RECONCILED Stamped only by BankTransactionService.update, whose jsonb merge has no delete channel. Cleared only by unreconcile, reached today from one caller. end note note right of Orphan Wrong path, never taken. An empty array key still counts, so unreconcile drops the key with its last id. end note ``` The deposit path end to end, since it is the only rail that both stamps and releases: ```mermaid sequenceDiagram participant Rail as rail settlement participant Batch as batch reconciliation participant BTS as BankTransactionService.update participant Row as bank_transaction participant Dep as DepositAdminService.update participant Un as unreconcile Rail->>BTS: reconciliation.DEPOSIT = dp_x BTS->>Row: jsonb merge, add or overwrite only Note over Row: derives RECONCILED Dep->>Dep: deposit lands in CANCELLED Dep->>Un: key DEPOSIT, railId dp_x Un->>Row: strip DEPOSIT when it names dp_x,
drop dp_x from DEPOSIT_IDS Note over Row: derives POSTED, back in the unreconciled queue alt the batch writer runs again Batch->>BTS: DEPOSIT_IDS recomputed for the whole ACH batch BTS->>Row: replaces the key wholesale Note over Batch, Row: it must exclude CANCELLED deposits, or the
retry re-adds exactly what the release removed end ``` **Indexes.** Six partial btree indexes (one per scalar key, e.g. `idx_bank_tx_recon_deposit`) each predicated on `data -> 'reconciliation' ? ''`, serving the reverse lookup "which bank transaction reconciled this deposit/withdrawal/ redemption/manual-mint/ACH-batch/internal-transfer." For an internal transfer the answer is every leg, which `bank_internal_transfer_leg` also holds. Three GIN indexes (`_ids` suffix) serve the `@> to_jsonb(id)` containment lookup against the plural array keys. See [`bank_transaction.ts`](../packages/db/src/drizzle/schema/bank_transaction.ts) for the exact index definitions. --- ## 6. Traps and invariants - **A withdrawal settles on `quote.sourceAmountAfterFees`, never `targetAmountAfterFees`.** The redemption holds the source amount, pays the fees to CFX's fee accounts and burns the rest, and that rest is the USD that leaves the bank. MetCap and RouteFusion convert a SWIFT payout from USD too, so this holds on every rail. Every withdrawal reconciliation service compares the debit to it; [`WithdrawalRedemptionReconciliationService`](../packages/withdrawal-services/src/WithdrawalRedemptionReconciliationService.ts) checks a withdrawal's redemption against it, and an ACH batch sums it over its filed withdrawals. Only a standalone redemption (no `withdrawalId`) checks its own `amount`. The target is the beneficiary's amount, in the target currency on a SWIFT; comparing a debit to it matches only because the two are equal on USD rails. - **`bankId` is a slug, not a foreign key.** It's `bank.external_id` (text, e.g. `"dart"`, `"fib"`, `"burling"`), stored as plain text with no FK constraint — not the integer `bank.id`. A wrong `bankId` attributes every row to the wrong bank silently; nothing in the schema catches it. - **The `count(*) OVER ()` window-count caveat applies here.** Per [`rules/service-pattern.md`](../rules/service-pattern.md), a window-count `total` only equals the entity count when every join in the search query is 0-or-1 per base row. `BankTransactionQueryService.search`'s only join (to `bank`, on its unique slug) is safe today — but a future join added to that query must preserve that property, or `total` starts overcounting silently. - **Producers that map the same provider payload twice will drift.** Victor does this today: the webhook consumer builds `methodData`/description inline per event type ([VictorWebhookEventConsumer.ts](../packages/backoffice-api/src/controllers/victor/VictorWebhookEventConsumer.ts)), while the scheduled sync has its own independent `getVictorBankTransactionMethodData` ([victorTransactionSyncActivities.ts:469-533](../packages/bank-transaction-services/src/victorTransactionSyncActivities.ts)) / `getVictorBankTransactionDescription` ([internal.ts:64-108](../packages/bank-transaction-services/src/internal.ts)). This has already caused two production bugs: an enum gap that left every synced book-transfer credit with no method and no reconciliation ids, and — separately — treating each leg of a Victor book transfer as a full debit+credit pair, which fabricated a leg that already existed under the other transaction's id and produced four rows per transfer. **RouteFusion avoids this by construction**: both its webhook and its sync call the same `toRouteFusionBankTransactionRow` mapper in [`internal.ts`](../packages/bank-transaction-services/src/internal.ts). Any new producer pair should share one mapper the same way. - **`amount` is `numeric`.** Every arithmetic operation on it must go through `BigNumber` — never raw JS `+ - * /`. Every producer in this doc constructs it via `BigNumber(...).toFixed(2)` or passes a string straight through. - **An internal transfer is any number of legs that balance.** [`bank_internal_transfer_leg`](../packages/db/src/drizzle/schema/bank_internal_transfer_leg.ts) holds one row per bank transaction on a transfer, and a leg's side is its bank transaction's `type`. [`BankInternalTransferReconciliationService`](../packages/bank-transaction-services/src/BankInternalTransferReconciliationService.ts) takes `debitBankTransactionIds` and `creditBankTransactionIds` in both `createCompletedBankInternalTransfer` and `processBankInternalTransfer`, and validates them before writing anything: | Error | When | | -------------------------------------- | ------------------------------------------------- | | `BANK_INTERNAL_TRANSFER_MISSING_LEG` | No debit, or no credit | | `BANK_INTERNAL_TRANSFER_DUPLICATE_LEG` | A bank transaction is named twice | | `BANK_TRANSACTION_NOT_FOUND` | An id matches no row | | `BANK_TRANSACTION_WRONG_TYPE` | A debit id names a CREDIT, or a credit id a DEBIT | | `BANK_TRANSACTION_ALREADY_RECONCILED` | A leg already carries a reconciliation marker | | `BANK_INTERNAL_TRANSFER_UNBALANCED` | The debit and credit totals differ | Validation reads through the search cache, so completion re-reads the legs `FOR UPDATE` and refuses any that carries a marker. It then writes the legs and stamps `BANK_INTERNAL_TRANSFER` on each in one transaction. The unique leg `bank_transaction_id` stops a bank row settling two transfers. `createCompletedBankInternalTransfer` inserts its transfer inside that same transaction, so a refused or failed completion leaves no row. A retry naming exactly the legs of an already COMPLETED transfer returns that transfer rather than failing on its own markers. A transfer leaves PENDING once: completing one that already left throws, and failing one is a no-op, so stamped legs never sit on a FAILED transfer. On the transfer row, `data.amount` is the legs' total once COMPLETED. `data.source` / `data.target` are the accounts a Victor book transfer was requested between; transfers paired before N-leg support also carry their legs' accounts there, and newer pairings leave them unset. Victor book transfers are one debit and one credit; the admin dashboard's **Reconcile** button, reconciling as an internal transfer, takes any number of each, and Interval's pairs one to one. - **A cancelled deposit is guarded by its own status, not by the marker.** The four `processFromBankTransaction` deposit rails ([`AchCreditDepositQuoteService`](../packages/deposit-services/src/AchCreditDepositQuoteService.ts), [`InstantDepositQuoteService`](../packages/deposit-services/src/InstantDepositQuoteService.ts), [`UsWireDepositQuoteService`](../packages/deposit-services/src/WireDepositQuoteService.ts), [`SwiftWireDepositQuoteService`](../packages/deposit-services/src/SwiftWireDepositQuoteService.ts)) bail out when the row already reads RECONCILED. That guard's stated job is "some domain already claimed this credit"; its unstated one was blocking a re-drive of a deposit that was processed and then cancelled, because the stale marker kept the row RECONCILED forever. Once `unreconcile` returns the row to POSTED, a redelivered webhook or a manual re-drive reaches the whole path. Nothing on it used to check the deposit's status: 1. `DepositAdminService.create` resolves `deposit.idempotency_key` to the **existing CANCELLED deposit** and returns it, terminal status and all. 2. `accept` flips the quote to ACCEPTED and starts a fresh deposit workflow under `workflowId: depositId`. The original run is closed, so the reuse policy permits it. 3. `usWireDepositWorkflow` checks only `deposit.type`, never `deposit.status`, then writes `status: "PROCESSING"` and re-runs the sweep and the mint. 4. The rail re-stamps `reconciliation.DEPOSIT` and answers `DEPOSITED`. Left alone, a cancelled deposit could go CANCELLED → PROCESSING → DEPOSITED with no operator decision while the caller reported success. **What closes it:** `DepositAdminService.create` takes an `onCancelled` policy, and the rails ask for `"recreate"`. A cancelled deposit is never handed back to a workflow that would drive it out of its terminal state — instead the credit gets a **new** deposit, and the cancelled row stays as the historical record. The three arms: | `onCancelled` | Who asks | Cancelled row under the key | | -------------------- | -------------------------------------------------- | -------------------------------------------------------------------------------------------- | | `"return"` (default) | the public accept path, on a customer-supplied key | handed back unchanged, because idempotency there means one key always answers the same thing | | `"revive"` | the Green Dot `AUTH` path only | brought back to PENDING, and the reversal published | | `"recreate"` | the four `processFromBankTransaction` rails | a fresh deposit is inserted; the cancelled row is left alone | `"recreate"` keeps the **same** idempotency key deliberately, so the new deposit inherits the old one's mint protection: `mintTokensForDeposit` keys its Solana idempotency on the deposit's `idempotencyKey`, not its id, so a signature written under that key still blocks a second mint. A key can therefore name more than one deposit — the non-unique `deposit_idempotency_key_idx` permits it, and the lookup resolves to the one non-CANCELLED row. When the cancelled deposit **did** mint (`receiptId` is set), `create` throws `DepositCancelledAfterMintError` rather than recreating, since a second deposit would silently adopt the first one's signature and report DEPOSITED without minting. The rails catch it and answer `DEPOSIT_CANCELLED_AFTER_MINT`, so the Temporal activity completes instead of retrying a permanent condition, and a person decides which row owns the mint. --- ## See Also - [docs/SERVICE.md](./SERVICE.md) — `Result` pattern, `ServiceError`, the general `search()` / window-count contract this table's service follows - [docs/CACHING.md](./CACHING.md) — `BankTransactionService`'s `bank-transaction-search` cache: TTLs, collection-tag busting, and why a scheduled Temporal activity reading through it (the Victor/RouteFusion sync dedup lookups) is safe — every read goes to Valkey, so a worker sees a bust on its next read (§4) - [docs/ESCROW_TRANSFER.md](./ESCROW_TRANSFER.md) — a similarly-shaped domain doc, for structure reference - [rules/service-pattern.md](../rules/service-pattern.md) — the window-count cardinality caveat referenced in §6