Objectives

• Intensive use of React hooks and context
• Create filter (search functionality) of expenses by payer and amount
• Migration: add category ressource and relation to expense
• Add category to expense form creation
• Search expenses by category

Assumption

• Your backend and frontend run correctly on your local machine and Render.
• The first migration has been done and the database contains the tables expense, user, and transfer.
• Your db must be populated with some data to test the search functionalities.

1. Search

1.1 Search by amount

Backend

The backend should be modified to handle the query parameter amount and return the expenses that have an amount greater than or equal to the value entered in the input field. In other words amount is a minimum, so searching for 10 also returns an expense of exactly 10.

In the file src/routes/expenses.router.ts, modify the route returning all expenses to handle the query parameter amount. Do the necessary checks to ensure that the parameter is a valid number (use zod preferably)

In the service src/services/expenses.service.ts, modify the getExpenses method to accept an optional parameter filter that is an object with a key amount and the value entered in the input field.

Modify the prisma call to filter the expenses by the amount. See official prisma documentation for filtering.

Verify with .http file that your backend works as expected.

UI

In the UI, create a new form to search expenses by amount. In this form, add a input field to enter a number and a button to search.

When the form is submitted, a call to the backend should be made on the URL /api/expenses. This call will take a query parameter with a key amount and the value entered in the input field. The new url will be something like /api/expenses?amount=<amount>.

Tip:

Your types/expense.ts file is not limited to export types. It can also export helper functions related to types. For example parsing raw input and coerce it, or validate an input. It can be a supercharged version of your guard file.

Here is an example for this exercice, leveraging zod for data validation, type checking, and parsing. We only define the data validation logic once, and then get everything we need from it.

import { z } from "zod";
import { userSchema } from "./user.ts";

export const expenseSchema = z.object({
  id: z.string(),
  date: z.iso.datetime({ offset: true }).pipe(z.coerce.date()),
  description: z.string().max(200),
  payerId: z.number(),
  payer: userSchema.optional(),
  amount: z.number().nonnegative(),
});

export const newExpenseSchema = expenseSchema.omit({ id: true });

const expenseFilterSchema = z.object({
  amount: z.coerce.number().nonnegative().optional(),
});

function parseWithSchema<T>(schema: z.ZodType<T>, data: unknown): T | false {
  const result = schema.safeParse(data);
  return result.success ? result.data : false;
}


export type Expense = z.infer<typeof expenseSchema>;
export type NewExpense = z.infer<typeof newExpenseSchema>;
export type ExpenseFilter = z.infer<typeof expenseFilterSchema>;


export function parseExpenseFilter(data: unknown): ExpenseFilter | false {
  return parseWithSchema(expenseFilterSchema, data);
}

export function parseNewExpense(data: unknown): NewExpense | false {
  return parseWithSchema(newExpenseSchema, data);
}

export function expenseFromDBO(dbo: any): Expense {
  return {
      ...dbo,
      id: dbo.id.toString(),
      date: new Date(dbo.date),
  };
}


export function expenseFromDBO(dbo: any): Expense {
  return {
      ...dbo,
      id: dbo.id.toString(),
      date: new Date(dbo.date),
  };
}

1.2 Search by payer

Backend

New ressources: Users

Create a new router users.router.ts in the src/routes directory. This router should have a route /api/users that returns all users. Create a service to handle the logic of fetching users from the database.

Verify with .http file that your backend works as expected.

Search Expenses by payerId

Modify the backend to handle the query parameter payerId and return the expenses that have a payer equal to the given value.

Name your files after the resource, in the plural: expenses.router.ts, users.router.ts, expenses.service.ts, users.service.ts. A router serves a collection of things, and consistent naming is one less decision to make every time you add a resource.

Verify with .http file that your backend works as expected.

UI

Step 1: create a form with an input to filter by payerId

First, create a form with an input field to enter the payerId value (as a string). Send this value to the backend when the form is submitted and observe that you visualize the expenses paid by this user only.

Step 2: define a dropdown to select a user

Create one hook to use the new endpoint /api/users. Only implement the fetchAll method. Follow the same pattern as the previous hooks (see lesson 3).

In the search form, prepare a dropdown menu to select a user. The dropdown should be populated with the users returned by the backend. Adpte the form to accept both the amount and the user as filters.

2. Migration

2.1 Create the new resource

Following the general migration pattern (see previous lesson), create a new migration to add a category resource and a relation to expense.

A category should have:

• a name (unique)
• a colour

Each expense can have one category. The category is optional.

Define a new prisma model Category in the src/prisma/contract.prisma file. Then define the relation between Expense and Category as a many-to-one relationship (see Prisma documentation).

2.2 Migration

Run the migration to create the new table in the database.

Reminder: to run the migration, use the following command in dev environment:

  npx prisma contract emit
  npx prisma migration plan --name add-category
  npx prisma db migrate --advance-ref db

Have a look to your migration plan and be sure that all the existing expense received the categoryId field with the value NULL as default.

While in production, use the following command:

npx prisma db migrate

Create a script to populate the database with some categories. Do not delete existing data. You try to reproduce your production environment as much as possible: so you try to migrate without dropping the database. That constraint is the whole exercise: adding a feature to a system that already holds data people care about.

Using some VSC extension, modify some existing expenses to have a category assigned to them.

A note on the reference solution. Our published solution ships a single baseline migration that builds the whole schema at once, and a db-populate.ts that wipes and refills the demo data. That is a convenience for someone adopting the solution on a fresh database — not a model of how to add a column to a live system. The migration you write here, on top of existing rows, is the realistic one.

2.3 BE + UI

Create a new route in the backend to fetch all categories.

Modify the UI to:

• create a new hook to fetch all categories
• allow users to select a category when creating an expense. Insert a dropdown menu to select a category. The dropdown should be populated with the categories returned by the backend.

At rendering time, display the category name with a colour in the expense list.

Finally, when creating an expense, ask the user to select a category.

2.4 Search by category

Backend

Modify the backend to handle the query parameter categoryId and return the expenses that have a category equal to the given value.

UI

In the search form, add a dropdown menu to select a category. The dropdown should be populated with the categories returned by the backend. Adpte the form to accept the category as a filter.