5 ms·

The real answer is that double entry is a different problem, and SQL/relational databases are not ergonomic for solving it. Most SQL ledgers are just a table o

The real answer is that double entry is a different problem, and SQL/relational databases are not ergonomic for solving it. Most SQL ledgers are just a table of transactions. What happens when you have an error/typo in a transaction? SQL databases will ensure that your error gets safely committed to disk, with zero built in machinery to detect what double entry knows to be an error in the data. (I don't know if OP actually solves that problem in their service, but this is the key idea in double entry.) Cheers.

zo1 6y ago
A string field typo shouldn't be causing your production system's ledger to become misaligned or get into an inconsistent state. To prevent that, you use some form of unique IDs and have constraints on your data model. Double-entry transactions should be done on top of a database transaction so that they're atomic.
- jonahbenton 6y ago
  The semantics required for double entry are essentially that multiple records are being added to different tables, and there are math rules that have to get applied to the values in specific fields across those tables (e.g. to ensure that the individual account transactions balance). It's not just an atomicity problem, there is a correctness issue specifically around the data. These rules are not expressible in what SQL databases consider to be field-level constraints. They can be expressed in triggers, but doing that is the path of pain.
  - zo1 6y ago
    Those are all application concerns which you should get right and no database out there can do that for you short of exotic ones that are purpose-built for this domain. You specifically said that "[SQL has] zero built in machinery to detect what double entry knows to be an error in the data" And I mentioned that's not the case because double-entry is solved by mostly two things: Zero or two transactions happen on separate accounts, not 1. And that the separate accounts happen on uniquely identified accounts (and not by string-identifiers that can have typos), which is solved by unique & foreign key constraints. The other side to that is that the sides of the double-entry have to be identical in amount. There are no "math rules" beyond that, but again this is an application concern. Relational databases are perfect for solving a ledger problem, and as long as you build a solid application layer above it to solve the domain problem, you won't have any issues. I understand there is a point that this thread is making that "block chain" and "NoSQL" DBs are somehow better for modelling a ledger, but that's inherently false and the same criticisms you apply to relational databases for this, you can just as easily (if not more) apply them on exotic database types.
emadda 6y ago
With double entry encoded in software, wouldn't the typo just get inserted into two places instead of just one?
- jonahbenton 6y ago
  The rules around how a single high level transaction gets divvied up into what ultimately need to be balanced accounts can be complex, with derived amounts being calculated, opportunities for rounding errors, etc.
VWWHFSfQ 6y ago
I've never done any double-entry accounting work before, but wouldn't it be sufficient to wrap the credit and debit operations in a single database transaction to ensure that either both are committed, or neither? maybe I'm misunderstanding what the problem is. Genuinely curious! I've always wondered how these kinds of things are implemented at scale.
- jonahbenton 6y ago
  Well, no- not only do the credit and debit (logical) financial transactions have to be committed in the same (physical) database transaction, but more importantly, the amounts have to balance. A SQL database doesn't have any opinion on those kinds of semantics. You can of course- and people do all the time- encode those kinds of "business rules" into application code, or into database code like triggers, but: 1) the abstraction level you are operating at in the double entry domain is around balanced accounts. Having a "ledger database" that provides that kind of abstraction to your application so that you are not solving for balancing transactions all over the place in application code is very important. Important for the same reason that using a SQL database saves you from having to worry about two phase commit and transaction logs and so forth in your application code when saving sensitive data. The guarantees a SQL database offers are lower level than what one needs in a ledger database. 2) to your point of "at scale"- at scale a double entry system often accepts some kinds of unbalanced transactions, and then has workflows to support various kinds of reconciliation. A lot of this reconciliation is time based- every bank to this day at least does reconciliation at the end of each business day with "close of business" batch processing jobs. Some can also do it intraday. At scale means a lot of additional actual "business rule" complexity. Hope that helps?
  - VWWHFSfQ 6y ago
    Yes that helps thank you!
andriosr 6y ago
Completely agree! I've seen SQL implementations of ledgers fail quite a few times. I'm using DynamoDB, but any no-SQL database will do the work (currently working on Cassandra and MongoDB connectors to enable self-hosting for enterprise customers) On the implementation side, it's a mix of both: I'm using the conditional insert/optimistic locking of no-SQL and modeled the ledger to be a hash-chain at the application layer. The design is actually simple, and it scales indefinitely. Besides that, I'm using DynamoDB streams to build roll-ups of the transactions and make sure that all transactions are always balancing to zero in real-time.
- jonahbenton 6y ago
  Cool, got it. Are you actually seeing demand for on-prem hosting? Serious demand such that you would lose the sale from a customer you cannot live without? Completely different support model and neither Cassandra and especially Mongo are anywhere near Dynamo. I also have seen plenty of cases where balance to zero only at end of day is feasible, but real time is a good way of picking the right customers. My completely unsolicited advice- do the same with the DB, stay away from customers who say they need prem. But I'm just a schmo on the internet. Very best of luck to you!
  - andriosr 6y ago
    A couple of companies asked for it, but I couldn't agree more with you. We are in the design phase of these implementations, as there is a lot more than code to consider for a self-hosted version. Thanks for the advice, it's great to get your view on it.
    - ethn 6y ago
      DynamoDB is the wrong technology for this. You can't prevent phantom reads. Transactional databases (precisely invented for this!) are the way to go. To quote DDIA: 'A service that allows users to spend money or points needs to check that a user doesn’t spend more than they have. You might implement this by inserting a ten‐ tative spending item into a user’s account, listing all the items in the account, and checking that the sum is positive [44]. With write skew, it could happen that two spending items are inserted concurrently that together cause the balance to go negative, but that neither transaction notices the other.'

zo1 6y ago

A string field typo shouldn't be causing your production system's ledger to become misaligned or get into an inconsistent state. To prevent that, you use some form of unique IDs and have constraints on your data model. Double-entry transactions should be done on top of a database transaction so that they're atomic.

jonahbenton 6y ago
The semantics required for double entry are essentially that multiple records are being added to different tables, and there are math rules that have to get applied to the values in specific fields across those tables (e.g. to ensure that the individual account transactions balance). It's not just an atomicity problem, there is a correctness issue specifically around the data. These rules are not expressible in what SQL databases consider to be field-level constraints. They can be expressed in triggers, but doing that is the path of pain.
- zo1 6y ago
  Those are all application concerns which you should get right and no database out there can do that for you short of exotic ones that are purpose-built for this domain. You specifically said that "[SQL has] zero built in machinery to detect what double entry knows to be an error in the data" And I mentioned that's not the case because double-entry is solved by mostly two things: Zero or two transactions happen on separate accounts, not 1. And that the separate accounts happen on uniquely identified accounts (and not by string-identifiers that can have typos), which is solved by unique & foreign key constraints. The other side to that is that the sides of the double-entry have to be identical in amount. There are no "math rules" beyond that, but again this is an application concern. Relational databases are perfect for solving a ledger problem, and as long as you build a solid application layer above it to solve the domain problem, you won't have any issues. I understand there is a point that this thread is making that "block chain" and "NoSQL" DBs are somehow better for modelling a ledger, but that's inherently false and the same criticisms you apply to relational databases for this, you can just as easily (if not more) apply them on exotic database types.

emadda 6y ago

With double entry encoded in software, wouldn't the typo just get inserted into two places instead of just one?

jonahbenton 6y ago
The rules around how a single high level transaction gets divvied up into what ultimately need to be balanced accounts can be complex, with derived amounts being calculated, opportunities for rounding errors, etc.

VWWHFSfQ 6y ago

I've never done any double-entry accounting work before, but wouldn't it be sufficient to wrap the credit and debit operations in a single database transaction to ensure that either both are committed, or neither? maybe I'm misunderstanding what the problem is. Genuinely curious! I've always wondered how these kinds of things are implemented at scale.

jonahbenton 6y ago
Well, no- not only do the credit and debit (logical) financial transactions have to be committed in the same (physical) database transaction, but more importantly, the amounts have to balance. A SQL database doesn't have any opinion on those kinds of semantics. You can of course- and people do all the time- encode those kinds of "business rules" into application code, or into database code like triggers, but: 1) the abstraction level you are operating at in the double entry domain is around balanced accounts. Having a "ledger database" that provides that kind of abstraction to your application so that you are not solving for balancing transactions all over the place in application code is very important. Important for the same reason that using a SQL database saves you from having to worry about two phase commit and transaction logs and so forth in your application code when saving sensitive data. The guarantees a SQL database offers are lower level than what one needs in a ledger database. 2) to your point of "at scale"- at scale a double entry system often accepts some kinds of unbalanced transactions, and then has workflows to support various kinds of reconciliation. A lot of this reconciliation is time based- every bank to this day at least does reconciliation at the end of each business day with "close of business" batch processing jobs. Some can also do it intraday. At scale means a lot of additional actual "business rule" complexity. Hope that helps?
- VWWHFSfQ 6y ago
  Yes that helps thank you!

andriosr 6y ago

Completely agree! I've seen SQL implementations of ledgers fail quite a few times. I'm using DynamoDB, but any no-SQL database will do the work (currently working on Cassandra and MongoDB connectors to enable self-hosting for enterprise customers) On the implementation side, it's a mix of both: I'm using the conditional insert/optimistic locking of no-SQL and modeled the ledger to be a hash-chain at the application layer. The design is actually simple, and it scales indefinitely. Besides that, I'm using DynamoDB streams to build roll-ups of the transactions and make sure that all transactions are always balancing to zero in real-time.

jonahbenton 6y ago
Cool, got it. Are you actually seeing demand for on-prem hosting? Serious demand such that you would lose the sale from a customer you cannot live without? Completely different support model and neither Cassandra and especially Mongo are anywhere near Dynamo. I also have seen plenty of cases where balance to zero only at end of day is feasible, but real time is a good way of picking the right customers. My completely unsolicited advice- do the same with the DB, stay away from customers who say they need prem. But I'm just a schmo on the internet. Very best of luck to you!
- andriosr 6y ago
  A couple of companies asked for it, but I couldn't agree more with you. We are in the design phase of these implementations, as there is a lot more than code to consider for a self-hosted version. Thanks for the advice, it's great to get your view on it.
  - ethn 6y ago
    DynamoDB is the wrong technology for this. You can't prevent phantom reads. Transactional databases (precisely invented for this!) are the way to go. To quote DDIA: 'A service that allows users to spend money or points needs to check that a user doesn’t spend more than they have. You might implement this by inserting a ten‐ tative spending item into a user’s account, listing all the items in the account, and checking that the sum is positive [44]. With write skew, it could happen that two spending items are inserted concurrently that together cause the balance to go negative, but that neither transaction notices the other.'