Transaction Deduplication#

Sure prevents duplicate transaction imports using two complementary strategies: a database-level unique constraint (primary) and an MD5 content fingerprint (fallback for providers that omit identifiers).

1. Primary: Composite Unique Index on entries#

Every synced transaction is stored as an Entry row tagged with an external_id (the provider's transaction identifier) and a source string (e.g., "enable_banking", "plaid").

A partial unique index enforces that no two entries on the same account can share the same (account_id, source, external_id) triple 1:

index_entries_on_account_source_and_external_id
  ON entries (account_id, source, external_id)
  WHERE external_id IS NOT NULL AND source IS NOT NULL

This index replaced an older (external_id, source) index to allow multiple providers to sync the same account without colliding 2. The WHERE clause means rows without either column are exempt — safe for manual/CSV-imported entries that have no external identifier.

The model-level validation mirrors the index 3:

validates :external_id, uniqueness: { scope: [:account_id, :source] },
  if: -> { external_id.present? && source.present? }

At import time, Account::ProviderImportAdapter#import_transaction resolves the entry via find_or_initialize_by(external_id:, source:) 4, making every sync idempotent: existing entries are updated in place, new ones are created.

2. Fallback: MD5 Content Fingerprint (Enable Banking)#

PSD2 / Open Banking permits ASPSPs (banks) to omit both transaction_id and entry_reference. When Enable Banking delivers such transactions, EnableBankingEntry::Processor.compute_external_id generates a deterministic fingerprint instead 5:

1. Prefer a provider ID — uses transaction_id, then entry_reference, prefixed as enable_banking_<id>.

2. Fall back to content hash — joins the following fields with a Unit Separator (\x1F) and MD5-hashes the result:

   • booking_date / value_date / transaction_date (first present)
   • transaction_amount.amount and .currency
   • credit_debit_indicator (DBIT / CRDT)
   • creditor.name / debtor.name
   • remittance_information (array elements sorted and joined with |)

   The resulting external_id is prefixed enable_banking_content_<md5hex>.

3. Reject unidentifiable transactions — if all fields are blank after stripping separators, compute_external_id returns nil, and process raises ArgumentError 6, preventing silent data loss.

The batch processor calls compute_external_id once per transaction before delegating to EnableBankingEntry::Processor#process so that already-excluded/claimed IDs can be skipped cheaply without touching the adapter 7.

3. Manual / CSV Import Deduplication#

When a new provider transaction arrives and no entry exists for its (external_id, source), import_transaction additionally searches for an unlinked entry (no external_id) with the same (date, amount, currency) — see find_duplicate_transaction. If found, the adapter "claims" it by writing external_id and source onto the existing row, preventing a second entry from being created.

4. Excluded-ID Allowlist (Enable Banking)#

Before iterating raw transactions, EnableBankingAccount::Transactions::Processor pre-fetches two sets of external_id values to skip 8:

These are unioned into an excluded_ids set and checked with a single Set#include? per transaction, avoiding N+1 queries 9.

Key Files#