Warranty tables

Claims, groups, their association handshake, the shared activity timeline, and dismissed recurrence pairs. 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
`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)
`product_group_id`	int8		Product group from the defect at claim creation
`reference`	varchar(20)		Human-readable identifier WC-YYYY-NNNN, generated on creation
`type`	varchar(20)		Claim type classification
`origin_defect_id`	uuid	✓	Earlier defect this REPARATION claim is a rework of

Constraints — 2 checks, 2 unique, 7 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)))`
Check	`warranty_claim_origin_matches_type`	`((((origin_defect_id IS NOT NULL) AND ((type)::text = 'REPARATION'::text)) OR ((NOT ((type)::text = 'REPARATION'::text)) AND (origin_defect_id IS 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		`(origin_defect_id) REFERENCES defect(id)`
Foreign key		`(product_group_id) REFERENCES assets_productgroup(id)`
Foreign key		`(project_id) REFERENCES project(id)`
Foreign key		`(vehicle_id) REFERENCES assets_vehicle(id)`
Primary key		`(id)`
Unique	`unique_warranty_claim_reference_per_project`	`(project_id, reference)`
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
`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
`product_group_id`	int8	Product group this group is classified on
`reference`	varchar(20)	Human-readable identifier WCG-YYYY-NNNN, generated on creation

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

Kind	Name	Definition
Foreign key		`(created_by_id) REFERENCES users_user(id)`
Foreign key		`(product_group_id) REFERENCES assets_productgroup(id)`
Foreign key		`(project_id) REFERENCES project(id)`
Primary key		`(id)`
Unique	`unique_warranty_claim_group_reference_per_project`	`(project_id, reference)`

`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)`

`warranty_reference_counter`

Column	Type	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`prefix`	varchar(4)	Reference prefix (WC or WCG)
`year`	int2	Calendar year the sequence belongs to
`last_number`	int	Highest number handed out so far
`project_id`	uuid	Parent project for multi-tenancy

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

Kind	Name	Definition
Check	`warranty_reference_counter_last_number_check`	`((last_number >= 0))`
Check	`warranty_reference_counter_year_check`	`((year >= 0))`
Foreign key		`(project_id) REFERENCES project(id)`
Primary key		`(id)`
Unique	`unique_warranty_reference_counter`	`(project_id, prefix, year)`

`similarity_recurrence_dismissal`

Column	Type	Description
`id`	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`defect_high_id`	uuid	Higher-ordered defect id of the dismissed pair
`defect_low_id`	uuid	Lower-ordered defect id of the dismissed pair
`dismissed_by_id`	uuid	Warranty Manager who dismissed the pair
`project_id`	uuid	Parent project for multi-tenancy

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

Kind	Name	Definition
Check	`recurrence_dismissal_pair_ordered`	`((defect_low_id < defect_high_id))`
Foreign key		`(defect_high_id) REFERENCES defect(id)`
Foreign key		`(defect_low_id) REFERENCES defect(id)`
Foreign key		`(dismissed_by_id) REFERENCES users_user(id)`
Foreign key		`(project_id) REFERENCES project(id)`
Primary key		`(id)`
Unique	`unique_recurrence_dismissal_pair`	`(project_id, defect_low_id, defect_high_id)`

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_reference_counter`

`similarity_recurrence_dismissal`

​warranty_claim

​warranty_claim_group

​warranty_claim_group_warranty_claim

​warranty_claim_status_update

​warranty_claim_comment

​warranty_claim_attachment

​warranty_reference_counter

​similarity_recurrence_dismissal

`warranty_claim`

`warranty_claim_group`

`warranty_claim_group_warranty_claim`

`warranty_claim_status_update`

`warranty_claim_comment`

`warranty_claim_attachment`

`warranty_reference_counter`

`similarity_recurrence_dismissal`