Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Accounting for Developers, Part II

moderntreasury.com

Post reply on HN
Print view View original

41–50 of 79 posts

Re: Accounting for Developers, Part II

#41

Post by [deleted] »

meekaaku wrote: post #37
yobbo wrote: post #32

Earlier quoted context omitted.

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: transaction_line (id, transaction_id[fk], dr_account_id[fk], cr_account_id[fk], amount)

The problem with this will be when you have 3 accounts involved on a transaction. Eg, you take a sales receipt with part bank transfer, part cash. Sales Cr $100 Cash Dr $30 Bank Dr $70 Your approach will have: Sales Cr $70, Bank Dr $70 Sales Cr $30, Cash $30 That looks like two sales, which is not really the case.

[deleted]

Re: Accounting for Developers, Part II

#42

Post by meekaaku »

yobbo wrote: post #38
meekaaku wrote: post #37

Earlier quoted context omitted.

The problem with this will be when you have 3 accounts involved on a transaction. Eg, you take a sales receipt with part bank transfer, part cash. Sales Cr $100 Cash Dr $30 Bank Dr $70 Your approach will have: Sales Cr $70, Bank Dr $70 Sales Cr $30, Cash $30 That looks like two sales, which is not really the case.

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. With sales tax, you would have: cr: sales, dr: cash, $100 cr: cash, dr: tax, $10

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.

Re: Accounting for Developers, Part II

#44

Post by NIL8 »

pwpw wrote: post #26
Naga wrote: post #18

Earlier quoted context omitted.

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 t…

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 qui…

Could some of these issues you mentioned be helped by having the ability to make notes in each accounting stage of a particular customer's file?

Re: Accounting for Developers, Part II

#45

Post by perlgeek »

ruuda wrote: post #30
perlgeek wrote: post #12

Does anybody know a good SQL / DDL schema for a double entry accounting system?

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…

Thanks!

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?

Re: Accounting for Developers, Part II

#46

Post by perlgeek »

ruuda wrote: post #31
meekaaku wrote: post #16

Earlier quoted context omitted.

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: accounts (id, name) transaction (id, date) /* some call it journal */ transaction_line (id, transaction_id[fk], account_id[fk], amount) I use -ve amount for credit, +ve for debit. That way when you…

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).

Re: Accounting for Developers, Part II

#47

Post by yobbo »

meekaaku wrote: post #42
yobbo wrote: post #38

Earlier quoted context omitted.

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. With sales tax, you would have: cr: sales, dr: cash, $100 cr: cash, dr: tax, $10

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.

Re: Accounting for Developers, Part II

#48

Post by rudasn »

nirav72 wrote: post #29

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.

Don't waste this opportunity to ask people around you and learn more stuff.

Personally I think that's the main reason for working on any project, let alone being your full time job.

Re: Accounting for Developers, Part II

#49

Post by yobbo »

perlgeek wrote: post #45
ruuda wrote: post #30

Earlier quoted context omitted.

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…

Thanks! 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…

> ... 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.

Re: Accounting for Developers, Part II

#50

Post by contingencies »

3pm wrote: post #33

A quote from a related blog post: "Eventually I figured it out: basic accounting is just graph theory. Accounts = Nodes, Transactions = Edges" https://martin.kleppmann.com/2011/03/07/accounting-for-compu... Also probably worth checking out Martin Fowler's writing on accounting. https://martinfowler.com/apsupp/accounting.pdf https://www.amazon.com/Analysis-Patterns-Reusable-Object-pap...

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).

Free thoughts on forward-looking accounting systems @ https://raw.githubusercontent.com/globalcitizen/ifex-protoco...

Post reply on HN