Domain Model
All schemas defined in
@nx/core/src/models/schemas/inventory/. Tables use PascalCase names. Numeric columns usestandardNumeric=decimal(15, 4).
1. Full ERD
2. Common Columns
Every entity below adds these via
generateCommonColumnDefs()unless noted.
| Column | Type | Notes |
|---|---|---|
id | text | PK, Snowflake via IdGenerator |
createdAt | timestamptz | now() default |
modifiedAt | timestamptz | updated on write |
createdBy | text | user id |
modifiedBy | text | user id |
deletedAt | timestamptz | soft-delete marker |
metadata | jsonb | extension bag |
status | text | when using generateCommonColumnWithStatusDefs |
3. Entities
3.1 InventoryLocation
| Property | Value |
|---|---|
| Table | InventoryLocation |
| Source | core/src/models/schemas/inventory/inventory-location/schema.ts |
| Soft-delete | yes |
| Owner | merchantId |
| Field | Type | Required | Description |
|---|---|---|---|
merchantId | text | ✓ | Owner |
parentId | text | Self-ref for hierarchy | |
identifier | text | ✓ | Auto, LOC prefix |
isDefault | boolean | ✓ | Default false; partial unique per merchant when true |
status | text | ✓ | NEW / ACTIVATED / DEACTIVATED / ARCHIVED (default NEW) |
name | i18n jsonb | ✓ | Display name |
type | text | ✓ | InventoryLocationTypes (default PHYSICAL) |
location | jsonb | Address ({ main, sub, long, lat, postCode }) |
Invariants: exactly one isDefault=true per merchant; cycle-safe parentId.
3.2 InventoryItem
| Property | Value |
|---|---|
| Table | InventoryItem |
| Polymorphic | yes — (itemType, itemId) via generatePrincipalColumnDefs({ discriminator: 'item' }); references Material or ProductVariant |
| Field | Type | Required | Description |
|---|---|---|---|
merchantId | text | ✓ | Owner |
itemType | text | ✓ | MATERIAL / PRODUCT_VARIANT |
itemId | text | ✓ | FK target id |
identifier | text | ✓ | Auto-generated, INI prefix |
status | text | ✓ | InventoryItemStatuses (default ACTIVATED) |
Indexes: partial unique (merchantId, itemType, itemId) WHERE deletedAt IS NULL; non-unique (merchantId), (merchantId, status). Idempotent upsert: InventoryItemRepository.ensureInventoryItem keyed by (merchantId, itemType, itemId).
3.3 InventoryStock
| Property | Value |
|---|---|
| Table | InventoryStock |
| Source | core/src/models/schemas/inventory/inventory-stock/schema.ts |
| Bucket key | (inventoryItemId, inventoryLocationId, lotNumber, serialNumber) UNIQUE NULLS NOT DISTINCT |
| Field | Type | Required | Description |
|---|---|---|---|
inventoryItemId | text | ✓ | FK |
inventoryLocationId | text | ✓ | FK |
merchantId | text | ✓ | Denormalized from InventoryItem |
quantityOnHand | decimal(15,4) | ✓ | Total physical stock |
quantityReserved | decimal(15,4) | ✓ | Allocated for orders |
quantityAvailable | decimal(15,4) | ✓ | Stored field; service maintains as onHand − reserved |
lastCountedAt | timestamptz | — | |
lastStockedAt | timestamptz | — | |
lotNumber | text | Bucket-key extension | |
serialNumber | text | Bucket-key extension | |
expiryDate | timestamptz | FEFO support | |
manufactureDate | timestamptz | — | |
averageCost | decimal(15,4) | AVCO snapshot | |
costingMethod | text | ✓ | AVERAGE (default), FIFO, LIFO, etc. |
Indexes: (inventoryItemId), (inventoryLocationId), (merchantId).
Atomic mutator: InventoryStockRepository.adjustStock({ stockId, adjustOnHand, adjustAvailable, adjustReserved, forceNonNegative }) — single SQL UPDATE with optional non-negative guard. Returns null if guard fails.
3.4 InventoryTracking
| Property | Value |
|---|---|
| Table | InventoryTracking |
| Mutability | append-only audit log; CRUD repository but service writes only on stock change |
| Field | Type | Required | Description |
|---|---|---|---|
inventoryStockId | text | ✓ | FK |
merchantId | text | ✓ | Denormalized from stock chain |
referenceType | text | ✓ | One of §4; typed<TInventoryTrackingReferenceType> |
referenceId | text | Originating doc id (nullable for orphan adjustments) | |
uomId | text | ✓ | Unit at write time |
multiplier | decimal(15,4) | ✓ | UoM conversion factor (default 1) |
quantityBefore | decimal(15,4) | ✓ | Pre-mutation snapshot |
quantityChange | decimal(15,4) | ✓ | Delta (signed) |
quantityAfter | decimal(15,4) | ✓ | Post-mutation snapshot |
effectivePrice | decimal(15,4) | Per-unit cost (PURCHASE writes only) | |
fromLocationId | text | TRANSFER source | |
toLocationId | text | TRANSFER destination | |
reasonCode | text | One of 13 InventoryTrackingReasons | |
lotNumber / serialNumber / expiryDate | text / timestamptz | Audit-immutable snapshot of moved bucket | |
remainingQuantity | decimal(15,4) | FIFO layer tracker (decrements as outbound consumes) | |
note | text | Free-form (see InventoryTrackingNotes) | |
createdBy / modifiedBy | text | User audit (anonymous allowed) |
Indexes: (inventoryStockId), (referenceId), (referenceType, referenceId), (fromLocationId), (toLocationId), (uomId), (merchantId).
Idempotency: lookups by (referenceType, referenceId, inventoryStockId) before write to avoid double-count on Kafka redelivery.
3.5 InventoryIdentifier
| Property | Value |
|---|---|
| Table | InventoryIdentifier |
| Polymorphic | tags InventoryItem or InventoryStock |
| Field | Type | Required | Description |
|---|---|---|---|
principalType | text | ✓ | INVENTORY_ITEM / INVENTORY_STOCK |
principalId | text | ✓ | FK target id |
scheme | text | ✓ | SKU / BARCODE / QRCODE / IMEI / SERIAL |
value | text | ✓ | Identifier string |
Constraint: unique (scheme, value) per principal — prevents duplicate barcodes.
3.6 InventoryTicket / InventoryTicketItem
InventoryTicket fields:
| Field | Type | Required | Description |
|---|---|---|---|
merchantId | text | ✓ | Owner |
identifier | text | ✓ | Auto, ITI prefix |
type | text | ✓ | InventoryTicketTypes (default UNKNOWN); see §5.4 for full set |
status | text | ✓ | Default DRAFT |
partnerType / partnerId | text | VENDOR / CUSTOMER (when applicable) | |
sourceLocationId / destinationLocationId | text | For TRANSFER | |
originReferenceType / originReferenceId | text | Lineage to triggering doc (e.g., a sale return ref) | |
returnOfTicketId | text | FK self — when this ticket is a return of another | |
backorderOfTicketId | text | FK self — backorder lineage | |
spawnedPurchaseOrderId | text | FK to PurchaseOrder — when ticket spawned a PO | |
reasonCode | text | One of InventoryTrackingReasons | |
effectiveDate / submittedAt / approvedAt / startedAt / completedAt / cancelledAt | timestamptz | Per-status timestamps | |
approvedBy | text | User audit for approval step | |
note | text | Free-form |
Behavior: ticket is a workflow document — no stock effect until COMPLETED (at which point InventoryTicketItem lines drive stock adjustments).
3.7 PurchaseOrder / PurchaseOrderItem
PurchaseOrder fields:
| Field | Type | Required | Description |
|---|---|---|---|
merchantId | text | ✓ | Owner |
purchaseOrderNumber | text | ✓ | Unique; default = <YYYYMMDDHHmmss>-<snowflake> |
name | text | Default = PurchaseOrder-<snowflake> | |
slug | text | Unique; same default pattern | |
vendorId | text | ✓ | FK to Vendor |
inventoryLocationId | text | ✓ | Destination on receive |
status | text | ✓ | See §5.1 — 6 values, default DRAFT |
orderDate | timestamptz | ✓ | Default now() |
expectedDeliveryDate / actualDeliveryDate | timestamptz | — | |
draftAt / processingAt / confirmedAt / receivedAt / completedAt / closedAt / cancelledAt | timestamptz | Per-status timestamps | |
currency | text | ✓ | Default VND |
exchangeRate | decimal(12,6) | Default 1 | |
subtotal / discount / tax / total | decimal(15,4) | ✓ | Recalculated by updateSummaryFromItems |
Indexes: (inventoryLocationId), (merchantId), (merchantId, status), (vendorId).
PurchaseOrderItem fields:
| Field | Type | Required | Description |
|---|---|---|---|
purchaseOrderId | text | ✓ | FK |
itemType | text | ✓ | MATERIAL / PRODUCT_VARIANT (default ProductVariant) |
itemId | text | ✓ | FK target id (polymorphic, no DB FK) |
currency | text | ✓ | Default VND |
uomId | text | ✓ | Soft-ref to UnitOfMeasure.id |
multiplier | decimal(15,4) | ✓ | UoM-to-base (default 1) |
quantity | decimal(15,4) | ✓ | Ordered qty |
receivedQuantity | decimal(15,4) | ✓ | Cumulative received |
unitPrice | decimal(15,4) | ✓ | Per UoM |
discount / tax | decimal(15,4) | ✓ | Per-line |
total | decimal(15,4) | Computed | |
lotNumber / expiryDate / manufactureDate | text / timestamptz | Per-line lot/serial metadata | |
serialNumbers | jsonb | string[] — for serialized inventory | |
landedCostShare | decimal(15,4) | Allocated landed cost portion | |
effectiveCost | decimal(15,4) | Generated column (unitPrice + landedCostShare) |
Idempotency on add: same (purchaseOrderId, itemType, itemId, uomId) → quantity sums instead of duplicating.
3.8 Vendor / VendorItem
Vendor fields:
| Field | Type | Required | Description |
|---|---|---|---|
merchantId | text | ✓ | Owner |
identifier | text | ✓ | Auto, VEN prefix |
slug | text | ✓ | URL-safe |
name | i18n jsonb | ✓ | Display |
description | i18n jsonb | — | |
status | text | ✓ | ACTIVATED / DEACTIVATED / ARCHIVED (default ACTIVATED) |
location | jsonb | { main, sub, long, lat, postCode } | |
taxNumber | text | Tax registration | |
currency | text | ✓ | Default VND |
contacts | jsonb (array) | Array<IVendorContact> (default []) | |
note | text | Free-form |
VendorItem fields — M:N catalog (NO vendorId on Material / ProductVariant):
| Field | Type | Required | Description |
|---|---|---|---|
vendorId | text | ✓ | FK |
merchantId | text | ✓ | Denormalized |
itemType | text | ✓ | MATERIAL / PRODUCT_VARIANT |
itemId | text | ✓ | FK target id |
uomId | text | Vendor's catalog UoM (soft ref) | |
unitPrice | decimal(15,4) | Quoted price | |
multiplier | decimal(15,4) | UoM-to-base conversion | |
isPreferred | boolean | ✓ | Partial unique per (merchantId, itemType, itemId) |
status | text | ✓ | ACTIVATED / DEACTIVATED / ARCHIVED |
lastInvoiced | jsonb | Snapshot from latest PO receive: { unitPrice, uomId, multiplier, orderedAt, receivedAt } |
Atomic preferred flip: VendorItemRepository.setPreferredAtomic demotes other rows and promotes target in one statement.
3.9 Material / MaterialIdentifier
Material fields:
| Field | Type | Required | Description |
|---|---|---|---|
merchantId | text | ✓ | Owner |
identifier | text | ✓ | Auto, MAT prefix |
slug | text | ✓ | URL-safe |
name | i18n jsonb | ✓ | Display |
description | i18n jsonb | — | |
status | text | ✓ | MaterialStatuses (default ACTIVATED) |
type | text | ✓ | MaterialTypes (default RAW); see source enum for full set |
uom | jsonb | IUomRole — { base, purchase, sale } (default empty strings); see ADR-0005 | |
cost | decimal(15,4) | Standard cost reference (no default) | |
weight | decimal(15,4) | — | |
categoryId | text | FK to Category | |
metadata | jsonb | IMaterialMetadata — by convention contains inventory.allowOversell (default false) + inventory.isInventoryTracked (default true) |
MaterialIdentifier fields:
| Field | Type | Required | Description |
|---|---|---|---|
materialId | text | ✓ | FK |
scheme | text | ✓ | SYSTEM (auto, prefix MAT) / SLUG / SKU / BARCODE / QRCODE |
value | text | ✓ | Unique per (materialId, scheme) |
3.10 MaterialRecipe / MaterialRecipeItem
MaterialRecipe fields:
| Field | Type | Required | Description |
|---|---|---|---|
merchantId | text | ✓ | Owner |
principalType | text | ✓ | MATERIAL / PRODUCT_VARIANT |
principalId | text | ✓ | What this recipe produces |
status | text | ✓ | DRAFT / ACTIVATED / DEACTIVATED |
type | text | ✓ | KIT (deduct at sale) / MANUFACTURED (requires ProductionOrder) |
version | int | Bumped on aggregate update |
MaterialRecipeItem fields:
| Field | Type | Required | Description |
|---|---|---|---|
materialRecipeId | text | ✓ | FK to MaterialRecipe |
principalType | text | ✓ | Polymorphic component type (MATERIAL or PRODUCT_VARIANT) |
principalId | text | ✓ | FK target id (component) |
quantity | decimal(15,4) | ✓ | Required quantity per principal unit |
uomId | text | ✓ | Soft ref |
isOptional | boolean | ✓ | Default false — when true, missing component does not block production |
Indexes: partial unique (principalType, principalId, materialRecipeId) WHERE deletedAt IS NULL; non-unique (materialRecipeId), (uomId).
3.11 ProductionOrder
| Field | Type | Required | Description |
|---|---|---|---|
merchantId | text | ✓ | Owner |
productionNumber | text | ✓ | Unique per merchant |
targetType | text | ✓ | Polymorphic produced-thing (MATERIAL / PRODUCT_VARIANT); default ProductVariant |
targetId | text | ✓ | FK target id |
materialRecipeId | text | ✓ | FK to recipe used |
plannedQuantity / actualQuantity / scrapQuantity | decimal(15,4) | ✓ | Production tracking |
uom | text | ✓ | UoM code (text, not jsonb) |
locationId | text | ✓ | FK to InventoryLocation |
status | text | ✓ | ProductionOrderStatuses (default DRAFT) |
scheduledStartAt / scheduledEndAt / startedAt / completedAt / cancelledAt | timestamptz | Lifecycle timestamps | |
outputLotNumber / outputExpiryDate | text / timestamptz | Output bucket metadata |
3.12 UnitOfMeasure
| Field | Type | Required | Description |
|---|---|---|---|
merchantId | text | NULL = system-wide; else merchant override | |
code | text | ✓ | e.g. kg, box, pair |
name | i18n jsonb | ✓ | Display |
category | text | ✓ | COUNT / WEIGHT / VOLUME / TIME |
referenceCode | text | Base unit code (self-ref) | |
ratio | decimal(15,4) | ✓ | Ratio to base (1.0 for base unit) |
Three-level scope: system (merchantId NULL) → merchant override → product/material (via uom jsonb on Material).
4. InventoryTrackingReferenceTypes
| Value | Used by |
|---|---|
PURCHASE_ORDER | PO receive |
SALE_ORDER | Sale payment success → product deduct + material reserve |
KITCHEN_TICKET | Kitchen ticket consume |
KITCHEN_TICKET_ITEM | Kitchen ticket item-level consume |
INVENTORY_TICKET | Workflow tickets (transfer, adjust, count, scrap, return) |
PRODUCTION_ORDER | Production consume + output |
ADJUSTMENT | Manual admin entry |
UNKNOWN | Fallback |
5. Status Enums
5.1 PurchaseOrderStatuses
| Value | Code | Stage |
|---|---|---|
DRAFT | 001_DRAFT | Items mutable |
PROCESSING | 203_PROCESSING | Items frozen, awaiting goods |
RECEIVED | 205_RECEIVED | Some/all items received, partial complete |
COMPLETED | 303_COMPLETED | All items fully received |
CLOSED | 404_CLOSED | Terminal — no further changes |
CANCELLED | 505_CANCELLED | Terminal — voided |
5.2 MaterialRecipeStatuses
| Value | Code |
|---|---|
DRAFT | 001_DRAFT |
ACTIVATED | 201_ACTIVATED |
DEACTIVATED | 202_DEACTIVATED |
5.3 Vendor / VendorItemStatuses
| Value | Code |
|---|---|
ACTIVATED | 201_ACTIVATED |
DEACTIVATED | 202_DEACTIVATED |
ARCHIVED | 300_ARCHIVED |
5.4 InventoryTicketStatuses
| Value | Code |
|---|---|
DRAFT | 001_DRAFT |
SUBMITTED | 200_SUBMITTED |
APPROVED | 250_APPROVED |
IN_PROGRESS | 300_IN_PROGRESS |
COMPLETED | 303_COMPLETED |
CANCELLED | 505_CANCELLED |
5.5 ReceivePurchaseOrderItemModes
| Value | Behavior |
|---|---|
OVERRIDE (default) | newReceived = receivedQuantity |
ACCUMULATIVE | newReceived = currentReceived + receivedQuantity |
6. FixedInventoryTrackingTypes (19)
| Direction | Types |
|---|---|
| Inbound (6) | STOCK_IN, PURCHASE, TRANSFER_IN, RETURN_FROM_CUSTOMER, ADJUSTMENT_IN, PRODUCTION_COMPLETE |
| Outbound (10) | STOCK_OUT, SALE, TRANSFER_OUT, RETURN_TO_VENDOR, ADJUSTMENT_OUT, EXPIRED, LOST, DAMAGED, USED_INTERNAL, USED_AS_MATERIAL |
| Neutral (2) | INVENTORY_COUNT, ADJUSTMENT_NEUTRAL |
| Custom (1) | CUSTOM |
7. Cross-entity Invariants
| Invariant | Enforcement |
|---|---|
quantityAvailable = quantityOnHand − quantityReserved (post-condition) | Service layer maintains; adjustStock mutates all three |
Exactly one default InventoryLocation per merchant | InventoryLocationRepository.setDefaultAtomic |
Exactly one preferred VendorItem per (merchantId, itemType, itemId) | VendorItemRepository.setPreferredAtomic |
MaterialRecipeItem.principalId references either Material or ProductVariant (polymorphic via principalType) | Schema; service-level zod validates principal exists |
InventoryTracking is append-only (no UPDATE except via admin tooling) | Repository convention; service writes only on stock change |
InventoryItem polymorphism — exactly one (merchantId, itemType, itemId) | ensureInventoryItem upsert |
Material.metadata.inventory.allowOversell controls forceNonNegative flag passed to adjustStock | InventoryService.loadPrincipalRefs |
Vendor link to items goes via VendorItem only — no vendorId column on principals | Schema + ADR |
8. Soft-delete Behavior
| Entity | Soft-delete | Notes |
|---|---|---|
InventoryLocation, InventoryItem, InventoryStock, InventoryIdentifier, InventoryTicket, InventoryTicketItem, Vendor, VendorItem, Material, MaterialIdentifier, MaterialRecipe, MaterialRecipeItem, ProductionOrder, PurchaseOrder, PurchaseOrderItem, UnitOfMeasure | ✓ | deletedAt marker; reads default to IS NULL |
InventoryTracking | ✓ schema-side only | Service treats as immutable audit; never written deletedAt |