Build a Full-Stack Loan Management System

Introduction

30 Second Summary

Every time someone takes out a student loan or a mortgage, there's software behind the scenes tracking payments, calculating interest, and keeping records straight. Banks rely on these systems every single day.

In this project, you will build a full-stack Loan Management System from scratch. You'll learn REST APIs, SQL, and async JavaScript by building every layer yourself.

What You'll Build

A 3-tier web application with a vanilla JavaScript frontend, an Express.js API, and a PostgreSQL database that tracks loans, compounds interest automatically, and displays data in an interactive table.

By the end of this project, you'll have:

• A working 3-tier architecture with a frontend, API, and database that you can explain line-by-line.
• A data-rich interactive table with sorting, filtering, inline editing, and currency formatting built from scratch with vanilla JavaScript.
• An automated monthly processing system using node-cron that compounds interest and generates loan statements on schedule.
• Secret Mission: Add a loan amortization schedule calculator that shows the full payment breakdown with a downloadable CSV export.

Are there any prerequisites?

You should be comfortable with basic JavaScript: functions, arrays, objects, and loops. No prior knowledge of REST APIs, databases, or async programming is required.

Before We Start

You are about to build a full-stack Loan Management System with three layers: a frontend the user sees, an API that handles logic, and a database that stores data.

Before writing any code, you need the right tools installed and a project folder ready to go. This step gets your machine set up with PostgreSQL (your database), confirms Node.js is ready, and creates the folder structure your project will live in.

In this step, get ready to:

• Confirm Node.js is installed on your machine.
• Install PostgreSQL 16 as your database.
• Create the project folder and install server dependencies.

Confirm Node.js is installed

Node.js is the JavaScript runtime that powers your server. Let's confirm it is working on your machine.

• Press Cmd+Space to open Spotlight search.
• Type Terminal and press Enter to open it.
• Check your Node.js version by running this command:

node --version

✔️ I see a version number

Node.js is ready to go. Move on to the next substep.

ⓧ Command not found

Node.js is not installed. Visit nodejs.org and download the LTS installer for macOS.

Run the installer, then close and reopen Terminal. Run node --version again to confirm it works.

Install Homebrew and PostgreSQL 16

PostgreSQL is the database where your loan data will live. You will install it using Homebrew, a package manager for macOS that makes installing developer tools simple.

• Check if Homebrew is already installed by running this command:

brew --version

✔️ I see a version number

Homebrew is ready. Skip ahead to the PostgreSQL install below.

ⓧ Command not found

• Install Homebrew by running this command:

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

What does this command do?

This downloads and runs the official Homebrew installer script. It will ask for your macOS password during installation. Type your password (you will not see characters appear) and press Enter.

• Follow any instructions the installer prints at the end. On Apple Silicon Macs, it typically asks you to run two commands to add Homebrew to your PATH.
• Close and reopen Terminal, then run brew --version again to confirm it works.

Now install PostgreSQL 16 using Homebrew.

• Install PostgreSQL 16 by running this command:

brew install postgresql@16

What does this command do?

This installs PostgreSQL version 16 on your machine. Homebrew automatically creates a default database cluster during installation.

The formula is "keg-only," which means its binaries are not added to your PATH by default. You will fix that in the next step.

• Add the PostgreSQL binaries to your PATH so your terminal can find the psql command. Run this:

echo 'export PATH="/opt/homebrew/opt/postgresql@16/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

Why do I need to update my PATH?

Because postgresql@16 is keg-only, Homebrew does not automatically link it into your system's default command locations. The echo command permanently adds the PostgreSQL binaries to your shell config.

The source command reloads your shell config so the change takes effect immediately.

• Start the PostgreSQL service by running this command:

brew services start postgresql@16

• Verify PostgreSQL is running by connecting with psql:

psql postgres

✔️ I see a postgres=# prompt

PostgreSQL is running. Type \q and press Enter to exit the prompt, then continue to the next substep.

ⓧ I see an older PostgreSQL version conflict

If you already had an older PostgreSQL installed, you may have a version conflict. Stop the old service first:

brew services stop postgresql
brew services start postgresql@16

Then try psql postgres again.

ⓧ Command not found or connection error

If psql is not found, make sure the PATH update from the previous step worked. Close and reopen Terminal, then try again.

If you see a connection error, the service may not have started. Check the service status:

brew services info postgresql@16

If it shows an error, try restarting:

brew services restart postgresql@16

Still having trouble with PostgreSQL?

Port conflicts are the most common issue. If another PostgreSQL version is already using port 5432, the new service cannot start. Stop all other PostgreSQL services before starting postgresql@16.

help me troubleshoot my PostgreSQL installation on macOS via Homebrew

Create the project folder and install dependencies

Your project needs a clear folder structure. You will create a root folder with two subdirectories: server/ for the API code and client/ for the frontend.

• Move to your Desktop by running this command:

cd ~/Desktop

• Create your project folder and move into it by running these commands:

mkdir loan-management
cd loan-management

• Create the server/ and client/ directories by running:

mkdir server client

• Confirm the folders exist by running:

ls

You should see client and server in the output.

• Move into the server/ directory and initialize it as a Node.js project by running these commands:

cd server
npm init -y

What does npm init -y do?

This creates a package.json file that tracks your project's metadata and dependencies. The -y flag accepts all default values so you do not have to answer prompts.

• Install the four dependencies your server needs by running:

npm install express pg cors node-cron

What are these packages?

• express is the web framework that handles HTTP requests and routes.
• pg is the PostgreSQL driver that lets Node.js talk to your database.
• cors allows your frontend to make requests to your API from a different port.
• node-cron schedules automated tasks (like monthly interest processing) using cron expressions.

Seeing an error during npm install?

Make sure you are inside the server/ folder when running npm install. Run pwd to check your current directory. It should end with loan-management/server.

help me fix npm install errors in my loan management project

• Now update your package.json to set the correct project name, description, and scripts. Open the folder in VS Code by running:

code .

What does code . do?

This opens the current folder (server/) in VS Code. If this command does not work, open VS Code manually. Press Cmd+Space, type Visual Studio Code, and press Enter.

Then click File in the menu bar, click Open Folder, and select the server folder inside loan-management on your Desktop.

• In the VS Code file explorer on the left, click package.json to open it.
• Replace the entire contents of the file with the following:

{
  "name": "loan-management-server",
  "version": "1.0.0",
  "description": "Loan Management System API",
  "main": "src/index.js",
  "scripts": {
    "start": "node src/index.js",
    "dev": "node --watch src/index.js",
    "seed": "node src/seed.js"
  },
  "dependencies": {
    "cors": "^2.8.5",
    "express": "^4.21.2",
    "node-cron": "^4.2.1",
    "pg": "^8.13.1"
  }
}

What are these scripts?

• start runs your server in production mode.
• dev runs your server and automatically restarts it when you save changes (thanks to Node's --watch flag).
• seed will populate your database with sample data. You will create this script later.

• Save package.json by pressing Cmd+S.

✔️ Awesome, I've got everything!

Great. Double check you have saved package.json and that it matches the code above.

ⓧ I'd like to double check the full code

Here is the complete server/package.json file. Make sure yours matches exactly:

{
  "name": "loan-management-server",
  "version": "1.0.0",
  "description": "Loan Management System API",
  "main": "src/index.js",
  "scripts": {
    "start": "node src/index.js",
    "dev": "node --watch src/index.js",
    "seed": "node src/seed.js"
  },
  "dependencies": {
    "cors": "^2.8.5",
    "express": "^4.21.2",
    "node-cron": "^4.2.1",
    "pg": "^8.13.1"
  }
}

Your environment is ready. You have PostgreSQL running, a project folder on your Desktop, and all four server dependencies installed. Next up, you will connect to PostgreSQL and build the database tables that store your loan data.

Set Up the Database Schema

Your project folder is ready and your dependencies are installed. Now you need somewhere to actually store loan data.

A database gives your application persistent, structured storage. In this step, you will create a PostgreSQL database, define tables that describe the shape of your data, and populate those tables with sample records so you have something to query later.

In this step, get ready to:

• Connect to PostgreSQL and create the loan_management database.
• Define the relational schema with five tables.
• Seed the database with sample borrowers and loans.

Connect to PostgreSQL and create the database

Before you can create tables, you need a database to hold them. PostgreSQL organizes data inside named databases. You will create one called loan_management.

• Open the PostgreSQL interactive terminal by running this command:

psql postgres

What is psql?

psql is the command-line client for PostgreSQL. It lets you type SQL commands and see results instantly. The word postgres after the command is the name of the default database that every PostgreSQL installation comes with. You connect to it first, then create your own database from there.

You should see a prompt that looks like postgres=#. This means you are connected and ready to type SQL.

Seeing "connection refused" or "command not found"?

Make sure the PostgreSQL service is running. You can start it with brew services start postgresql@16. If psql itself is not found, you may need to add the Homebrew PostgreSQL bin directory to your PATH.

Help me troubleshoot my psql connection

• Create the loan_management database by running this SQL command:

CREATE DATABASE loan_management;

• Connect to your new database by running:

\c loan_management

Your prompt should change to loan_management=# confirming you are now inside the new database.

Create the database tables

Tables define the shape of your data. Each table has columns with specific data types that control what values can be stored. You will create five tables that work together to track borrowers, loans, financial transactions, monthly statements, and automated job history.

Why NUMERIC instead of regular numbers?

JavaScript and most programming languages use floating-point math, where 0.1 + 0.2 does not equal 0.3 exactly. For money, that tiny imprecision compounds over thousands of transactions.

PostgreSQL's NUMERIC(12,2) type stores numbers with exact decimal precision. The 12 means up to 12 total digits, and the 2 means exactly 2 decimal places. This is the standard approach for financial data.

• Create the borrowers table by running this SQL in your psql session:

CREATE TABLE borrowers (
  id SERIAL PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

What does this table do?

SERIAL PRIMARY KEY auto-generates a unique ID for each row. TEXT NOT NULL means the column stores text and cannot be left empty. UNIQUE on email prevents duplicate email addresses.

TIMESTAMPTZ DEFAULT NOW() automatically records when each borrower was added.

You should see CREATE TABLE printed in the terminal, confirming the table was created.

• Create the loans table by running:

CREATE TABLE loans (
  id SERIAL PRIMARY KEY,
  borrower_id INTEGER REFERENCES borrowers(id),
  principal_amount NUMERIC(12,2) NOT NULL,
  current_balance NUMERIC(12,2) NOT NULL,
  interest_rate NUMERIC(5,4) NOT NULL,
  term_months INTEGER NOT NULL,
  start_date DATE NOT NULL,
  status TEXT NOT NULL DEFAULT 'ACTIVE',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

What does this table do?

REFERENCES borrowers(id) creates a foreign key. This means every loan must belong to a valid borrower. PostgreSQL will reject any loan that references a borrower ID that does not exist.

NUMERIC(5,4) on interest_rate stores rates like 0.0750 (7.5%). The status column defaults to ACTIVE so new loans are automatically marked as active.

• Create the ledger_entries table by running:

CREATE TABLE ledger_entries (
  id SERIAL PRIMARY KEY,
  loan_id INTEGER REFERENCES loans(id),
  entry_type TEXT NOT NULL,
  amount NUMERIC(12,2) NOT NULL,
  balance_after NUMERIC(12,2) NOT NULL,
  description TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

What is a ledger entry?

A ledger is a record of every financial transaction. Each time interest is charged or a payment is made, a new row goes into this table. The balance_after column creates a traceable history of how the loan balance changed over time.

• Create the monthly_statements table by running:

CREATE TABLE monthly_statements (
  id SERIAL PRIMARY KEY,
  loan_id INTEGER REFERENCES loans(id),
  statement_date DATE NOT NULL,
  opening_balance NUMERIC(12,2) NOT NULL,
  interest_charged NUMERIC(12,2) NOT NULL,
  closing_balance NUMERIC(12,2) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

• Create the job_runs table by running:

CREATE TABLE job_runs (
  id SERIAL PRIMARY KEY,
  job_name TEXT NOT NULL,
  status TEXT NOT NULL,
  started_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,
  loans_processed INTEGER,
  errors TEXT
);

What are monthly_statements and job_runs for?

The monthly_statements table stores a snapshot of each loan at the end of every month: what the balance was, how much interest was charged, and the resulting new balance.

The job_runs table logs every time your automated cron job runs. It records whether it succeeded, how many loans it processed, and any errors. This gives you an audit trail of the system's automated work.

• Verify all five tables were created by running:

\dt

You should see a list of five tables: borrowers, loans, ledger_entries, monthly_statements, and job_runs.

Don't see all five tables?

If a CREATE TABLE command failed, check for typos in column names or missing commas between column definitions. You can drop a table with DROP TABLE tablename; and recreate it.

Make sure your prompt says loan_management=# and not postgres=#. If it says postgres, you forgot to switch databases with \c loan_management.

Help me fix my CREATE TABLE error

Write a seed script to populate the database

Empty tables are not very useful for testing. You need sample data to verify that your API works later. You will create a Node.js script that connects to the database and inserts 20 borrowers and 50 loans using raw SQL INSERT statements.

• First, exit psql by running:

\q

• In VS Code, create a new folder called src inside the server directory.
• Create a new file at server/src/db.js. This file manages the connection between your Node.js code and PostgreSQL.
• Paste the following code into db.js:

const { Pool } = require('pg');

// Create a connection pool to the loan_management database
const pool = new Pool({
  user: process.env.USER,
  host: 'localhost',
  database: 'loan_management',
  port: 5432,
});

module.exports = { pool };

What does this code do?

Pool from the pg package manages a group of reusable database connections. Instead of opening a new connection for every query (which is slow), a pool keeps several connections open and shares them.

process.env.USER automatically uses your macOS username, which matches the default PostgreSQL user created by Homebrew. No password is needed for local connections.

• Save db.js.
• Create a new file at server/src/seed.js. This script will insert sample data into your database.
• Paste the following code into seed.js:

const { pool } = require('./db');

// Sample first and last names for generating borrowers
const firstNames = [
  'James', 'Maria', 'Robert', 'Linda', 'David',
  'Sarah', 'Michael', 'Jennifer', 'William', 'Patricia',
  'Richard', 'Elizabeth', 'Joseph', 'Susan', 'Thomas',
  'Jessica', 'Christopher', 'Karen', 'Daniel', 'Nancy'
];

const lastNames = [
  'Smith', 'Johnson', 'Williams', 'Brown', 'Jones',
  'Garcia', 'Miller', 'Davis', 'Rodriguez', 'Martinez',
  'Hernandez', 'Lopez', 'Gonzalez', 'Wilson', 'Anderson',
  'Thomas', 'Taylor', 'Moore', 'Jackson', 'Martin'
];

async function seed() {
  try {
    // Clear existing data (order matters due to foreign keys)
    await pool.query('DELETE FROM monthly_statements');
    await pool.query('DELETE FROM ledger_entries');
    await pool.query('DELETE FROM loans');
    await pool.query('DELETE FROM borrowers');

    // Insert 20 borrowers
    for (let i = 0; i < 20; i++) {
      await pool.query(
        'INSERT INTO borrowers (first_name, last_name, email) VALUES ($1, $2, $3)',
        [
          firstNames[i],
          lastNames[i],
          `${firstNames[i].toLowerCase()}.${lastNames[i].toLowerCase()}@email.com`
        ]
      );
    }
    console.log('Inserted 20 borrowers');

    // Insert 50 loans with varied amounts, rates, and terms
    const statuses = ['ACTIVE', 'ACTIVE', 'ACTIVE', 'PAID_OFF', 'DEFAULTED'];
    for (let i = 0; i < 50; i++) {
      const borrowerId = (i % 20) + 1;
      const principal = (Math.floor(Math.random() * 50) + 5) * 1000;
      const rate = (Math.floor(Math.random() * 15) + 3) / 100;
      const term = [12, 24, 36, 48, 60][Math.floor(Math.random() * 5)];
      const status = statuses[Math.floor(Math.random() * statuses.length)];
      const balance = status === 'PAID_OFF' ? 0 : principal * (1 + rate * Math.random());

      await pool.query(
        `INSERT INTO loans (borrower_id, principal_amount, current_balance, interest_rate, term_months, start_date, status)
         VALUES ($1, $2, $3, $4, $5, CURRENT_DATE - INTERVAL '1 month' * $6, $7)`,
        [
          borrowerId,
          principal,
          Math.round(balance * 100) / 100,
          rate,
          term,
          Math.floor(Math.random() * term),
          status
        ]
      );
    }
    console.log('Inserted 50 loans');

    console.log('Seed complete!');
  } catch (err) {
    console.error('Seed failed:', err.message);
  } finally {
    // Close the connection pool
    await pool.end();
  }
}

seed();

What does this script do?

The script uses parameterized queries ($1, $2, etc.) to safely insert values. This is how professional code avoids SQL injection vulnerabilities.

It first deletes any existing data in reverse order of dependencies (statements before loans, loans before borrowers) because foreign keys prevent you from deleting a borrower that still has loans. Then it loops through names to create 20 borrowers and generates 50 loans with randomized amounts, rates, and terms.

• Save seed.js.
• Return to your terminal and make sure you are inside the server folder.
• Run the seed script by running:

npm run seed

You should see three lines printed in the terminal:

Inserted 20 borrowers
Inserted 50 loans
Seed complete!

Seeing "relation does not exist" or connection errors?

If you see a "relation does not exist" error, make sure you created all five tables in the loan_management database (not in postgres). Reconnect to psql and check with \c loan_management then \dt.

If you see a connection error, confirm PostgreSQL is running with brew services list. Look for postgresql@16 with a status of started.

Help me fix my seed script error

• Verify the data is in the database by opening psql again:

psql loan_management

• Run a SELECT query to confirm the data exists:

SELECT COUNT(*) FROM borrowers;
SELECT COUNT(*) FROM loans;

You should see 20 for borrowers and 50 for loans. Exit psql with \q when you are done.

✔️ Awesome, I've got everything!

Great. Make sure both server/src/db.js and server/src/seed.js are saved.

ⓧ I'd like to double check the full code

Here is the complete db.js file:

const { Pool } = require('pg');

const pool = new Pool({
  user: process.env.USER,
  host: 'localhost',
  database: 'loan_management',
  port: 5432,
});

module.exports = { pool };

Here is the complete seed.js file:

const { pool } = require('./db');

const firstNames = [
  'James', 'Maria', 'Robert', 'Linda', 'David',
  'Sarah', 'Michael', 'Jennifer', 'William', 'Patricia',
  'Richard', 'Elizabeth', 'Joseph', 'Susan', 'Thomas',
  'Jessica', 'Christopher', 'Karen', 'Daniel', 'Nancy'
];

const lastNames = [
  'Smith', 'Johnson', 'Williams', 'Brown', 'Jones',
  'Garcia', 'Miller', 'Davis', 'Rodriguez', 'Martinez',
  'Hernandez', 'Lopez', 'Gonzalez', 'Wilson', 'Anderson',
  'Thomas', 'Taylor', 'Moore', 'Jackson', 'Martin'
];

async function seed() {
  try {
    await pool.query('DELETE FROM monthly_statements');
    await pool.query('DELETE FROM ledger_entries');
    await pool.query('DELETE FROM loans');
    await pool.query('DELETE FROM borrowers');

    for (let i = 0; i < 20; i++) {
      await pool.query(
        'INSERT INTO borrowers (first_name, last_name, email) VALUES ($1, $2, $3)',
        [
          firstNames[i],
          lastNames[i],
          `${firstNames[i].toLowerCase()}.${lastNames[i].toLowerCase()}@email.com`
        ]
      );
    }
    console.log('Inserted 20 borrowers');

    const statuses = ['ACTIVE', 'ACTIVE', 'ACTIVE', 'PAID_OFF', 'DEFAULTED'];
    for (let i = 0; i < 50; i++) {
      const borrowerId = (i % 20) + 1;
      const principal = (Math.floor(Math.random() * 50) + 5) * 1000;
      const rate = (Math.floor(Math.random() * 15) + 3) / 100;
      const term = [12, 24, 36, 48, 60][Math.floor(Math.random() * 5)];
      const status = statuses[Math.floor(Math.random() * statuses.length)];
      const balance = status === 'PAID_OFF' ? 0 : principal * (1 + rate * Math.random());

      await pool.query(
        `INSERT INTO loans (borrower_id, principal_amount, current_balance, interest_rate, term_months, start_date, status)
         VALUES ($1, $2, $3, $4, $5, CURRENT_DATE - INTERVAL '1 month' * $6, $7)`,
        [
          borrowerId,
          principal,
          Math.round(balance * 100) / 100,
          rate,
          term,
          Math.floor(Math.random() * term),
          status
        ]
      );
    }
    console.log('Inserted 50 loans');

    console.log('Seed complete!');
  } catch (err) {
    console.error('Seed failed:', err.message);
  } finally {
    await pool.end();
  }
}

seed();

Your database is set up with a proper relational schema and loaded with sample data. Next up, you will build a REST API that reads and writes to this database.

Build Your First REST API Endpoints

Your database is seeded with 20 borrowers and 50 loans. The data is sitting there, ready to be used. But right now, the only way to access it is by typing SQL commands directly in psql.

In this step, you will build a REST API that turns your database into something the frontend can talk to. You will create endpoints that accept HTTP requests and respond with JSON data.

In this step, get ready to:

• Set up the Express server and build your first GET endpoint.
• Add endpoints for fetching a single loan, creating loans, and updating loans.
• Create the borrowers route and the monthly interest processing service.

Set up the Express server and build your first GET endpoint

A REST API is a set of rules for how software communicates over the internet. URLs represent resources (like /api/loans represents all loans). HTTP methods represent actions: GET reads data, POST creates data, PATCH updates data, DELETE removes data.

What is a REST API?

Think of a restaurant. The menu is your list of URLs. Your order (GET, POST, PATCH) tells the kitchen what action to take. The waiter (your Express server) carries requests from the table (frontend) to the kitchen (database) and brings back the food (JSON data).

Every REST endpoint follows the same pattern: receive a request, do something with the database, send back a response. That is the entire pattern you will repeat for every endpoint in this step.

First, you will create a route file that handles all loan-related requests. Express uses a Router to group related endpoints into their own file.

• Create a new folder inside server/src/ called routes by running this command from the server folder:

mkdir src/routes

• Create a new file called loans.js inside server/src/routes/ and add the following code:

const express = require('express');
const router = express.Router();
const { pool } = require('../db');

// GET all loans with borrower info
router.get('/', async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT l.*, b.first_name, b.last_name, b.email
      FROM loans l
      JOIN borrowers b ON l.borrower_id = b.id
      ORDER BY l.created_at DESC
    `);
    res.json(result.rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;

What does this code do?

• express.Router() creates a mini-app that handles a group of related routes. You will mount it at /api/loans so this / handler actually responds to GET /api/loans.
• The SQL query uses a JOIN to combine loan data with the borrower's name and email, so the frontend gets everything it needs in one request.
• async/await lets you wait for the database query to finish before sending the response. The try/catch block catches any database errors and returns a 500 status code.

Now you need the main Express server file that mounts this route and starts listening for requests.

• Create a new file called index.js inside server/src/ and add the following code:

const express = require('express');
const cors = require('cors');
const loanRoutes = require('./routes/loans');

const app = express();
const PORT = 3001;

// Middleware: allow cross-origin requests and parse JSON bodies
app.use(cors());
app.use(express.json());

// Mount loan routes at /api/loans
app.use('/api/loans', loanRoutes);

app.listen(PORT, () => {
  console.log(`Loan Management API running on http://localhost:${PORT}`);
});

What does this code do?

• cors() allows your frontend (which runs on a different port) to make requests to this API. Without it, the browser blocks cross-origin requests.
• express.json() parses incoming JSON request bodies so you can access req.body in your route handlers.
• app.use('/api/loans', loanRoutes) mounts the router. Every route defined in loans.js is prefixed with /api/loans.

• Save both files.
• Start the server by running this command from the server folder:

npm run dev

You should see Loan Management API running on http://localhost:3001 in your terminal.

• Open a second terminal and test your endpoint by running:

curl http://localhost:3001/api/loans

You should see a JSON array of loan objects printed in your terminal, each containing loan details plus the borrower's name and email.

Seeing an error or empty response?

• If you see Cannot find module './routes/loans', make sure your file is at server/src/routes/loans.js (not server/routes/loans.js).
• If you see connection refused, check that PostgreSQL is running with brew services list.
• If you get an empty array [], make sure you ran the seed script in Step 2.

Help me debug my Express server

Add loan CRUD endpoints

Your API can list all loans, but a real application needs more. It needs to fetch a single loan, create new loans, and update existing ones. Each operation maps to a different HTTP method.

• In server/src/routes/loans.js, find the line that says module.exports = router; and add the following endpoint above it:

// GET single loan by ID
router.get('/:id', async (req, res) => {
  try {
    const result = await pool.query(
      `SELECT l.*, b.first_name, b.last_name, b.email
       FROM loans l JOIN borrowers b ON l.borrower_id = b.id
       WHERE l.id = $1`,
      [req.params.id]
    );
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'Loan not found' });
    }
    res.json(result.rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

What are request parameters?

The /:id in the route path is a URL parameter. When someone requests GET /api/loans/5, Express captures 5 into req.params.id.

The $1 in the SQL query is a parameterized placeholder. The pg library replaces it with the value in the array [req.params.id]. This prevents SQL injection attacks.

• Below the GET /:id endpoint, add the POST endpoint for creating new loans:

// POST create a new loan
router.post('/', async (req, res) => {
  const { borrower_id, principal_amount, interest_rate, term_months } = req.body;

  if (!borrower_id || !principal_amount || !interest_rate || !term_months) {
    return res.status(400).json({ error: 'Missing required fields' });
  }

  try {
    const result = await pool.query(
      `INSERT INTO loans (borrower_id, principal_amount, current_balance, interest_rate, term_months, start_date, status)
       VALUES ($1, $2, $2, $3, $4, CURRENT_DATE, 'ACTIVE')
       RETURNING *`,
      [borrower_id, principal_amount, interest_rate, term_months]
    );
    res.status(201).json(result.rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

What does this endpoint do?

• req.body contains the JSON data sent with the POST request. The destructuring line pulls out the four required fields.
• The validation check returns a 400 (Bad Request) if any field is missing, so the database never receives incomplete data.
• Notice that $2 appears twice in the VALUES. This sets current_balance equal to principal_amount when a loan is first created.
• RETURNING * tells PostgreSQL to send back the newly created row, so the API can confirm what was inserted.

• Below the POST endpoint, add the PATCH endpoint for updating loans:

// PATCH update a loan
router.patch('/:id', async (req, res) => {
  const allowedFields = ['interest_rate', 'term_months', 'status'];
  const updates = [];
  const values = [];
  let paramCount = 1;

  for (const [key, value] of Object.entries(req.body)) {
    if (allowedFields.includes(key)) {
      updates.push(`${key} = $${paramCount}`);
      values.push(value);
      paramCount++;
    }
  }

  if (updates.length === 0) {
    return res.status(400).json({ error: 'No valid fields to update' });
  }

  values.push(req.params.id);

  try {
    const result = await pool.query(
      `UPDATE loans SET ${updates.join(', ')}, updated_at = NOW() WHERE id = $${paramCount} RETURNING *`,
      values
    );
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'Loan not found' });
    }
    res.json(result.rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

Why is PATCH more complex than POST?

PATCH only updates the fields you send, not the entire record. The allowedFields array acts as a whitelist. Only interest_rate, term_months, and status can be changed. This prevents someone from directly editing the loan balance through the API.

The loop dynamically builds the SQL query based on which fields were sent. If you send {"status": "PAID_OFF"}, it builds UPDATE loans SET status = $1, updated_at = NOW() WHERE id = $2.

• Save loans.js. Your dev server auto-restarts thanks to node --watch.
• Test the GET single loan endpoint in your second terminal:

curl http://localhost:3001/api/loans/1

You should see a single JSON object with the details of loan ID 1.

• Test the POST endpoint by creating a new loan:

curl -X POST http://localhost:3001/api/loans -H "Content-Type: application/json" -d '{"borrower_id": 1, "principal_amount": 25000, "interest_rate": 0.065, "term_months": 36}'

You should see the newly created loan object with a status of ACTIVE and current_balance matching the principal_amount.

Getting a 400 or 500 error on POST?

• Make sure you included -H "Content-Type: application/json" in your curl command. Without it, Express cannot parse the body.
• Check that borrower_id is a number that exists in your borrowers table (1 through 20 if you ran the seed).

Help me fix my POST endpoint

Create the borrowers route and interest processing service

Your loan endpoints are working. Now you need a borrowers endpoint (so the frontend can show a dropdown of borrower names) and the service that handles monthly interest calculations.

• Create a new file called borrowers.js inside server/src/routes/ and add the following code:

const express = require('express');
const router = express.Router();
const { pool } = require('../db');

// GET all borrowers
router.get('/', async (req, res) => {
  try {
    const result = await pool.query('SELECT * FROM borrowers ORDER BY last_name ASC');
    res.json(result.rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;

What does this code do?

This is the simplest possible endpoint. It queries all borrowers, orders them alphabetically by last name, and returns the array as JSON. The frontend will use this to populate dropdown menus when creating new loans.

• Create a new file called jobs.js inside server/src/routes/ and add the following placeholder code:

const express = require('express');
const router = express.Router();

// Job endpoints will be added in a later step

module.exports = router;

You will expand this file in a later step to handle job triggers and history. For now, it just needs to exist so the main server file can import it.

• Create a new folder called services inside server/src/ by running:

mkdir src/services

• Create a new file called interestService.js inside server/src/services/ and add the following code:

const { pool } = require('../db');

/**
 * Calculate monthly interest for all active loans.
 * Uses PostgreSQL NUMERIC math (not JavaScript floating point)
 * to keep financial calculations precise.
 */
async function processMonthlyInterest() {
  const client = await pool.connect();

  try {
    // Record that this job started
    const jobResult = await client.query(
      `INSERT INTO job_runs (job_name, status, started_at) VALUES ('monthly_interest', 'RUNNING', NOW()) RETURNING id`
    );
    const jobId = jobResult.rows[0].id;

    // Get all active loans
    const loansResult = await client.query(
      `SELECT id, current_balance, interest_rate FROM loans WHERE status = 'ACTIVE'`
    );

    let processed = 0;
    const errors = [];

    for (const loan of loansResult.rows) {
      try {
        // Use a transaction for each loan so one failure does not affect others
        await client.query('BEGIN');

        // Calculate interest using PostgreSQL NUMERIC arithmetic
        const interestResult = await client.query(
          `SELECT ($1::NUMERIC(12,2) * $2::NUMERIC(5,4) / 12) AS interest`,
          [loan.current_balance, loan.interest_rate]
        );
        const interest = interestResult.rows[0].interest;
        const newBalance = parseFloat(loan.current_balance) + parseFloat(interest);

        // Update the loan balance
        await client.query(
          `UPDATE loans SET current_balance = $1, updated_at = NOW() WHERE id = $2`,
          [newBalance, loan.id]
        );

        // Create a ledger entry
        await client.query(
          `INSERT INTO ledger_entries (loan_id, entry_type, amount, balance_after, description) VALUES ($1, 'INTEREST_ACCRUAL', $2, $3, $4)`,
          [loan.id, interest, newBalance, `Monthly interest for ${new Date().toISOString().slice(0, 7)}`]
        );

        // Create a monthly statement
        await client.query(
          `INSERT INTO monthly_statements (loan_id, statement_date, opening_balance, interest_charged, closing_balance) VALUES ($1, CURRENT_DATE, $2, $3, $4)`,
          [loan.id, loan.current_balance, interest, newBalance]
        );

        await client.query('COMMIT');
        processed++;
      } catch (err) {
        await client.query('ROLLBACK');
        errors.push(`Loan ${loan.id}: ${err.message}`);
      }
    }

    // Update job record with final status
    await client.query(
      `UPDATE job_runs SET status = $1, completed_at = NOW(), loans_processed = $2, errors = $3 WHERE id = $4`,
      [errors.length > 0 ? 'COMPLETED_WITH_ERRORS' : 'COMPLETED', processed, errors.length > 0 ? errors.join('; ') : null, jobId]
    );

    return { processed, errors: errors.length };
  } catch (err) {
    throw err;
  } finally {
    client.release();
  }
}

module.exports = { processMonthlyInterest };

What does this service do?

• It loops through every active loan and calculates monthly interest using the formula: interest = balance * (annualRate / 12).
• Each loan is wrapped in its own BEGIN/COMMIT transaction. If one loan fails, ROLLBACK ensures that loan stays untouched, but other loans still get processed.
• The interest calculation happens inside PostgreSQL using NUMERIC types instead of JavaScript floating-point math. This avoids rounding errors with money.
• It records the job run in the job_runs table so you can track when processing happened and whether it succeeded.

Why NUMERIC instead of JavaScript math?

In JavaScript, 0.1 + 0.2 equals 0.30000000000000004. That is a floating-point precision error. For a loan system handling thousands of dollars, those tiny errors compound over time. PostgreSQL's NUMERIC type stores exact decimal values, so $50,000 * 0.065 / 12 always produces the correct result.

Now update index.js to import all your new routes and add the dashboard stats endpoint plus the cron schedule.

• Replace the entire contents of server/src/index.js with this code:

const express = require('express');
const cors = require('cors');
const cron = require('node-cron');
const { pool } = require('./db');
const loanRoutes = require('./routes/loans');
const borrowerRoutes = require('./routes/borrowers');
const jobRoutes = require('./routes/jobs');
const { processMonthlyInterest } = require('./services/interestService');

const app = express();
const PORT = 3001;

// Middleware
app.use(cors());
app.use(express.json());

// Routes
app.use('/api/loans', loanRoutes);
app.use('/api/borrowers', borrowerRoutes);
app.use('/api/jobs', jobRoutes);

// Dashboard stats endpoint
app.get('/api/dashboard/stats', async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT 
        COUNT(*) FILTER (WHERE status = 'ACTIVE') AS active_loans,
        COALESCE(SUM(current_balance) FILTER (WHERE status = 'ACTIVE'), 0) AS total_portfolio,
        COALESCE(SUM(current_balance - principal_amount) FILTER (WHERE status = 'ACTIVE'), 0) AS total_interest_accrued
      FROM loans
    `);
    res.json(result.rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// Schedule monthly interest processing: 1st of every month at midnight
cron.schedule('0 0 1 * *', async () => {
  console.log('Running monthly interest processing...');
  await processMonthlyInterest();
});

app.listen(PORT, () => {
  console.log(`Loan Management API running on http://localhost:${PORT}`);
});

What is new in this version?

• Three route files are now mounted: loans, borrowers, and jobs.
• The /api/dashboard/stats endpoint uses PostgreSQL's FILTER clause to calculate stats in a single query instead of multiple round trips.
• cron.schedule('0 0 1 * *', ...) sets up node-cron to run the interest processing function on the 1st of every month at midnight. You will also add a manual trigger button in a later step.

• Save index.js. The dev server restarts automatically.
• Test the borrowers endpoint:

curl http://localhost:3001/api/borrowers

You should see a JSON array of borrower objects sorted alphabetically by last name.

• Test the dashboard stats endpoint:

curl http://localhost:3001/api/dashboard/stats

You should see a JSON object with active_loans, total_portfolio, and total_interest_accrued values.

Server crashing on restart?

• If you see a module-not-found error, check that all four files exist at the correct paths: routes/loans.js, routes/borrowers.js, routes/jobs.js, and services/interestService.js.
• If you see a syntax error, check for missing commas or mismatched brackets in index.js.

Help me fix my server crash

✔️ Awesome, I've got everything!

Great. Double check you have saved all four files before moving on.

ⓧ I'd like to double check the full code

Here is the full code for each file at this point in the project:

const express = require('express');
const cors = require('cors');
const cron = require('node-cron');
const { pool } = require('./db');
const loanRoutes = require('./routes/loans');
const borrowerRoutes = require('./routes/borrowers');
const jobRoutes = require('./routes/jobs');
const { processMonthlyInterest } = require('./services/interestService');

const app = express();
const PORT = 3001;

app.use(cors());
app.use(express.json());

app.use('/api/loans', loanRoutes);
app.use('/api/borrowers', borrowerRoutes);
app.use('/api/jobs', jobRoutes);

app.get('/api/dashboard/stats', async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT 
        COUNT(*) FILTER (WHERE status = 'ACTIVE') AS active_loans,
        COALESCE(SUM(current_balance) FILTER (WHERE status = 'ACTIVE'), 0) AS total_portfolio,
        COALESCE(SUM(current_balance - principal_amount) FILTER (WHERE status = 'ACTIVE'), 0) AS total_interest_accrued
      FROM loans
    `);
    res.json(result.rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

cron.schedule('0 0 1 * *', async () => {
  console.log('Running monthly interest processing...');
  await processMonthlyInterest();
});

app.listen(PORT, () => {
  console.log(`Loan Management API running on http://localhost:${PORT}`);
});

const express = require('express');
const router = express.Router();
const { pool } = require('../db');

router.get('/', async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT l.*, b.first_name, b.last_name, b.email
      FROM loans l
      JOIN borrowers b ON l.borrower_id = b.id
      ORDER BY l.created_at DESC
    `);
    res.json(result.rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.get('/:id', async (req, res) => {
  try {
    const result = await pool.query(
      `SELECT l.*, b.first_name, b.last_name, b.email
       FROM loans l JOIN borrowers b ON l.borrower_id = b.id
       WHERE l.id = $1`,
      [req.params.id]
    );
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'Loan not found' });
    }
    res.json(result.rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.post('/', async (req, res) => {
  const { borrower_id, principal_amount, interest_rate, term_months } = req.body;
  if (!borrower_id || !principal_amount || !interest_rate || !term_months) {
    return res.status(400).json({ error: 'Missing required fields' });
  }
  try {
    const result = await pool.query(
      `INSERT INTO loans (borrower_id, principal_amount, current_balance, interest_rate, term_months, start_date, status)
       VALUES ($1, $2, $2, $3, $4, CURRENT_DATE, 'ACTIVE')
       RETURNING *`,
      [borrower_id, principal_amount, interest_rate, term_months]
    );
    res.status(201).json(result.rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.patch('/:id', async (req, res) => {
  const allowedFields = ['interest_rate', 'term_months', 'status'];
  const updates = [];
  const values = [];
  let paramCount = 1;
  for (const [key, value] of Object.entries(req.body)) {
    if (allowedFields.includes(key)) {
      updates.push(`${key} = $${paramCount}`);
      values.push(value);
      paramCount++;
    }
  }
  if (updates.length === 0) {
    return res.status(400).json({ error: 'No valid fields to update' });
  }
  values.push(req.params.id);
  try {
    const result = await pool.query(
      `UPDATE loans SET ${updates.join(', ')}, updated_at = NOW() WHERE id = $${paramCount} RETURNING *`,
      values
    );
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'Loan not found' });
    }
    res.json(result.rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;

const express = require('express');
const router = express.Router();
const { pool } = require('../db');

router.get('/', async (req, res) => {
  try {
    const result = await pool.query('SELECT * FROM borrowers ORDER BY last_name ASC');
    res.json(result.rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;

const express = require('express');
const router = express.Router();

// Job endpoints will be added in a later step

module.exports = router;

const { pool } = require('../db');

async function processMonthlyInterest() {
  const client = await pool.connect();
  try {
    const jobResult = await client.query(
      `INSERT INTO job_runs (job_name, status, started_at) VALUES ('monthly_interest', 'RUNNING', NOW()) RETURNING id`
    );
    const jobId = jobResult.rows[0].id;
    const loansResult = await client.query(
      `SELECT id, current_balance, interest_rate FROM loans WHERE status = 'ACTIVE'`
    );
    let processed = 0;
    const errors = [];
    for (const loan of loansResult.rows) {
      try {
        await client.query('BEGIN');
        const interestResult = await client.query(
          `SELECT ($1::NUMERIC(12,2) * $2::NUMERIC(5,4) / 12) AS interest`,
          [loan.current_balance, loan.interest_rate]
        );
        const interest = interestResult.rows[0].interest;
        const newBalance = parseFloat(loan.current_balance) + parseFloat(interest);
        await client.query(
          `UPDATE loans SET current_balance = $1, updated_at = NOW() WHERE id = $2`,
          [newBalance, loan.id]
        );
        await client.query(
          `INSERT INTO ledger_entries (loan_id, entry_type, amount, balance_after, description) VALUES ($1, 'INTEREST_ACCRUAL', $2, $3, $4)`,
          [loan.id, interest, newBalance, `Monthly interest for ${new Date().toISOString().slice(0, 7)}`]
        );
        await client.query(
          `INSERT INTO monthly_statements (loan_id, statement_date, opening_balance, interest_charged, closing_balance) VALUES ($1, CURRENT_DATE, $2, $3, $4)`,
          [loan.id, loan.current_balance, interest, newBalance]
        );
        await client.query('COMMIT');
        processed++;
      } catch (err) {
        await client.query('ROLLBACK');
        errors.push(`Loan ${loan.id}: ${err.message}`);
      }
    }
    await client.query(
      `UPDATE job_runs SET status = $1, completed_at = NOW(), loans_processed = $2, errors = $3 WHERE id = $4`,
      [errors.length > 0 ? 'COMPLETED_WITH_ERRORS' : 'COMPLETED', processed, errors.length > 0 ? errors.join('; ') : null, jobId]
    );
    return { processed, errors: errors.length };
  } catch (err) {
    throw err;
  } finally {
    client.release();
  }
}

module.exports = { processMonthlyInterest };

Your API is live and serving data. Next up, you will build the frontend that calls these endpoints and displays the loan data in an interactive table.

Build the Frontend Data Table

Your API is running and returning loan data as JSON. Now you need a way for bank employees to actually see that data.

In this step, you will build the frontend layer of your 3-tier architecture. You will create an HTML page, style it with CSS, and write JavaScript that calls your API using fetch() and async/await. By the end, you will have a professional-looking data table that pulls live loan data from your Express server.

In this step, get ready to:

• Create the HTML structure with a dashboard header, filter controls, and data table.
• Build a dynamic table that renders loan data from the API with formatted currency and sortable columns.
• Add inline editing so status changes save directly to the database via your PATCH endpoint.

Create the HTML and CSS files

Every web app starts with HTML for structure and CSS for appearance. Your client/ folder is currently empty. You will create three files: one for the page layout, one for styling, and one for JavaScript logic.

• In VS Code, create a new file called index.html inside the client/ folder.
• Paste the following HTML into index.html:

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Loan Management System</title>
  <link rel="stylesheet" href="style.css">
</head>
<body>
  <header>
    <h1>Loan Management System</h1>
    <nav>
      <!-- Navigation buttons for switching between pages -->
      <button id="nav-dashboard" class="nav-btn active">Dashboard</button>
      <button id="nav-jobs" class="nav-btn">Job History</button>
    </nav>
  </header>

  <main>
    <!-- Dashboard summary cards -->
    <section id="dashboard-stats">
      <div class="stat-card"><h3>Active Loans</h3><p id="stat-active">--</p></div>
      <div class="stat-card"><h3>Total Portfolio</h3><p id="stat-portfolio">--</p></div>
      <div class="stat-card"><h3>Interest Accrued</h3><p id="stat-interest">--</p></div>
    </section>

    <!-- Search, filter, and action controls -->
    <section id="controls">
      <input type="text" id="search-input" placeholder="Search by borrower name...">
      <select id="status-filter">
        <option value="">All Statuses</option>
        <option value="ACTIVE">Active</option>
        <option value="PAID_OFF">Paid Off</option>
        <option value="DEFAULTED">Defaulted</option>
      </select>
      <button id="btn-process" class="btn-primary">Process Monthly Interest</button>
    </section>

    <!-- Loan data table with sortable column headers -->
    <section id="loan-table-container">
      <table id="loan-table">
        <thead>
          <tr>
            <th data-sort="first_name">Borrower</th>
            <th data-sort="principal_amount">Principal</th>
            <th data-sort="current_balance">Balance</th>
            <th data-sort="interest_rate">Rate</th>
            <th data-sort="term_months">Term</th>
            <th data-sort="status">Status</th>
            <th>Actions</th>
          </tr>
        </thead>
        <tbody id="loan-tbody"></tbody>
      </table>
    </section>
  </main>

  <!-- Load JavaScript last so the DOM is ready -->
  <script src="app.js"></script>
</body>
</html>

What does this HTML do?

The page has three main sections. The dashboard-stats section shows summary numbers (active loans, total portfolio value, interest accrued). The controls section provides a search box, status filter dropdown, and a button to trigger monthly processing.

The table uses data-sort attributes on each <th> element. Your JavaScript will read these attributes to know which column to sort by when the header is clicked.

• Create a new file called style.css inside the client/ folder.
• Paste the following CSS into style.css:

* {
  margin: 0;
  padding: 0;
  box-sizing: border-box;
}

body {
  font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif;
  background: #f5f5f5;
  color: #333;
}

/* Header and navigation */
header {
  background: #1a1a2e;
  color: white;
  padding: 1rem 2rem;
  display: flex;
  justify-content: space-between;
  align-items: center;
}

header h1 {
  font-size: 1.4rem;
}

.nav-btn {
  background: transparent;
  border: 1px solid rgba(255, 255, 255, 0.3);
  color: white;
  padding: 0.5rem 1rem;
  border-radius: 4px;
  cursor: pointer;
  margin-left: 0.5rem;
}

.nav-btn.active {
  background: rgba(255, 255, 255, 0.2);
  border-color: white;
}

/* Main content area */
main {
  max-width: 1200px;
  margin: 2rem auto;
  padding: 0 1rem;
}

/* Dashboard stat cards */
#dashboard-stats {
  display: grid;
  grid-template-columns: repeat(3, 1fr);
  gap: 1rem;
  margin-bottom: 1.5rem;
}

.stat-card {
  background: white;
  padding: 1.5rem;
  border-radius: 8px;
  box-shadow: 0 2px 4px rgba(0, 0, 0, 0.1);
}

.stat-card h3 {
  font-size: 0.85rem;
  color: #666;
  margin-bottom: 0.5rem;
}

.stat-card p {
  font-size: 1.5rem;
  font-weight: 700;
  color: #1a1a2e;
}

/* Controls section */
#controls {
  display: flex;
  gap: 1rem;
  margin-bottom: 1.5rem;
  align-items: center;
}

#search-input {
  padding: 0.6rem 1rem;
  border: 1px solid #ddd;
  border-radius: 4px;
  flex: 1;
  max-width: 300px;
}

#status-filter {
  padding: 0.6rem 1rem;
  border: 1px solid #ddd;
  border-radius: 4px;
}

.btn-primary {
  background: #2d6a4f;
  color: white;
  border: none;
  padding: 0.6rem 1.2rem;
  border-radius: 4px;
  cursor: pointer;
  margin-left: auto;
}

.btn-primary:disabled {
  opacity: 0.6;
  cursor: not-allowed;
}

/* Loan data table */
#loan-table-container {
  background: white;
  border-radius: 8px;
  box-shadow: 0 2px 4px rgba(0, 0, 0, 0.1);
  overflow-x: auto;
}

#loan-table {
  width: 100%;
  border-collapse: collapse;
}

#loan-table th {
  background: #f8f9fa;
  padding: 0.75rem 1rem;
  text-align: left;
  font-size: 0.85rem;
  color: #555;
  cursor: pointer;
  border-bottom: 2px solid #e9ecef;
}

#loan-table td {
  padding: 0.75rem 1rem;
  border-bottom: 1px solid #e9ecef;
}

/* Editable cells */
.editable {
  cursor: pointer;
}

.editable:hover {
  background: #e8f4fd;
}

/* Status dropdown and action button */
.status-select {
  padding: 0.3rem;
  border-radius: 4px;
  border: 1px solid #ddd;
}

.btn-schedule {
  background: #eee;
  border: 1px solid #ddd;
  padding: 0.3rem 0.8rem;
  border-radius: 4px;
  cursor: pointer;
}

/* Modal (used later for amortization) */
.modal {
  position: fixed;
  top: 0;
  left: 0;
  width: 100%;
  height: 100%;
  background: rgba(0, 0, 0, 0.5);
  display: flex;
  align-items: center;
  justify-content: center;
}

.modal-content {
  background: white;
  padding: 2rem;
  border-radius: 8px;
  max-height: 80vh;
  overflow-y: auto;
  max-width: 800px;
  width: 90%;
}

.btn-close {
  margin-top: 1rem;
  padding: 0.5rem 1rem;
  background: #dc3545;
  color: white;
  border: none;
  border-radius: 4px;
  cursor: pointer;
}

What does this CSS do?

The stylesheet uses CSS Grid for the stat cards, Flexbox for the controls row, and a clean table layout with hover effects on editable cells. The .btn-primary:disabled rule grays out the process button while the monthly job is running.

The .modal styles are included now but will only be used later (in the Secret Mission) when you add the amortization schedule popup.

• Save both index.html and style.css.
• Open index.html in your browser by double-clicking the file in Finder or dragging it into a browser tab.
• You should see a dark header with "Loan Management System", three stat cards showing --, a search bar, and an empty table with column headers.

Page looks unstyled or blank?

Make sure style.css is in the same folder as index.html (both inside client/). The <link> tag in your HTML references href="style.css" as a relative path, so both files must be siblings.

Help me figure out why my HTML page is not loading styles.

Build the JavaScript with fetch and table rendering

Your HTML is just a static skeleton right now. JavaScript brings it to life by calling your API, receiving JSON data, and inserting rows into the table. This is where you will learn async/await for the first time.

• Create a new file called app.js inside the client/ folder.
• Add the constants, state variables, and utility functions at the top of app.js:

const API_URL = 'http://localhost:3001/api';

// State: holds all loans and current sort settings
let allLoans = [];
let sortColumn = 'current_balance';
let sortDirection = 'desc';

// --- Utility Functions ---

// Format a number as US currency (e.g. 5000 -> $5,000.00)
function formatCurrency(amount) {
  return new Intl.NumberFormat('en-US', { style: 'currency', currency: 'USD' }).format(Number(amount));
}

// Format a decimal rate as a percentage (e.g. 0.065 -> 6.50%)
function formatPercent(rate) {
  return (Number(rate) * 100).toFixed(2) + '%';
}

What does this code do?

API_URL stores the base address of your Express server so you do not repeat it in every fetch call. The three let variables track the loan data array and current sort state.

formatCurrency uses the browser's built-in Intl.NumberFormat API to turn raw numbers like 50000 into $50,000.00. This is how real financial apps format money without writing your own comma-insertion logic.

• Below the utilities, add the two async functions that fetch data from your API:

// --- API Functions (using fetch + async/await) ---

// Fetch all loans from the server and re-render the table
async function fetchLoans() {
  const response = await fetch(`${API_URL}/loans`);
  if (!response.ok) throw new Error('Failed to fetch loans');
  allLoans = await response.json();
  renderTable();
}

// Fetch dashboard statistics and update the stat cards
async function fetchStats() {
  const response = await fetch(`${API_URL}/dashboard/stats`);
  if (!response.ok) throw new Error('Failed to fetch stats');
  const stats = await response.json();
  document.getElementById('stat-active').textContent = stats.active_loans;
  document.getElementById('stat-portfolio').textContent = formatCurrency(stats.total_portfolio);
  document.getElementById('stat-interest').textContent = formatCurrency(stats.total_interest_accrued);
}

What is async/await?

When your JavaScript calls fetch(), it sends an HTTP request to the server. The response takes time to come back (even on localhost, it is not instant). The await keyword pauses the function until the response arrives, without freezing the entire page.

Any function that uses await must be marked async. Think of it as: "this function does something that takes time, so it returns a Promise." The response.json() call also needs await because parsing the body is another asynchronous step.

• Below the fetch functions, add the renderTable function that filters, sorts, and displays loan data:

// --- Rendering ---

// Filter, sort, and render loans into the HTML table
function renderTable() {
  const searchTerm = document.getElementById('search-input').value.toLowerCase();
  const statusFilter = document.getElementById('status-filter').value;

  // Filter loans by search text and status dropdown
  let filtered = allLoans.filter((loan) => {
    const nameMatch = `${loan.first_name} ${loan.last_name}`.toLowerCase().includes(searchTerm);
    const statusMatch = !statusFilter || loan.status === statusFilter;
    return nameMatch && statusMatch;
  });

  // Sort filtered results by the selected column
  filtered.sort((a, b) => {
    let valA = a[sortColumn];
    let valB = b[sortColumn];
    if (!isNaN(Number(valA))) { valA = Number(valA); valB = Number(valB); }
    if (valA < valB) return sortDirection === 'asc' ? -1 : 1;
    if (valA > valB) return sortDirection === 'asc' ? 1 : -1;
    return 0;
  });

  // Build table rows with formatted values and inline controls
  const tbody = document.getElementById('loan-tbody');
  tbody.innerHTML = filtered.map((loan) => `
    <tr>
      <td>${loan.first_name} ${loan.last_name}</td>
      <td>${formatCurrency(loan.principal_amount)}</td>
      <td>${formatCurrency(loan.current_balance)}</td>
      <td class="editable" data-id="${loan.id}" data-field="interest_rate">${formatPercent(loan.interest_rate)}</td>
      <td class="editable" data-id="${loan.id}" data-field="term_months">${loan.term_months} mo</td>
      <td>
        <select class="status-select" data-id="${loan.id}">
          <option value="ACTIVE" ${loan.status === 'ACTIVE' ? 'selected' : ''}>Active</option>
          <option value="PAID_OFF" ${loan.status === 'PAID_OFF' ? 'selected' : ''}>Paid Off</option>
          <option value="DEFAULTED" ${loan.status === 'DEFAULTED' ? 'selected' : ''}>Defaulted</option>
        </select>
      </td>
      <td><button class="btn-schedule" data-id="${loan.id}">Schedule</button></td>
    </tr>
  `).join('');

  // Attach change listeners to each status dropdown
  document.querySelectorAll('.status-select').forEach((select) => {
    select.addEventListener('change', (e) => { updateLoan(e.target.dataset.id, 'status', e.target.value); });
  });

  // Attach click listeners to Schedule buttons (used in Secret Mission)
  document.querySelectorAll('.btn-schedule').forEach((btn) => {
    btn.addEventListener('click', (e) => { viewAmortization(e.target.dataset.id); });
  });
}

What does renderTable do?

This single function handles three jobs. First, it reads the current search text and status filter to narrow down which loans appear. Second, it sorts the filtered array by whichever column the user last clicked. Third, it builds HTML strings using template literals and sets tbody.innerHTML to replace the table body.

The status column renders a <select> dropdown for each loan. When the user picks a new status, the change event fires and calls updateLoan (which you will add next) to save the change to the database.

• Below renderTable, add the sort click handlers, event listeners, and initialization calls:

// --- Sorting ---

// When a column header is clicked, toggle sort direction or switch columns
document.querySelectorAll('th[data-sort]').forEach((th) => {
  th.addEventListener('click', () => {
    const column = th.dataset.sort;
    if (sortColumn === column) {
      sortDirection = sortDirection === 'asc' ? 'desc' : 'asc';
    } else {
      sortColumn = column;
      sortDirection = 'asc';
    }
    renderTable();
  });
});

// --- Event Listeners ---
document.getElementById('search-input').addEventListener('input', renderTable);
document.getElementById('status-filter').addEventListener('change', renderTable);
document.getElementById('btn-process').addEventListener('click', runMonthlyProcessing);

// --- Initialize ---
fetchLoans();
fetchStats();

What does this code do?

The sort handler reads the data-sort attribute from the clicked header. If you click the same column twice, it flips between ascending and descending. If you click a different column, it switches to that column in ascending order.

The last two lines (fetchLoans() and fetchStats()) run immediately when the page loads. They call your API and populate the table and stat cards automatically.

• Save app.js.
• Make sure your server is still running in the terminal (npm run dev from the server/ folder).
• Refresh index.html in your browser. You should see the stat cards populate with numbers and the table fill with loan rows showing formatted dollar amounts.

Table is empty or stat cards still show --?

Open the browser console (Cmd+Option+J on macOS or Ctrl+Shift+J on Windows). Look for errors. A Failed to fetch error means your Express server is not running on port 3001. Return to your terminal and start it with npm run dev from inside the server/ folder.

If you see a CORS error, make sure the cors() middleware is applied in server/src/index.js before the route definitions.

Help me debug why my frontend is not loading data from the API.

Add inline editing and monthly processing

The table renders data, but users cannot change anything yet. The status dropdown is visible but does not save. You need two more functions: one that sends a PATCH request when a field changes, and one that triggers monthly interest processing.

• In app.js, add the updateLoan function below fetchStats:

// Send a PATCH request to update one field on a loan
async function updateLoan(id, field, value) {
  const response = await fetch(`${API_URL}/loans/${id}`, {
    method: 'PATCH',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({ [field]: value }),
  });
  if (!response.ok) throw new Error('Failed to update loan');
  // Re-fetch everything so the table and stats reflect the change
  await fetchLoans();
  await fetchStats();
}

What does this code do?

updateLoan sends an HTTP PATCH request to your /api/loans/:id endpoint. The body: JSON.stringify({ [field]: value }) uses a computed property name, so calling updateLoan(5, 'status', 'PAID_OFF') sends {"status": "PAID_OFF"} as JSON.

After the update succeeds, it re-fetches all loans and stats. This keeps the table and dashboard perfectly in sync with the database.

• Below updateLoan, add the runMonthlyProcessing function:

// Trigger the monthly interest job manually via the API
async function runMonthlyProcessing() {
  const btn = document.getElementById('btn-process');
  btn.disabled = true;
  btn.textContent = 'Processing...';
  try {
    const response = await fetch(`${API_URL}/jobs/run-monthly`, { method: 'POST' });
    const result = await response.json();
    alert(`Processing complete! ${result.processed} loans processed.`);
    await fetchLoans();
    await fetchStats();
  } catch (err) {
    alert('Error: ' + err.message);
  } finally {
    btn.disabled = false;
    btn.textContent = 'Process Monthly Interest';
  }
}

What does this code do?

This function disables the button and changes its text to "Processing..." so the user knows something is happening. The finally block always re-enables the button, whether the request succeeded or failed.

The endpoint POST /api/jobs/run-monthly triggers the same interest calculation logic that the cron job runs automatically on the 1st of each month. This lets you test it without waiting 30 days.

• Save app.js.
• Refresh the page in your browser. Change a loan's status using the dropdown (for example, switch one from Active to Paid Off). The table should briefly flicker as it re-renders with updated data.
• Click the Process Monthly Interest button. You should see an alert showing how many loans were processed.

Status change does nothing or throws an error?

Check the browser console for errors. If you see updateLoan is not defined, make sure you placed the updateLoan function above the renderTable function in app.js (or anywhere before the initialization calls). JavaScript hoists function declarations, so order only matters if you use arrow functions.

If the button says "Processing..." but stays stuck, your server may not have the /api/jobs/run-monthly endpoint yet. This endpoint is built in Step 6. For now, the button will show an error alert, which is expected.

Help me fix my inline editing in the loan table.

✔️ Awesome, I've got everything!

Great. Double check you have saved all three files in the client/ folder: index.html, style.css, and app.js.

ⓧ I'd like to double check the full code

Here is the complete client/app.js file. Compare it against yours to find any differences:

const API_URL = 'http://localhost:3001/api';

let allLoans = [];
let sortColumn = 'current_balance';
let sortDirection = 'desc';

function formatCurrency(amount) {
  return new Intl.NumberFormat('en-US', { style: 'currency', currency: 'USD' }).format(Number(amount));
}

function formatPercent(rate) {
  return (Number(rate) * 100).toFixed(2) + '%';
}

async function fetchLoans() {
  const response = await fetch(`${API_URL}/loans`);
  if (!response.ok) throw new Error('Failed to fetch loans');
  allLoans = await response.json();
  renderTable();
}

async function fetchStats() {
  const response = await fetch(`${API_URL}/dashboard/stats`);
  if (!response.ok) throw new Error('Failed to fetch stats');
  const stats = await response.json();
  document.getElementById('stat-active').textContent = stats.active_loans;
  document.getElementById('stat-portfolio').textContent = formatCurrency(stats.total_portfolio);
  document.getElementById('stat-interest').textContent = formatCurrency(stats.total_interest_accrued);
}

async function updateLoan(id, field, value) {
  const response = await fetch(`${API_URL}/loans/${id}`, {
    method: 'PATCH',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({ [field]: value }),
  });
  if (!response.ok) throw new Error('Failed to update loan');
  await fetchLoans();
  await fetchStats();
}

async function runMonthlyProcessing() {
  const btn = document.getElementById('btn-process');
  btn.disabled = true;
  btn.textContent = 'Processing...';
  try {
    const response = await fetch(`${API_URL}/jobs/run-monthly`, { method: 'POST' });
    const result = await response.json();
    alert(`Processing complete! ${result.processed} loans processed.`);
    await fetchLoans();
    await fetchStats();
  } catch (err) {
    alert('Error: ' + err.message);
  } finally {
    btn.disabled = false;
    btn.textContent = 'Process Monthly Interest';
  }
}

function renderTable() {
  const searchTerm = document.getElementById('search-input').value.toLowerCase();
  const statusFilter = document.getElementById('status-filter').value;
  let filtered = allLoans.filter((loan) => {
    const nameMatch = `${loan.first_name} ${loan.last_name}`.toLowerCase().includes(searchTerm);
    const statusMatch = !statusFilter || loan.status === statusFilter;
    return nameMatch && statusMatch;
  });
  filtered.sort((a, b) => {
    let valA = a[sortColumn];
    let valB = b[sortColumn];
    if (!isNaN(Number(valA))) { valA = Number(valA); valB = Number(valB); }
    if (valA < valB) return sortDirection === 'asc' ? -1 : 1;
    if (valA > valB) return sortDirection === 'asc' ? 1 : -1;
    return 0;
  });
  const tbody = document.getElementById('loan-tbody');
  tbody.innerHTML = filtered.map((loan) => `
    <tr>
      <td>${loan.first_name} ${loan.last_name}</td>
      <td>${formatCurrency(loan.principal_amount)}</td>
      <td>${formatCurrency(loan.current_balance)}</td>
      <td class="editable" data-id="${loan.id}" data-field="interest_rate">${formatPercent(loan.interest_rate)}</td>
      <td class="editable" data-id="${loan.id}" data-field="term_months">${loan.term_months} mo</td>
      <td>
        <select class="status-select" data-id="${loan.id}">
          <option value="ACTIVE" ${loan.status === 'ACTIVE' ? 'selected' : ''}>Active</option>
          <option value="PAID_OFF" ${loan.status === 'PAID_OFF' ? 'selected' : ''}>Paid Off</option>
          <option value="DEFAULTED" ${loan.status === 'DEFAULTED' ? 'selected' : ''}>Defaulted</option>
        </select>
      </td>
      <td><button class="btn-schedule" data-id="${loan.id}">Schedule</button></td>
    </tr>
  `).join('');
  document.querySelectorAll('.status-select').forEach((select) => {
    select.addEventListener('change', (e) => { updateLoan(e.target.dataset.id, 'status', e.target.value); });
  });
  document.querySelectorAll('.btn-schedule').forEach((btn) => {
    btn.addEventListener('click', (e) => { viewAmortization(e.target.dataset.id); });
  });
}

document.querySelectorAll('th[data-sort]').forEach((th) => {
  th.addEventListener('click', () => {
    const column = th.dataset.sort;
    if (sortColumn === column) {
      sortDirection = sortDirection === 'asc' ? 'desc' : 'asc';
    } else {
      sortColumn = column;
      sortDirection = 'asc';
    }
    renderTable();
  });
});

document.getElementById('search-input').addEventListener('input', renderTable);
document.getElementById('status-filter').addEventListener('change', renderTable);
document.getElementById('btn-process').addEventListener('click', runMonthlyProcessing);

fetchLoans();
fetchStats();

And here is the complete client/index.html:

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Loan Management System</title>
  <link rel="stylesheet" href="style.css">
</head>
<body>
  <header>
    <h1>Loan Management System</h1>
    <nav>
      <button id="nav-dashboard" class="nav-btn active">Dashboard</button>
      <button id="nav-jobs" class="nav-btn">Job History</button>
    </nav>
  </header>
  <main>
    <section id="dashboard-stats">
      <div class="stat-card"><h3>Active Loans</h3><p id="stat-active">--</p></div>
      <div class="stat-card"><h3>Total Portfolio</h3><p id="stat-portfolio">--</p></div>
      <div class="stat-card"><h3>Interest Accrued</h3><p id="stat-interest">--</p></div>
    </section>
    <section id="controls">
      <input type="text" id="search-input" placeholder="Search by borrower name...">
      <select id="status-filter">
        <option value="">All Statuses</option>
        <option value="ACTIVE">Active</option>
        <option value="PAID_OFF">Paid Off</option>
        <option value="DEFAULTED">Defaulted</option>
      </select>
      <button id="btn-process" class="btn-primary">Process Monthly Interest</button>
    </section>
    <section id="loan-table-container">
      <table id="loan-table">
        <thead>
          <tr>
            <th data-sort="first_name">Borrower</th>
            <th data-sort="principal_amount">Principal</th>
            <th data-sort="current_balance">Balance</th>
            <th data-sort="interest_rate">Rate</th>
            <th data-sort="term_months">Term</th>
            <th data-sort="status">Status</th>
            <th>Actions</th>
          </tr>
        </thead>
        <tbody id="loan-tbody"></tbody>
      </table>
    </section>
  </main>
  <script src="app.js"></script>
</body>
</html>

Your frontend is now connected to your API and displaying live data. In the next step, you will add filtering, search, and a dashboard header that summarizes the entire loan portfolio at a glance.

Implement the Cron Job and Job History

Your loan management system can now display loans, update them, and calculate interest on demand. The missing piece is automation. In a real bank, interest compounds on a fixed schedule without anyone pressing a button.

In this step, you will complete the automated processing pipeline. You already have the cron schedule wired up in index.js and the interest service ready to go. Now you will build the API endpoints that let you trigger processing manually and inspect past job runs, plus a frontend page that displays the processing log.

In this step, get ready to:

• Complete the job routes with a manual trigger and a history endpoint.
• Add a Job History section to the frontend HTML.
• Wire up the JavaScript to fetch, render, and navigate between views.

Complete the jobs route file

The cron schedule in server/src/index.js already calls processMonthlyInterest() on the 1st of every month at midnight. But waiting a month to test is impractical. You need a manual trigger endpoint and a history endpoint so you can run it on demand and see what happened.

What is a cron expression?

The string '0 0 1 * *' is a cron expression. Each position represents: minute, hour, day-of-month, month, day-of-week.

So 0 0 1 * * means "at minute 0, hour 0 (midnight), day 1, every month, any day of the week." The result: it fires once on the 1st of every month.

• In VS Code, open server/src/routes/jobs.js.
• Replace the entire contents of the file with this code:

const express = require('express');
const router = express.Router();
const { pool } = require('../db');
const { processMonthlyInterest } = require('../services/interestService');

// POST /api/jobs/run-monthly - manually trigger interest processing
router.post('/run-monthly', async (req, res) => {
  try {
    const result = await processMonthlyInterest();
    res.json(result);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /api/jobs/history - fetch recent job runs
router.get('/history', async (req, res) => {
  try {
    const result = await pool.query(
      `SELECT * FROM job_runs ORDER BY started_at DESC LIMIT 50`
    );
    res.json(result.rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;

What does this code do?

• POST /run-monthly calls the same processMonthlyInterest() function the cron uses. This lets you test without waiting until the 1st of the month.
• GET /history queries the job_runs table (which processMonthlyInterest inserts into) and returns the 50 most recent runs, newest first.

• Save server/src/routes/jobs.js.
• Your server should auto-restart (if you are running npm run dev). In the browser, click the Process Monthly Interest button on your dashboard.
• You should see an alert saying something like "Processing complete! 50 loans processed."

Button does nothing or shows an error?

• Check that your server restarted without errors in the terminal. A syntax error in jobs.js will crash the server.
• Verify the import path matches exactly: ../services/interestService. A wrong path causes a module-not-found crash on startup.
• Make sure PostgreSQL is still running. If it stopped, the database query will fail.

Help me debug my jobs route endpoint.

Add the Job History HTML section

Now that the server tracks job runs, you need a place to display them. You will add a hidden section to index.html that becomes visible when the learner clicks the Job History nav button.

• In client/index.html, find the closing </section> tag of loan-table-container.
• Add this new section directly below it (before the closing </main> tag):

    <section id="job-history-container" style="display:none">
      <table id="job-table">
        <thead>
          <tr><th>Job</th><th>Status</th><th>Started</th><th>Completed</th><th>Loans Processed</th><th>Errors</th></tr>
        </thead>
        <tbody id="job-tbody"></tbody>
      </table>
    </section>

What does this code do?

• style="display:none" hides this section by default. The JavaScript will toggle its visibility when the nav button is clicked.
• The job-tbody element is where your JavaScript will inject rows showing each job run's details.

• Save client/index.html.
• Refresh your browser. The page should look unchanged because the new section is hidden.

Wire up the Job History JavaScript

The last piece is the JavaScript that fetches job history from the API, renders it into the table, and toggles between the Dashboard and Job History views when you click the nav buttons.

• In client/app.js, find the runMonthlyProcessing function. Add the following two functions directly below it:

// Fetch job run history from the API
async function fetchJobHistory() {
  const response = await fetch(`${API_URL}/jobs/history`);
  if (!response.ok) throw new Error('Failed to fetch job history');
  const jobs = await response.json();
  renderJobHistory(jobs);
}

// Render job runs into the history table
function renderJobHistory(jobs) {
  const tbody = document.getElementById('job-tbody');
  tbody.innerHTML = jobs.map((job) => `
    <tr>
      <td>${job.job_name}</td>
      <td>${job.status}</td>
      <td>${new Date(job.started_at).toLocaleString()}</td>
      <td>${job.completed_at ? new Date(job.completed_at).toLocaleString() : '--'}</td>
      <td>${job.loans_processed ?? '--'}</td>
      <td>${job.errors ?? ''}</td>
    </tr>
  `).join('');
}

What does this code do?

• fetchJobHistory() calls GET /api/jobs/history and passes the result to the render function. This is the same async/await pattern you used for fetchLoans().
• renderJobHistory() builds table rows from the job data. The ?? operator (nullish coalescing) shows a fallback value when a field is null (like when a job is still running and has no completed_at yet).

Now you need the navigation logic that toggles between views. The nav buttons already exist in the HTML, so you just need event listeners.

• In client/app.js, find the line that says:

document.getElementById('search-input').addEventListener('input', renderTable);

• Add the following navigation listeners directly above that line:

// Navigation: toggle between Dashboard and Job History views
document.getElementById('nav-dashboard').addEventListener('click', () => {
  document.getElementById('loan-table-container').style.display = '';
  document.getElementById('dashboard-stats').style.display = '';
  document.getElementById('controls').style.display = '';
  document.getElementById('job-history-container').style.display = 'none';
  document.getElementById('nav-dashboard').classList.add('active');
  document.getElementById('nav-jobs').classList.remove('active');
});

document.getElementById('nav-jobs').addEventListener('click', () => {
  document.getElementById('loan-table-container').style.display = 'none';
  document.getElementById('dashboard-stats').style.display = 'none';
  document.getElementById('controls').style.display = 'none';
  document.getElementById('job-history-container').style.display = '';
  document.getElementById('nav-jobs').classList.add('active');
  document.getElementById('nav-dashboard').classList.remove('active');
  fetchJobHistory();
});

What does this code do?

• Clicking Dashboard shows the stats, controls, and loan table while hiding the job history.
• Clicking Job History hides the dashboard sections and shows the job history table. It also calls fetchJobHistory() to load fresh data each time you switch.
• The classList.add('active') and classList.remove('active') calls visually highlight which nav button is currently selected.

• Save client/app.js.
• Refresh your browser. Click the Process Monthly Interest button first to create a job run record.
• Then click the Job History nav button. You should see a table with one row showing the job name, a COMPLETED status, timestamps, and the number of loans processed.

Job History table is empty?

• Make sure you clicked Process Monthly Interest at least once before switching to Job History. The table only shows runs that have already happened.
• Open DevTools (Cmd+Option+I on macOS, Ctrl+Shift+I on Windows) and check the Console tab for fetch errors. A 500 response means the job_runs table might not exist in your database.
• Confirm the element ID job-tbody in your HTML matches what renderJobHistory references in JavaScript. A typo means the rows render nowhere.

Help me debug my Job History page.

Why node-cron instead of BullMQ?

BullMQ is a popular job queue that supports retries, distributed workers, and persistence. It requires a separate Redis server to function.

For a single-instance learning project, node-cron is two lines of code and zero extra infrastructure. It clearly demonstrates the scheduling concept. In an interview, saying "I used a cron expression to schedule monthly processing" is perfectly valid for an intern-level system.

✔️ Awesome, I've got everything!

Great. Double check you have saved all three files: server/src/routes/jobs.js, client/index.html, and client/app.js.

ⓧ I'd like to double check the full code

Here is the full code for each file you changed in this step.

const express = require('express');
const router = express.Router();
const { pool } = require('../db');
const { processMonthlyInterest } = require('../services/interestService');

router.post('/run-monthly', async (req, res) => {
  try {
    const result = await processMonthlyInterest();
    res.json(result);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.get('/history', async (req, res) => {
  try {
    const result = await pool.query(
      `SELECT * FROM job_runs ORDER BY started_at DESC LIMIT 50`
    );
    res.json(result.rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Loan Management System</title>
  <link rel="stylesheet" href="style.css">
</head>
<body>
  <header>
    <h1>Loan Management System</h1>
    <nav>
      <button id="nav-dashboard" class="nav-btn active">Dashboard</button>
      <button id="nav-jobs" class="nav-btn">Job History</button>
    </nav>
  </header>
  <main>
    <section id="dashboard-stats">
      <div class="stat-card"><h3>Active Loans</h3><p id="stat-active">--</p></div>
      <div class="stat-card"><h3>Total Portfolio</h3><p id="stat-portfolio">--</p></div>
      <div class="stat-card"><h3>Interest Accrued</h3><p id="stat-interest">--</p></div>
    </section>
    <section id="controls">
      <input type="text" id="search-input" placeholder="Search by borrower name...">
      <select id="status-filter">
        <option value="">All Statuses</option>
        <option value="ACTIVE">Active</option>
        <option value="PAID_OFF">Paid Off</option>
        <option value="DEFAULTED">Defaulted</option>
      </select>
      <button id="btn-process" class="btn-primary">Process Monthly Interest</button>
    </section>
    <section id="loan-table-container">
      <table id="loan-table">
        <thead>
          <tr>
            <th data-sort="first_name">Borrower</th>
            <th data-sort="principal_amount">Principal</th>
            <th data-sort="current_balance">Balance</th>
            <th data-sort="interest_rate">Rate</th>
            <th data-sort="term_months">Term</th>
            <th data-sort="status">Status</th>
            <th>Actions</th>
          </tr>
        </thead>
        <tbody id="loan-tbody"></tbody>
      </table>
    </section>
    <section id="job-history-container" style="display:none">
      <table id="job-table">
        <thead>
          <tr><th>Job</th><th>Status</th><th>Started</th><th>Completed</th><th>Loans Processed</th><th>Errors</th></tr>
        </thead>
        <tbody id="job-tbody"></tbody>
      </table>
    </section>
  </main>
  <script src="app.js"></script>
</body>
</html>

const API_URL = 'http://localhost:3001/api';

let allLoans = [];
let sortColumn = 'current_balance';
let sortDirection = 'desc';

function formatCurrency(amount) {
  return new Intl.NumberFormat('en-US', { style: 'currency', currency: 'USD' }).format(Number(amount));
}

function formatPercent(rate) {
  return (Number(rate) * 100).toFixed(2) + '%';
}

async function fetchLoans() {
  const response = await fetch(`${API_URL}/loans`);
  if (!response.ok) throw new Error('Failed to fetch loans');
  allLoans = await response.json();
  renderTable();
}

async function fetchStats() {
  const response = await fetch(`${API_URL}/dashboard/stats`);
  if (!response.ok) throw new Error('Failed to fetch stats');
  const stats = await response.json();
  document.getElementById('stat-active').textContent = stats.active_loans;
  document.getElementById('stat-portfolio').textContent = formatCurrency(stats.total_portfolio);
  document.getElementById('stat-interest').textContent = formatCurrency(stats.total_interest_accrued);
}

async function updateLoan(id, field, value) {
  const response = await fetch(`${API_URL}/loans/${id}`, {
    method: 'PATCH',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({ [field]: value }),
  });
  if (!response.ok) throw new Error('Failed to update loan');
  await fetchLoans();
  await fetchStats();
}

async function runMonthlyProcessing() {
  const btn = document.getElementById('btn-process');
  btn.disabled = true;
  btn.textContent = 'Processing...';
  try {
    const response = await fetch(`${API_URL}/jobs/run-monthly`, { method: 'POST' });
    const result = await response.json();
    alert(`Processing complete! ${result.processed} loans processed.`);
    await fetchLoans();
    await fetchStats();
  } catch (err) {
    alert('Error: ' + err.message);
  } finally {
    btn.disabled = false;
    btn.textContent = 'Process Monthly Interest';
  }
}

async function fetchJobHistory() {
  const response = await fetch(`${API_URL}/jobs/history`);
  if (!response.ok) throw new Error('Failed to fetch job history');
  const jobs = await response.json();
  renderJobHistory(jobs);
}

function renderJobHistory(jobs) {
  const tbody = document.getElementById('job-tbody');
  tbody.innerHTML = jobs.map((job) => `
    <tr>
      <td>${job.job_name}</td>
      <td>${job.status}</td>
      <td>${new Date(job.started_at).toLocaleString()}</td>
      <td>${job.completed_at ? new Date(job.completed_at).toLocaleString() : '--'}</td>
      <td>${job.loans_processed ?? '--'}</td>
      <td>${job.errors ?? ''}</td>
    </tr>
  `).join('');
}

function renderTable() {
  const searchTerm = document.getElementById('search-input').value.toLowerCase();
  const statusFilter = document.getElementById('status-filter').value;
  let filtered = allLoans.filter((loan) => {
    const nameMatch = `${loan.first_name} ${loan.last_name}`.toLowerCase().includes(searchTerm);
    const statusMatch = !statusFilter || loan.status === statusFilter;
    return nameMatch && statusMatch;
  });
  filtered.sort((a, b) => {
    let valA = a[sortColumn];
    let valB = b[sortColumn];
    if (!isNaN(Number(valA))) { valA = Number(valA); valB = Number(valB); }
    if (valA < valB) return sortDirection === 'asc' ? -1 : 1;
    if (valA > valB) return sortDirection === 'asc' ? 1 : -1;
    return 0;
  });
  const tbody = document.getElementById('loan-tbody');
  tbody.innerHTML = filtered.map((loan) => `
    <tr>
      <td>${loan.first_name} ${loan.last_name}</td>
      <td>${formatCurrency(loan.principal_amount)}</td>
      <td>${formatCurrency(loan.current_balance)}</td>
      <td class="editable" data-id="${loan.id}" data-field="interest_rate">${formatPercent(loan.interest_rate)}</td>
      <td class="editable" data-id="${loan.id}" data-field="term_months">${loan.term_months} mo</td>
      <td>
        <select class="status-select" data-id="${loan.id}">
          <option value="ACTIVE" ${loan.status === 'ACTIVE' ? 'selected' : ''}>Active</option>
          <option value="PAID_OFF" ${loan.status === 'PAID_OFF' ? 'selected' : ''}>Paid Off</option>
          <option value="DEFAULTED" ${loan.status === 'DEFAULTED' ? 'selected' : ''}>Defaulted</option>
        </select>
      </td>
      <td><button class="btn-schedule" data-id="${loan.id}">Schedule</button></td>
    </tr>
  `).join('');
  document.querySelectorAll('.status-select').forEach((select) => {
    select.addEventListener('change', (e) => { updateLoan(e.target.dataset.id, 'status', e.target.value); });
  });
  document.querySelectorAll('.btn-schedule').forEach((btn) => {
    btn.addEventListener('click', (e) => { viewAmortization(e.target.dataset.id); });
  });
}

document.querySelectorAll('th[data-sort]').forEach((th) => {
  th.addEventListener('click', () => {
    const column = th.dataset.sort;
    if (sortColumn === column) {
      sortDirection = sortDirection === 'asc' ? 'desc' : 'asc';
    } else {
      sortColumn = column;
      sortDirection = 'asc';
    }
    renderTable();
  });
});

document.getElementById('nav-dashboard').addEventListener('click', () => {
  document.getElementById('loan-table-container').style.display = '';
  document.getElementById('dashboard-stats').style.display = '';
  document.getElementById('controls').style.display = '';
  document.getElementById('job-history-container').style.display = 'none';
  document.getElementById('nav-dashboard').classList.add('active');
  document.getElementById('nav-jobs').classList.remove('active');
});

document.getElementById('nav-jobs').addEventListener('click', () => {
  document.getElementById('loan-table-container').style.display = 'none';
  document.getElementById('dashboard-stats').style.display = 'none';
  document.getElementById('controls').style.display = 'none';
  document.getElementById('job-history-container').style.display = '';
  document.getElementById('nav-jobs').classList.add('active');
  document.getElementById('nav-dashboard').classList.remove('active');
  fetchJobHistory();
});

document.getElementById('search-input').addEventListener('input', renderTable);
document.getElementById('status-filter').addEventListener('change', renderTable);
document.getElementById('btn-process').addEventListener('click', runMonthlyProcessing);

fetchLoans();
fetchStats();

Your system now processes interest automatically on schedule and keeps a full audit trail of every run. This is the kind of operational visibility that separates a toy project from something you could actually deploy. Next up, you will tackle the Secret Mission and add an amortization schedule calculator with CSV export.

Secret mission

Amortization Schedule Calculator

Your loan table shows current balances, but borrowers want to see the full picture. How much of each payment goes to interest vs. principal? When will the loan be paid off? Build an amortization schedule calculator that answers these questions with a click.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing cloud costs. PostgreSQL uses minimal resources when idle, and your Express server only runs while your terminal is open.

Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

• PostgreSQL 16 running as a Homebrew service.
• The loan_management database with borrowers, loans, ledger entries, statements, and job history.
• Project files on your Desktop (server/ and client/ folders with all source code and node_modules).

Keep everything running

No action needed. Choose this if you are still actively building or want to revisit the project soon.

• PostgreSQL continues running in the background via Homebrew services. It uses very little memory or CPU when idle.
• Your project files stay on your Desktop ready to use.
• To restart your Express server later, open your terminal, navigate to your server/ folder, and run npm run dev.

Pause - I'll come back to this later

Shut down running processes to free up memory. Your files and database stay intact so you can pick up where you left off.

• Stop your Express server by pressing Ctrl+C in the terminal where it is running.
• Stop the PostgreSQL service by running:

brew services stop postgresql@16

• When you come back, start PostgreSQL again by running:

brew services start postgresql@16

• Then navigate to your server/ folder and run npm run dev to restart the API.

Delete - I don't want to use this again

Remove all project resources and start fresh. This is permanent.

• Stop your Express server by pressing Ctrl+C in the terminal where it is running.
• Stop the PostgreSQL service and drop your database by running these commands:

brew services stop postgresql@16
dropdb loan_management

• Uninstall PostgreSQL 16 from Homebrew by running:

brew uninstall postgresql@16

• Remove your project files from the Desktop by running:

cd ~/Desktop
rm -rf loan-management

• Homebrew itself remains installed. It is useful for future projects and does not consume significant resources.

Nice Work!

Nice Work!

You just built a full-stack financial application from scratch. Starting with basic JavaScript knowledge, you now have a working 3-tier Loan Management System that you can explain line-by-line.

You've learned how to:

• Built a complete 3-tier architecture where a vanilla JavaScript frontend communicates with an Express.js API, which communicates with a PostgreSQL database. You can draw this on a whiteboard and explain every arrow.
• Created 10+ REST API endpoints covering GET, POST, PATCH, and DELETE, backed by raw SQL queries with JOINs, transactions, and NUMERIC types for precise money calculations.
• Implemented automated monthly interest processing using node-cron that compounds interest on all active loans with per-loan transaction isolation, error recovery, and a job history log.
• Built an amortization schedule calculator with interactive what-if analysis for rate and term adjustments, plus CSV export functionality.

Ready to quiz yourself?