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

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:

1. The caller is authorised to assign equipment.
2. The asset exists.
3. The asset is available.
4. The employee exists and is active.
5. Both belong to the caller's permitted organisation.
6. 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:

1. HR creates Sarah's employee record.
2. IT receives an equipment request.
3. An administrator selects an available laptop.
4. The system creates an assignment.
5. Sarah acknowledges receipt.
6. 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:

1. Separate asset inventory from assignment history.
2. Define clear asset and return lifecycle states.
3. Prevent conflicting assignments at the database level.
4. Use transactions for operations that modify related records.
5. Record verified returns instead of assuming equipment was received.
6. Preserve audit history.
7. Handle concurrency, retries, and exceptional cases.
8. 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.

---