schema.md

Database Specifications

| Back to Main Page | Trade-offs & System Architecture Decisions |

Table: Orders

Field	Type	Description	Index
ID	UUID	PK
user_id	UUID	FK
total_price	decimal
status	varchar
order_no	varchar
created_at	timestamp
updated_at	timestamp

Table: OrderItems

Field	Type	Description	Index
ID	UUID	PK
order_id	UUID	FK
quantity	integer
price	decimal
customization_id	UUID	FK
created_at	timestamp
updated_at	timestamp

Table: Users

Field	Type	Description	Index
ID	UUID	PK
full_name	varchar
email	varchar
role	varchar
created_at	timestamp
updated_at	timestamp

Table: Categories

Field	Type	Description	Index
ID	UUID	PK
name	varchar
description	varchar
created_at	timestamp
updated_at	timestamp

Table: Products

Field	Type	Description	Index
ID	UUID	PK
name	varchar		Search index
description	varchar
base_price	decimal
category_id	UUID	FK
created_at	timestamp
updated_at	timestamp

Table: Parts

Field	Type	Description	Index
ID	UUID	PK
name	varchar		Search Index
created_at	timestamp
updated_at	timestamp

Table: PartOptions

Field	Type	Description	Index
ID	UUID	PK
name	varchar		Search Index
price	decimal
part_id	UUID	FK
in_stock	boolean
created_at	timestamp
updated_at	timestamp

Table: PartOptionCombinations

Field	Type	Description	Index
ID	UUID	PK
part_options	jsonb		General Inverted Index - GIN
combination_price	decimal
created_at	timestamp
updated_at	timestamp

Table: PartOptionProhibitions

Field	Type	Description	Index
ID	UUID	PK
part_options	jsonb		General Inverted Index - GIN
created_at	timestamp
updated_at	timestamp

Table: Customizations

Field	Type	Description	Index
ID	UUID	PK
user_id	UUID	FK
product_id	UUID	FK
total_price	decimal
configurations	jsonb		General Inverted Index - GIN
created_at	timestamp
updated_at	timestamp
Add Composite index FK columns

Table: ProductParts (Join Table)

Field	Type	Description	Index
ID	UUID	PK
product_id	UUID	FK
part_id	UUID	FK
created_at	timestamp
updated_at	timestamp
Add Composite index FK columns

Table: ProductPartOptions (Join Table)

Field	Type	Description	Index
ID	UUID	PK
product_part_id	UUID	FK
part_option_id	UUID	FK
created_at	timestamp
updated_at	timestamp
Add Composite index FK columns

| Back to Main Page | Trade-offs & System Architecture Decisions |