I used to worship at the altar of plain text, and indeed text is ideal in some situations. However I have found that text falls apart when I need to programmatically manipulate data and write it to a file. It’s hard to formulate structured “plain text” formats that are human readable, human writeable, machine readable, and machine writable. Yes it’s easy enough with simple data (and Ledger is relatively simple.) with richer and more complex data this gets very hard.

After years of using Ledger and trying to adapt it and my own systems to store richer financial data and manipulate it programmatically, I have migrated to my own solution based in SQLite rather than plain text. I realized that what I have is a data storage and manipulation problem, which is exactly the problem SQL databases solve very well.

Text works well for some things but it doesn’t deserve all the worship it gets in geek circles. Part of this was my own fault because I tried to make text work in situations where it wasn’t suited, but hopefully others will read my comment and not make the same mistake solely because they read some blog posts and other things from old-school UNIX heads saying “text is great!” Don’t try to make text do everything.

As a concrete example of what you're talking about, all of these plain-text accounting solutions are difficult/impossible to sort the transactions in because their plain-text-ness gives so much freedom.

Then, because transactions aren't sorted, it becomes easier to load up some kind of UI (CLI or GUI) to browse things by-date or by-account or whatever. To say nothing of charting & reporting...

And at that point...the usefulness of plain-text-ness seems quite diminished. It becomes more like a data-entry format than anything else.

That said, I continue to use a plain-text accounting system because the alternatives aren't really any better at this point and often come with their own issues.

Structured plain text sounds kind of self-contradictory. Obviously I don't know what you worked on but could the issues you ran into maybe have been easier to work with if the data were stored in multiple files and folders instead of trying to add structure to plain text?

Sure that’s possible but not any easier. Anything can be stored in “plain text” just as it’s possible to drive nails with a screwdriver. That doesn’t make it the right tool.

I'd be interested to understand the rough details of your system. I have played around with ledger and beancount, but like you I find myself wanting a more programmatic access to the data. Maybe what we need is a sqliteaccounting.org site to explain the pros/cons!

There are two main tables: one that can hold any transaction, and another that's specifically for transactions I download from bank and credit card statements. It holds different data such as the unique identifier that banks assign in the OFX data (this helps avoid duplicate transactions.) Difficulty in dealing with data like that is a big reason I started to move away from plain text.

There are other tables to hold what I call "categories" (accountants would call these "accounts") and "accounts" (these correspond to the more colloquial usage) and commodities and other items like the balances from bank statements.

I considered both SQLite and Postgres. Postgres has nice things like true ALTER TABLE support but SQLite's ease of administration and simple API swamps any advantage that Postgres would offer for this.

My user interface is in Haskell, but one could use any language for that - I just like Haskell. It's all CLI based. I import the overwhelming volume of transactions from bank downloads, but when I need to enter something manually I currently just write a little Haskell script to do it (eventually I might add a CLI program to do this.)

> It’s hard to formulate structured “plain text” formats that are human readable, human writeable, machine readable, and machine writable.

XML? Or is that too far from plain text?

It’s hard to do this in XML because parsers nearly always strip out comments and often whitespace too. Thus writing days to the file strips out important data that I expect to be able to add if the file were truly human writable.

XML is not human readable or writeable.

You are not wrong, but the argument is a text file could hold the link to the audio file and any metadata. I am a right tool for the job person so I use plain text for personal stuff but when it comes to work, there are so many more requirements at play that plain text is rarely the best answer other for, say, exchanging regular tabular data with third parties.

Without the right framework to manage sidecar files, this system rots quickly. Tools like DVC are good but there are still rough edges.

The issue partly stems from the lack of coupling between data and metadata on file systems. I think the next evolution of file systems involves the ability to link files together in an organic way. But that is tricky since it breaks the abstraction of files as stream of bytes. Might need to go up a level of abstraction and create new primitives for cp, mv, etc.