BankingSync: the first recap on v4

A little under four weeks ago, v4 went out on the stable channel, and with it, for the first time, BankingSync left the house it had been written in. What followed was, in hindsight, entirely predictable: bug reports from people I have never met, about banks I don’t bank with - and every single one of them turned out to be less a report about BankingSync than a report about somebody’s bank.

That doesn’t get me off the hook, mind you. A tool sitting between a bank and a budget exists for precisely the moment a bank does something unexpected; if it only ever coped with the banks its author happens to use, it would be a script, and a rather self-indulgent one at that. What it does mean is that every fix in this post began the same way - with me asking an esteemed user what their bank actually sends, and them being patient enough to show me.

Housekeeping first

A handful of point releases have gone out quietly since v4. A renewed bank session used to leave its account stranded under the old identifier; it is now carried across, and the account found again by its identification hash. The release images are built for arm64 as well as amd64, and HTTPS can be switched off for those of you running behind a reverse proxy of your own - both of which, much to my delight, arrived as pull requests from strangers on GitHub. (The images are still built on my own pipeline. Some habits I’m not giving up.)

Two halves of one purchase, waiting in separate rooms

The first report was a duplicate, which is the one kind of bug this project was built to make impossible: one purchase, two rows in the budget.

What had happened is almost comically simple once you see it. The card authorisation came in first, and the matcher, unsure about it, held it for review - which, quite as intended, keeps it out of the budget until somebody decides. The budget, however, is also where the matcher looks for its candidates. So when the booking arrived a few days later, it was dutifully weighed against every row from that shop, save for the one it actually belonged to, which was sitting in the review queue where nobody could see it. Nothing convincing turned up, the booking was imported on its own, and as soon as the review was answered, the authorisation followed it into the budget. Two rows, each of them individually correct, and together wrong.

The fix came from reading Fellegi and Sunter a little more carefully than I evidently had the first time: what their model holds for review is a pair, not a record, and a record that has been held back has by no means left the pool. Bookings are now weighed against the authorisations waiting in the queue as well. If the pairing clears the automatic threshold, the booking settles the authorisation on its own, and one row is written for the two, dated like the authorisation; if it doesn’t, the booking waits alongside it, and its review offers the authorisation as a candidate, marked also waiting for review, so that a single answer settles both.

The tests guarding this read like a retelling of the report itself:

TestSync_aBookingSettlesItsHeldAuthorisation
TestSync_aSettledHeldAuthorisationSeenAgainIsNotImported
TestSync_aDriftedBookingIsHeldBesideItsHeldAuthorisation
TestSync_pruningTheReviewQueueReleasesItsHeldKeys

The second coffee

The second problem pointed the opposite way, and it’s the more serious of the two by some distance, for it is the only failure this project can have that loses money rather than counting it twice.

Banks re-deliver. Every run fetches the last few weeks anew, and every booking inside that window comes back, every single time. For a booking carrying a bank reference, recognising it again is a lookup. For one that doesn’t, BankingSync left it to the matcher: the booking found its own row, agreed with it on payee, amount and date, and settled there, as it should.

Now buy two coffees at the same café, for the same price, two days apart, from a bank that supplies no reference. The second coffee arrives and is compared against the first coffee’s row - same payee, same amount, two days between them - and that is precisely the comparison a re-delivery of the first coffee would have produced. Not a similar one; the identical one, level for level. There is no weighting of those three fields under which the two cases come apart, and so the second coffee was merged into the first and quietly vanished from the budget, without so much as a log line to mourn it.

If the fields can’t tell the two apart, the record itself has to. Every booked record now carries a content key: a hash over the complete record the bank sent, stripped of transaction_id (of which more in a moment) and of every field that is null, since Enable Banking pads its entire schema with nulls, and a schema growing by one field must not re-identify every record ever imported. Records identical down to the last byte are told apart by their position among the day’s records - which is, as it turns out, how hledger’s importer deals with a neighbouring problem, remembering not merely the last date it saw but how many records it saw on it. A booking imported once is, from then on, recognised by what it is rather than by what it resembles.

On its own, that would have been rather a blunt instrument, though. Banks do touch up their records - a merchant name gets corrected, a remittance line turns up a day late - and a strict “different hash, different booking” rule would turn every such correction into a duplicate of its own. So the hard rule has a reach: a booking may never adopt a row standing for a record the feed can no longer deliver, one older than the fetch window, which therefore cannot possibly be coming back. Inside the window, the matcher still decides, and every time it settles a booking onto a row that was booked for a different record, bankingsync_booking_identity_changed_total ticks up by one - which hands me, for the first time, an actual number for how stable each bank’s booked records are.

TestSync_aSecondIdenticalPurchaseOnALaterDayIsNotAbsorbed
TestSync_twoIdenticalBookingsOnOneDayStayTwoAcrossRuns
TestSync_aDriftedRedeliveryWithinReachStillAdoptsItsOwnRow

A field that said, in writing, not to use it

While chasing the coffee, I reread Enable Banking’s FAQ, and came across this sentence about transaction_id:

It should not be used as a unique reference to identify transactions, because the value may change if the list of transactions is retrieved again.

BankingSync used transaction_id as its fallback reference wherever a bank supplied no entry_reference - which is to say, it relied on the one field it had been told in writing not to rely on, in precisely the situation that sentence was written for. There’s no good reason to report for that one.

It’s gone now. A bank without an entry_reference is identified by content, as above, and anything an earlier version imported under a transaction_id is still found through a bridge that dismantles itself once the 38-day retention has passed over those records. A new counter, bankingsync_reference_source_total, reports which identifier each bank actually supplies. For what it’s worth: my own Revolut export came back with 1,038 booked records, every one of them carrying an entry_reference and not a single one a transaction_id, so Revolut users never went anywhere near this.

mandatereference:,creditorid:,remittanceinformation:

The third report came from an ING customer, and the heading above is, verbatim, how their notes began. ING Deutschland delivers its remittance information as labelled fields glued into one string, and it’s a known quirk - Firefly III’s data importer ran into the very same string via Nordigen. BankingSync’s SEPA parser knew EREF+, MREF+, CRED+ and SVWZ+, had never seen this particular dialect, and so copied it into the notes as it came.

Reading it takes one regular expression:

var ingRemittancePattern = regexp.MustCompile(`(?is)^\s*mandatereference:(.*?),creditorid:(.*?),remittanceinformation:(.*)$`)

The only detail worth pointing out is the last group, which runs to the end of the string rather than to the next comma: remittance texts have commas of their own, and PAYPAL (EUROPE) S.A.R.L. ET CIE, S.C.A. would otherwise have lost the better part of its name.

The payee was the trickier bit, and a neat example of a fix that is obvious and wrong at the same time. Where a bank sends no counterparty name - a transfer between your own accounts, typically - the payee falls back to the first remittance line, so an ING transfer could just as well end up with a payee called mandatereference:,creditorid:,…. The obvious fix is to clean that as well. For an authorisation without a bank reference, however, the payee is part of the very identity it is stored under, and an authorisation stored under the raw payee before the upgrade would never have met a booking computed under the clean one afterwards: every open ING authorisation would have been imported a second time on the day you updated. So the identity keeps the payee exactly as it always was, and only the payee you actually see gets cleaned.

Writing the test for that reminded me of something I really ought not to have forgotten. The first run of TestSync_anINGAuthorisationStillFindsItsBookingAcrossTheUpgrade failed, and the reason was a mistake in the test’s own setup: the pending entry it planted pointed at a row that didn’t exist. Every assertion about the budget passed regardless - one row, cleared, pending entry consumed - because the matcher had simply stepped in and done the key’s job for it. Only the assertion asking how the booking got there caught it. There is a comment on an older test in the repository, written for exactly this situation:

Measured: with the payee, the status or the legacy fallback removed, the whole sync suite stays green — the matcher re-derives the right answer from the candidates and hides the damage. That is a good property of the matcher and a bad reason to leave the key untested, because the day the key is wrong and the matcher is uncertain is the day a transaction goes missing.

A matcher that forgives your mistakes is lovely in production and treacherous in a test suite, which is why this test, and the one for a cancelled pre-authorisation further down, ask by which route a booking arrived rather than merely where it ended up.

Seven statuses, and only one of them was ever read

The same ING report had a second half: entries in the budget dated in the future. Looking into it turned up something considerably worse than the future.

Enable Banking reports every transaction with one of seven statuses, and BankingSync looked at exactly one of them. PDNG was an authorisation; anything else was, by default, a booking. That covered SCHD, scheduled and not yet executed - and it also covered CNCL and RJCT, cancelled and rejected. So a hotel pre-authorisation, once the hotel released it, didn’t merely get imported: it ran through the booking path, found its own authorisation waiting, and marked it cleared. Money that never left the account, written into the budget as spent, with the full confidence of a tool nobody had ever told that “cancelled” might mean something. That one stings.

The status is read now, and the whole of it fits into one switch statement:

func parseStatus(t map[string]any) string {
	switch status, _ := t["status"].(string); status {
	case "":
		return "BOOK"
	case "HOLD":
		return "PDNG"
	default:
		return status
	}
}

and one table:

OTHR stays a booking on purpose: a bank that cannot map its own status onto the standard would otherwise have its real bookings silently discarded, and of the two possible errors, that strikes me as the worse one. Whatever isn’t imported is also kept out of the opening balance, which would otherwise have subtracted money the account never actually lost.

One case remains awkward by necessity. When an authorisation has already been imported and the bank later reports it as cancelled, BankingSync cannot remove the row, for neither Actual nor Firefly lets it delete anything. The row now stays uncleared rather than being cleared, stops holding the fetch window open for a booking that will never arrive, and a sync.authorisation_cancelled warning names its date, amount and payee, so that you can delete it by hand. The single exception is a booking of the same identity in the very same feed - a pre-authorisation released and charged anew - which settles the row just as before.

What I have deliberately not done is second-guess a bank that delivers as BOOK what it hasn’t booked yet. A date-based rule was on the table for a while, and I dropped it: the status is the bank’s own statement about its own ledger, and any heuristic overruling it would trade one bank’s error for a rule that is wrong about every other bank. If your bank tells BankingSync that something has been booked, it is imported as booked, and the complaint, I’m afraid, belongs with your bank.

The reference that described a slightly different program

v4 closed with the promise that a test holds docs/telemetry.md against the source, and so it did - for names. Whether each metric actually carried the labels the document claimed was, in the test’s own words, left “to a reader”, and the reader, as it turns out, had not been doing a stellar job.

A second test now parses the source into a syntax tree and follows every metric from its declaration - through struct fields, gauge callbacks and attributes stashed away in variables - to every place it is recorded, and compares the labels it finds there against the document. On its first run, it found six metrics documented with the wrong labels: backend missing from two gauges, route and status missing from the Firefly request counters, a reason nobody had ever written down, and a result label on a histogram that has never had one. Every one of those is a query somebody would have written straight from the reference and then watched return nothing at all. While I was in there, the reference also turned out to claim that every web request was traced twice over; it isn’t, and that section now describes the program that actually exists.

bankingsync_transactions_skipped_total gained a reason label, too, since “skipped” had quietly come to mean six different things - a known reference, the transaction_id bridge, a known content key, an authorisation already imported or already settled, and a row the matcher found already in its final form - and which of these it was is the very first thing you want to know when a run imports nothing.

Upgrading

Pull, restart, carry on - nothing is migrated, and nothing is re-imported. A few things are worth knowing nevertheless:

• ING rows imported before this release keep their prefix. Notes are only ever filled in where they’re empty, so nothing you have edited gets overwritten - which cuts both ways, since nothing is cleaned retroactively either. Actual rules matching on the prefix or the raw payee will stop matching new rows.
• A cancelled authorisation already in your budget has to be deleted by hand; sync.authorisation_cancelled and bankingsync_authorisations_withdrawn_total will tell you which one.
• Dashboards will notice. transactions_skipped_total is split by reason now, and since re-delivered bookings no longer pass through the matcher, bankingsync_match_probability loses a fair share of its volume. Those decisions were never evidence of anything - a booking settling onto its own row agrees with itself by definition - but the graph will show a change, and a real one at that.
• Bookings imported before this release have no content key, and are matched the old way until they leave the fetch window.

To everyone who took the time to write these reports up, and then answered my follow-up questions about their bank’s API responses with a good deal more patience than such questions deserve: thank you. Every fix above started with you showing me something I could never have found out on my own, for the simple reason that I don’t bank where you do.

References

Fellegi, I. P., & Sunter, A. B. (1969). A Theory for Record Linkage. Journal of the American Statistical Association 64(328), 1183–1210. - why a held pair is a pair and not a record.

Enable Banking. Frequently Asked Questions - on transaction_id and entry_reference.

Enable Banking. API reference, TransactionStatus - the seven statuses, and the descriptions quoted in the table above.

Firefly III. ING Germany description quirk, issue #5403 - the same remittance format, met by another importer.

hledger. import command documentation - counting same-date records to tell them apart across imports.