Building an Equipment Checkout System with PostgreSQL, Node.js, and Express

Database design, REST APIs, concurrency control, and practical equipment custody workflows.

Imagine an IT department managing laptops, monitors, and tablets for 200 employees.

Every time a device is issued, someone records the assignment. Every time it is returned, someone updates the equipment register.

That sounds like a simple CRUD application.

But consider what happens when:

• Two administrators try to assign the same laptop simultaneously.
• Someone returns equipment that was already marked as returned.
• An employee leaves, but their assigned devices are difficult to identify.
• A transaction updates the asset status but fails before saving the checkout record.
• A device changes hands without preserving its previous assignment history.

These aren't just interface problems. They are data integrity and concurrency problems.

In this tutorial, we'll build the core of an equipment checkout system using PostgreSQL, Node.js, and Express.

By the end, you'll understand how to:

1. Design a relational model for physical assets and employee custody.
2. Prevent duplicate active equipment assignments.
3. Build transactional checkout and return endpoints.
4. Protect shared assets from concurrent updates.
5. Record custody events.
6. Test common failure scenarios.
7. Identify what must be added before production deployment.

This is a small, executable educational example—not a complete enterprise asset management platform.

1. Understand the Business Problem

An equipment checkout system needs to distinguish between an asset and an assignment.

An asset is a physical item owned or managed by an organization.

For example:

Asset ID	Equipment	Current status
LAP-101	Dell Latitude laptop	Available
LAP-102	MacBook Pro	Checked out
MON-201	Dell monitor	Maintenance

An assignment describes a period during which a particular employee is responsible for an asset.

For example:

Asset	Employee	Issued	Returned
LAP-101	Alice	Oct 1	Oct 10
LAP-101	Bob	Oct 12	Not yet returned

Notice that the same laptop can have multiple historical assignments but should have only one active checkout at a time.

That's our most important business rule.

Core workflow

Register asset
     |
     v
  Available
     |
     | Checkout
     v
 Checked out
     |
     | Return
     v
  Available

Additional states such as maintenance, reservation, retirement, and transfer can be introduced later.

For this tutorial, we'll focus on the checkout/return lifecycle.

2. Define the Database Invariants

Before designing database tables, establish conditions the application must preserve.

Rule 1: Each asset has a unique asset tag.

Rule 2: An asset cannot have multiple active checkout records.

Rule 3: Only available assets may be checked out.

Rule 4: A checkout must reference a valid, active employee.

Rule 5: A return must close the active checkout record.

Rule 6: Asset status and checkout history must be updated together.

Rule 7: Historical custody records should be retained.

Some of these rules can be enforced directly by PostgreSQL constraints. Others require transactions and application-level validation.

Using both layers makes the system more reliable.

3. Create the PostgreSQL Schema

We'll use three primary tables and one simple event table.

The examples use standard PostgreSQL features.

Employees

CREATE TABLE employees (
    id BIGSERIAL PRIMARY KEY,
    full_name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    is_active BOOLEAN NOT NULL DEFAULT TRUE
);

An employee can become inactive without deleting their historical checkout records.

Assets

CREATE TABLE assets (
    id BIGSERIAL PRIMARY KEY,
    asset_tag TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    serial_number TEXT,

    status TEXT NOT NULL DEFAULT 'available'
        CHECK (
            status IN (
                'available',
                'checked_out',
                'maintenance',
                'retired'
            )
        )
);

The asset_tag uniquely identifies a physical device.

A QR code or barcode can encode that identifier or a URL that resolves to its record.

Importantly, scanning a QR code does not automatically establish custody or continuous location tracking. The business application must explicitly record those activities.

Checkout history

CREATE TABLE checkouts (
    id BIGSERIAL PRIMARY KEY,

    asset_id BIGINT NOT NULL
        REFERENCES assets(id),

    employee_id BIGINT NOT NULL
        REFERENCES employees(id),

    checked_out_at TIMESTAMPTZ NOT NULL
        DEFAULT NOW(),

    expected_return_at TIMESTAMPTZ,
    returned_at TIMESTAMPTZ,

    issued_condition TEXT,
    return_condition TEXT,

    CHECK (
        expected_return_at IS NULL
        OR expected_return_at >= checked_out_at
    ),

    CHECK (
        returned_at IS NULL
        OR returned_at >= checked_out_at
    )
);

We keep historical checkouts instead of deleting them.

An active checkout is defined as:

returned_at IS NULL

Prevent duplicate active checkouts

CREATE UNIQUE INDEX one_active_checkout_per_asset
ON checkouts (asset_id)
WHERE returned_at IS NULL;

This is a PostgreSQL partial unique index.

It enforces a critical invariant at the database level: no more than one active checkout record per asset.

Unlike a simple application-level SELECT check, the unique index still protects data when concurrent requests attempt to insert conflicting records.

PostgreSQL documents partial indexes here:

https://www.postgresql.org/docs/current/indexes-partial.html

Record custody events

CREATE TABLE asset_events (
    id BIGSERIAL PRIMARY KEY,

    asset_id BIGINT NOT NULL
        REFERENCES assets(id),

    checkout_id BIGINT
        REFERENCES checkouts(id),

    event_type TEXT NOT NULL
        CHECK (
            event_type IN (
                'CHECKED_OUT',
                'RETURNED'
            )
        ),

    occurred_at TIMESTAMPTZ NOT NULL
        DEFAULT NOW()
);

This small event table provides an operational history of checkout and return events.

It is not a full security audit log because it does not yet identify the authenticated administrator who performed the action.

A production audit log would typically include a verified actor identity and additional event context.

4. Insert Example Data

Let's create two employees and two assets.

INSERT INTO employees (full_name, email)
VALUES
    ('Alice Carter', '[e-mail]'),
    ('Bob Smith', '[e-mail]');

INSERT INTO assets (asset_tag, name, category)
VALUES
    ('LAP-101', 'Dell Latitude', 'Laptop'),
    ('MON-201', 'Dell Monitor', 'Monitor');

These are fictional example records.

Both devices initially have the status available.

5. Set Up Node.js and Express

Start a new Node.js project.

mkdir equipment-checkout-api
cd equipment-checkout-api
npm init -y
npm install express pg dotenv

Add "type": "module" to package.json.

Create a PostgreSQL database, then execute the schema and seed statements above using a PostgreSQL client such as psql.

Create a .env file:

DATABASE_URL=postgresql://localhost:5432/asset_tracking
PORT=3000

Replace the connection string with one that matches your local PostgreSQL configuration.

Never commit credentials or .env files containing secrets to a public repository.

Database connection

Create db.js:

import "dotenv/config";
import pg from "pg";

export const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
});

We use a connection pool for database operations.

However, every statement within a transaction must execute using the same checked-out client.

The node-postgres documentation explicitly requires this.

Reference:

https://node-postgres.com/features/transactions

6. Build the Checkout API

Create server.js.

import express from "express";
import { pool } from "./db.js";

const app = express();

app.use(express.json());

app.post("/api/checkouts", async (req, res) => {
  const { assetId, employeeId } = req.body ?? {};

  if (
    !Number.isSafeInteger(assetId) ||
    assetId <= 0 ||
    !Number.isSafeInteger(employeeId) ||
    employeeId <= 0
  ) {
    return res.status(400).json({
      error: "Valid assetId and employeeId are required",
    });
  }

  const client = await pool.connect();

  try {
    await client.query("BEGIN");

    const assetResult = await client.query(
      `SELECT id, status
       FROM assets
       WHERE id = $1
       FOR UPDATE`,
      [assetId]
    );

    const asset = assetResult.rows[0];

    if (!asset) {
      await client.query("ROLLBACK");

      return res.status(404).json({
        error: "Asset not found",
      });
    }

    if (asset.status !== "available") {
      await client.query("ROLLBACK");

      return res.status(409).json({
        error: "Asset is not available",
      });
    }

    const employeeResult = await client.query(
      `SELECT id
       FROM employees
       WHERE id = $1
         AND is_active = TRUE`,
      [employeeId]
    );

    if (employeeResult.rowCount === 0) {
      await client.query("ROLLBACK");

      return res.status(400).json({
        error: "Employee not found or inactive",
      });
    }

    const checkoutResult = await client.query(
      `INSERT INTO checkouts (
         asset_id,
         employee_id
       )
       VALUES ($1, $2)
       RETURNING id, asset_id, employee_id,
                 checked_out_at`,
      [assetId, employeeId]
    );

    const checkout = checkoutResult.rows[0];

    await client.query(
      `UPDATE assets
       SET status = 'checked_out'
       WHERE id = $1`,
      [assetId]
    );

    await client.query(
      `INSERT INTO asset_events (
         asset_id,
         checkout_id,
         event_type
       )
       VALUES ($1, $2, 'CHECKED_OUT')`,
      [assetId, checkout.id]
    );

    await client.query("COMMIT");

    return res.status(201).json({
      message: "Equipment checked out",
      checkout,
    });
  } catch (error) {
    await client.query("ROLLBACK");

    console.error("Checkout error:", error);

    if (error.code === "23505") {
      return res.status(409).json({
        error: "Conflicting active assignment",
      });
    }

    return res.status(500).json({
      error: "Checkout failed",
    });
  } finally {
    client.release();
  }
});

Why use a transaction?

A successful checkout changes several records:

1. The checkout history gains a new active assignment.
2. The asset status becomes checked_out.
3. An event is recorded.

These changes must succeed together.

If any statement fails, the transaction rolls back.

This prevents a partially completed checkout from leaving the records inconsistent.

Why use FOR UPDATE?

SELECT id, status
FROM assets
WHERE id = $1
FOR UPDATE;

The row lock prevents another conflicting transaction from updating or locking the same asset row until the current transaction finishes.

Consider two checkout requests arriving at nearly the same time.

Request A locks LAP-101

Request B waits for LAP-101

Request A creates assignment
Request A commits

Request B reads the updated status

Request B receives HTTP 409

In combination with the partial unique index, this provides strong protection against duplicate active assignments.

The example assumes every custody-modifying operation follows the same transaction and locking conventions.

Reference:

https://www.postgresql.org/docs/current/explicit-locking.html

7. Build the Return API

Returning equipment must close its active assignment and update the corresponding asset status.

To reduce lock-order conflicts, we'll locate the checkout's asset, lock the asset first, and then lock the checkout record.

Add this endpoint to server.js:

app.post("/api/checkouts/:id/return", async (req, res) => {
  const checkoutId = Number(req.params.id);

  if (
    !Number.isSafeInteger(checkoutId) ||
    checkoutId <= 0
  ) {
    return res.status(400).json({
      error: "Invalid checkout ID",
    });
  }

  const client = await pool.connect();

  try {
    await client.query("BEGIN");

    const lookup = await client.query(
      `SELECT asset_id
       FROM checkouts
       WHERE id = $1`,
      [checkoutId]
    );

    if (lookup.rowCount === 0) {
      await client.query("ROLLBACK");

      return res.status(404).json({
        error: "Checkout not found",
      });
    }

    const assetId = lookup.rows[0].asset_id;

    await client.query(
      `SELECT id
       FROM assets
       WHERE id = $1
       FOR UPDATE`,
      [assetId]
    );

    const checkoutResult = await client.query(
      `SELECT id, returned_at
       FROM checkouts
       WHERE id = $1
       FOR UPDATE`,
      [checkoutId]
    );

    if (
      checkoutResult.rowCount === 0 ||
      checkoutResult.rows[0].returned_at !== null
    ) {
      await client.query("ROLLBACK");

      return res.status(409).json({
        error: "Checkout is not active",
      });
    }

    const result = await client.query(
      `UPDATE checkouts
       SET returned_at = NOW()
       WHERE id = $1
       RETURNING id, asset_id, employee_id,
                 returned_at`,
      [checkoutId]
    );

    await client.query(
      `UPDATE assets
       SET status = 'available'
       WHERE id = $1`,
      [assetId]
    );

    await client.query(
      `INSERT INTO asset_events (
         asset_id,
         checkout_id,
         event_type
       )
       VALUES ($1, $2, 'RETURNED')`,
      [assetId, checkoutId]
    );

    await client.query("COMMIT");

    return res.json({
      message: "Equipment returned",
      checkout: result.rows[0],
    });
  } catch (error) {
    await client.query("ROLLBACK");

    console.error("Return error:", error);

    return res.status(500).json({
      error: "Return failed",
    });
  } finally {
    client.release();
  }
});

This is a deliberately simple return workflow.

It assumes returned equipment is available for reassignment.

In a real IT department, damaged equipment may need inspection or maintenance before becoming available again.

For that reason, a production system should support condition checks and an appropriate post-return status.

8. Add an Employee Equipment Lookup API

During offboarding, IT needs to identify every device currently assigned to an employee.

Add the following endpoint:

app.get("/api/employees/:id/assets", async (req, res) => {
  const employeeId = Number(req.params.id);

  if (
    !Number.isSafeInteger(employeeId) ||
    employeeId <= 0
  ) {
    return res.status(400).json({
      error: "Invalid employee ID",
    });
  }

  try {
    const result = await pool.query(
      `SELECT
         a.asset_tag,
         a.name,
         a.category,
         c.id AS checkout_id,
         c.checked_out_at
       FROM checkouts c
       JOIN assets a ON a.id = c.asset_id
       WHERE c.employee_id = $1
         AND c.returned_at IS NULL
       ORDER BY c.checked_out_at DESC`,
      [employeeId]
    );

    return res.json({
      employeeId,
      assets: result.rows,
    });
  } catch (error) {
    console.error("Asset lookup error:", error);

    return res.status(500).json({
      error: "Unable to fetch assets",
    });
  }
});

This endpoint queries the active checkout records rather than only checking the asset status.

For employee offboarding, that's the relevant source of custody information.

Start the server

Finally, add:

const port = Number(process.env.PORT) || 3000;

app.listen(port, () => {
  console.log(`Server running on port ${port}`);
});

Run it:

node server.js

9. Test the Workflow

Let's check out the first laptop to the first employee.

curl -X POST http://localhost:3000/api/checkouts \
  -H "Content-Type: application/json" \
  -d '{"assetId":1,"employeeId":1}'

If the seed data was inserted into a new database in the stated order, this should create an active checkout.

The server should return HTTP 201.

Now repeat the same request.

Because the asset is already checked out, the second request should return HTTP 409.

To retrieve the employee's equipment:

curl http://localhost:3000/api/employees/1/assets

To return the equipment, use the checkout ID from the first successful checkout response:

curl -X POST \
  http://localhost:3000/api/checkouts/1/return

The example above assumes the first generated checkout ID is 1; use the actual returned ID in your environment.

After the return, the asset should be available for another checkout.

Test concurrent requests

The most important concurrency test is to submit two checkout requests for the same available asset at approximately the same time.

Expected behavior:

• One request succeeds.
• The other receives a conflict.
• Only one active checkout exists in the database.

Verify that final condition directly:

SELECT *
FROM checkouts
WHERE asset_id = 1
  AND returned_at IS NULL;

The query should return no more than one row.

The partial unique index provides the database-level guarantee for that condition.

10. What This Example Does—and Doesn't—Guarantee

The example implements:

• Unique asset identifiers.
• Historical checkout records.
• Prevention of duplicate active checkouts.
• Transactional checkout and return operations.
• Row locking for asset-level concurrency.
• Basic checkout and return events.
• Employee equipment lookup.

It does not implement a complete production system.

Before deployment, several additional components are necessary.

Authentication and authorization

Each request must identify its authenticated user.

An employee should not be able to check out an arbitrary device or retrieve another employee's records without authorization.

Tenant isolation

If multiple organizations use the same application, their assets, employees, and checkout histories must be isolated.

Idempotency

A client may retry a request after a network timeout even if the original operation succeeded.

Production APIs should consider persistent idempotency keys to identify repeated requests safely.

Employee transfers

Transferring equipment should preserve its previous custody period while creating a new assignment.

A transfer workflow should be handled transactionally, with explicit business rules.

Full audit history

A production audit trail should record the authenticated actor, relevant changes, timestamps, and appropriate context.

Operational controls

Real applications also need structured logging, database migrations, monitoring, secure configuration, backup and recovery procedures, and automated integration tests.

A particularly important engineering consideration is that the checkout and return endpoints rely on consistent asset-first locking. Any future transfer or maintenance workflow must coordinate with that strategy.

11. Why These Design Decisions Matter to IT Teams

From a business perspective, these database concepts solve practical problems.

Technical decision	Operational benefit
Unique asset tags	Consistent equipment identification
Partial unique index	Prevents conflicting active assignments
Historical checkouts	Preserves previous custody records
Transactions	Prevents partially completed handovers
Row locking	Protects concurrent equipment assignments
Employee lookup API	Supports offboarding and reconciliation
Custody events	Provides a foundation for change history

For growing businesses, the most useful equipment management system is not necessarily the one with the longest feature list.

It is one that keeps responsibility and asset records consistent as equipment moves between employees and locations.

12. How This Relates to Real Asset Management Platforms

Building an internal equipment checkout system can be a valuable engineering project.

However, teams also need to consider ongoing maintenance, authorization, auditing, user experience, and the operational effort required to manage equipment at scale.

That is why some organizations evaluate dedicated physical asset management platforms instead of maintaining custom software.

For example, Stackroom focuses on centralized physical asset records, equipment assignments, documented handovers and returns, and audit workflows.

For an operational explanation of the equipment handover process, see:

Equipment Checkout Process Guide — Stackroom

Disclosure: I am associated with Stackroom, a physical asset and equipment management platform.

Conclusion

A reliable equipment checkout system is more than an inventory database.

It needs to preserve custody information, enforce business rules, and remain consistent when operations happen concurrently.

The most important lessons are:

1. Model assets and checkout periods separately.
2. Use database constraints for critical invariants.
3. Wrap multi-step state changes in transactions.
4. Lock shared asset records consistently.
5. Retain historical checkout information.
6. Record custody events.
7. Test duplicate requests, concurrency, and failure conditions.
8. Treat authorization and tenant isolation as production requirements.

A good equipment checkout system should answer not only “What equipment do we own?” but also “Who currently has it, and how did that assignment happen?”

Discussion

If you were designing an equipment tracking application, would you store transfers as separate checkout periods or introduce a dedicated transfer entity?

What trade-offs would influence your decision?

---

Technical references

• PostgreSQL — Partial Indexes
• PostgreSQL — Explicit Locking
• node-postgres — Transactions
• Express — Error Handling