Ensuring the Consistency of Accounting Information in Moneybird

Moneybird allows you as an entrepreneur to focus on sending invoices to your customers. No need to occupy yourself with accounting, as Moneybird will take care of this by itself. How do we make sure your books are always in order?

Accounting 101 #

The record of all things that are happening in your Moneybird account is called the journal. The journal contains transactions that consist of two or more entries. These transactions move money between ledger accounts. A ledger account can be anything from an actual real-world bank account to a virtual account like Accounts Receivable.

When moving money between ledger accounts in a transaction, the sum of all entries must be zero. If it is not, money is lost during the transaction.

Example #

1. Suppose you send an invoice to one of your customers. This invoice will have to be paid by the customer, so Moneybird moves the total amount of the invoice to Accounts Receivable.
2. Your customer pays the full amount by bank transfer. The total amount of the invoice will be subtracted from Accounts Receivable and added to your bank account.

The Journal in Moneybird #

In Moneybird the journal is represented as a view in our database (PostgreSQL). The view collects information from a lot of different places in your account and converts them to journal entries. This makes it very easy to check that everything is in balance and to generate reports.

Unfortunately, querying this view takes a long time because of its complexity. Therefore we use a materialized view that is updated when needed.

Materialized Views in PostgreSQL #

Support for materialized views is not that great in PostgreSQL. Only a basic materialized view (opent in nieuw tabblad) is available. It is generated completely from scratch on every update and the view is unavailable for reading while it is being updated.

We implemented our own solution that updates just the journal entries for entities that have been changed and keeps the view available for reading during updates.

-- Delete old journal entries DELETE FROM journal_entries WHERE document_type='Invoice' AND document_id=1234; -- Insert new journal entries from other views INSERT INTO journal_entries SELECT * FROM je_invoices_to_accounts_receivable WHERE document_type='Invoice' AND document_id=1234; INSERT INTO journal_entries SELECT * FROM je_invoices_to_taxes WHERE document_type='Invoice' AND document_id=1234; INSERT INTO journal_entries SELECT * FROM je_details_to_ledger_accounts WHERE document_type='Invoice' AND document_id=1234;

The je_X_to_Y views that provide the input for our materialized journal_entries view are more specialized views that extract journal entry information from certain entities in a Moneybird account.

Concurrent Updates #

This may not be very complicated, but it gets interesting when multiple threads are trying to update journal entries for the same entity at the same time. This can happen, because multiple users can access the same Moneybird account concurrently.

Additionally, external events can automatically update entities in a Moneybird account. For example, when a customer pays your invoice on-line via PayPal or iDEAL (opent in nieuw tabblad). Or when you are using our API (opent in nieuw tabblad).

Example #

1. A user registers a partial payment for an invoice in Moneybird. The invoice is saved and old journal entries are deleted. New journal entries are starting to be inserted.
2. At the same time, another user registers another partial payment for the same invoice. Journal entries are updated.
3. The journal entry update for the first partial payment finishes, selecting part of the information from the new situation created in step 2. Our materialized view is now in an inconsistent state.

Transaction Isolation #

Fortunately PostgreSQL allows us to isolate transactions from changes that are happening concurrently. It provides a REPEATABLE READ transaction isolation level (opent in nieuw tabblad) that ensures all statements in a transaction see the same snapshot of the database.

Furthermore, when committing the transaction the database checks if another transaction has been committing changes to the same rows we where trying to update. In that case, our current transaction will be aborted automatically.

This solves our problem of inconsistent view states, but introduces a new problem: how can we be sure that the view always contains the most recent state if some transactions are aborted?

Delayed Update #

This is a problem that is quite hard to solve for real-time updates. Therefore we decided to split the problem into two different problems and solutions:

1. First, we want to make sure that no user action will lead to unbalanced journal entries. We check this immediately using entity-specific database constraints, for example when registering a payment.
2. Second, we want to make sure that accounting reports are always in a consistent state and are eventually at the most recent state. For each journal entry we record the time it was last updated and compare this periodically to the last updates of invoice, payments, etcetera. Should outdated journal entries be found, we update them to the latest version.

Conclusions #

Caching complicated views that are accessed concurrently can be a challenge. Using PostgreSQL’s transaction isolation and by delaying certain updates we ensure that they are kept in a consistent state.