Measurements tables - A7 manager

Measurement types, thresholds and recorded values. 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.

`measurements_measurementtype`

Column	Type	Null	Default	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`code`	varchar(50)			Short identifier, e.g. ‘WHEEL_DIAMETER’
`name`	varchar(255)			Display name, e.g. ‘Wheel diameter’
`unit`	varchar(20)			Display unit, e.g. ‘mm’, ‘h’, ‘kg’
`progression_rule`	varchar(20)			Expected direction of consecutive measurements; enforced at import unless action_code is in reset_action_codes.
`allowed_action_codes`	character varying(50)[]			Closed set of accepted action codes. Empty = no enforcement.
`reset_action_codes`	character varying(50)[]			Action codes that exempt the progression rule and start a new analytics segment. Must be a subset of allowed_action_codes.
`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_measurement_type_code_per_project`	`(project_id, code)`

`measurements_measurementthreshold`

Column	Type	Null	Default	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`nominal_value`	numeric(10,2)	✓		New/healthy reading.
`min_value`	numeric(10,2)	✓		Wear/replacement threshold (and import floor).
`max_value`	numeric(10,2)	✓		Upper sanity bound (and import ceiling).
`component_type_id`	uuid			The component type these bounds apply to.
`measurement_type_id`	uuid			The kind of measurement these bounds apply to.
`reframing_task_id`	uuid	✓		Preventive-reframing task that caps the wheel reframing forecast.

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

Kind	Name	Definition
Foreign key		`(component_type_id) REFERENCES assets_componenttype(id)`
Foreign key		`(measurement_type_id) REFERENCES measurements_measurementtype(id)`
Foreign key		`(reframing_task_id) REFERENCES servicebook_maintenancetask(id)`
Primary key		`(id)`
Unique	`unique_threshold_per_pair`	`(measurement_type_id, component_type_id)`

`measurements_measurement`

Column	Type	Null	Default	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`position`	varchar(50)	✓		Slot position from CSV; cross-checked against active mount.
`measured_at`	timestamptz			When the reading was captured.
`performance_km`	int	✓		Vehicle reading at measurement time.
`operating_hours`	int	✓		Vehicle reading at measurement time.
`value`	numeric(10,2)			The reading itself; unit comes from measurement_type.unit.
`action_code`	varchar(50)	✓		Action code from source system.
`action_description`	varchar(255)	✓
`reason_code`	varchar(50)	✓		Free-text reason code (not validated).
`reason_description`	varchar(255)	✓
`notes`	text
`external_system_id`	varchar(100)			Unique identifier from source measurement system.
`external_updated_at`	timestamptz			Last modification timestamp from the master system.
`component_id`	uuid	✓		Component this reading was captured against (resolved at import).
`project_id`	uuid			Parent project for multi-tenancy
`vehicle_id`	uuid	✓		Vehicle this reading was captured against (resolved at import).
`measurement_type_id`	uuid			The kind of reading this row captures.

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

Kind	Name	Definition
Check	`measurement_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		`(measurement_type_id) REFERENCES measurements_measurementtype(id)`
Foreign key		`(vehicle_id) REFERENCES assets_vehicle(id)`
Foreign key		`(project_id) REFERENCES project(id)`
Primary key		`(id)`
Unique	`unique_measurement_external_id_per_project`	`(external_system_id, project_id)`

⌘I