Assets tables

Vehicle types, product groups, component types, slots, vehicles, components and odometer readings. 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.

`assets_vehicletype`

Column	Type	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`code`	varchar(50)	Short identifier, e.g. ‘X60A’
`name`	varchar(255)	Descriptive name, e.g. ‘X60 4-car variant’
`project_id`	uuid	Parent project for multi-tenancy

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

Kind	Name	Definition
Foreign key		`(project_id) REFERENCES project(id)`
Primary key		`(id)`
Unique	`unique_vehicle_type_code_per_project`	`(project_id, code)`

`assets_productgroup`

Column	Type	Description
`id`	int8
`path`	varchar(255)
`depth`	int
`numchild`	int
`code`	varchar(20)	Human-readable identifier, e.g. ‘BC’ for Bogies
`name`	varchar(255)	Descriptive name, e.g. ‘Bogies’
`is_active`	bool	Soft-delete for standard nodes; hidden from future selections
`is_standard`	bool	True for auto-populated EN 15380-2 L1/L2 nodes
`vehicle_type_id`	uuid	Each vehicle type owns its own product group tree

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

Kind	Name	Definition
Check	`assets_productgroup_depth_check`	`((depth >= 0))`
Check	`assets_productgroup_numchild_check`	`((numchild >= 0))`
Foreign key		`(vehicle_type_id) REFERENCES assets_vehicletype(id)`
Primary key		`(id)`
Unique	`assets_productgroup_path_key`	`(path)`
Unique	`unique_product_group_code_per_vehicle_type`	`(vehicle_type_id, code)`

`assets_componenttype`

Column	Type	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`code`	varchar(50)	Short identifier, e.g. ‘BOGIE’
`name`	varchar(255)	Descriptive name, e.g. ‘Bogie Assembly’
`project_id`	uuid	Parent project for multi-tenancy

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

Kind	Name	Definition
Foreign key		`(project_id) REFERENCES project(id)`
Primary key		`(id)`
Unique	`unique_component_type_code_per_project`	`(project_id, code)`

`assets_vehicletypeslot`

Column	Type	Null	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`position`	varchar(20)		Position identifier, e.g. ‘1’, ‘L1’, ‘R2’
`position_label`	varchar(255)		Human-readable label, e.g. ‘Bogie 1 (Leading)‘
`is_required`	bool		Whether this slot must be filled
`parent_slot_id`	uuid	✓	Parent slot on the same vehicle type (physical nesting; may cross product group branches)
`product_group_id`	int8		Product group node where this slot is mounted
`vehicle_type_id`	uuid		Vehicle type this slot belongs to

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

Kind	Name	Definition
Foreign key		`(parent_slot_id) REFERENCES assets_vehicletypeslot(id)`
Foreign key		`(product_group_id) REFERENCES assets_productgroup(id)`
Foreign key		`(vehicle_type_id) REFERENCES assets_vehicletype(id)`
Primary key		`(id)`
Unique	`unique_position_per_product_group`	`(vehicle_type_id, product_group_id, "position")`

`assets_vehicletypeslot_allowed_component_types`

Column	Type	Null	Default	Description
`id`	int8
`vehicletypeslot_id`	uuid
`componenttype_id`	uuid

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

Kind	Name	Definition
Foreign key		`(componenttype_id) REFERENCES assets_componenttype(id)`
Foreign key		`(vehicletypeslot_id) REFERENCES assets_vehicletypeslot(id)`
Primary key		`(id)`
Unique	`assets_vehicletypeslot_a_vehicletypeslot_id_compo_c30301e7_uniq`	`(vehicletypeslot_id, componenttype_id)`

`assets_vehicle`

Column	Type	Null	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`vehicle_number`	varchar(100)		Unique identifier within project, e.g. ‘X60001’
`external_system_id`	varchar(100)	✓	Integration key for external systems
`current_mileage_km`	int		Denormalized from latest reading
`current_operating_hours`	int		Denormalized from latest reading
`last_reading_update`	timestamptz	✓	When mileage/hours were last updated
`project_id`	uuid		Parent project for multi-tenancy
`vehicle_type_id`	uuid		Vehicle variant (immutable after creation)

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

Kind	Name	Definition
Foreign key		`(project_id) REFERENCES project(id)`
Foreign key		`(vehicle_type_id) REFERENCES assets_vehicletype(id)`
Primary key		`(id)`
Unique	`unique_vehicle_number_per_project`	`(project_id, vehicle_number)`
Unique index	`unique_vehicle_external_id_per_project`	`(project_id, external_system_id) WHERE (external_system_id IS NOT NULL)`

`assets_component`

Column	Type	Null	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`serial_number`	varchar(100)		Unique identifier within project, e.g. ‘BG-001’
`external_system_id`	varchar(100)	✓	Integration key for external systems
`status`	varchar(20)		Current lifecycle status
`current_location`	varchar(100)	✓	Physical location, e.g. ‘Depot Hagalund’
`total_mileage_km`	int		Lifetime total across all mounts
`total_operating_hours`	int		Lifetime total across all mounts
`last_reading_update`	timestamptz	✓	When mileage/hours were last updated
`component_type_id`	uuid		Component category (e.g. Bogie, Wheelset)
`project_id`	uuid		Parent project for multi-tenancy

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

Kind	Name	Definition
Foreign key		`(component_type_id) REFERENCES assets_componenttype(id)`
Foreign key		`(project_id) REFERENCES project(id)`
Primary key		`(id)`
Unique	`unique_serial_number_per_project`	`(project_id, serial_number)`
Unique index	`unique_component_external_id_per_project`	`(project_id, external_system_id) WHERE (external_system_id IS NOT NULL)`

`assets_vehiclereading`

Column	Type	Null	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`reading_date`	date		Date of the odometer snapshot
`mileage_km`	int		Absolute odometer reading in km
`operating_hours`	int	✓	Operating hours (optional — some systems don’t report hours)
`external_system_id`	varchar(100)	✓	Source system identifier for traceability
`external_updated_at`	timestamptz	✓	Source system timestamp for staleness checks
`project_id`	uuid		Project scope for multi-tenancy
`vehicle_id`	uuid		The vehicle this reading belongs to

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

Kind	Name	Definition
Foreign key		`(project_id) REFERENCES project(id)`
Foreign key		`(vehicle_id) REFERENCES assets_vehicle(id)`
Primary key		`(id)`
Unique	`unique_reading_per_vehicle_per_day`	`(vehicle_id, reading_date)`
Unique index	`unique_reading_external_id_per_vehicle`	`(external_system_id, vehicle_id) WHERE (external_system_id IS NOT NULL)`

Overview

File formats

Tables

`assets_vehicletype`

`assets_productgroup`

`assets_componenttype`

`assets_vehicletypeslot`

`assets_vehicletypeslot_allowed_component_types`

`assets_vehicle`

`assets_component`

`assets_vehiclereading`

​assets_vehicletype

​assets_productgroup

​assets_componenttype

​assets_vehicletypeslot

​assets_vehicletypeslot_allowed_component_types

​assets_vehicle

​assets_component

​assets_vehiclereading

`assets_vehicletype`

`assets_productgroup`

`assets_componenttype`

`assets_vehicletypeslot`

`assets_vehicletypeslot_allowed_component_types`

`assets_vehicle`

`assets_component`

`assets_vehiclereading`