Reporting tables

Code lists, reporter settings, defect reports with their conversations, and delivery to the maintainer system. 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.

`reporting_code_list`

Column	Type	Null
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`list_type`	varchar(20)
`code`	varchar(50)
`label`	varchar(255)
`description`	text
`sort_order`	int2
`is_active`	bool
`project_id`	uuid
`vehicle_type_id`	uuid	✓

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

Kind	Name	Definition
Check	`reporting_code_list_sort_order_check`	`((sort_order >= 0))`
Check	`reporting_code_vehicle_type_only_location`	`(((vehicle_type_id IS NULL) OR ((list_type)::text = 'LOCATION'::text)))`
Foreign key		`(project_id) REFERENCES project(id)`
Foreign key		`(vehicle_type_id) REFERENCES assets_vehicletype(id)`
Primary key		`(id)`
Unique	`unique_reporting_code_per_list`	`NULLS NOT DISTINCT (project_id, vehicle_type_id, list_type, code)`

`reporting_settings`

Column	Type	Null
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`assistant_instructions`	text
`project_id`	uuid
`updated_by_id`	uuid	✓

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

Kind	Name	Definition
Foreign key		`(project_id) REFERENCES project(id)`
Foreign key		`(updated_by_id) REFERENCES users_user(id)`
Primary key		`(id)`
Unique	`reporting_settings_project_id_key`	`(project_id)`

`reporting_defect_report`

Column	Type	Null
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`status`	varchar(20)
`version`	int
`language`	varchar(5)
`component_serial`	varchar(100)	✓
`defect_datetime`	timestamptz	✓
`delay_minutes`	int	✓
`title`	varchar(60)
`description`	text
`external_reference`	varchar(100)	✓
`submitted_at`	timestamptz	✓
`synced_at`	timestamptz	✓
`archived_at`	timestamptz	✓
`component_id`	uuid	✓
`created_by_id`	uuid
`defect_id`	uuid	✓
`product_group_id`	int8	✓
`project_id`	uuid
`slot_id`	uuid	✓
`vehicle_id`	uuid
`vehicle_type_id`	uuid
`category_id`	uuid	✓
`consequence_id`	uuid	✓
`fault_indication_id`	uuid	✓
`location_id`	uuid	✓
`severity_id`	uuid	✓

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

Kind	Name	Definition
Check	`reporting_defect_report_delay_minutes_check`	`((delay_minutes >= 0))`
Check	`reporting_defect_report_version_check`	`((version >= 0))`
Foreign key		`(category_id) REFERENCES reporting_code_list(id)`
Foreign key		`(component_id) REFERENCES assets_component(id)`
Foreign key		`(consequence_id) REFERENCES reporting_code_list(id)`
Foreign key		`(fault_indication_id) REFERENCES reporting_code_list(id)`
Foreign key		`(location_id) REFERENCES reporting_code_list(id)`
Foreign key		`(product_group_id) REFERENCES assets_productgroup(id)`
Foreign key		`(severity_id) REFERENCES reporting_code_list(id)`
Foreign key		`(slot_id) REFERENCES assets_vehicletypeslot(id)`
Foreign key		`(vehicle_id) REFERENCES assets_vehicle(id)`
Foreign key		`(vehicle_type_id) REFERENCES assets_vehicletype(id)`
Foreign key		`(created_by_id) REFERENCES users_user(id)`
Foreign key		`(defect_id) REFERENCES defect(id)`
Foreign key		`(project_id) REFERENCES project(id)`
Primary key		`(id)`
Unique	`reporting_defect_report_defect_id_key`	`(defect_id)`

`reporting_report_session`

Column	Type	Null	Default	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`kind`	varchar(20)
`status`	varchar(20)
`turn_count`	int2
`reasoning_effort`	varchar(10)
`input_tokens`	int
`output_tokens`	int
`cached_input_tokens`	int
`report_id`	uuid

Constraints — 4 checks, 1 unique index, 1 foreign key, 1 primary key

Kind	Name	Definition
Check	`reporting_report_session_cached_input_tokens_check`	`((cached_input_tokens >= 0))`
Check	`reporting_report_session_input_tokens_check`	`((input_tokens >= 0))`
Check	`reporting_report_session_output_tokens_check`	`((output_tokens >= 0))`
Check	`reporting_report_session_turn_count_check`	`((turn_count >= 0))`
Foreign key		`(report_id) REFERENCES reporting_defect_report(id)`
Primary key		`(id)`
Unique index	`one_open_session_per_report_kind`	`(report_id, kind) WHERE ((status)::text = ANY ((ARRAY['ACTIVE'::character varying, 'IN_TURN'::character varying, 'REVIEW'::character varying])::text[]))`

`reporting_report_message`

Column	Type	Null
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`sequence`	int
`role`	varchar(20)
`content`	text
`input_mode`	varchar(10)
`raw_transcript`	text	✓
`client_message_id`	uuid	✓
`choices`	jsonb	✓
`tool_calls`	jsonb	✓
`field_deltas`	jsonb	✓
`usage`	jsonb	✓
`session_id`	uuid

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

Kind	Name	Definition
Check	`reporting_report_message_sequence_check`	`((sequence >= 0))`
Foreign key		`(session_id) REFERENCES reporting_report_session(id)`
Primary key		`(id)`
Unique	`unique_client_message_id_per_session`	`(session_id, client_message_id)`
Unique	`unique_message_sequence_per_session`	`(session_id, sequence)`

`integrations_maintainer_integration`

Column	Type	Null
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`adapter_key`	varchar(50)
`enabled`	bool
`config`	jsonb
`credential_source`	varchar(10)
`project_id`	uuid
`updated_by_id`	uuid	✓

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

Kind	Name	Definition
Foreign key		`(project_id) REFERENCES project(id)`
Foreign key		`(updated_by_id) REFERENCES users_user(id)`
Primary key		`(id)`
Unique	`integrations_maintainer_integration_project_id_key`	`(project_id)`

`integrations_outbound_sync`

Column	Type	Null
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`adapter_key`	varchar(50)
`status`	varchar(20)
`attempts`	int2
`next_attempt_at`	timestamptz
`max_attempts`	int2
`correlation_id`	uuid
`payload`	jsonb
`external_id`	varchar(100)	✓
`response`	jsonb	✓
`last_error`	text	✓
`sent_at`	timestamptz	✓
`defect_id`	uuid	✓
`project_id`	uuid
`report_id`	uuid

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

Kind	Name	Definition
Check	`integrations_outbound_sync_attempts_check`	`((attempts >= 0))`
Check	`integrations_outbound_sync_max_attempts_check`	`((max_attempts >= 0))`
Foreign key		`(report_id) REFERENCES reporting_defect_report(id)`
Foreign key		`(defect_id) REFERENCES defect(id)`
Foreign key		`(project_id) REFERENCES project(id)`
Primary key		`(id)`

Overview

File formats

Tables

`reporting_code_list`

`reporting_settings`

`reporting_defect_report`

`reporting_report_session`

`reporting_report_message`

`integrations_maintainer_integration`

`integrations_outbound_sync`

​reporting_code_list

​reporting_settings

​reporting_defect_report

​reporting_report_session

​reporting_report_message

​integrations_maintainer_integration

​integrations_outbound_sync

`reporting_code_list`

`reporting_settings`

`reporting_defect_report`

`reporting_report_session`

`reporting_report_message`

`integrations_maintainer_integration`

`integrations_outbound_sync`