Warranty tables

Claims, groups, their association handshake, and the shared activity timeline. 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.

`warranty_claim`

Column	Type	Null	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`status`	varchar(30)		Current workflow status
`warranty_claim_title`	varchar(60)		Claim title
`warranty_claim_description`	text		Detailed claim description
`subsystem`	varchar(100)		Affected subsystem (derived from defect product_group or subsystem)
`created_by_id`	uuid		User who created the claim
`defect_id`	uuid		Source defect (1:1 relationship)
`project_id`	uuid		Parent project for multi-tenancy
`component_id`	uuid	✓	Component this claim relates to (from defect)
`vehicle_id`	uuid	✓	Vehicle this claim relates to (from defect)

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

Kind	Name	Definition
Check	`warranty_claim_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		`(created_by_id) REFERENCES users_user(id)`
Foreign key		`(defect_id) REFERENCES defect(id)`
Foreign key		`(project_id) REFERENCES project(id)`
Foreign key		`(vehicle_id) REFERENCES assets_vehicle(id)`
Primary key		`(id)`
Unique	`warranty_claim_defect_id_key`	`(defect_id)`

`warranty_claim_group`

Column	Type	Description
`id`	uuid
`warranty_claim_group_title`	varchar(60)	Display title for the group
`warranty_claim_group_description`	text	Detailed description of the grouped issue
`subsystem`	varchar(255)	Subsystem category
`type`	varchar(20)	Group type classification
`status`	varchar(30)	Current workflow status
`created_at`	timestamptz
`updated_at`	timestamptz
`created_by_id`	uuid	User who created the group
`project_id`	uuid	Parent project for multi-tenancy

Constraints — 2 foreign keys, 1 primary key

Kind	Name	Definition
Foreign key		`(created_by_id) REFERENCES users_user(id)`
Foreign key		`(project_id) REFERENCES project(id)`
Primary key		`(id)`

`warranty_claim_group_warranty_claim`

Column	Type	Null	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`association_status`	varchar(20)		Current approval status
`reviewed_at`	timestamptz	✓	When the association was reviewed
`claim_id`	uuid		The warranty claim being associated
`group_id`	uuid		The warranty claim group
`project_id`	uuid		Parent project for multi-tenancy
`reviewed_by_id`	uuid	✓	Supplier who reviewed the association

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

Kind	Name	Definition
Check	`association_review_fields_consistency`	`(((((association_status)::text = 'PENDING'::text) AND (reviewed_by_id IS NULL) AND (reviewed_at IS NULL)) OR ((NOT ((association_status)::text = 'PENDING'::text)) AND (reviewed_by_id IS NOT NULL) AND (reviewed_at IS NOT NULL))))`
Foreign key		`(claim_id) REFERENCES warranty_claim(id)`
Foreign key		`(group_id) REFERENCES warranty_claim_group(id)`
Foreign key		`(project_id) REFERENCES project(id)`
Foreign key		`(reviewed_by_id) REFERENCES users_user(id)`
Primary key		`(id)`
Unique	`unique_claim_group_association`	`(claim_id, group_id)`
Unique index	`unique_accepted_association_per_claim`	`(claim_id) WHERE ((association_status)::text = 'ACCEPTED'::text)`
Unique index	`unique_pending_association_per_claim`	`(claim_id) WHERE ((association_status)::text = 'PENDING'::text)`

`warranty_claim_status_update`

Column	Type	Null	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`new_status`	varchar(30)		Status after the transition
`message`	text	✓	Optional message (required for rejections)
`project_id`	uuid		Parent project (auto-derived from claim or group)
`user_id`	uuid		User who triggered the transition
`warranty_claim_id`	uuid	✓	Target warranty claim (mutually exclusive with group)
`warranty_claim_group_id`	uuid	✓	Target warranty claim group (mutually exclusive with claim)

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

Kind	Name	Definition
Check	`status_update_exactly_one_target`	`((((warranty_claim_id IS NOT NULL) AND (warranty_claim_group_id IS NULL)) OR ((warranty_claim_id IS NULL) AND (warranty_claim_group_id IS NOT NULL))))`
Foreign key		`(warranty_claim_group_id) REFERENCES warranty_claim_group(id)`
Foreign key		`(warranty_claim_id) REFERENCES warranty_claim(id)`
Foreign key		`(project_id) REFERENCES project(id)`
Foreign key		`(user_id) REFERENCES users_user(id)`
Primary key		`(id)`

`warranty_claim_comment`

Column	Type	Null	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`content`	text		Comment text
`project_id`	uuid		Parent project (auto-derived from claim or group)
`user_id`	uuid		Comment author
`warranty_claim_id`	uuid	✓	Target warranty claim (mutually exclusive with group)
`warranty_claim_group_id`	uuid	✓	Target warranty claim group (mutually exclusive with claim)

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

Kind	Name	Definition
Check	`comment_exactly_one_target`	`((((warranty_claim_id IS NOT NULL) AND (warranty_claim_group_id IS NULL)) OR ((warranty_claim_id IS NULL) AND (warranty_claim_group_id IS NOT NULL))))`
Foreign key		`(warranty_claim_group_id) REFERENCES warranty_claim_group(id)`
Foreign key		`(warranty_claim_id) REFERENCES warranty_claim(id)`
Foreign key		`(project_id) REFERENCES project(id)`
Foreign key		`(user_id) REFERENCES users_user(id)`
Primary key		`(id)`

`warranty_claim_attachment`

Column	Type	Null	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`name`	varchar(255)		Display name / filename
`file_url`	varchar(1024)	✓	Permanent Azure Blob URL; null until finalize confirms upload.
`mime_type`	varchar(100)	✓	MIME type (e.g., application/pdf)
`file_size`	int8	✓	File size in bytes
`project_id`	uuid		Parent project (auto-derived from claim or group)
`user_id`	uuid		User who registered the attachment
`warranty_claim_id`	uuid	✓	Target warranty claim (mutually exclusive with group)
`warranty_claim_group_id`	uuid	✓	Target warranty claim group (mutually exclusive with claim)
`client_id`	uuid		Client-generated UUID for upload retry idempotency.
`uploaded_at`	timestamptz	✓	Set by DocumentService.finalize_upload when the blob is confirmed.

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

Kind	Name	Definition
Check	`attachment_exactly_one_target`	`((((warranty_claim_id IS NOT NULL) AND (warranty_claim_group_id IS NULL)) OR ((warranty_claim_id IS NULL) AND (warranty_claim_group_id IS NOT NULL))))`
Foreign key		`(warranty_claim_group_id) REFERENCES warranty_claim_group(id)`
Foreign key		`(warranty_claim_id) REFERENCES warranty_claim(id)`
Foreign key		`(project_id) REFERENCES project(id)`
Foreign key		`(user_id) REFERENCES users_user(id)`
Primary key		`(id)`
Unique	`warranty_claim_attachment_client_id_key`	`(client_id)`
Unique index	`attachment_unique_name_per_claim`	`(warranty_claim_id, name) WHERE (warranty_claim_id IS NOT NULL)`
Unique index	`attachment_unique_name_per_group`	`(warranty_claim_group_id, name) WHERE (warranty_claim_group_id IS NOT NULL)`

Overview

File formats

Tables

`warranty_claim`

`warranty_claim_group`

`warranty_claim_group_warranty_claim`

`warranty_claim_status_update`

`warranty_claim_comment`

`warranty_claim_attachment`

​warranty_claim

​warranty_claim_group

​warranty_claim_group_warranty_claim

​warranty_claim_status_update

​warranty_claim_comment

​warranty_claim_attachment

`warranty_claim`

`warranty_claim_group`

`warranty_claim_group_warranty_claim`

`warranty_claim_status_update`

`warranty_claim_comment`

`warranty_claim_attachment`