Bookkeeping software lives or dies on a boring feature: when a bank transaction shows up, guess what it is. If the app can look at UBER *EATS 8829 and say “you called this Meals last time,” the user taps accept and moves on. If it can’t, they’re doing data entry, and they’ll leave.

The naive version — exact-match the description against past transactions — fails immediately, because banks never send the same string twice. UBER *EATS 8829, UBER* EATS, UBER EATS SYDNEY are the same merchant with different noise. You need fuzzy matching. The reflex today is to reach for embeddings or a model. You don’t need one. Postgres ships the tools, and matching against the user’s own history makes them work far better than they have any right to.

The two Postgres functions that do the work

The fuzzystrmatch extension gives you two functions that pair beautifully:

CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;

soundex(text) — a phonetic code. soundex('UBEREATS') and soundex('UBER EATS') collapse to the same code. It’s crude and English-biased, but as a prefilter it’s perfect: it buckets “sounds roughly like this” cheaply.
levenshtein(a, b) — edit distance: how many single-character insert/delete/substitutes turn one string into the other. This is the precise measure, but it’s O(len × len), so you don’t want to run it across every row.

The trick is to use them together: soundex narrows the candidate set to a phonetic bucket, then levenshtein measures the survivors and you keep only those under a small threshold.

The query

Here’s the real query that suggests a category for an incoming transaction. It matches against a normalized clean_text (the raw descriptor with the noise stripped out) — never the raw string — and, crucially, only against this user’s past transactions:

SELECT bt.date AS suggestion_source_date, bt.category_id, clean_text,
       levenshtein(clean_text, $matching_text) AS score
FROM bank_transactions bt
JOIN bank_accounts ba ON ba.id = bt.bank_account_id
JOIN bank_customers bc ON bc.id = ba.bank_customer_id AND bc.user_id = $user_id
WHERE bt.date >= $from_date
  AND soundex(bt.clean_text) = soundex($matching_text)
  AND levenshtein(clean_text, $matching_text) < $levenshtein_threshold
  AND bt.category_id IS NOT NULL
  AND bt.discarded_at IS NULL
  AND bt.transaction_type = $transaction_type
ORDER BY bt.date, bt.id DESC
LIMIT 1

Read the WHERE clause top to bottom, because every line is there for a reason learned the hard way:

bc.user_id = ... — match only against the person’s own history. This is the whole reason it works without a model: one freelancer’s transactions are a tiny, self-consistent vocabulary. “How did you categorize this before” beats “how does the world categorize this.”
soundex(...) = soundex(...) — the cheap phonetic prefilter, so levenshtein only runs on a handful of rows.
levenshtein(...) < threshold — the precise filter. The default threshold is small (2), so UBER* EATS matches UBER *EATS but genuinely different merchants don’t bleed together.
category_id IS NOT NULL — only learn from transactions the user actually categorized.
discarded_at IS NULL — never learn from something they deleted.
transaction_type = ... — a debit and a credit with similar text are not the same thing; a refund shouldn’t inherit a payment’s category.
ORDER BY bt.date, bt.id DESC LIMIT 1 — collapse several matches to one deterministic pick. And an honest wart, because I’d rather flag it than have you spot it: the DESC binds only to bt.id, so bt.date sorts ascending — which means this takes the oldest match, not the newest. If the intent is “trust my most recent decision,” it should be bt.date DESC. I read that as a latent bug in the query, and writing the clause out for this post is how I noticed it.

The Ruby around it is thin — it runs the SQL and wraps the row in a suggestion value object, so callers get a typed result, not a hash:

def match_transaction_category
  result = ActiveRecord::Base.connection.exec_query(sql, 'SuggestTransactionCategory').first
  return nil if result.nil?

  TransactionCategorySuggestion.new(
    transaction: @transaction,
    suggestion_source_date: result.fetch('suggestion_source_date'),
    bank_transaction_category_id: result.fetch('category_id'),
    matching_method: MatchInfo::LevenshteinMatchInfo.new(score: result.fetch('score').to_i)
  )
end

The score (the edit distance) rides along on the suggestion, so the UI can show stronger matches first, and you can tune the threshold later with real numbers instead of guesses.

One safety note, since this builds SQL by hand: the free-text value is the only injection surface, and it’s passed through connection.quote before it ever reaches the query —

def matching_text
  @_matching_text ||= ActiveRecord::Base.connection.quote(@transaction.autosuggest_matching_text)
end

The other interpolated values — the user id, the date, the edit-distance threshold, the transaction type — are internal (integers, a formatted date, a settings value), not free text a bank or a user types. That’s the load-bearing assumption, and worth stating plainly: this stays safe only as long as those remain non-string internal values. Quote the free text; keep everything else strictly typed.

Why not machine learning?

Because the problem doesn’t need it, and the cost of not needing it is enormous:

It’s explainable. “We suggested Meals because on March 3rd you categorized a nearly identical transaction that way” is a sentence you can put in the UI. A model’s “0.87 confidence” is not.
It’s per-user by construction. There’s no training pipeline, no cold-start, no model to retrain when a user changes how they categorize. Their history is the model, and it updates the instant they accept a suggestion.
It ships this week. One extension, one query, one value object. No infra, no GPU, no vendor.

The honest limits: soundex is English-centric (fine for merchant strings, weak for other scripts), and levenshtein is character-level, so it matches shapes of strings, not meaning. The phonetic prefilter has to stay in front of it or the edit-distance scan gets slow on heavy users — and it does stay cheap here, because there’s a functional index on soundex(clean_text) backing it, so that prefilter is an index lookup rather than a table scan.

For “categorize this freelancer’s bank feed,” this is the right amount of technology. fuzzystrmatch turned a feature that reads like “we need ML” into one query, and scoping every match to the user’s own history is what makes it both accurate and explainable — the suggestion can literally point at the past transaction it copied, which is the only kind of automation people actually trust.

I build financial and data-heavy features in Rails and Postgres, and I have a bias toward the cheap correct solution over the impressive fragile one. If that’s the kind of problem you’re staring at, get in touch.