Database Architecture¶
The application uses PostgreSQL 15 with Django ORM.
Core Models¶
Product Hierarchy¶
erDiagram
Category ||--o{ Category : "parent (MPTT)"
Category ||--o{ Product : contains
Brand ||--o{ Product : manufactures
Product ||--o{ ProductVariant : has
Product ||--o{ ProductColorImage : has
Product }o--o{ Color : "available_colors"
Product }o--o{ Size : "available_sizes"
ProductVariant }o--|| Color : uses
ProductVariant }o--|| Size : uses
ProductColorImage }o--|| Color : usesOrder & Payment Flow¶
erDiagram
User ||--o{ Order : places
User ||--o| ShoppingCart : has
ShoppingCart ||--o{ CartItem : contains
CartItem }o--|| ProductVariant : references
CartItem }o--o| CustomDesign : includes
Order ||--o{ OrderItem : contains
OrderItem }o--|| ProductVariant : references
OrderItem }o--o| CustomDesign : includes
Order ||--o{ Payment : "has payments"
Order |o--o| Invoice : "has invoice"
Payment ||--o{ Refund : "has refunds"
Order ||--o{ PaymentAuditLog : "audit trail"Designs & Users¶
erDiagram
User ||--o| UserProfile : has
User ||--o{ ShippingAddress : "address book"
User ||--o{ WishlistItem : "wishlist"
User ||--o{ CustomDesign : creates
CustomDesign }o--|| Product : "for product"
CustomDesign ||--o{ TeamNameList : "team members"
TeamNameList }o--|| Size : uses
WishlistItem }o--|| Product : referencesPricing & Discounts¶
erDiagram
DiscountCode }o--o{ Product : "applies to"
DiscountCode }o--o{ Category : "applies to"
VolumeDiscount ||--o{ VolumeDiscountTier : "has tiers"
VolumeDiscount }o--o{ Product : "applies to"
TeamDiscount }o--o{ Product : "applies to"
SpecialOffer }o--o{ Product : "applies to"
SpecialOffer }o--o{ Category : "applies to"Key Models¶
Product¶
class Product(models.Model):
sku = models.CharField(max_length=50, unique=True)
name = models.CharField(max_length=300)
slug = models.SlugField(unique=True)
category = models.ForeignKey(Category, on_delete=models.PROTECT)
brand = models.ForeignKey(Brand, on_delete=models.SET_NULL, null=True)
base_price = models.DecimalField(max_digits=10, decimal_places=2)
# Print area configuration
max_print_width = models.IntegerField()
max_print_height = models.IntegerField()
print_positions = models.JSONField(default=dict)
# Available colors/sizes for variant generation
available_colors = models.ManyToManyField(Color, blank=True)
available_sizes = models.ManyToManyField(Size, blank=True)
is_active = models.BooleanField(default=True)
is_featured = models.BooleanField(default=False)
custom_text_price = models.DecimalField(default=0)
custom_number_price = models.DecimalField(default=0)
ProductVariant¶
class ProductVariant(models.Model):
product = models.ForeignKey(Product, related_name='variants')
color = models.ForeignKey(Color, on_delete=models.PROTECT)
size = models.ForeignKey(Size, on_delete=models.PROTECT)
sku = models.CharField(max_length=100, unique=True)
stock_quantity = models.IntegerField(default=0)
low_stock_threshold = models.IntegerField(default=10)
price_adjustment = models.DecimalField(default=0)
# Multi-view images (front/back/left/right)
image_front = models.ImageField(null=True)
image_back = models.ImageField(null=True)
image_left = models.ImageField(null=True)
image_right = models.ImageField(null=True)
is_active = models.BooleanField(default=True)
class Meta:
unique_together = ['product', 'color', 'size']
ProductColorImage¶
Per product-color combination: 4 view images, 4 print area configs (16 fields), and 12 responsive WebP variants (4 views x 3 sizes: thumbnail/card/detail).
class ProductColorImage(models.Model):
product = models.ForeignKey(Product, related_name='color_images')
color = models.ForeignKey(Color, on_delete=models.CASCADE)
# 4 view images
image_front = models.ImageField(...)
image_back = models.ImageField(...)
image_left = models.ImageField(...)
image_right = models.ImageField(...)
# Print area config per view (x, y, width, height for each)
print_area_front_x, print_area_front_y, ...
# WebP responsive variants (auto-generated by Celery)
image_front_thumbnail_webp, image_front_card_webp, image_front_detail_webp, ...
class Meta:
unique_together = ['product', 'color']
CustomDesign¶
class CustomDesign(models.Model):
user = models.ForeignKey(User, null=True, on_delete=models.SET_NULL)
session_key = models.CharField(max_length=100, blank=True)
name = models.CharField(max_length=200, blank=True)
product = models.ForeignKey(Product, on_delete=models.CASCADE)
variant = models.ForeignKey(ProductVariant, null=True)
status = models.CharField(choices=STATUS_CHOICES, default='draft')
# Fabric.js JSON data per view (4 views)
design_json_front = models.JSONField(default=dict)
design_json_back = models.JSONField(default=dict)
design_json_left = models.JSONField(default=dict)
design_json_right = models.JSONField(default=dict)
# Preview images per view
preview_image_front = models.ImageField(null=True)
preview_image_back = models.ImageField(null=True)
preview_image_left = models.ImageField(null=True)
preview_image_right = models.ImageField(null=True)
print_file = models.FileField(null=True)
is_saved = models.BooleanField(default=False)
Payment¶
class Payment(models.Model):
order = models.ForeignKey(Order, related_name='payments')
mollie_payment_id = models.CharField(max_length=100, unique=True)
status = models.CharField(max_length=20, default='open')
# Statuses: open, pending, paid, failed, expired, canceled
amount = models.DecimalField(max_digits=10, decimal_places=2)
method = models.CharField(max_length=50, default='ideal')
issuer = models.CharField(max_length=100, blank=True)
checkout_url = models.URLField(max_length=500, blank=True)
metadata = models.JSONField(default=dict)
Invoice¶
class Invoice(models.Model):
order = models.OneToOneField(Order, related_name='invoice')
invoice_number = models.CharField(max_length=20, unique=True)
# Format: INV-YYYY-NNNNN (gap-free sequential)
pdf_file = models.FileField(upload_to='invoices/')
amount = models.DecimalField(max_digits=10, decimal_places=2)
btw_amount = models.DecimalField(max_digits=10, decimal_places=2)
generated_at = models.DateTimeField(auto_now_add=True)
DiscountCode¶
class DiscountCode(models.Model):
code = models.CharField(max_length=50, unique=True)
discount_type = models.CharField(choices=DISCOUNT_TYPES)
# Types: percentage, fixed_amount, free_shipping
value = models.DecimalField(max_digits=10, decimal_places=2)
minimum_order_amount = models.DecimalField(default=0)
maximum_discount_amount = models.DecimalField(null=True)
usage_limit = models.IntegerField(null=True)
usage_count = models.IntegerField(default=0)
valid_from = models.DateTimeField()
valid_until = models.DateTimeField(null=True)
applies_to_products = models.ManyToManyField(Product, blank=True)
applies_to_categories = models.ManyToManyField(Category, blank=True)
Database Indexes¶
Key indexes for performance:
# Products
models.Index(fields=['is_active', 'is_featured'])
models.Index(fields=['category', 'is_active'])
models.Index(fields=['brand', 'is_active'])
# ProductVariant
models.Index(fields=['product', 'is_active'])
models.Index(fields=['product', 'color', 'is_active'])
models.Index(fields=['is_active', 'stock_quantity'])
# Category (MPTT)
models.Index(fields=['is_active', 'sort_order'])
models.Index(fields=['parent', 'is_active'])
# Orders
models.Index(fields=['created_at', 'status'])
# Payments
mollie_payment_id: unique + db_index
models.Index(fields=['order', '-timestamp']) # PaymentAuditLog
models.Index(fields=['action', '-timestamp']) # PaymentAuditLog
Migrations¶
Always review migrations before applying: