[MortyWaves]

What does your schema look like? Either out of paranoia or naivety I feel that this is actually not such a simple task.

The variety of formats and tools out there plus things like “double entry accounting”, makes me think that the database design would also need to be thought about deeply.

[aswinmohanme]

We use a proper ledger to track the flow of money. It was sizable technical investment to get the schema right, but it has made the subsequent implementations of features easier.

[MortyWaves]

Yeah but what does a “proper ledger schema” look like??

[aswinmohanme]

My bad. The design is based on https://fragment.dev/docs#design-your-ledger. They have detailed docs about designing proper ledgers.

[MortyWaves]

Thanks!

[apatheticonion]

The schema is pretty straightforward to be honest. I have one table that covers transactions:

   id, date, date_settled, currency, bank, account, account_type, reference, credit, debit, account_balance, total_balance

Dates are in ISO UTC, I have several bank accounts across multiple countries so it's helpful to track currency and bank. Credit cards are just an account.

Anything additional that I might experiment with (like post processing transactions and assigning tags) I do in additional tables that relate back to the original transaction's PK.

I have additional tables tracking my adventures trading stocks/forex and another one for assets.

I wanted it to be serverless because of cost/convenience so I host the DB using CloudFlare's D1 database.

I have a CloudFlare worker that takes a POST request accepting raw SQL, executes the query and returns the result as JSON. Basically a crappy firebase implementation where the client does the work. Obviously this is not safe for a production application, but I'm the only user so it's fine.

I wrote a little web frontend to help with data entry and complete tasks like parse bank statements - which, at least in Australia and New Zealand, are distributed as PDF files. The banking sector is in the stone ages here.

Here is my statement parser: https://github.com/alshdavid/commbank-statement-converter

I've experimented with using AWS Lambda + DynamoDB as the back end. I have also tried Lambda + S3 Athena but nothing has been as simple to set up as Cloudflare D1. I may migrate to Athena at some point, but I am lazy and what I have works.

To represent the data/generate reports & summaries, I use Google Sheets with custom AppScripts that make http requests to the data source (CF worker) with various SQL queries.

AppScripts are a bit slow and I'd like to create a custom front end for this but I am lazy and Google Sheets works.

I have my statements sent to my email so I have been thinking about setting up an AWS SES + lambda to receive and automatically parse & add them. I wrote web scrapers for the bank accounts that I could log into programmatically - but there are no "webhook" facilities on transaction events so syncing is manual.

I like some of the ideas in OP's app - like calculating cash flow and incorporating assets into the net worth calculation - so I might add those calculations.

I'd open source my system, add authentication and all that but I doubt anyone would use it.