How to Build an IT Asset Lifecycle Management System with Node.js and PostgreSQL
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:
availableassignedmaintenanceretired
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 ...