M7 Database Schema

Overview

M7 uses PostgreSQL (version 17 in development, 12+ in production) managed via Prisma ORM (v7.2.0). The schema is defined in prisma/schema.prisma and is the single source of truth for the database structure.

All primary keys are cuid strings generated by Prisma. Timestamps (createdAt, updatedAt) are managed automatically.

---

Entity Relationship Diagram

erDiagram
    Department {
        string id PK
        string name UK
        datetime createdAt
        datetime updatedAt
    }

    Employee {
        string id PK
        string name
        string email
        string phone
        string role
        string location
        string avatarUrl
        boolean isActive
        datetime hireDate
        datetime createdAt
        datetime updatedAt
        string departmentId FK
    }

    Asset {
        string id PK
        string assetId UK
        string qrCode
        int csvOriginalId
        string assetTag
        AssetCategory category
        string name
        string manufacturer
        string modelNumber
        string specifications
        string operatingSystem
        datetime purchaseDate
        datetime warrantyExpiry
        string serviceHistory
        datetime lastMaintenance
        datetime nextMaintenance
        string supportContact
        AssetCondition condition
        datetime lastUsedDate
        float purchaseCost
        string networkPhone
        string notes
        AssetStatus status
        BatteryCondition batteryCondition
        float ramGb
        float storageGb
        float storageUsedGb
        float screenSizeInch
        string cpuInfo
        string imei
        string productLink
        string photoUrl
        string location
        datetime createdAt
        datetime updatedAt
        string assignedToId FK
        string departmentId FK
    }

    TransferHistory {
        string id PK
        datetime transferDate
        TransferReason reason
        string notes
        string assetId FK
        string fromEmployeeId FK
        string toEmployeeId FK
        string fromLocation
        string toLocation
        datetime createdAt
    }

    CheckoutForm {
        string id PK
        datetime checkoutDate
        datetime returnDate
        datetime expectedReturn
        CheckoutStatus status
        datetime acknowledgedAt
        string acknowledgedNote
        string conditionOnCheckout
        string conditionOnReturn
        string notes
        string assetId FK
        string employeeId FK
        datetime createdAt
        datetime updatedAt
    }

    ActivityLog {
        string id PK
        ActivityAction action
        string entityType
        string entityId
        string description
        json metadata
        datetime createdAt
    }

    Department ||--o{ Employee : "has"
    Department ||--o{ Asset : "owns"
    Employee ||--o{ Asset : "assigned_to"
    Employee ||--o{ TransferHistory : "transferred_from"
    Employee ||--o{ TransferHistory : "transferred_to"
    Employee ||--o{ CheckoutForm : "checks_out"
    Asset ||--o{ TransferHistory : "has_history"
    Asset ||--o{ CheckoutForm : "checked_out_via"

---

Models

Department

Represents an organisational unit (e.g. Sales, Operations, Development).

Field	Type	Constraints	Description
id	String	PK, @default(cuid())	Internal identifier
name	String	@unique	Department name
createdAt	DateTime	@default(now())	Record creation timestamp
updatedAt	DateTime	@updatedAt	Last update timestamp

Relations:

• employees → Employee[] — all staff in this department
• assets → Asset[] — all assets assigned to this department

---

Employee

Represents a company staff member who can be assigned hardware assets.

Field	Type	Constraints	Description
id	String	PK, @default(cuid())	Internal identifier
name	String	required	Full name
email	String?	—	Work email
phone	String?	—	Phone number
role	String?	—	Job title
location	String?	—	Office or remote location
avatarUrl	String?	—	Profile photo URL
isActive	Boolean	@default(true)	Soft-delete flag
hireDate	DateTime?	—	Employment start date
createdAt	DateTime	@default(now())	—
updatedAt	DateTime	@updatedAt	—
departmentId	String?	FK → Department.id	Owning department

Database indexes: departmentId, name

Relations:

• department → Department?
• assets → Asset[] (assets currently assigned to this employee)
• transfersFrom → TransferHistory[] (transfers initiated from this employee)
• transfersTo → TransferHistory[] (transfers received by this employee)
• checkoutForms → CheckoutForm[]

---

Asset

The core entity. Represents a single physical hardware item.

Identity & Classification

Field	Type	Constraints	Description
id	String	PK, @default(cuid())	Internal identifier
assetId	String	@unique	Human-readable ID (format: HW-00001)
qrCode	String?	—	QR code value (reserved, not yet generated)
csvOriginalId	Int?	—	Row ID from original CSV import
assetTag	String?	—	Physical label / serial number
category	AssetCategory	@default(OTHER)	See enum below
name	String	required	Asset name / model name
manufacturer	String?	—	Brand (e.g. Apple, Dell, Samsung)
modelNumber	String?	—	Manufacturer model number

Hardware Specifications

Field	Type	Description
specifications	String?	Free-text spec summary
operatingSystem	String?	OS installed
ramGb	Float?	RAM in GB (parsed from specs)
storageGb	Float?	Total storage in GB
storageUsedGb	Float?	Used storage in GB
screenSizeInch	Float?	Display size in inches
cpuInfo	String?	Processor description
imei	String?	IMEI number (mobile devices)
networkPhone	String?	Carrier / SIM info (phones)

Lifecycle & Condition

Field	Type	Constraints	Description
status	AssetStatus	@default(WORKING)	Operational status
condition	AssetCondition	@default(NORMAL)	Physical condition
batteryCondition	BatteryCondition	@default(UNKNOWN)	Battery health
purchaseDate	DateTime?	—	Date of purchase
purchaseCost	Float?	—	Purchase price
warrantyExpiry	DateTime?	—	Warranty end date
lastMaintenance	DateTime?	—	Last service date
nextMaintenance	DateTime?	—	Scheduled next service
serviceHistory	String?	—	Free-text service notes
supportContact	String?	—	Vendor support contact
lastUsedDate	DateTime?	—	Last recorded usage date

Assignment & Location

Field	Type	Constraints	Description
assignedToId	String?	FK → Employee.id	Assigned employee
departmentId	String?	FK → Department.id	Owning department
location	String?	—	Physical location

Media & Links

Field	Type	Description
photoUrl	String?	Condition photo URL (storage not yet connected)
productLink	String?	Product page / spec sheet URL
notes	String?	Free-text notes

Timestamps

Field	Type
createdAt	DateTime @default(now())
updatedAt	DateTime @updatedAt

Database indexes: assignedToId, departmentId, category, status, warrantyExpiry, purchaseDate, manufacturer

Relations:

• assignedTo → Employee?
• department → Department?
• transferHistory → TransferHistory[]
• checkoutForms → CheckoutForm[]

---

TransferHistory

Immutable audit record of every asset movement between employees or locations.

Field	Type	Constraints	Description
id	String	PK	—
transferDate	DateTime	@default(now())	When the transfer occurred
reason	TransferReason	@default(OTHER)	Reason for the transfer
notes	String?	—	Optional notes
assetId	String	FK → Asset.id, cascade delete	The asset being transferred
fromEmployeeId	String?	FK → Employee.id	Employee the asset came from (null = from pool)
toEmployeeId	String?	FK → Employee.id	Employee receiving the asset (null = returned to pool)
fromLocation	String?	—	Origin location
toLocation	String?	—	Destination location
createdAt	DateTime	@default(now())	Record creation

Database indexes: assetId, fromEmployeeId, toEmployeeId, transferDate

Transfer records are never updated — they are append-only. Deleting an asset cascades to its transfer history.

---

CheckoutForm

Tracks temporary loans of equipment to employees (as opposed to permanent assignments).

Field	Type	Constraints	Description
id	String	PK	—
checkoutDate	DateTime	@default(now())	When equipment was checked out
returnDate	DateTime?	—	When it was actually returned
expectedReturn	DateTime?	—	Originally planned return date
status	CheckoutStatus	@default(PENDING)	Current state of the checkout
acknowledgedAt	DateTime?	—	When employee acknowledged receipt
acknowledgedNote	String?	—	Note added at acknowledgement
conditionOnCheckout	String?	—	Condition description at checkout
conditionOnReturn	String?	—	Condition description on return
notes	String?	—	General notes
assetId	String	FK → Asset.id, cascade delete	The asset being checked out
employeeId	String	FK → Employee.id	The employee checking out
createdAt	DateTime	@default(now())	—
updatedAt	DateTime	@updatedAt	—

Database indexes: assetId, employeeId, status

Checkout lifecycle:

stateDiagram-v2
    [*] --> PENDING : createCheckout()
    PENDING --> ACKNOWLEDGED : acknowledgeCheckout()
    PENDING --> CANCELLED : (manual cancel)
    ACKNOWLEDGED --> RETURNED : returnCheckout()
    RETURNED --> [*]
    CANCELLED --> [*]

---

ActivityLog

Append-only audit trail. Every create, update, delete, and assign operation writes a log entry. There is no foreign-key relation to other tables — entityType and entityId are stored as strings to allow logging of deleted records.

Field	Type	Constraints	Description
id	String	PK	—
action	ActivityAction	required	The action performed
entityType	String	required	Model name (e.g. "Asset", "Employee")
entityId	String	required	ID of the affected record
description	String	required	Human-readable description
metadata	Json?	—	Structured extra context (e.g. changed fields)
createdAt	DateTime	@default(now())	Timestamp

Database indexes: (entityType, entityId), action, createdAt

---

Enums

AssetCategory

Value	Description
LAPTOP	Laptops and notebooks
PHONE	Mobile phones
MONITOR	Display screens
PERIPHERAL	Keyboards, mice, webcams, headsets, etc.
STORAGE	External drives, USB sticks
DOCKING_STATION	USB-C / Thunderbolt hubs
OTHER	Anything not categorised above

AssetStatus

Value	Description
WORKING	Fully operational
BROKEN	Non-functional
IN_REPAIR	Currently being serviced
DECOMMISSIONED	Retired from service
LOST	Cannot be located
UNASSIGNED	In the pool, not assigned

AssetCondition

Value	Description
GOOD	Like new / excellent
NORMAL	Normal wear and tear
FAIR	Visible wear, functional
POOR	Heavy wear or cosmetic damage
ABNORMAL	Unusual condition requiring review

BatteryCondition

Value	Description
NORMAL	Healthy battery
LOW	Below recommended capacity
ABNORMAL	Swelling or irregular behaviour
SERVICE_RECOMMENDED	Should be serviced
UNKNOWN	Not assessed

TransferReason

Value	Description
NEW_ASSIGNMENT	First assignment to a new employee
ROLE_CHANGE	Employee changed role or team
EMPLOYEE_DEPARTURE	Employee left the company
REASSIGNMENT	Moved to a different employee
REPAIR	Sent for repair
RETURN_TO_POOL	Returned to unassigned pool
BULK_IMPORT	Created during CSV data import
OTHER	General or unspecified reason

CheckoutStatus

Value	Description
PENDING	Checkout created, not yet acknowledged
ACKNOWLEDGED	Employee confirmed receipt
RETURNED	Equipment returned
CANCELLED	Checkout cancelled

ActivityAction

Value	Trigger
ASSET_CREATED	New asset added
ASSET_UPDATED	Asset fields changed
ASSET_DELETED	Asset deleted
ASSET_ASSIGNED	Asset assigned to an employee
ASSET_UNASSIGNED	Asset returned to pool
ASSET_TRANSFERRED	Asset moved between employees
EMPLOYEE_CREATED	New employee added
EMPLOYEE_UPDATED	Employee fields changed
EMPLOYEE_DEACTIVATED	Employee soft-deleted
CHECKOUT_CREATED	Checkout form created
CHECKOUT_ACKNOWLEDGED	Checkout acknowledged by employee
CHECKOUT_RETURNED	Equipment returned
BULK_IMPORT	Data imported via CSV seed script

---

Seed Data

The prisma/seed.ts script imports hardware data from a CSV file (Hardware_update_11112025(Hardware).csv). The seed script performs:

1. CSV parsing with quoted field support
2. Department normalisation (maps freeform text to known department names)
3. Employee name and department mapping
4. Asset category auto-detection from device names
5. Date parsing (handles multiple formats: DD/MM/YYYY, Month Year, MFG:MONTH,YEAR)
6. Hardware spec extraction (RAM, storage, screen size, IMEI) from free-text fields
7. Enum value normalisation (status, condition, battery)
8. Initial TransferHistory record creation (BULK_IMPORT reason)
9. ActivityLog entries for all imported records

To run: npm run db:seed

The seed is a one-time import. Re-running it will attempt to create duplicate records unless the database is cleared first.

---

Migration Strategy

The project currently uses prisma db push (schema push without migration files) for local development. To introduce tracked migrations:

# Create a named migration
npm run db:migrate

# Apply pending migrations (production)
npx prisma migrate deploy

Note: No migration files currently exist in the repository. The schema has been applied directly via db push. Requires manual verification before enabling migration-based deployments.