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
- on database folder, create a .env.local file with
- 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