Lesson 12.2• 10 min read

Bank extraction and idempotent ingestion

At-least-once extraction, deduplicating on the provider’s transaction id, pending transactions that post with a new id and amount, and the transactional outbox.

What you will learn

  • Explain why the same bank transaction can arrive more than once, or change after it arrives.
  • Choose a deduplication key that survives the change from pending to posted.
  • Notify the rest of the system reliably with a transactional outbox.

Extraction is at-least-once

The primer's extraction service pulls jobs from a queue. Queues such as SQS can deliver a message more than once (chapter 7). A job can also be retried after a timeout, and providers re-send data. Many providers also push news of changes as webhooks, HTTP requests sent to your server, and send one again when your server does not answer it successfully; Plaid's documentation asks applications to expect duplicate webhooks and to handle them idempotently. So the same transaction will arrive more than once, and ingestion must be idempotent: applying the same data twice must leave the same result as applying it once.

The data itself also changes after it first arrives. The documentation of Plaid, a provider that connects apps to users' bank accounts, is a concrete example:

  • A card payment first appears as pending. When it settles, "the posted transaction is a new transaction" with its own transaction_id. A pending_transaction_id field links it to the pending one, or is null if Plaid cannot match them.
  • "The pending and posted versions of a transaction may not necessarily share the same details: their name and amount may change." A hotel hold of 320.00 USD can post as 345.82 USD.
  • Superseded or deleted transactions are reported as removed.

Choosing the deduplication key

Use the provider's id. Put a unique constraint on (account_id, provider_transaction_id) and insert. A repeat fails the constraint, or is skipped with ON CONFLICT DO NOTHING, so the database itself enforces "at most once". This is the idempotent-consumer guard from chapter 7.

Do not build the key from the transaction's details. A hash of merchant, amount and date looks deterministic, but the same purchase changes amount between pending and posted. The posted version gets a new key, both rows are stored, and the purchase is counted twice. The visual lets you try both keys.

Handle the pending-to-posted change explicitly. When a posted transaction arrives with a pending_transaction_id, insert the posted row and delete the pending one in the same database transaction. Also process the provider's list of removed transactions.

BEGIN;
WITH added AS (
  INSERT INTO transactions (account_id, provider_txn_id, seller, amount_cents, posted_on)
  VALUES (:account_id, :provider_txn_id, :seller, :amount_cents, :posted_on)
  ON CONFLICT (account_id, provider_txn_id) DO NOTHING
  RETURNING provider_txn_id
)
INSERT INTO outbox (event, payload)
SELECT 'transaction.added', :event_payload FROM added;  -- no event for a repeat
DELETE FROM transactions
WHERE account_id = :account_id
  AND provider_txn_id = :pending_transaction_id;    -- if the provider sent one
COMMIT;

Avoid putting a Redis "seen" check in front of the database as the guard. A crash between setting the Redis key and committing loses the transaction for good (chapter 7). If you use a cache to skip obvious repeats, the database constraint must still decide.


Notifying the rest of the system: the outbox

The Budget Service and notifications need to hear about new transactions. Writing the row and then publishing a message separately is a dual write: a crash in between loses the event, or announces a row that was rolled back.

The transactional outbox (chapter 7) writes the event to an outbox table in the same database transaction as the rows. A relay then publishes outbox rows to the queue, either by polling or by change data capture such as Debezium reading the database log. The relay may publish an event more than once, so consumers must be idempotent too.


When the primer's design needs this

The primer keeps raw extraction results as log files in an object store and then updates the SQL tables. Its extraction service is the one place where duplicates, retries and pending-to-posted changes meet, so that is where the unique constraint, the pending replacement and the outbox belong.

Idempotent transaction ingestion

Each batch is one database transaction that writes the rows and their outbox events together. Compare deduplicating by the provider's transaction id with a key built from the transaction's details.

Ledger: 0 rows, total 0.00 USD
empty
Outbox events (relayed to the queue)
none
Send events in order to see what the ledger stores.
Account data provider1× instanceLoad balancer2× instancesExtraction service3× instancesSQL database(transactions andoutbox)2× instancesOutbox relay3× instancesMessage queue(events)2× instancesBudget service3× instancesAccount data provider1× instanceLoad balancer2× instancesExtraction service3× instancesSQL database(transactions andoutbox)2× instancesOutbox relay3× instancesMessage queue(events)2× instancesBudget service3× instancesAccount data provider1× instanceLoad balancer2× instancesExtraction service3× instancesSQL database(transactions andoutbox)2× instancesOutbox relay3× instancesMessage queue(events)2× instancesBudget service3× instances
Figure 2

The extraction service writes each transaction and its outbox event in one database transaction; a relay publishes the events to the budget service.

Follow one transaction. The provider sends it to the extraction service, by a webhook or in the reply to an extraction job, perhaps not for the first time. In one database transaction, the extraction service inserts the row, doing nothing if the account and the provider’s transaction ID are already there, writes an outbox event only for a row it inserted, and deletes the pending row a posted transaction replaces. The outbox relay reads new outbox rows and publishes them to the message queue, and the budget service consumes them, ignoring an event it has already applied, since the relay can publish one twice. No cache stands in front of the database: the unique constraint decides what is a repeat. Servers are counted at the lab’s 1,000 a second each for 2,000 transactions a second, plus one spare; one load balancer and one queue node are enough, plus a spare each.

  • Account data provider: 1 × Users
  • Load balancer: 2 × Load balancer
  • Extraction service: 3 × App server
  • SQL database (transactions and outbox): 2 × Relational database
  • Outbox relay: 3 × App server
  • Message queue (events): 2 × Queue
  • Budget service: 3 × App server
  • Account data provider sends requests to Load balancer
  • Load balancer sends requests to Extraction service
  • Extraction service sends requests to SQL database (transactions and outbox)
  • Outbox relay sends requests to SQL database (transactions and outbox)
  • Outbox relay sends requests to Message queue (events)
  • Message queue (events) sends requests to Budget service

Key takeaways

  • Queues redeliver, jobs retry and providers resend; a pending card payment posts later as a new transaction, possibly for a different amount.
  • Deduplicate on (account_id, provider_transaction_id) with a unique constraint; a key built from merchant, amount and date breaks when the amount changes.
  • Insert the posted row and delete its pending row in the same database transaction.
  • Write the outbox event in the same database transaction as the rows, so other services hear about every row that committed and none that rolled back; the relay may publish twice, so consumers must be idempotent.
Sources5