Granola

What's this?

This is an app to manually track expenses. General idea is:

• Create categories
• Store expenses
• List monthly expenses
• Search for expenses
• Generate reports and charts about monthly/yearly expenses

There is a little more information on this article.

What it's not

This app will not automatically integrate with your bank and retrieve your expenses.

How it was build

• Database in PostgreSQL
• Next.js for frontend and backend.
  • Material UI: starting point was this example.
  • Charts powered by Google Charts and this library.

Where is it hosted?

It's not. You can run in locally or host it anywhere you like, though. If you want to do it for free, I suggest Vercel and ElephantSQL.

How can I run it locally?

If you have Node.js and docker installed, you just need to:

• git clone https://github.com/jeromevonk/granola.git
• choose a path for docker to store the volume used by the PostgreSQL database and set it on the docker-compose.yml file
• start the containers with cd granola/database; docker-compose up
• if you want to use sample user (user = 'sample', password = '123456'), categories and expenses
  • on database folder, create a .env.local file with PG_CONNECTION_STRING=postgres://postgres:my_postgresql_password@localhost:5432/postgres
  • run ./migrate_and_seed.sh
• go to backend-frontend folder and create a .env.local file with variables PG_CONNECTION_STRING=postgres://postgres:my_postgresql_password@localhost:5432/postgres and JWT_SECRET='your-own-secret'
• run npm install
• run npm run dev

Comments

Authentication: idea was derived from Jason Watmore's blog post: Next.js 11 - User Registration and Login Tutorial with Example

Details

Backend

Database

My goal was to create a lightweight solution so it could be hosted for free (ElephantSQL for instance offers free plan with 20MB of data).

Therefore, I decided not to include some columns (such as time_created) to save space.

One interesting thing to keep in mind about space in PostgreSQL is the so-called "Column tetris" - the order or your columns might impact disk usage!

Category table

The main categories and sub-categories can be represented in the same table with the Adjavency list model, described in this very nice article.

Keep in mind that:

• deleting one parent category will delete child categories as well
• deleting a category will delete all expenses related to this category

API routes

For easier understanding, import this file into Postman.

• POST /api/users/authenticate
• POST /api/users/register
• DELETE /api/users
• GET /api/categories
• POST /api/categories
• PATCH /api/categories/:id
• DELETE /api/categories/:id
• GET /api/expenses
• GET /api/expenses/:year/:month
• GET /api/expenses/years
• POST /api/expenses
• POST /api/expenses/recurring
• PUT /api/expenses/:id
• DELETE /api/expenses
• DELETE /api/expenses/:id
• GET /api/stats/year-evolution
• GET /api/stats/month-evolution
• GET /api/stats/category-report