Comment by eadz | Hacker News Reader

Comment by eadz | Hacker News Reader

I'm not convinced by the seperate DB/API.

Advantages for using Postgres ( assuming a double entry schema[1] ) and you're using Postgres for your main app db;

  - You can do financial transactions inside business db transactions, and roll back both atomically

  - Adding up numbers is one of the things computers are best at, and Postgres can easily handle a huge amount of financial transactions

  - Re-use PaaS Postgres hosting/scaling/clustering/backups

  - Easier integration with the rest of your app with foreign keys to relevant records relating to the financial transaction

  - Easy integration with BI tools given Postgres is well connectable

[1] Roughy `account(id, cached_balance)`, `transaction_lines(src_account, dst_account, amount)`

This gem does literally billions of dollars worth of financial accounting for various companies at scale: https://github.com/envato/double_entry

It's dated, the API is a bit messy and needs work, as it was initially written 10+ years ago, but for a web based app I would choose a v2 of it over a non-postgres ( assuming you are using Postgres for your app ) solution.

I blogged about this recently - here's a DB schema that can do all of the double entry stuff with about 2 tables. I also have example queries for balance sheets and other useful bits:

https://blog.journalize.io/posts/an-elegant-db-schema-for-do...

I hadn't seen that gem before! My comment on most accounting tools is that they ignore the concept of "normal" account balances, which means they don't really think about "debits" and "credits". This is fine mathematically, of course, but makes it awfully difficult for accountants to understand how the software is supposed to behave!

We're running on mid-sized Postgres right now, but find that clickhouse (or duckdb) are something like 100x faster once we do GROUP BYs on 5M+ rows.

That's a good blog post, thanks for sharing.

Incidentally, I just came across Journalize. But I'm struggling to understand what it's for. It might be helpful to say - either here, or on your homepage - who or what it is competing with. I'm probably not the exact target audience, but it seems to do more or less what Xero does for us (but I don't want to rule out that I just don't understand the homepage).

Appreciate the feedback!

If your business generates a lot of transactions, to the point that you have a full time accounting or engineering staff whose job it is to wrangle 200MB excel files or complicated sql queries, then Journalize is for you.

We’re basically BI tailor made to create outputs that are relevant to accountants (we know what “depreciation” is supposed to look like) and let them take a monthly ledger entry and drill down to a specific user’s impact on the GL and tie it to the line of source data.

In this way, we’re doing the data engineering work, helping accounting teams have BI type tools to set up accounting rules, and giving transparency to “why” an aggregate number is correct.

I think they key part is "distributed financial accounting database". For anything that didn't need to be distributed to handle massive volume of transactions I'd also pick Postgres, but I can imagine there's a limit where that wouldn't work, and as soon as you need to do this in a distributed database things get exciting.

No experience with the linked gem but the description states

> While this gem acts like a double-entry bookkeeping system, as it creates two entries in the database for each transfer, it does not enforce accounting rules.

And that seems like a far cry from things like "rejecting transfers if the accounts involved would exceed their net debit or credit balances".

>> While this gem acts like a double-entry bookkeeping system, as it creates two entries in the database for each transfer, it does not enforce accounting rules.

> And that seems like a far cry from things like "rejecting transfers if the accounts involved would exceed their net debit or credit balances".

The READEME needs to be updated[1] for clarity. It doesn't enforce "accounting" rules, however The DoubleEntry gem has rules to optionally ensure a balance cannot go negative, and it also has an allowlist of allowed transfers ( which are defined by source account, dest account and code ).

[1] https://github.com/envato/double_entry/pull/216/files

> I think they key part is "distributed financial accounting database". For anything that didn't need to be distributed to handle massive volume of transactions I'd also pick Postgres, but I can imagine there's a limit where that wouldn't work, and as soon as you need to do this in a distributed database things get exciting.

That is why I qualified my statement with "you're using Postgres for your main app db".

For every financial transaction you'd likely have many more business logic level transactions, and you would want your business transactions to be as consistent as your financial transactions. No point storing a financial transfer if you can't match it up with a purchase on your business db.

Reply on news.ycombinator.com