Nate Swenson

Fixing duplicate-detection bugs by grouping on a canonical key, not a raw string

Local Budget · No. 051 · 6 min read ·v0.1.1

Shipped

v0.1.1 of local-budget, my local-first bank-statement agent, carries the public-readiness pass from earlier in this tag range (issue templates, a security policy, dropped internal spike scripts, a rename from Wells-Fargo-specific to generic bank-statement language) plus two bug fixes I want to walk through. The first: recurring-charge and anomaly detection stopped recognizing Hulu and Netflix as subscriptions, because the code grouped transactions by the exact bank descriptor string, and that string drifts. The second: money moved into an investment account was counted as ordinary spend, dragging the monthly “Net” figure deep into the red. The interesting one, and the one with a transferable lesson, is the first.

The bug: same vendor, different string, every few months

My budget report has a “Subscriptions & recurring bills” section. Hulu had been on it for over a year, then quietly disappeared. Querying the database showed why: the bank had sent three different descriptors for the same subscription over five months.

HLU HULU.COM BILL   (Nov 2025 - May 2026)
HULU                (Jun 2026)
HULU SANTA MONICA   (Jul 2026)

My recurring-charge detector grouped transactions by that raw descriptor and required at least three distinct months on the same key before calling something recurring. HLU HULU.COM BILL cleared that bar easily, so Hulu was “known recurring” in the system. But this month’s charge came in as HULU SANTA MONICA, a string the detector had never seen attached to that group. It didn’t match, so it silently dropped out of this month’s report, even though the subscription never stopped.

This is a plain entity resolution problem: the same real-world merchant, several different string representations, and code that treats string equality as identity equality. Entity resolution exists as a field because this happens everywhere data crosses a system boundary, not just in banking. Plaid’s engineering blog describes the same fight at a much bigger scale: bank feeds send thousands of different ways of formatting the same transaction, and a merchant like a gas station chain can show up under wildly different descriptors depending on which bank processed the charge.

Building a canonical key

The fix has two parts: a small alias table that maps known brand tokens to one canonical name, and a lookup function that checks it before falling back to the raw string.

# A brand token, matched anywhere in the cleaned descriptor, resolves to one
# canonical name. Kept small and hand-curated on purpose: token matching is
# precise enough to avoid false merges (see Gotchas).
BRAND_ALIASES = {
    "HULU": "Hulu",
    "NETFLIX": "Netflix",
}

def canonical_key(merchant_norm):
    tokens = merchant_norm.replace(".", " ").split()
    for token in tokens:
        if token in BRAND_ALIASES:
            return BRAND_ALIASES[token]
    return merchant_norm  # no alias -> fall back to the raw string, unchanged

This is the same idea behind the Canonical Data Model pattern from enterprise integration: instead of writing a translation between every pair of representations, you translate everything into one shared form once, then work in that form everywhere downstream. Here the “systems” are just bank statement descriptors from different months, but the shape of the problem, and the fix, is identical.

The fallback matters as much as the mapping. A merchant with no alias keeps its raw string as its own key, so it’s still grouped consistently with itself, it just doesn’t get the clean display name. Skip the fallback and every un-aliased merchant collapses into one bucket keyed on None, which is a worse bug than the one you started with.

Wiring it into detection

With canonical_key in place, the recurring-charge detector groups by it instead of the raw descriptor:

from collections import defaultdict

RECUR_MIN_MONTHS = 3

def detect_recurring(txns, key_fn):
    groups = defaultdict(list)
    for t in txns:
        groups[key_fn(t["merchant_norm"])].append(t)
    return {k for k, items in groups.items() if len(items) >= RECUR_MIN_MONTHS}

def month_is_recurring(txn, recurring_keys, key_fn):
    return key_fn(txn["merchant_norm"]) in recurring_keys

detect_recurring builds the set of merchants with enough history to count as recurring. month_is_recurring checks whether a single transaction belongs to one of those merchants, this is the check that runs per report, every month, against the global recurring set.

Seeing the fix work

Five months of Hulu under two different descriptors, then a sixth month under a third:

history = [
    {"month": "2026-02", "merchant_norm": "HLU HULU.COM BILL", "amount": -13.95},
    {"month": "2026-03", "merchant_norm": "HLU HULU.COM BILL", "amount": -13.95},
    {"month": "2026-04", "merchant_norm": "HLU HULU.COM BILL", "amount": -13.95},
    {"month": "2026-05", "merchant_norm": "HLU HULU.COM BILL", "amount": -13.95},
    {"month": "2026-06", "merchant_norm": "HULU", "amount": -13.95},
]
this_month = {"month": "2026-07", "merchant_norm": "HULU SANTA MONICA", "amount": -13.95}

for label, key_fn in [("raw merchant_norm", lambda m: m), ("canonical_key", canonical_key)]:
    recurring_keys = detect_recurring(history, key_fn)
    is_recurring = month_is_recurring(this_month, recurring_keys, key_fn)
    print(f"{label}:")
    print(f"  known recurring keys = {recurring_keys}")
    print(f"  this month's charge ({this_month['merchant_norm']!r}) "
          f"recognized as recurring? {is_recurring}")

Running that:

raw merchant_norm:
  known recurring keys = {'HLU HULU.COM BILL'}
  this month's charge ('HULU SANTA MONICA') recognized as recurring? False
canonical_key:
  known recurring keys = {'Hulu'}
  this month's charge ('HULU SANTA MONICA') recognized as recurring? True

Same five months of history, same current-month charge, one boolean flips because the grouping key changed from a raw string to a resolved identity.

Gotchas

The alias table already existed, and it still wasn’t enough. My codebase already had a canonical_merchant column and an alias-resolution function, built for a completely different feature (subscription budget rollups). It correctly resolved every Hulu and Netflix variant. The bug wasn’t missing logic, it was that three separate places computed “is this recurring” independently: the detector itself, an API tool, and the report renderer’s month-to-report cross-reference. None of them read the column that already had the answer. Before writing a new normalization pass, grep your own schema for a column that already solved this in a different context.

A long-running server can hide your own fix from you. After changing the code, I re-ran the exact same query and got the exact same wrong answer. The process serving that query had loaded the old code into memory before I made the edit and was still running it. Restarting the process, not the code, was the actual fix I was missing for twenty minutes. If a fix looks like it did nothing, check whether something long-lived is still running the old version before you doubt the fix.

Fixing one consumer broke a cross-check in another. A separate feature excludes a merchant from the “unusual charge” anomaly list if it’s already known to be recurring, by comparing merchant name strings between the two lists. Once the recurring detector started returning canonical names (“Hulu”) while anomaly detection still returned raw descriptors (“HULU SANTA MONICA”), that comparison stopped matching and a normal price bump would have shown up as a false anomaly alongside the correct recurring entry. Any two functions that independently group the same kind of record by “merchant” need to agree on what identity means, or update both in lockstep.

Sources

Changelog

  • fix: recognize canonical merchant identity in recurring/anomaly detection (3679b62)
  • fix: report floor-marked spend categories (e.g. Investments) as savings (d392bff)
  • refactor: rebrand from Wells Fargo-specific to generic bank statements (f976193)
  • Public-readiness: badges, issue templates, PII scrub, dead code (8690e3a)