# Assets Module Schema Gap Analysis

Date: 2026-05-09

## Frontend Scope Reviewed

Frontend path reviewed: `C:\Apache24\htdocs\railserp\src\pages\assets`

The Assets module is broader than the current three-table database shape. The frontend includes pages for:

- Asset register and asset creation
- Asset categories, status, locations, allocation, transfers, movement logs, and audit trail
- Procurement purchase orders and asset capitalization
- Depreciation, revaluation, disposal, and decommissioning
- Maintenance requests, schedules, planning, history, materials, and cost tracking
- Inspection logs and compliance certificates
- Warranty tracking, vendor assignment, and spare-parts BOM
- Register, depreciation, utilization, and maintenance cost reports

## Existing Schema

The attached database screenshot shows only these tables under `assets`:

- `assets.asset_categories`
- `assets.asset_maintenance`
- `assets.assets`

That is not sufficient for the frontend module because those tables can hold the core asset record and a coarse maintenance record only. They do not persist the workflow records, financial lifecycle records, compliance artifacts, procurement records, location/allocation history, or reporting source data required by the screens.

## Required Structural Additions

The migration `database/migrations/20260509_000009_close_assets_schema_gap.sql` closes the gap by:

- Adding missing core columns to the existing asset register, category, and maintenance tables.
- Adding location, allocation, transfer, movement, status history, document, and audit tables.
- Adding financial lifecycle tables for capitalization, depreciation entries, revaluations, disposals, and decommissioning.
- Adding maintenance request, schedule, planning, material, cost, and history tables.
- Adding inspection, compliance certificate, warranty, vendor assignment, procurement order, procurement item, and spare-part BOM tables.

## Tenant Model

All new asset tables include `tenant_id uuid NOT NULL` and reference `platform.tenants(id)`. Existing asset tables receive a `tenant_id` column if it is missing. Existing tables may need tenant backfill before a future hard `NOT NULL` enforcement if they already contain rows without tenant data.

## Tables Added

- `assets.asset_locations`
- `assets.asset_allocations`
- `assets.asset_transfers`
- `assets.asset_movement_logs`
- `assets.asset_status_history`
- `assets.asset_documents`
- `assets.asset_audit_trail`
- `assets.asset_capitalizations`
- `assets.asset_depreciation_entries`
- `assets.asset_revaluations`
- `assets.asset_disposals`
- `assets.asset_decommissions`
- `assets.asset_procurement_orders`
- `assets.asset_procurement_order_items`
- `assets.asset_vendor_assignments`
- `assets.asset_warranties`
- `assets.asset_inspection_logs`
- `assets.asset_compliance_certificates`
- `assets.maintenance_schedules`
- `assets.maintenance_requests`
- `assets.maintenance_plans`
- `assets.maintenance_materials`
- `assets.maintenance_costs`
- `assets.maintenance_history`
- `assets.spare_parts`
- `assets.asset_spare_parts_bom`

## Reporting Approach

The report pages should be powered from the transactional tables above. No separate report tables are required at this stage unless the product later needs frozen report snapshots.