Track Your Expenses with this Next.js/React App

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 strings. 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.
  • password is hashed using bcrypt and saved in the database along with the username. Note that it's not the password itself that gets saved in the database.
• 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:

/* Finding the main categories*/
SELECT *
FROM category
WHERE user_id = 1 AND parent_id IS NULL;

/* Finding sub-categories */
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;

-- Index: year
CREATE INDEX IF NOT EXISTS year
    ON public.expense USING btree
    (year ASC NULLS LAST)
    TABLESPACE pg_default;

-- Trigger
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

• 3^rd October, 2022: Initial version
• 12^th December, 2022: Added SonarQube section