A complete double-entry accounting system in PostgreSQL

Hacker News

Share card

A complete double-entry accounting system in PostgreSQL

I wanted to share an interesting experiment I've been working on: creating a double-entry accounting system entirely in a PostgreSQL database. It's inspired by plain-text accounting, especially Beancount, a fantastic project for plain-text double-entry accounting. I've implemented most core accounting features as PostgreSQL functions, including multi-currency support, account balance calculations, and cost-basis calculations for lot matching, average cost, FIFO, or LIFO. It turns out it's easier to create a "database-based" accounting system than you'd think! The core idea is to use a custom PostgreSQL type called amount, which combines a numeric value with a currency and then write custom aggregation functions to sum amounts into balances (baskets of currencies) which can be used in other custom functions. I also created two Python scripts: one to import Beancount files into the database and another to export the database back to a Beancount file. I've been using this system, called Beanpost, with my personal Beancount file, which has about four years of data and over 10,000 transactions across multiple currencies. I started Beanpost for fun and to create a backend for a custom mobile-friendly web app using Beancount data. While a database isn't as immediately usable as a text file, the possibilities for integrating with other tools are broader. I'm not sure where to take this next, but it's been a fun experiment, and I hope some of you find it intriguing. I'd love to hear any feedback or suggestions! Here's the link if you'd like to check it out: https://github.com/gerdemb/beanpost