Skip to main content

Database Design

Multi-Factory Support​

The multi-factory schema enables scoping core entities (purchase orders, customer shipments, work orders) to specific factories and controlling user access per factory with granular permissions.

ERD​

Design Decisions​

  • Factory is the central entity identified by a unique factoryCode
  • UserFactoryAccess is a junction table with a unique constraint on (userId, factoryId) and an AccessLevel enum (viewer, editor, admin)
  • User has both a direct factoryId (default factory) and granular per-factory access via UserFactoryAccess
  • PurchaseOrder, CustomerShipment, and WorkOrder each have a nullable factoryId to support gradual migration of existing data
  • On factory deletion: entity FKs are SET NULL (preserves records), while UserFactoryAccess rows CASCADE DELETE
  • On user deletion: UserFactoryAccess rows also CASCADE DELETE

Core Order Lifecycle​

The primary business flow: purchase orders contain customer shipments, which contain work orders. Each work order references a style, color, and size.

ERD​

Design Decisions​

  • PurchaseOrder is the top-level container — shipments and work orders both reference it
  • WorkOrder has FKs to both PurchaseOrder and CustomerShipment, with poId currently nullable during migration
  • Soft deletes are used across all three entities (isDeleted, deletedAt, deletedBy)
  • CustomerShipment tracks cancellation state with cancelledShipment flag and replacement shipment references
  • ShipmentLabelStatus tracks the label lifecycle: Pending → Acknowledged → Printed → Shipped (or Suspended)
  • Bilingual support: Style and Color have both Cn and En name fields

Material Reference Data​

Styles have associated garment measurements (per size) and material construction notes. These tables power the work order spec sheets.

ERD​

Design Decisions​

  • GarmentMeasurement is a junction table with a unique constraint on (styleId, measurementId, size) — each style has one measurement value per measurement type per size
  • MaterialConstruction is a junction table with a unique constraint on (styleId, materialId) — each style has one set of construction notes per material
  • Measurement and Material use sortOrder to control display ordering in the UI
  • Both junction tables cascade delete when their parent Style is deleted
  • All reference tables support bilingual (Cn/En) fields