In this article, we run the Granola app locally and perhaps get you interested in expanding it.
Introduction
In this article, we will run the Granola app locally and perhaps get you interested in expanding it.
Find the source code at https://github.com/jeromevonk/granola
What Does It Do?
The purpose of the app is to control your expenses manually. There are the use cases:
- Create users
- Users create their own categories and sub-categories
- Store expenses
- Analyze the expenses:
- List monthly expenses
- Search for expenses
- Generate reports and charts about monthly/yearly expenses
Concepts
Categories
Users must create categories before creating expenses. Categories must have at least one sub-category. Every expense created will be related to a sub-category. Main categories exist for visualization purposes.
Expense
An expense object takes the following form:
{
"year": 2022,
"month": 12,
"day": null,
"description": "Test",
"details": "via Postman",
"amountPaid": 222,
"amountReimbursed": 22,
"category": 13,
"recurring": true
}
year
(YYYY) and month
(1-12) must be integers. day
can be an integer (1-31) or can be null
. This means that an expense happened in a particular month, but doesn't have a specific day. description
and details
(if provided) must be string
s. details
is optional and can be null
. amountPaid
must be a number greater than 0
, representing the value paid. amountReimbursed
must be a number, but can be 0
. It is used to represent any amount reimbursed for you for any reason (insurance / cashback / someone paid you back) and you want to register it. If you don't, leave it as zero. category
must be an integer representing the sub-category. recurring
, if true
, means that it's an expense that usually appears every month. In this app, this will have two effects:
- It will appear in bold in the expenses list
- Users will have the option to copy recurring expenses for the next month. While doing so, they can choose to copy the amounts as well, or set them as zero and edit later.
Architecture
App is built in React using the Next.js framework. We are also using API routes to provide backend functionality.
API
These are the endpoints with a brief explanation. For complementary understanding, you can import this file into Postman.
POST /api/users/authenticate
(authenticate user with email and password) POST /api/users/register
(create an user) DELETE /api/users
(delete user) GET /api/categories
(get all created categories for the logged user) POST /api/categories
(create a new category) PATCH /api/categories/:id
(rename a category) DELETE /api/categories/:id
(delete a category) GET /api/expenses
(get all expenses for logged user) GET /api/expenses/:year/:month
(get expenses for a particular year and month) GET /api/expenses/years
(get a list of years in which user created at least one expense) POST /api/expenses
(create a new expense) POST /api/expenses/recurring
(copy recurring expenses for the next month) PUT /api/expenses/:id
(edit expense) DELETE /api/expenses
(delete a list of expenses) DELETE /api/expenses/:id
(delete a particular expense) GET /api/stats/year-evolution
(expense data grouped by year (and optionally, category) for chart presentation) GET /api/stats/month-evolution
(expense data grouped by month (and optionally, category) for chart presentation) GET /api/stats/category-report
(expense data grouped by month, year and category, for tabular presentation)
Authentication
Authentication to the API is done via JSON web token (JWT). Here is what happens:
- When user signs up, a request is made to
/api/users/register
with username and password.
- When user logs in, they send its password, which is hashed and compared to what is stored in the database. If they match, API returns a JWT.
- With the user logged in, any request to the API to retrieve/create/update/delete expenses or categories must send JWT in the header of the request.
Database
In this project, we are using a PostgreSQL database with the following tables:
Users
A very straightforward table.
Gives every user an id and stores the username and hashed password.
CREATE TABLE IF NOT EXISTS public.users
(
id integer NOT NULL GENERATED ALWAYS AS IDENTITY _
( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
username character varying(20) COLLATE pg_catalog."default" NOT NULL,
hash bytea NOT NULL,
CONSTRAINT user_pkey PRIMARY KEY (id)
);
Categories
In this table, we can store both main categories and sub-categories, with a strategy called Adjacency List Tree.
Every category gets its own id
and belongs to a certain user_id
. There's a foreign key constraint with table users.
A sub-category will have a parent_id
, which must be a valid id from the same table (also a foreign key, this time in the same table). If it's a main category, it will have parent_id
as null
.
CREATE TABLE IF NOT EXISTS public.category
(
id integer NOT NULL GENERATED ALWAYS AS IDENTITY _
( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
user_id integer NOT NULL,
parent_id integer,
title character varying(25) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT unique_id UNIQUE (id),
CONSTRAINT self FOREIGN KEY (parent_id)
REFERENCES public.category (id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT user_id FOREIGN KEY (user_id)
REFERENCES public.users (id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
)
Please note ON UPDATE CASCADE
and ON DELETE CASCADE
set for the constraints. This means that if a user is deleted, their categories will also be deleted. Also, if a main category is deleted, its sub-categories will be deleted as well.
Finding main categories and sub-categories can be done with simple queries as such:
SELECT *
FROM category
WHERE user_id = 1 AND parent_id IS NULL;
SELECT *
FROM category
WHERE user_id = 1 AND parent_id IS NOT NULL;
Expense
This table will hold an expense. It's very similar to the expense object we saw earlier, with some constraints.
CREATE TABLE IF NOT EXISTS public.expense
(
last_modified timestamp without time zone DEFAULT now(),
id integer NOT NULL GENERATED ALWAYS AS IDENTITY _
( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
user_id integer NOT NULL,
category integer NOT NULL,
year smallint NOT NULL,
month smallint NOT NULL,
day smallint,
recurring boolean DEFAULT false,
amount_paid numeric(7,2) NOT NULL,
amount_reimbursed numeric(7,2) NOT NULL DEFAULT 0,
description character varying(70) COLLATE pg_catalog."default" NOT NULL,
details character varying(70) COLLATE pg_catalog."default",
CONSTRAINT expense_pkey PRIMARY KEY (id),
CONSTRAINT expense_category_fkey FOREIGN KEY (category)
REFERENCES public.category (id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT expense_user_id_fkey FOREIGN KEY (user_id)
REFERENCES public.users (id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT amount_paid CHECK (amount_reimbursed <= amount_paid),
CONSTRAINT description CHECK (length(description::text) > 2),
CONSTRAINT valid_day CHECK (day >= 1 AND day <= 31),
CONSTRAINT valid_month CHECK (month >= 1 AND month <= 12),
CONSTRAINT valid_year CHECK (year > 2011 AND year < 2050)
)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS year
ON public.expense USING btree
(year ASC NULLS LAST)
TABLESPACE pg_default;
CREATE FUNCTION sync_lastmod() RETURNS trigger AS $$
BEGIN
NEW.last_modified := NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER
sync_lastmod
BEFORE UPDATE ON
expense
FOR EACH ROW EXECUTE PROCEDURE
sync_lastmod();
A couple observations:
- a
last_modified
timestamp column. It's automatically filled when an expense is created (DEFAULT now()
) or updated (look at trigger sync_lastmod
). - foreign keys with tables users and category (also with
CASCADE
) - index created on the
year
column for efficient querying by year - column types were carefully chosen for optimization data usage
- It's a very good idea to make constraints at the deepest possible level to avoid unexpected behavior. The following constraints were used:
year
must be between 2011 and 2050 (for my personal case. change if you like it!) month
must be between 1 and 12 day
must be between 1 and 31 description
and details
are limited to 70 characters (description can't be empty, must have at least 2 characters). - regarding
amount_paid
and amount_reimbursed
:
- numeric(7,2) means 7 significant digits, being 2 decimal digits in the fractional part. This means the maximum number allowed is 99,999.99 (if you need bigger numbers, change this!)
amount_paid
must be greater than or equal to amount_reimburserd
- the order of the column matters regarding disk usage! More details in this question.
Creating constraints in the database does not mean you should not verify the same thing in the API or the frontend (will be faster for the user if you do), it's an extra layer for preventing errors.
Expense View
We stored amount_paid
and amount_reimbursed
. We will present it to the user on the expenses list, but for the reports and charts, we want to consider (amount_paid
- amount_reimbursed
). A good approach here is to create a view, with only the columns we need, that will be updated automatically every time an expense is created or updated.
Notice that we are creating the column amount_spent
.
CREATE OR REPLACE VIEW public.expense_view
AS
SELECT
expense.user_id,
expense.year,
expense.month,
expense.day,
expense.category,
expense.recurring,
COALESCE(expense.amount_paid, 0::numeric) - _
COALESCE(expense.amount_reimbursed, 0::numeric) AS amount_spent
FROM expense;
For example, the query for the evolution chart would be made against the expense_view
, like this:
SELECT year, month, SUM(amount_spent) as sum
FROM public.expense_view
WHERE user_id = 1
and year BETWEEN 2021 and 2022
GROUP BY year, month
ORDER BY year, month;
While a query for the expenses list would be against expense
table.
SELECT id, user_id, year, month, day, description, details, _
recurring, amount_paid, amount_reimbursed, last_modified
FROM public.expense
WHERE user_id = 1
AND year = 2022
ORDER BY id asc;
Frontend
This project uses Material UI as design system and UI tools. You might find that buttons, texts, inputs, datepickers look similar to components on Google's products.
Login / Register
These two pages are very similar. They both rely on the UserPasswordForm component, which creates a simple form with two inputs (username and password).
Components
In total, there are 18 reusable components. For example:
- ExpensesTable for listing expenses, both when they are listed by month or searched via keyword.
- AppBar is present on all pages.
- YearPicker appears multiple times on the project.
Custom App Component
Next.js uses the App component to initialize pages. You can control page initialization, allowing you to:
- persist layout between page changes
- keep stage when navigating
- inject data into pages
- add global CSS
In this project, we have done:
- Categories are fetched only once, when users log in, and provided as context to all pages. This is because users will not always change their categories. We expect they will create them once it and then rarely change. So we load them only once per login unless user makes changes to them (in this case, a full reload is done).
- Screen size (for responsive layout adjustments) and visibility settings (to hide values) are also provided to all pages.
Charts
To create the evolution charts, Google charts and this library were used. So far, only the Column chart has been used. Maybe more to come in the future!
Running Locally
First, you must have Git, Node.js, and docker installed.
We will use docker to launch a PostgreSQL database instance and run the project with Node.
Then:
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
Code Quality and Security
Being open-source, this project takes advantage of the free SonarCloud product by SonarQube project. After every build, code quality is measured and bugs, code smells, vulnerabilities and security hotspots are identified. There is also data for test coverage and code duplication.
You can check the reports here.
History
- 3rd October, 2022: Initial version
- 12th December, 2022: Added
SonarQube
section