Build a Service Request REST API

Build an Express and PostgreSQL API to manage service requests.

Introduction

30 Second Summary

Service requests often arrive through scattered messages. Ownership becomes unclear as urgent work gets buried beneath newer conversations.

In this project, you will build the backend core of an internal service desk as a REST API. An Express server backed by PostgreSQL will keep each request available after a restart.

What You'll Build

From PowerShell, you will submit a request to your service desk and see the same record after restarting the server.

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

  • A persistent request workflow where you can submit a request with service details. The request remains available after restarting the API.
  • A searchable request queue that filters work by category, priority, or status. You can assign each request and move it through resolution to closure.
  • A dashboard endpoint that reports open, resolved, closed, and total counts. A private GitHub repository will hold the tested source while .env stays local.
  • Secret Mission: Enforce a controlled status workflow that rejects skipped stages.

Are there any prerequisites?

You need basic JavaScript knowledge. You should also have completed one Node.js or database application.

Before We Start

This checkpoint locks in who the service-request workflow serves and why the organisation needs it before any hands-on work begins.

Prepare the API Workspace

A dependable REST API needs a verified workspace before request logic starts relying on it. This removes environment uncertainty from every later test.

This step checks your local toolchain before any service request logic depends on it. You will finish with a live Express health endpoint backed by PostgreSQL inside a Visual Studio Code workspace.

In this step, get ready to:
  • Verify that the required development tools are available.
  • Create the API workspace with its pinned dependencies.
  • Start the API and confirm its health response.
Verify the local toolchain

Node.js runs the server. PowerShell lets you check the runtime from the same terminal you use throughout this project.

The remaining checks confirm that npm, psql, and Git are reachable. Finding a missing tool here keeps the workspace predictable.

  • Press the Windows key to open search.
  • Type Visual Studio Code into the search field.
  • Press Enter to open Visual Studio Code.
  • Click Terminal in the top menu.
  • Click New Terminal to open the PowerShell terminal.
  • Check the four tool versions by running these commands:
node --version
npm --version
psql --version
git --version

What do these checks prove?

  • The first line prints the installed Node.js version.
  • The second line confirms that the Node.js package manager is available.
  • The third line confirms that PowerShell can reach the PostgreSQL command-line client.
  • The fourth line confirms that source control is available for the workspace.

The documented path uses Node.js v24.21.0 LTS. Choose the tab that matches the first command's output.

✔️ I see the required Node.js version

Your Node.js runtime is ready. You can continue with the installed versions of the remaining tools.

ⓧ I see an older Node.js version

The API uses the environment-file support available on the documented Node.js path. Update Node.js before creating the workspace.

  • Visit the official Node.js previous releases page.
  • Download the Windows installer for Node.js v24.21.0 LTS.
  • Run the downloaded installer.
  • Return to the Visual Studio Code terminal.
  • Run the same Node.js version check again.

ⓧ Node.js is unavailable

Install the documented Node.js release before continuing. The installer also provides npm.

  • Visit the official Node.js previous releases page.
  • Download the Windows installer for Node.js v24.21.0 LTS.
  • Run the downloaded installer.
  • Return to the Visual Studio Code terminal.
  • Run the same Node.js version check again.

Is another tool unavailable?

Confirm that you opened a PowerShell terminal in Visual Studio Code. Restart Visual Studio Code after repairing an installation so the terminal receives the updated system path.

Share the non-secret command output with the NextWork community if the tool still cannot be found.

Help me diagnose the missing development tool.

Create the workspace files

A workspace keeps the API source, package configuration, and local connection settings together. Placing it on your Desktop also makes the project easy to find.

Visual Studio Code may ask whether you trust the folder after it opens. You created this folder yourself, so approving it enables normal workspace features.

  • Create the service-request-api folder on your Desktop by running these commands:
Set-Location -Path "$HOME\Desktop"
New-Item -Path . -Name "service-request-api" -ItemType Directory
Set-Location -Path ".\service-request-api"
code .

What do these commands do?

  • The first command moves PowerShell to your Desktop.
  • The second command creates the service-request-api folder there.
  • The third command makes that folder the terminal's current location.
  • The final command opens the current folder as a Visual Studio Code workspace.
  • Approve the workspace trust prompt if Visual Studio Code displays it.
  • Confirm that service-request-api appears at the top of the Explorer sidebar.

Did the workspace stay closed?

Use the File menu to select Open Folder. Choose the service-request-api folder from your Desktop.

Help me open my project folder in Visual Studio Code.

  • Create the four initial files inside service-request-api by running this command in the workspace terminal:
New-Item -ItemType File -Path "package.json", ".env", ".gitignore", "server.js"

What does this command do?

PowerShell creates four empty files in the current folder. Each file holds one part of the API setup.

  • Confirm that package.json, .env, .gitignore, and server.js appear in the Explorer sidebar.
  • Open package.json from the Explorer sidebar.
  • Define the project metadata with its pinned dependencies by pasting this code:
{
  "name": "service-request-api",
  "version": "1.0.0",
  "private": true,
  "type": "module",
  "scripts": {
    "start": "node --env-file=.env server.js"
  },
  "dependencies": {
    "express": "5.2.1",
    "pg": "8.23.0"
  }
}

What does this configuration do?

  • The module setting allows server.js to use JavaScript imports.
  • The start script loads values from .env before running the server.
  • The dependency pins install Express 5.2.1 with node-postgres 8.23.0.
  • Save package.json.
  • Install the pinned dependencies by running this command:
npm install

What does this command do?

npm reads package.json and downloads the two pinned packages. It also records the resolved dependency tree in a lock file.

  • Confirm that the command finishes successfully.
  • Check that node_modules appears in the Explorer sidebar.

Did the dependency install fail?

Check that the terminal path ends with service-request-api. Confirm that package.json contains valid JSON with matching braces.

Help me troubleshoot the dependency installation.

Your database password stays in .env. This value remains on your computer because the next file keeps it out of source control.

  • Open .env from the Explorer sidebar.
  • Add the PostgreSQL connection settings by pasting this content:
PGUSER=postgres
PGPASSWORD=your-postgres-password-here
PGHOST=localhost
PGPORT=5432
PGDATABASE=service_requests

What do these settings control?

The connection pool reads these environment variables when Node.js starts. They identify the local PostgreSQL user, password, host, port, and database.

  • Replace your-postgres-password-here with the password for your local PostgreSQL user.
  • Save .env.
  • Open .gitignore from the Explorer sidebar.
  • Define the files that Git must ignore by pasting this content:
node_modules/
.env

Why ignore these files?

The dependency folder can be rebuilt from package.json. The environment file contains a local database credential that must stay outside the future repository.

  • Save .gitignore.
  • Confirm that both files remain visible in the Explorer sidebar.
Run the database-backed health endpoint

A health endpoint gives you a small test surface for the whole setup. Its database query proves that the server can reach PostgreSQL before request routes are added.

  • Open server.js from the Explorer sidebar.
  • Create the Express app with its health route by pasting this code:
import express from 'express';
import { Pool } from 'pg';

const app = express();
const pool = new Pool();
const PORT = 3000;

const CATEGORIES = ['facilities', 'it', 'hr', 'other'];
const PRIORITIES = ['low', 'medium', 'high', 'urgent'];
const STATUSES = ['open', 'in_progress', 'resolved', 'closed'];

const hasText = (value) => typeof value === 'string' && value.trim().length > 0;

app.disable('x-powered-by');
app.use(express.json());

app.get('/health', async (_req, res) => {
  const result = await pool.query('SELECT CURRENT_TIMESTAMP AS database_time');
  res.json({ status: 'ok', databaseTime: result.rows[0].database_time });
});

app.listen(PORT, () => {
  console.log(`Service request API listening on port ${PORT}`);
});

What does this server code do?

  • The imports load Express with the node-postgres connection pool.
  • The pool reads the PostgreSQL environment variables from the running process.
  • The constants centralize the categories, priorities, and statuses accepted by the API.
  • The hasText helper recognizes non-empty text values.
  • The JSON middleware prepares Express to read JSON request bodies.
  • The health route queries PostgreSQL before returning a status with the database time.
  • The listener keeps the API available on port 3000.
  • Save server.js.
  • Start the API by running this command in the workspace terminal:
node --env-file=.env server.js

What does this command do?

Node.js loads the connection values from .env before running server.js. The server then keeps this terminal busy while it listens for requests.

You should see Service request API listening on port 3000 in the terminal. That message proves the Express listener is active.

Did the server stop during startup?

Confirm that the dependencies finished installing. Check that the terminal is inside service-request-api before starting the API again.

Help me troubleshoot the API startup.

Predictable error responses make the health check easier to trust. The final middleware handles unknown routes and unexpected server failures.

  • Stop the running API by pressing Ctrl+C in its terminal.
  • In server.js, find the closing line of the health route.
  • Insert this middleware before app.listen:
app.use((_req, res) => {
  res.status(404).json({ error: 'route not found' });
});

app.use((error, _req, res, _next) => {
  console.error(error);
  res.status(500).json({ error: 'internal server error' });
});

What does this middleware do?

  • The first handler returns a JSON response when no earlier route matches the request.
  • The second handler logs unexpected errors before returning a generic server response.
  • Their position after the routes lets successful requests reach the intended handler first.
  • Save server.js.
  • Start the updated API by running this command:
node --env-file=.env server.js

What does this command confirm?

The updated server starts with the new middleware in place. The terminal remains attached to the running API.

  • Open a browser tab at http://localhost:3000/missing.

You should see a JSON error containing route not found. This confirms that the unknown-route middleware is active.

  • Return to Visual Studio Code.
  • Click Terminal in the top menu.
  • Click New Terminal to open a second PowerShell terminal.
  • Before running the check, predict whether the API returns a database timestamp.
  • Call the health endpoint from the second terminal by running:
Invoke-RestMethod -Uri 'http://localhost:3000/health'

What should I see?

PowerShell displays a response with status equal to ok. It also displays a databaseTime value returned by PostgreSQL.

That is the workspace online. Your Express server can now receive HTTP requests and reach its database connection.

Did the health check fail?

Confirm that the first terminal still shows the running API. Check that PostgreSQL is running locally.

Verify that PGPASSWORD in .env matches your local PostgreSQL password. Keep the password out of screenshots and help requests.

Help me debug the health endpoint safely.

✔️ Awesome, I've got everything!

Your workspace files match this checkpoint. Keep the API running for the final confirmation.

ⓧ I'd like to double check the full code

Compare each file with the references below. The .env reference keeps a safe password placeholder, while your local file contains your own password.

package.json:

{
  "name": "service-request-api",
  "version": "1.0.0",
  "private": true,
  "type": "module",
  "scripts": {
    "start": "node --env-file=.env server.js"
  },
  "dependencies": {
    "express": "5.2.1",
    "pg": "8.23.0"
  }
}

.env:

PGUSER=postgres
PGPASSWORD=your-postgres-password-here
PGHOST=localhost
PGPORT=5432
PGDATABASE=service_requests

.gitignore:

node_modules/
.env

server.js:

import express from 'express';
import { Pool } from 'pg';

const app = express();
const pool = new Pool();
const PORT = 3000;

const CATEGORIES = ['facilities', 'it', 'hr', 'other'];
const PRIORITIES = ['low', 'medium', 'high', 'urgent'];
const STATUSES = ['open', 'in_progress', 'resolved', 'closed'];

const hasText = (value) => typeof value === 'string' && value.trim().length > 0;

app.disable('x-powered-by');
app.use(express.json());

app.get('/health', async (_req, res) => {
  const result = await pool.query('SELECT CURRENT_TIMESTAMP AS database_time');
  res.json({ status: 'ok', databaseTime: result.rows[0].database_time });
});

app.use((_req, res) => {
  res.status(404).json({ error: 'route not found' });
});

app.use((error, _req, res, _next) => {
  console.error(error);
  res.status(500).json({ error: 'internal server error' });
});

app.listen(PORT, () => {
  console.log(`Service request API listening on port ${PORT}`);
});

Your API workspace is ready. Next, you will turn this health-only server into a request workflow that can create and list service requests.

Build the First Request Workflow

Your health endpoint proves that Express can answer a request. A service desk also needs a REST resource that accepts service requests from people.

This step starts with process memory so you can build a usable create-and-list flow. A restart test then reveals how long the submitted data survives.

In this step, get ready to:
  • Add an in-memory collection with a list route.
  • Submit a validated request through a create route.
  • Restart the API to test the durability of its data.
Add the in-memory request list

The requests array represents the service desk queue inside the running Node.js process. The list route returns the current contents as JSON.

  • In the VS Code Explorer sidebar, select server.js.
  • Find the closing line of the /health route.
  • Add the in-memory collection and list route below the health route by pasting this code:
const requests = [];

app.get('/api/requests', (_req, res) => {
  // Return every request currently held by this process.
  return res.json(requests);
});

What Does This Code Do?

  • The requests array holds each submitted service request while the server process runs.
  • The GET /api/requests route returns every object currently stored in that array.
  • Express converts the array into a JSON response for the client.
  • Save server.js.
  • Stop the running API by pressing Ctrl+C in the first PowerShell terminal.
  • Restart the API by running this command:
node --env-file=.env server.js

What Does This Command Do?

Node.js loads the connection settings from .env. It then starts server.js on port 3000.

This terminal stays occupied while the API is running. Keep it running while you send requests from the second terminal.

Before you check the new route, how many service requests do you expect the fresh array to contain?

  • Check the current request count from the second PowerShell terminal by running:
@(Invoke-RestMethod -Uri 'http://localhost:3000/api/requests').Count

What Does This Check Do?

PowerShell calls the list route and wraps its response in an array. The Count property reports how many service requests came back.

You will see 0. Your API now has a working list route with an empty starting queue.

Cannot Reach the List Route?

Confirm that the first terminal still shows the API listening on port 3000. Check that the list route appears above the fallback 404 middleware in server.js.

If the count command still fails, help me troubleshoot the request list route.

Create and list a service request

A POST route turns a JSON body into a new service request. Input validation checks required text before the request enters the queue.

  • Find the requests array declaration in server.js.
  • Insert this POST route beneath the declaration:
app.post('/api/requests', (req, res) => {
  // Read and validate the fields that define a service request.
  const { title, description, category, priority = 'medium', requesterName } = req.body;

  if (![title, description, category, requesterName].every(hasText)) {
    return res.status(400).json({
      error: 'title, description, category, and requesterName are required',
    });
  }

  if (!CATEGORIES.includes(category) || !PRIORITIES.includes(priority)) {
    return res.status(400).json({ error: 'category or priority is invalid' });
  }

  // Keep the new request in process memory with timestamps for later edits.
  const now = new Date().toISOString();
  const request = {
    id: requests.length + 1,
    title: title.trim(),
    description: description.trim(),
    category, priority,
    requesterName: requesterName.trim(),
    status: 'open',
    createdAt: now, updatedAt: now,
  };

  requests.push(request);
  return res.status(201).json(request);
});

How Does Request Creation Work?

  • The route reads the submitted fields from req.body.
  • The hasText helper rejects missing or empty required text.
  • The CATEGORIES and PRIORITIES arrays limit those fields to supported values.
  • Each accepted request receives an ID plus an open status. It also receives creation and update timestamps.
  • Save server.js.
  • Stop the running API by pressing Ctrl+C in the first PowerShell terminal.
  • Start the updated API by running:
node --env-file=.env server.js

What Changed on Restart?

Node.js loads the saved version of server.js. The running API now includes the create route plus the list route.

  • Submit a facilities request from the second PowerShell terminal by running this script:
$requestBody = @{
  title = 'Leaking tap in kitchen'
  description = 'The kitchen tap continues dripping after being turned off.'
  category = 'facilities'
  priority = 'high'
  requesterName = 'Alex Morgan'
} | ConvertTo-Json

Invoke-RestMethod -Method Post -Uri 'http://localhost:3000/api/requests' -ContentType 'application/json' -Body $requestBody

What Does This Script Send?

The hash table holds the five request fields. ConvertTo-Json serializes those values into the request body.

Invoke-RestMethod sends that body to the POST route. The content type tells Express to parse the payload as JSON.

You will see the submitted request with an id of 1. Its status is open.

That is your first complete service request workflow. The API now validates a submission and returns the record it created.

  • Retrieve the current request queue by running:
Invoke-RestMethod -Uri 'http://localhost:3000/api/requests'

What Does the List Show?

The GET route reads the same requests array used by the POST route. PowerShell displays the request you just submitted.

You will see one request with the same title and requester. Its ID remains 1.

Request Rejected or Missing?

Confirm that every required property in the PowerShell hash table matches the field names used by req.body. Check that facilities and high appear in the allowed arrays.

If the request still does not appear, help me debug the POST and GET request flow.

✔️ Awesome, I've got everything!

Your create and list routes are working. Double-check that you saved server.js before the restart test.

ⓧ I'd like to double check the full code

import express from 'express';
import { Pool } from 'pg';

const app = express();
const pool = new Pool();
const PORT = 3000;

const CATEGORIES = ['facilities', 'it', 'hr', 'other'];
const PRIORITIES = ['low', 'medium', 'high', 'urgent'];
const STATUSES = ['open', 'in_progress', 'resolved', 'closed'];

const hasText = (value) => typeof value === 'string' && value.trim().length > 0;

app.disable('x-powered-by');
app.use(express.json());

app.get('/health', async (_req, res) => {
  const result = await pool.query('SELECT CURRENT_TIMESTAMP AS database_time');
  res.json({ status: 'ok', databaseTime: result.rows[0].database_time });
});

const requests = [];

app.post('/api/requests', (req, res) => {
  // Read and validate the fields that define a service request.
  const { title, description, category, priority = 'medium', requesterName } = req.body;

  if (![title, description, category, requesterName].every(hasText)) {
    return res.status(400).json({
      error: 'title, description, category, and requesterName are required',
    });
  }

  if (!CATEGORIES.includes(category) || !PRIORITIES.includes(priority)) {
    return res.status(400).json({ error: 'category or priority is invalid' });
  }

  // Keep the new request in process memory with timestamps for later edits.
  const now = new Date().toISOString();
  const request = {
    id: requests.length + 1,
    title: title.trim(),
    description: description.trim(),
    category, priority,
    requesterName: requesterName.trim(),
    status: 'open',
    createdAt: now, updatedAt: now,
  };

  requests.push(request);
  return res.status(201).json(request);
});

app.get('/api/requests', (_req, res) => {
  // Return every request currently held by this process.
  return res.json(requests);
});

app.use((_req, res) => {
  res.status(404).json({ error: 'route not found' });
});

app.use((error, _req, res, _next) => {
  console.error(error);
  res.status(500).json({ error: 'internal server error' });
});

app.listen(PORT, () => {
  console.log(`Service request API listening on port ${PORT}`);
});
Test the request across a restart

The request is available through both API routes during the current run. Restarting the Node.js process tests whether the same record remains available afterward.

  • Stop the running API by pressing Ctrl+C in the first PowerShell terminal.
  • Start a new API process by running:
node --env-file=.env server.js

What Does This Restart Change?

The command starts a new Node.js process from the saved server.js file. The API exposes the same routes on port 3000.

Before you check, do you think the request with ID 1 is still available?

  • Check the request count from the second PowerShell terminal by running:
@(Invoke-RestMethod -Uri 'http://localhost:3000/api/requests').Count

What Does This Final Check Measure?

The command calls the list route after the restart. It counts the records returned by the new server process.

You will see 0. The request disappeared because the new process created a fresh empty requests array.

This shortfall is the intended result. Process memory cannot preserve the organisation's requests across server restarts.

Still Seeing One Request?

Confirm that you stopped the original Node.js process before starting the API again. A request count of 1 indicates that you are still calling the original process.

If the restart behavior remains unclear, help me verify which Node.js process is serving the API.

You now have clear evidence that the workflow needs durable storage. Next, you will move service requests into PostgreSQL so a restart cannot erase the queue.

Persist Requests in PostgreSQL

The restart test from the previous step erased the request because the Express process held the only copy. An organisation pilot needs every submitted request to outlive that process.

PostgreSQL gives the API durable relational storage. In this step, node-postgres provides a connection pool that links the existing routes to stored rows.

In this step, get ready to:
  • Create the service_requests database with its constrained requests table.
  • Connect the Express API through a node-postgres connection pool.
  • Replace temporary process memory with database queries that survive restarts.
Create and apply the database schema

A relational schema defines the shape of every stored request. Its constraints stop invalid categories or statuses from entering the table.

Your local database password is a credential. It stays inside .env, which .gitignore already excludes from Git.

  • In the PowerShell terminal running the API, press Ctrl+C to stop Node.js.
  • In Visual Studio Code, switch back to .env from earlier.
  • Replace your-postgres-password-here with your local PostgreSQL password.
  • Save .env.
  • Create the local service_requests database by running this command:
psql -U postgres -c "CREATE DATABASE service_requests;"

What does this command do?

The -U option connects as the PostgreSQL user named postgres. The -c option executes the database creation statement.

You should see a creation confirmation from psql. Good work. PostgreSQL now has a dedicated database for the service desk.

Database creation failed?

  • Confirm that the PostgreSQL service is running.
  • Retry the command with the password for your local PostgreSQL user.
  • Check whether the service_requests database already exists from an earlier attempt.

Get help with creating the local PostgreSQL database

  • In the VS Code Explorer sidebar, create schema.sql inside the service-request-api folder.

You should see schema.sql listed beside server.js.

  • Define the request table plus its indexes in schema.sql by pasting this code:
CREATE TABLE IF NOT EXISTS requests (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title TEXT NOT NULL CHECK (char_length(title) BETWEEN 3 AND 120),
  description TEXT NOT NULL CHECK (char_length(description) BETWEEN 3 AND 2000),
  category TEXT NOT NULL CHECK (category IN ('facilities', 'it', 'hr', 'other')),
  priority TEXT NOT NULL DEFAULT 'medium' CHECK (priority IN ('low', 'medium', 'high', 'urgent')),
  status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'in_progress', 'resolved', 'closed')),
  requester_name TEXT NOT NULL CHECK (char_length(requester_name) BETWEEN 2 AND 100),
  assigned_to TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS requests_status_idx ON requests (status);
CREATE INDEX IF NOT EXISTS requests_priority_idx ON requests (priority);

What does this schema do?

  • The identity primary key gives each request a unique numeric id.
  • The CHECK constraints enforce valid text lengths plus approved workflow values.
  • The timestamp columns record when each request is created or updated.
  • The indexes prepare frequent status and priority lookups for faster filtering.
  • Save schema.sql.
  • Apply the schema to service_requests by running this command:
psql -U postgres -d service_requests -f schema.sql

How is the schema applied?

The -d option selects service_requests. The -f option runs every SQL statement stored in schema.sql.

You should see confirmations for the table plus both indexes. The database can now enforce the request model independently of the API.

Schema application failed?

  • Confirm that PowerShell is using the service-request-api folder.
  • Confirm that schema.sql is saved in that folder.
  • Check the reported line number against the schema block above.

Get help with applying the PostgreSQL schema

Connect the API to PostgreSQL

The pool becomes the API's shared entry point to PostgreSQL. Its empty constructor reads the five existing PostgreSQL settings from .env.

  • In VS Code, switch back to server.js.
  • Replace the opening lines through PORT with this database-enabled setup:
import express from 'express';
import { Pool } from 'pg';

const app = express();
const pool = new Pool();
const PORT = 3000;

What does this setup do?

  • The Pool import brings the node-postgres connection pool into the API.
  • The pool instance reuses database connections across route calls.
  • The empty constructor reads the existing PostgreSQL variables from .env.
  • Save server.js.
  • Confirm that the API still starts by running:
node --env-file=.env server.js

What does this command load?

Node.js loads the database settings from .env before starting server.js.

You should see the API listening on port 3000.

  • Press Ctrl+C in the API terminal to stop Node.js.
  • Replace the existing /health route in server.js with this database-backed version:
app.get('/health', async (_req, res) => {
  const result = await pool.query('SELECT CURRENT_TIMESTAMP AS database_time');
  res.json({ status: 'ok', databaseTime: result.rows[0].database_time });
});

What does this health check prove?

  • The route asks PostgreSQL for its current timestamp through pool.query().
  • A successful response proves that the API can reach the database.
  • The response includes databaseTime so you can see that PostgreSQL answered.
  • Save server.js.
  • Start the database-backed API by running:
node --env-file=.env server.js

What happens at startup?

The API starts on port 3000. The pool opens a database connection when the health route sends its first query.

  • In the second PowerShell terminal from earlier, check the database connection by running:
Invoke-RestMethod -Uri 'http://localhost:3000/health'

What does this check prove?

The request reaches Express before the route queries PostgreSQL. A successful response confirms that the full path is connected.

You should see status equal to ok plus a databaseTime value. The database connection is live.

Health check cannot reach PostgreSQL?

  • Confirm that .env contains your own local PostgreSQL password.
  • Confirm that the service_requests database was created successfully.
  • Confirm that PostgreSQL is running before restarting the API.

Get help with debugging the API database connection

Replace memory with database queries

A parameterized query keeps SQL text separate from request values. PostgreSQL receives those values through numbered placeholders.

  • Press Ctrl+C in the API terminal to stop Node.js.
  • In server.js, locate the existing POST /api/requests route.
  • Replace its in-memory request creation block with this database insert:
  const result = await pool.query(
    `INSERT INTO requests (title, description, category, priority, requester_name)
     VALUES ($1, $2, $3, $4, $5)
     RETURNING *`,
    [title.trim(), description.trim(), category, priority, requesterName.trim()],
  );

  return res.status(201).json(result.rows[0]);

How does the insert stay safe?

  • The SQL statement uses numbered placeholders for every submitted value.
  • The separate values array supplies the validated request data.
  • The RETURNING * clause sends the stored row back to the route.
  • Save server.js.
  • Start the updated API by running:
node --env-file=.env server.js

What is ready now?

The create route now writes validated requests into PostgreSQL. Its response comes from the stored database row.

  • Switch to the second PowerShell terminal.
  • Press Up Arrow until your request submission from the previous step returns.
  • Press Enter to submit a request to the updated API.

You should see a numeric id plus status set to open. The response also includes database timestamps.

Request was not stored?

  • Confirm that the schema application completed before the API started.
  • Confirm that your earlier request body contains every required field.
  • Check the API terminal for the database operation that failed.

Get help with debugging the request insert

  • Press Ctrl+C in the API terminal to stop Node.js.
  • Replace the existing GET /api/requests route with this database query:
app.get('/api/requests', async (_req, res) => {
  const result = await pool.query(
    'SELECT * FROM requests ORDER BY created_at DESC',
  );

  return res.json(result.rows);
});

How does the list route work?

  • The route selects every row from the requests table.
  • The ordering places the newest request first.
  • The route returns the query result as JSON.
  • Save server.js.
  • Start the API again by running:
node --env-file=.env server.js

What changed after the restart?

The restarted process has no access to its earlier memory. The list route now reads the request from PostgreSQL.

  • Switch to the second PowerShell terminal.
  • Press Up Arrow until your earlier request-list command returns.
  • Press Enter to list the stored requests.

You should see the request created before the restart. PostgreSQL preserved it while Node.js was stopped.

  • Press Ctrl+C in the API terminal to stop Node.js.
  • Delete the unused in-memory requests array declaration from server.js.
  • Save server.js.
  • Start the final database-backed version by running:
node --env-file=.env server.js

What does this final restart prove?

The API now starts without an in-memory request store. PostgreSQL is the only source used by both request routes.

Before you list requests again, do you expect the row created before the restart to still be there?

  • Switch to the second PowerShell terminal.
  • Press Up Arrow until the earlier request-list command returns.
  • Press Enter to run the final persistence check.

You should see the same stored request again. That closes the persistence gap exposed in the previous step.

Stored request missing after restart?

  • Confirm that the create response came from the database-backed POST route.
  • Confirm that the list route queries the requests table.
  • Confirm that both terminals are calling the API on port 3000.

Get help with debugging request persistence

✔️ Awesome, I've got everything!

Your files now match the durable PostgreSQL version of the API.

ⓧ I'd like to double check the full code

{
  "name": "service-request-api",
  "version": "1.0.0",
  "private": true,
  "type": "module",
  "scripts": {
    "start": "node --env-file=.env server.js"
  },
  "dependencies": {
    "express": "5.2.1",
    "pg": "8.23.0"
  }
}
PGUSER=postgres
PGPASSWORD=your-postgres-password-here
PGHOST=localhost
PGPORT=5432
PGDATABASE=service_requests
node_modules/
.env
CREATE TABLE IF NOT EXISTS requests (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title TEXT NOT NULL CHECK (char_length(title) BETWEEN 3 AND 120),
  description TEXT NOT NULL CHECK (char_length(description) BETWEEN 3 AND 2000),
  category TEXT NOT NULL CHECK (category IN ('facilities', 'it', 'hr', 'other')),
  priority TEXT NOT NULL DEFAULT 'medium' CHECK (priority IN ('low', 'medium', 'high', 'urgent')),
  status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'in_progress', 'resolved', 'closed')),
  requester_name TEXT NOT NULL CHECK (char_length(requester_name) BETWEEN 2 AND 100),
  assigned_to TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS requests_status_idx ON requests (status);
CREATE INDEX IF NOT EXISTS requests_priority_idx ON requests (priority);
import express from 'express';
import { Pool } from 'pg';

const app = express();
const pool = new Pool();
const PORT = 3000;

const CATEGORIES = ['facilities', 'it', 'hr', 'other'];
const PRIORITIES = ['low', 'medium', 'high', 'urgent'];
const STATUSES = ['open', 'in_progress', 'resolved', 'closed'];

const hasText = (value) => typeof value === 'string' && value.trim().length > 0;

app.disable('x-powered-by');
app.use(express.json());

app.get('/health', async (_req, res) => {
  const result = await pool.query('SELECT CURRENT_TIMESTAMP AS database_time');
  res.json({ status: 'ok', databaseTime: result.rows[0].database_time });
});

app.post('/api/requests', async (req, res) => {
  const {
    title,
    description,
    category,
    priority = 'medium',
    requesterName,
  } = req.body;

  if (![title, description, category, requesterName].every(hasText)) {
    return res.status(400).json({ error: 'title, description, category, and requesterName are required' });
  }

  if (!CATEGORIES.includes(category) || !PRIORITIES.includes(priority)) {
    return res.status(400).json({ error: 'category or priority is invalid' });
  }

  const result = await pool.query(
    `INSERT INTO requests (title, description, category, priority, requester_name)
     VALUES ($1, $2, $3, $4, $5)
     RETURNING *`,
    [title.trim(), description.trim(), category, priority, requesterName.trim()],
  );

  return res.status(201).json(result.rows[0]);
});

app.get('/api/requests', async (_req, res) => {
  const result = await pool.query(
    'SELECT * FROM requests ORDER BY created_at DESC',
  );

  return res.json(result.rows);
});

app.use((_req, res) => {
  res.status(404).json({ error: 'route not found' });
});

app.use((error, _req, res, _next) => {
  console.error(error);
  res.status(500).json({ error: 'internal server error' });
});

app.listen(PORT, () => {
  console.log(`Service request API listening on port ${PORT}`);
});

Your service requests now survive server restarts. Next up, you will let staff assign work and move each request through its operational status.

Add Assignment and Status Tracking

Your PostgreSQL database now keeps every request after the server restarts. Your REST API can create those persistent records.

A stored request still cannot support daily service desk work until staff can claim it. Staff also need to edit details or move the request through its status stages.

In this step, get ready to:
  • Add a PATCH /api/requests/:id route for partial updates.
  • Validate every supplied field before updating PostgreSQL.
  • Test assignment, editing, status changes, and missing request handling.
Handle partial request updates

A partial update changes selected fields while preserving every omitted value. The PATCH method gives your Express API that behavior.

  • In server.js, find the fallback route that starts with app.use((_req, res) => {.
  • Insert the update handler above that fallback route by pasting this code:
app.patch('/api/requests/:id', async (req, res) => {
  const id = Number(req.params.id);
  const { title, description, category, priority, assignedTo, status } = req.body;

  const result = await pool.query(
    `UPDATE requests
     SET title = COALESCE($1, title),
         description = COALESCE($2, description),
         category = COALESCE($3, category),
         priority = COALESCE($4, priority),
         assigned_to = COALESCE($5, assigned_to),
         status = COALESCE($6, status),
         updated_at = CURRENT_TIMESTAMP
     WHERE id = $7
     RETURNING *`,
    [
      title?.trim() ?? null,
      description?.trim() ?? null,
      category ?? null,
      priority ?? null,
      assignedTo?.trim() ?? null,
      status ?? null,
      id,
    ],
  );

  return res.json(result.rows[0]);
});

What does this route do?

  • The route converts req.params.id into a number that identifies the row to update.
  • The node-postgres pool sends the SQL separately from the seven parameter values.
  • Each COALESCE expression keeps the current column value when its matching parameter is null.
  • The database replaces updated_at with the current timestamp whenever the update succeeds.
  • Save server.js.
  • Switch back to the running PowerShell terminal from earlier.
  • Press Ctrl+C to stop the API.
  • Restart the API with the updated route by running this command:
node --env-file=.env server.js

What does this command do?

Node.js loads the database settings from .env. It then starts the updated API from server.js.

You should see Service request API listening on port 3000 in the server terminal.

  • Switch to the second PowerShell terminal from earlier.
  • Assign request 1 to a staff member by running this update:
Invoke-RestMethod -Uri 'http://localhost:3000/api/requests/1' -Method Patch -ContentType 'application/json' -Body (@{ assignedTo = 'Jordan Lee'; status = 'in_progress' } | ConvertTo-Json)

What should you see?

The returned row should show Jordan Lee under assigned_to. Its status should be in_progress.

Compare updated_at with created_at. The update timestamp should be newer.

Good progress. Request 1 now has an owner and an active work status.

Assignment update not working?

Confirm that the server terminal still shows the API running. Check that request 1 exists by calling the list endpoint from earlier.

Check that assignedTo uses camel case in the request body. The database response uses assigned_to.

Help me debug my PATCH assignment request.

Reject invalid changes

Request bodies contain untrusted values. Validation protects the database from empty text, unsupported options, and invalid identifiers.

  • In the PATCH handler inside server.js, find the line that reads values from req.body.
  • Insert these validation checks immediately below that line:
  if (!Number.isInteger(id) || id < 1) {
    return res.status(400).json({ error: 'id must be a positive integer' });
  }

  if ([title, description, category, priority, assignedTo, status].every((value) => value === undefined)) {
    return res.status(400).json({ error: 'provide at least one field to update' });
  }

  if ((title !== undefined && !hasText(title)) || (description !== undefined && !hasText(description))) {
    return res.status(400).json({ error: 'title and description cannot be empty' });
  }
  if (category !== undefined && !CATEGORIES.includes(category)) {
    return res.status(400).json({ error: 'category is invalid' });
  }
  if (priority !== undefined && !PRIORITIES.includes(priority)) {
    return res.status(400).json({ error: 'priority is invalid' });
  }
  if (status !== undefined && !STATUSES.includes(status)) {
    return res.status(400).json({ error: 'status is invalid' });
  }
  if (assignedTo !== undefined && !hasText(assignedTo)) {
    return res.status(400).json({ error: 'assignedTo cannot be empty' });
  }

How do these checks protect the route?

  • The ID check accepts positive integers only.
  • The field-presence check rejects an update body with no supported values.
  • The text checks reject blank titles, descriptions, and assignee names.
  • The option checks reuse CATEGORIES, PRIORITIES, and STATUSES as the permitted values.
  • Save server.js.
  • Switch to the server terminal.
  • Press Ctrl+C to stop the API.
  • Restart the API with the validation checks by running this command:
node --env-file=.env server.js

Why restart the API?

The running Node.js process still holds the previous route code. Restarting it loads the validation checks you just saved.

  • Switch to the second PowerShell terminal.
  • Test the validation by sending the unsupported status waiting with this command:
Invoke-RestMethod -Uri 'http://localhost:3000/api/requests/1' -Method Patch -ContentType 'application/json' -Body (@{ status = 'waiting' } | ConvertTo-Json)

Not getting the validation response?

This request is expected to fail with HTTP status 400. The response body should contain status is invalid.

If the update succeeds, confirm that the validation block sits above the pool.query() call. Restart the API after moving the block.

Help me debug my PATCH validation checks.

Return missing-request responses

A valid ID can still point to no database row. The route needs a separate response for that missing resource.

  • In the PATCH handler inside server.js, find return res.json(result.rows[0]);.
  • Insert this row-count check immediately above that return line:
  if (result.rowCount === 0) {
    return res.status(404).json({ error: 'request not found' });
  }

What does this check do?

PostgreSQL reports how many rows the update matched through rowCount. A value of 0 produces a 404 response because the requested record does not exist.

  • Save server.js.
  • Switch to the server terminal.
  • Press Ctrl+C to stop the API.
  • Restart the complete update route by running this command:
node --env-file=.env server.js

What should happen now?

The API starts on port 3000 with the complete update route. Successful updates return a row while missing IDs return 404.

✔️ Awesome, I've got everything!

Great. Double check that you saved server.js before testing the complete workflow.

ⓧ I'd like to double check the full code

import express from 'express';
import { Pool } from 'pg';

const app = express();
const pool = new Pool();
const PORT = 3000;

const CATEGORIES = ['facilities', 'it', 'hr', 'other'];
const PRIORITIES = ['low', 'medium', 'high', 'urgent'];
const STATUSES = ['open', 'in_progress', 'resolved', 'closed'];

const hasText = (value) => typeof value === 'string' && value.trim().length > 0;

app.disable('x-powered-by');
app.use(express.json());

app.get('/health', async (_req, res) => {
  const result = await pool.query('SELECT CURRENT_TIMESTAMP AS database_time');
  res.json({ status: 'ok', databaseTime: result.rows[0].database_time });
});

app.post('/api/requests', async (req, res) => {
  const {
    title,
    description,
    category,
    priority = 'medium',
    requesterName,
  } = req.body;

  if (![title, description, category, requesterName].every(hasText)) {
    return res.status(400).json({ error: 'title, description, category, and requesterName are required' });
  }

  if (!CATEGORIES.includes(category) || !PRIORITIES.includes(priority)) {
    return res.status(400).json({ error: 'category or priority is invalid' });
  }

  const result = await pool.query(
    `INSERT INTO requests (title, description, category, priority, requester_name)
     VALUES ($1, $2, $3, $4, $5)
     RETURNING *`,
    [title.trim(), description.trim(), category, priority, requesterName.trim()],
  );

  return res.status(201).json(result.rows[0]);
});

app.get('/api/requests', async (_req, res) => {
  const result = await pool.query(
    'SELECT * FROM requests ORDER BY created_at DESC',
  );

  return res.json(result.rows);
});

app.patch('/api/requests/:id', async (req, res) => {
  const id = Number(req.params.id);
  const { title, description, category, priority, assignedTo, status } = req.body;

  if (!Number.isInteger(id) || id < 1) {
    return res.status(400).json({ error: 'id must be a positive integer' });
  }

  if ([title, description, category, priority, assignedTo, status].every((value) => value === undefined)) {
    return res.status(400).json({ error: 'provide at least one field to update' });
  }

  if ((title !== undefined && !hasText(title)) || (description !== undefined && !hasText(description))) {
    return res.status(400).json({ error: 'title and description cannot be empty' });
  }
  if (category !== undefined && !CATEGORIES.includes(category)) {
    return res.status(400).json({ error: 'category is invalid' });
  }
  if (priority !== undefined && !PRIORITIES.includes(priority)) {
    return res.status(400).json({ error: 'priority is invalid' });
  }
  if (status !== undefined && !STATUSES.includes(status)) {
    return res.status(400).json({ error: 'status is invalid' });
  }
  if (assignedTo !== undefined && !hasText(assignedTo)) {
    return res.status(400).json({ error: 'assignedTo cannot be empty' });
  }

  const result = await pool.query(
    `UPDATE requests
     SET title = COALESCE($1, title),
         description = COALESCE($2, description),
         category = COALESCE($3, category),
         priority = COALESCE($4, priority),
         assigned_to = COALESCE($5, assigned_to),
         status = COALESCE($6, status),
         updated_at = CURRENT_TIMESTAMP
     WHERE id = $7
     RETURNING *`,
    [
      title?.trim() ?? null,
      description?.trim() ?? null,
      category ?? null,
      priority ?? null,
      assignedTo?.trim() ?? null,
      status ?? null,
      id,
    ],
  );

  if (result.rowCount === 0) {
    return res.status(404).json({ error: 'request not found' });
  }

  return res.json(result.rows[0]);
});

app.use((_req, res) => {
  res.status(404).json({ error: 'route not found' });
});

app.use((error, _req, res, _next) => {
  console.error(error);
  res.status(500).json({ error: 'internal server error' });
});

app.listen(PORT, () => {
  console.log(`Service request API listening on port ${PORT}`);
});

Before you send this update, make a mental prediction about which fields will change:

  • Switch to the second PowerShell terminal.
  • Edit the request title and priority by running this command:
Invoke-RestMethod -Uri 'http://localhost:3000/api/requests/1' -Method Patch -ContentType 'application/json' -Body (@{ title = 'Replace meeting room projector'; priority = 'urgent' } | ConvertTo-Json)

What should you see?

The returned row should contain the new title and urgent priority. The existing assignee and in_progress status should remain unchanged.

  • Resolve request 1 by running this update:
Invoke-RestMethod -Uri 'http://localhost:3000/api/requests/1' -Method Patch -ContentType 'application/json' -Body (@{ status = 'resolved' } | ConvertTo-Json)

Check the resolved request

The returned row should show resolved under status. Its updated_at value should advance again.

  • Close request 1 by running this update:
Invoke-RestMethod -Uri 'http://localhost:3000/api/requests/1' -Method Patch -ContentType 'application/json' -Body (@{ status = 'closed' } | ConvertTo-Json)

Check the closed request

The returned row should show closed under status. The title, priority, and assignee should still contain their previous values.

Before this final check, make a mental prediction about whether a missing ID returns 400 or 404:

  • Confirm missing-request handling by sending an update for request 999999 with this command:
Invoke-RestMethod -Uri 'http://localhost:3000/api/requests/999999' -Method Patch -ContentType 'application/json' -Body (@{ status = 'closed' } | ConvertTo-Json)

Check the missing request response

This request is expected to fail with HTTP status 404. Its response body should contain request not found.

If you receive an internal server error, confirm that the rowCount check appears before return res.json(result.rows[0]);.

Help me debug the missing request response.

That is the workflow core working. Your API can assign, edit, resolve, and close persistent service requests.

Your request queue now supports the updates staff need during daily work. Next, you will make that queue searchable before adding dashboard counts and publishing the backend to GitHub.

Add Search, Dashboard Counts, and GitHub

Your PostgreSQL-backed API now stores requests through every assignment or status change. Staff still need a quick way to find urgent work and measure the queue.

This step adds filtered search to your Express API. You will also publish the finished backend to a private GitHub repository for review.

In this step, get ready to:
  • Add validated request filters with parameterized text search.
  • Return queue totals from a dashboard endpoint.
  • Publish the source to a private GitHub repository.
Add filters and search

A request queue becomes harder to use as it grows. Query parameters let staff narrow the queue without creating a separate endpoint for every possible view.

  • In server.js from earlier, find the existing app.get('/api/requests' route.
  • Replace that complete route with this first filtered version:
app.get('/api/requests', async (req, res) => {
  const { status, priority, category, search } = req.query;

  if (status && !STATUSES.includes(status)) {
    return res.status(400).json({ error: 'status is invalid' });
  }

  const clauses = [];
  const values = [];

  const addFilter = (sql, value) => {
    values.push(value);
    clauses.push(sql.replace('?', `$${values.length}`));
  };

  if (status) addFilter('status = ?', status);

  const whereClause = clauses.length ? `WHERE ${clauses.join(' AND ')}` : '';
  const result = await pool.query(
    `SELECT * FROM requests ${whereClause} ORDER BY created_at DESC`,
    values,
  );

  return res.json(result.rows);
});

How does the filter work?

  • The route reads status from req.query so the URL can control the filter.
  • The STATUSES check rejects values outside the API's supported workflow.
  • The values array keeps request data separate from the SQL text.
  • The whereClause stays empty when the URL has no filters.
  • Save server.js.
  • Stop the running API by pressing Ctrl+C in its PowerShell terminal.
  • Restart the API by running this command:
node --env-file=.env server.js

What should the server show?

Node.js loads the database settings from .env before running server.js. You should see the message confirming that the service request API is listening on port 3000.

  • In the second PowerShell terminal from earlier, call the closed-request view by running:
Invoke-RestMethod -Uri 'http://localhost:3000/api/requests?status=closed'

What should the filter return?

You should see only requests whose status value is closed. An empty array means your database currently has no closed requests.

Filter request failing?

Confirm the API terminal still shows a running process. Check that the route uses req instead of the earlier unused request parameter.

If the server reports a syntax problem, compare every brace around the replacement route. Help me debug the filtered request route

The same query pipeline can support priority and category filters. Each value needs validation before it reaches the database.

  • In server.js, find the closing brace of the status validation.
  • Paste these checks directly below it:
  if (priority && !PRIORITIES.includes(priority)) {
    return res.status(400).json({ error: 'priority is invalid' });
  }
  if (category && !CATEGORIES.includes(category)) {
    return res.status(400).json({ error: 'category is invalid' });
  }

What do these checks protect?

  • The PRIORITIES check accepts only the priority values supported by the API.
  • The CATEGORIES check applies the same boundary to request categories.
  • Find the if (status) line inside the same route.
  • Paste these filter calls directly below it:
  if (priority) addFilter('priority = ?', priority);
  if (category) addFilter('category = ?', category);

How are filters combined?

Each supplied value adds one SQL condition to clauses. The final query joins those conditions with AND so every returned request matches the complete filter.

  • Record the category from one request you created earlier: your existing category.
  • Record the priority from the same request: your existing priority.
  • Save server.js.
  • Stop the API by pressing Ctrl+C in its PowerShell terminal.
  • Restart the API by running:
node --env-file=.env server.js

Why restart the API?

The running Node.js process keeps the previous route code in memory. Restarting it loads the new validation and filter calls from the saved file.

  • Test the matching category and priority by running:
Invoke-RestMethod -Uri 'http://localhost:3000/api/requests?category=[[CATEGORY_FILTER="your existing category"]]&priority=[[PRIORITY_FILTER="your existing priority"]]'

What should the combined filter show?

Every returned request should match the category you recorded. Its priority should also match the recorded priority.

Text search follows the same parameterized pattern. PostgreSQL can compare the search term against both the title and description without placing that term directly inside the SQL string.

  • Record one complete word from the title of the same request: one word from your request title.
  • In server.js, find the if (category) filter line.
  • Paste this search block directly below it:
  if (hasText(search)) {
    addFilter('(title ILIKE ? OR description ILIKE ?)', `%${search.trim()}%`);
    values.push(`%${search.trim()}%`);
    clauses[clauses.length - 1] = clauses[clauses.length - 1].replace('?', `$${values.length}`);
  }

How does parameterized search stay safe?

  • The existing hasText() helper ignores an empty search value.
  • The two placeholders compare the same term with the request title and description.
  • The percentage characters let ILIKE match the term anywhere in either field.
  • The search values stay inside the parameter array instead of being joined into the SQL text.
  • Save server.js.
  • Stop the API by pressing Ctrl+C in its PowerShell terminal.
  • Restart the API by running:
node --env-file=.env server.js

What changed in the route?

The restarted route now accepts status, priority, category, and search query parameters. Any supplied filters become one parameterized database query.

  • Test all three values against the same request by running:
Invoke-RestMethod -Uri 'http://localhost:3000/api/requests?category=[[CATEGORY_FILTER="your existing category"]]&priority=[[PRIORITY_FILTER="your existing priority"]]&search=[[SEARCH_TERM="one word from your request title"]]'

What proves search works?

You should see the request whose category and priority you recorded. The recorded word should appear in its title or description.

Search returning no requests?

Use one complete word that appears in the stored title or description. Check that the category and priority came from that same request.

If every search is empty, compare the two parameter values with the two ILIKE placeholders. Help me debug my parameterized search

Build dashboard counts

A dashboard needs a small summary instead of every request row. A filtered aggregate query calculates several queue totals in one trip to PostgreSQL.

  • In server.js, find the route-level 404 middleware near the bottom of the file.
  • Paste this dashboard route directly above that middleware:
app.get('/api/dashboard', async (_req, res) => {
  const result = await pool.query(
    `SELECT
       count(*) FILTER (WHERE status IN ('open', 'in_progress'))::integer AS open,
       count(*) FILTER (WHERE status = 'resolved')::integer AS resolved,
       count(*) FILTER (WHERE status = 'closed')::integer AS closed,
       count(*)::integer AS total
     FROM requests`,
  );

  return res.json(result.rows[0]);
});

What does the dashboard query count?

  • The open count includes requests with open or in-progress work.
  • The resolved count includes requests waiting after resolution.
  • The closed count includes completed requests.
  • The total count covers every row in the requests table.
  • Save server.js.
  • Stop the API by pressing Ctrl+C in its PowerShell terminal.
  • Restart the API by running:
node --env-file=.env server.js

What is now available?

The restarted API registers /api/dashboard before the fallback route. Requests to that endpoint now reach the aggregate query.

Before you call the dashboard, predict how your earlier status updates affect each count.

  • Retrieve the dashboard summary from the second PowerShell terminal by running:
Invoke-RestMethod -Uri 'http://localhost:3000/api/dashboard'

What should the dashboard return?

You should see integer values for open, resolved, closed, and total. The total should equal the number of persistent requests in your database.

Dashboard route missing?

Confirm the dashboard route appears above the fallback middleware. A fallback route placed first handles the request before the dashboard query can run.

If a count looks wrong, compare each status spelling with the values stored in PostgreSQL. Help me debug my dashboard counts

Your filtered list and dashboard now complete the core API. Use these tabs to compare the finished server.js before publishing it.

✔️ Awesome, I've got everything!

Great. Double-check that you saved server.js before you continue.

ⓧ I'd like to double check the full code

import express from 'express';
import { Pool } from 'pg';

const app = express();
const pool = new Pool();
const PORT = 3000;

const CATEGORIES = ['facilities', 'it', 'hr', 'other'];
const PRIORITIES = ['low', 'medium', 'high', 'urgent'];
const STATUSES = ['open', 'in_progress', 'resolved', 'closed'];

const hasText = (value) => typeof value === 'string' && value.trim().length > 0;

app.disable('x-powered-by');
app.use(express.json());

app.get('/health', async (_req, res) => {
  const result = await pool.query('SELECT CURRENT_TIMESTAMP AS database_time');
  res.json({ status: 'ok', databaseTime: result.rows[0].database_time });
});

app.post('/api/requests', async (req, res) => {
  const {
    title,
    description,
    category,
    priority = 'medium',
    requesterName,
  } = req.body;

  if (![title, description, category, requesterName].every(hasText)) {
    return res.status(400).json({ error: 'title, description, category, and requesterName are required' });
  }

  if (!CATEGORIES.includes(category) || !PRIORITIES.includes(priority)) {
    return res.status(400).json({ error: 'category or priority is invalid' });
  }

  const result = await pool.query(
    `INSERT INTO requests (title, description, category, priority, requester_name)
     VALUES ($1, $2, $3, $4, $5)
     RETURNING *`,
    [title.trim(), description.trim(), category, priority, requesterName.trim()],
  );

  return res.status(201).json(result.rows[0]);
});

app.get('/api/requests', async (req, res) => {
  const { status, priority, category, search } = req.query;

  if (status && !STATUSES.includes(status)) {
    return res.status(400).json({ error: 'status is invalid' });
  }
  if (priority && !PRIORITIES.includes(priority)) {
    return res.status(400).json({ error: 'priority is invalid' });
  }
  if (category && !CATEGORIES.includes(category)) {
    return res.status(400).json({ error: 'category is invalid' });
  }

  const clauses = [];
  const values = [];

  const addFilter = (sql, value) => {
    values.push(value);
    clauses.push(sql.replace('?', `$${values.length}`));
  };

  if (status) addFilter('status = ?', status);
  if (priority) addFilter('priority = ?', priority);
  if (category) addFilter('category = ?', category);
  if (hasText(search)) {
    addFilter('(title ILIKE ? OR description ILIKE ?)', `%${search.trim()}%`);
    values.push(`%${search.trim()}%`);
    clauses[clauses.length - 1] = clauses[clauses.length - 1].replace('?', `$${values.length}`);
  }

  const whereClause = clauses.length ? `WHERE ${clauses.join(' AND ')}` : '';
  const result = await pool.query(
    `SELECT * FROM requests ${whereClause} ORDER BY created_at DESC`,
    values,
  );

  return res.json(result.rows);
});

app.patch('/api/requests/:id', async (req, res) => {
  const id = Number(req.params.id);
  const { title, description, category, priority, assignedTo, status } = req.body;

  if (!Number.isInteger(id) || id < 1) {
    return res.status(400).json({ error: 'id must be a positive integer' });
  }

  if ([title, description, category, priority, assignedTo, status].every((value) => value === undefined)) {
    return res.status(400).json({ error: 'provide at least one field to update' });
  }

  if ((title !== undefined && !hasText(title)) || (description !== undefined && !hasText(description))) {
    return res.status(400).json({ error: 'title and description cannot be empty' });
  }
  if (category !== undefined && !CATEGORIES.includes(category)) {
    return res.status(400).json({ error: 'category is invalid' });
  }
  if (priority !== undefined && !PRIORITIES.includes(priority)) {
    return res.status(400).json({ error: 'priority is invalid' });
  }
  if (status !== undefined && !STATUSES.includes(status)) {
    return res.status(400).json({ error: 'status is invalid' });
  }
  if (assignedTo !== undefined && !hasText(assignedTo)) {
    return res.status(400).json({ error: 'assignedTo cannot be empty' });
  }

  const result = await pool.query(
    `UPDATE requests
     SET title = COALESCE($1, title),
         description = COALESCE($2, description),
         category = COALESCE($3, category),
         priority = COALESCE($4, priority),
         assigned_to = COALESCE($5, assigned_to),
         status = COALESCE($6, status),
         updated_at = CURRENT_TIMESTAMP
     WHERE id = $7
     RETURNING *`,
    [
      title?.trim() ?? null,
      description?.trim() ?? null,
      category ?? null,
      priority ?? null,
      assignedTo?.trim() ?? null,
      status ?? null,
      id,
    ],
  );

  if (result.rowCount === 0) {
    return res.status(404).json({ error: 'request not found' });
  }

  return res.json(result.rows[0]);
});

app.get('/api/dashboard', async (_req, res) => {
  const result = await pool.query(
    `SELECT
       count(*) FILTER (WHERE status IN ('open', 'in_progress'))::integer AS open,
       count(*) FILTER (WHERE status = 'resolved')::integer AS resolved,
       count(*) FILTER (WHERE status = 'closed')::integer AS closed,
       count(*)::integer AS total
     FROM requests`,
  );

  return res.json(result.rows[0]);
});

app.use((_req, res) => {
  res.status(404).json({ error: 'route not found' });
});

app.use((error, _req, res, _next) => {
  console.error(error);
  res.status(500).json({ error: 'internal server error' });
});

app.listen(PORT, () => {
  console.log(`Service request API listening on port ${PORT}`);
});
Publish to a private GitHub repository

A Git commit creates a reviewable snapshot of the backend. The existing .gitignore keeps the database password and installed packages outside that snapshot.

  • In the VS Code PowerShell terminal from earlier, initialize the repository and create the first commit by running:
git init -b main
git add .
git commit -m "First commit"

What do these Git commands do?

  • The first command creates the local repository with main as its starting branch.
  • The second command stages project files that are not excluded by .gitignore.
  • The final command saves the staged files as First commit.

Commit not created?

If Git asks for your author details, complete the requested identity configuration before running the commit command again. Keep .env closed and untracked.

If the commit still fails, use the reported message to identify which Git setting needs attention. Help me complete my first Git commit

The local commit needs an empty remote repository before it can be pushed. Create that destination without adding starter files that would conflict with your existing project history.

  • Switch back to your signed-in GitHub browser tab.
  • Select New repository.
  • Enter service-request-api in the repository name field.
  • Choose the private repository visibility.
  • Leave every repository initialization option unselected.
  • Click Create repository.
  • Copy the repository URL shown in the setup instructions.
  • Record the copied URL here: REMOTE-URL.

Git may ask you to authenticate during the first push. The approval connects this local repository to the private remote you just created.

  • Connect the local repository to the copied URL by running:
git remote add origin [[REMOTE_URL="REMOTE-URL"]]

What does origin mean?

The name origin is the local label for your GitHub repository URL. Git uses that label when you push or fetch changes.

  • Push the committed main branch to GitHub by running:
git push -u origin main

What does the first push establish?

The push uploads your committed source to the private repository. The tracking option connects the local main branch with the remote branch for later pushes.

Push rejected or authentication blocked?

Complete the GitHub sign-in flow if your browser requests approval. Confirm that the repository URL belongs to the empty private repository you just created.

If Git reports a remote configuration problem, compare the copied URL with the repository setup page. Help me debug my first GitHub push

Before you run the final checks, predict which outputs prove that the local API and remote repository are both ready.

  • Verify the filtered queue, dashboard totals, and configured remote by running:
Invoke-RestMethod -Uri 'http://localhost:3000/api/requests?status=closed'
Invoke-RestMethod -Uri 'http://localhost:3000/api/dashboard'
git remote -v

What should the final checks prove?

  • The first result contains only closed requests or an empty array when none are closed.
  • The second result contains integer open, resolved, closed, and total counts.
  • The final output lists your GitHub repository as origin for fetch and push operations.
  • Return to the private service-request-api repository page on GitHub.
  • Refresh the repository page.

You should see package.json, server.js, schema.sql, and .gitignore on the main branch. The repository should not contain .env or node_modules.

That completes the API core. Your searchable queue, dashboard counts, persistent database, and private source repository are ready for the workflow challenge.

Secret mission

Enforce Valid Status Transitions

Your API currently accepts any recognized status for a service request. Add a controlled workflow that rejects skipped stages with HTTP 409 while preserving valid progress from open to in_progress to resolved to closed.

Clean Up Your Resources

Clean Up Your Resources

The documented path costs $0. Your three cleanup choices are to keep the resources available, pause the work, or delete every project resource.

Resources you used:

  • The local PostgreSQL database named service_requests with its persistent service-request data.
  • The local service-request-api folder containing the project files plus installed npm dependencies.
  • The private GitHub repository named service-request-api.

Keep everything running

No cleanup action is needed. This option keeps the API foundation ready for the authentication and React follow-on projects.

  • Leave the local service_requests database in place.
  • Leave the local service-request-api folder in place.
  • Leave the private service-request-api repository in place.
  • If the API is running, return to its PowerShell terminal.
  • Press Ctrl+C when you finish working with the API.

Pause - I'll come back to this later

Pausing preserves the local work while taking the active API and repository out of everyday use. The local database remains available at no cost.

  • If the API is running, return to its PowerShell terminal.
  • Press Ctrl+C to stop the API process.
  • Open the private service-request-api repository on GitHub.
  • Select Settings.
  • Find the Danger Zone section.
  • Click Archive this repository.
  • Click I understand the consequences, archive this repository.

Your service_requests database and local service-request-api folder remain available for your return.

Delete - I don't want to use this again

Deleting these resources is final for the local database. The database command permanently removes every saved service request.

  • If the API is running, return to its PowerShell terminal.
  • Press Ctrl+C to release the database connection.
  • Remove the service_requests database by running this command:
psql -U postgres -d postgres -c "DROP DATABASE service_requests;"

Why connect to another database?

  • The -U postgres option selects the database user.
  • The -d postgres option connects to a database other than service_requests.
  • The -c option executes the SQL command.
  • The DROP DATABASE service_requests; command permanently removes the named database.

You should see a PowerShell confirmation that the database was removed.

The local folder holds the ignored .env file plus the installed node_modules dependencies. Removing the folder clears the complete local project copy.

  • Close any editor window using the local service-request-api folder.
  • Use File Explorer to locate the local service-request-api folder.
  • Delete the service-request-api folder.
  • Check File Explorer for the deleted folder.

You should no longer see the local service-request-api folder.

The private repository is now the final copy of the API. GitHub uses a confirmation flow before removing it.

  • Open the private service-request-api repository on GitHub.
  • Select Settings.
  • Select General.
  • Find the Danger Zone section.
  • Click Delete this repository.
  • Complete the documented confirmation flow.
  • Return to your GitHub repositories.

You should no longer see the private service-request-api repository.

Nice Work!

Nice Work!

Mission complete! You built a service request backend that preserves records across server restarts.

You've learned how to:

  • Built an Express REST API that carries a service request from submission through assignment to closure.
  • Used PostgreSQL plus parameterized SQL to preserve requests safely across server restarts.
  • Delivered queue search plus dashboard counts in a private GitHub repository that excludes .env.
  • Secret Mission: Enforced a status-transition policy for the sequence open to in_progress to resolved to closed. Invalid jumps now receive HTTP 409.

Ready to quiz yourself?