defect
| Column | Type | Null | Default | Description |
|---|---|---|---|---|
id | uuid | |||
created_at | timestamptz | |||
updated_at | timestamptz | |||
external_system_id | varchar(100) | Unique identifier from source maintenance system | ||
defect_datetime | timestamptz | When the defect occurred | ||
defect_title | varchar(60) | Short summary of the defect | ||
defect_description | text | Detailed description of the defect | ||
subsystem | varchar(100) | ✓ | Legacy subsystem string (e.g., Brakes, HVAC). Nullable — not all projects use legacy codes. | |
location | varchar(100) | ✓ | Location on vehicle/component. Optional free text — the loose topology aspect alongside the slot FK. | |
action_code | varchar(50) | ✓ | Maintenance action code | |
action_description | varchar(255) | ✓ | Description of action taken | |
cause_code | varchar(50) | ✓ | Root cause code | |
cause_description | varchar(255) | ✓ | Description of root cause | |
fault_indication_code | varchar(50) | ✓ | Fault indication/symptom code | |
warranty_flag | bool | Whether flagged for warranty claim processing | ||
project_id | uuid | Parent project for multi-tenancy | ||
external_updated_at | timestamptz | Last modification timestamp from the master system. NOT NULL. | ||
category | varchar(100) | ✓ | Defect category from source system (e.g., Mechanical, Electrical) | |
closed_date | timestamptz | ✓ | When the defect was closed/resolved. Null means still open. | |
component_id | uuid | ✓ | Component this defect was reported against. Resolved from component_serial_number string by the validate SP. | |
consequence_code | varchar(50) | ✓ | Consequence/impact code from source system | |
consequence_description | varchar(255) | ✓ | Description of consequence/impact | |
delay_minutes | int | ✓ | Operational delay caused by the defect, in minutes | |
product_group_id | int8 | ✓ | UIC product group classification (new standard). Nullable — not all projects have adopted product groups. | |
severity | varchar(50) | ✓ | Severity classification from source system (e.g., Critical, Major) | |
vehicle_id | uuid | ✓ | Vehicle this defect was reported against. Resolved from vehicle_number string by the validate SP. | |
slot_id | uuid | ✓ | Vehicle type slot the defect resolves to (topology aspect). Nullable — resolved from the vehicle configuration history at defect_datetime, and not resolvable for every defect. |
Constraints — 2 checks, 1 unique, 5 foreign keys, 1 primary key
Constraints — 2 checks, 1 unique, 5 foreign keys, 1 primary key
| Kind | Name | Definition |
|---|---|---|
| Check | defect_must_have_subsystem_or_product_group | (((subsystem IS NOT NULL) OR (product_group_id IS NOT NULL))) |
| Check | defect_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 | (product_group_id) REFERENCES assets_productgroup(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 | unique_external_system_id_per_project | (external_system_id, project_id) |