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
  • Airtable sync: Style, Color, and Size are mastered in Airtable (Product Master base). Each table has a nullable airtableRecordId (unique) and airtableSyncedAt timestamp. Airtable Automations post to /api/webhooks/v1/airtable/{style|color|size} on record create/update; the handler fetches the full record from the Airtable API and upserts keyed by airtableRecordId. Each endpoint has its own API key so secrets can be rotated independently.

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