Service Book tables

Mounting history, maintenance plans and tasks, performed work, out-of-service periods and cleaning. A ✓ in the Null column means the field is optional. Constraint definitions are reproduced verbatim from PostgreSQL, so they are the exact rule being enforced. Descriptions come from the models; a blank one means the field carries no description yet, not that it has no meaning.

`servicebook_vehicleconfiguration`

Column	Type	Null	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`mounted_at`	timestamptz		When the component was mounted
`mounted_mileage_km`	int	✓	Vehicle odometer at mount time
`dismounted_at`	timestamptz	✓	When dismounted (NULL = currently mounted)
`dismounted_mileage_km`	int	✓	Vehicle odometer at dismount time
`dismount_reason`	varchar(255)		Reason for dismount
`notes`	text		Free-text notes
`external_system_id`	varchar(100)	✓	Integration key from master system
`external_updated_at`	timestamptz	✓	Source system last-modified timestamp
`component_id`	uuid		The mounted component
`project_id`	uuid		Parent project for multi-tenancy
`slot_id`	uuid		The slot position on the vehicle type
`vehicle_id`	uuid		The vehicle this configuration belongs to
`component_mileage_at_mount`	int		Snapshot of component.total_mileage_km at mount time
`depot`	varchar(100)	✓	Where the dismount was performed

Constraints — 1 check, 3 unique indexes, 4 foreign keys, 1 primary key

Kind	Name	Definition
Check	`depot_required_on_dismount`	`(((dismounted_at IS NULL) OR (depot IS NOT NULL)))`
Foreign key		`(component_id) REFERENCES assets_component(id)`
Foreign key		`(project_id) REFERENCES project(id)`
Foreign key		`(slot_id) REFERENCES assets_vehicletypeslot(id)`
Foreign key		`(vehicle_id) REFERENCES assets_vehicle(id)`
Primary key		`(id)`
Unique index	`component_single_active_mount`	`(component_id) WHERE (dismounted_at IS NULL)`
Unique index	`slot_single_active_component`	`(vehicle_id, slot_id) WHERE (dismounted_at IS NULL)`
Unique index	`unique_config_external_id_per_project`	`(external_system_id, project_id) WHERE (external_system_id IS NOT NULL)`

`servicebook_maintenanceplan`

Column	Type	Null	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`external_system_id`	varchar(100)		Identity from master maintenance system
`revision`	varchar(50)	✓	Informational version (e.g. ‘3’, ‘2024-R2’)
`name`	varchar(200)		Plan name (e.g. ‘X60A Maintenance Program’)
`description`	text		Plan description
`external_updated_at`	timestamptz	✓	Source system last-modified timestamp
`component_type_id`	uuid	✓	Target component type (XOR with vehicle_type)
`project_id`	uuid		Parent project for multi-tenancy
`vehicle_type_id`	uuid	✓	Target vehicle type (XOR with component_type)

Constraints — 1 check, 1 unique, 2 unique indexes, 3 foreign keys, 1 primary key

Kind	Name	Definition
Check	`maintenance_plan_xor_target`	`((((component_type_id IS NULL) AND (vehicle_type_id IS NOT NULL)) OR ((component_type_id IS NOT NULL) AND (vehicle_type_id IS NULL))))`
Foreign key		`(component_type_id) REFERENCES assets_componenttype(id)`
Foreign key		`(vehicle_type_id) REFERENCES assets_vehicletype(id)`
Foreign key		`(project_id) REFERENCES project(id)`
Primary key		`(id)`
Unique	`unique_plan_external_id_per_project`	`(external_system_id, project_id)`
Unique index	`one_plan_per_component_type_per_project`	`(component_type_id, project_id) WHERE (component_type_id IS NOT NULL)`
Unique index	`one_plan_per_vehicle_type_per_project`	`(vehicle_type_id, project_id) WHERE (vehicle_type_id IS NOT NULL)`

`servicebook_maintenancetask`

Column	Type	Null	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`external_system_id`	varchar(100)		Task identity from master maintenance system
`name`	varchar(200)		Task name (e.g. ‘Bogie Overhaul’)
`description`	text		Task description
`task_type`	varchar(10)		RECURRING, ONE_TIME, or SEASONAL
`group`	varchar(100)	✓	Grouping label (e.g. ‘A-service’, ‘C1-overhaul’)
`interval_type`	varchar(10)		Primary interval measurement unit
`interval_value`	int		Primary interval value
`tolerance_before`	int		Can do this many units early
`tolerance_after`	int		Overdue after this many units late
`secondary_interval_type`	varchar(10)	✓	Secondary interval measurement unit
`secondary_interval_value`	int	✓	Secondary interval value
`secondary_tolerance_before`	int		Secondary: can do this many units early
`secondary_tolerance_after`	int		Secondary: overdue after this many units late
`season_start_month`	int2	✓	Start month (1-12) for seasonal tasks
`season_start_day`	int2	✓	Start day (1-31) for seasonal tasks
`season_end_month`	int2	✓	End month (1-12) for seasonal tasks
`season_end_day`	int2	✓	End day (1-31) for seasonal tasks
`safety`	bool		Whether this is a safety-critical task
`location`	varchar(100)	✓	Location on vehicle/component
`sort_order`	int		Position within the plan
`external_updated_at`	timestamptz	✓	Source system last-modified timestamp
`maintenance_plan_id`	uuid		Parent maintenance plan
`project_id`	uuid		Denormalized from plan for multi-tenancy filtering
`estimated_cost`	numeric(12,2)	✓	Estimated total cost per execution, in project currency
`estimated_manhours`	numeric(7,2)	✓	Estimated labour hours per execution (locally maintained)

Constraints — 8 checks, 1 unique, 2 foreign keys, 1 primary key

Kind	Name	Definition
Check	`maintenance_task_seasonal_fields_check`	`((((season_end_day IS NOT NULL) AND (season_end_month IS NOT NULL) AND (season_start_day IS NOT NULL) AND (season_start_month IS NOT NULL) AND ((task_type)::text = 'SEASONAL'::text)) OR ((NOT ((task_type)::text = 'SEASONAL'::text)) AND (season_end_day IS NULL) AND (season_end_month IS NULL) AND (season_start_day IS NULL) AND (season_start_month IS NULL))))`
Check	`maintenance_task_secondary_interval_all_or_nothing`	`((((secondary_interval_type IS NULL) AND (secondary_interval_value IS NULL)) OR ((secondary_interval_type IS NOT NULL) AND (secondary_interval_value IS NOT NULL))))`
Check	`servicebook_maintenancetask_interval_value_check`	`((interval_value >= 0))`
Check	`servicebook_maintenancetask_secondary_interval_value_check`	`((secondary_interval_value >= 0))`
Check	`servicebook_maintenancetask_secondary_tolerance_after_check`	`((secondary_tolerance_after >= 0))`
Check	`servicebook_maintenancetask_secondary_tolerance_before_check`	`((secondary_tolerance_before >= 0))`
Check	`servicebook_maintenancetask_tolerance_after_check`	`((tolerance_after >= 0))`
Check	`servicebook_maintenancetask_tolerance_before_check`	`((tolerance_before >= 0))`
Foreign key		`(maintenance_plan_id) REFERENCES servicebook_maintenanceplan(id)`
Foreign key		`(project_id) REFERENCES project(id)`
Primary key		`(id)`
Unique	`unique_task_external_id_per_plan`	`(external_system_id, maintenance_plan_id)`

`servicebook_maintenancerecord`

Column	Type	Null	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`completed_at`	timestamptz		When the maintenance work was completed
`depot`	varchar(100)	✓	Where the maintenance work was performed
`performance_km`	int		Vehicle performance reading (km) at time of work
`operating_hours`	int	✓	Vehicle operating hours at time of work
`deviation`	int		Primary interval deviation (negative=early, positive=late)
`secondary_deviation`	int	✓	Secondary interval deviation
`notes`	text		Free-text remarks about the work performed
`external_system_id`	varchar(100)		Integration key from source maintenance system
`external_updated_at`	timestamptz		Source system last-modified timestamp
`component_id`	uuid	✓	Component this work was performed on (at-least-one with vehicle)
`maintenance_task_id`	uuid	✓	Optional link to scheduled maintenance task
`project_id`	uuid		Parent project for multi-tenancy
`vehicle_id`	uuid	✓	Vehicle this work was performed on (at-least-one with component)

Constraints — 1 check, 1 unique, 4 foreign keys, 1 primary key

Kind	Name	Definition
Check	`record_must_have_vehicle_or_component`	`(((vehicle_id IS NOT NULL) OR (component_id IS NOT NULL)))`
Foreign key		`(component_id) REFERENCES assets_component(id)`
Foreign key		`(maintenance_task_id) REFERENCES servicebook_maintenancetask(id)`
Foreign key		`(vehicle_id) REFERENCES assets_vehicle(id)`
Foreign key		`(project_id) REFERENCES project(id)`
Primary key		`(id)`
Unique	`unique_record_external_id_per_project`	`(external_system_id, project_id)`

`servicebook_vehicleoutofservice`

Column	Type	Null	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`external_system_id`	varchar(100)		Integration key from source system
`out_of_service_at`	timestamptz		When the vehicle was taken out of service
`in_service_at`	timestamptz	✓	When the vehicle was returned to service (null = still out)
`reason`	varchar(20)		Reason category for out-of-service event
`notes`	text		Free-text detail about the out-of-service event
`depot`	varchar(100)	✓	Where the vehicle is held during out-of-service
`external_updated_at`	timestamptz		Source system last-modified timestamp
`project_id`	uuid		Parent project for multi-tenancy
`vehicle_id`	uuid		Vehicle taken out of service

Constraints — 1 check, 1 unique, 2 foreign keys, 1 primary key

Kind	Name	Definition
Check	`oos_in_service_after_out_of_service`	`(((in_service_at IS NULL) OR (in_service_at > out_of_service_at)))`
Foreign key		`(project_id) REFERENCES project(id)`
Foreign key		`(vehicle_id) REFERENCES assets_vehicle(id)`
Primary key		`(id)`
Unique	`unique_oos_external_id_per_project`	`(external_system_id, project_id)`

`servicebook_cleaningrecord`

Column	Type	Null	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`external_system_id`	varchar(100)		Integration key from source system
`cleaning_type`	varchar(20)		Type of cleaning performed
`status`	varchar(20)		Whether the cleaning was completed or cancelled
`completed_at`	timestamptz		When the cleaning was completed or cancelled
`depot`	varchar(100)	✓	Where the cleaning was performed
`notes`	text		Free-text remarks about the cleaning
`external_updated_at`	timestamptz		Source system last-modified timestamp
`project_id`	uuid		Parent project for multi-tenancy
`vehicle_id`	uuid		Vehicle that was cleaned

Constraints — 1 unique, 2 foreign keys, 1 primary key

Kind	Name	Definition
Foreign key		`(vehicle_id) REFERENCES assets_vehicle(id)`
Foreign key		`(project_id) REFERENCES project(id)`
Primary key		`(id)`
Unique	`unique_cleaning_external_id_per_project`	`(external_system_id, project_id)`

Overview

File formats

Tables

`servicebook_vehicleconfiguration`

`servicebook_maintenanceplan`

`servicebook_maintenancetask`

`servicebook_maintenancerecord`

`servicebook_vehicleoutofservice`

`servicebook_cleaningrecord`

​servicebook_vehicleconfiguration

​servicebook_maintenanceplan

​servicebook_maintenancetask

​servicebook_maintenancerecord

​servicebook_vehicleoutofservice

​servicebook_cleaningrecord

`servicebook_vehicleconfiguration`

`servicebook_maintenanceplan`

`servicebook_maintenancetask`

`servicebook_maintenancerecord`

`servicebook_vehicleoutofservice`

`servicebook_cleaningrecord`