Introducing Beanpost: A PostgreSQL-Based Experiment for Beancount Users

[Ben Gerdemann]

Hi Everyone,

I hope this email finds you well. I've been using Beancount for many years. It's been an amazing tool, and I'm grateful to this community and especially to Martin Blais for maintaining such a fantastic project.

I'm sharing a project I've been working on called Beanpost, which you can find on GitHub at https://github.com/gerdemb/beanpost. This started as an experiment to see if I could recreate plain text accounting functionality using a PostgreSQL database, and it turned out more successful than I expected. I thought the Beancount community might find it useful and interesting.

Beanpost consists of a PostgreSQL schema and import/export commands that let you transfer data between a beancount file and a PostgreSQL database. Much of Beancount's functionality is implemented using custom PostgreSQL functions, allowing for complex queries and data manipulation. This setup provides a flexible backend that can integrate with other tools like web apps or reporting systems.

One of the reasons I created Beanpost was to build a mobile-friendly custom front-end. Fava is a fantastic tool, but it's not very mobile-friendly, isn't intuitive for beginners, and is hard to customize. With Beanpost, you have more flexibility to create a custom front-end that suits your needs.

The included functionality is as follows:

• Functions to calculate account balances or changes over a specific period.
• Support for calculations on individual accounts or account hierarchies (e.g., "Assets").
• Check balance assertions.
• Verify that transactions balance.
• Support for explicit precisions for each currency to use for balance tolerances.
• Display the running balance of an account by posting.
• Convert between currencies, including converting baskets of currencies.
• Store documents as byte-data in the database.

I'm curious to hear your thoughts on this approach. Would love to hear any feedback. I think I've implemented the majority of the required features, but maybe I'm missing a few things?

Maybe we could call this "database-based accounting" instead of "plain text accounting." 🙂

Thank you for your time, and I look forward to hearing from you.

Best regards,

Ben

[Martin Blais]

Thanks for sharing, Ben,

Broadly speaking this approach will work if you don't have to match cost basis and if you have only a single currency.

Note that export to SQL has existed for a while, but I think to be able to most generally replace the functionality from Beancount one would need a custom aggregator implementation (to accumulate positions with a variety of commodities and track individual lots, i.e., the Inventory class).

--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/143e6263-3db3-4532-ae0e-eac6d7e6ffebn%40googlegroups.com.

[Ben Gerdemann]

Hi Martin,

Thanks for your feedback, and thanks for maintaining Beancount. Your work has made it such a great tool for so many of us.

I wanted to clarify that Beanpost does support multiple currencies using a custom PostgreSQL type and custom aggregator functions. I apologize for not making that clearer in my initial message. For example, the query SELECT sum(amount) FROM posting will return a summed inventory of all the currencies for the postings, allowing you to work with different commodities.

Regarding cost-basis, I haven't personally needed it yet, so that's why it's not implemented. However, I do import the cost and price information for each posting, which would be the basis for the calculation. I would need to review the documentation about how to calculate cost-basis accurately, but my basic idea is to write custom PostgreSQL functions to perform different cost-basis calculations. I'll also need to think about how to represent the matching of lots in SQL. Perhaps using foreign keys to link back to the matching lots could be a potential solution.

Thanks again for your guidance and insights.

Best regards,

Ben

[b...@gerdemann.org]

Hi Martin,

Thanks for your reply and for creating such a valuable tool in Beancount. I wrote a response yesterday, but I haven't seen it posted to the mailing list yet, so apologies if there's some repetition in this message.

Beanpost does support multiple currencies using a custom PostgreSQL type and custom aggregator functions. For example, the query SELECT sum(amount) FROM posting; will return a summed balance (a basket of currencies) for the postings, allowing you to work with different commodities and calculate balances across various accounts. I'm sorry I didn't make that clearer in my earlier message.

Regarding cost basis, I went ahead and implemented a basic version. I added functions for matching augmentations and reductions in an inventory, equivalent to the "STRICT" booking method. There's also a function for calculating an average cost basis. The query to retrieve these results looks like this:

SELECT
inventory(posting.*) AS booking_method_none,
cost_basis(posting.*) AS booking_method_strict,
cost_basis_avg(posting.*) AS booking_method_average
FROM
posting;

There's no validation yet, so lots can go negative, and currencies between augmentation and reduction don't have to match. I think these checks should be fairly straightforward to add later. If you'd like to explore the implementation, you can find it in the new branch of my repository: https://github.com/gerdemb/beanpost/tree/inventory. I'm also considering how to implement FIFO and LIFO cost-basis methods.

Thank you again for your interest and for your valuable feedback. I'm looking forward to hearing what you think.

Best regards,
Ben

> You received this message because you are subscribed to a topic in the Google Groups "Beancount" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/beancount/t_99aXRbG78/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to beancount+...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhO1JLNPorhm3qweWGcHX%2BdyAJ-kjxLMbT10rh96oKgzag%40mail.gmail.com.

[Martin Blais]

Oh wow, that's crazy cool. I didn't know postgresql supported that!

Will have a more detailed look, sorry for not digging in deeper

--

[b...@gerdemann.org]

On Apr 24, 2024, at 8:19 AM, Martin Blais <bl...@furius.ca> wrote:

Oh wow, that's crazy cool. I didn't know postgresql supported that!

Will have a more detailed look, sorry for not digging in deeper

Martin, 

Thanks for your interest. This started as a simple experimental side project, but I was able to take the implementation much further than I expected, and I thought it was cool enough to share with the Beancount community. To be honest, I'm not really sure what I want to do with it next, but I thought it would be interesting to get some feedback from the community to validate the idea. The current implementation covers my personal use cases, but I know that Beancount is used in many different ways, and I wonder if there is any other core functionality that might be missing.

The foundation of the idea is defining a custom type `amount` in PostgreSQL that combines a numeric (decimal) number with a commodity code. Then we can write custom functions to add and aggregate amounts to create balances of currencies, aggregate postings into lots for cost-basis calculations, and convert between different currencies.

I would say that currently, the biggest missing features I’m aware of revolve around validation. Although there are checks that transactions are balanced and that balance assertions are correct, there are some minor validation points and edge cases that are not yet addressed. I've made a list of the ones I can think of in the README, but there are probably more.

Another major missing feature is statement import. Since the data is just stored in a PostgreSQL database, it should be straightforward to write an importer—or convert an existing Beancount importer—to INSERT data directly into the database. However, I would consider this out of the scope of this project.

For my personal project, I am using Postgraphile to automatically expose a GraphQL API of the database and am writing a custom React-based front-end as a client to the data. For now, I’m still importing the Beancount data into the database and then exporting the changes back to a Beancount file, as Beancount still provides functionality that I need. But eventually, I could imagine moving everything to the database.

By the way, I just finished adding LIFO/FIFO cost-basis functions. Check the README for more details.

https://github.com/gerdemb/beanpost

Best Regards,

Ben

[b...@gerdemann.org]

Chary,

Thanks for your interest and good questions!

On Apr 24, 2024, at 3:22 PM, Chary Chary <char...@gmail.com> wrote:

Question 1: What can you do with beanpost, what you can't do with beanquery?

I think there are two key differences between beanpost and beanquery: the SQL ecosystem and integration with other systems.

At a basic level, the query syntax for beanquery is similar to PostgreSQL as used in beanpost. However, as Martin points out in the "Motivation" section of the beanquery documentation (https://beancount.github.io/docs/beancount_query_language.html), writing pure SQL queries for Beancount data can be challenging, which is why he created beanquery to offer a "SQL-like query client." The beanquery client has extra features that simplify query writing compared to pure SQL. I have already implemented some of these features in beanpost, but working with pure SQL may never be as straightforward as using a specialized query language like beanquery.

However, beanquery is an "SQL-like" language, while beanpost uses PostgreSQL. This means we have the full power of a real database with standard SQL and aren't limited to the features provided by beanquery. In theory, any query or data manipulation is possible. For example, I wrote a function in PostgreSQL to calculate an average cost basis—a feature not provided by Beancount (as far as I know; someone correct me if I'm wrong), which would probably be impossible to implement with beanquery.

Additionally, PostgreSQL is an industry-standard tool and can be used as a back-end for various clients, such as custom web apps, reporting tools, and more. These kinds of integrations aren't really feasible with beanquery.

Question 2: The documentation says:

• Transaction dates: Each posting can have its own date, allowing transactions to balance even if individual postings have different dates. This helps with common issues when transferring money between accounts where withdrawal and deposit dates differ.

How do you achieve this, if initially the database is created by importing beancount file, which has only one date per transaction?

On import into the database, we assign the beancount transaction date to each posting. On export to a beancount file, since beancount doesn't support dated postings, we use the date of the first posting for the transaction date.

I hope this answers your questions. If you have more queries or need further clarification, I'm happy to help.

Best regards,

Ben

[Chary Chary]

On Wednesday, April 24, 2024 at 11:24:23 PM UTC+2 Ben Gerdemann wrote:

Question 2: The documentation says:

• Transaction dates: Each posting can have its own date, allowing transactions to balance even if individual postings have different dates. This helps with common issues when transferring money between accounts where withdrawal and deposit dates differ.

How do you achieve this, if initially the database is created by importing beancount file, which has only one date per transaction?

On import into the database, we assign the beancount transaction date to each posting. On export to a beancount file, since beancount doesn't support dated postings, we use the date of the first posting for the transaction date.

I hope this answers your questions. If you have more queries or need further clarification, I'm happy to help.

Best regards,

Ben

Thanks for the answer. but I am not sure I understand the workflow

1) You create a beancount file normal way

2) You import beancount file into the beanpost database. 

3)Then you do some manipulations on the entries inside the beanpost  which create the situation, equivalent of the beancount having different dates on postings, which belong to the same transaction.

Is this correct?

Or do you you create new entries, which are not coming from beancount?

 

[b...@gerdemann.org]

Yes, your understanding of the workflow is correct. The database schema used by Beanpost allows each posting in a transaction to have its own date. No new entries are created—this flexibility works fine within the Beanpost database. However, when exporting the database back to a Beancount file, which does not support individual dates for postings, we arbitrarily use the date of the first posting as the transaction date.

In general, I tried to keep the Beanpost data schema as close as possible to the Beancount format. However, when I wrote queries for the custom functions, I found it more natural to assign dates to postings rather than transactions, as it reduces the number of SQL joins needed. This approach also had the added benefit of addressing a long-standing feature request in Beancount to allow postings to have their own dates.

One of the advantages of using a database over a plain text file is that schema changes like this are relatively simple. In contrast, adding this feature to Beancount itself would require modifying the Beancount file syntax, which could be more complicated and involve careful consideration of backward compatibility, among other things.

Best regards,
Ben

[Brian Lalor]

There’s a plugin that adds processing for an effective_date field. Maybe part of Reds’ suite? I use this extensively. Maybe worth exploring and adding explicit support for? 

-- 
Brian Lalor (he/him)
bla...@bravo5.org

[b...@gerdemann.org]

> On Apr 26, 2024, at 1:53 PM, Brian Lalor <bjl...@bravo5.org> wrote:
>
> There’s a plugin that adds processing for an effective_date field. Maybe part of Reds’ suite? I use this extensively. Maybe worth exploring and adding explicit support for?

Hi Brian,

Thanks for sharing this! I wasn't aware of this plugin. Are you referring to this one (https://github.com/redstreet/beancount_reds_plugins/tree/main/beancount_reds_plugins/effective_date#readme)? Beanpost already supports importing and exporting metadata, so it wouldn’t be hard to implement this syntax.

However, unlike Beancount, Beanpost doesn't require transactions to occur at a specific point in time. This can lead to a situation where money "disappears" from your assets while "in transit" between accounts. While this is somewhat closer to reality, it might not always be what you want. In this case, you could create manual holding transactions, like in the example.

Beanpost doesn't have "plugins” yet to do things like this automatically, but it's something to consider for the future.

To be honest, I didn't think much about allowing postings to have their own dates when I implemented it. This change could be undone if it causes more confusion or problems than it solves, but I have seen this feature requested for Beancount a few times.

Best regards,
Ben

[Brian Lalor]

Yes, that’s the one! I might be a bit overly pedantic about setting the effective date, but it helps reconciling transactions with my bank. 

-- 
Brian Lalor (he/him)
bla...@bravo5.org

On Apr 26, 2024, at 10:58 PM, b...@gerdemann.org wrote:



--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/5BAF897D-5F22-40FE-B58D-E15D9D404BD5%40gerdemann.org.

[Martin Blais]

It's not pedantic, it's totally needed at some point

It would be nice to be able to generate accurate statements from both sides of these transactions.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/3A1E6703-508D-4051-87F9-1A51A44AF438%40bravo5.org.

[Chao Chen]

Hi Ben. It's so cool to do accounting in a real database! So I am
eager to learn more about your way of using beancount and beanpost.

1. I haven't tried beanpost yet (I'll definitely do!), but my first
thought is that editing beancount files is much easier than updating
the postgresql entries. Do you use UI tools (like fava, or some magic
tools from the SQL world) to assist you do that?
2. Do you have dedicated pipelines to sync between beancount files and
the postgresql db?
3. One powerful feature of beancount is the explicit error checking
(for example account open/close, transaction balance, and balance at
date). Does beanpost also do these?
4. Beanpost's README mentions "it can easily be used as a back-end for
other services like web apps, reporting tools". Is it convenient to
share an example?

Thanks!

Regards,
Chao

> To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhMSJ1x8wXwKUJkgMq%2B_ZmcLL_qAbSFL8ep9-ZUthn1Ctg%40mail.gmail.com.