tbl_assets_history

-1 rows


Description

Module: Asset & Inventory Management (Audit)
Purpose: Provides a complete, immutable audit trail for all changes and lifecycle events of a company asset (e.g., laptop, monitor, license).
Data: Each row captures a full snapshot of the parent asset record (tbl_assets) at the time a significant action occurs (allocation, return, status change).
Process Usage:
- Automatically populated whenever a CUD operation or allocation change occurs on the parent asset table.
- Used by administrators and auditors to review the entire chain of custody and status changes.
Key Points:
- Immutable Log: Ensures data integrity for financial and allocation compliance.
- Snapshot: Stores all core asset, depreciation, and current allocation data at the event time.
Business Impact: Essential for tracking company property, resolving employee/asset disputes, and managing asset depreciation schedules.

Columns

Column Type Size Nulls Auto Default Children Parents Comments
id bigserial 19 √ nextval('tbl_assets_history_id_seq'::regclass)

Primary key
Format: Long (auto-increment)
Used as: Unique identifier for a single historical event.
asset_id int8 19 null

FK: public.tbl_assets.id
Meaning: The parent asset ID whose history is being recorded.
asset_type_id int8 19 null

FK: public.tbl_asset_type.id
Meaning: The category or type of the asset (e.g., Laptop, Mobile Phone, Software License).
serial_number varchar 30 null

The asset’s unique identifier/Serial Number at the time of the event.
asset_name varchar 100 null

The descriptive name of the asset.
brand_name varchar 100 √ null

The brand or manufacturer of the asset.
status varchar 20 √ null

The status of the asset at the time of the event.
Values: e.g., “Allocated”, “In Stock”, “Under Repair”, “Disposed”.
non_recoverable bool 1 √ null

Boolean flag: Indicates if the asset is non-recoverable (e.g., small consumable items).
allocation_type varchar 20 null

The type of allocation.
Values: e.g., “Common”, “Backup”, “Individual”, “Shared”.
branch_location_id int8 19 √ null

FK: public.tbl_branch.id
Meaning: The physical location/branch where the asset is currently stored or assigned.
purchase_date date 13 √ null

The date the asset was purchased.
purchase_cost numeric 0 √ null

The original purchase cost of the asset.
depreciation_percentage numeric 0 √ null

The annual depreciation rate applicable to the asset.
depreciation_frequency varchar 20 √ null

How often depreciation is calculated (e.g., MONTHLY, QUARTERLY, ANNUALLY).
vendor_id int8 19 √ null

FK: public.tbl_vendor.id
Meaning: The vendor/supplier from whom the asset was purchased.
renewal_date date 13 √ null

Next expected renewal date (e.g., for licenses or service agreements).
expiration_date date 13 √ null

The date the asset is expected to expire or become obsolete.
warranty_end_date date 13 √ null

The expiration date of the asset’s warranty.
insurance_expiration_date date 13 √ null

The expiration date of the asset’s insurance policy.
description varchar 1000 √ null

Detailed description or specifications of the asset.
attachments text 2147483647 √ null

File paths or links to primary asset attachments (e.g., invoices, manuals).
allocation_date date 13 √ null

The date the asset was last allocated to a user or department.
allocated_to text 2147483647 √ null

The ID(s) of the employee currently holding the asset (Text format for multiple assignments, comma separated employee ids).
return_date date 13 √ null

The date the asset was officially returned from the allocated party (if applicable).
allocation_remarks varchar 1000 √ null

Remarks/notes related to the last allocation or return event.
allocation_attachments text 2147483647 √ null

File paths or links to attachments related to the allocation (e.g., sign-off forms).
history_type varchar 50 √ null

The nature of the event that triggered this history record creation.
Values:
- DEPARTMENT_CLEARANCE: Recorded during an employee’s exit clearance.
- RETURN: Asset was returned to stock.
- REVOKE: Allocation was cancelled/revoked.
- ALLOCATION: Asset was assigned to a party.
- DOCUMENT_ADD/DOCUMENT_UPDATE: Primary asset details were changed.
createdby varchar 50 √ null
createddate timestamp 29,6 √ null
modifiedby varchar 50 √ null
modifieddate timestamp 29,6 √ null

Indexes

Constraint Name Type Sort Column(s)
pk_tbl_assets_history Primary key Asc id

Relationships

Close relationships within degrees of separation