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
Factoryis the central entity identified by a uniquefactoryCodeUserFactoryAccessis a junction table with a unique constraint on(userId, factoryId)and anAccessLevelenum (viewer,editor,admin)Userhas both a directfactoryId(default factory) and granular per-factory access viaUserFactoryAccessPurchaseOrder,CustomerShipment, andWorkOrdereach have a nullablefactoryIdto support gradual migration of existing data- On factory deletion: entity FKs are SET NULL (preserves records), while
UserFactoryAccessrows CASCADE DELETE - On user deletion:
UserFactoryAccessrows 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
PurchaseOrderis the top-level container — shipments and work orders both reference itWorkOrderhas FKs to bothPurchaseOrderandCustomerShipment, withpoIdcurrently nullable during migration- Soft deletes are used across all three entities (
isDeleted,deletedAt,deletedBy) CustomerShipmenttracks cancellation state withcancelledShipmentflag and replacement shipment referencesShipmentLabelStatustracks the label lifecycle:Pending→Acknowledged→Printed→Shipped(orSuspended)- Bilingual support:
StyleandColorhave bothCnandEnname fields - Airtable sync:
Style,Color, andSizeare mastered in Airtable (Product Master base). Each table has a nullableairtableRecordId(unique) andairtableSyncedAttimestamp. 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 byairtableRecordId. 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
GarmentMeasurementis a junction table with a unique constraint on(styleId, measurementId, size)— each style has one measurement value per measurement type per sizeMaterialConstructionis a junction table with a unique constraint on(styleId, materialId)— each style has one set of construction notes per materialMeasurementandMaterialusesortOrderto control display ordering in the UI- Both junction tables cascade delete when their parent
Styleis deleted - All reference tables support bilingual (
Cn/En) fields