Skip to content

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.