Pricing Schema

The pricing schema contains 7 models that implement a flexible, rule-based pricing engine. It supports static pricing, dynamic pricing with contextual rules, hierarchical fare structures, costs, and multi-tax configuration.

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

Models Overview

Model	Table Name	Description
Fare	pricing.Fare	Price point with amount, validity period, and quantity constraints
FareSet	pricing.FareSet	Container linking a ProductVariant to its pricing configuration
FareRule	pricing.FareRule	Conditional rule that must be satisfied for a Fare to apply
Cost	pricing.Cost	Product variant cost tracking with time-based effective periods
Tax	pricing.Tax	Tax rate entries within a tax set
TaxSet	pricing.TaxSet	Tax configuration container using polymorphic ownership
TaxType	pricing.TaxType	Tax type definitions (VAT, Excise, etc.)

Fare

A pricing tier with optional validity period, quantity constraints, and rule-based conditions. Supports parent-child hierarchies for override/promotional pricing.

Column	Type	Constraints	Description
name	jsonb (i18n)		Internationalized name
description	jsonb (i18n)		Internationalized description
status	text	NOT NULL, DEFAULT ACTIVATED	Fare status
type	text		Fare type (SALE, OVERRIDE)
effective_from	isoTimestamp		Validity start date
effective_to	isoTimestamp		Validity end date
min_quantity	decimal(15,4)		Minimum quantity for this fare
max_quantity	decimal(15,4)		Maximum quantity for this fare
amount	decimal(15,4)		Price amount
rules_count	integer		Cached count of attached rules
children_count	integer		Cached count of child fares
fare_set_id	text	NOT NULL	FK to FareSet
parent_id	text		FK to parent Fare (self-reference)
+ common columns			id, createdAt, modifiedAt, deletedAt, metadata

Statuses: ACTIVATED, DEACTIVATED, ARCHIVED

Types:

• SALE -- Standard sale price
• OVERRIDE -- Override/promotional price

FareSet

Container linking a ProductVariant to its pricing configuration. One-to-one relationship with ProductVariant.

Column	Type	Constraints	Description
status	text	NOT NULL, DEFAULT ACTIVATED	FareSet status
product_variant_id	text	NOT NULL, UNIQUE	One FareSet per ProductVariant
+ common columns

Statuses: ACTIVATED, DEACTIVATED, ARCHIVED

Relations: One FareSet has many Fares.

FareRule

Conditional rule attached to a Fare. All rules on a Fare must pass for the Fare to apply. Rules are evaluated in priority order.

Column	Type	Constraints	Description
code	text	NOT NULL	Rule identifier (e.g., quantity, saleChannelId)
priority	integer	NOT NULL	Evaluation order (lower = first)
operator	text	NOT NULL	Comparison operator
fare_id	text	NOT NULL	FK to Fare
+ data-type columns			dataType, tValue, nValue, boValue, bValue, jValue
+ common columns

Operators (from FareRuleOperators):

Operator	Aliases	Description
eq		Equal
ne	neq	Not equal
gt		Greater than
gte		Greater than or equal
lt		Less than
lte		Less than or equal
in	inq	In array
nin		Not in array

The generateDataTypeColumnDefs() helper provides typed value storage:

• tValue (text) -- for string comparisons
• nValue (double precision) -- for numeric comparisons
• boValue (boolean) -- for boolean checks
• bValue (bytea) -- for binary data
• jValue (jsonb) -- for complex rules

Cost

Product variant cost tracking with time-based effective periods.

Column	Type	Constraints	Description
amount	decimal(15,4)	NOT NULL	Cost amount
note	text		Cost note
effective_from	isoTimestamp	NOT NULL	Period start (required)
effective_to	isoTimestamp		Period end (null = indefinite)
product_variant_id	text	NOT NULL	FK to ProductVariant
created_by	text		Creator user ID
modified_by	text		Last modifier user ID
+ common columns

Tax

Individual tax rate entries within a tax set.

Column	Type	Constraints	Description
name	jsonb (i18n)	NOT NULL	Internationalized name
amount	decimal(15,4)		Fixed tax amount
percentage	decimal(15,4)		Tax percentage rate
effective_from	isoTimestamp	NOT NULL	Period start
effective_to	isoTimestamp		Period end
priority	integer	DEFAULT 0	Evaluation order (highest first when multiple taxes apply)
status	text	NOT NULL, DEFAULT ACTIVATED	Tax status
tax_set_id	text	NOT NULL	FK to TaxSet
tax_type_id	text	NOT NULL	FK to TaxType
+ common columns

Statuses: ACTIVATED, DEACTIVATED, ARCHIVED

TaxSet

Tax configuration container using polymorphic ownership (can be linked to any entity).

Column	Type	Constraints	Description
principal_id	text		Polymorphic owner ID
principal_type	text		Polymorphic owner type
name	jsonb (i18n)		Internationalized name
status	text	NOT NULL, DEFAULT ACTIVATED	TaxSet status
+ common columns

Unique constraint: (principalId, principalType, status, deletedAt)

Statuses: ACTIVATED, DEACTIVATED, ARCHIVED

TaxType

Tax type definitions shared across merchants.

Column	Type	Constraints	Description
type	text	NOT NULL	Tax type code
name	jsonb (i18n)	NOT NULL	Internationalized name
description	jsonb (i18n)		Internationalized description
status	text	NOT NULL, DEFAULT ACTIVATED	TaxType status
merchant_id	text		FK to Merchant (null for global types)
+ common columns

Unique constraint: (type, merchantId)

Fixed Tax Types (from FixedTaxTypes):

Constant	Value	Description
VAT	000_VAT	Value-Added Tax
EXCISE	100_EXCISE5	Excise tax
ENVIRONMENTAL	200_ENVIRONMENTAL	Environmental tax
LUXURY	300_LUXURY	Luxury tax

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
• Allocation Schema -- 4 allocation models
• Sale Schema -- 2 sale order models
• Inventory Schema -- 8 inventory models
• Finance Schema -- 3 finance models
• Payment Schema -- 1 webhook config model
• Migrations -- DDL migrations and seed data framework