defects

Faults recorded against a vehicle or a component. Delivered daily. Update key: external_system_id

Columns

Column	Type	Required	Notes
`external_system_id`	text(100)	●	Update key
`defect_datetime`	timestamptz	●	When the fault occurred
`defect_title`	text(60)	●	Maximum 60 characters
`defect_description`	text	●
`subsystem`	text(100)	○	Legacy subsystem code. Required unless `product_group_code` is set
`location`	text(100)	○	Location on the vehicle or component
`vehicle_number`	text(100)	○	Must exist in project. Required unless `component_serial_number` is set
`component_serial_number`	text(100)	○	Must exist in project
`action_code`	text(50)	○	Free text, not validated
`action_description`	text(255)	○
`cause_code`	text(50)	○	Free text, not validated
`cause_description`	text(255)	○
`fault_indication_code`	text(50)	○	Free text, not validated
`warranty_flag`	boolean	●	`True` / `False`, never empty
`last_updated`	timestamptz	●	Staleness key
`consequence_code`	text(50)	○
`consequence_description`	text(255)	○
`category`	text(100)	○	Free text, e.g. `Mechanical`
`severity`	text(50)	○	Free text, e.g. `Major`
`delay_minutes`	integer	○	Operational delay caused
`closed_date`	timestamptz	○	Empty means still open
`product_group_code`	text(100)	○	Product group code for the vehicle’s type. Required unless `subsystem` is set
`slot_position`	text(20)	○	Position on the vehicle’s type, resolved together with `product_group_code`. Matched exactly, case-sensitive. A value that matches no slot — including anything longer than 20 characters — imports with a warning rather than rejecting the file, up to the 100 characters the staging table accepts; beyond that the file is rejected

Rules

Every row needs at least one of vehicle_number / component_serial_number.
Every row needs at least one of subsystem / product_group_code.
vehicle_number and component_serial_number must resolve within the project, or the file is rejected.
slot_position only means something together with product_group_code — the pair identifies a slot on the vehicle’s type. The vehicle type comes from the vehicle; never send it.
location is free text and is never parsed. Send the position in slot_position if you want it recorded as a position; writing Bogie 1 in location does not resolve a slot, and neither field replaces the other.

product_group_code and slot_position are the only soft references in the entire import. If either cannot be resolved, the row is still imported with that field empty and a warning is logged rather than the file being rejected. Because it is only a warning, a systematically wrong code will import silently for months.One exception: a row whose product_group_code does not resolve and whose subsystem is empty has no classification left at all, and is rejected with a row number — such a row could not be stored anyway. Note that on a component-only row the code resolves through the mount-derived vehicle, so for this row shape a gap in the configuration history can be what triggers the rejection.

Unlike every other file, defects reports all of its error classes in one message rather than aborting at the first failing stage.

Resolution rules

The import resolves the fields the row names first, then fills those still empty from the configuration record covering defect_datetime — mounted_at at or before it, and either still open or dismounted after it. Derivation reads whatever mount history is already loaded, so vehicle_configurations must be imported before defects.

Explicit values win. slot_position is matched against the slots defined on the resolved product group or any of its ancestors on the vehicle’s type; the nearest node wins. The search never descends — a code above the slot’s node does not resolve. Surrounding whitespace is trimmed before matching. A resolved position is never overridden by the mount history.
A component derives its vehicle and slot. A row with a component_serial_number takes its missing vehicle_number and slot from the covering mount, and through the derived vehicle resolves its product_group_code as well. A mount on a different vehicle than the row resolved leaves the slot empty; the warning names both vehicles.
A slot derives its component. A row with a resolved vehicle and slot but no serial takes the component mounted there at defect_datetime.

Overlapping mounts at defect_datetime derive nothing and warn; a missing covering mount derives nothing and is silent. Re-imports treat all four resolved links — vehicle, component, product group and slot — identically: a resolved value overwrites the stored one, an unresolved value keeps it. A warned row can therefore still carry a value from an earlier import, and an empty column never clears a link — corrections require re-sending a row that resolves.

Example

external_system_id,defect_datetime,defect_title,defect_description,subsystem,location,vehicle_number,component_serial_number,action_code,action_description,cause_code,cause_description,fault_indication_code,warranty_flag,last_updated,consequence_code,consequence_description,category,severity,delay_minutes,closed_date,product_group_code,slot_position
X61-DEF-001,2025-06-02T08:30:00Z,Wheelset flat spot detected,Flat spot on wheelset causing vibration at speed.,EC,Bogie 1,X61001,,INSP,Inspected and measured,WEAR,Normal wear and tear,EC-WEAR-01,True,2026-03-22T10:00:00Z,CSQ-001,Service disruption 15 min,Mechanical,Major,15,2025-07-10T00:00:00Z,EC,1
X61-DEF-002,2025-06-05T14:15:00Z,Traction inverter fault,Inverter reported overcurrent protection trip during acceleration.,FD,Car A,X61001,,REP,Replaced inverter module,ELEC,Electrical fault,FD-ELEC-01,True,2026-03-22T10:00:00Z,CSQ-002,Train stopped at station,Electrical,Critical,45,2025-07-15T00:00:00Z,FD,

The first row pins the fault to bogie position 1. The second leaves slot_position empty — the traction inverter is not a slotted position on this type, and location carries what is known about where it sits.

Why a file gets rejected

Row 5: missing or empty defect_title
Row 8: must have vehicle_number or component_serial_number
Row 9: must have subsystem or product_group_code
Row 12: vehicle_number "X61099" not found in project
Row 14: component_serial_number "0123-9" not found in project
Row N: product_group_code "X" could not be resolved and no subsystem given

N is the row number in the file, and the quoted values are echoed from that row. Warnings, which do not reject the file. There are six, one per situation, and each is emitted only when the field it names actually ends up empty (<ts> is the row’s defect_datetime):

Row N: product_group_code "X" could not be resolved (will be NULL)
Row N: slot_position "P" could not be resolved - slot left empty
Row N: component "S" has overlapping configuration history at <ts> - vehicle and slot left empty
Row N: component "S" has overlapping configuration history at <ts> - slot left empty
Row N: component "S" was mounted on vehicle "A" at <ts>, not "B" - slot left empty
Row N: slot has overlapping configuration history at <ts> - component left empty

Overview

File formats

Tables

Columns

Rules

Resolution rules

Example

Why a file gets rejected

​Columns

​Rules

​Resolution rules

​Example

​Why a file gets rejected

Columns

Rules

Resolution rules

Example

Why a file gets rejected