DEV Community: Arbaz

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

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

1. Understand the Business Problem

Core workflow

2. Define the Database Invariants

3. Create the PostgreSQL Schema

Employees

Assets

Checkout history

Prevent duplicate active checkouts

Record custody events

4. Insert Example Data

5. Set Up Node.js and Express

Database connection

6. Build the Checkout API

Why use a transaction?

Why use FOR UPDATE?

7. Build the Return API

8. Add an Employee Equipment Lookup API

Start the server

9. Test the Workflow

Test concurrent requests

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

Authentication and authorization

Tenant isolation

Idempotency

Employee transfers

Full audit history

Operational controls

11. Why These Design Decisions Matter to IT Teams

12. How This Relates to Real Asset Management Platforms

Conclusion

Discussion

Technical references

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

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

2. What Exactly Is an Asset Management Audit Trail?

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

4. Designing an Audit Trail Database

5. How to Implement Audit Logging in Your Backend

6. Why Audit Trails Should Be Append-Only

7. Audit Trails Become Even More Important With Multiple Users

8. The Difference Between Event Sourcing and an Audit Trail

9. Common Audit Logging Mistakes Developers Make

10. Turning Audit Events Into Useful Product Features

11. A Real-World Example: Employee Offboarding

12. A Simple Design Checklist Before Shipping

Final Thoughts

Over to you

Why use `FOR UPDATE`?