Database Schema

MokoSuite Field uses 10 tables, all prefixed with #__mokosuitefield_. Tables are created by the system plugin's sql/install.sql script.

Tables

`#__mokosuitefield_technicians`

Technician profiles linked to Joomla contacts.

Column	Type	Description
`id`	INT UNSIGNED PK	Auto-increment ID
`contact_id`	INT	FK to Joomla contact
`name`	VARCHAR(255)	Technician name
`email`	VARCHAR(255)	Email address
`phone`	VARCHAR(50)	Phone number
`skills`	VARCHAR(500)	Comma-separated skills
`hourly_rate`	DECIMAL(10,2)	Billing rate per hour
`status`	ENUM	`active`, `inactive`, `on_leave`, `training`
`current_lat` / `current_lng`	DECIMAL(10,7)	GPS position
`published`	TINYINT	Published state
`created`	DATETIME	Record creation timestamp

`#__mokosuitefield_equipment`

Customer equipment registry with warranty and location tracking.

Column	Type	Description
`id`	INT UNSIGNED PK	Auto-increment ID
`customer_contact_id`	INT	FK to customer contact
`name`	VARCHAR(255)	Equipment name
`equipment_type`	ENUM	`hvac`, `plumbing`, `electrical`, `appliance`, `generator`, `elevator`, `fire_system`, `other`
`brand`	VARCHAR(100)	Manufacturer brand
`model`	VARCHAR(100)	Model number
`serial_number`	VARCHAR(100)	Serial number
`install_date`	DATE	Installation date
`warranty_expiry`	DATE	Warranty expiration
`location_address`	VARCHAR(500)	Site address
`notes`	TEXT	Free-form notes
`published`	TINYINT	Published state
`created`	DATETIME	Record creation timestamp

`#__mokosuitefield_equipment_history`

Service history log for each piece of equipment.

Column	Type	Description
`id`	INT UNSIGNED PK	Auto-increment ID
`equipment_id`	INT UNSIGNED	FK to equipment
`work_order_id`	INT UNSIGNED	FK to work order (nullable)
`action`	ENUM	`install`, `repair`, `maintenance`, `inspection`, `replacement`, `decommission`
`description`	VARCHAR(500)	Action description
`technician_id`	INT UNSIGNED	FK to technician
`action_date`	DATE	Date of action
`cost`	DECIMAL(10,2)	Cost of the action
`created`	DATETIME	Record creation timestamp

`#__mokosuitefield_work_orders`

Core work order records with full lifecycle tracking.

Column	Type	Description
`id`	INT UNSIGNED PK	Auto-increment ID
`work_order_ref`	VARCHAR(20) UNIQUE	Reference number
`customer_contact_id`	INT	FK to customer contact
`customer_name`	VARCHAR(255)	Customer display name
`customer_phone`	VARCHAR(50)	Customer phone
`equipment_id`	INT UNSIGNED	FK to equipment (nullable)
`technician_id`	INT UNSIGNED	FK to assigned technician
`status`	ENUM	`requested`, `scheduled`, `dispatched`, `in_progress`, `on_hold`, `completed`, `invoiced`, `cancelled`
`priority`	ENUM	`emergency`, `high`, `normal`, `low`
`work_type`	ENUM	`repair`, `maintenance`, `installation`, `inspection`, `warranty`, `callback`
`title`	VARCHAR(255)	Work order title
`description`	TEXT	Detailed description
`site_address`	VARCHAR(500)	Service site address
`site_lat` / `site_lng`	DECIMAL(10,7)	GPS coordinates
`scheduled_date`	DATE	Scheduled service date
`dispatched_at`	DATETIME	Dispatch timestamp
`completed_at`	DATETIME	Completion timestamp
`labor_hours`	DECIMAL(5,2)	Hours worked
`labor_cost`	DECIMAL(10,2)	Labor cost
`parts_cost`	DECIMAL(10,2)	Parts cost
`total_cost`	DECIMAL(10,2)	Total cost
`customer_signature`	TEXT	Base64 signature data
`notes`	TEXT	Internal notes
`created`	DATETIME	Record creation timestamp
`created_by`	INT	Joomla user ID of creator

`#__mokosuitefield_parts`

Parts/materials inventory with reorder tracking.

Column	Type	Description
`id`	INT UNSIGNED PK	Auto-increment ID
`name`	VARCHAR(255)	Part name
`part_number`	VARCHAR(100)	Manufacturer part number
`category`	VARCHAR(100)	Part category
`unit_cost`	DECIMAL(10,2)	Cost per unit
`sell_price`	DECIMAL(10,2)	Selling price
`stock_qty`	INT	Current stock quantity
`reorder_level`	INT UNSIGNED	Reorder threshold (default: 5)
`supplier`	VARCHAR(255)	Supplier name
`published`	TINYINT	Published state
`created`	DATETIME	Record creation timestamp

`#__mokosuitefield_truck_inventory`

Parts loaded on each technician's service vehicle.

Column	Type	Description
`id`	INT UNSIGNED PK	Auto-increment ID
`technician_id`	INT UNSIGNED	FK to technician
`part_id`	INT UNSIGNED	FK to part
`quantity`	INT	Quantity on truck
`last_restocked`	DATE	Last restock date

Unique constraint on (technician_id, part_id).

`#__mokosuitefield_checklists`

Checklist templates associated with work types.

Column	Type	Description
`id`	INT UNSIGNED PK	Auto-increment ID
`name`	VARCHAR(255)	Checklist name
`work_type`	ENUM	`repair`, `maintenance`, `installation`, `inspection`, `warranty`, `callback`, `all`
`published`	TINYINT	Published state
`ordering`	INT	Display order
`created`	DATETIME	Record creation timestamp

`#__mokosuitefield_checklist_items`

Individual items within a checklist template.

Column	Type	Description
`id`	INT UNSIGNED PK	Auto-increment ID
`checklist_id`	INT UNSIGNED	FK to checklist
`label`	VARCHAR(255)	Item label
`item_type`	ENUM	`checkbox`, `text`, `number`, `photo`, `pass_fail`
`required`	TINYINT	Whether item is required
`ordering`	INT	Display order

`#__mokosuitefield_pm_agreements`

Preventive maintenance service agreements.

Column	Type	Description
`id`	INT UNSIGNED PK	Auto-increment ID
`customer_contact_id`	INT	FK to customer contact
`equipment_id`	INT UNSIGNED	FK to equipment (nullable)
`name`	VARCHAR(255)	Agreement name
`frequency`	ENUM	`monthly`, `quarterly`, `semi_annual`, `annual`
`next_service_date`	DATE	Next scheduled service
`annual_price`	DECIMAL(10,2)	Annual contract price
`status`	ENUM	`active`, `expired`, `cancelled`
`start_date`	DATE	Contract start
`end_date`	DATE	Contract end
`auto_renew`	TINYINT	Auto-renewal flag
`created`	DATETIME	Record creation timestamp

`#__mokosuitefield_dispatches`

Dispatch offers sent to technicians for work orders.

Column	Type	Description
`id`	INT UNSIGNED PK	Auto-increment ID
`work_order_id`	INT UNSIGNED	FK to work order
`technician_id`	INT UNSIGNED	FK to technician
`status`	ENUM	`offered`, `accepted`, `rejected`, `expired`, `cancelled`
`offered_at`	DATETIME	When the offer was sent
`responded_at`	DATETIME	When the technician responded
`distance_km`	DECIMAL(10,2)	Distance to job site
`eta_minutes`	DECIMAL(10,2)	Estimated arrival time
`attempt_number`	TINYINT UNSIGNED	Dispatch attempt number

Relationships

technicians ──┬── work_orders (technician_id)
              ├── dispatches (technician_id)
              ├── truck_inventory (technician_id)
              └── equipment_history (technician_id)

equipment ────┬── work_orders (equipment_id)
              ├── equipment_history (equipment_id)
              └── pm_agreements (equipment_id)

work_orders ──┬── dispatches (work_order_id)
              └── equipment_history (work_order_id)

parts ────────── truck_inventory (part_id)

checklists ───── checklist_items (checklist_id)

Pages