Skip to the lesson
CivOps AI Academy · M05Maintenance (CMMS): Work Orders, Preventive Maintenance and Reliability on One Asset Tree
0%

Chapter 1 · Purpose, scope and standards

From request to reliability figure

A maintenance system answers four questions: what is broken or due, who is doing what about it, what did it cost, and which machines keep failing. This chapter says what Maintenance (CMMS) builds, what it leaves to other modules, why it keeps one asset tree and not two, and which standards it is measured against.

20 min12 capabilities3 tiers: MVP, STD, BIC8 standards

By the end of this chapter you can

  • Say what a CMMS does from request to reliability figure, and which tools the module replaces.
  • Name what the module leaves to M11 and M12, and what stays outside the course entirely.
  • Explain why maintenance attributes hang on the spine's equipment and not on a separate asset list.
  • Name the standards the module is built against and what each is used for.

What the module does

Maintenance (CMMS) runs maintenance from the first request to the reliability figure that shows whether the programme works. It keeps one asset register on the shared ISA-95 equipment hierarchy, work orders with ISO 14224 failure coding, preventive maintenance triggered by time, meters or condition, spare parts for maintenance, repair and operations (MRO), calibration records, and technicians who complete work on a phone with no signal. It then measures the result with the maintenance KPIs of SMRP and EN 15341 [8].

The module specification names the tools it replaces: IBM Maximo, Rockwell Fiix, UpKeep, MaintainX, Limble, Fluke eMaint, SAP PM and HxGN EAM. Four of them (Fiix, MaintainX, Limble and UpKeep) export assets, PMs, work orders and parts as CSV or through an API, which is what makes the cutover in chapter 4 possible [5][6][7]. CMMS (computerised maintenance management system) and EAM (enterprise asset management) describe the same family of tools; EAM leans toward the whole life of an asset.

From request to reliability figureSix steps: a request becomes a work order, which is planned and scheduled, executed on a phone, closed with failure coding, and analysed. The analysis feeds back into preventive maintenance and criticality. 1 RequestQR, mobile, shift log, form, SCADA2 Work ordertype, priority, status, downtime3 Plan and schedulejob plan, craft, parts, permit flag4 Executemobile, offline, checklist, labour5 Closeoutmode, cause, mechanism, remedy6 AnalyseMTBF, MTTR, bad actors, PM complianceAnalysis feeds back into PM intervals, criticality and the next request.
From request to reliability figure. A request becomes a work order, which is planned and scheduled, executed on a phone, closed with failure coding and analysed. The analysis feeds back into PM intervals, criticality and the next request.

What is in, and what is not

The module is wide enough to replace the tools above and narrow enough to leave permits, stock valuation and predictive models to the modules built for them.

The edge of MaintenanceInside: asset profile and QR labels, requests and work orders, planning and preventive maintenance, failure coding and calibration, spares and reliability analytics. Outside, each with its owner: permits and LOTO in M11, inventory valuation and purchasing in M12, and predictive models in a later catalogue wave. INSIDE MAINTENANCE (M05)Asset profile and QRRequests, work ordersPlanning and PMCoding and calibrationSpares and reliabilityOUTSIDE, WITH ITS OWNERPermit issuance and LOTO proceduresowned by M11; CMMS references the permitInventory valuation and purchasingowned by M12; CMMS posts parts to its ledgerPredictive models and APMcatalogue Wave 4; feeds condition triggers
The edge of Maintenance. Inside: asset profile and QR labels, requests and work orders, planning and PM, failure coding and calibration, spares and reliability analytics. Outside, each with its owner: permit issuance and LOTO procedures (M11), inventory valuation and purchasing (M12) and predictive models (catalogue Wave 4, which feed condition triggers here).
  • In: a maintenance profile on spine equipment (criticality, ISO 14224 class, warranty, costs); work requests from QR, mobile, shift log, forms and SCADA conditions; the work order lifecycle with planning, scheduling, execution and closeout coding; preventive, meter-based and condition-based maintenance with forecasting; job plans, crafts, labour, parts, tools and permit flags; MRO spares and reorder points; calibration records; reliability analytics.
  • Out: permit issuance and LOTO procedure ownership (M11; the CMMS only references the permit); general inventory valuation and purchasing (M12; spare-part stock movements are posted to M12's ledger); predictive models and asset performance management (catalogue Wave 4; they feed condition triggers here).

Twelve capabilities in three tiers

Each capability carries a tier. MVP must exist for a business to cancel its incumbent subscription with confidence. STD is parity with mainstream tools, what a buyer expects in a demo. BIC marks best-in-class differences, here AI that suggests failure codes and PM intervals for a person to approve.

CapabilityWhat it doesTier
Asset registerMaintenance profile on spine equipment: ISO 14224 class, criticality (A, B or C, or scored), warranty, purchase and replacement cost, nameplate attributes, documents, QR labels, spare-parts listMVP
Work requestsAnyone raises a request (QR scan, mobile, shift log, a failed form item, a SCADA condition); triage accepts, rejects or converts it; the requester is toldMVP
Work order lifecycleEight types, a priority, statuses from approval to closure, on-hold with a reason, downtime linkageMVP
Preventive maintenanceCalendar, meter, condition and hybrid triggers; fixed or floating schedules; lead time; auto-generation; forecast; PM complianceMVP
Mobile execution (offline)Assigned work, checklists with measurements, photos, parts used, labour clocking and signatures, all working offlineMVP
Planning and schedulingJob plans and tasks, estimated labour by craft, parts and tools kitting, permit and LOTO flags, a weekly schedule against craft capacity, backlog in crew-weeksSTD
Closeout codingMandatory failure mode, cause, mechanism and remedy per equipment class from ISO 14224 pick-lists; a supervisor reviews the codingSTD
MRO sparesSpare-part links per asset, critical spares, minimum, maximum and reorder points, issue and return against work orders posted to the M12 ledger, stock-out alertsSTD
Meters and conditionManual or tag-fed meters; thresholds create work requests; a trend viewSTD
CalibrationInstrument schedule, procedures, as-found and as-left, tolerance, pass, fail or adjusted, a certificate, and an impact assessment when out of toleranceSTD
Reliability analyticsMTBF, MTTR, bad-actor ranking, failure Pareto, cost per asset, PM compliance, schedule compliance, planned against reactive workSTD
RCM / FMEA and AIReliability-centred maintenance worksheets per asset class; AI suggestions for failure codes and PM intervals, always approved by a personBIC

MVP and STD are required for the course. The BIC capability is advanced work; this course meets it only where a later session asks for an AI suggestion that a person approves.

One asset tree, not two

Maintenance needs a list of what it maintains. The production side already has one: the equipment hierarchy on the shared spine, built on the ISA-95 model. ISO 14224 defines a taxonomy for equipment in nine levels, from industry down to the part, and shows where maintenance data attaches [1][3]. If the CMMS keeps its own asset list, the two drift: the same pump is counted twice, or not at all, and no figure built on either list can be trusted.

One asset tree, not twoLeft: a separate CMMS asset list drifts from the production tree, so one pump is counted twice or missed. Right: cmms_asset_profile has one row for each core_equipment row, and meters, PM schedules and work orders all point at the same equipment. TWO TREES, DRIFTING APARTProduction treepump 1, pump 2, pump 3CMMS asset listpump A, pump 3, pump 3Same pump counted twice, or not at allno failure figure can be trustedONE TREE ON THE SPINEcore_equipmentthe production hierarchycmms_asset_profilecriticality, class, warranty, cost, QR1 : 1Meters, PM schedules, work ordersequipment_id
One asset tree, not two. Left: a separate CMMS list drifts from the production tree. Right: cmms_asset_profile holds the maintenance attributes of each core_equipment row, one for one, and meters, PM schedules and work orders point at the same equipment.

The standards, and what each is used for

StandardUsed in this module for
ISO 55000, 55001 and 55002 [4]The asset management system: criticality, lifecycle cost, and a line of sight from objectives to work
ISO 14224:2016 [3]Equipment taxonomy (levels 1 to 9), equipment classes, and failure mode, cause and mechanism coding
EN 13306:2017Maintenance terminology: corrective, preventive, condition-based and predictive
EN 15341:2019 and SMRP Best Practices metricsThe KPIs: PM compliance, schedule compliance, MTBF, MTTR, backlog, maintenance cost against replacement asset value
ISO 17359General guidelines for condition monitoring and diagnostics
ISO 10012 and ISO/IEC 17025Measurement management and calibration traceability
OSHA 29 CFR 1910.147Control of hazardous energy (lockout and tagout, LOTO) during maintenance work
MIMOSA CCOM and OIIEAn open model for exchanging asset-management information; an integration reference

Knowledge check

Which of these does Maintenance (CMMS) leave to another module?

Knowledge check

Why do the maintenance attributes of a machine sit in cmms_asset_profile, one row for each core_equipment row?

References

  1. Accendo Reliability: setting up the asset hierarchy (ISO 14224 levels). https://accendoreliability.com/setup-asset-hierarchy/
  2. Reliability Magazine: how to build an asset hierarchy in a CMMS. https://reliamag.com/guides/how-to-build-asset-hierarchy-cmms/
  3. ISO 14224:2016 Petroleum, petrochemical and natural gas industries: collection and exchange of reliability and maintenance data for equipment. https://www.iso.org/standard/64076.html
  4. ISO 55001:2014 Asset management: management systems, requirements. https://www.iso.org/standard/55089.html
  5. Limble: Fiix alternatives and core CMMS features. https://limblecmms.com/?p=26572
  6. EZO: EZO against Fiix and Limble. https://ezo.io/ezo-cmms/blog/ezo-vs-fiix-vs-limble/
  7. GetApp: MaintainX against Limble (modules). https://www.getapp.ca/compare/117239/2049215/getmaintainx/vs/limble-cmms
  8. SMRP: Society for Maintenance and Reliability Professionals, maintenance and reliability metrics. https://smrp.org/

Chapter 2 · The data model and who may do what

Fifteen tables, five roles, five screens

How maintenance is stored decides what you can ever measure. Fifteen cmms_ tables hold the asset profile, the plans, the work and its results. Five roles, four machine accounts and five screens decide who does what. This chapter is the reference your agent builds from in Sessions 2 and 3.

30 min15 tables5 roles5 screens

By the end of this chapter you can

  • Name the fifteen cmms_ tables, group them, and say which spine tables they point at.
  • Read the column tables: type, required, and allowed values.
  • Say why some links are plain uuid columns with no foreign key.
  • Name the five roles and the five screens, and say which role uses which screen.

Fifteen tables on the shared spine

Every table starts with the standard columns every platform table has: id, tenant_id, created_at, created_by, updated_at, updated_by, row_version, archived_at and ext. The module has no people table, item table, site list or file store of its own: it points at core_equipment, core_equipment_class, core_person, core_item, core_storage_location, core_uom, core_data_tag, core_reason_code, core_attachment and core_e_signature. Its own tables share the prefix cmms_. Pointing at core_equipment keeps one asset hierarchy for production and maintenance, built on the levels that ISO 14224 describes [1][2][3]. Together the tables cover the core features a CMMS product offers: assets, work orders, preventive maintenance, parts and mobile work [5].

The fifteen cmms_ tablesFifteen tables in four groups: asset and meters (four), plan (four), do (five) and code and check (two). All of them point at the shared spine tables, such as core_equipment, and at one another. ASSET AND METERScmms_asset_profilecmms_metercmms_meter_readingcmms_spare_part_linkPLANcmms_craftcmms_job_plancmms_job_plan_taskcmms_pm_scheduleDOcmms_work_requestcmms_work_ordercmms_wo_taskcmms_wo_laborcmms_wo_partCODE AND CHECKcmms_failure_codecmms_calibration_recordSpine: core_equipment, core_person, core_item, core_uom, core_attachment and the other core_ tables
The fifteen cmms_ tables. Asset and meters (four), plan (four), do (five), and code and check (two). All of them point at the spine's core_ tables and at one another.

The tables below are the full definition: every column the module adds to the standard ones. Types are written as in the specification. text, int, num (a decimal number), bool, date, ts (a timestamp with time zone), uuid and json (jsonb in Postgres) are as usual. money is an amount of money and qty a decimal quantity; both are numeric columns, never text. An arrow means a foreign key to that table, and req means required (not null). Allowed values become check constraints.

Assets, crafts, meters, spares and failure codes

TableHoldsColumns
cmms_asset_profileMaintenance attributes, one row on each piece of equipmentequipment_id → core_equipment req, unique; criticality text req: a, b or c; criticality_score num; iso14224_class text; maintainable bool req; warranty_expires_at date; purchase_cost money; replacement_cost money; expected_life_years num; qr_code text; status text req: in_service, standby, out_of_service or retired
cmms_craftA trade or skill pool with a labour ratecode text req, unique; name text req; hourly_rate money; capacity_hours_per_week num
cmms_meterA runtime, cycle or condition meter on an assetequipment_id → core_equipment req; name text req; uom_id → core_uom; data_tag_id → core_data_tag; reading_type text req: cumulative or gauge; alert_low num; alert_high num
cmms_meter_readingOne reading, typed in or fed from a tagmeter_id → cmms_meter req; value num req; read_at ts req; source text req: manual, tag or import; read_by → core_person
cmms_spare_part_linkThe spare-parts list of an assetequipment_id → core_equipment req; item_id → core_item req; qty_installed qty; critical_spare bool req; min_qty qty; max_qty qty; reorder_point qty
cmms_failure_codeISO 14224-aligned codes per equipment classcode_type text req: mode, cause, mechanism or remedy; equipment_class_id → core_equipment_class; code text req; name text req; iso14224_ref text; active bool req

Plans and preventive schedules

TableHoldsColumns
cmms_job_planA reusable maintenance procedurecode text req, unique; name text req; equipment_class_id → core_equipment_class; craft_id → cmms_craft; est_hours num; loto_required bool req; permit_types json; safety_notes text; procedure_doc_ref uuid, a soft link to doc_controlled_document
cmms_job_plan_taskAn ordered task, with an optional measurement and limitsjob_plan_id → cmms_job_plan req; seq int req; description text req; measurement_required bool; uom_id → core_uom; lsl num; usl num
cmms_pm_scheduleA preventive maintenance triggercode text req, unique; equipment_id → core_equipment req; job_plan_id → cmms_job_plan req; trigger text req: calendar, meter, condition or hybrid; interval_value num; interval_unit text: day, week, month, year, hour, cycle or unit; meter_id → cmms_meter; meter_interval num; condition_rule json; lead_days int; floating bool req; next_due_at ts; next_due_meter num; last_completed_at ts; active bool req

Requests, work orders and their results

TableHoldsColumns
cmms_work_requestA request for work, raised by anyonerequest_no text req, unique; equipment_id → core_equipment; location_text text; description text req; requested_by → core_person req; priority_suggested int; source text req: mobile, qr, shift_log, form, scada, email or portal; status text req: new, accepted, rejected, converted or duplicate; work_order_id → cmms_work_order; photo_attachment_id → core_attachment
cmms_work_orderThe maintenance work orderwo_number text req, unique; equipment_id → core_equipment req; wo_type text req: corrective, preventive, predictive, inspection, calibration, project, safety or improvement; priority int req; status text req: draft, awaiting_approval, approved, planned, scheduled, in_progress, on_hold, completed, closed or cancelled; hold_reason_code_id → core_reason_code; pm_schedule_id → cmms_pm_schedule; job_plan_id → cmms_job_plan; requested_at, required_by, scheduled_start, scheduled_end, actual_start, actual_end ts; equipment_down bool; downtime_min num; assigned_craft_id → cmms_craft; assigned_to → core_person; failure_mode_id, failure_cause_id, failure_mechanism_id, remedy_id → cmms_failure_code; completion_notes text; permit_ref uuid, a soft link to ehs_permit; completion_signature_id → core_e_signature
cmms_wo_taskA task line with its resultwork_order_id → cmms_work_order req; seq int req; description text req; result text: pass, fail, done, not_done or not_applicable; measured_value num; done_by → core_person; done_at ts
cmms_wo_laborLabour booked to a work orderwork_order_id → cmms_work_order req; person_id → core_person req; craft_id → cmms_craft; started_at ts; ended_at ts; hours num req; rate money; is_overtime bool
cmms_wo_partParts planned and usedwork_order_id → cmms_work_order req; item_id → core_item req; qty_planned qty; qty_used qty; storage_location_id → core_storage_location; unit_cost money; inventory_txn_ref uuid, a soft link to inv_inventory_transaction
cmms_calibration_recordA calibration event, as found and as leftequipment_id → core_equipment req; work_order_id → cmms_work_order; procedure_doc_ref uuid, a soft link to doc_controlled_document; due_at date; performed_at ts; performed_by → core_person; reference_standard_id → core_equipment; as_found, as_left and tolerance json; result text req: pass, fail, adjusted_pass or out_of_tolerance; certificate_attachment_id → core_attachment; impact_assessment_ref uuid, a soft link to qms_quality_event

Rules the schema holds

  • Four soft links. permit_ref, procedure_doc_ref, inventory_txn_ref and impact_assessment_ref are plain uuid columns with no foreign key, because the modules they point to (M11, M06, M12, M08) may not be installed yet. Everything else points only at core_ tables and at other cmms_ tables. For exchange with systems outside the platform, the specification names MIMOSA's open models, CCOM and OIIE, as the integration reference [4].
  • One profile for one machine. equipment_id on cmms_asset_profile is required and unique, so a second profile for the same equipment is refused. core_equipment.status is the master status; the profile's status is mapped to it and a test checks they agree.
  • Numbers come from a sequence. request_no and wo_number are unique per tenant and issued from core_number_sequence, not counted by hand.
  • One open work order for one schedule. A partial unique index on cmms_work_order (pm_schedule_id) applies only while the status is not completed, closed or cancelled, so the database itself refuses a second open work order for one PM schedule. Chapter 3 relies on it.
  • Four failure keys, not one text. The four key columns on cmms_work_order point at cmms_failure_code, whose code_type and equipment_class_id say which list a code belongs to.

Five roles, four machine accounts

Roles are jobs, not names. Each maps to a row in the spine's core_job_role. A person who holds two roles may not approve their own work.

RoleWhat they do in the module
RequesterEvery signed-in employee. Raises a request with a photo and reads own requests. No right on cmms_asset_profile, which holds purchase and replacement cost
TechnicianReads own assigned work orders and their tasks and plans; clocks labour; records measurements and meter readings; completes work with a signature, offline if needed
PlannerCreates and plans work orders; builds job plans and PM schedules; schedules against craft capacity; converts accepted requests; sees stock below the reorder point
Maintenance supervisorTriages requests, approves work, reviews failure coding and closes work orders. Not the production supervisor, who only holds requester
Reliability engineerOwns criticality, failure codes, meters and PM schedules, and reads the reliability dashboard

Four machine accounts act with their own login and no person behind them: cmms_pm_engine generates preventive work, cmms_stock_route posts parts to the inventory ledger, cmms_calibration_route records calibration results and raises impact assessments, and cmms_import is used only for the data move in Session 5 and removed in Session 6. Nobody gets delete anywhere: a mistake is a new row, a reversing ledger entry or a status. No role holds a right on core_number_sequence either: request and work order numbers come from a security-definer function, a function that runs with its owner's rights so the caller needs none.

Five roles and five screensA grid of roles against screens. The requester uses the request screen, the technician the work screen, the planner the schedule and queue, the maintenance supervisor the queue, and the reliability engineer the reliability dashboard. Some roles also read other screens. ROLERequestWorkScheduleQueueReliabilityRequester (everyone)usesTechnicianusesPlannerusesusesreadsMaintenance supervisorreadsusesreadsReliability engineerreadsusesThe technician's sync route is /api/maintenance/sync; parts and calibration routes run as machine accounts.
Five roles, five screens. The requester uses the request screen, the technician the work screen, the planner the schedule and the queue, the supervisor the queue, and the reliability engineer the reliability dashboard. Some roles also read other screens.

The screens are /maintenance/request (opened by a QR label whose address carries the equipment code), /maintenance/work (the technician's offline list), /maintenance/schedule (the weekly schedule against each craft's capacity), /maintenance/queue (triage, approval and closeout review) and /maintenance/reliability (the dashboard). The module's own routes are /api/maintenance/sync, /api/maintenance/parts and /api/maintenance/calibration.

Exercise · Profile one real asset15 minutes

You need: One machine from the first area you chose in Session 1, its row in your equipment list, and your AI coding agent

Work on paper or in a text file first. Use the machine's real code but no person's name. Leave a field empty when you do not know it; never guess a date or a cost.

Outcome: A one-page profile for a real machine, with no invented values, and a row your agent drew that you checked against the column table, plus the two inserts it said the database would refuse.

Knowledge check

What may a requester do with cmms_asset_profile?

Knowledge check

Why are permit_ref and inventory_txn_ref plain uuid columns with no foreign key?

References

  1. Accendo Reliability: setting up the asset hierarchy (ISO 14224 levels). https://accendoreliability.com/setup-asset-hierarchy/
  2. Reliability Magazine: how to build an asset hierarchy in a CMMS. https://reliamag.com/guides/how-to-build-asset-hierarchy-cmms/
  3. ISO 14224:2016 Petroleum, petrochemical and natural gas industries: collection and exchange of reliability and maintenance data for equipment. https://www.iso.org/standard/64076.html
  4. MIMOSA: open standards for asset-management information exchange (CCOM, OIIE). https://www.mimosa.org/
  5. Limble: Fiix alternatives and core CMMS features. https://limblecmms.com/?p=26572

Chapter 3 · The workflow and its rules

Work orders, PM, failure coding and calibration

A work order is a small state machine with guards at the start and the finish. Preventive work is generated by an engine, once and only once. A job is not closed until it says how and why the machine failed. Parts leave the stock through the ledger, and a calibration says what the instrument read before and after.

30 min10 statuses4 PM triggers4 failure keys

By the end of this chapter you can

  • Trace a request to a closed work order and name the role that makes each move.
  • Explain how calendar, meter, condition and hybrid triggers create exactly one work order, and how floating and fixed schedules differ.
  • Say why closeout needs four structured failure keys, and what the LOTO guard refuses.
  • Describe how parts and calibration results are recorded and what an out-of-tolerance result starts.

From request to work order

Anyone can raise a work request from a QR label, a phone, a shift log entry, a failed form item or a SCADA condition. A request is one row in cmms_work_request with the status new, the source, who asked and, if known, the equipment and a photo. The maintenance supervisor triages it: accepted, rejected (with the reason in a comment) or duplicate. Each decision tells the requester in the same transaction, and by default the message stays inside the app: texts and email cost whatever your provider charges, so they are switched on only after you have read its price page. The planner then turns an accepted request into a draft work order and marks the request converted, with work_order_id filled in.

The ten statuses

A work order starts as a draft and ends as closed or cancelled. Row-level security cannot see which move a row makes, so one trigger on cmms_work_order holds the rule: it allows only the moves below and refuses every other.

The ten work order statusesA work order moves from draft to awaiting_approval, approved, planned and scheduled, then in_progress, which can go on_hold and back. It is completed with a signature, then closed by a supervisor, or sent back to in_progress. Any state before in_progress can be cancelled. Nothing leaves closed or cancelled. draftnew row: plannerawaiting_approvalplanner submitsapprovedsupervisor approvesplannedplanner, job planscheduledplanner, dates, crewin_progresstechnician startson_holdreason code requiredcompletedtechnician, signaturedone + signaturesent backclosedsupervisor reviewscancelledwith a comment, before work startsNothing leaves closed or cancelled.Each move is one a named role may make.
The ten statuses. draft, awaiting_approval, approved, planned and scheduled lead to in_progress, which can go on_hold and back. A technician completes it with a signature; a supervisor closes it or sends it back. Any state before in_progress can be cancelled. Nothing leaves closed or cancelled.
MoveWhoWhat the rule demands
draft → awaiting_approvalPlannerNothing more
awaiting_approval → approvedMaintenance supervisorAn approval row, step 1
approved → plannedPlannerA job plan; its tasks are copied onto the order, so later edits to the plan do not change an issued order
planned → scheduledPlannerScheduled start and end, craft and assignee
scheduled → in_progressTechnicianactual_start is set; the LOTO guard below
in_progress ↔ on_holdTechnicianA hold reason code
in_progress → completedTechnicianactual_end not before actual_start, and a signature
completed → closed, or back to in_progressMaintenance supervisorClosing needs the failure keys; sending back needs a comment, and the supervisor may not close an order they are assigned to
any state before in_progress → cancelledPlanner or supervisorA comment

A new row may start only as draft (the planner), as approved (the PM engine, or the supervisor for an emergency breakdown) or, for imported open orders and history in Session 5, in any status before in_progress or as closed (the import account only). Because a right on a table is a right on its whole row, the same trigger also refuses any change to equipment, type, priority, scheduled dates or assignee by anyone but the planner or supervisor.

A trigger on the same table writes the module's events to core_event_outbox. It publishes cmms.work_order.status_changed and cmms.work_order.completed there, and four more triggers write the rest, because core_event_outbox is written only by triggers and no role has a right on it: cmms.work_request.created on insert into cmms_work_request, cmms.pm.generated on insert into cmms_work_order where pm_schedule_id is set, cmms.calibration.out_of_tolerance on insert into cmms_calibration_record with result out_of_tolerance, and cmms.asset.out_of_service on cmms_asset_profile when status becomes out_of_service. It consumes five events from other modules when those modules are installed: shf.log_entry.created (shift handover, M02), frm.response.failed (digital forms, M01), mes.equipment.state_changed (MES, M04), ehs.permit.closed (EHS, M11) and inv.item.stock_below_reorder (inventory, M12). Nothing in this course needs them, so the module works without them.

Two guards: LOTO at the start, coding at the end

Lockout and tagout. A job plan with loto_required true cannot start without a permit reference. The specification enforces that reference in the database when M11 is installed; where it is not, a manual reference is typed instead: the technician enters the number of the paper lockout permit, it is stored in ext as permit_ref_manual, and the start is refused while both it and permit_ref are empty. OSHA's standard on the control of hazardous energy (29 CFR 1910.147) is the reason: a machine must be isolated before it is serviced [2]. The permit itself is issued and owned by M11, so the CMMS holds only the soft link, permit_ref. When the EHS module and its ehs_permit table exist, the permit's status must also be active, and the trigger checks that table's existence first so it still runs where the module is not installed.

Measurements are judged by the server. A task measurement outside its lower and upper limit (lsl and usl on the job plan task) is stored as result fail by the server, never by the browser, and a failed task raises a work request. Labour rows take their rate from cmms_craft.hourly_rate on the server, never from the phone.

Closeout coding

When a corrective or predictive work order is closed it must carry four structured keys: how it failed (mode), why (cause), the physical process (mechanism) and what was done (remedy). Each points at a cmms_failure_code of the right code_type, whose equipment class is the equipment's class or empty. ISO 14224 supplies the pick-lists [1][4]. The supervisor reviews the coding before closing; a closeout with a code missing is refused.

Four failure keys, not one notecmms_work_order holds four keys, failure_mode_id, failure_cause_id, failure_mechanism_id and remedy_id, each pointing at cmms_failure_code, whose code_type says which list a code belongs to. A single free-text note cannot be counted. cmms_work_orderfailure_mode_idfailure_cause_idfailure_mechanism_idremedy_idfour separate keyscmms_failure_codemode: how it failedcause: why it failedmechanism: the physical processremedy: what was doneA free-text note onlyno Pareto, no bad-actor rankingFour codes at closeoutcorrective and predictive work cannot close without them
Four failure keys, not one note. cmms_work_order holds four keys, each pointing at cmms_failure_code, whose code_type says which list a code belongs to. A free-text note alone cannot be counted, so no Pareto and no bad-actor ranking can be built from it.

Preventive maintenance without double work

A cmms_pm_schedule says which job plan runs on which equipment, and what triggers it. One SQL function, cmms_generate_pm(as_of), run by the machine account cmms_pm_engine, finds every active schedule on a maintainable asset that is due at the given time and makes one preventive work order for each, with its tasks. Tests run it with a fixed clock. Scheduled preventive work of this kind is one of the core features every CMMS product offers [5]. The engine first rolls forward the schedules whose work order was completed, then generates, so a completed order never leaves its schedule due twice. A forecast function lists the dates each calendar schedule falls due in the next 90 days, so the planner sees the work coming.

Four PM triggers, one work orderCalendar, meter, condition and hybrid triggers all feed cmms_generate_pm, which makes one preventive work order. A partial unique index on pm_schedule_id refuses a second open work order for one schedule. Calendarnext_due_at minus lead_days reachedMeterreading reaches next_due_meterConditioncondition_rule is trueHybridcalendar or meter, either onecmms_generate_pmrun by cmms_pm_engineas of a given timeOne work orderpreventive, status approvedwith its tasksPartial unique index on cmms_work_order (pm_schedule_id)applies while status is not completed, closed or cancelledrefuses a second open order
Four triggers, one work order. Calendar, meter, condition and hybrid triggers all feed cmms_generate_pm, which makes one preventive work order. A partial unique index on pm_schedule_id refuses a second open order for the same schedule.
TriggerDue whenExample
calendarnext_due_at minus lead_days is not after the given timeA PM every 30 days
meterThe latest reading has reached next_due_meterA PM every 500 cycles on the second asset's cycle counter
conditionThe rule in condition_rule is true: one comparison such as {"meter_id": the meter's id, "op": ">", "value": 80} on the meter's latest readingA threshold on a tag-fed meter
hybridEither of the calendar and meter conditions is metEvery 30 days or 500 hours, whichever comes first

Because the database already refuses a second open work order for one schedule, running the engine twice makes one. The seed proves it: on the cycle counter of the second asset, with a next_due_meter of 500, the readings 495, 505 and 512, with the engine run after each, give exactly one open work order, made at 505 and still the only one after 512. The 8000-hour meter on the first asset is not used for this test; it is kept for the MTBF and MTTR check. A cumulative meter that goes down is refused, because a replaced meter needs a new cmms_meter row. Separately, when a reading passes alert_high or falls below alert_low, the engine inserts one work request with source scada, but only while an earlier one is still new or accepted.

Floating or fixed

When a PM work order is completed the engine sets last_completed_at and the next due value. A floating schedule counts the next interval from the day the work was done: 30 days after a PM completed 10 days late, or the meter value at completion plus meter_interval. A fixed schedule counts from the previous due value, adding intervals until the next one is in the future, so a long gap does not create a burst of work orders.

Floating and fixed schedules, to scaleA 30-day PM due on day 30 is completed on day 40, ten days late. A floating schedule counts the next interval from completion and is due on day 70. A fixed schedule counts from the planned date and is due on day 60. Drawn to scale at 10 pixels a day. day 0day 30 dueday 40 doneday 60day 70Floating10 days late30 days from completion: next due day 70Fixed30 days from the planned date: next due day 60Floating: the work was late, so the next one waits a full interval after the day it was done.Fixed: the calendar is kept; add intervals until the next date is in the future.
Floating and fixed, to scale. A 30-day PM is due on day 30 and done on day 40, ten days late. Floating counts 30 days from completion and is next due on day 70. Fixed counts from the planned date and is due on day 60. Drawn at 10 pixels a day.

Parts and calibration

Parts go through the ledger. The phone sends an id it made itself, the work order, the item and the quantity. The route, run as cmms_stock_route, for a use checks that the work order is assigned to the caller and is in_progress, or completed and not yet closed (a phone that worked offline sends its parts after its completion entry has arrived; the queue sends each part move to this route after the entry is accepted, with its own id), and for a return checks that the caller holds the maintenance supervisor role and the work order is not cancelled. It inserts the cmms_wo_part row, posts one inv_inventory_transaction from the storage location against the work order, and writes unit_cost and inventory_txn_ref back, all in one transaction. A retry posts once. Parts returned unused are a reversing entry that names the original; the ledger is append-only and a part count is never changed by editing a number in a cmms_ table. Stock at or below reorder_point on cmms_spare_part_link shows on the planner's schedule screen.

Calibration records what the instrument read. A calibration work order has measurement tasks for the as-found points, then the adjustment, then the as-left points. On completion /api/maintenance/calibration, run as cmms_calibration_route, builds as_found and as_left from the results, compares them with the limits copied into tolerance, and inserts one cmms_calibration_record with the result pass, fail, adjusted_pass or out_of_tolerance. The certificate is a core_attachment, and the record's due_at comes from the instrument's calibration schedule, a calendar PM schedule whose work orders are of type calibration. The route refuses a reference standard whose own latest calibration is past its due date, which is the traceability that ISO 10012 and ISO/IEC 17025 ask for [3].

Exercise · Run a meter PM and a late PM on paper15 minutes

You need: Paper or a spreadsheet, and your AI coding agent for the last step

Use the seed's numbers: a meter PM every 500 cycles on the second asset's cycle counter with next_due_meter 500, and a calendar PM every 30 days. Nothing here is real data.

Outcome: A filled-in table of three readings and two next-due values (1012 and 1000 cycles; day 70 and day 60), your floating or fixed choice for a real PM, and a test your agent wrote that expects exactly one work order.

Knowledge check

A 30-day floating PM, due on day 30, is completed on day 40. When is it next due?

Knowledge check

A corrective work order has a failure mode and a cause but no mechanism or remedy. What should happen at closeout?

References

  1. Fabrico: ISO 14224 failure taxonomy codes. https://www.fabrico.io/blog/iso-14224-failure-taxonomy-codes/
  2. OSHA: 29 CFR 1910.147, The control of hazardous energy (lockout/tagout). https://www.osha.gov/laws-regs/regulations/standardnumber/1910/1910.147
  3. ISO/IEC 17025:2017 General requirements for the competence of testing and calibration laboratories. https://www.iso.org/standard/66912.html
  4. ISO 14224:2016 Petroleum, petrochemical and natural gas industries: collection and exchange of reliability and maintenance data for equipment. https://www.iso.org/standard/64076.html
  5. Limble: Fiix alternatives and core CMMS features. https://limblecmms.com/?p=26572

Chapter 4 · Phone, figures and the move

Offline work, reliability measures and cutover

A technician's phone is often out of signal exactly where the machine is. This chapter covers how work finished offline reaches the database once and only once, the seven measures that say whether maintenance is working and how to check them by hand, and how to move the old system in and switch it off.

20 min7 measures20 offline orders to test4 tests of done

By the end of this chapter you can

  • Describe how offline work is queued and synced without loss or duplication, and what happens to a stale status move.
  • Define PM compliance, schedule compliance, MTBF, MTTR, planned against reactive, backlog and cost against replacement value.
  • Check MTBF and MTTR by hand against the dashboard.
  • Plan a cutover from an incumbent CMMS and state the four tests that mean the module is done.

Offline execution

The technician's work screen saves, on sign-in, the technician's assigned work orders, tasks, job plans, pick-lists and spares list to IndexedDB, the browser's own database on the phone, which survives closing the app, with a "last updated" time. Each change is queued with an id the phone made itself using crypto.randomUUID() (a function every modern browser has that makes a random id no other phone will pick), so twenty queued completions are twenty separate records and none can overwrite another. The queue is sent when a connection exists and retried until the server confirms.

From the technician's phone to the serverThe phone saves assigned work, queues each change with an id it made itself, and sends when it can. The server inserts once, checks the row version, and confirms or refuses; a refusal stays in the queue. PHONE (MAY BE OFFLINE)Save the workorders, tasks, plans in IndexedDBQueue each changeid from crypto.randomUUID()Send when it canretried until confirmedSERVERInsert onceon conflict (id) do nothingCheck row_versiona stale status move is refusedConfirm or refusea refusal stays in the queueSend the same queue twice: the counts do not change.
From the technician's phone to the server. The phone saves assigned work and queues each change with its own id. The server inserts once, checks the row version, and confirms or refuses. A refusal stays in the queue.
  • Insert once. The sync route inserts new rows with on conflict (id) do nothing, so a repeated send does nothing the second time.
  • Set, never add. A task result or a status move sets a value; setting the same value twice leaves the same result. That property is called idempotent.
  • Stale moves are refused. The queue keeps one entry per job holding its moves in order (start, task results, labour, completion), with the row_version the phone saw when it last downloaded the job. The route applies the moves in order in one transaction and checks the version once, against the first move, because each applied move adds one to row_version. It refuses the whole entry when the row has changed in the meantime, for example when a planner cancelled the job, and answers a repeat of an entry it has already applied as done. The refusal is kept and shown, never deleted.
  • Two clocks. The phone's own times go in actual_start, actual_end, done_at and started_at; the server's receiving time goes in ext as synced_at.
  • Expired sign-in waits. If the sign-in has expired when the phone reconnects, the queue waits for sign-in and loses nothing.

Seven measures

The module's KPIs follow the maintenance metrics of EN 15341 and SMRP [1]. Each can be counted from the tables in chapter 2, so a dashboard can be checked against a hand count.

MeasureDefinitionCounted from
PM compliance (SMRP 5.4.x)PM work orders completed within their window ÷ PM work orders duecmms_work_order, cmms_pm_schedule
Schedule complianceScheduled hours worked as scheduled ÷ scheduled hourscmms_work_order, cmms_wo_labor
MTBFOperating time ÷ number of failures, per asset or classcmms_meter_reading, closed corrective orders
MTTRTotal repair time ÷ number of repairsactual_start and actual_end of repairs
Planned against reactivePlanned maintenance hours ÷ total maintenance hourscmms_wo_labor by work order type
Backlog (crew-weeks)Estimated hours of ready backlog ÷ weekly craft capacitycmms_job_plan, capacity_hours_per_week
Maintenance cost as % of RAVMaintenance cost ÷ replacement asset valuelabour and parts cost, replacement_cost

MTBF is mean time between failures, MTTR mean time to repair, and RAV the replacement asset value, which is why replacement_cost sits on the asset profile. Beyond these, the dashboard ranks bad actors (the assets that fail or cost the most) and draws a failure Pareto by equipment class and mode, which only works because the codes are structured.

MTBF and MTTR on the seed, to scaleTop: eight thousand operating hours with four failures, drawn as four equal stretches of two thousand hours, so MTBF is 2000 hours. Bottom: four repairs of 3, 5, 4 and 8 hours, 20 hours in all, so MTTR is 5 hours. Each panel is drawn to its own scale. A year of running on the seed: 8000 hours, 4 failures100 px = 1000 hours2000 h2000 h2000 h2000 hMTBF8000 h ÷ 4 failures = 2000 hThe four repairs, in order: 3, 5, 4 and 8 hours30 px = 1 hour3 h5 h4 h8 hMTTR20 h ÷ 4 repairs = 5 h
MTBF and MTTR on the seed, to scale. Top: 8000 operating hours and four failures make four stretches of 2000 hours, so MTBF is 2000 hours. Bottom: four repairs of 3, 5, 4 and 8 hours total 20 hours, so MTTR is 5 hours. Each panel has its own scale.

The check is the point. If the dashboard says 2000 and your hand count of the seed says 2000, the dashboard is right for that data. If they differ, either the query's definition or the data is wrong, and the difference must be explained before anyone trusts the figures on real work. Operating time normally excludes the time the machine was down; the seed takes the running-hours meter's 8000 hours as they are and does not subtract the 20 repair hours, so the figure stays easy to check by hand. On real data, write in your view's definition which you use.

Moving off the old system

Per the module specification, Fiix, MaintainX, Limble and UpKeep export assets, PMs, work orders and parts as CSV or through an API; for any other system, read its own export page [2][3][4]. Work orders contain people's names, so keep the export in a private folder outside the repository until you have read what it holds.

  1. Count the rows in each file and compare them with the counts you took in Session 1. A truncated file is found now, not after cancellation.
  2. Map asset ids to spine equipment codes first, in three outcomes: matched, near match for a person to decide, and no match. Never create a separate maintenance list.
  3. Map failure codes: the free way is mapping the most used ones by hand. An AI step is optional and costs what your provider charges; it sends only distinct old texts and the code list, and a person decides each proposal.
  4. Import job plans and PM schedules with their old next due dates unchanged, switched off, and dry-run the engine before switching any on, because a burst of overdue work orders is the usual surprise.
  5. Load spares without the old on-hand counts, which are usually stale; count the critical spares and load opening stock as ledger adjustments.
  6. Import open work orders, then closed work orders as read-only history, with the old numbers kept in ext, and no ledger posting for history.
  7. Run old and new side by side for a week, compare the figures, cut over on a date you set, and cancel the old subscription with the saving written from real invoices.

Done means testable

The definition of done is four tests an agent can run, and you should run them yourself. ISO 55001 asks an asset management system to monitor, measure and evaluate its own performance [5]; these tests and the seven measures are how this module does it.

  • A meter reading crossing a threshold generates exactly one PM work order.
  • A LOTO-required work order cannot start without a permit reference (where the permit module is present).
  • MTBF and MTTR on the seeded data match a spreadsheet calculation.
  • Mobile offline completion of 20 work orders syncs without loss.

In the module's ecosystem, equipment is the shared spine hierarchy, downtime comes from MES equipment-state events, and the activity-based costing of the CivOps core consumes the labour and parts cost recorded here.

Exercise · Check MTBF and MTTR by hand15 minutes

You need: The Session 2 seed (or one asset's closed corrective work orders from your own export), and a spreadsheet

Use the seed first. If you have real closed work orders for one asset, repeat steps 2 to 5 on them afterwards.

Outcome: A spreadsheet that reproduces the dashboard's MTBF (2000 hours) and MTTR (5 hours) from raw rows, and a one-line explanation of why a longer repair changes MTTR and not MTBF.

Knowledge check

A phone sends the same completed work order twice because the first confirmation was lost. What does the server do?

Knowledge check

Suppose ready backlog is 240 estimated hours and a craft has 80 hours of weekly capacity. What is the backlog in crew-weeks?

References

  1. SMRP: Society for Maintenance and Reliability Professionals, maintenance and reliability metrics. https://smrp.org/
  2. Limble: Fiix alternatives and core CMMS features. https://limblecmms.com/?p=26572
  3. GetApp: MaintainX against Limble (modules). https://www.getapp.ca/compare/117239/2049215/getmaintainx/vs/limble-cmms
  4. EZO: EZO against Fiix and Limble. https://ezo.io/ezo-cmms/blog/ezo-vs-fiix-vs-limble/
  5. ISO 55001:2014 Asset management: management systems, requirements. https://www.iso.org/standard/55089.html

Chapter 5 · 12 questions · 80% passes

Final assessment

Twelve questions across the element. Score 80% (10 of 12) to pass. Your LMS records your score and each answer; you can review the chapters and try again.

15 min12 questions≈ 15 minutesRetake allowed

Choose one answer for each question, then submit. You will see the right answer and why for every question.

1. What does cmms_asset_profile hold?
2. Which module issues permits and owns the lockout procedure, which the CMMS only references?
3. A job plan has loto_required true. What must exist before its work order can move to in_progress?
4. How is PM compliance defined in this module?
5. On the seed, an asset runs 8000 hours in a year with four failures. What is its MTBF?
6. What does the partial unique index on cmms_work_order (pm_schedule_id) do?
7. A meter PM every 500 units, due at reading 500, is completed at reading 512. If the schedule is fixed, when is it next due?
8. What must a corrective work order carry before it can be closed?
9. Why can a work order sent twice from a phone not be stored twice?
10. A calibration finds an instrument out of tolerance. What must the module do?
11. What does ISO 14224 provide to this module?
12. How is backlog in crew-weeks calculated?