> It’s impossible to split a single payment into multiple payouts, since there is a many-to-one relationship of payments to payouts.
So your model used a many-to-one relationship when you really wanted a many-to-many?
> Since this is just a SQL database, there’s nothing preventing the payouts from becoming inconsistent.
Preventing inconsistencies by enforcing constraints is a key point of an RDBMS. This is literally why people use SQL DBs.
> The payout_id can be ensured to be a valid foreign key, but nothing is stopping it from being nulled out.
You mean, like a NOT NULL constraint? Or, if you mean zeroed out, an appropriate constraint and/or before update trigger protecting the amount?
> So your model used a many-to-one relationship when you really wanted a many-to-many?
In many schema discussions you’d be right, but in the case of payments systems it’s not the case. Payments and payouts are separate transactions and only loosely related. The allocation of credit from one leads to an increment in the settlement of the other, but there’s no structural relationship in the journal itself.
> Preventing inconsistencies by enforcing constraints is a key point of an RDBMS. This is literally why people use SQL DBs
The inconsistencies in question relate to the domain model of accounting, not referential integrity or other (normally) easily enforced database invariants.
> You mean, like a NOT NULL constraint? Or, if you mean zeroed out, an appropriate constraint and/or before update trigger protecting the amount?
When you want to archive off aged records, or constrain across db shards, this is a serious issue. Taking the piss doesn’t make it go away. But in that particular paragraph I believe they’re referring to the payout itself become void e.g. due to a reversal, and having designed around similar issues I understand their concern. It’s exactly where single-column ledgers become a huge pain in the audit.
Constraints can be more than referential integrity. When PostgreSQL released transition tables, I built a very small proof-of-concept using them to constrain a double-entry (-like) system. https://github.com/jimktrains/pgdea allows any number of debits and credits in a transaction, but the whole transaction must have a 0 balance.
> When you want to archive off aged records, or constrain across db shards, this is a serious issue. Taking the piss doesn’t make it go away. But in that particular paragraph I believe they’re referring to the payout itself become void e.g. due to a reversal, and having designed around similar issues I understand their concern. It’s exactly where single-column ledgers become a huge pain in the audit.
My impression from the article was more the "baby record" problem: not everything has been paid yet. They were using NULL to indicate "not paid" and thus couldn't put a NOT NULL constraint on that column because it was a valid value for new rows. There are ugly workarounds that could have allowed them to add NOT NULL such as using a special/magic "guard" payment that indicates not paid instead. (Going down that route you'd probably want a "guard" payment per client and then you're getting close to a backwards form of double-entry accounting.)
> In fact, for the ease of modeling we relaxed how we reason about it and we don’t stick to standard Double Entry Accounting which has debit-normal and credit normal books which determine the sign as we prefer to consistently treating debits as positive and credits and negative.
Surely some of their customers have competent accountants maintaining the business's books. This unasked-for effort will make their job harder.
> Surely some of their customers have competent accountants maintaining the business's books. This unasked-for effort will make their job harder.
Uh, why? It's trivial to present information tracked as positive debits and negative credits in two positive columns; keeping the internal architecture of the database this way should be irrelevant to the end user, it's just avoiding using a hack designed to optimize the experience of people doing a manual process with medieval European technology and attitudes towards negative numbers in an automated backend where none of the problems it mitigates exist in the first place.
You just end up with an apparently negative balance in the income account that represents your own cut due to fees etc. From the GL point of view it looks like a subledger with a contra account but that’s nothing to faze a competent accountant. Back in the 13th century it was probably tricky to represent on an abacus, not so much now.
>Preventing inconsistencies by enforcing constraints is a key point of an RDBMS. This is literally why people use SQL DBs.
This comment is not specific or related to the work at hand, but note that this is only true for a single-machine database or a sharded setup in which you never have to perform transactions across shards. That is, the ACID guarantees are only enforceable in most RDBMS if all your data is on the same machine.
From the Cockroach documentation (source: https://www.cockroachlabs.com/docs/stable/transactions.html )
> CockroachDB supports bundling multiple SQL statements into a single all-or-nothing transaction. Each transaction guarantees ACID semantics spanning arbitrary tables and rows, even when data is distributed.
Hindsight and all that, and every team has inhouse stuff that could probably be gotten rid of, but this seems like a major operational danger compared to relying on more battle-tested stuff. They should probably get the Jepsen guy on this and see if he can get race conditions
And not just for overpriced knives.
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.
To this day, I have yet to find another resource on multi-currency that is as solid as this one:
https://www.mathstat.dal.ca/~selinger/accounting/tutorial.ht...
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
So, approach this space with a healthy dose of skepticism until its been appropriately vetted and certified by third party auditors.
[1] https://docs.google.com/document/d/1RaondTJCS_IUPBHFNdT8oqFK...
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.
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.
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.
I'm a little surprised they didn't use double entry accounting from the beginning.
It provides models with Postgres constraints to ensure the double entry accounting rules are not broken.
It isn’t the same scale as Books, but I imagine that won’t matter for a lot of use cases.
> To address consistency, we picked a well-established, public-domain, battle-tested approach to modeling financials that enables all of our properties ...
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.
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.
That would generate deadlocks in RDBMSs, curious to know if Spanner is able to scale it.
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.
Similarly a quibble about the "immutable" in the headline given that they are still using a mutable field at the book level for balances to make them easier to query. I guess I was hoping they'd have solved a nicer merkle tree balancer or something for balances. Not that I was hoping for a "blockchain" solution, but something smarter than blockchain for "centralized" trust environments like Square.
Good luck with your application -- I, for one, think there's a need for it. I've wished for years that someone would put something like a secure, web-based Gnucash out into the world.
Can still make a case for licensing, though, depending on the features.
We built this for our internal accounting, it's a ledger service that enforced immutable double entry rules and can run as a container with a Postgres DB configured. https://github.com/realimage/qledger
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>`.
[1] https://www.waveapps.com/ [2] https://play.google.com/store/apps/details?id=com.waveaccoun...
Basically, it’s just a list of transactions you can only append to. You can’t delete any of the entries or modify them - they really are immutable.
Another plus is that you won’t run into an issue like race conditions where two threads are attempting to change the same thing.
What happens if you've transferred too much from your savings account into your current/cheque account? You make another transaction to adjust the initial one.
...at last ;)
Reading the page, that's exactly it; journal entries are an append-only log.
Man, don't fuck up.
https://www.accountingtools.com/articles/correcting-entry-de...
