Inspections tables

Templates, reports, findings, OK marks, photos and generated PDF artifacts. 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.

`inspection_template`

Column	Type	Null
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`name`	varchar(255)
`description`	text	✓
`is_active`	bool
`created_by_user_id`	uuid
`project_id`	uuid
`vehicle_type_id`	uuid

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

Kind	Name	Definition
Foreign key		`(created_by_user_id) REFERENCES users_user(id)`
Foreign key		`(project_id) REFERENCES project(id)`
Foreign key		`(vehicle_type_id) REFERENCES assets_vehicletype(id)`
Primary key		`(id)`
Unique	`inspection_template_project_id_vehicle_type__0e8845c2_uniq`	`(project_id, vehicle_type_id, name)`

`inspection_template_node`

Column	Type	Null	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`sort_order`	int
`locations`	jsonb	✓	Predefined location options, e.g. [“Left 1”, “Right 1”]
`product_group_id`	int8
`template_id`	uuid

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

Kind	Name	Definition
Check	`inspection_template_node_sort_order_check`	`((sort_order >= 0))`
Foreign key		`(product_group_id) REFERENCES assets_productgroup(id)`
Foreign key		`(template_id) REFERENCES inspection_template(id)`
Primary key		`(id)`
Unique	`inspection_template_node_template_id_product_grou_2a8102b1_uniq`	`(template_id, product_group_id)`

`inspection_template_sublist`

Column	Type	Null	Default	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`name`	varchar(255)
`sort_order`	int
`template_id`	uuid

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

Kind	Name	Definition
Check	`inspection_template_sublist_sort_order_check`	`((sort_order >= 0))`
Foreign key		`(template_id) REFERENCES inspection_template(id)`
Primary key		`(id)`
Unique	`inspection_template_sublist_template_id_name_ea1d01c5_uniq`	`(template_id, name)`

`inspection_checklist_item`

Column	Type	Null	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`location`	varchar(255)	✓	Admin-defined location label leaf (not a finding’s free text).
`sort_order`	int
`node_id`	uuid
`slot_id`	uuid	✓
`sublist_id`	uuid	✓

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

Kind	Name	Definition
Check	`checklist_item_one_dimension`	`((NOT ((slot_id IS NOT NULL) AND (location IS NOT NULL))))`
Check	`inspection_checklist_item_sort_order_check`	`((sort_order >= 0))`
Foreign key		`(node_id) REFERENCES inspection_template_node(id)`
Foreign key		`(slot_id) REFERENCES assets_vehicletypeslot(id)`
Foreign key		`(sublist_id) REFERENCES inspection_template_sublist(id)`
Primary key		`(id)`
Unique	`uniq_checklist_leaf`	`NULLS NOT DISTINCT (node_id, slot_id, location)`

`inspection_report`

Column	Type	Null
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`status`	varchar(20)
`notes`	text	✓
`submitted_at`	timestamptz	✓
`created_by_user_id`	uuid
`project_id`	uuid
`vehicle_id`	uuid
`template_id`	uuid

Constraints — 4 foreign keys, 1 primary key

Kind	Name	Definition
Foreign key		`(created_by_user_id) REFERENCES users_user(id)`
Foreign key		`(project_id) REFERENCES project(id)`
Foreign key		`(template_id) REFERENCES inspection_template(id)`
Foreign key		`(vehicle_id) REFERENCES assets_vehicle(id)`
Primary key		`(id)`

`inspection_report_assignment`

Column	Type	Null	Default	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`inspector_id`	uuid
`report_id`	uuid

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

Kind	Name	Definition
Foreign key		`(inspector_id) REFERENCES users_user(id)`
Foreign key		`(report_id) REFERENCES inspection_report(id)`
Primary key		`(id)`
Unique	`inspection_report_assign_report_id_inspector_id_2dfe3e86_uniq`	`(report_id, inspector_id)`

`inspection_report_finding`

Column	Type	Null	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`client_id`	uuid		Client-generated UUID for offline idempotency.
`severity`	varchar(20)
`description`	text
`additional_placement`	varchar(255)	✓
`component_id`	uuid	✓
`inspector_id`	uuid
`node_id`	uuid
`report_id`	uuid
`component_serial`	varchar(255)	✓	Point-in-time serial snapshot of the component (if any).
`checklist_item_id`	uuid	✓	The specific leaf this finding was logged against.

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

Kind	Name	Definition
Foreign key		`(checklist_item_id) REFERENCES inspection_checklist_item(id)`
Foreign key		`(component_id) REFERENCES assets_component(id)`
Foreign key		`(inspector_id) REFERENCES users_user(id)`
Foreign key		`(node_id) REFERENCES inspection_template_node(id)`
Foreign key		`(report_id) REFERENCES inspection_report(id)`
Primary key		`(id)`
Unique	`inspection_report_finding_client_id_key`	`(client_id)`

`inspection_report_item_status`

Column	Type	Null	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`client_id`	uuid		Client-generated UUID for offline idempotency.
`component_serial`	varchar(255)	✓	Point-in-time serial snapshot — authoritative for history.
`checklist_item_id`	uuid
`component_id`	uuid	✓	Component mounted in the slot at inspection time (slot items).
`inspector_id`	uuid
`report_id`	uuid

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

Kind	Name	Definition
Foreign key		`(checklist_item_id) REFERENCES inspection_checklist_item(id)`
Foreign key		`(component_id) REFERENCES assets_component(id)`
Foreign key		`(inspector_id) REFERENCES users_user(id)`
Foreign key		`(report_id) REFERENCES inspection_report(id)`
Primary key		`(id)`
Unique	`inspection_report_item_s_report_id_checklist_item_ffdb39e9_uniq`	`(report_id, checklist_item_id)`
Unique	`inspection_report_item_status_client_id_key`	`(client_id)`

`inspection_finding_photo`

Column	Type	Null	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`client_id`	uuid		Client-generated UUID for offline idempotency.
`blob_url`	varchar(1024)	✓
`filename`	varchar(255)
`mime_type`	varchar(100)
`file_size`	int8
`captured_at`	timestamptz
`uploaded_at`	timestamptz	✓
`finding_id`	uuid

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

Kind	Name	Definition
Foreign key		`(finding_id) REFERENCES inspection_report_finding(id)`
Primary key		`(id)`
Unique	`inspection_finding_photo_client_id_key`	`(client_id)`

`inspection_report_artifact`

Column	Type	Null
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`kind`	varchar(20)
`status`	varchar(20)
`locale`	varchar(10)
`ai_context_notes`	text	✓
`verbatim_notes`	text	✓
`summary_text`	text	✓
`outliers`	jsonb	✓
`stats`	jsonb	✓
`blob_url`	varchar(1024)	✓
`page_count`	int	✓
`byte_size`	int8	✓
`failure_reason`	varchar(255)	✓
`started_at`	timestamptz	✓
`completed_at`	timestamptz	✓
`generated_by_id`	uuid
`project_id`	uuid
`report_id`	uuid	✓
`template_id`	uuid	✓

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

Kind	Name	Definition
Check	`artifact_kind_target_consistency`	`(((((kind)::text = 'PER_INSPECTION'::text) AND (report_id IS NOT NULL) AND (template_id IS NULL)) OR (((kind)::text = 'AGGREGATE'::text) AND (report_id IS NULL) AND (template_id IS NOT NULL))))`
Foreign key		`(generated_by_id) REFERENCES users_user(id)`
Foreign key		`(report_id) REFERENCES inspection_report(id)`
Foreign key		`(template_id) REFERENCES inspection_template(id)`
Foreign key		`(project_id) REFERENCES project(id)`
Primary key		`(id)`
Unique index	`one_ready_per_inspection_per_report`	`(report_id) WHERE (((kind)::text = 'PER_INSPECTION'::text) AND ((status)::text = 'READY'::text))`

`inspection_artifact_generation_log`

Column	Type	Null
`id`	int8
`kind`	varchar(20)
`target_id`	uuid
`outcome`	varchar(20)
`created_at`	timestamptz
`project_id`	uuid
`user_id`	uuid
`artifact_id`	uuid	✓

Constraints — 3 foreign keys, 1 primary key

Kind	Name	Definition
Foreign key		`(artifact_id) REFERENCES inspection_report_artifact(id)`
Foreign key		`(project_id) REFERENCES project(id)`
Foreign key		`(user_id) REFERENCES users_user(id)`
Primary key		`(id)`

Overview

File formats

Tables

`inspection_template`

`inspection_template_node`

`inspection_template_sublist`

`inspection_checklist_item`

`inspection_report`

`inspection_report_assignment`

`inspection_report_finding`

`inspection_report_item_status`

`inspection_finding_photo`

`inspection_report_artifact`

`inspection_artifact_generation_log`

​inspection_template

​inspection_template_node

​inspection_template_sublist

​inspection_checklist_item

​inspection_report

​inspection_report_assignment

​inspection_report_finding

​inspection_report_item_status

​inspection_finding_photo

​inspection_report_artifact

​inspection_artifact_generation_log

`inspection_template`

`inspection_template_node`

`inspection_template_sublist`

`inspection_checklist_item`

`inspection_report`

`inspection_report_assignment`

`inspection_report_finding`

`inspection_report_item_status`

`inspection_finding_photo`

`inspection_report_artifact`

`inspection_artifact_generation_log`