> "We hope to publish guides about more complex use cases (lending, insurance, etc.) in the future."
That would be appreciated. It'd be nice to see the minimal complexity needed for something like a local credit union that managed customer savings and checking accounts, as well as home and car loans.
More ambitious would be a central banking app, in particular how does double-entry accounting work when a central bank (i.e. the Fed) is doing 'quantitative easing' and using their helicopter money to buy up Treasury bonds and mortgage-backed securities in order to keep major banks and government solvent? What's the private bank's balance sheet look like when they use the central bank money for stock buy-backs instead of for increasing commercial lending?
For example, the Fed says it's unloading the mortgage-backed securities it bought up in 2008-2009. That's got to be some convoluted accounting:
That's a cool idea. Any other deep-dive guides on fintech/payments that would be helpful for readers here? We're always looking for new topics to write about.
Do you mean for card payments, like the card network and authorization flow? I found this article really good from the systems design side although you might have to be a subscriber: https://newsletter.pragmaticengineer.com/p/payments-system
Klepmann is correct but practically you don't control external accounts thus cannot authoritatively determine if they either exist, have ceased to exit, or the contents of their ledgers. Thus, a large number of transactions will always have hanging references. This ultimately dictates the need for a "settlement state", which should be modeled as a state machine with careful transitions. Reversible transactions, fees, taxes and discounts then come in to play, some of which may be shared between parties, some of which are not calculable before the fact.
Fowler's approach is amusing in that, in classic UML style, he models things which are optional in an authoritative way as if they are requirements, thus muddying the waters even further. While his adjustment implementations are interesting as a basis for feature comparison, there's a lot to be said for simplicity, and this effectively requires throwing out what the bean-counters are used to and reconsidering the need from scratch. The default correction is another transaction, and this requires no special implementation.
New systems recommendation:
(1) For account identification, use IIBAN which provides IBAN-compatible account identification and checksums and is an open system @ https://github.com/globalcitizen/iiban
(2) For all accounting, use UTC.
(3) For transaction identification, use UTC second of origination (UTCSO) + account of interest (AOI; eg. IIBAN) + intra-second transaction identifier (ISTI).
> Klepmann is correct but practically you don't control external accounts thus cannot authoritatively determine if they either exist, have ceased to exit, or the contents of their ledgers.
True. But it doesn't actually matter.
> Thus, a large number of transactions will always have hanging references.
No, it doesn't need to be any dangling references. Because you model external accounts with an internal account (node) in your ledger.
you model external accounts with an internal account (node) in your ledger.
... achieving what, exactly? How is the set of outbound transactions already stored associated with a given external destination not precisely the same information? This is needless data duplication, AKA database design no-no 101.
Achieving an internal record that balances out, and has no dangling references. Later inter-company audits can also verify that neither company has unilaterally corrupted their books. To be clear: I’m just talking about the standard accounting practise here (as normally done with tables/books), nothing fancy. Just applied to graphs, so the graph has no dangling references/edges.
In the database world it is generally recognized that doing data duplication to 'flatten' a particular view is a special case of normalization (adapting data to a model) which is encouraged to be done dynamically (eg. as an SQL view) and not in stored data (as this would create redundant information, which negatively affects the database in terms of ongoing management, authority and clarity). Generating reports in whatever format users prefer is generally a very cheap and fast operation.
Also, duplicating the data does not make it more correct or more authoritative. Do not confuse presentation with data. A rose by any other name would smell as sweet.
Edit: I can't reply to your response so will reply here. I suggest reading some basic database design books. Good luck.
Thank you. Those are some interesting references. I do like your reference of state machines at the edges of the accounting model. Definitely the case in payments systems. I don't like how Modern Ledger goes straight to credits and debits whereas Klepmann has a graduated approach. I have been thinking about writing a bit more about this.
We live in a global era. Any novel system should assume that it may in future be operated cross-border, distributed, etc. or used in combination with such systems. Many countries even have multiple timezones internally. Given such a circumstance, any use of local timezones is by definition a presentation layer concern. Not recognizing this at design time is a surefire way to create needless technical debt with zero functional benefit.
As a CPA and software developer, I've been wondering if I should build my own product out, but there seems to be a number of options available in the ledger space. However, I'm surprised there isn't more of an overlap in software engineering and accounting. There's a bit of overlap in the fields that scratch the same itch. Although the theory behind accounting is a lot more interesting than most of the work in my experience.
I'm also a CPA and develop software now (there are dozens of us!). I think part of this is that the perception among devs is that accounting is too complicated, and therefore can be radically simplified in software, which can be true, but largely doesn't meet real world scenarios. The problem is that accounting is generally complicated because business is complicated. I've seen software that throws away the ability to add more than 2 line items to a transaction. I've also seen software that goes too far the other way and automates every step of a process (okay great, how can we show the auditors now that this works?). It's a pretty classic misunderstanding of the domain, where most CPAs don't speak software, so there's a severe lack of communication (which probably makes CPAs who do understand software very valuable!).
The other factor is that while accounting software as a whole sucks, but at least for larger businesses, its probably better to tell the auditors that you use quickbooks, versus some custom in house software. From a risk management perspective, that's a much easier value proposition for the board/management.
> The problem is that accounting is generally complicated because business is complicated.
This is it. From a high level, accounting lends itself very well to software. You have accounts, money, and transactions, and it appears that the challenge is moving money between accounts and then adding everything up. Perfect domain for software, that's all stuff computers are very good at.
It's only once you start digging in with real companies that you realize the math is the easy part. The hard parts are the opposite of things that computers are good at - e.g. "given this change in rules that takes effect next year and is written in plain English, how do we account for this transaction?" Or "when an exception occurs, we need to define a process for how a human can handle it after month close."
The parts that are fun for a developer (look at the cool stuff the computer can do!) are not the parts that are valuable to a user; the valuable parts are super tedious and boring.
That's great insight. When I was at big 4, I helped look over in house software and translate it for auditors to gain reasonable assurance that the software was correctly implementing accounting processes.
A buddy of mine is working on a YC-funded company (https://www.keeper.app/) that's designed around assisting the lives of bookkeepers using quickbooks. I think it's a great angle because I agree with you that quickbooks is the most logical option for an average company. Where software could really be improved in my eyes is SAP. Boy does their software make me want to hurl... But they have such a strong hold in the industry.
This is a bit tangential, but my biggest insight when conducting walkthroughs with the client's accountants was that there is so much valuable knowledge that is internalized in singular individuals. I'd have an accountant show me their month end close process with links between 5 Excel worksheets. Totally illogical flow and only that person understood how to follow the process from start to finish. There would be situations like randomly multiplying a line item by 32 because of some piece of paper on their desk that they had written down years ago. These people had been at the company for 20+ years. I have to think there's a better system for handling accounting processes like that. Weeding through a messy code base can be a nightmare, but going through someone else's accounting worksheets that are crucial for tying out the financial statements can be nearly impossible. I suppose eventually the audit team has to decide what's material, make a judgement call, and move on.
Hey I'm co-founder of Proper Finance https://www.properfinance.io we're building integrated ledgering and reconciliation software for fintech companies. Happy to be helpful where we can - most of the team were early engineers at Marqeta where we worked on the core ledger, as well as reconciliation and financial controls - so happy to provide perspective. shoot me a note - travis@properfinance.io
I’ve often thought that had I been born into a world without computers I’d have been an accountant.
On high school career day I attended a talk by an accountant, and unfortunately he lived up to the stereotype. One of the most (superficially, anyway) boring people I’ve ever met.
I’m actually the founder is a startup trying to bridge the gap between Eng and finance. I’d love to hear your perspective on what a good solution would look like!
would love to connect as we're evaluating ledgers for rental properties. there are a gazillion management softwares with "accounting" ledgers but they were always an after thought. we're interested in building the defacto single source of truth ledger for real estate. taylor at apmhelp.com
It's running on NextJS which pre-loads the content of each link in the viewport unless you tell it not to, so it's loading a .json file containing the article content immediately, making the page transition instant when and if you click it.
I'm on the growth team at Modern Treasury. Using our Ledgers and Payments products together could definitely help here. Ledgers is actually free to get started. The startup plan includes 5000 transactions a month and is free forever and the enterprise plan has volume-based pricing. We'd love to chat more about your use case and pricing. Feel free to drop a note - pranav@moderntreasury.com.
Don't want to hijack the thread here but we're building an open-source platform that includes ledgers and a scripting DSL for money movements at Formance, with payments connectors in beta and coming up for recon - always keen to chat
Mentioned this above but I'm a co-founder of Proper Finance https://www.properfinance.io where are building integrated ledgering and reconciliation software for fintech companies. We are happy to help where we can - the team were early engineers at Marqeta where we built the ledger, as well as reconciliation and financial controls. Shoot me a note - travis@properfinance.io
The heart of double entry accounting is extremely simple. Forget about asset/liability/expense. Money always flows from one account to another. What goes out from account_1 must go into another account(s).
Typical tables:
I use -ve amount for credit, +ve for debit. That way when you do SUM(trascation_line.amount) it would come to 0.
This also cleanly maps to database transaction too where all transaction_line rows and transaction row should be atomic.
If you want multi-currency support, instead of amount column, it needs to be currency_code, currency_rate, amount_in_currency, amount_in_basecurrency ( i know we don't need this all, but sometimes you want to record the amount as entered, eg EURO 52.10 u want to record as entered even if your base currency is USD)
To build a chart of accounts, you can have a parent column in accounts table.
Account balances is just:
SELECT account.name, SUM(amount) balance
FROM account ac
INNER JOIN transaction_line tl ON tl.account_id = ac.id
GROUP BY account.name
You can cache this balance values with a current_balance column on accounts table
Once you have that, for any real world transaction, all you need to figure out is what are the accounts to debit/credit, ie classification. That is a higher level thing and is the business logic of an accounting application.
This looks nice, but it doesn't enforce in the schema that all transaction lines sum to zero. Is that a problem in practice? Or is it one of those things where if you get it wrong, you tend to notice immediately because everything breaks (as opposed to silently creating or destroying currency that goes unnoticed for a long time)?
I guess you could have a stored procedure that checks for balanced transactions before inserting (or before committing, at least), with SECURITY DEFINER, and not give anybody else permissions to insert or modify the transaction lines table.
But yes, that is a downside, and if I were to write such a thing I'd make sure to have at least two mechanisms to avoid / detect errors (like, one validation in business logic and/or stored procedure, plus regular monitoring for transactions that don't add up to zero).
Its not a problem in practice. There might be a way to enforce that through database constraints, but in practice, checking transaction lines sums to 0 in business logic is not that hard. Having said that, you can run accounting entry sanity checks on the entire database. As previously said
SELECT SUM(amount) /* this should sum to zero */
FROM transaction_line
---
Also to identify any non-balancing transaction is easy:
SELECT tx.id, tx.date, SUM(amount) tx_sum
FROM transaction tx
INNER JOIN transaction_line txl ON txl.transaction_id = tx.id
GROUP BY tx.id
HAVING tx_sum != 0
---
This will identify them even if caused by your business logic bug, database bug, disk corruption etc.
This can also be done on the single accounting transaction just after insert too and can be done within the same database transaction.
This sort of schema requires a process to verify the sum before committing, and verification is annoying to achieve with constraints. Instead, you might consider something like:
I think you could solve this with a `sale_id` for grouping transactions together, no?
The benefit of row-level guarantees on balancing is considerable, even if it somewhat complicates the application layer (the layer that translates your first ledger into your second ledger, and vice versa).
If you're storing a transaction that needs to balance across N rows, the only solution is at the "application level" (i.e. a stored procedure, or some middleware in code), which is unfortunately more difficult to reason about.
There exists cases where it is problematic, but your example is fine. In this case, you would use individual transaction lines to represent payments, rather than sales, which is closer to reality. But you can group transactions in any way you want.
The above looks like $70 was taken from cash and deposited to bank. Thats not whats happening in the real world. Since cash is fungible, one could ignore that. But if it was cheque + bank-transfer or something else that leaves a record, then that wont work. Or when you include VAT/GST/Sales tax. Or when you pay a loan monthly payment of $1000 that needs to be split to principal and interest. etc.
Yes, it is best if transactions correspond to verifiable events. Complicated situations can be modelled by letting events be represented by their own "accounts", in a separate charter.
In your example, you might let the monthly payment be represented by its own account, with three transactions (bank payment, interest, principal).
The purpose is to make the database constraints enforce double entry verification. If you don't need this, it can be made simpler as you suggest.
Distinguishing between sales and payments is required, but any confusion is basically impossible in online systems such as this schema would be used in, in real examples.
Typical accounting GUIs present transactions and ledgers such that it becomes intuitive for business users to enter sales/invoices. Entering the above transaction in those GUIs might cause confusion.
The schema here is used by an online system recording business events as a type of log of user interactions. It lives in a sql database, and thus needs to rely on row constraints to guarantee double-entry. Transactions are never entered manually or randomly.
The effect of split transactions can be achieved by using an intermediate account. Those accounts have balances which are not guaranteed to be zero, but that is unavoidable and sometimes OK and intended.
One sale is always one sale, even if it has multiple transactions associated with it[1]. It must never look like multiple sales.
[1] Lots of use-cases: People paying partly by credit card, partly in cash. People using vouchers which are recorded as a negative transaction. Group of people splitting a bill and each doing a CC transaction for their bit. None of these are multiple sales, because recording each transaction as a single sale wreaks havoc on the inventory control.
No. In the real world it is a single sale. In the accounting/finance domain it is single sale, so it should be represented as such. It also matters when you look at KPI like avg order size, num orders for customer, not to mention it is one invoice at the time of invoice creation, but payment collected in multiple methods.
-ve i used to denote credit, not necessarily to indicate increase/decrease. In accounting terms, if you sales increase, your entries on the credit side of sales account increase. In my implementation it means larger negative number. Hope that explains.
I would like to know about this as well. I struggled with this for a while for a prediction market app that I'm building. Eventually I ended up with [1]. I am somewhat pleased with it, but it does feel unwieldy to work with. I have some vague hope that somebody who actually implemented banking software would know of an obvious and elegant schema.
The summary of my approach is:
* A table with accounts.
* A table with account balances and an index on (account id, id), so you can efficiently query the current balance.
* A table with transactions.
* A table with mutations. Mutations have an amount, and reference a credit account, debit account, and transaction. (So one transaction can consist of multiple mutations.)
* The account balances table list the post-balance, but also references the mutation that caused it to be that new value.
All of these tables are append-only. I later added another layer, with transactions and subtransactions, but I'm not sure if this was a good idea.
If I understood your explanation and schema correctly, a mutation itself is balanced, and if you have a transaction that involves three accounts, that would be split up into two balanced mutations, right?
The advantage I see with this design is that a mutation (and thus a transaction) is always balanced (you store the amount only once, and credit account and a debit account).
The disadvantages seem to be that the transaction itself doesn't explicitly list the total changes to an account explicitly, and that for each account you have to join the mutations twice (once for the credit side, once for the debit side) to get to re-calculate the current amount.
Storing both the current balance in the account means you cannot have concurrent updates to one account, so you must rely on row-level locking for consistency. (Which sounds a bit like a potential bottleneck, if you have something like a company-wide Cash account that is involved in lots of transactions, as in the ModernTreasury blog post).
Does that seem like a fair summary to you? Are there other trade-offs you have noticed?
> ... if you have a transaction that involves three accounts, that would be split up into two balanced mutations, right?
Three or more "mutations", but these might be grouped together in whatever way you want. For this purpose, the meaning of "account" is up to you define. You might call them "accounting objects" representing subscriptions, contracts, invoices, and so on.
Account balances (per transaction) can only be calculated sequentially in the order of transactions, which becomes a bottle neck at some rate of transactions.
Performance is not something I'm worried about for my app, maybe a few dozen people would use it at the same time, and I run everything at serializable isolation level anyway. But I can imagine that for processing real-world payment volumes, at some point you need to sacrifice the balanced-by-construction property for performance.
One issue I noticed is that there is some freedom in the representation of transfers. You can pick a canonical representation by demanding that the amount is positive, but then you have to make a case distinction everywhere in code. Often the code becomes much simpler if negative amounts are allowed. But it does make the credit/debit more confusing, and it goes against the observation in part 1 of the series, that accounting systems rarely work with negative numbers. I wonder why though.
The experimental directory of `beancount` has some code for converting beancount data structures into SQL structures [1].
You could also take a look at the data structures used by GNU Ledger (e.g. account [2]) and transcribe them to SQL. A bit more work, but probably educational.
Wish there was similar guide explaining the basics of cooperate accounting for developers. As a dev that has to occasionally work on integrating web apps with our erp system, I still get lost when the erp guys or biz people talk about various accounting processes.
Is chart of accounts usually a flat structure, or can it look more like a tree? I'm thinking of grouping similiar accounts together to see what's the balance on single ones vs the group.
As a CPA and software developer, I've been debating whether to create my own solution or explore the possibilities already present in the ledger area. However, I'm surprised that accounting and software engineering don't have more in common. The issue is that because business is complicated, accounting is often complicated as well. Software that disables the ability to add more than two line items to a transaction has been seen.
I did like Charles Dickens who has Mr Micawber say ‘Annual income 20 pounds, annual expenditure 19 [pounds] 19 [shillings] and six [pence], result happiness. Annual income 20 pounds, annual expenditure 20 pounds ought and six, result misery.’
I'll take advantage of the simplistic calculation. If you give me all your money, I'll give you back all your money plus N (N guaranteed to be a positive number). Now, I'll give it back in 10 years, but you'll have definitely made money. Or, in your terms "hells yeah"!
