Sale Schema

The sale schema contains 2 models that manage the sale order lifecycle. Orders progress through a defined state machine from draft to completion or cancellation.

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

Models Overview

Model	Table Name	Description
SaleOrder	sale.SaleOrder	Sale order with status lifecycle and pricing totals
SaleOrderItem	sale.SaleOrderItem	Line items within a sale order

SaleOrder

Status Lifecycle

Status Values (from SaleOrderStatuses):

Status	Value	Terminal	Can Modify Items	Can Checkout	Can Cancel
DRAFT	001_DRAFT	No	Yes	Yes	Yes
PROCESSING	203_PROCESSING	No	No	No	Yes
PARTIAL	300_PARTIAL	No	No	No	Yes
COMPLETED	303_COMPLETED	Yes	No	No	No
CANCELLED	505_CANCELLED	Yes	No	No	No

Helper methods on SaleOrderStatuses:

• isValid(status) -- checks if status is in the valid set
• isTerminal(status) -- returns true for COMPLETED or CANCELLED
• canModifyItems(status) -- returns true only for DRAFT
• canCheckout(status) -- returns true only for DRAFT
• canRevertToCart(status) -- returns true only for PROCESSING
• canCancel(status) -- returns true for any active status (DRAFT, PROCESSING, PARTIAL)

Columns

Column	Type	Constraints	Description
order_number	text	NOT NULL, UNIQUE	Auto-generated unique order number
name	text		Order name
slug	text	UNIQUE	URL-friendly identifier
validity	jsonb		{ from: string, to: string } validity period
status	text	NOT NULL, DEFAULT 001_DRAFT, indexed	Order status
draft_at	timestamptz		Timestamp when order entered DRAFT
processing_at	timestamptz		Timestamp when order entered PROCESSING
partial_at	timestamptz		Timestamp when order entered PARTIAL
completed_at	timestamptz		Timestamp when order COMPLETED
cancelled_at	timestamptz		Timestamp when order CANCELLED
cancellation_reason	text		Reason for cancellation
merchant_id	text	NOT NULL, indexed	FK to Merchant
sale_channel_id	text	NOT NULL, indexed	FK to SaleChannel
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 before tax/discount
tax	decimal(15,4)	NOT NULL, DEFAULT '0'	Total tax amount
discount	decimal(15,4)	NOT NULL, DEFAULT '0'	Total discount amount
total	decimal(15,4)	NOT NULL, DEFAULT '0'	Final total
counter	jsonb		Payment counter { paid: number, paidItemIds: string[], total: number }
metadata	jsonb		Typed as TSaleOrderMetadata
created_by	text		Creator user ID
modified_by	text		Last modifier user ID
+ common columns			id, createdAt, modifiedAt, deletedAt

TSaleOrderMetadata

type TSaleOrderMetadata = {
  note?: string;
  merchantId: string;
  finance:
    | { use: false }
    | {
        use: true;
        walletId: string;
        categoryId: string;
      };
};

The finance field controls whether a finance transaction is automatically created when the order completes:

• { use: false } -- no finance integration
• { use: true, walletId, categoryId } -- automatically create an income transaction in the specified wallet and category

Relations

Relation	Type	Target	Description
creator	One	User	Created by user
modifier	One	User	Last modified by user
saleChannel	One	SaleChannel	Associated sale channel
items	Many	SaleOrderItem	Line items in this order

SaleOrderItem

Item Modes

Mode	Value	Description
PRODUCT	000_PRODUCT	Standard product item linked to a ProductVariant
CUSTOM	100_CUSTOM	Custom/manual item not linked to a product

Columns

Column	Type	Constraints	Description
sale_order_id	text	NOT NULL, indexed, FK	FK to SaleOrder
mode	text	NOT NULL, DEFAULT 000_PRODUCT	Item mode (PRODUCT or CUSTOM)
lead_item_id	text		Lead item for grouped/addon items
item_type	text		Polymorphic entity type (default: ProductVariant)
item_id	text		Polymorphic entity ID
currency	text	NOT NULL, DEFAULT 'VND'	Currency code
base_price	decimal(15,4)	NOT NULL, DEFAULT '0'	Original base price
unit_price	decimal(15,4)	NOT NULL, DEFAULT '0'	Price per unit (after adjustments)
discount	decimal(15,4)	NOT NULL, DEFAULT '0'	Discount amount
quantity	decimal(15,4)	NOT NULL, DEFAULT '1'	Quantity
tax	decimal(15,4)	NOT NULL, DEFAULT '0'	Tax amount
total	decimal(15,4)	NOT NULL, GENERATED	Computed: (unit_price * quantity + tax)
fare_id	text		Reference to the Fare used for pricing
fare_provider	text		Provider that supplied the fare
price_metadata	jsonb		TPriceMetadata with fare source details
metadata	jsonb		TProductMetadata with product snapshot
created_by	text		Creator user ID
modified_by	text		Last modifier user ID
+ common columns			id, createdAt, modifiedAt, deletedAt

Indexes: (saleOrderId), (itemType, itemId)

Foreign key: saleOrderId references SaleOrder.id

TProductMetadata

Product snapshot captured at time of sale:

type TProductMetadata = {
  name: { default: string; vi?: string; en?: string };
  description?: string;
  sku?: string;
  barcode?: string;
  imageUrl?: string;
  category?: string;
  subcategory?: string;
  brand?: string;
  attributes?: Record<string, string | number | boolean>;
  taxRate?: number;
  externalId?: string;
  externalSource?: string;
  customData?: Record<string, unknown>;
};

TFareSource (Discriminated Union)

Tracks how the price was determined:

// Manual pricing (operator-entered)
type TFareSourceManual = {
  type: 'MANUAL';
  unitPrice: number;
  basePrice: number;
  tax: { mode: 'AMOUNT'; value: number } | { mode: 'PERCENTAGE'; value: number };
};

// System pricing (from fare engine)
type TFareSourceSystem = {
  type: 'SYSTEM';
  fareId: string;
  unitPrice: number;
  basePrice: number;
  originalPrice?: number;
  provider?: string;
  appliedRules?: Array<{ fareRuleId: string; code: string }>;
  validUntil?: string;
  metadata?: Record<string, unknown>;
};

type TFareSource = TFareSourceManual | TFareSourceSystem;

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
• Inventory Schema -- 8 inventory models
• Finance Schema -- 3 finance models
• Payment Schema -- 1 webhook config model
• Migrations -- DDL migrations and seed data framework
• Sale Package -- Sale order service, checkout, and payment integration