Bank extraction and idempotent ingestion

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. 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. Plaid's documentation for its transactions API 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 USD 320.00 can post as USD 345.82.
• 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 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.