The 10-Minute Collision Window: Killing Duplicate Bank SMS Transactions

When you build an automated transaction ingestion pipeline from banking SMS notifications, your biggest enemy is not malformed text.

Your biggest enemy is duplicate delivery.

Telecommunication networks and banking infrastructure duplicate messages regularly:

1. Network Retransmission: If a phone travels through a weak cellular zone or hands off between cell towers, carrier SMS gateways frequently deliver the same message twice.
2. Dual Bank Alerts: Many Indian banks send two distinct notifications for a single POS transaction: first an authorization alert from the card processor (e.g. Visa or Mastercard), followed minutes later by an SMS from the issuing bank confirming the debit.
3. Inbox Re-Scanning: When a user runs a historical inbox scan, the scanner will encounter messages that were already captured in real-time by the application's background BroadcastReceiver days earlier.

If an expense tracker records both alerts, your account balance is deducted twice, ruining your ledger.

However, fixing this with naive deduplication is even more dangerous: it can cause silent data loss.

Here is how a subtle regression in FinLedger led to the discovery of the deduplication collision dilemma, and how we solved it with a calibrated four-factor matching engine.

The Flawed Query: When Deduplication Causes Silent Data Loss

In an early development sprint, FinLedger implemented a straightforward duplicate detection query in SQLite. The logic attempted to catch both identical timestamps and identical transactions:

-- The Flawed Early Query
SELECT * FROM transactions 
WHERE dateTimestamp = :timestamp 
   OR (amount = :amount AND accountId = :accountId);

Can you spot the catastrophic flaw in this logic?

Look closely at the OR condition:

flowchart TD
    subgraph SilentLoss["The Danger of Naive Deduplication"]
        MorningCoffee["9:00 AM: Buy Coffee for Rs. 150.00 at Starbucks"] --> Insert1["Inserted into Ledger:
        Amount: 150.00, Time: 9:00 AM"]

        LunchCoffee["1:30 PM: Buy Coffee for Rs. 150.00 at Starbucks"] --> Check["Deduplication Query:
        'Does a transaction exist with Amount = 150.00 on this account?'"]

        Check -->|YES: Matches Morning Coffee!| Drop["Catastrophic Bug:
        Lunch coffee dropped as 'Duplicate'!
        Rs. 150 quietly vanishes from ledger tracking."]
    end

Because the query used an over-broad OR clause:

1. If you bought two coffees of the exact same price (Rs. 150.00) on the same day, the second coffee was rejected as a duplicate!
2. Even worse, if you ran automated bills where two subscriptions (e.g., Spotify and iCloud) processed at the exact same second (dateTimestamp), the second subscription was discarded!

This was a severe accounting bug discovered during regression testing (documented under TASK-H4.4 in the FinLedger codebase). We were dropping genuine financial transactions to prevent duplicate SMS alerts.

The Four-Factor Matching Engine (DuplicateDetectionTest.kt)

To prevent duplicate bank alerts without dropping legitimate recurring transactions, we completely rewrote the deduplication predicate.

A candidate transaction is only classified as a duplicate if all four discriminating factors match simultaneously:

flowchart TD
    Candidate["Incoming SMS Candidate"] --> Factor1{"1. Amount Match:
    abs(a - b) < 0.005
    (Within half a paisa)"}

    Factor1 -->|No| Safe["Insert as Unique Transaction"]
    Factor1 -->|Yes| Factor2{"2. Time Window:
    abs(t1 - t2) <= 10 Minutes
    (600,000 ms)"}

    Factor2 -->|No| Safe
    Factor2 -->|Yes| Factor3{"3. Polarity Match:
    candidate.type == seen.type
    (DEBIT vs CREDIT)"}

    Factor3 -->|No| Safe
    Factor3 -->|Yes| Factor4{"4. Entity Match:
    candidate.merchant == seen.merchant"}

    Factor4 -->|No| Safe
    Factor4 -->|Yes| Duplicate["CONFIRMED DUPLICATE:
    Drop without corrupting balances"]

Let's examine why each factor is essential.

1. Amount Precision (Half a Paisa)

In financial math, floating-point numbers cannot be compared with direct equality (==). The engine verifies that:
abs(seen.amount - candidate.amount) < 0.005
This accounts for minuscule floating-point rounding quirks while strictly preserving distinct transactions.

2. The 10-Minute Collision Window (600,000 ms)

Why 10 minutes?

• Cellular network retries and carrier message duplication almost always occur within 60 to 180 seconds.
• Secondary bank authorization alerts for POS machines occasionally experience 5 to 7 minute delays during peak server loads.
• However, it is virtually impossible for a human being to purchase the exact same item, for the exact same price, at the exact same merchant, within a 10-minute window without it being an accidental duplicate tap or double-charge.
• If you buy coffee at 9:00 AM and another coffee at 1:30 PM, the delta is 270 minutes—well outside the collision window. Both are correctly preserved.

3. Transaction Polarity (Type Matching)

A debit followed immediately by a credit (such as an instant refund or reversed payment) carries the same amount, timestamp, and merchant. By verifying that candidate.type == seen.type, an incoming refund credit of Rs. 500 is never dropped because of the original Rs. 500 debit alert.

4. Counter-Party Normalization

The merchant name must match after string normalization (stripping trailing UPI reference numbers and whitespace). If you pay Rs. 500 for a taxi and Rs. 500 for groceries five minutes later, the distinct merchant identifiers ensure neither transaction is dropped.

Testing with Automated Regression Suites

To guarantee that future refactoring never reintroduces duplicate data loss, FinLedger maintains a comprehensive unit test suite in DuplicateDetectionTest.kt.

The test suite systematically asserts against real-world edge cases:

• Two different transactions occurring at the exact same millisecond (e.g. simultaneous automated investments) are asserted as NOT duplicates.
• Identical amounts at different merchants are asserted as NOT duplicates.
• Identical amounts and merchants separated by 11 minutes (660,000 ms) are asserted as NOT duplicates.
• Identical amounts, merchants, and accounts arriving within 3 minutes with matching transaction references are asserted as DUPLICATES and successfully consolidated.

Key Takeaway

Deduplication in financial software cannot be treated as a simple database uniqueness constraint.

Real-world banking alerts are messy, delayed, and asynchronous. By establishing a strict multi-factor collision window—spanning amount precision, time delta, transaction polarity, and merchant identity—FinLedger eliminates redundant SMS alerts without ever losing a penny of user data.

FinLedger is a privacy-first, offline-first personal finance application for Android that models bank accounts, credit cards, and cash without double-counting.

Explore FinLedger on Google Play: https://play.google.com/store/apps/details?id=com.devanshufinance.app