pricing — Phase 7

4 tables, 56 cols (revised: price_list_assignment +2 cols for assignment_scope + customer_group_id). Owns pre-defined pricing policy: levels, per-variant rules (fixed price, percent-off, amount-off), quantity breaks, date-windowed sales, and customer-level price assignments. PricingService resolves the correct price for a variant at checkout time; InventoryService owns the base price.

Section invariants

1. Base price source of truth = inventory.item_variant.base_price_cents. Pricing layers rules on top. price_change_log may record base-price changes for unified price history, but the value itself lives in Inventory.
2. Pricing owns predefined policy; POS/Orders own transaction-time discounts. price_type = 'percent_off' / 'amount_off' on price_rule means a predefined pricing policy (e.g. "20% off all perennials this weekend") — not a cashier override or coupon applied at checkout. Cashier overrides and coupon codes are POS/Orders concerns and live in those schemas.
3. Resolution precedence (PricingService logic — not DB-enforced). Given (variant, customer, qty, date, site):
   • (a) Candidate rules: tenant_id match + variant_id match + is_active = true + date window contains now (or window is open) + min_qty ≤ requested qty + (site_id matches OR site_id IS NULL).
   • (b) Specificity among rules: customer-scoped > sale (dated) > quantity break > level > variant base price. For level-assignment lookup: customer-specific assignment > customer-group assignment > is_default price level.
   • (c) Within same specificity: highest matching min_qty wins.
   • (d) Then site-specific rule (site_id NOT NULL) beats tenant-wide rule (site_id IS NULL).
   • (e) Then highest priority; then newest created_at.
4. v1.0 scope: tenant-wide pricing only (site_id always NULL in v1.0). Site-specific pricing is v1.5 (per Multi-Location Module forward decision #3 — schema must leave room for site-level rows; it does via nullable site_id).

Cross-Phase Foreign Keys (pricing)

Column	Target	Status
*.tenant_id	platform.tenant	Phase 1 exists — enforced.
price_rule.variant_id	inventory.item_variant	Phase 7 exists — enforced.
price_rule.site_id	multi_loc.site	Phase 4 exists — enforced (nullable).
price_rule.price_level_id	pricing.price_level	Intra-schema — enforced at Phase 7 migration.
price_rule.customer_id	crm.customer	CRM Phase 7 exists — enforced (nullable).
price_list_assignment.price_level_id	pricing.price_level	Intra-schema — enforced at Phase 7 migration.
price_list_assignment.customer_id	crm.customer	CRM Phase 7 exists — enforced (nullable).
price_list_assignment.customer_group_id	crm.customer_group	CRM Phase 7 exists — enforced (nullable).
price_change_log.price_rule_id	pricing.price_rule	Intra-schema — enforced at Phase 7 migration (nullable).
price_change_log.variant_id	inventory.item_variant	Phase 7 exists — enforced.
price_change_log.changed_by	identity.identity_user	Phase 3 exists — enforced (nullable).

---

pricing.price_level

Named pricing tier (e.g. "retail", "wholesale", "member"). Customers are assigned to a level via price_list_assignment; variants get per-level rules via price_rule. Exactly one level per tenant may be is_default — used when a customer has no explicit assignment.

Tenant-scoped. Master data — no site_id.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
code	text	NOT NULL	—	Short code, e.g. 'retail', 'wholesale', 'member'
name	text	NOT NULL	—	Display name
is_default	boolean	NOT NULL	false	Used when customer has no price-level assignment; at most one per tenant
is_active	boolean	NOT NULL	true
sort_order	integer	NOT NULL	0
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id)
• UNIQUE on (tenant_id, code) WHERE deleted_at IS NULL
• UNIQUE on (tenant_id) WHERE is_default = true AND deleted_at IS NULL — at most one default level per tenant

---

pricing.price_rule

Per-variant pricing rule — the core workhorse. Defines a price or discount for a specific variant, optionally scoped to a price level or customer, with optional quantity break, date window, and site scope.

Tenant-scoped. Master data — no site_id requirement on table; site_id column present for v1.5 site-specific pricing.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
variant_id	UUID	NOT NULL	—	FK → inventory.item_variant
site_id	UUID	nullable	—	FK → multi_loc.site — NULL = tenant-wide (all sites); v1.0 always NULL
scope_type	text	NOT NULL	—	CHECK IN ('level','customer') — determines which of price_level_id / customer_id is set
price_level_id	UUID	nullable	—	FK → pricing.price_level — set when scope_type = 'level'
customer_id	UUID	nullable	—	Forward-ref crm.customer (FK deferred to CRM lock) — set when scope_type = 'customer'
price_type	text	NOT NULL	'fixed'	CHECK IN ('fixed','percent_off','amount_off')
price_cents	bigint	nullable	—	Final price in cents. Set when price_type = 'fixed'; NULL otherwise
discount_value	numeric	nullable	—	Units depend on price_type. When 'percent_off': percentage (0–100); resolved price = ROUND(applicable_base_cents × (1 − discount_value / 100)). When 'amount_off': amount in cents (same unit as price_cents); resolved price = applicable_base_cents − discount_value (floored at 0). applicable_base = the price the rule discounts from per resolution precedence (level price or variant base). NULL when price_type = 'fixed'.
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
min_qty	numeric	NOT NULL	1	Quantity-break threshold; resolution uses highest matching min_qty ≤ requested qty
starts_at	timestamptz	nullable	—	Rule active from; NULL = no start bound
ends_at	timestamptz	nullable	—	Rule expires at; NULL = no end bound
rule_kind	text	NOT NULL	'standard'	CHECK IN ('standard','sale','scheduled') — label for UI / reporting; does not change resolution logic
name	text	nullable	—	Display label, e.g. 'Spring sale', 'Wholesale tray price'
priority	integer	NOT NULL	0	Tiebreaker within same specificity; higher wins
is_active	boolean	NOT NULL	true
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
(scope CHECK)	—	—	—	CHECK (scope_type = 'level' AND price_level_id IS NOT NULL AND customer_id IS NULL) OR (scope_type = 'customer' AND customer_id IS NOT NULL AND price_level_id IS NULL)
(price_type CHECK)	—	—	—	CHECK (price_type = 'fixed' AND price_cents IS NOT NULL AND discount_value IS NULL) OR (price_type IN ('percent_off','amount_off') AND discount_value IS NOT NULL AND price_cents IS NULL)
(value CHECKs)	—	—	—	CHECK price_cents IS NULL OR price_cents >= 0; CHECK discount_value IS NULL OR discount_value >= 0; CHECK min_qty > 0; CHECK priority >= 0
(date CHECK)	—	—	—	CHECK ends_at IS NULL OR starts_at IS NULL OR ends_at >= starts_at

Indexes:

• PK on id
• on (tenant_id)
• on (variant_id)
• on (price_level_id)
• on (customer_id)
• on (site_id)
• on (tenant_id, variant_id, is_active) — resolution query: all active rules for a variant
• on (ends_at) WHERE rule_kind = 'sale' — sale-expiry sweep

---

pricing.price_list_assignment

Assigns a customer or a customer group to a price level (with optional date window). assignment_scope determines which of customer_id / customer_group_id is set. Records the full assignment history — a new row is inserted when the assignment changes rather than updating the old row. At most one open-ended active assignment per customer (or per group) at a time (two partial uniques + service-layer overlap check).

Tenant-scoped. Master data — no site_id.

RLS: enabled — tenant isolation policy on tenant_id.

Resolution precedence among assignments (PricingService, not DB-enforced): customer-specific assignment > customer-group assignment > is_default price level.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
assignment_scope	text	NOT NULL	—	CHECK (assignment_scope IN ('customer','customer_group')) — determines which FK is set
customer_id	UUID	nullable	—	FK → crm.customer — set when assignment_scope = 'customer'
customer_group_id	UUID	nullable	—	FK → crm.customer_group — set when assignment_scope = 'customer_group'
price_level_id	UUID	NOT NULL	—	FK → pricing.price_level
starts_at	timestamptz	nullable	—	Assignment active from; NULL = immediate
ends_at	timestamptz	nullable	—	Assignment expires at; NULL = open-ended
is_active	boolean	NOT NULL	true
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
(scope CHECK)	—	—	—	CHECK (assignment_scope = 'customer' AND customer_id IS NOT NULL AND customer_group_id IS NULL) OR (assignment_scope = 'customer_group' AND customer_group_id IS NOT NULL AND customer_id IS NULL)
(date CHECK)	—	—	—	CHECK ends_at IS NULL OR starts_at IS NULL OR ends_at >= starts_at

Indexes:

• PK on id
• on (tenant_id)
• on (customer_id) — nullable; used for customer-scoped resolution
• on (customer_id, is_active) — PricingService customer-level resolution: WHERE customer_id = ? AND is_active = true
• on (customer_group_id) — nullable; used for group-scoped resolution
• on (price_level_id)
• UNIQUE on (tenant_id, customer_id) WHERE assignment_scope = 'customer' AND is_active = true AND ends_at IS NULL AND deleted_at IS NULL — at most one open-ended active assignment per customer
• UNIQUE on (tenant_id, customer_group_id) WHERE assignment_scope = 'customer_group' AND is_active = true AND ends_at IS NULL AND deleted_at IS NULL — at most one open-ended active assignment per group

Overlap prevention: Postgres cannot enforce non-overlapping date ranges without btree_gist / tsrange. PricingService checks for active overlapping assignments before insert. The two partial uniques guard only the open-ended case.

---

pricing.price_change_log

Immutable audit trail for pricing changes. Records creates, updates, deactivations, deletions of price_rule rows, and optionally base-price changes on inventory.item_variant (logged here for unified price history; the value itself lives in Inventory).

Tenant-scoped. Immutable — no updated_at, no deleted_at.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
price_rule_id	UUID	nullable	—	FK → pricing.price_rule — NULL for change_type = 'base_price_changed'
variant_id	UUID	NOT NULL	—	FK → inventory.item_variant
change_type	text	NOT NULL	—	CHECK IN ('created','updated','deleted','activated','deactivated','base_price_changed')
old_price_cents	bigint	nullable	—	Price before change; NULL for 'created' entries
new_price_cents	bigint	nullable	—	Price after change; NULL for 'deleted' / 'deactivated' entries
currency_code	char(3)	NOT NULL	—	ISO 4217
change_reason	text	nullable	—	Human-readable reason
source_type	text	nullable	—	CHECK IN ('manual','import','promotion','scheduled_job')
source_id	text	nullable	—	ID of the originating import job or promotion (text — cross-module, no enforced FK)
changed_by	UUID	nullable	—	FK → identity.identity_user — NULL for system-generated changes
created_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (tenant_id)
• on (price_rule_id)
• on (variant_id, created_at) — price history timeline for a variant

---

pricing — Design Notes

Column counts

Table	Cols
price_level	10
price_rule	21
price_list_assignment	12
price_change_log	13
Total	56

Note: _(table CHECK)_ pseudo-rows are constraint notation, not data columns. The 3 CHECK blocks on price_rule and 2 on price_list_assignment are counted as 0 columns each.

v1.0 scope vs. v1.5 deferrals

v1.0 (built now)	v1.5 (deferred)
Tenant-wide rules (site_id always NULL)	Site-specific rules (site_id set to a specific site)
Level + customer scope types	—
Fixed price, percent-off, amount-off types	Tiered / matrix pricing
Quantity breaks via min_qty	—
Date-windowed sales via starts_at / ends_at	—

Deferred notes (low-priority GAPs with written triggers)

Deferred item	Trigger for resolution
price_change_log.change_type — add 'expired'	When auto-expiry scheduler / sweep for date-windowed rules is built
price_change_log — add index on changed_by	When Audit module defines the "price changes by user" query

Forward-ref FK resolution

CRM is now built (Phase 7). The previously-deferred FK constraints on price_rule.customer_id and price_list_assignment.customer_id are now enforceable. Add in the Phase 7 pricing migration (after CRM tables exist):

ALTER TABLE pricing.price_rule
  ADD CONSTRAINT price_rule_customer_id_fkey
  FOREIGN KEY (customer_id) REFERENCES crm.customer(id);

ALTER TABLE pricing.price_list_assignment
  ADD CONSTRAINT price_list_assignment_customer_id_fkey
  FOREIGN KEY (customer_id) REFERENCES crm.customer(id);

ALTER TABLE pricing.price_list_assignment
  ADD CONSTRAINT price_list_assignment_customer_group_id_fkey
  FOREIGN KEY (customer_group_id) REFERENCES crm.customer_group(id);

---