Plaid Sync: Cursor, Versions, Promotion

This page covers the core of the bank pipeline: how a Plaid TRANSACTIONS webhook becomes cursor-driven sync runs, append-only transaction versions, and candidate operating source events — and why each of those layers exists. The logic lives in apps/api/src/handlers/plaid-sync.ts, deliberately transport-free (no SQS, no Lambda types) so it unit-tests against fake repos and a fake Plaid client; the Lambda entry (apps/api/src/entries/plaid-sync-worker.ts) only builds the real dependencies and loops the batch. For where the resulting records sit in the domain model, see Domain seams.

Why cursor sync makes at-least-once safe

Plaid’s /transactions/sync is cursor-driven. Each connection stores its cursor; a sync run opens with cursor_before, drains added/modified/removed pages, and completeSyncRun atomically advances the cursor only on success. On failure the run is marked failed, the error rethrows, SQS retries — and because the cursor never advanced, the retry replays the same window. A duplicate webhook, conversely, syncs from the already-advanced cursor and finds nothing. That single invariant is what makes at-least-once SQS delivery harmless.

ITEM webhooks take a much shorter path: itemStatusForCode maps the code to a connection-status transition (ERROR → error; USER_PERMISSION_REVOKED / USER_ACCOUNT_REVOKED / PENDING_DISCONNECT → disconnected; LOGIN_REPAIRED → active) and everything else is logged and acked.

Transaction versions: the append-only raw layer

Every added/modified/removed transaction appends a row to source_bank_transaction_versions — nothing is ever updated in place. appendTxn gives a brand-new transaction version: 1; a modification or removal supersedes the current version (supersedes_id) and increments the version number. The “live” feed is simply transaction_state IN ('added','modified') — superseded and removed rows stay forever as evidence (ADR-0003 makes the raw version a first-class evidence target for “show me exactly why you think this expense is real”).

There is a subtle race guard in appendTxn worth knowing:


// apps/api/src/handlers/plaid-sync.ts
// Idempotency: /transactions/sync should only ever return a given txn as
// "added" once. If we already hold a version for it, this "added" is a
// redundant re-pull — two near-simultaneous syncs racing on the same
// un-advanced cursor ... Skip rather than appending a duplicate version.
if (args.state === 'added' && current !== undefined) {
  return current;
}

True-concurrent first inserts are additionally caught by UNIQUE(tenant_id, provider_transaction_id, version): the loser’s run fails, SQS retries, and the guard skips on the retry.

Promotion: version → candidate operating source event

Account attribution check

promoteVersions (OPS-25, step i→ii) only promotes a version whose source_bank_account is attributed to a vehicle (legal_entity_id set) and whose vehicle resolves to an accounting_entity books scope. Unattributed accounts keep their raw versions but promote nothing — no vehicle means no scope means nothing can ever post.

Deterministic OSE identity

The candidate is built with buildCandidateOperatingSourceEvent (packages/persistence/src/source-bank-event-promotion.ts) under the deterministic id `ose:bank:${version.id}`. A re-sync or replay returns already_saved instead of duplicating — one OSE per version, forever. This determinism is also what makes promoteCurrentBankAccountTransactions safe: when a bank account is attributed to a fund after transactions synced, the whole history is re-driven and converges idempotently.

Follow-on dispatch

On a fresh save, the worker calls the ADR-0004 D3 seam:


await promotion.dispatcher.dispatch({
  trigger: 'candidate_created',
  operatingSourceEvent: event,
});

FollowOnDispatcher defaults to NOOP_FOLLOW_ON_DISPATCHER; production wires persistence.followOnDispatcher. This is where matching, paperwork prompts, and coverage-gap routing attach without the sync path knowing about any of them.

Supersession (OPS-30)

A modified transaction’s new version retires the prior version’s OSE, pointing it at the replacement (reason: 'bank_transaction_modified'). A removed transaction supersedes its OSE with no replacement — the cash movement no longer exists.

Everything here is review-only: every OSE enters as candidate, and only an acceptance policy can move it to accepted — the precondition for it ever becoming a ledger_source_event. The sync worker cannot post to the GL. See Domain seams.

Sign convention and amount handling

Plaid’s convention is inverted from intuition: amount > 0 is money out (debit), amount < 0 is money in (credit). bankMovementFromTxn maps this to direction plus an absolute two-decimal amount, and skips transactions with a zero/missing amount, currency, or date rather than guessing.

Pitfalls.

Never advance the cursor outside completeSyncRun, and never mark a failed run complete — the whole at-least-once safety story rests on “cursor advances only on success”.
Never interpret a raw Plaid amount sign without going through bankMovementFromTxn; a direct read silently inverts inflows and outflows.
Never append or update transaction rows outside appendTxn — the version chain (supersedes_id) and the ose:bank:${version.id} determinism both assume it is the single writer.

Side channels in the same sync run

Account backfill (OPS-41) — runs before version appends so freshness timestamps stay atomic; unknown account_ids get a bare source_bank_account row (null attribution, to be enriched later) and existing rows are never clobbered.
Balance snapshots — each sync appends per-account balance observations (appendBalanceSnapshot, minor units, source plaid_transactions_sync).

Two reconciliation passes (pending→posted and account re-key) also run inside the sync — they are covered in Operational gotchas because each one exists to neutralize a specific Plaid behavior that bit us in production.