External Bank Feed Aggregator Integration

A design reference for pulling externally-aggregated bank transaction data into the reconciliation pipeline

1. Requirements

1.1 Functional requirements

• Let an admin link a legal entity’s bank accounts to a third-party bank-data aggregation service, so transaction history arrives automatically instead of a manually imported statement file (the manual path stays available for unlinked accounts; the two feed one pipeline, documented separately).
• Offer a single hosted linking flow, initiated in-app, authenticating the institution-and-account pair through the aggregator’s own hosted interface and returning a durable access credential reused for later pulls.
• Represent every surfaced account as an ordinary bank account master, tagged with the aggregator’s own account identifier, so it participates in the books like any other account.
• Pull new transactions across a window from “last successful sync” through today, defaulting to a twelve-month lookback on first sync.
• Land every pulled transaction as a normal bank feed line, submitted immediately, de-duplicated by the aggregator’s own transaction identifier.
• Offer both an on-demand pull and an unattended periodic pull, gated by two independent settings — enabled at all, and unattended sync separately enabled on top — and support selecting the aggregator’s operating environment (sandbox / development / production) plus an optional expanded country-and-language set.

1.2 Non-functional requirements

• Pull-only, by design: no inbound endpoint exists for the aggregator to call into. Freshness is bounded by how often a pull runs, not by how quickly the outside world can reach in (scheduling implications in §4, not a push flow invented here).
• Idempotent ingestion: a transaction already landed, by aggregator transaction id, is never landed twice.
• Non-fatal partial failure: one account’s pull failing must not block any other account’s pull in the same sweep.
• Credential confidentiality: client identifier/secret and each institution’s access credential are ordinary settings/master fields, masked where the field type supports it, never logged in plain text.

1.3 Constraints

• A single aggregator integration ships today — one settings record, one client wrapper, no multi-aggregator selection layer.
• All linked accounts share one set of aggregator credentials; the settings record is global, not scoped per legal entity.
• Unlike the payment-provider integration’s generic outbound-call auditing, no equivalent structured call log exists here — see §3.4/§5.

2. High-Level Design

2.1 Component diagram

2.2 Sequence — the pull round trip

The one system-boundary crossing, drawn as a sequence:

3. Deep Dive

3.1 Data model

Aggregator Settings (a single, global record) — an enabled flag, an unattended-sync flag (meaningful only once enabled), a client identifier, a client secret (masked field type), an environment selector (sandbox / development / production), and an optional expanded-coverage flag. The only configuration surface (per §1.3).

External Account Link — not a dedicated join record. Unlike the payment-provider integration’s dedicated link entity, this integration stores linkage as plain fields on the two masters it connects: the Banking Institution carries the durable access credential (one per institution, shared by every account linked under it), and the Bank Account carries the aggregator’s account identifier plus a “last successfully synced” watermark. So a credential is fetched once per institution, not per account — sibling accounts cannot be re-authenticated independently — and there is no independent “linked accounts” table: any Bank Account with a non-empty aggregator identifier is, by construction, linked, and that field alone is what both triggers use to discover what to pull.

Bank Feed Line — reused as defined in the bank reconciliation design (deposit/withdrawal amount, currency, description, reference number, bank-supplied transaction id/type). The aggregator-created line is inserted and submitted in one step — no intermediate unsubmitted preview, unlike a manually imported file — landing directly in the “submitted, balance remains” branch of that design’s status lifecycle. The aggregator’s transaction identifier is the de-duplication key; a transaction the aggregator itself still marks pending is skipped outright rather than landed early.

3.2 The sync algorithm

1. Resolve the window. Read the account’s watermark; default to twelve months back on first sync. Window end is always today.
2. Request and paginate. Ask the client for every transaction in that window; it requests pages until the running count matches the aggregator-reported total, returning one assembled list.
3. Land each transaction, oldest first: create and submit a Bank Feed Line for each not already present by transaction id and not still pending.
4. Advance the watermark to the newest transaction’s date, once the whole list is walked without an unhandled exception.
5. On exception at any earlier step: log the error for this account only and stop; the watermark stays at its previous value, so the next pull re-requests the same window from scratch. Transactions already landed earlier in the same failed pass are protected from duplication by the transaction-id check — a partial pull is safely retryable with no explicit resume point needed.

3.3 Aggregator client contract (illustrative)

Client(access_token=None):
  authenticate() -> uses client id + secret + environment from Aggregator Settings
  exchange_public_token(one_time_token) -> durable access credential
  get_link_token(update_mode=False) -> short-lived token for the hosted linking flow
                                        (update_mode re-authenticates an expired link)
  get_transactions(access_token, start_date, end_date, account_id?) -> paginated list

The client is a thin wrapper: no linked-account state of its own (that lives on the Bank Account masters), no retry or backoff logic — retry happens one level up, by re-running the sync algorithm.

3.4 Error handling

• Credential/session expiry is distinguished from a generic failure: the aggregator’s own “re-authentication required” error type logs under a distinct message pointing at re-linking, rather than folding into the same catch-all as everything else — the one failure mode most likely to need a person’s action, though nothing beyond the log entry proactively notifies anyone.
• No structured call log. The payment-provider integration records every outbound call and callback in a generic, replayable log; this integration has no equivalent. Failures go to the generic error log; successes leave no call-level record, only the resulting Bank Feed Lines — a scope gap carried into §5–6, not fixed here.
• The module’s generic webhook helpers are unused here. The same source module defines a signed-request validator and a callback-address builder for a named “connector,” but neither is called in this pull path or anywhere else in the read tree (repository-wide search). The address builder’s own naming implies a connector-registry structure that has no actual connector modules in this codebase — generic, unused scaffolding, plausibly left over from a broader push-capable layer that was never populated, not evidence of a hidden push path. The one client-side callback that does exist is the browser SDK’s completion handler for the linking flow — an in-page UI callback, not a server-side webhook receiver.
• Non-fatal by account: one account’s mid-pull exception aborts that account only; siblings already enqueued as separate jobs are unaffected.

4. Scale and Reliability

• Load is entirely self-scheduled. With no push channel, all traffic against the aggregator is initiated on this system’s own timetable — the polling interval is simultaneously the freshness bound and the main lever on call volume.
• The periodic sweep runs on an hourly maintenance cadence, not a daily one. The scheduler configuration places the automatic-sync entry point in the same bucket as several other short-interval tasks (including this plan’s own bulk-conversion retry sweep, documented separately); the daily bucket is empty. A fully-enabled account can be at most about an hour stale plus queue drain time, not up to a day.
• One job per account, trivially parallel — a slow or failing pull for one account never delays another; more accounts add jobs rather than lengthening a shared one.
• No rate-limit or backoff handling: the client paginates against the reported total but throttles nothing of its own; a rate-limit response surfaces as a generic logged failure, not a handled one.
• Credential expiry is silent until noticed, surfacing only as the distinct log entry from §3.4, with no proactive notification.

5. Trade-off Analysis

6. What to Revisit as the System Grows

• A structured per-pull call log, mirroring the payment-provider pattern, so a failed pull is queryable by account and window rather than only discoverable in a generic error log.
• Mid-window checkpointing, so a failure deep into a long transaction list doesn’t force re-requesting the entire window next time.
• A deliberate decision on the unused webhook helpers: build a genuine push path if the service tier ever supports one, or remove the dead surface area — leaving it as-is invites a future reader to assume a push flow exists when it does not.
• Multi-aggregator support, promoting today’s single settings record and hard-coded client into a small adapter registry, the same shape the payment-provider integration already uses, if a second service is ever added.