This looks good but some of the background has the air of the kind of artificial problems presented in late night infomercials:<p>> It’s impossible to split a single payment into multiple payouts, since there is a many-to-one relationship of payments to payouts.<p>So your model used a many-to-one relationship when you really wanted a many-to-many?<p>> Since this is just a SQL database, there’s nothing preventing the payouts from becoming inconsistent.<p>Preventing inconsistencies by enforcing constraints is a key point of an RDBMS. This is literally why people use SQL DBs.<p>> The payout_id can be ensured to be a valid foreign key, but nothing is stopping it from being nulled out.<p>You mean, like a NOT NULL constraint? Or, if you mean <i>zeroed</i> out, an appropriate constraint and/or before update trigger protecting the amount?
Nice article, I'd be curious to know if/how their engine abstracts the complexity behind multi-currency transactions, or whether they rely on the <i>accounting</i> model to handle multi-currency.<p>We built our own double-entry accounting engine at my previous company, and while the engine was not as fancy as what Square describes, the real challenge was building out the accounting models that manipulated the engine's primitives.<p>To this day, I have yet to find another resource on multi-currency that is as solid as this one:<p><a href="https://www.mathstat.dal.ca/~selinger/accounting/tutorial.html" rel="nofollow">https://www.mathstat.dal.ca/~selinger/accounting/tutorial.ht...</a>
I’ve been involved with several two-sided marketplaces and payment services, and a robust double-entry book-keeping subsystem has always been there from the start. It’s easy to validate the need but/so I also find it hard to believe that Square only started keeping 2E transaction books in the last year. It’s the basis of many high quality controls and their internal audits and reconciliation to GL must’ve been a horrorshow without it.<p>It’s also the original event-sourced conflict-free replicated data type and the joke about CRDT for your debits is a classic thigh-slapper in the exciting world of accounting software backend implementation
I found it a lot of fun writing a double entry system, but the fun stops when implementation is finished and the "real work" begins. Next comes a costly and risky endeavor of defining the rules governing accounting events transacted by the AIS. This is where real difficulty and liability lies. An event triggers a series of transactions that must be financially sound and sufficient for meeting the expectations of an audit. Competent, qualified people <i>must</i> define these rules. These rules aren't universal truths. Accounting rules aren't black and white. This lead me to imagine that it is difficult, if not impossible, to define universally-applicable accounting events across all kinds of business. With that given, Square <i>might</i> be able to achieve these rules exclusively for the mom and pop retail customers whose types of business transactions are very similar, but the truth of this can only be revealed by third party auditors.<p>So, approach this space with a healthy dose of skepticism until its been appropriately vetted and certified by third party auditors.
For personal use, Beancount[1] looks like a good double-entry bookkeeping tool.<p>[1] <a href="https://docs.google.com/document/d/1RaondTJCS_IUPBHFNdT8oqFKJjVJDsfsn6JEjBG04eA/edit" rel="nofollow">https://docs.google.com/document/d/1RaondTJCS_IUPBHFNdT8oqFK...</a>
Ask the same programmer to write an invoicing & payments system, then coupon creation and track its claims, then something where users can recharge their phone, and their usage is tracked and balance is computed -- and each one of them will be a set of ad-hoc tables and custom code.<p>But most transactional systems where things move from one entity to another and has the notion of "balance value" is best represented by the double-entry system.<p>When a customer purchases pre-paid balance, the phone company can record it as a "credit", and every call they make becomes a "debit". This is an immutable log - one of the earliest application of immutable data structures in human history.<p>In this post authors used positive and negative values to represent debit and credit - we could call it yin and yang for all it mattered. The core principle is just that every transaction has a source and destination and the ledger is an append-only table.
In case it is of use to anyone on this thread, I’ve developed ‘django-hordak’, the core of double entry accounting system based on Django.<p>It provides models with Postgres constraints to ensure the double entry accounting rules are not broken.<p>It isn’t the same scale as Books, but I imagine that won’t matter for a lot of use cases.<p><a href="https://github.com/adamcharnock/django-hordak" rel="nofollow">https://github.com/adamcharnock/django-hordak</a>
Shouldn't an accounting database service (specifically, the ledger) be immutable and double-entry in any case?<p>> To address consistency, we picked a well-established, public-domain, battle-tested approach to modeling financials that enables all of our properties ...<p>I think doing anything other than double-entry leads to reinventing the wheel. When a ledger system is implemented correctly, the main remaining issue becomes scalability.
Excellent work and a great write-up! I’ve been implementing double blind accounting systems the past decade or so and what they teach in accounting classes is spot on there just isn’t much need for more complexity. There is the added benefit of staying PCI compliant and reconciling things the right way. Doing splits like this is hard work but I gather there are lots of powerful tools out there to help nowadays. Having worked with NACHA and Wells Fargo getting those ACHs done in one fell swoop is extremely innovative actually the system doesn’t lend itself to this very well.
Great write up, but I don’t see how it could scale with the pessimistic lock of Spanner.<p>Updating the current balance of big merchants, which are receiving multiple payments per second seems to create a lot of lost transactions, with the locking of the balance.<p>That would generate deadlocks in RDBMSs, curious to know if Spanner is able to scale it.<p>I built a similar system using DynamoDB and optimistic locking, and for that I had to remove concurrency of updates in the same document, meaning you can get ˜30 updates of the same document per second, with each update taking ˜30 milliseconds.
When making a double entry accounting database, the users should only have INSERT and SELECT privileges to the table, so in order to correct a mistake you make another insert.
There is really no scaling problem as the data is immutable. And at the end of the year you can achieve the ledger - so you don't have to map over it to check the account status - just put the sum of the last year as the first insert. A database service would still be useful though, in order to make sure you do not edit the values.
I actually submitted a YC application for the upcoming batch to work, more-or-less, on exactly this idea - a double entry accounting service for developers.
I don't see how this schema enforces double-entry accounting. You can specify an entry with a debit, and another with a credit, but they are not linked, their amounts are not necessarily equal and you're not required to simultaneously submit both entries. So this is really a single-entry accounting system that they happen to use to do double-entry accounting. Or am I missing anything?<p>For those of you reading this who wish to implement a simple system for double-entry accounting, do it like this instead: specify `debit` and `credit` columns with a foreign key to a particular account (what Square calls a "book"), and a field for the `amount`. When, later, you want to calculate the balance of an account, take the sum of all transaction `amount` where `debit = <account>` and subtract the sum of all transaction `amount` where `credit = <account>`.
I've been using Wave [1] for personal use. It hooks onto all accounts (via Plaid), and it also had a pretty sweet OCR receipts app [2] that posts the transaction to your Wave account.<p>[1] <a href="https://www.waveapps.com/" rel="nofollow">https://www.waveapps.com/</a>
[2] <a href="https://play.google.com/store/apps/details?id=com.waveaccounting.receipts" rel="nofollow">https://play.google.com/store/apps/details?id=com.waveaccoun...</a>