Plaid: Handling Pending → Posted Transactions

On Plaid-linked connections (Chase, Amex, Truist, and other Plaid-backed institutions), a pending transaction that posts is not an in-place update. Plaid reports it as a deletion of the pending transaction plus the creation of a brand-new transaction with a different id. If your integration keys any local state (a "reviewed" flag, notes, a category override) off the transaction id, that state doesn't carry over automatically.

Why This Happens

Plaid's sync delta has three buckets: added, modified, removed. modified is a genuine in-place update — same transaction id, a field changed (categorization backfill, merchant cleanup, amount correction). Pending → posted is removed (the old pending id) + added (a new id), correlated only via a pending_transaction_id field pointing back at the removed id.

Araucaria mirrors this: the old pending transaction is hard-deleted (you'll get a transaction.deleted webhook for it) and the posted transaction shows up as a new row on your next fetch.

What Araucaria Exposes

The new posted transaction carries an optional previousTransactionId field — the Araucaria id (the same id space you already use, txn_...) of the pending transaction this row replaced. It's present only on Plaid connections, only on a promoted posted transaction. See the Transaction Object reference.

{
  "id": "txn_01HQ3K5J7X8Y9Z0A1B2C3D4E7H",
  "amount": -450,
  "currency": "USD",
  "description": "Coffee Shop",
  "transactionDate": "2024-01-16",
  "status": "posted",
  "previousTransactionId": "txn_01HQ3K5J7X8Y9Z0A1B2C3D4E5G"
}

You'll also still receive a transaction.deleted webhook for the old id, as with any deletion.

The Ordering Hazard

transaction.deleted for the old id and your discovery of the new posted transaction are not ordered relative to each other. Araucaria's event delivery has no ordering key, and failed webhook deliveries are retried independently with backoff up to 24 hours. There's no transaction.created event either — you discover new transactions by reacting to accounts.updated and fetching, which is itself a separate, independently-retried delivery.

Concretely, either of these can happen, and both are normal:

• You receive transaction.deleted for the pending id, and only later (possibly hours later, on a webhook retry) fetch the posted transaction that references it.
• You fetch and store the posted transaction (with previousTransactionId set) before the transaction.deleted webhook for the old id has arrived.

Any reconciliation logic that assumes one always precedes the other will lose data intermittently in production.

Recommended Pattern: Delete Always, Cache the Carry-Forward State

Keep your main transaction store simple — always hard-delete on transaction.deleted, exactly as the event says. Don't turn it into a soft-delete; that pushes "is this row live?" filtering into every other query, report, and balance calculation you have.

Instead, keep a small, separate, self-expiring cache of just the few fields you need to carry forward (reviewed flag, notes, category override — not the whole transaction). A Redis key with EXPIRE, a DynamoDB item with a TTL attribute, or a tiny table with a nightly purge all work.

// On transaction.deleted(X): always cache first, then delete.
async function handleTransactionDeleted(transactionId: string) {
  const existing = await db.transactions.findById(transactionId);
  if (existing) {
    await reviewCache.set(
      transactionId,
      { reviewStatus: existing.reviewStatus, notes: existing.notes },
      { ttlSeconds: 14 * 24 * 60 * 60 }, // 14 days
    );
  }
  await db.transactions.delete(transactionId);
}

// On seeing a transaction with previousTransactionId set (from a GET pull
// triggered by accounts.updated, or your own poll):
async function handleIncomingTransaction(txn: Transaction) {
  if (txn.previousTransactionId) {
    const carried = await resolveCarryForward(txn.previousTransactionId);
    if (carried) {
      txn = { ...txn, ...carried };
    }
  }
  await db.transactions.upsert(txn);
}

// Check both places: the old row may still be live (delete hasn't arrived
// yet) or already gone (delete arrived first, state is in the cache).
async function resolveCarryForward(previousTransactionId: string) {
  const stillLive = await db.transactions.findById(previousTransactionId);
  if (stillLive) {
    return { reviewStatus: stillLive.reviewStatus, notes: stillLive.notes };
  }
  return reviewCache.get(previousTransactionId); // undefined if expired or never existed
}

The one detail that makes this order-independent: resolveCarryForward checks the live table first, then the cache — not just the cache. The cache is only populated once the delete has actually happened; if the new transaction arrives first, the old row is still sitting in your main table, untouched, and the local state is right there.

Sizing the TTL

Set it generously — at least 7 days, we'd suggest 14. It only has to hold a few small fields per transaction, so the storage cost of being generous is trivial, and it needs to outlast both normal settlement lag and the worst-case webhook retry tail (up to 24h on the final retry, plus whatever your own processing queue adds).

Alternative: Soft-Delete in Place

If your transaction store already handles soft-deletes everywhere (a deletedAt column your queries already account for), you can skip the separate cache and just tombstone the row in place for the same retention window instead of hard-deleting immediately, then purge it after. Functionally equivalent — pick whichever fits your existing data model with less new machinery.

Idempotency Notes

• previousTransactionId may reference an id you never stored (you joined after that pending transaction was created, or missed an earlier webhook). Treat a cache/table miss as a no-op — nothing to carry forward, just store the transaction normally.
• Not every pending→posted promotion is guaranteed to correlate: if the removal and the replacement land in separate sync deltas rather than together (not the common case), previousTransactionId will simply be absent. Handle that the same as any other new transaction.
• transaction.deleted webhooks are already at-least-once — dedupe by the event id before running the cache-then-delete step, so a redelivered event doesn't re-run it against an already-deleted row.