DEV Community: Arbaz

How to Build an IT Asset Lifecycle Management System with Node.js and PostgreSQL

Arbaz — Wed, 04 Nov 2026 05:50:07 +0000

Managing company equipment sounds simple until you have dozens of laptops, monitors, phones, and other devices moving between employees, departments, and locations.

A spreadsheet might tell you which laptop belongs to whom today. But can it tell you who previously held that laptop, when it went for repair, whether its warranty has expired, or why it was retired?

Those questions become important as an organization grows.

In this tutorial, we'll build the foundation of an IT asset lifecycle management system using Node.js, Express, and PostgreSQL. Rather than creating another basic CRUD application, we'll focus on asset states, controlled transitions, transaction safety, and historical records.

By the end, you'll understand how to structure a backend that keeps track of equipment throughout its lifecycle.

1. Understanding the IT Asset Lifecycle

An IT asset lifecycle describes the stages an asset passes through, from acquisition to final disposal.

A typical lifecycle looks like this:

Stage	Description
Procurement	Equipment is purchased or ordered
Registration	Asset information is recorded
Available	Equipment is ready for assignment
Assigned	Equipment is issued to an employee
Maintenance	Equipment is being inspected or repaired
Returned	Equipment is received back and assessed
Retired	Equipment is permanently removed from active use

There is an important distinction here: not every business event needs to become a permanent asset status.

For example, returned is often better modeled as an event. Once a laptop is returned and inspected, its current state might become available or maintenance.

This avoids building an unnecessarily complicated state machine.

For our implementation, we'll use four operational states:

available
assigned
maintenance
retired

We'll record procurement and registration details separately, while assignment, return, repair, and retirement actions will create historical events.

2. Choosing the Technology Stack

We'll use three main technologies:

Technology	Responsibility
Node.js	JavaScript runtime
Express	HTTP API and routing
PostgreSQL	Relational database and transaction management

PostgreSQL is particularly useful here because equipment records have relationships, constraints, and consistency requirements.

Consider a laptop assignment. We need to update its current holder and record the assignment event. Either both operations should succeed or neither should be committed.

That's where database transactions become essential.

Project Setup

Create a project:

mkdir asset-lifecycle-api
cd asset-lifecycle-api

npm init -y

npm install express pg dotenv

Enable ES modules in package.json:

{
  "type": "module",
  "scripts": {
    "start": "node server.js"
  },
  "dependencies": {
    "dotenv": "^17.0.0",
    "express": "^5.0.0",
    "pg": "^8.0.0"
  }
}

The version ranges above are illustrative; use currently maintained compatible releases and commit your generated lockfile.

Create a .env file:

DATABASE_URL=postgresql://postgres:password@localhost:5432/asset_db
PORT=3000

Replace the example credentials with your local database credentials. Never commit .env files or real secrets to source control.

Create the database:

CREATE DATABASE asset_db;

We'll run our schema against that database next.

3. Designing the PostgreSQL Database

A common mistake in asset tracking applications is storing everything in one table.

That approach makes the current inventory easy to display, but historical information becomes difficult to reconstruct.

For this example, we'll use two tables:

assets — the current state of each asset.
asset_events — the history of lifecycle actions.

This gives us a practical separation between current state and historical events.

Create the Assets Table

CREATE TABLE assets (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

    asset_tag TEXT NOT NULL UNIQUE,
    serial_number TEXT UNIQUE,
    name TEXT NOT NULL,

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

    assigned_to TEXT,

    purchased_on DATE,
    warranty_expires_on DATE,

    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT valid_assignment CHECK (
        (status = 'assigned' AND assigned_to IS NOT NULL)
        OR
        (status <> 'assigned' AND assigned_to IS NULL)
    )
);

The valid_assignment constraint prevents an asset from remaining assigned to someone while its status says available, maintenance, or retired.

This is useful because database constraints protect data even when an application bug bypasses expected validation.

In a larger system, assigned_to should normally reference an employee or user record through a foreign key. We're using text here to keep the example focused.

Create the Asset Events Table

CREATE TABLE asset_events (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

    asset_id BIGINT NOT NULL
        REFERENCES assets(id) ON DELETE RESTRICT,

    event_type TEXT NOT NULL
        CHECK (event_type IN (
            'registered',
            'assigned',
            'returned',
            'sent_to_maintenance',
            'maintenance_completed',
            'retired'
        )),

    from_status TEXT,
    to_status TEXT NOT NULL,

    performed_by TEXT NOT NULL,
    details JSONB NOT NULL DEFAULT '{}'::jsonb,

    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_asset_events_history
ON asset_events (asset_id, created_at DESC, id DESC);

The event table records what happened, when it happened, and which application identity performed the action.

For example:

Event	Previous State	New State
Registered	—	Available
Assigned	Available	Assigned
Returned	Assigned	Available
Sent for repair	Available	Maintenance
Repair completed	Maintenance	Available
Retired	Available	Retired

An event history is useful for troubleshooting, compliance evidence, and understanding equipment movement.

However, an ordinary database table is not automatically a tamper-proof audit log. Stronger audit requirements may need restricted permissions, append-only enforcement, external log retention, or additional integrity controls.

4. Connecting Node.js to PostgreSQL

Create db.js:

import 'dotenv/config';
import pg from 'pg';

const { Pool } = pg;

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  connectionTimeoutMillis: 5000,
  idleTimeoutMillis: 30000,
});

A connection pool lets your application reuse database connections instead of opening a new connection for every request.

For ordinary independent queries, pool.query() is convenient.

For transactions, we'll acquire a dedicated client from the pool.

This distinction matters because all statements in a PostgreSQL transaction must run through the same database connection.

5. Implementing Controlled Lifecycle Transitions

Instead of allowing arbitrary status updates, we'll define which changes are permitted.

Our state-transition rules are:

const transitions = {
  available: {
    assign: 'assigned',
    send_to_maintenance: 'maintenance',
    retire: 'retired',
  },

  assigned: {
    return: 'available',
  },

  maintenance: {
    complete_maintenance: 'available',
    retire: 'retired',
  },

  retired: {},
};

An assigned laptop cannot be directly assigned to somebody else using this simplified workflow. It must first be returned.

Similarly, a retired asset cannot be reactivated accidentally.

Different organizations may need additional rules, such as direct reassignments, lost equipment, quarantine, or disposal approval.

For now, we want a small state machine with predictable behavior.

6. Building the Lifecycle REST API

Create server.js:

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

const app = express();
app.use(express.json({ limit: '100kb' }));

const transitions = {
  available: {
    assign: 'assigned',
    send_to_maintenance: 'maintenance',
    retire: 'retired',
  },
  assigned: {
    return: 'available',
  },
  maintenance: {
    complete_maintenance: 'available',
    retire: 'retired',
  },
  retired: {},
};

const eventNames = {
  assign: 'assigned',
  return: 'returned',
  send_to_maintenance: 'sent_to_maintenance',
  complete_maintenance: 'maintenance_completed',
  retire: 'retired',
};

function getAssetId(value) {
  if (!/^[1-9]\d*$/.test(value)) return null;

  const id = Number(value);

  return Number.isSafeInteger(id) ? id : null;
}

// Demo identity only. Replace with authenticated
// authorization context before production.
function getActor() {
  return 'demo-operator';
}

app.post('/assets', async (req, res) => {
  const { assetTag, name } = req.body ?? {};

  if (
    typeof assetTag !== 'string' ||
    !assetTag.trim() ||
    typeof name !== 'string' ||
    !name.trim()
  ) {
    return res.status(400).json({
      error: 'Valid assetTag and name are required',
    });
  }

  const client = await pool.connect();

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

    const result = await client.query(
      `INSERT INTO assets (asset_tag, name)
       VALUES ($1, $2)
       RETURNING *`,
      [assetTag.trim(), name.trim()]
    );

    const asset = result.rows[0];

    await client.query(
      `INSERT INTO asset_events
       (asset_id, event_type, to_status, performed_by)
       VALUES ($1, 'registered', 'available', $2)`,
      [asset.id, getActor()]
    );

    await client.query('COMMIT');

    return res.status(201).json(asset);
  } catch (error) {
    await client.query('ROLLBACK');

    if (error.code === '23505') {
      return res.status(409).json({
        error: 'Asset tag already exists',
      });
    }

    throw error;
  } finally {
    client.release();
  }
});

app.post('/assets/:id/transition', async (req, res) => {
  const id = getAssetId(req.params.id);
  const { action, assignedTo } = req.body ?? {};

  if (!id || typeof action !== 'string') {
    return res.status(400).json({
      error: 'Invalid asset ID or action',
    });
  }

  if (
    action === 'assign' &&
    (typeof assignedTo !== 'string' ||
      !assignedTo.trim())
  ) {
    return res.status(400).json({
      error: 'assignedTo is required',
    });
  }

  const client = await pool.connect();

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

    const result = await client.query(
      'SELECT * FROM assets WHERE id = $1 FOR UPDATE',
      [id]
    );

    const asset = result.rows[0];

    if (!asset) {
      await client.query('ROLLBACK');
      return res.status(404).json({
        error: 'Asset not found',
      });
    }

    const nextStatus = transitions[asset.status]?.[action];

    if (!nextStatus) {
      await client.query('ROLLBACK');

      return res.status(409).json({
        error: 'Invalid lifecycle transition',
      });
    }

    const holder =
      action === 'assign' ? assignedTo.trim() : null;

    const updated = await client.query(
      `UPDATE assets
       SET status = $1,
           assigned_to = $2,
           updated_at = NOW()
       WHERE id = $3
       RETURNING *`,
      [nextStatus, holder, id]
    );

    await client.query(
      `INSERT INTO asset_events
       (asset_id, event_type, from_status,
        to_status, performed_by, details)
       VALUES ($1, $2, $3, $4, $5, $6::jsonb)`,
      [
        id,
        eventNames[action],
        asset.status,
        nextStatus,
        getActor(),
        JSON.stringify(
          action === 'assign'
            ? { assignedTo: holder }
            : {}
        ),
      ]
    );

    await client.query('COMMIT');

    return res.json(updated.rows[0]);
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
});

app.get('/assets/:id/history', async (req, res) => {
  const id = getAssetId(req.params.id);

  if (!id) {
    return res.status(400).json({
      error: 'Invalid asset ID',
    });
  }

  const result = await pool.query(
    `SELECT event_type, from_status, to_status,
            performed_by, details, created_at
     FROM asset_events
     WHERE asset_id = $1
     ORDER BY created_at DESC, id DESC`,
    [id]
  );

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

app.use((error, req, res, next) => {
  console.error(error);

  res.status(500).json({
    error: 'Internal server error',
  });
});

app.listen(process.env.PORT || 3000, () => {
  console.log('Asset lifecycle API is running');
});

This example demonstrates the core workflow. It is not a complete production authentication or authorization implementation.

Why Use `SELECT ... FOR UPDATE`?

Imagine two IT administrators trying to assign the same available laptop at almost the same time.

Without proper concurrency control, both requests might read the asset as available before either update completes.

PostgreSQL's FOR UPDATE acquires a row-level lock inside the transaction.

The second transaction must wait for the first to finish. It then reads the updated status and can reject an invalid transition.

This is one way to prevent conflicting changes to an asset's current state.

Why Keep Updates and Events in One Transaction?

Suppose the application successfully changes an asset to assigned but fails to write the assignment event.

Now the system shows a current holder without a corresponding historical record.

By wrapping both operations in a transaction, we ensure they are committed together or rolled back together.

That is essential when the asset history needs to remain consistent with operational changes.

7. Testing the Asset Lifecycle API

Start the server:

npm start

Register an Asset

curl -X POST http://localhost:3000/assets \
  -H "Content-Type: application/json" \
  -d '{
    "assetTag": "LAP-001",
    "name": "Dell Latitude Laptop"
  }'

The response will contain the newly registered asset, initially marked as available.

Assign the Asset

Replace 1 with the ID returned during registration if necessary.

curl -X POST \
  http://localhost:3000/assets/1/transition \
  -H "Content-Type: application/json" \
  -d '{
    "action": "assign",
    "assignedTo": "employee-101"
  }'

The asset now moves from available to assigned.

The assignment also creates a historical event.

Return the Asset

curl -X POST \
  http://localhost:3000/assets/1/transition \
  -H "Content-Type: application/json" \
  -d '{
    "action": "return"
  }'

After a valid return, its current status becomes available, and the previous holder is cleared.

View Lifecycle History

curl http://localhost:3000/assets/1/history

A typical response includes:

[
  {
    "event_type": "returned",
    "from_status": "assigned",
    "to_status": "available",
    "performed_by": "demo-operator",
    "details": {},
    "created_at": "..."
  },
  {
    "event_type": "assigned",
    "from_status": "available",
    "to_status": "assigned",
    "performed_by": "demo-operator",
    "details": {
      "assignedTo": "employee-101"
    },
    "created_at": "..."
  }
]

This is much more useful than knowing only the asset's latest status.

It gives the IT team a chronological record of important activities.

8. Adding Warranty Tracking

Asset lifecycle management is not just about assignment and return.

IT departments also need visibility into warranty expiry, service contracts, and maintenance schedules.

Our assets table already includes warranty_expires_on.

We can query warranties expiring within the next 30 calendar days:

SELECT
    id,
    asset_tag,
    name,
    warranty_expires_on
FROM assets
WHERE status <> 'retired'
  AND warranty_expires_on >= CURRENT_DATE
  AND warranty_expires_on <= CURRENT_DATE + 30
ORDER BY warranty_expires_on ASC;

A scheduled job could run this query daily and notify the responsible team.

For production, the notification system should also keep delivery records to avoid repeatedly sending identical alerts.

A simple notification_log table with an appropriate uniqueness rule can help prevent duplicate reminders.

You should also distinguish between equipment warranties and software subscription renewals. They may involve different records, owners, and renewal policies.

9. Preparing the System for Production

The API we've created demonstrates state management and transaction safety, but several additional controls are necessary before using it with real company data.

Authentication and Authorization

Our example uses a fixed demo-operator identity.

A production system must replace that with verified identity information from its authentication layer.

Permissions should distinguish between users who can view equipment, assign assets, approve transfers, manage maintenance, and retire equipment.

Do not trust an identity or permission value simply because it appears in an incoming HTTP request.

Input Validation

Validate request bodies with a schema validation library such as Zod or another maintained solution.

Examples include:

Enforcing valid asset identifiers
Limiting string lengths
Rejecting unsupported actions
Validating dates and serial numbers
Applying consistent error responses

Tenant Isolation

If multiple organizations use the same application, add organization ownership to relevant tables.

Every operation must then enforce organization-level access controls. For stronger isolation, consider PostgreSQL row-level security with correctly scoped database roles and policies.

Transaction Safety

Always release database clients, even when an operation fails.

Use parameterized SQL queries to separate untrusted values from query text.

For higher availability and resilience, consider connection limits, transaction timeouts, retry strategies for appropriate transient failures, and graceful shutdown.

Auditing

A useful audit record should capture:

Who performed the action
What changed
When it changed
Which asset was affected
Relevant business context

For stronger accountability, preserve the previous assignee, current assignee, approved request, and reason for each handover.

Other Production Requirements

A complete implementation should also include:

Database migrations
Automated tests for valid and invalid transitions
Pagination for event history
Backup and recovery procedures
Error monitoring and structured logging
Idempotency for retried commands
Asset reconciliation and periodic audits
Secure equipment disposal procedures
Protection of historical events from unauthorized changes

An important test case is sending two simultaneous assignment requests for the same device and confirming that only one succeeds.

10. Build Your Own or Use an Existing Platform?

Building an asset management backend is a useful engineering project.

But maintaining a complete IT asset management platform involves more than storing equipment records.

Real organizations often need QR code scanning, signed handovers, employee offboarding, maintenance workflows, location tracking, reports, and role-based access.

If your goal is to learn backend architecture, building the system yourself is an excellent exercise.

If your goal is operational asset management, it may be more practical to evaluate an existing solution.

For example, Stackroom's IT asset management platform provides equipment assignment, custody records, and lifecycle visibility as part of a broader asset management workflow.

The right choice depends on whether you're solving a software engineering problem or an equipment management problem.

11. Final Thoughts

An effective IT asset lifecycle management system needs more than an assets table and a few CRUD endpoints.

The most important architectural decisions are often the less visible ones:

Keeping current state separate from historical activity
Defining valid lifecycle transitions
Using database transactions for consistent updates
Preventing conflicting assignments
Enforcing constraints at the database level
Preserving meaningful operational history

Node.js and PostgreSQL provide a solid foundation for implementing these patterns.

The same principles also apply to equipment checkout systems, inventory services, rental platforms, and other applications where resources move between people or operational states.

Start with a small, reliable lifecycle model. Add workflows as your actual business requirements become clear.

A system that accurately answers "Who has this asset, and how did it get here?" is already solving one of the most important problems in equipment management.

References and Further Reading

How to Build a QR Code-Based IT Asset Tracking System with Node.js and PostgreSQL

Arbaz — Thu, 29 Oct 2026 07:28:47 +0000

Most businesses have no shortage of equipment records. The challenge begins when someone needs to identify a particular laptop, monitor, or other device and find its details quickly.

An asset might have a serial number, an internal inventory ID, a spreadsheet entry, and an employee assignment record. But connecting the physical device to its digital record can still take time.

QR codes provide a simple bridge between physical equipment and an asset management system.

In this tutorial, we'll design a basic QR-based IT asset tracking application using Node.js, Express, PostgreSQL, and the qrcode package.

We'll cover the data model, QR generation, asset lookup endpoints, and security considerations.

1. How QR-Based Asset Tracking Works

The concept is straightforward:

Register an IT asset in a database.
Generate a unique public identifier for that asset.
Encode an asset lookup URL inside a QR code.
Attach the QR code label to the physical equipment.
Scan the code to open its digital asset record.

For example, a laptop might have:

Field	Example
Asset Name	Dell Latitude Laptop
Internal Asset ID	LT-001
Serial Number	DEVICE-SERIAL-001
Status	Available
Location	IT Storage Room

The QR code contains a lookup URL, rather than a serial number or confidential employee information.

That separation is important. A physical asset label can be seen or photographed by anyone nearby.

2. Technology Stack

We'll use:

Node.js: JavaScript runtime for the backend.
Express: HTTP routing and API handling.
PostgreSQL: Relational database for asset records.
pg: PostgreSQL client for Node.js.
qrcode: QR code generation.
dotenv: Local environment configuration.

Start a new project:

mkdir qr-asset-tracker
cd qr-asset-tracker
npm init -y
npm install express pg qrcode dotenv

Use a currently supported Node.js LTS release.

3. Create the Database Schema

For this example, we need an asset table containing an internal ID and a separate public lookup token.

CREATE TABLE assets (
  id BIGSERIAL PRIMARY KEY,
  asset_code VARCHAR(50) UNIQUE NOT NULL,
  public_id UUID UNIQUE NOT NULL,
  name VARCHAR(150) NOT NULL,
  serial_number VARCHAR(150),
  status VARCHAR(30) NOT NULL DEFAULT 'available',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT valid_asset_status
    CHECK (status IN (
      'available',
      'assigned',
      'maintenance',
      'retired'
    ))
);

The asset_code is an internal business identifier, such as LT-001.

The public_id is a random UUID used in the QR lookup URL. This prevents the public URL from directly exposing sequential database IDs.

However, a UUID is an identifier, not an authentication mechanism.

4. Configure the Application

Create a .env file:

DATABASE_URL=postgresql://localhost:5432/asset_tracker
PORT=3000
PUBLIC_BASE_URL=http://localhost:3000
ADMIN_API_KEY=replace-with-a-long-random-secret

Create a server.js file:

require("dotenv").config();

const express = require("express");
const { Pool } = require("pg");
const QRCode = require("qrcode");
const { randomUUID, timingSafeEqual } = require("node:crypto");

const app = express();

app.disable("x-powered-by");
app.use(express.json({ limit: "10kb" }));

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

function requireAdmin(req, res, next) {
  const expected = process.env.ADMIN_API_KEY;
  const supplied = req.get("x-admin-key");

  if (!expected || !supplied) {
    return res.sendStatus(401);
  }

  const a = Buffer.from(supplied);
  const b = Buffer.from(expected);

  if (a.length !== b.length ||
      !timingSafeEqual(a, b)) {
    return res.sendStatus(401);
  }

  next();
}

The administrator API key is used here only to keep the demonstration small. A production system should use proper user authentication and role-based authorisation.

5. Create an Asset Registration Endpoint

Next, register equipment and generate a random public lookup ID.

Add this endpoint to server.js:

app.post("/api/assets", requireAdmin, async (req, res) => {
  const { assetCode, name, serialNumber } = req.body;

  if (
    typeof assetCode !== "string" ||
    typeof name !== "string" ||
    !assetCode.trim() ||
    !name.trim() ||
    assetCode.length > 50 ||
    name.length > 150 ||
    (serialNumber != null &&
      (typeof serialNumber !== "string" ||
       serialNumber.length > 150))
  ) {
    return res.status(400).json({
      error: "Invalid asset details"
    });
  }

  try {
    const result = await pool.query(
      `INSERT INTO assets
       (asset_code, public_id, name, serial_number)
       VALUES ($1, $2, $3, $4)
       RETURNING id, asset_code, public_id, name, status`,
      [
        assetCode.trim(),
        randomUUID(),
        name.trim(),
        serialNumber ?? null
      ]
    );

    res.status(201).json(result.rows[0]);
  } catch (error) {
    if (error.code === "23505") {
      return res.status(409).json({
        error: "Asset code already exists"
      });
    }

    console.error("Asset registration failed");
    res.sendStatus(500);
  }
});

Parameterized SQL statements help prevent SQL injection.

For a production implementation, additional validation, permission checks, rate limiting, and audit logging should be included.

6. Generate QR Codes for Registered Assets

After registration, generate a QR code that links to the corresponding asset record.

app.get(
  "/api/assets/:publicId/qr",
  requireAdmin,
  async (req, res) => {
    try {
      const result = await pool.query(
        "SELECT public_id FROM assets WHERE public_id = $1",
        [req.params.publicId]
      );

      if (!result.rowCount) {
        return res.sendStatus(404);
      }

      const assetUrl =
        `${process.env.PUBLIC_BASE_URL}/assets/` +
        result.rows[0].public_id;

      const qr = await QRCode.toBuffer(assetUrl, {
        type: "png",
        width: 300,
        margin: 2,
        errorCorrectionLevel: "M"
      });

      res.type("png").send(qr);
    } catch (error) {
      console.error("QR generation failed");
      res.sendStatus(500);
    }
  }
);

A generated QR code can then be printed on a label and attached to the device.

For deployment, PUBLIC_BASE_URL should point to the application's actual HTTPS domain.

7. Retrieve Asset Details After Scanning

When the QR code is scanned, the browser opens a lookup URL.

For example:

https://assets.example.com/assets/{publicId}

That public-facing route should display only information authorised for the person scanning the code.

For this demonstration, we'll expose only basic identification information.

app.get("/assets/:publicId", async (req, res) => {
  try {
    const result = await pool.query(
      `SELECT asset_code, name
       FROM assets
       WHERE public_id = $1`,
      [req.params.publicId]
    );

    if (!result.rowCount) {
      return res.sendStatus(404);
    }

    res.json(result.rows[0]);
  } catch (error) {
    console.error("Asset lookup failed");
    res.sendStatus(500);
  }
});

app.listen(process.env.PORT || 3000, () => {
  console.log("Asset tracker API started");
});

The route returns an identification record in JSON. A frontend can use the same endpoint to display a mobile-friendly asset page.

A complete application could let authorised employees view assignment history, maintenance records, or return status after logging in.

8. Test the Workflow

Start the server:

node server.js

Create an asset:

curl -X POST http://localhost:3000/api/assets \
  -H "Content-Type: application/json" \
  -H "x-admin-key: YOUR_ADMIN_KEY" \
  -d '{
    "assetCode": "LT-001",
    "name": "Dell Latitude Laptop",
    "serialNumber": "DEVICE-SERIAL-001"
  }'

The response contains a public_id.

Use that identifier to request the QR image through the authenticated QR endpoint:

GET /api/assets/{publicId}/qr

Scanning the QR code opens the asset lookup URL.

For testing on a physical phone, remember that localhost refers to the phone itself, not your development computer. Use a reachable development URL.

9. Security Considerations

QR-based asset tracking introduces several security concerns.

Never place confidential information directly in QR codes

Avoid encoding employee names, personal email addresses, internal credentials, or sensitive device information.

Authorise sensitive record access

Anyone who scans a label might obtain its URL. A random UUID does not replace authentication.

Restrict inventory modifications

Asset registration, assignment updates, deletions, and maintenance changes should be limited to authorised roles.

Protect against label replacement

A malicious person could replace a QR label with a different destination. Use trusted HTTPS domains, clearly branded labels, and appropriate physical verification procedures.

Maintain audit records

Recording who changed an asset's status and when is important for accountability. Audit events should be protected against unauthorised changes.

10. Moving From a Prototype to Production

The implementation above demonstrates the core identification flow. It is not a complete production asset management system.

A production-ready application would typically add:

Employee and department relationships
Equipment assignment and return workflows
Role-based access control
Maintenance history
Inventory reconciliation
Audit logs
Secure QR label printing
Rate limiting and operational monitoring

The goal is not merely to generate QR codes. It is to connect the physical equipment lifecycle with reliable digital records.

Conclusion

QR codes provide a practical way to connect physical IT equipment with a digital inventory system.

By combining unique public identifiers, a PostgreSQL database, and a Node.js API, developers can establish the foundation for equipment identification and retrieval.

From there, teams can extend the application with assignments, returns, maintenance tracking, and reporting.

For teams exploring practical equipment tracking workflows, Stackroom is another resource worth reviewing.

How would you handle QR code replacement, offline scanning, or asset ownership verification in a production system?

I'd be interested to hear how other developers approach these challenges.

How to Build an IT Asset Assignment and Return System (SQL + REST API)

Arbaz — Mon, 19 Oct 2026 09:35:48 +0000

Imagine your company has 50 employees and 80 IT assets, including laptops, monitors, and smartphones.

An employee leaves the organisation. HR confirms their departure, but IT cannot confirm whether their laptop was returned.

The asset register still lists the employee as its owner. Nobody knows whether the device is sitting in a storage room, being used by someone else, or missing.

This is more than an inventory problem. It's a workflow and accountability problem.

A reliable IT asset management system should answer five questions:

What equipment does the company own?
Who currently has each device?
When was the equipment assigned?
Has it been physically returned and inspected?
What happened to the device throughout its lifecycle?

In this tutorial, we'll design an IT asset assignment and return system using PostgreSQL and REST APIs.

We'll cover database relationships, asset lifecycle management, assignment transactions, duplicate prevention, audit logs, employee offboarding, and practical failure scenarios.

By the end, you'll understand how to design the core backend workflow for an IT equipment tracking application.

Scope: This tutorial covers a practical reference design for individual IT assets. It is not a complete production application. Authentication, migrations, notifications, API framework code, and operational deployment need additional implementation.

1. Start With a Real-World Asset Tracking Scenario

Let's use one laptop throughout the tutorial.

Asset: Dell Latitude 5440

Asset tag: LAP-1001

Employee: Sarah

Initial status: AVAILABLE

The following events occur:

Step	Event	Expected result
1	IT registers the laptop	Asset becomes available
2	Sarah receives the laptop	An assignment record is created
3	Sarah uses it for three months	Assignment remains active
4	Sarah leaves the company	A return is requested
5	Sarah returns the laptop	IT verifies receipt and condition
6	IT discovers screen damage	Asset moves to maintenance
7	Laptop is repaired	Asset becomes available again

The application must preserve every important transition.

For example, returning the laptop should not delete Sarah's assignment history. Instead, the system should close the existing assignment and retain it for future reference.

This becomes particularly important during employee offboarding, inventory reconciliation, and internal audits.

2. Define the Asset Lifecycle

Before creating database tables, establish the asset's possible states.

A simplified lifecycle is:

AVAILABLE
    |
    | Assign to employee
    v
ASSIGNED
    |
    | Physical return verified
    v
INSPECTION
    |
    +---- Good condition ----> AVAILABLE
    |
    +---- Damaged -----------> MAINTENANCE
                                   |
                                   | Repaired
                                   v
                               AVAILABLE

AVAILABLE / MAINTENANCE
    |
    | Approved retirement
    v
RETIRED

These states describe the asset itself.

A return request, however, describes a separate business workflow.

For example, when Sarah requests to return her laptop, its asset status should remain ASSIGNED until IT confirms physical receipt.

Otherwise, the application could incorrectly make an unreturned laptop available for another employee.

Design principle: Separate the physical asset lifecycle from requests, approvals, and other workflow states.

Employee count also isn't a reliable threshold for adopting software. A company with 10 employees and hundreds of shared devices may need structured tracking sooner than a company with 50 employees and very little equipment.

The trigger is operational complexity, not headcount alone.

3. Design the PostgreSQL Database

A practical starting point includes five tables:

Table	Responsibility
`employees`	Employee identity and active status
`assets`	Equipment inventory and current status
`asset_assignments`	Assignment history and verified returns
`return_requests`	Return workflow before physical receipt
`asset_events`	Audit trail of asset activity

We'll also use an app_users table to identify authenticated administrators responsible for assignments, inspections, and other operations.

3.1 Employees and application users

Employees and application users are related but not necessarily identical.

An employee may receive equipment without having access to the asset management application's administrative interface.

CREATE TABLE employees (
    id BIGSERIAL PRIMARY KEY,
    employee_code VARCHAR(100) UNIQUE NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    department VARCHAR(100),
    is_active BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE app_users (
    id BIGSERIAL PRIMARY KEY,
    employee_id BIGINT UNIQUE REFERENCES employees(id),
    role VARCHAR(50) NOT NULL
);

For simplicity, we're using database IDs for application users. In a real system, authentication identities may come from an external identity provider such as Microsoft Entra ID.

User authentication and permissions must be validated before executing protected operations.

3.2 Assets table

CREATE TABLE assets (
    id BIGSERIAL PRIMARY KEY,
    asset_tag VARCHAR(100) UNIQUE NOT NULL,
    serial_number VARCHAR(150),
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    status VARCHAR(30) NOT NULL DEFAULT 'AVAILABLE',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT valid_asset_status CHECK (
        status IN (
            'AVAILABLE',
            'ASSIGNED',
            'INSPECTION',
            'MAINTENANCE',
            'RETIRED'
        )
    )
);

An asset record represents the current state of a device.

Example:

Asset tag	Device	Status
LAP-1001	Dell Latitude 5440	ASSIGNED
LAP-1002	MacBook Air	AVAILABLE
MON-2001	Dell Monitor	MAINTENANCE

Use the internal asset tag as the inventory identifier and keep the manufacturer's serial number separately.

The serial number is useful for warranty verification and hardware identification, while the asset tag identifies the equipment within your business workflow.

3.3 Asset assignments table

This is the most important table in the system.

CREATE TABLE asset_assignments (
    id BIGSERIAL PRIMARY KEY,
    asset_id BIGINT NOT NULL REFERENCES assets(id),
    employee_id BIGINT NOT NULL REFERENCES employees(id),

    assigned_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expected_return_at TIMESTAMPTZ,

    returned_at TIMESTAMPTZ,
    return_condition VARCHAR(30),

    assigned_by BIGINT NOT NULL REFERENCES app_users(id),
    received_by BIGINT REFERENCES app_users(id),

    CHECK (
        returned_at IS NULL
        OR returned_at >= assigned_at
    ),

    CHECK (
        returned_at IS NULL
        OR received_by IS NOT NULL
    )
);

Instead of overwriting the assigned employee in the assets table, every new assignment creates a historical record.

For example:

Asset	Employee	Assigned	Returned
LAP-1001	Sarah	2026-07-01	2026-10-19
LAP-1001	James	2026-10-22	—

This preserves the chain of custody.

In this simplified model, a null returned_at means the assignment remains open.

More advanced systems should model lost equipment, disputed custody, and approved write-offs separately instead of marking those situations as normal physical returns.

4. Prevent the Same Laptop From Being Assigned Twice

Consider this situation:

Two IT administrators are assigning LAP-1001.

Both open the asset record when its status is AVAILABLE.

If the application simply checks availability and then inserts an assignment, concurrent requests may conflict.

We need a database-level safeguard.

Use a partial unique index

CREATE UNIQUE INDEX one_open_assignment_per_asset
ON asset_assignments (asset_id)
WHERE returned_at IS NULL;

This tells PostgreSQL that an asset may have many historical assignments, but only one assignment where returned_at is null.

This is a genuine database constraint, not merely frontend validation.

It protects against conflicting inserts even if requests arrive concurrently.

PostgreSQL officially supports partial unique indexes for enforcing uniqueness over subsets of rows.

Reference: PostgreSQL — Partial Indexes

5. Design the Asset Assignment REST API

We can expose the following endpoint:

POST /api/assignments
Content-Type: application/json

Request:

{
  "asset_id": 101,
  "employee_id": 25,
  "expected_return_at": "2026-12-31T17:00:00Z"
}

The authenticated user performing the assignment should come from the backend authentication context, not a client-supplied administrator ID.

Before creating the assignment, verify:

The caller is authorised to assign equipment.
The asset exists.
The asset is available.
The employee exists and is active.
Both belong to the caller's permitted organisation.
The request contains valid data.

Then run the state-changing operations inside a database transaction.

Example assignment transaction

The following is a transaction template; the application must perform the validation indicated by the comments and roll back on failure.

BEGIN;

-- Lock the asset row to serialize competing assignments.
SELECT id, status
FROM assets
WHERE id = 101
FOR UPDATE;

-- Application must verify status = 'AVAILABLE'
-- and validate the intended employee and permissions.

INSERT INTO asset_assignments (
    asset_id,
    employee_id,
    expected_return_at,
    assigned_by
)
VALUES (
    101,
    25,
    '2026-12-31T17:00:00Z',
    7
);

UPDATE assets
SET status = 'ASSIGNED'
WHERE id = 101
  AND status = 'AVAILABLE';

-- Verify exactly one asset row was updated.

COMMIT;

If validation fails, the application must issue ROLLBACK instead of continuing.

The ID 7 represents the already authenticated administrator in this example.

Why use `FOR UPDATE`?

PostgreSQL's SELECT ... FOR UPDATE locks the selected rows against conflicting updates until the transaction ends.

When two cooperating assignment transactions try to lock the same asset, one must wait.

After the first transaction completes, the second can observe the updated state and reject an assignment that is no longer valid.

The partial unique index adds another layer of protection.

Reference: PostgreSQL — Explicit Locking

Important: Both assignment and return operations should use a consistent locking strategy. Otherwise, different workflows may still create race conditions or deadlocks.

6. Design the Equipment Return Workflow

Now assume Sarah is leaving the company.

HR initiates offboarding, and IT requests the return of LAP-1001.

We should distinguish the return request from the physical return.

Return requests table

CREATE TABLE return_requests (
    id BIGSERIAL PRIMARY KEY,

    assignment_id BIGINT NOT NULL
        REFERENCES asset_assignments(id),

    status VARCHAR(30) NOT NULL DEFAULT 'PENDING',

    requested_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    completed_at TIMESTAMPTZ,

    CHECK (
        status IN (
            'PENDING',
            'CANCELLED',
            'COMPLETED'
        )
    )
);

This design allows IT to record that a return has been requested while the laptop remains assigned.

Production systems should add constraints governing active requests, completion timestamps, and valid status transitions.

Return workflow

Return Requested
       |
       v
Employee Brings Device
       |
       v
IT Verifies Asset Identity
       |
       v
IT Inspects Device
       |
       +---- Good -----> AVAILABLE
       |
       +---- Damaged --> MAINTENANCE

If Sarah returns the laptop with a damaged screen, the workflow should record the verified return and move the device to maintenance.

Return API

POST /api/assignments/5001/returns
Content-Type: application/json

Request:

{
  "condition": "DAMAGED",
  "notes": "Screen cracked; charger received"
}

The server should determine the receiving administrator from the authenticated session and record the receipt timestamp.

Do not allow a caller to mark someone else's asset as returned without appropriate authorisation.

Return transaction

The following represents the core SQL operations after validation.

BEGIN;

-- Lock the asset associated with assignment 5001,
-- then lock and validate the open assignment.

UPDATE asset_assignments
SET
    returned_at = NOW(),
    return_condition = 'DAMAGED',
    received_by = 7
WHERE id = 5001
  AND returned_at IS NULL
RETURNING asset_id;

-- Application verifies that exactly one assignment
-- was closed and reads the returned asset_id.

UPDATE assets
SET status = 'MAINTENANCE'
WHERE id = 101
  AND status = 'ASSIGNED';

-- Verify the asset update succeeded.
-- Complete the applicable return request and
-- insert an audit event before committing.

COMMIT;

The asset lock and authorisation checks are intentionally described rather than fully implemented in this SQL fragment.

They are required for a correct production workflow. In particular, verify that the returned assignment's asset_id matches the asset being updated, and never update an unrelated asset by trusting a fixed ID.

All changes must succeed together or be rolled back.

7. Keep an Audit Trail of Asset Activities

Assignment history shows when an employee possessed equipment.

An audit log records additional operational events.

For example:

Asset registered
Equipment assigned
Return requested
Return verified
Asset sent for maintenance
Asset repaired
Asset retired

Create an event table:

CREATE TABLE asset_events (
    id BIGSERIAL PRIMARY KEY,

    asset_id BIGINT NOT NULL REFERENCES assets(id),
    actor_id BIGINT REFERENCES app_users(id),

    event_type VARCHAR(50) NOT NULL,
    details JSONB NOT NULL DEFAULT '{}'::jsonb,

    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Example event:

{
  "event_type": "ASSET_RETURNED",
  "asset_id": 101,
  "actor_id": 7,
  "details": {
    "assignment_id": 5001,
    "condition": "DAMAGED",
    "next_status": "MAINTENANCE"
  }
}

Audit records should be generated in the same transaction as their corresponding asset operation.

Otherwise, the application could successfully change an asset's status while failing to record who made the change.

For stronger auditability, restrict the ability to modify or delete event records and establish appropriate retention policies.

8. Integrate Employee Onboarding and Offboarding

Asset management becomes especially useful when connected to employee lifecycle processes.

Onboarding example

Sarah joins the company.

The sequence could be:

HR creates Sarah's employee record.
IT receives an equipment request.
An administrator selects an available laptop.
The system creates an assignment.
Sarah acknowledges receipt.
The asset status becomes ASSIGNED.

The acknowledgement should reference the actual equipment issued, including accessories when appropriate.

Offboarding example

Three months later, Sarah resigns.

IT needs to identify all equipment still assigned to her.

SELECT
    a.asset_tag,
    a.name,
    aa.assigned_at,
    aa.expected_return_at
FROM asset_assignments aa
JOIN assets a
    ON a.id = aa.asset_id
WHERE aa.employee_id = 25
  AND aa.returned_at IS NULL;

This produces a list of outstanding assignments for the employee.

That list can serve as the basis for an offboarding checklist.

However, marking the employee inactive should not automatically close the assignments.

Employment status and physical asset custody are different facts.

A laptop remains outstanding until its return, loss, or write-off process is properly resolved.

9. Handle Production Edge Cases

A reliable backend needs to handle more than successful assignment and return requests.

Case 1: Two administrators assign the same asset

Expected behaviour:

Only one assignment succeeds.
The second receives an appropriate conflict response.
The database never contains two open assignments.

A suitable response might use HTTP 409 Conflict.

Case 2: An employee reports a lost laptop

Do not record the device as physically returned.

Instead, introduce a loss incident or disposition workflow.

A more advanced schema may require a separate LOST asset status and an assignment closure reason.

Case 3: The return API is called twice

Imagine a return succeeds, but the client's network connection fails before receiving the response.

The client retries.

Without retry handling, the application may attempt the same workflow twice.

For mutation endpoints, an idempotency key can help identify repeated operations.

Example:

Idempotency-Key: return-5001-20261019

A reliable implementation stores the operation key, request identity and outcome in a durable store with a uniqueness constraint.

When the same request is retried, it should return the previously recorded result rather than performing a second return.

If the same key is reused with different request parameters, the API should reject it.

A client-supplied header alone does not provide idempotency; the backend must implement the corresponding persistence and concurrency rules.

Case 4: An employee transfers departments

The asset may remain with the same employee.

Do not automatically create a new physical equipment assignment simply because the employee's department changes.

Record the organisational change separately if it matters for reporting.

Case 5: IT receives a damaged laptop

Close the assignment only after verifying physical receipt.

Record the condition, then place the asset into maintenance instead of making it available.

10. Security: Protect Employee and Asset Records

Asset tracking applications contain operational information that should not be publicly accessible.

A typical permissions model might be:

Role	Responsibility
Employee	View their own assigned equipment
IT Administrator	Assign and receive equipment
Operations Manager	View permitted inventory and reports
Auditor	Read-only access to authorised history
Administrator	Manage users, roles, and settings

Permission checks must happen on the server.

For example, this endpoint:

GET /api/assets/101/history

must not reveal asset history solely because a user knows asset ID 101.

The backend should verify access to that particular asset.

This is especially important for multi-tenant SaaS systems, where every organisation must be isolated from others.

For a multi-tenant architecture, incorporate organisation identifiers, tenant-scoped relationships, and enforce the access policy consistently. PostgreSQL row-level security may provide an additional defence where suitable.

OWASP identifies broken object-level authorisation as a major API security risk.

Reference: OWASP API1:2023 — Broken Object Level Authorization

11. Test the Workflow Before Deployment

A few carefully selected tests can expose serious business-logic problems.

Test	Expected result
Assign an available laptop	Assignment created successfully
Assign an already assigned laptop	Request rejected
Submit simultaneous assignments	Only one succeeds
Request a return	Asset remains assigned
Verify a good-condition return	Asset moves to available
Verify a damaged return	Asset moves to maintenance
Repeat an already completed return	No duplicate side effects
Offboard an employee	Outstanding devices remain visible
Access another organisation's asset	Request denied
Inspect historical assignments	Previous records remain intact

Also test failures between database operations, database constraint violations, transaction rollback, and recovery from network timeouts.

A simple happy-path demonstration isn't enough to establish that an asset tracking system is reliable.

12. Spreadsheet vs Dedicated Asset Management Software

Not every company needs a custom asset management platform.

A spreadsheet can be a reasonable starting point when:

There are relatively few devices.
Equipment rarely changes hands.
One person is responsible for updates.
Basic inventory visibility is sufficient.

Dedicated software becomes more useful when:

Multiple administrators manage equipment.
Employees frequently join, leave, or transfer.
Assets regularly move between locations.
Teams need reliable assignment histories.
Manual reconciliation takes significant time.
Consistent audit records are important.

The goal should be to solve operational problems, not to introduce software merely because an organisation reaches a particular size.

If you're building such a system yourself, start with a reliable assignment and return workflow before adding features such as QR scanning, analytics dashboards, automated reminders, or hardware integrations.

Conclusion

Designing an IT asset assignment and return system is fundamentally about maintaining trustworthy records of equipment custody.

The most important engineering decisions are:

Separate asset inventory from assignment history.
Define clear asset and return lifecycle states.
Prevent conflicting assignments at the database level.
Use transactions for operations that modify related records.
Record verified returns instead of assuming equipment was received.
Preserve audit history.
Handle concurrency, retries, and exceptional cases.
Enforce appropriate access controls.

A system that handles these fundamentals correctly can provide a strong foundation for additional equipment management features as an organisation grows.

The key question is not simply “Where is this laptop?” It's “Can we reliably explain who had it, what happened to it, and what its current status actually means?”

That is what turns a basic inventory table into a dependable IT asset management workflow.

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

Arbaz — Sat, 17 Oct 2026 12:40:47 +0000

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:

Design a relational model for physical assets and employee custody.
Prevent duplicate active equipment assignments.
Build transactional checkout and return endpoints.
Protect shared assets from concurrent updates.
Record custody events.
Test common failure scenarios.
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', 'alice@example.com'),
    ('Bob Smith', 'bob@example.com');

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:

The checkout history gains a new active assignment.
The asset status becomes checked_out.
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:

Model assets and checkout periods separately.
Use database constraints for critical invariants.
Wrap multi-step state changes in transactions.
Lock shared asset records consistently.
Retain historical checkout information.
Record custody events.
Test duplicate requests, concurrency, and failure conditions.
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

Why Your Asset Tracking System Needs an Audit Trail (Not Just a Database)

Arbaz — Fri, 16 Oct 2026 11:41:56 +0000

*Your database can tell you who has a laptop today. But can it explain how that laptop got there?
*
Imagine you're building an asset tracking system for a company with 500 employees.

Your application manages laptops, monitors, smartphones, and other company equipment.

Everything looks fine.

You have a database, an asset dashboard, and an API to assign equipment to employees.

Then one day, an IT administrator notices something strange.

A laptop originally assigned to Sarah now appears under John's name.

Sarah says she returned it two weeks ago.

John says he received it yesterday.

The database shows:

Asset: LAP-1042

Assigned To: John

Status: Assigned

Location: London Office

Technically, the data is correct.

But there's a problem.

You cannot answer:

Who transferred the laptop?
When did the assignment change?
Was it returned to IT first?
Who updated the record?
Was the transfer authorized?

Your database tells you the current state.

It doesn't necessarily tell you the story behind that state.

This is why an asset management audit trail matters.

And it's something developers should think about before their application reaches production.

1. A Database Stores State. An Audit Trail Explains Change.

Most developers begin an asset management application with a straightforward database table.

For example:

This table is useful.

It answers questions about the current inventory.

But suppose Sarah transfers a laptop to John.

Your backend updates the assignment:

UPDATE assets SET assigned_to = 'John' WHERE id = 1042;

The previous value disappears from the current-state table.

Unless you have another mechanism for recording changes, the application no longer knows that Sarah previously held the laptop.

Now imagine this happening hundreds of times across multiple departments.

Assets get reassigned, returned, repaired, moved, and retired.

Every update modifies the current state.

Without a separate change history, reconstructing what happened becomes difficult.

An audit trail solves this problem by recording important events alongside the current asset record.

2. What Exactly Is an Asset Management Audit Trail?

An audit trail is a chronological record of actions or changes affecting an asset.

Instead of only storing the latest assignment, it records the significant events that led to the current state.

For example:

Timestamp	Event	Actor	Details
Oct 1, 09:00	Asset created	IT Admin	Laptop LAP-1042 added
Oct 2, 10:30	Assigned	IT Admin	Assigned to Sarah
Oct 10, 14:15	Returned	IT Admin	Returned by Sarah
Oct 11, 11:00	Assigned	IT Admin	Assigned to John

Now the system can answer more useful questions.

Who previously held the laptop?

When was it returned?

Who recorded the new assignment?

Was the asset available between transfers?

This becomes valuable for physical asset management, financial systems, administrative applications, and many other business workflows.

An audit trail is not just about security.

It's also about understanding the lifecycle of important business data.

3. Audit Logs vs Application Logs: What's the Difference?

Developers sometimes assume normal application logging is enough.

For example, your backend might produce:

INFO: Asset assignment updated successfully

This helps developers understand that an operation ran.

But it doesn't necessarily explain which equipment was affected, what changed, or who initiated the action.

Application logs and audit logs serve different purposes.

Application logs primarily help engineers monitor application behavior and troubleshoot technical issues.

Audit logs record business-relevant actions that may need to be reviewed later.

Consider an employee equipment transfer.

An application log might say:

PUT /assets/1042 → 200 OK

An audit event should provide more context:

Asset ID: 1042
Action: Assignment changed
Previous custodian: Sarah
New custodian: John
Performed by: IT Admin
Timestamp: 2026-10-11T11:00:00Z
Reason: Department transfer

The application log tells you that an API request succeeded.

The audit trail explains what happened to the business record.

Both are useful, but they are not interchangeable.

4. Designing an Audit Trail Database

Let's look at a simple design.

You already have an assets table containing the current state.

Now introduce an asset_audit_logs table.

A possible PostgreSQL schema is:

assets

Column	Type	Purpose
id	UUID	Unique asset identifier
name	TEXT	Asset name
assigned_to	UUID	Current custodian
status	TEXT	Current asset status
updated_at	TIMESTAMPTZ	Last update time

asset_audit_logs

Column	Type	Purpose
id	UUID	Unique event identifier
asset_id	UUID	Related asset
action	TEXT	Type of action
actor_id	UUID	User who performed it
previous_value	JSONB	Previous relevant state
new_value	JSONB	Updated relevant state
reason	TEXT	Optional explanation
created_at	TIMESTAMPTZ	Event timestamp

The audit table should record changes such as:

Asset creation
Assignment
Return
Custodian transfer
Location update
Maintenance status change
Asset retirement

Not every mouse click needs an audit event.

Focus on actions that affect accountability, operational decisions, or important asset records.

5. How to Implement Audit Logging in Your Backend

Suppose you're using Node.js, PostgreSQL, and a REST API.

When an administrator assigns an asset to an employee, two actions must happen:

First: Update the asset's current assignment.

Second: Insert an audit record describing that assignment.

These actions should ideally happen inside the same database transaction.

Why?

Because you don't want a situation where the asset is updated successfully but the audit event fails to save.

A simplified implementation might look like this:

async function assignAsset(db, assetId, employeeId, actorId) {
  const client = await db.connect();

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

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

    if (result.rowCount === 0) {
      throw new Error("Asset not found");
    }

    const previousOwner = result.rows[0].assigned_to;

    await client.query(
      `UPDATE assets
       SET assigned_to = $1, updated_at = NOW()
       WHERE id = $2`,
      [employeeId, assetId]
    );

    await client.query(
      `INSERT INTO asset_audit_logs
       (id, asset_id, action, actor_id,
        previous_value, new_value, created_at)
       VALUES (gen_random_uuid(), $1, $2, $3, $4, $5, NOW())`,
      [
        assetId,
        "ASSET_ASSIGNED",
        actorId,
        JSON.stringify({ assigned_to: previousOwner }),
        JSON.stringify({ assigned_to: employeeId })
      ]
    );

    await client.query("COMMIT");
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release();
  }
}

This example assumes an initialized PostgreSQL connection pool, suitable table definitions, and an authenticated actor.

The transaction ensures that the update and audit insertion either both succeed or both roll back.

The FOR UPDATE clause locks the selected asset row during the transaction, helping prevent concurrent assignment operations from reading and modifying the same old state.

In a production application, you'd also validate permissions, confirm that the employee exists, enforce valid asset transitions, and handle retries or idempotency where necessary.

The important design principle is simple: an important state change and its audit event should remain consistent.

6. Why Audit Trails Should Be Append-Only

Here's another important design decision.

Should administrators be allowed to modify previous audit events?

Usually, ordinary application users should not.

Imagine an administrator changes an asset assignment and later edits the historical event to hide the action.

The audit trail would no longer be trustworthy.

A better approach is an append-only event history.

Instead of modifying an old record, create a new event that documents the correction.

For example:

Asset assigned to John
Assignment identified as incorrect
Assignment corrected to Sarah

This preserves the sequence of events.

However, append-only behavior at the application layer is not enough by itself.

Production systems should also consider restricted database permissions, independent storage or replication for sensitive logs, defined retention policies, and monitoring for unauthorized changes.

For stronger tamper evidence, organizations may use cryptographic integrity mechanisms or protected audit infrastructure.

A trustworthy audit trail must be protected from the same actors whose actions it records.

7. Audit Trails Become Even More Important With Multiple Users

Single-user applications are relatively easy to reason about.

Multi-user applications introduce new challenges.

Imagine two administrators opening the same asset record.

Administrator A transfers the laptop to John.

Administrator B still has the older screen open and assigns it to Sarah.

Without concurrency controls, one update may overwrite the other.

Even worse, the audit history may become confusing if the application records events without properly coordinating database changes.

Depending on the system, developers can address this using:

Database transactions
Row-level locking
Optimistic concurrency control
Record version numbers
Domain-specific state transitions

The objective is to ensure that asset history reflects the actual order of committed changes.

Audit logging should be part of your application's data consistency strategy, not an afterthought.

8. The Difference Between Event Sourcing and an Audit Trail

These concepts are related, but they're not identical.

With a traditional database and audit logs, your application stores the current asset state in one place and records relevant changes separately.

With event sourcing, the event stream itself is the primary record of state changes, and current state is derived from those events.

Event sourcing can offer powerful history and replay capabilities.

But it also introduces additional complexity involving event versioning, projections, replay logic, and operational tooling.

For many straightforward asset tracking applications, a conventional relational database with a well-designed audit log is entirely reasonable.

You don't need event sourcing just to maintain useful change history.

Choose architecture based on actual requirements.

9. Common Audit Logging Mistakes Developers Make

Several implementation mistakes can undermine an otherwise useful audit system.

Recording only the latest value

Without the previous relevant state, an event may not clearly explain what changed.

Trusting actor IDs supplied by the client

The backend should derive the authenticated actor from the server-side security context, not blindly accept an arbitrary user ID from a request body.

Writing audit events outside the transaction

This can create gaps when a state update succeeds but logging fails, or vice versa.

Logging sensitive information unnecessarily

Audit events should not indiscriminately capture passwords, access tokens, personal information, or full request bodies.

Use data minimization and applicable retention policies.

Allowing normal users to delete audit records

This weakens the reliability of historical evidence.

Ignoring readability

A thousand JSON records may be technically complete but difficult for IT administrators to understand.

Consider presenting history in a human-readable activity timeline.

Confusing audit history with live location tracking

An event showing that an asset was scanned at a location does not prove that it remains there continuously.

This distinction matters especially in physical equipment tracking.

10. Turning Audit Events Into Useful Product Features

Once your application records meaningful asset events, you can build features that improve the everyday user experience.

For example, an asset detail page might display:

MacBook Pro — LAP-1042

Current custodian: John

Current status: Assigned

Recorded location: London Office

Activity history

Oct 11 — Assigned to John
Oct 10 — Returned by Sarah
Oct 2 — Assigned to Sarah
Oct 1 — Added to inventory

This timeline helps administrators understand the asset's recorded history without searching through separate spreadsheets or support tickets.

You can also support structured audit reviews, assignment investigations, operational reporting, and equipment offboarding workflows.

These are practical capabilities that emerge when a system records events as first-class business information.

11. A Real-World Example: Employee Offboarding

Let's connect the architecture to an everyday business process.

An employee is leaving the company.

The IT team needs to confirm which equipment is assigned to them.

A system with only current-state records may provide the current assignment list.

But a system with reliable asset history can also help answer:

When was each device issued?

Was the equipment transferred?

Was a previous device returned?

Who recorded the return?

Were there any corrections or discrepancies?

This historical context makes investigations easier and supports clearer equipment accountability.

Physical asset management platforms increasingly emphasize these kinds of documented workflows.

For example, Stackroom focuses on physical equipment records, employee custody, handovers, returns, and asset audits.

For an example of the business workflows that auditability supports, see Stackroom's asset audits and compliance solution.

Disclosure: I am associated with Stackroom. The database design and code in this article are illustrative examples, not a description of Stackroom's internal implementation.

12. A Simple Design Checklist Before Shipping

Before releasing an audit-enabled asset tracking feature, check the following:

Does every important asset change generate an event?
Are the current-state update and audit insert atomic?
Is the actor derived from an authenticated context?
Do concurrent updates preserve a consistent history?
Can ordinary users alter or delete historical events?
Are sensitive values excluded where unnecessary?
Can administrators understand the history without reading raw JSON?
Are retention and access rules defined?
Can the application explain who changed an asset, when, and what changed?

If the answer to several of these questions is no, your application may be storing equipment data without maintaining a reliable account of how that data changed.

Final Thoughts

Building an asset tracking system isn't only about storing equipment information.

It's about maintaining trust in that information.

A database tells you the current status of an asset.

An audit trail helps explain how that status changed.

For applications involving employee assignments, equipment transfers, maintenance, and offboarding, that distinction becomes extremely important.

You don't need an unnecessarily complicated architecture to get started.

A well-designed audit table, consistent transactions, authenticated actions, and sensible access controls can provide a strong foundation.

As the application grows, that foundation makes troubleshooting and operational accountability much easier.

A good asset tracking system tells you where things stand. A great one helps you understand how they got there.

Over to you

When building business applications, do you prefer maintaining a separate audit table, using database triggers, or adopting event sourcing?

What trade-offs have you encountered in production?

DEV Community: Arbaz

How to Build an IT Asset Lifecycle Management System with Node.js and PostgreSQL

1. Understanding the IT Asset Lifecycle

2. Choosing the Technology Stack

Project Setup

3. Designing the PostgreSQL Database

Create the Assets Table

Create the Asset Events Table

4. Connecting Node.js to PostgreSQL

5. Implementing Controlled Lifecycle Transitions

6. Building the Lifecycle REST API

Why Use SELECT ... FOR UPDATE?

Why Keep Updates and Events in One Transaction?

7. Testing the Asset Lifecycle API

Register an Asset

Assign the Asset

Return the Asset

View Lifecycle History

8. Adding Warranty Tracking

9. Preparing the System for Production

Authentication and Authorization

Input Validation

Tenant Isolation

Transaction Safety

Auditing

Other Production Requirements

10. Build Your Own or Use an Existing Platform?

11. Final Thoughts

References and Further Reading

How to Build a QR Code-Based IT Asset Tracking System with Node.js and PostgreSQL

1. How QR-Based Asset Tracking Works

2. Technology Stack

3. Create the Database Schema

4. Configure the Application

5. Create an Asset Registration Endpoint

6. Generate QR Codes for Registered Assets

7. Retrieve Asset Details After Scanning

8. Test the Workflow

9. Security Considerations

Never place confidential information directly in QR codes

Authorise sensitive record access

Restrict inventory modifications

Protect against label replacement

Maintain audit records

10. Moving From a Prototype to Production

Conclusion

How to Build an IT Asset Assignment and Return System (SQL + REST API)

1. Start With a Real-World Asset Tracking Scenario

2. Define the Asset Lifecycle

3. Design the PostgreSQL Database

3.1 Employees and application users

3.2 Assets table

3.3 Asset assignments table

4. Prevent the Same Laptop From Being Assigned Twice

Use a partial unique index

5. Design the Asset Assignment REST API

Example assignment transaction

Why use FOR UPDATE?

6. Design the Equipment Return Workflow

Return requests table

Return workflow

Return API

Return transaction

7. Keep an Audit Trail of Asset Activities

8. Integrate Employee Onboarding and Offboarding

Onboarding example

Offboarding example

9. Handle Production Edge Cases

Case 1: Two administrators assign the same asset

Case 2: An employee reports a lost laptop

Case 3: The return API is called twice

Case 4: An employee transfers departments

Case 5: IT receives a damaged laptop

10. Security: Protect Employee and Asset Records

11. Test the Workflow Before Deployment

12. Spreadsheet vs Dedicated Asset Management Software

Conclusion

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

1. Understand the Business Problem

Core workflow

Why Use `SELECT ... FOR UPDATE`?

Why use `FOR UPDATE`?

Why use `FOR UPDATE`?