← CMMS on CAP-686: UI sample vs built
CMMS tables
Branch CAP-686 · migration 20261009104038-cmms-tab.js · structure read from a local database with the migration applied, 14 Oct 2026
One migration: 1 new table, columns added to 4 tables, and existing work-order values converted. Everything runs in a single transaction, and the down step reverses all of it, including converting the values back.
1 new table
work_order_activities, the work-order timeline19 new columns13 on
work_orders, 3 on users, 1 on service_contracts, 2 on camp_inventory_items3 tables usedby CMMS directly:
work_orders, work_order_activities, parts_pos (unchanged)6 functionsrisk-level function and its 5 triggers updated for the Retired status
On this page
- Table changes in CAP-686
work_orderswork_order_activitiesparts_pos- New columns on other tables
- Reused tables
- How the tables connect
Table changes in CAP-686
New created by this migration Changed columns added or values converted Unchanged used as before
| Table | Change | What changes |
|---|---|---|
work_order_activities | New | 9 columns. One row per event on a work order: created, assigned, reassigned, stage or status changed, note, escalated, closed, reopened. Rows are deleted with their work order. The analytics role reads it per account only. |
work_orders | Changed |
13 columns added:
type default changes from 'Repair' to 'PM'. Existing type and status values are converted to the new sets. type, status and priority now carry comments listing their allowed values.
|
users | Changed | 3 columns added: is_technician, technician_title, technician_capacity. Users who already have work orders assigned are set to is_technician = true. |
service_contracts | Changed | 1 column added: coverage_source (oem, third_party or self). Empty means OEM vs HTM is worked out from vendor vs manufacturer. |
camp_inventory_items | Changed | 2 columns added: retired_at, retired_by. The risk-level function and its triggers now return Retired for assets the bulk retire marks in needs_attention, and existing marked assets are recalculated. |
parts_pos | Unchanged | Used as before. vendor_name is still free text. |
work_orders Changed
30 columns: 17 from CAP-15 and 13 new. One row per work order, per account. Soft delete. The number is unique per account.
| Column | Type | Null | Default | Notes |
|---|---|---|---|---|
id | integer | no | serial | PK |
account_id | integer | no | FK → accounts | |
inventory_id | integer | yes | FK → camp_inventory_items (the asset), on delete set null | |
asset_name new | varchar(255) | yes | the asset as typed; the only reference when there is no inventory link | |
work_order_number | varchar(64) | no | WO-{accountId}-00001; unique per account | |
type changed | varchar(48) | no | 'PM' (was 'Repair') | PM, CM, IN, Install, Project, Other |
priority | varchar(24) | no | 'Medium' | Urgent, High, Medium, Low |
status changed | varchar(24) | no | 'Open' | Open, Under Customer, Escalated, Closed (was Open, In Progress, Completed, Cancelled) |
stage new | varchar(32) | no | 'Assigned' | Assigned, First Touch, Resolution |
assignee_user_id | integer | yes | FK → users (the technician) | |
opened_at | timestamptz | no | now | |
due_at | timestamptz | yes | ||
closed_at | timestamptz | yes | ||
closed_reason new | varchar(32) | yes | completed or cancelled | |
closed_by new | integer | yes | FK → users, on delete set null | |
requested_by_user_id new | integer | yes | FK → users, on delete set null | |
requested_by_name new | varchar(150) | yes | ||
requested_by_title new | varchar(150) | yes | ||
requested_by_email new | varchar(255) | yes | ||
requested_by_phone new | varchar(50) | yes | ||
sla_hours new | integer | yes | hours from opened_at until the SLA window closes | |
escalated_at new | timestamptz | yes | ||
escalation_target new | varchar(120) | yes | who an escalation goes to, for example “OEM · Level 2 Tech” | |
description | text | yes | ||
notes | text | yes | ||
created_by | integer | yes | FK → users | |
updated_by new | integer | yes | FK → users, on delete set null | |
created_at | timestamptz | no | now | |
updated_at | timestamptz | no | now | |
deleted_at | timestamptz | yes | soft delete |
work_order_activities New
9 columns. Append-only: rows are added, never edited. The drawer shows the newest 200.
| Column | Type | Null | Default | Notes |
|---|---|---|---|---|
id | bigint | no | serial | PK |
account_id | integer | no | FK → accounts, on delete cascade | |
work_order_id | integer | no | FK → work_orders, on delete cascade | |
action | varchar(32) | no | created, assigned, reassigned, stage_changed, status_changed, note, escalated, closed, reopened | |
from_value | text | yes | the value before | |
to_value | text | yes | the value after | |
note | text | yes | the note entered with the change | |
actor_user_id | integer | yes | FK → users, on delete set null | |
created_at | timestamptz | no | now |
parts_pos Unchanged
19 columns, from CAP-15. One row per purchase order, per account. Soft delete. The number is unique per account.
| Column | Type | Null | Default | Notes |
|---|---|---|---|---|
id | integer | no | serial | PK |
account_id | integer | no | FK → accounts | |
po_number | varchar(64) | no | PO-{accountId}-00001; unique per account | |
part_number | varchar(128) | yes | ||
description | text | yes | ||
vendor_name | varchar(255) | yes | free text; there is no vendors table | |
quantity | integer | no | 1 | |
unit_cost | decimal(12,2) | yes | ||
status | varchar(24) | no | 'Ordered' | Ordered, Backordered, Delivered, Cancelled |
ordered_at | timestamptz | no | now | |
expected_at | timestamptz | yes | ||
received_at | timestamptz | yes | ||
linked_inventory_id | integer | yes | FK → camp_inventory_items, on delete set null | |
linked_work_order_id | integer | yes | FK → work_orders, on delete set null; parts in the drawer come from here | |
notes | text | yes | ||
created_by | integer | yes | FK → users | |
created_at | timestamptz | no | now | |
updated_at | timestamptz | no | now | |
deleted_at | timestamptz | yes | soft delete |
New columns on other tables
| Column | Type | Null | Default | Notes |
|---|---|---|---|---|
users.is_technician new | boolean | no | false | shows the user in technician pickers and Team Stats |
users.technician_title new | varchar(100) | yes | for example “Sr. Biomed Tech”; falls back to the job title | |
users.technician_capacity new | smallint | no | 6 | open + active work orders that count as a full load; not read by any endpoint yet |
service_contracts.coverage_source new | varchar(20) | yes | oem, third_party, self; empty means derive from vendor vs manufacturer | |
camp_inventory_items.retired_at new | timestamptz | yes | when the CMMS retire archived the asset | |
camp_inventory_items.retired_by new | integer | yes | FK → users, on delete set null |
Functions: compute_risk_level_for_row gains a needs_attention argument and returns Retired when it holds the retired mark. The row trigger trg_inv_risk_level_set now also fires when needs_attention changes, and the four cascade triggers (category, contract, model, model EOL) pass the new argument. The matching .sql files in database/triggers-and-functions/functions/ are updated.
Reused tables
CMMS reads these existing tables. CAP-686 doesn’t change them, apart from the columns listed above.
PM and coverage columns on camp_inventory_items
| Column | Type | Used for |
|---|---|---|
next_service_date | date | PM Overdue (before today), within 30 / 60 days |
last_service_date | date | PM schedule in the drawer |
pm_schedule | varchar(150) | PM schedule |
pm_interval | varchar(255) | PM schedule |
pm_status | varchar(255) | PM schedule |
pm_source | enum: file, sticker, self | where the PM data came from |
pm_vendor_name, pm_phone, pm_email | varchar(255) | PM vendor |
pm_notes | text | PM notes |
service_contract_id | integer | contract coverage, OEM vs HTM, high-risk (annual cost ÷ purchase price ≥ 40%) |
warranty_id, warranty_expiration_date | integer, date | warranties ending in 90 days |
risk_level | varchar(20) | includes the new Retired value |
needs_attention | varchar(200) | holds the retired mark the bulk retire sets |
service_histories
Service lines imported from a customer’s own CMMS export (CAP-677). One row per activity line; serial number is the join key to inventory.
| Column | Type | Null | Notes |
|---|---|---|---|
id | integer | no | PK |
account_id | integer | no | FK → accounts |
inventory_id | integer | yes | FK → camp_inventory_items, on delete set null |
document_manager_id | integer | yes | FK → document_manager (the source file) |
additional_details | jsonb | yes | default {} |
extracted_data | jsonb | yes | |
tag | varchar(255) | yes | |
serial_number | varchar(255) | yes | |
wo_number | varchar(255) | yes | the customer’s own WO number |
wo_request_date | timestamptz | yes | |
service_type | varchar(255) | yes | CMMS codes, for example PM, CM, IN |
status | varchar(255) | yes | CMMS codes, for example CL, OP |
rank | integer | yes | |
activity_done_date | timestamptz | yes | |
response_code | varchar(255) | yes | |
actions | text | yes | |
failure_code | varchar(255) | yes | |
stat_date | timestamptz | yes | |
created_at, updated_at | timestamptz | no | |
deleted_at | timestamptz | yes | soft delete |
inventory_service_history
Older per-asset service tickets, from 2024.
| Column | Type | Null | Notes |
|---|---|---|---|
id | integer | no | PK |
account_id | integer | no | FK → accounts |
inventory_id | integer | no | FK → camp_inventory_items |
ticket_number | varchar(255) | yes | |
service_request_date | timestamptz | yes | |
request_source | varchar(255) | yes | |
service_type | varchar(255) | yes | |
status | varchar(255) | yes | |
item_number | varchar(255) | yes | |
serial_number | varchar(255) | yes | |
po_number | varchar(255) | yes | |
vendor_name | varchar(255) | yes | |
vendor_eta | timestamptz | yes | |
service_description | text | yes | |
created_at, updated_at | timestamptz | no | |
deleted_at | timestamptz | yes | soft delete |
Also read
users: technicians, assignees, requesters, the people who close and update work orders, and timeline actors.accounts: each work order, activity and PO belongs to one account (facility).- Location tables (location, floor, department, room): the drawer’s asset location and the Intake picker.
service_contractsand warranties: coverage, the 90-day banner, the drawer’s contract section.inventory_historical_records: one row per asset retired from CMMS.
How the tables connect
| From | Column | To | Meaning |
|---|---|---|---|
work_orders | account_id | accounts | the facility that owns the WO |
work_orders | inventory_id | camp_inventory_items | the asset (or asset_name when there is none) |
work_orders | assignee_user_id | users | the technician |
work_orders | requested_by_user_id, created_by, updated_by, closed_by | users | who asked, created, last changed, closed |
work_order_activities | work_order_id | work_orders | the WO’s timeline |
work_order_activities | actor_user_id | users | who did it |
parts_pos | linked_work_order_id | work_orders | parts on a WO |
parts_pos | linked_inventory_id | camp_inventory_items | the asset the part is for |
camp_inventory_items | retired_by | users | who retired the asset |
service_histories, inventory_service_history | inventory_id | camp_inventory_items | imported and older service records |