Re-stitching the chain
2022-11-16 by Evrim Öztamur

Beware This article mainly concerns the implementation details of immutable ledgers and working with Xero, it’s not necessarily about the sales tax comparison report, which is why this is a separate article.

Re-stitching the chain

Individual journals carry a lot of metadata on their origins, such as the type of resource that they originate from (e.g., a sales invoice, a manual journal, a bank statement entry, etc.).

Analysis of this metadata can give further insights and allow you to infer a more complete overall history:

A sales invoice, for example, can have various parts of it changed in a way that does not actually get reflected in its journals. Changing the quantity of a unit and multiplying it by two, but reducing its price by half will net you the same line entry in your journal for Price × Quantity, instead of Price and Quantity separately.

Description Account Debit Credit
INV-0010 8000 – Revenue 100.00
INV-0010 1900 – VAT Payable 21.00
INV-0010 1200 – Accounts Receivable 121.00

For INV-0010 with a line item 20 × 5 is the same as with 10 × 10 in our case: both will credit revenue by 100.00.

API limitations

Not all this information is accessible in a programmable way through the API, and I’m not even sure if it’s stored at all on the Xero end. So you have to resort to having a numerically accurate (for majority of use cases) report, but with gradually decreasing level of information as you get more detailed in your ‘audit’ through history.

There are some minor issues that are outstanding complaints with Xero, and may get resolved (hopefully) by them, on their end. Certain resources such as fixed assets and their depreciation journals are loosely coupled. In fact, you cannot trace a depreciation journal back to its fixed asset source(s). The best you can do is compare the ledger accounts noted on the journal and assume that it’s a depreciation journal if it matches the ledger accounts listed under the separate fixed asset API.

There are several other kinds of journals that are hard to track, but fortunately it’s not too severe of an issue. Most ones, such as the singular journal for the opening balances of your administration (used when transferring your bookkeeping from one source to another to set the balance sheet in order), are identifiable by similar heuristics as with the fixed assets’.

Inferring blame

Majority of the work involves dealing with the structure of the underlying data (with varying levels of exposure to you as the end user, who only has API-exposed truth) in the end.

For example, all linked resources contain their own history endpoint which tells you what edits or notes were made by which users. You can trace these back to the corresponding journals by means of checking the timestamps of the journals against that specific resource’s. This is unfortunately a very expensive operation and must be done asynchronously upon user request. Each complete deduction of resource history versus its journal history may take several hundred valuable milliseconds and API rate-limit space!

Possibilities and a way through

These issues could be alleviated by having a complete access to the application database, but again, more often than not, this is not possible as a third-party integrator.

These sort of implementation differences vary wildly between tools: It’s by complete chance that I discovered Xero had an accessible immutable ledger exposed through its API.

All in all, for Xero, that is plenty enough to populate such a report, especially to a point where version control concepts become meaningful.

Status-quo reality

Unfortunately, not everybody keeps complete (i.e. an immutable ledger or a complete audit record, either is workable so as long as it is complete in giving an accurate representation of nominal adjustments) records when building accounting software, and it is presently not a legal requirement either. Many accounting software out there is probably not implementing immutable ledgers at all.1

Once again, I discuss these and some other implementation concerns over at another article.

  1. Tip Run away if your accountant is using Excel to do your bookkeeping. Excel definitely is going to lose your edit history unless you can guarantee that your files always remain on the Office 365 cloud. Same goes for Google Sheets, by the way. ↩︎