Inventory Schema

The inventory schema contains 12 models that manage stock levels, inventory tracking, purchase orders, vendor relationships, and bill-of-materials recipes with raw-material tracking. It supports multi-location inventory with detailed audit trails.

Source: packages/core/src/models/schemas/inventory/

Models Overview

Model	Table Name	Description
InventoryLocation	inventory.InventoryLocation	Physical or logical storage locations
InventoryItem	inventory.InventoryItem	Trackable items linked to product variants or materials
InventoryStock	inventory.InventoryStock	Stock quantities for an inventory item
InventoryTracking	inventory.InventoryTracking	Audit log of all stock movements
InventoryTrackingType	inventory.InventoryTrackingType	Categorization of tracking movements
PurchaseOrder	inventory.PurchaseOrder	Purchase orders from vendors
PurchaseOrderItem	inventory.PurchaseOrderItem	Line items within a purchase order
Vendor	inventory.Vendor	Supplier/vendor information
Material	inventory.Material	Raw materials and semi-finished goods used in bill-of-materials recipes
MaterialIdentifier	inventory.MaterialIdentifier	Scheme-based identifiers (SKU, BARCODE, QRCODE, …) attached to a Material
MaterialRecipe	inventory.MaterialRecipe	Versioned bill-of-materials recipe attached to a principal (typically a ProductVariant)
MaterialRecipeItem	inventory.MaterialRecipeItem	Line item in a MaterialRecipe: a Material consumed at a given quantity

InventoryLocation

Physical or logical storage locations with hierarchical support.

Column	Type	Constraints	Description
identifier	text	NOT NULL, UNIQUE	Auto-generated (e.g., LOC_20260216_...)
name	jsonb (i18n)	NOT NULL	Internationalized name
status	text	NOT NULL, DEFAULT NEW	Location status (NEW, ACTIVATED, DEACTIVATED, ARCHIVED)
location	jsonb (ILocation)		Physical address and coordinates
is_default	boolean	NOT NULL, DEFAULT false	Whether this is the default location
merchant_id	text	NOT NULL	FK to Merchant
parent_id	text		FK to parent InventoryLocation
+ common columns			id, createdAt, modifiedAt, deletedAt, metadata

InventoryItem

Trackable items linked to a principal entity via polymorphic references. The principal can be a Product, ProductVariant, or Material; ProductVariant is the default.

Column	Type	Constraints	Description
item_type	text		Polymorphic entity type: Product, ProductVariant, or Material (default: ProductVariant)
item_id	text		Polymorphic entity ID
identifier	text	NOT NULL, UNIQUE	Auto-generated (e.g., SKU_20260216_...)
status	text	NOT NULL, DEFAULT ACTIVATED	Item status (ACTIVATED, DEACTIVATED, ARCHIVED)
min_stock_level	decimal(15,4)		Minimum stock threshold
max_stock_level	decimal(15,4)		Maximum stock threshold
base_unit	text	NOT NULL	Base unit of measure
inventory_location_id	text	NOT NULL	FK to InventoryLocation
+ common columns

InventoryStock

Stock quantities for an inventory item. Tracks on-hand, reserved, and available stock.

Column	Type	Constraints	Description
quantity_on_hand	decimal(15,4)	NOT NULL, DEFAULT '0'	Total physical stock
quantity_reserved	decimal(15,4)	NOT NULL, DEFAULT '0'	Allocated for orders
quantity_available	decimal(15,4)	NOT NULL, DEFAULT '0'	Available for sale (on-hand - reserved)
last_counted_at	isoTimestamp		Last physical count timestamp
last_stocked_at	isoTimestamp		Last stocking timestamp
inventory_item_id	text	NOT NULL	FK to InventoryItem
+ common columns

InventoryTracking

Audit log entry recording every stock movement with before/after quantities.

Column	Type	Constraints	Description
reference_id	text		Reference entity ID (e.g., SaleOrder ID, PurchaseOrder ID)
reference_type	text		Reference entity type
uom	text	NOT NULL	Unit of measure for this movement
multiplier	decimal(15,4)	NOT NULL, DEFAULT '1'	UOM multiplier
quantity_before	decimal(15,4)	NOT NULL	Stock quantity before movement
quantity_change	decimal(15,4)	NOT NULL	Change amount (positive or negative)
quantity_after	decimal(15,4)	NOT NULL	Stock quantity after movement
effective_price	decimal(15,4)		Price per unit for this movement
inventory_stock_id	text	NOT NULL	FK to InventoryStock
inventory_tracking_type_id	text	NOT NULL	FK to InventoryTrackingType
note	text		Movement note
created_by	text		Creator user ID
modified_by	text		Last modifier user ID
+ common columns

InventoryTrackingType

Categorization of tracking movements with direction (inbound, outbound, neutral).

Column	Type	Constraints	Description
type	text	NOT NULL	Tracking type code
name	jsonb (i18n)	NOT NULL	Internationalized name
description	jsonb (i18n)		Internationalized description
direction	text	NOT NULL	Direction: 000_IN, 100_OUT, 200_NEUTRAL
status	text	DEFAULT ACTIVATED	Status (ACTIVATED, DEACTIVATED, ARCHIVED)
merchant_id	text		FK to Merchant (null for global types)
+ common columns

Unique constraint: (type, merchantId)

Fixed Inventory Tracking Types

Inbound (IN):

Code	Value	Description
STOCK_IN	000_STOCK_IN	General stock in
PURCHASE	001_PURCHASE	Received from purchase order
TRANSFER_IN	002_TRANSFER_IN	Transferred from another location
RETURN_FROM_CUSTOMER	003_RETURN_FROM_CUSTOMER	Returned by customer
ADJUSTMENT_IN	004_ADJUSTMENT_IN	Manual increase adjustment
PRODUCTION_COMPLETE	005_PRODUCTION_COMPLETE	Completed production

Outbound (OUT):

Code	Value	Description
STOCK_OUT	100_STOCK_OUT	General stock out
SALE	101_SALE	Sold to customer
TRANSFER_OUT	102_TRANSFER_OUT	Transferred to another location
RETURN_TO_VENDOR	103_RETURN_TO_VENDOR	Returned to vendor
ADJUSTMENT_OUT	104_ADJUSTMENT_OUT	Manual decrease adjustment
EXPIRED	105_EXPIRED	Expired stock
LOST	106_LOST	Lost stock
DAMAGED	106_DAMAGED	Damaged stock
USED_INTERNAL	107_USED_INTERNAL	Used internally
USED_AS_MATERIAL	107_USED_AS_MATERIAL	Consumed as a raw material in a production/recipe flow

Neutral (NEUTRAL):

Code	Value	Description
INVENTORY_COUNT	200_INVENTORY_COUNT	Physical inventory count
ADJUSTMENT_NEUTRAL	201_ADJUSTMENT_NEUTRAL	Neutral adjustment (no quantity change)

PurchaseOrder

Purchase orders from vendors with a status lifecycle identical to SaleOrder.

Status Lifecycle

Status Values: DRAFT (001_DRAFT), PROCESSING (203_PROCESSING), RECEIVED (205_RECEIVED), COMPLETED (303_COMPLETED), CLOSED (404_CLOSED), CANCELLED (505_CANCELLED)

Active set (can be modified or cancelled): DRAFT, PROCESSING, RECEIVED, COMPLETED. Receivable set (can accept goods receipt): PROCESSING, RECEIVED, COMPLETED. Terminal set (read-only): CLOSED, CANCELLED.

Columns

Column	Type	Constraints	Description
purchase_order_number	text	NOT NULL, UNIQUE	Auto-generated (e.g., 20260216120000-...)
name	text		Order name (auto-generated)
slug	text	UNIQUE	URL-friendly identifier (auto-generated)
order_date	isoTimestamp	NOT NULL	Order date (defaults to now)
expected_delivery_date	isoTimestamp		Expected delivery date
actual_delivery_date	isoTimestamp		Actual delivery date
status	text	NOT NULL, DEFAULT 001_DRAFT	Order status
draft_at	isoTimestamp		Timestamp when entered DRAFT
processing_at	isoTimestamp		Timestamp when entered PROCESSING
confirmed_at	isoTimestamp		Timestamp when confirmed
received_at	isoTimestamp		Timestamp when entered RECEIVED
completed_at	isoTimestamp		Timestamp when entered COMPLETED
closed_at	isoTimestamp		Timestamp when entered CLOSED
cancelled_at	isoTimestamp		Timestamp when entered CANCELLED
merchant_id	text	NOT NULL	FK to Merchant
vendor_id	text	NOT NULL	FK to Vendor
inventory_location_id	text	NOT NULL	FK to InventoryLocation
currency	text	NOT NULL, DEFAULT 'VND'	Currency code
exchange_rate	decimal(12,6)	DEFAULT '1'	Exchange rate
subtotal	decimal(15,4)	NOT NULL, DEFAULT '0'	Subtotal
tax	decimal(15,4)	NOT NULL, DEFAULT '0'	Total tax
discount	decimal(15,4)	NOT NULL, DEFAULT '0'	Total discount
total	decimal(15,4)	NOT NULL, DEFAULT '0'	Final total
metadata	jsonb		TPurchaseOrderMetadata
created_by	text		Creator user ID
modified_by	text		Last modifier user ID
+ common columns

TPurchaseOrderMetadata

type TPurchaseOrderMetadata = {
  note?: string;
  merchantId?: string;
  isDiscountManual?: boolean; // true = use PO.discount, ignore item discounts
  isTaxManual?: boolean;      // true = use PO.tax, ignore item taxes
  [key: string]: any;
};

PurchaseOrderItem

Line items within a purchase order with polymorphic product/material reference and computed total.

Column	Type	Constraints	Description
purchase_order_id	text	NOT NULL	FK to PurchaseOrder
item_type	text		Polymorphic entity type: Product, ProductVariant, or Material (default: ProductVariant)
item_id	text		Polymorphic entity ID
currency	text	NOT NULL, DEFAULT 'VND'	Currency code
uom	text	NOT NULL	Unit of measure
multiplier	decimal(15,4)	NOT NULL, DEFAULT '1'	UOM multiplier
quantity	decimal(15,4)	NOT NULL	Ordered quantity
received_quantity	decimal(15,4)	NOT NULL, DEFAULT '0'	Received quantity
unit_price	decimal(15,4)	NOT NULL, DEFAULT '0'	Price per unit
tax	decimal(15,4)	NOT NULL, DEFAULT '0'	Tax amount
discount	decimal(15,4)	NOT NULL, DEFAULT '0'	Discount amount
total	decimal(15,4)	NOT NULL, GENERATED	Computed: (unit_price * quantity * multiplier + tax - discount)
created_by	text		Creator user ID
modified_by	text		Last modifier user ID
+ common columns

Item Modes: PRODUCT (000_PRODUCT), MATERIAL (001_MATERIAL), CUSTOM (100_CUSTOM)

• PRODUCT mode: line item references a ProductVariant (default).
• MATERIAL mode: line item references a Material (raw ingredient or semi-finished good purchased for BOM consumption).
• CUSTOM mode: free-text line item with no SKU reference — the metadata fields (name, description, sku, barcode, imageUrl) are used instead.

Vendor

Supplier/vendor information.

Column	Type	Constraints	Description
name	text	NOT NULL	Vendor name
identifier	text	NOT NULL, UNIQUE	Auto-generated (e.g., VEN_20260216_...)
emails	text[]	DEFAULT '{}'	Email addresses
phones	text[]	DEFAULT '{}'	Phone numbers
location	jsonb (ILocation)		Physical address
tax_number	text		Tax identification number
note	text		Notes
merchant_id	text	NOT NULL	FK to Merchant
+ common columns

Material

Raw materials and semi-finished goods used as components inside a MaterialRecipe (bill-of-materials). Materials are not sellable on their own — they exist to be consumed by a recipe that produces a sellable ProductVariant.

Column	Type	Constraints	Description
slug	text	NOT NULL	Human-readable identifier within the Merchant scope
status	text	NOT NULL, DEFAULT 201_ACTIVATED	Material status (ACTIVATED, DEACTIVATED, ARCHIVED)
identifier	text	NOT NULL, UNIQUE	Auto-generated (e.g., MAT_20260414_...)
name	jsonb (i18n)	NOT NULL	Internationalized name
description	jsonb (i18n)		Internationalized description
merchant_id	text	NOT NULL	FK to Merchant
+ common columns			id, createdAt, modifiedAt, deletedAt, metadata

Status Values: ACTIVATED (201_ACTIVATED), DEACTIVATED (202_DEACTIVATED), ARCHIVED (203_ARCHIVED)

MaterialIdentifier

Scheme-based identifier for a Material. Enables looking up a Material by SKU, barcode, QR code, or other code systems. A Material can have multiple identifiers across different schemes; the (scheme, identifier) pair is unique globally so the same barcode or SKU cannot be registered against two different materials.

Column	Type	Constraints	Description
scheme	text	NOT NULL, DEFAULT SYSTEM	Identifier scheme (SYSTEM, SLUG, SKU, BARCODE, QRCODE)
identifier	text	NOT NULL	The identifier value
material_id	text	NOT NULL	FK to Material
+ common columns

Unique index: UQ_MaterialIdentifier_scheme_identifier on (scheme, identifier).

Schemes: SYSTEM (internal ID, default), SLUG (URL-friendly), SKU (stock-keeping unit), BARCODE (linear barcode), QRCODE (QR code).

MaterialRecipe

Versioned bill-of-materials recipe defining which Materials are consumed to produce a given principal entity. The principal is polymorphic (currently Product or ProductVariant). Recipes are versioned — a single principal can have multiple recipe versions, but only the activated version is looked up at runtime.

Column	Type	Constraints	Description
principal_type	text		Polymorphic principal type: Product or ProductVariant
principal_id	text	NOT NULL	Polymorphic principal ID
status	text	NOT NULL, DEFAULT 201_ACTIVATED	Recipe status (DRAFT, ACTIVATED, DEACTIVATED)
merchantId	text	NOT NULL	FK to Merchant (note: camelCase column name)
name	jsonb (i18n)		Optional recipe display name
description	jsonb (i18n)		Optional recipe description
version	numeric(10,1)	NOT NULL, DEFAULT '1.0'	Recipe version
+ common columns

Unique index: UQ_MaterialRecipe_product_variant_id_version on (principal_type, principal_id, version). Prevents duplicate recipes of the same version for the same principal.

Status Values: DRAFT (001_DRAFT), ACTIVATED (201_ACTIVATED), DEACTIVATED (202_DEACTIVATED).

Principal Types: Product, ProductVariant — resolved via the polymorphic (principal_type, principal_id) pair.

INFO

The merchantId column is camelCase in the database (unlike other inventory tables that use snake_case). This is a historical artifact from migration 0004_abnormal_photon.sql where the column was added post-hoc. Query it as "merchantId" with quotes in raw SQL.

MaterialRecipeItem

A single line in a MaterialRecipe: "consume X units of Material Y." One recipe has many items; each item references exactly one Material.

Column	Type	Constraints	Description
quantity	decimal(15,4)	NOT NULL	Quantity of the material consumed per produced unit
unit	text	NOT NULL	Unit of measure for the quantity
material_id	text	NOT NULL	FK to Material
material_recipe_id	text	NOT NULL	FK to MaterialRecipe
+ common columns

Unique index: UQ_MaterialRecipeItem_material_id_material_recipe_id on (material_id, material_recipe_id). A Material can appear at most once as an item within the same recipe.

Entity Relationships

Related Documentation

• Database Overview -- Schema summary, common columns, shared types
• Entity Relationship Diagram -- Comprehensive ERD for all 55 models
• Public Schema -- 30 models in the public schema
• Pricing Schema -- 7 pricing models
• Allocation Schema -- 4 allocation models
• Sale Schema -- 2 sale order models
• Finance Schema -- 3 finance models
• Payment Schema -- 1 webhook config model
• Migrations -- DDL migrations and seed data framework