Database Architecture¶
The application uses PostgreSQL 15 with the Django ORM. All models live in
backend/apps/<app>/models.py; the lists below match the code as of 2026-11-07 (v1.60).
Models per App¶
| App | Models |
|---|---|
| products | Category (MPTT), Brand, ColorCategory, Color, SizeType, Size, Product, TwoColorCombination, ProductColorImage, ProductDetailImage, ProductPrintZone, ProductViewScale, ProductVariant |
| designs | CustomDesign, DesignTemplate, TextColor, Font |
| orders | ShoppingCart, OrderGroup (team order, migration orders/0024), CartItem, Order, OrderItem, OrderItemPrintZone, OrderMessage, ManualProductionItem |
| payments | Payment, Refund, PaymentAuditLog |
| invoices | Invoice, InvoiceCounter |
| informer | InformerBooking (one per order sent to the Informer bookkeeping) |
| quotes | Quote, QuoteItem, QuoteCounter |
| pricing | DiscountCode, VolumeDiscount, VolumeDiscountTier, SpecialOffer, PricingRule, DecorationMethod, DecorationAreaTier |
| users | UserProfile, ShippingAddress, WishlistItem, NewsletterSubscription (auth uses Django's User) |
| core | SiteSettings (singleton), Page, AdminAuditLog, BackupRecord |
| personalization | PersonalizationColor, PersonalizationTemplate (JSON small_texts, migration personalization/0002) |
| clipart | ClipartCategory, ClipartItem |
| analytics | WebsiteWeekSnapshot |
| agent_api | AgentApiKey (hashed key + scopes), AgentApiCall (audit log per call), AgentSuggestion (draft of the quote assistant) |
Third-party tables: django-allauth (accounts, MFA authenticators), django-axes
(access attempts/logs), django-mptt fields on Category.
Core Relationships¶
Product Hierarchy¶
erDiagram
Category ||--o{ Category : "parent (MPTT)"
Category ||--o{ Product : "category"
Product }o--o{ Category : "additional_categories"
Brand ||--o{ Product : manufactures
Brand ||--o{ ColorCategory : "colour palette"
ColorCategory }o--o{ Color : groups
Color |o--o{ Color : "secondary_color"
SizeType ||--o{ Size : groups
Product ||--o{ ProductVariant : "colour x size"
Product ||--o{ ProductColorImage : "images per colour"
Product ||--o{ ProductDetailImage : "gallery"
Product ||--o{ ProductPrintZone : "print zones per view"
Product ||--o{ ProductViewScale : "px per cm per view"
Product ||--o{ TwoColorCombination : "two-colour images"
Product }o--o{ Color : "available_colors"
Product }o--o{ Size : "available_sizes"
ProductVariant }o--|| Color : uses
ProductVariant }o--|| Size : uses
ProductPrintZone }o--o| DecorationMethod : "priced by"
Product |o--o| PersonalizationTemplate : "personalisation"
Cart, Order & Payment¶
erDiagram
User ||--o| ShoppingCart : has
ShoppingCart ||--o{ CartItem : contains
ShoppingCart |o--o{ OrderGroup : "groups (team orders)"
OrderGroup }o--|| Product : "product"
OrderGroup }o--|| Color : "color"
OrderGroup }o--o| CustomDesign : "base_design"
OrderGroup |o--o{ CartItem : "group (1 piece per row)"
OrderGroup |o--o{ OrderItem : "group"
OrderGroup |o--o{ QuoteItem : "group"
ShoppingCart |o--o| Order : "checkout_order (idempotency)"
CartItem }o--|| ProductVariant : references
CartItem }o--o| CustomDesign : includes
User ||--o{ Order : places
Order }o--o| DiscountCode : "discount_code"
Order ||--o{ OrderItem : contains
OrderItem }o--|| ProductVariant : references
OrderItem }o--o| CustomDesign : includes
OrderItem ||--o{ OrderItemPrintZone : "zone snapshots"
Order ||--o{ OrderMessage : "message thread"
Order ||--o{ Payment : "Mollie payments"
Payment ||--o{ Refund : refunds
Order |o--o| Invoice : invoice
Order ||--o{ PaymentAuditLog : "audit trail"
Order ||--o{ InformerBooking : "bookkeeping"
ManualProductionItem stands alone: an off-webshop job on the production board,
deliberately not linked to Order. An OrderGroup keeps its roster when an order
or quote refers to it: discard_from_cart only detaches it (cart = NULL) instead of
deleting it, because OrderItem.group and QuoteItem.group are SET_NULL.
Designs & Users¶
erDiagram
User ||--o| UserProfile : has
User ||--o{ ShippingAddress : "address book"
User ||--o{ WishlistItem : wishlist
User ||--o{ CustomDesign : creates
User ||--o{ CustomDesign : "reviewed_by"
CustomDesign }o--|| Product : "for product"
CustomDesign }o--o| ProductVariant : "variant"
CustomDesign |o--o{ CustomDesign : "source_design (copy)"
CustomDesign }o--o| Category : "collection_category"
CustomDesign |o--o{ OrderGroup : "base_design (team)"
WishlistItem }o--|| Product : references
NewsletterSubscription is keyed by email only (no FK to User): source,
confirmed_at, unsubscribed_at, synced_at (Resend contacts list).
Pricing & 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"
SpecialOffer }o--o{ Product : "applies to"
SpecialOffer }o--o{ Category : "applies to"
DecorationMethod ||--o{ DecorationAreaTier : "area tiers"
SiteSettings }o--o| DecorationMethod : "default_decoration_method"
Quotes¶
Customer quote requests. QuoteCounter issues gap-free sequential numbers per
year (format OFF-YYYY-NNNNN), mirroring InvoiceCounter (INV-YYYY-NNNNN).
erDiagram
User ||--o{ Quote : requests
User ||--o{ Quote : "created_by (staff)"
Quote ||--o{ QuoteItem : contains
QuoteItem }o--|| Product : references
QuoteItem }o--|| ProductVariant : references
QuoteItem }o--o| CustomDesign : includes
Quote }o--o| Order : "converted_order"
QuoteCounter ||..|| Quote : "numbers (per year)"
Numbering Schemes¶
| Entity | Format | Source |
|---|---|---|
| Order | ORD-YYYYMMDD-XXXXXX (6 random hex chars) |
Order.generate_order_number() |
| Order token | 64-char URL-safe secret for guest access | Order.order_token |
| Invoice | INV-YYYY-NNNNN, gap-free per year |
InvoiceCounter (row-locked) |
| Quote | OFF-YYYY-NNNNN, gap-free per year |
QuoteCounter |
| Mollie ids | tr_… / re_… from Mollie, unique |
Payment.mollie_payment_id, Refund.mollie_refund_id |
Key Models¶
Product¶
class Product(models.Model):
sku = models.CharField(max_length=50, unique=True)
name = models.CharField(max_length=300, db_index=True)
slug = models.SlugField(unique=True)
category = models.ForeignKey(Category, on_delete=models.PROTECT)
additional_categories = models.ManyToManyField(Category, blank=True)
brand = models.ForeignKey(Brand, on_delete=models.SET_NULL, null=True)
description = models.TextField()
material_composition, weight = CharField(...), CharField(...)
base_price = models.DecimalField(max_digits=10, decimal_places=2)
cost_price = models.DecimalField(...) # COGS, admin-only (not in the public API)
# Legacy whole-product print area (cm) + per-view positions; zones live in ProductPrintZone
max_print_width = models.IntegerField()
max_print_height = models.IntegerField()
print_positions = models.JSONField(default=dict, blank=True)
main_image = models.ImageField(upload_to='products/', blank=True)
main_image_width, main_image_height = PositiveIntegerField(null=True) # photo check (products/0038)
available_colors = models.ManyToManyField(Color, blank=True)
available_sizes = models.ManyToManyField(Size, blank=True)
is_active = models.BooleanField(default=True, db_index=True)
is_featured = models.BooleanField(default=False, db_index=True)
is_listed = models.BooleanField(default=True, db_index=True) # off: direct link only (products/0037)
show_print_area, show_designer_button = BooleanField(...), BooleanField(...)
size_chart_mode, size_chart_image, size_chart_columns, size_chart_rows
custom_text_price = models.DecimalField(default=0)
custom_number_price = models.DecimalField(default=0)
color_slot_labels = models.JSONField(...) # labels for two-colour products
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, db_index=True)
low_stock_threshold = models.IntegerField(default=10)
price_adjustment = models.DecimalField(max_digits=6, decimal_places=2, default=0)
cost_adjustment = models.DecimalField(max_digits=6, decimal_places=2, default=0)
image, image_front, image_back, image_left, image_right = ImageField(...)
is_active = models.BooleanField(default=True, db_index=True)
objects = ProductVariantQuerySet.as_manager() # .sellable(): variant AND colour active
class Meta:
unique_together = ['product', 'color', 'size']
The shop only shows and sells variants from ProductVariant.objects.sellable()
(is_active=True, color__is_active=True; property is_sellable). The admin, existing
orders and quotes do not filter on it.
ProductColorImage and print zones¶
Per product-colour combination: 4 view images, the legacy 4 × (x, y, width, height)
print-area boxes, and 12 responsive WebP variants (4 views × thumbnail/card/detail),
generated by the convert_image_to_webp Celery task from a post_save signal.
class ProductColorImage(models.Model):
product = models.ForeignKey(Product, related_name='color_images')
color = models.ForeignKey(Color, on_delete=models.CASCADE)
image_front, image_back, image_left, image_right = ImageField(...)
image_front_width, image_front_height, ... # pixels per view, photo check (products/0038)
print_area_front_x, print_area_front_y, print_area_front_width, print_area_front_height, ...
image_front_thumbnail_webp, image_front_card_webp, image_front_detail_webp, ...
is_primary = models.BooleanField(...)
class Meta:
unique_together = ['product', 'color']
Multi-zone print areas are modelled separately, so a product can have several priced zones per view:
class ProductPrintZone(models.Model):
product = models.ForeignKey(Product, related_name='print_zones')
view = models.CharField(choices=VIEW_CHOICES) # front/back/left/right
name, code = CharField(...), SlugField(...) # unique_together = ['product', 'code']
x, y, width, height = IntegerField(...) # pixels on the colour image
width_cm, height_cm, max_area_cm2 = DecimalField(...)
decoration_method = models.ForeignKey('pricing.DecorationMethod', null=True)
pricing_mode, price_modifier, fixed_price
show_boundary, is_active, sort_order
class ProductViewScale(models.Model): # px ↔ cm reference per view
product, view, ref_x, ref_y, ref_width, ref_height, ref_width_cm
# unique_together = ['product', 'view']
CustomDesign¶
class CustomDesign(DesignCostMixin, models.Model):
STATUS_CHOICES = draft | pending_review | approved | rejected | finalized | in_production
user = models.ForeignKey(User, null=True, on_delete=models.SET_NULL, related_name='designs')
session_key = models.CharField(max_length=100, blank=True) # anonymous designs
guest_email = models.EmailField(blank=True) # guest design check (designs/0012); wiped after 12 months
name = models.CharField(max_length=200, blank=True)
product = models.ForeignKey(Product, on_delete=models.CASCADE)
variant = models.ForeignKey(ProductVariant, null=True)
position = models.CharField(...) # legacy single position
design_json = models.JSONField(default=dict) # legacy single view
design_json_front / _back / _left / _right = JSONField(...) # Fabric.js JSON per view
preview_image / _front / _back / _left / _right = ImageField(...) # PNG only
print_file = models.FileField(null=True)
status = models.CharField(choices=STATUS_CHOICES, default='draft')
review_note, reviewed_at, reviewed_by # staff review
is_saved, saved_at
source_design = models.ForeignKey('self', null=True, on_delete=models.SET_NULL)
# Shop collection (ready-made article)
is_collection_item, collection_name, collection_slug, collection_description,
collection_category = models.ForeignKey(Category, null=True), collection_sort_order
OrderGroup (team order)¶
class OrderGroup(models.Model):
cart = models.ForeignKey(ShoppingCart, null=True, on_delete=models.SET_NULL, related_name='groups')
team_name = models.CharField(max_length=100, blank=True)
product = models.ForeignKey(Product, on_delete=models.PROTECT)
color = models.ForeignKey('products.Color', on_delete=models.PROTECT)
base_design = models.ForeignKey(CustomDesign, null=True, on_delete=models.PROTECT, related_name='team_groups')
roster = models.JSONField(default=list) # [{"variant_id", "size", "name", "number", "design_id"}, ...]
Each roster row becomes one CartItem (and later OrderItem) of quantity 1 with
group set, so sizes, names and numbers stay per piece; print_unit_cost() prices
the base design once over the whole group (set-up fees fall once per team). At most
200 rows (TEAM_ROSTER_MAX_ROWS in apps/orders/team_groups.py).
Order¶
class Order(models.Model):
STATUS_CHOICES = pending | paid | processing | printing | shipped | delivered
| ready_for_pickup | picked_up | cancelled | refunded
FULFILLMENT_CHOICES = shipping | pickup
order_number = models.CharField(max_length=50, unique=True) # ORD-YYYYMMDD-XXXXXX
order_token = models.CharField(max_length=64, unique=True) # guest access
user = models.ForeignKey(User, null=True, on_delete=models.SET_NULL)
email, phone
fulfillment_method = models.CharField(choices=FULFILLMENT_CHOICES, default='shipping')
shipping_* (blank for pickup), billing_*
subtotal, shipping_cost, tax_amount, discount_amount, total
discount_code = models.ForeignKey('pricing.DiscountCode', null=True, on_delete=models.SET_NULL)
status = models.CharField(choices=STATUS_CHOICES, default='pending', db_index=True)
needs_attention = models.BooleanField(default=False, db_index=True) # oversell, amount mismatch, artwork review
payment_method, payment_id, paid_at, payment_email_sent_at, admin_notified_at
shop_email_sent_at # shop mail "Nieuwe bestelling" (orders/0025)
tracking_number, shipping_provider, estimated_delivery_date, shipped_at, delivered_at
ready_for_pickup_at, picked_up_at, review_request_sent_at
terms_accepted_at, personalized_no_return_accepted_at
customer_notes, admin_notes
OrderItem snapshots product_name, variant_description, unit_price,
unit_cost, custom_text/custom_number (+ costs), printing_cost,
personalization (JSON), an optional group (team order) and a production_status
(pending | printing | completed | shipped); OrderItemPrintZone snapshots each
priced zone (zone_code, view, area_cm2, decoration_method_name, price).
Payment¶
class Payment(models.Model):
STATUS_CHOICES = open | pending | paid | failed | expired | canceled
order = models.ForeignKey(Order, related_name='payments')
mollie_payment_id = models.CharField(max_length=100, unique=True, db_index=True)
status = models.CharField(max_length=20, choices=STATUS_CHOICES, default='open')
amount = models.DecimalField(max_digits=10, decimal_places=2)
method = models.CharField(max_length=50, default='ideal') # updated from Mollie once chosen
issuer = models.CharField(max_length=100, blank=True)
checkout_url = models.URLField(max_length=500, blank=True)
metadata = models.JSONField(default=dict, blank=True)
Refund statuses: queued | pending | processing | refunded | failed | canceled
(failed/canceled never count as refunded money).
Invoice¶
class Invoice(models.Model):
order = models.OneToOneField(Order, related_name='invoice')
invoice_number = models.CharField(max_length=20, unique=True) # INV-YYYY-NNNNN
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)
class InvoiceCounter(models.Model):
year = models.IntegerField(unique=True)
last_number = models.IntegerField(default=0)
InformerBooking¶
class InformerBooking(models.Model):
STATUS_CHOICES = sending | retrying | sent | preview | failed | skipped
order = models.ForeignKey('orders.Order', on_delete=models.PROTECT, related_name='informer_bookings')
kind = models.CharField(max_length=10, default='invoice')
status, attempts, started_at, next_attempt_at, retry_until
informer_relation_id, informer_invoice_id (unique), informer_invoice_number, informer_url
payload, warnings, last_error, amount_mismatch
informer_incl_vat, informer_paid, informer_open, informer_concept # payment status (informer/0002)
DiscountCode¶
class DiscountCode(models.Model):
code = models.CharField(max_length=50, unique=True)
name = models.CharField(max_length=200)
discount_type = models.CharField(choices=DISCOUNT_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) # incremented once per paid order (F() expression)
valid_from = models.DateTimeField(default=timezone.now)
valid_until = models.DateTimeField(null=True)
is_active = models.BooleanField(default=True)
applies_to_products = models.ManyToManyField(Product, blank=True)
applies_to_categories = models.ManyToManyField(Category, blank=True)
SiteSettings (singleton)¶
Loaded with SiteSettings.load() and cached. Besides store/footer data, VAT and
shipping defaults it holds the feature switches staff toggle in the admin:
stock_management_enabled, team_order_enabled, all_payment_methods_enabled +
payment_methods_offered (Mollie methods), cart_reminder_enabled +
cart_reminder_delay_hours, review_request_enabled + review_request_delay_days,
google_place_id, newsletter_enabled, whatsapp_buttons_enabled (core/0026),
catalog_color_swatches_enabled (core/0029),
shop_order_email_enabled + shop_order_email_address (core/0027) and
shop_order_email_enabled_since (core/0028),
informer_mode (off | preview | live) + informer_payment_condition and the
informer_*_ledger_number fields, homepage_collection_category,
default_decoration_method, design_text_price, design_image_price. The footer
block has footer_whatsapp_number instead of a LinkedIn URL (core/0025).
Database Indexes¶
Key indexes for performance (from Meta.indexes and db_index=True):
# Product
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'])
# Order
models.Index(fields=['created_at', 'status'], name='idx_order_analytics')
order_number, order_token, email, status, fulfillment_method, needs_attention: db_index
# Payment / PaymentAuditLog
mollie_payment_id: unique + db_index
models.Index(fields=['order', '-timestamp'])
models.Index(fields=['action', '-timestamp'])
Migrations¶
Always review migrations before applying:
# Create migrations
python manage.py makemigrations --dry-run
# Review SQL
python manage.py sqlmigrate app_name 0001
# Apply migrations
python manage.py migrate
Migrations are single-track work: never create them in parallel branches (see
.claude/CLAUDE.md). On production, scripts/deploy-rolling.sh runs deploy checks
and a backup before migrating.