pricing — Phase 14 (module #13, first module of the sell path)

4 tables, 81 columns (was 75 at 2026-07-06 lock; +3 from the 2026-07-07 reopen to 78; +3 more from the 2026-07-20 Gap-Fill Batch A1 reopen — see below) — schema-locked 2026-07-06. pricing is the pricing-rule engine sitting between inventory.item_variant.base_price_cents (the catalog's default price) and every future selling channel (POS, Orders): it decides what a specific variant actually costs for a specific customer, site, and quantity, and keeps a supersede-don't-edit history of every change. This build followed a two-stage outside-critique process — an independent gap analysis (3 cold-read critics + this agent's own pass) against standard commerce/pricing-engine patterns (Odoo/Shopify/Magento/NetSuite/Stripe) found 6 real gaps against the original 4-table design, all 6 resolved directly in schema this pass (not deferred), plus 3 non-schema gaps recorded as binding cross-module hard contracts. Depends on platform (tenant ownership), identity (actor FKs throughout), inventory (item_variant — the pricing target and, for cost_plus_percent, the live cost anchor), multi_loc (site — site-scoped rules), shared (currency — ISO-governed currency snapshot), and crm (customer/customer_group — customer-scoped rules and tier assignment).

PROJECT_DECISIONS entry: #26 (build), #28 (2026-07-07 reopen — 3 erosion fixes, see DR-17/DR-18 below), #74 (2026-07-20 Gap-Fill Batch A1 reopen — category/brand-scoped price rules, see below and module_spec/pricing.md DR-21).

Global rules for this schema:

• Uniform tenant-scoping — all 4 tables carry tenant_id NOT NULL FK → platform.tenant, no mixed-scope (nullable tenant_id) case anywhere in this module, unlike ai.ai_request. All 4 tables have RLS enabled with a permissive tenant-isolation policy named <table>_tenant_isolation, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid, matching the exact naming convention live-confirmed on item_variant/site/customer/agent_duty_grant. Plain index on tenant_id on all 4.
• updated_at is trigger-maintained via platform.set_updated_at() on 3 of the 4 tables (verified live: price_level, price_rule, price_list_assignment). The one exception, price_change_log, correctly has no updated_at column at all and no trigger — it is an append-only ledger, matching ai.agent_execution's own immutability precedent.
• Soft delete on 3 of 4 tables (price_level, price_rule, price_list_assignment all carry deleted_at timestamptz, nullable); no soft delete on price_change_log (append-only, lifecycle fully captured by the fact that a row was ever written).
• Agent-as-actor attribution, continuing the canonical pattern — every *_actor_id column (created_by_actor_id, updated_by_actor_id, reviewed_by_actor_id, changed_by_actor_id) targets identity.actor (the polymorphic root), never identity.identity_user directly, matching crm/inventory/ai's native pattern.
• Autonomy treatment is per-table, not uniform. price_level (a tiny, human-set-up-once reference catalog, directly analogous to crm.customer_group) gets no autonomy columns at all. price_rule and price_list_assignment get the full autonomy column set (actor attribution, automation_source, the full review seam, decision_provenance) — assigning a customer to a price tier is a plausible agent capability, directly analogous to crm.customer_segment_membership's own "agent-computed... wholesale-like classification" precedent. price_change_log gets automation_source only (cheap Reporting query surface) — never the full review seam, since the log itself is never "pending," only the row it describes is.
• v1 baseline vs. this build — a delta, not a from-scratch design. v1's locked docs/old/schema/schema_modules/schema_pricing.md (35-line rationale + 229-line schema doc) specified 4 tables / 56 cols for this module, and MODULE_INDEX.md's pre-existing pricing row ("4 tables / 56 cols") matches v1 exactly — not a stale placeholder mismatch like crm's v1 baseline was. This build's delta: table count unchanged at 4, +19 columns (56→75) — price_level 10→11, price_rule 21→33 (+12 gross, net +4 after removing is_active), price_list_assignment 12→19 (+7), price_change_log 13→12 (−1 net; change_type's enum values changed, not its column count — see that table's section).
• Money-type deviation, formally recorded (not left as informal prose). price_rule.price_value is a bare numeric, not this project's standard _cents bigint convention (item_variant.base_price_cents, agent_duty_grant.spend_limit_cents) — deliberate, since one column must hold both currency amounts (an integer count of the smallest unit) AND percentages (which need fractional precision, e.g. 12.5). This mirrors shared's own precedent for a documented, deliberate deviation from a project-wide default (PROJECT_DECISIONS #17, the natural-key-PK exception).
• Precedence resolution is PricingService logic, not a DB constraint — carried forward unchanged from v1: no DB constraint enforces which of several matching price_rule candidates wins; that is PricingService.resolvePrice()'s job (5-step precedence algorithm, documented in module_spec/pricing.md, not this schema doc).
• Base price source of truth stays inventory.item_variant.base_price_cents — pricing only layers rules on top, never duplicates it. When no price_rule matches, resolution falls through to the bare base price.
• Two rounds of independent design verification, real bugs found and fixed both rounds — see the dedicated section near the end of this doc for the full list, with specifics.

Cross-Phase Foreign Keys (pricing)

Column	Target	Notes
price_level.tenant_id, price_rule.tenant_id, price_list_assignment.tenant_id, price_change_log.tenant_id	platform.tenant	NOT NULL on all 4 tables — no mixed-scope case in this module.
price_rule.item_variant_id	inventory.item_variant.id	Nullable since the 2026-07-20 Gap-Fill Batch A1 reopen (was NOT NULL) — set only when item_scope_type='variant', see below. ON DELETE RESTRICT. The pricing target, and (for cost_plus_percent) the live cost anchor via item_variant.avg_cost_cents.
price_change_log.item_variant_id	inventory.item_variant.id	NOT NULL, unaffected by the Batch A1 reopen (the log always names a concrete variant).
price_rule.category_id	inventory.category.id	Added 2026-07-20 (Gap-Fill Batch A1). Nullable composite FK (category_id, tenant_id) → inventory.category (id, tenant_id), set only when item_scope_type='category'. Required a new prerequisite UNIQUE(id, tenant_id) on inventory.category (added in inventory's own migration for this same batch — see schema_docs/inventory.md, not restated here).
price_rule.brand_id	inventory.brand.id	Added 2026-07-20 (Gap-Fill Batch A1). Nullable composite FK (brand_id, tenant_id) → inventory.brand (id, tenant_id), set only when item_scope_type='brand'. inventory.brand is itself a brand-new table built in this same batch specifically to support this FK (id/tenant_id/name/created_by_actor_id/timestamps only — no slug, description, or hierarchy) — see schema_docs/inventory.md for its own definition, not restated here.
price_rule.site_id	multi_loc.site.id	Nullable, ON DELETE RESTRICT. NULL = tenant-wide for default/price_level scope; see applies_all_sites for the customer/customer_group case.
price_rule.price_level_id, price_list_assignment.price_level_id	pricing.price_level.id	Intra-schema. Nullable on price_rule (only set when scope_type='price_level'); NOT NULL on price_list_assignment, both ON DELETE RESTRICT.
price_rule.customer_id, price_list_assignment.customer_id	crm.customer.id	Nullable on both, ON DELETE RESTRICT.
price_rule.customer_group_id, price_list_assignment.customer_group_id	crm.customer_group.id	Nullable on both, ON DELETE RESTRICT.
price_rule.currency_code	shared.currency.iso_code	NOT NULL, ON DELETE RESTRICT. Populated from item_variant.currency_code at INSERT time and never updated thereafter — a point-in-time snapshot, not a live derivation, mirroring ai.agent_execution.authority_level_applied's own snapshot-not-live-reference pattern.
price_rule.superseded_by_id	pricing.price_rule.id (self)	Nullable self-FK. The supersede-don't-edit mechanism — see dedicated section below. UNIQUE when set.
price_rule.created_by_actor_id, price_rule.updated_by_actor_id, price_rule.reviewed_by_actor_id, price_list_assignment.created_by_actor_id, price_list_assignment.updated_by_actor_id, price_list_assignment.reviewed_by_actor_id, price_change_log.changed_by_actor_id	identity.actor	Nullable throughout. Cross-schema, enforced.
price_change_log.price_rule_id	pricing.price_rule.id	Nullable — NULL if this log row is about an item_variant.base_price_cents change instead (change_type='base_price_changed'), not a price_rule field.

---

pricing.price_level (11 cols)

Named pricing tier (retail/wholesale/member) — a human-curated, rarely-changing reference catalog, directly analogous to crm.customer_group's own established precedent: no autonomy columns at all, since this is tiny, human-set-up-once reference data, never a target of AI mutation.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy price_level_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid.

Soft delete: deleted_at timestamptz, nullable.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
code	text	NOT NULL	—	Unique per tenant while not deleted — see indexes
name	text	NOT NULL	—
description	text	nullable	—
is_default	boolean	NOT NULL	false	Exactly one true per tenant — partial unique, mirrors multi_loc.site.is_primary's own pattern exactly
is_active	boolean	NOT NULL	true
sort_order	integer	NOT NULL	0
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete

No CHECK constraints on this table (verified live — none defined; validity is fully structural via the two partial-unique indexes and NOT NULL defaults).

Indexes:

• PK on id
• Plain index on tenant_id
• price_level_tenant_id_code_unique — UNIQUE, btree (tenant_id, code) WHERE deleted_at IS NULL
• price_level_tenant_id_default_unique — UNIQUE, btree (tenant_id) WHERE is_default = true AND deleted_at IS NULL — mirrors multi_loc.site's own is_primary pattern exactly, guaranteeing at most one default price level per tenant

---

pricing.price_rule (38 cols, was 35) — the workhorse

Per-variant pricing rule (fixed price, percent off, amount off, or cost-plus markup), optionally scoped to a price level, a customer, or a customer group, with an optional quantity break, a date window, and site scope. Since the 2026-07-20 Gap-Fill Batch A1 reopen, a rule can also target an entire inventory.category or inventory.brand — or every item tenant-wide — instead of a single item_variant, via the new item_scope_type column (see dedicated section below). Full autonomy treatment: actor attribution → identity.actor (never identity.identity_user), automation_source, and the full human-in-the-loop review seam (review_status/review_reason/reviewed_by_actor_id/reviewed_at) — a pending row has zero live effect on resolution, which alone satisfies the draft-only autonomy boundary without needing a separate proposal table.

The supersede-don't-edit mechanism (closes the outside critique's #1 finding — price mutability with no history). price_value, price_type, and currency_code are now never mutated in place. A price change = a brand-new row (fresh id, the new values, status='active') + the OLD row's status flipped to 'superseded' with superseded_by_id pointing at the new row's id — the old row's price fields themselves are never touched, only its lifecycle-tracking columns. This is what replaced v1's is_active boolean toggle (now removed).

review_status and status are two orthogonal state machines that coexist on the same row: review_status governs whether a human has approved a row for resolution (independent of price history); status governs whether THIS row is the current version of a price or has been replaced/aged out. A newly-created draft naturally starts status='active' + review_status='pending' — these are independent dimensions, not a combined lifecycle. status='expired' is an optional, sweep-driven bookkeeping state (a scheduled job MAY flip a row past its valid_until to expired) — resolution correctness never depends on the sweep having run, since PricingService.resolvePrice() always filters on the valid_from/valid_until window directly regardless of status. Reversing a bad change is either (a) soft-delete via deleted_at (pulled, no replacement), or (b) supersede with a corrected row (the normal price-change mechanism itself) — is_active toggling no longer exists as a third option.

item_scope_type — a third orthogonal state machine, added 2026-07-20 (Gap-Fill Batch A1). A new WHAT-axis discriminator (variant/category/brand/all) sitting alongside scope_type (the pre-existing WHO-axis: default/price_level/customer/customer_group) and status/review_status (the lifecycle/approval axes above) — a 3rd independent dimension on the same row, not a variant of any of the other two. item_variant_id was relaxed nullable to accommodate it: it is now set only when item_scope_type='variant' (the pre-existing, still-default behavior), with category_id/brand_id taking its place for the two new scope kinds and all three left NULL for item_scope_type='all'. See module_spec/pricing.md DR-21 for why this is a distinct column rather than an overload of scope_type (a real naming collision caught before the migration was written), and for why cross-scope resolution precedence (variant > category > brand > all) is a documented PricingService rule, not a DB constraint — the same treatment already given to scope_type's own precedence question.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy price_rule_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid.

Soft delete: deleted_at timestamptz, nullable.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
item_variant_id	UUID	nullable (was NOT NULL)	—	FK → inventory.item_variant, ON DELETE RESTRICT. Relaxed nullable 2026-07-20 (Gap-Fill Batch A1) — set only when item_scope_type='variant', enforced via chk_price_rule_item_scope_fk_consistency
item_scope_type	text	NOT NULL	'variant'	Added 2026-07-20 (Gap-Fill Batch A1). CHECK IN (variant,category,brand,all) — the WHAT-axis (which item(s) a rule prices), orthogonal to scope_type's WHO-axis. See dedicated section above
category_id	UUID	nullable	—	Added 2026-07-20 (Gap-Fill Batch A1). Composite FK (category_id,tenant_id) → inventory.category (id, tenant_id). Set only when item_scope_type='category'
brand_id	UUID	nullable	—	Added 2026-07-20 (Gap-Fill Batch A1). Composite FK (brand_id,tenant_id) → inventory.brand (id, tenant_id). Set only when item_scope_type='brand'
site_id	UUID	nullable	—	FK → multi_loc.site, ON DELETE RESTRICT. NULL = tenant-wide for default/price_level scope; see applies_all_sites for customer/customer_group scope
applies_all_sites	boolean	NOT NULL	false	Closes gap #2 (site-scoping/privacy blast-radius) — see dedicated section below
scope_type	text	NOT NULL	—	CHECK IN (default,price_level,customer,customer_group) — enforced via chk_price_rule_scope_fk_consistency, not a standalone CHECK on this column alone. Orthogonal to item_scope_type above — the two discriminators never collide, see module_spec/pricing.md DR-21
price_level_id	UUID	nullable	—	FK → pricing.price_level, ON DELETE RESTRICT. Set only when scope_type='price_level'
customer_id	UUID	nullable	—	FK → crm.customer, ON DELETE RESTRICT. Set only when scope_type='customer'
customer_group_id	UUID	nullable	—	FK → crm.customer_group, ON DELETE RESTRICT. Set only when scope_type='customer_group'
currency_code	char(3)	NOT NULL	—	FK → shared.currency.iso_code, ON DELETE RESTRICT. Point-in-time snapshot from item_variant.currency_code at INSERT time, never updated thereafter
price_type	text	NOT NULL	—	CHECK IN (fixed_price,percent_off,amount_off,cost_plus_percent) — the 4th value, cost_plus_percent, closes gap #5
price_value	numeric	NOT NULL	—	Deliberate deviation from the _cents bigint convention — see Global rules above. Meaning depends on price_type, bounded by chk_price_rule_price_value
tax_treatment	text	NOT NULL	'exclusive'	CHECK IN (inclusive,exclusive) — closes gap #4, see dedicated section below
min_qty	integer	nullable	—	Quantity break threshold. CHECK: min_qty IS NULL OR min_qty > 0
valid_from	timestamptz	nullable	—	Date window start
valid_until	timestamptz	nullable	—	Date window end
priority	integer	NOT NULL	0	Tiebreak among equally-specific candidate rules
status	text	NOT NULL	'active'	CHECK IN (active,superseded,expired) — replaces v1's is_active boolean, see mechanism above
superseded_by_id	UUID	nullable	—	Self-FK → pricing.price_rule.id. UNIQUE when set. See supersede-don't-edit mechanism above
rule_kind	text	NOT NULL	'standard'	Added 2026-07-07 (DR-17) — restores a v1 erosion. CHECK IN (standard,sale,scheduled), classifies a rule's TYPE independent of its date window; backs price_rule_sale_expiry_idx below
name	text	nullable	—	Added 2026-07-07 (DR-17) — restores a v1 erosion. Human display label (e.g. "Wholesale tray price"); campaign_label/reason are not substitutes for it
campaign_label	text	nullable	—	Lightweight grouping tag — closes gap #6, see dedicated section below
reason	text	nullable	—	Especially important for markdowns
idempotency_key	text	nullable	—	UNIQUE per (tenant, creating actor) when set — see indexes
created_by_actor_id	UUID	nullable	—	FK → identity.actor
updated_by_actor_id	UUID	nullable	—	FK → identity.actor
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
review_status	text	NOT NULL	'not_required'	CHECK IN (not_required,pending,approved,rejected)
review_reason	text	nullable	—
reviewed_by_actor_id	UUID	nullable	—	FK → identity.actor. Cannot equal created_by_actor_id when both set — see chk_price_rule_reviewer_not_creator
reviewed_at	timestamptz	nullable	—
decision_provenance	jsonb	nullable	—	Cites stock.last_movement_at as evidence for markdown proposals; extended with memory_refs/delegated_by_actor_id keys per ai.agent_memory
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete

CHECK constraints (19 on this table as of the 2026-07-20 Gap-Fill Batch A1 reopen, 26 module-total — was 17/24 before this reopen added chk_price_rule_item_scope_type/chk_price_rule_item_scope_fk_consistency; was 16/23 before the 2026-07-08 Remediation Phase 1 pass added chk_price_rule_approved_requires_reviewer; was 15/22 before the 2026-07-07 reopen added chk_price_rule_rule_kind):

Name	Condition
chk_price_rule_price_type	price_type IN ('fixed_price','percent_off','amount_off','cost_plus_percent')
chk_price_rule_review_status	review_status IN ('not_required','pending','approved','rejected')
chk_price_rule_automation_source	automation_source IN ('human','agent','system','seed')
chk_price_rule_status	status IN ('active','superseded','expired')
chk_price_rule_rule_kind	rule_kind IN ('standard','sale','scheduled') — added 2026-07-07 (DR-17)
chk_price_rule_tax_treatment	tax_treatment IN ('inclusive','exclusive')
chk_price_rule_price_value	(price_type='fixed_price' AND price_value>=0) OR (price_type='percent_off' AND price_value>0 AND price_value<=100) OR (price_type='amount_off' AND price_value>0) OR (price_type='cost_plus_percent' AND price_value>=-100 AND price_value<=1000) — see cost_plus_percent bound rationale below
chk_price_rule_price_value_integer_for_currency_amounts	price_type IN ('percent_off','cost_plus_percent') OR price_value = floor(price_value) — see currency-integer convention below
chk_price_rule_valid_window	valid_until IS NULL OR valid_from IS NULL OR valid_until >= valid_from
chk_price_rule_min_qty	min_qty IS NULL OR min_qty > 0
chk_price_rule_superseded_by_consistency	(status='superseded' AND superseded_by_id IS NOT NULL) OR (status != 'superseded' AND superseded_by_id IS NULL)
chk_price_rule_no_self_supersede	superseded_by_id IS NULL OR superseded_by_id != id
chk_price_rule_scope_fk_consistency	4-branch CHECK, one FK-set combination per scope_type value — see below
chk_price_rule_customer_scope_site_explicit	scope_type NOT IN ('customer','customer_group') OR site_id IS NOT NULL OR applies_all_sites = true
chk_price_rule_applies_all_sites_xor_site_id	applies_all_sites = false OR site_id IS NULL
chk_price_rule_reviewer_not_creator	reviewed_by_actor_id IS NULL OR created_by_actor_id IS NULL OR reviewed_by_actor_id != created_by_actor_id
chk_price_rule_approved_requires_reviewer	review_status != 'approved' OR reviewed_by_actor_id IS NOT NULL — added 2026-07-08, Remediation Phase 1 (see PROJECT_DECISIONS #37)
chk_price_rule_item_scope_type	item_scope_type IN ('variant','category','brand','all') — added 2026-07-20, Gap-Fill Batch A1 (see PROJECT_DECISIONS #74)
chk_price_rule_item_scope_fk_consistency	4-branch CHECK, exactly one of item_variant_id/category_id/brand_id set per item_scope_type value (variant→item_variant_id only; category→category_id only; brand→brand_id only; all→none set) — added 2026-07-20, Gap-Fill Batch A1 (see PROJECT_DECISIONS #74)

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on item_variant_id
• Partial index on review_status WHERE = 'pending'
• price_rule_resolve_price_idx — composite, btree (tenant_id, item_variant_id, site_id) WHERE status='active' AND review_status IN ('not_required','approved') AND deleted_at IS NULL — the hot-path resolution index, supporting PricingService.resolvePrice()'s actual query shape, this module's single hottest read path (every POS/Orders line-item price resolution)
• price_rule_sale_expiry_idx — btree (valid_until) WHERE rule_kind='sale' AND status='active' AND deleted_at IS NULL — added 2026-07-07 (DR-17), restores v1's sale-expiry sweep support
• price_rule_pending_scope_dedup_unique — UNIQUE, on (tenant_id, item_variant_id, scope_type, price_type, COALESCE(site_id, '00000000-0000-0000-0000-000000000000'::uuid), COALESCE(price_level_id, ...), COALESCE(customer_id, ...), COALESCE(customer_group_id, ...), COALESCE(min_qty, 0)) WHERE review_status='pending' AND status='active' AND deleted_at IS NULL — see dedup-index section below
• price_rule_tenant_actor_idempotency_unique — UNIQUE, btree (tenant_id, created_by_actor_id, idempotency_key) WHERE created_by_actor_id IS NOT NULL AND idempotency_key IS NOT NULL AND deleted_at IS NULL
• price_rule_tenant_null_actor_idempotency_unique — UNIQUE, btree (tenant_id, idempotency_key) WHERE created_by_actor_id IS NULL AND idempotency_key IS NOT NULL AND deleted_at IS NULL — post-build adversarial-verification fix: created_by_actor_id is nullable (system/import/unattributed callers), and the original single unique on (tenant_id, created_by_actor_id, idempotency_key) let two NULL-actor rows share the same idempotency_key — Postgres treats NULL as distinct, so both inserted successfully (live-reproduced), silently defeating idempotency for any unattributed caller. Mirrors ai.ai_request's own NULL-tenant fix. Live-tested: two NULL-actor rows sharing an idempotency_key are now rejected with duplicate key value violates unique constraint "price_rule_tenant_null_actor_idempotency_unique". The identical fix was applied to price_list_assignment_tenant_actor_idempotency_unique/price_list_assignment_tenant_null_actor_idempotency_unique.
• price_rule_superseded_by_id_unique — UNIQUE, btree (superseded_by_id) WHERE superseded_by_id IS NOT NULL — a successor row can only be claimed by exactly one predecessor, mirrors ai.agent_execution.resolves_execution_id's own uniqueness precedent
• price_rule_category_id_idx — partial index on category_id WHERE NOT NULL — added 2026-07-20, Gap-Fill Batch A1
• price_rule_brand_id_idx — partial index on brand_id WHERE NOT NULL — added 2026-07-20, Gap-Fill Batch A1

---

pricing.price_list_assignment (20 cols, was 19)

Assigns a customer OR a customer group to a price level, with a date window. Full autonomy treatment too (unlike the static price_level catalog) — assigning a customer to a price tier is a plausible agent capability, directly analogous to crm.customer_segment_membership's own "agent-computed... wholesale-like classification" precedent, so it gets the identical column set price_rule gets.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy price_list_assignment_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid.

Soft delete: deleted_at timestamptz, nullable.

updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
price_level_id	UUID	NOT NULL	—	FK → pricing.price_level, ON DELETE RESTRICT
customer_id	UUID	nullable	—	FK → crm.customer, ON DELETE RESTRICT. Exactly one of customer_id/customer_group_id set — see chk_price_list_assignment_customer_xor_group
customer_group_id	UUID	nullable	—	FK → crm.customer_group, ON DELETE RESTRICT
valid_from	timestamptz	NOT NULL	now()
valid_until	timestamptz	nullable	—
is_active	boolean	NOT NULL	true	Added 2026-07-07 (DR-18) — restores a v1 erosion. Backs the at-most-one-open-ended-active-assignment guarantee below
idempotency_key	text	nullable	—	UNIQUE per (tenant, creating actor) when set
created_by_actor_id	UUID	nullable	—	FK → identity.actor
updated_by_actor_id	UUID	nullable	—	FK → identity.actor
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
review_status	text	NOT NULL	'not_required'	CHECK IN (not_required,pending,approved,rejected)
review_reason	text	nullable	—
reviewed_by_actor_id	UUID	nullable	—	FK → identity.actor
reviewed_at	timestamptz	nullable	—
decision_provenance	jsonb	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete

CHECK constraints (verified live):

Name	Condition
chk_price_list_assignment_customer_xor_group	(customer_id IS NOT NULL AND customer_group_id IS NULL) OR (customer_id IS NULL AND customer_group_id IS NOT NULL)
chk_price_list_assignment_valid_window	valid_until IS NULL OR valid_from IS NULL OR valid_until >= valid_from
chk_price_list_assignment_automation_source	automation_source IN ('human','agent','system','seed')
chk_price_list_assignment_review_status	review_status IN ('not_required','pending','approved','rejected')
chk_price_list_assignment_approved_requires_reviewer	review_status != 'approved' OR reviewed_by_actor_id IS NOT NULL — added 2026-07-08, Remediation Phase 1 (see PROJECT_DECISIONS #37)

Indexes:

• PK on id
• Plain index on tenant_id
• Partial index on customer_id WHERE NOT NULL
• Partial index on customer_group_id WHERE NOT NULL — both needed, since resolvePrice() must find a customer's OR a group's assigned price level
• price_list_assignment_tenant_actor_idempotency_unique — UNIQUE, btree (tenant_id, created_by_actor_id, idempotency_key) WHERE created_by_actor_id IS NOT NULL AND idempotency_key IS NOT NULL AND deleted_at IS NULL — grain matches price_rule's own idempotency-key fix (see below)
• price_list_assignment_tenant_null_actor_idempotency_unique — UNIQUE, btree (tenant_id, idempotency_key) WHERE created_by_actor_id IS NULL AND idempotency_key IS NOT NULL AND deleted_at IS NULL — same post-build NULL-actor fix as price_rule's own (see above)
• price_list_assignment_customer_open_ended_active_unique — UNIQUE, btree (customer_id) WHERE customer_id IS NOT NULL AND is_active = true AND valid_until IS NULL AND deleted_at IS NULL — added 2026-07-07 (DR-18), restores v1's at-most-one-open-ended-active-assignment guarantee
• price_list_assignment_group_open_ended_active_unique — UNIQUE, btree (customer_group_id) WHERE customer_group_id IS NOT NULL AND is_active = true AND valid_until IS NULL AND deleted_at IS NULL — same guarantee, group side

Known, deliberately-deferred gap: no overlap-prevention across time-windowed assignments for the same customer/group (would need a btree_gist EXCLUDE constraint on a date range — a bigger schema commitment v1 never had either). Logged to OPEN_ITEMS rather than solved now.

---

pricing.price_change_log (12 cols) — append-only

Immutable audit trail of price_rule lifecycle events (and item_variant.base_price_cents changes). Matches ai.agent_execution's own append-only shape — no updated_at, no deleted_at, no trigger. Gets automation_source only (cheap Reporting query surface — "how many price changes were agent-initiated") — not the full review seam, since the log itself is never "pending," only the row it describes is.

Tenant-scoped, append-only. tenant_id NOT NULL FK → platform.tenant. No updated_at, no deleted_at. RLS enabled — permissive policy price_change_log_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
price_rule_id	UUID	nullable	—	FK → pricing.price_rule. NULL if this log row is about an item_variant.base_price_cents change instead (change_type='base_price_changed')
item_variant_id	UUID	NOT NULL	—	FK → inventory.item_variant
change_type	text	NOT NULL	—	CHECK IN (rule_created,rule_updated,rule_superseded,rule_deleted,rule_expired,base_price_changed) — re-derived for the supersede-don't-edit model, see below
old_value	jsonb	nullable	—	See example shapes below
new_value	jsonb	nullable	—	See example shapes below
source_type	text	NOT NULL	—	CHECK IN (manual,import,promotion,scheduled_job,agent) — 'agent' is a v1 deviation, absent in v1's enum
changed_by_actor_id	UUID	nullable	—	FK → identity.actor. Retargeted from v1's changed_by → identity.identity_user
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
reason	text	nullable	—
created_at	timestamptz	NOT NULL	now()

No updated_at, no deleted_at — append-only; a change-log entry is a historical fact, never edited or removed.

CHECK constraints (verified live):

Name	Condition
chk_price_change_log_change_type	change_type IN ('rule_created','rule_updated','rule_superseded','rule_deleted','rule_expired','base_price_changed')
chk_price_change_log_source_type	source_type IN ('manual','import','promotion','scheduled_job','agent')
chk_price_change_log_automation_source	automation_source IN ('human','agent','system','seed')

change_type re-derivation for the supersede-don't-edit model. With is_active gone and price fields never mutated in place, v1's rule_reactivated no longer corresponds to any real transition (there is no "un-supersede"/"un-expire" operation — bringing a price back is naturally a fresh rule_created row, or a rule_superseded event replacing an expired/deleted one), and rule_deactivated is redundant with rule_deleted now that is_active is gone (both describe "pulled, no replacement" — collapsed into one). A new value, rule_superseded, is added for the specific "this row was replaced by a successor" event (previously conflated with rule_updated, which now means only non-price-field edits — reason, priority, campaign_label, or a review_status transition like approval).

old_value/new_value example shapes (one per structurally distinct event):

• rule_created: old_value=NULL, new_value={"price_type":"percent_off","price_value":15,"status":"active","currency_code":"USD"}.
• rule_superseded: old_value={"price_type":"percent_off","price_value":15,"status":"active"}, new_value={"price_type":"percent_off","price_value":20,"status":"active","superseded_row_id":"<old row's id, for convenience>"}.
• base_price_changed: old_value={"base_price_cents":2999}, new_value={"base_price_cents":2499} (about item_variant.base_price_cents, not a price_rule field at all — price_rule_id is NULL for this change_type).

Indexes:

• PK on id
• Plain index on tenant_id
• Partial index on price_rule_id WHERE NOT NULL
• Plain index on item_variant_id

---

Triggers

trg_price_rule_validate_supersession — the one genuinely new trigger function this module introduces (all other trigger behavior in this schema is the shared platform.set_updated_at() function reused unchanged).

Fires BEFORE INSERT OR UPDATE OF superseded_by_id ON pricing.price_rule, calling pricing.validate_price_rule_supersession():

CREATE OR REPLACE FUNCTION pricing.validate_price_rule_supersession()
RETURNS trigger AS $$
BEGIN
  IF NEW.superseded_by_id IS NOT NULL THEN
    IF NOT EXISTS (
      SELECT 1 FROM pricing.price_rule
      WHERE id = NEW.superseded_by_id
        AND tenant_id = NEW.tenant_id
        AND item_variant_id = NEW.item_variant_id
        AND scope_type = NEW.scope_type
    ) THEN
      RAISE EXCEPTION 'price_rule.superseded_by_id must reference a row with the same tenant_id, item_variant_id, and scope_type';
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_price_rule_validate_supersession
  BEFORE INSERT OR UPDATE OF superseded_by_id ON pricing.price_rule
  FOR EACH ROW EXECUTE FUNCTION pricing.validate_price_rule_supersession();

Why a trigger, not a CHECK. A same-row CHECK cannot express this — it requires comparing the row being written against a different row (the target of superseded_by_id). Without this guard, a bare self-FK with no composite constraint tying the target's tenant_id/item_variant_id/scope_type to the row being replaced is exploitable via an ordinary application bug (not just malice): a cross-tenant supersession would let an audit/history JOIN leak another tenant's pricing data — precisely the class of leak this module's own RLS otherwise exists to prevent. Unlike every other cross-table concern this build deferred to documentation (given a "build thin" discipline for not-yet-real callers), this one gets a real trigger because the risk is a live RLS-adjacent data leak, not a hypothetical caller. Live-tested: a same-tenant/variant/scope supersession succeeds; a cross-tenant supersession attempt is rejected by this trigger.

set_updated_at (via platform.set_updated_at()) fires BEFORE UPDATE on 3 of the 4 tables (verified live: price_level, price_rule, price_list_assignment). The 1 table without it: price_change_log (no updated_at column at all — append-only ledger).

---

pricing — Design Patterns Summary

Column-count reconciliation

Table	Cols
price_level	11
price_rule	33
price_list_assignment	19
price_change_log	12
Total	75

Verified live via information_schema.columns GROUP BY, schema pricing: 4 tables, 75 columns, matching the per-table sum exactly. 22 CHECK constraints total (verified live via pg_constraint), RLS enabled on all 4 tables (verified live via pg_tables.rowsecurity = true), 4 triggers total (set_updated_at × 3 + trg_price_rule_validate_supersession × 1).

(Note: this table reflects the original 2026-07-06 lock snapshot. Subsequent reopens changed the live count — the 2026-07-07 reopen (DR-17/DR-18 above) added 3 columns (75→78); Remediation Phase 1 below added 2 more CHECK constraints (24 total) with no column-count change; the 2026-07-20 Gap-Fill Batch A1 reopen (see its own subsection below) added 3 more columns to price_rule (78→81) plus 2 more CHECK constraints (26 total). Current live totals: 4 tables, 81 columns, 26 CHECK constraints — see that subsection for detail.)

Remediation Phase 1 (2026-07-08)

A cross-cutting Remediation Plan Phase 1 pass (closing gaps a senior-architect review found) added one CHECK constraint each to price_rule and price_list_assignment, same shape as the equivalent billing/inventory instances added in the same pass:

• pricing.price_rule.chk_price_rule_approved_requires_reviewer
• pricing.price_list_assignment.chk_price_list_assignment_approved_requires_reviewer

Both enforce that a row cannot be review_status='approved' without a reviewed_by_actor_id set — closing an approval-with-no-reviewer gap. No column or table count change: 4 tables, 78 columns unchanged. Total CHECK constraints for this module: 24 (22 at 2026-07-06/07 lock + 2 from this pass). See PROJECT_DECISIONS #37 for the full cross-module record.

Gap-Fill Batch A1 (2026-07-20) — category/brand-scoped price rules

A cross-module Gap-Fill Batch pass (closing 6 gaps the 2026-07-19 gap-validation report confirmed against 6 modules) reopened pricing for A1: price_rule.item_variant_id relaxed nullable, plus 3 new columns — item_scope_type (text, NOT NULL DEFAULT 'variant'), category_id (nullable composite FK → inventory.category (id, tenant_id)), brand_id (nullable composite FK → inventory.brand (id, tenant_id)) — and 2 new CHECK constraints, chk_price_rule_item_scope_type and chk_price_rule_item_scope_fk_consistency (both documented in price_rule's own column/CHECK tables above). A pre-migration collision check found scope_type — the task's own originally-proposed column name — already in use for an unrelated axis; resolved by architect ruling to the distinct name item_scope_type (full rationale in module_spec/pricing.md DR-21, not restated here).

Two prerequisite dependencies on inventory, cross-referenced not restated: inventory.category gained a new UNIQUE(id, tenant_id) in this same batch (required for price_rule.category_id's composite FK) — see schema_docs/inventory.md for that table's own definition. inventory.brand is a brand-new table built in this same batch specifically to support price_rule.brand_id's FK (id/tenant_id/name/created_by_actor_id/timestamps only, no slug/description/hierarchy — nothing beyond this FK consumes it yet) — see schema_docs/inventory.md for its own definition.

No table-count change; price_rule 35→38 cols, module total 78→81 cols. Resolution precedence across item-scope types (variant > category > brand > all) is documented as a future PricingService rule, not schema-enforced — see module_spec/pricing.md DR-21. See PROJECT_DECISIONS #74 for the full cross-module record (5 modules reopened in the same batch: pricing, inventory, pos, receiving, offers, orders).

Remediation Phase 2 (2026-07-08)

A cross-cutting Remediation Plan Phase 2 pass changed pricing.price_change_log.id's DEFAULT from gen_random_uuid() to platform.uuid_generate_v7() (Remediation Phase 2, Item 6). price_change_log is this module's append-only event log; UUIDv7 is time-ordered (unlike the random UUIDv4 gen_random_uuid() produces), which keeps future time-range partitioning possible on this ledger without a PK rewrite — a rewrite that becomes impossible once data has already landed on a random UUIDv4 PK. DEFAULT-only change: no column added, no column dropped, no table count change. 4 tables, 78 columns unchanged. See PROJECT_DECISIONS #38 for the full cross-module record.

v1 baseline vs. this build — a delta, not a from-scratch design

v1's locked docs/old/schema/schema_modules/schema_pricing.md (229 lines) + docs/old/design_rationale/rationale_pricing.md (35 lines) specified 4 tables / 56 cols for this module: price_level (10), price_rule (21), price_list_assignment (12), price_change_log (13). This is numerically exact, not a stale/placeholder mismatch like crm's v1 baseline was — but it is still a pre-v2 count: no currency FK (raw char(3) default 'USD', since shared.currency didn't exist at design time), no agent/AI concept anywhere (price_change_log.source_type's CHECK had no 'agent' value; changed_by FK'd identity.identity_user directly, not an actor-polymorphic root), and v1.0 was explicitly tenant-wide only (site_id always NULL, deferred to v1.5 per multi_loc's own forward decision #3 — multi_loc is now locked, so this build makes site-scoping real). This build's delta: table count unchanged at 4, +19 columns (56→75).

v1's own governing decisions carried forward as-is: Pricing owns predefined policy; POS/Orders own transaction-time discounts (cashier overrides, coupons) — an explicit boundary, not touched here. One price_rule table absorbs all pricing kinds via discriminator columns, not per-kind tables. Precedence resolution is PricingService logic, never a DB constraint. Base price source of truth stays inventory.item_variant.base_price_cents.

The outside-critique-to-resolution narrative

Stage 1 — the gap analysis. An outside-expert gap analysis compared the original 4-table pricing design against standard commerce/pricing-engine patterns (Odoo/Shopify/Magento/NetSuite/Stripe), producing 3 independent cold-read critiques plus this agent's own analysis. Real gaps found: no rounding/tax-inclusive/currency-precision handling; no cost-plus pricing; no promotion-grouping identifier; a mutable price_rule vs. append-only-by-convention price_list_assignment inconsistency (the top finding, cited by all 3 independent critics); and a site-scoping/privacy blast-radius risk for Flutter POS sync of customer-specific rules.

Stage 2 — resolution. All 6 schema-fixable gaps were resolved directly in schema (not deferred), plus 3 non-schema gaps were recorded as binding hard contracts (not vague deferrals) — see the Hard Contracts section below. The 6 resolved gaps, and what each closes:

1. Supersede-don't-edit (status/superseded_by_id) — closes "what was the price on date X," the #1 finding cited by all 3 independent critics. price_value/price_type/currency_code are never mutated in place again.
2. applies_all_sites — closes a privacy/blast-radius gap: a customer/customer_group-scoped rule with site_id=NULL previously meant "silently visible/synced everywhere," including sites a customer may never visit — a real risk for a lost/stolen Flutter POS tablet carrying readable negotiated pricing for unrelated customers.
3. chk_price_rule_price_value_integer_for_currency_amounts — closes the currency-precision gap: fixed_price/amount_off amounts must be a whole-number integer count of the currency's smallest unit, matching item_variant.base_price_cents's convention uniformly across currencies without needing shared.currency.decimal_places at the CHECK level.
4. tax_treatment — closes a "global from day 1" gap (EU/UK/AU are tax-inclusive by convention, US is tax-exclusive) that had zero schema seam anywhere before this pass.
5. price_type='cost_plus_percent' — closes the gap where the original design claimed "margin-erosion detection reuses price_rule in reverse" without having any schema path to actually express it. Resolves live against item_variant.avg_cost_cents, never cached.
6. campaign_label — closes the narrower gap of grouping related price changes for reporting, deliberately NOT building the larger promotion/campaign header object (usage caps, coupon codes, stacking rules) the critique separately flagged — that stays out of scope, logged to OPEN_ITEMS.

Verification history — two full rounds, real bugs found and fixed both times. Design-phase verification ran twice: once on the original 4-table proposal, once again on the round-2 expanded design after the 6 gaps were folded in — each round via an independent 3-agent Workflow (a Section 4 audit + adversarial verification + a third specialized check). Specific bugs found and fixed:

• The scope_fk_consistency CHECK ambiguity bug. The first draft's prose implied "each branch pins only its own FK," which the Section 4 auditor live-tested and found let a scope_type='price_level' row ALSO set customer_id — a leaked second FK, live-confirmed as a real bug (INSERT 0 1 succeeded when it should have failed). Fixed by writing chk_price_rule_scope_fk_consistency as fully explicit SQL pinning ALL THREE optional FKs (price_level_id/customer_id/customer_group_id) in every one of the 4 branches, not just the FK for that branch's own scope. Live-verified afterward: correctly rejects a customer-scoped row that also sets customer_group_id; correctly rejects default-scoped rows with any FK set; does NOT forbid customer_id + site_id together (a legitimate combination — a customer-specific rule can still be site-specific).
• The min_qty-missing-from-dedup-index bug. The pending-scope dedup partial-unique index originally omitted min_qty from its key, so a legitimate 3-tier quantity-break ladder (e.g. min_qty=10, 25, 50, all pending, same variant/scope) would collapse to one dedup key and the 2nd/3rd rows would be falsely rejected as duplicates. Fixed by adding COALESCE(min_qty, 0) to the index key. A related, separately-found bug in the same index: price_type was also missing from the key, so a cost_plus_percent markup proposal and an unrelated fixed_price proposal for the exact same scope produced the identical dedup key and falsely collided. Fixed by adding price_type to the key too. Both fixes are reflected in the live price_rule_pending_scope_dedup_unique index (8-column key, listed under price_rule's indexes above).
• The supersession-cycle bugs (two, found by adversarial verification). (1) Nothing originally prevented superseded_by_id from being set to the row's OWN id — a trivial self-reference passing the NULL-ness CHECK. Fixed with chk_price_rule_no_self_supersede. (2) A longer cycle (row A's superseded_by_id=B, row B's superseded_by_id=A, both status='superseded') satisfies every CHECK/index independently (the UNIQUE(superseded_by_id) index sees two distinct target values, not a collision) and would infinite-loop any "walk to the current version" query. This is not reachable through the intended write path (a genuinely fresh row always has superseded_by_id IS NULL by construction) and would require a bug or deliberate misuse outside the two-step write pattern — judged disproportionate for a general DB-enforced cycle-detector (would need a recursive trigger), so it is documented as a binding write-path discipline instead: always INSERT the new row first with nothing pointing at it, then UPDATE only the ONE specific predecessor being replaced — never re-point an already-superseded row, never chain more than one hop per write.
• The cross-tenant-supersession trigger. The most severe finding this round: a bare self-FK with no composite constraint tying the target's tenant_id/item_variant_id/scope_type to the replacing row is exploitable via an ordinary application bug, letting a cross-tenant supersession leak another tenant's pricing data through an audit/history JOIN. A same-row CHECK cannot express a cross-row comparison, so this became the one genuinely new trigger this module introduces — trg_price_rule_validate_supersession (full definition in the Triggers section above). Live-tested: a legitimate same-tenant/variant/scope supersession succeeds; a cross-tenant supersession attempt is rejected by the trigger.
• The applies_all_sites + site_id ambiguity. Nothing originally prevented both being set at once, and the documented sync query only credited applies_all_sites when site_id IS NULL — so a row with both set would silently behave as site-only, making applies_all_sites=true a dead, misleading flag exactly when a form or agent might default both fields together. Fixed with chk_price_rule_applies_all_sites_xor_site_id: exactly one of "pinned to a specific site" or "explicitly all-sites" may be true, never both. A related fix in the same area: the documented Flutter sync query was found to be missing a review_status gate, meaning a PENDING agent-authored row with applies_all_sites=true would replicate to every POS device tenant-wide regardless of approval — reopening the exact privacy risk applies_all_sites exists to close, at the sync/replication layer even though the pricing layer stays correctly gated. Fixed by making the review_status IN ('not_required','approved') gate explicit in the documented sync contract (module_spec/pricing.md), not just implied.
• The cost_plus_percent unbounded-ceiling bug. The original branch of chk_price_rule_price_value only required price_value > 0 for cost_plus_percent (reusing percent_off's bound shape), which meant (a) a 150%+ "markup" inserted cleanly with zero ceiling — a fat-finger of 4000 vs. 40, or a runaway agent, had no DB-level backstop, and (b) there was no way to express "sell at cost" (0% markup) or a deliberate below-cost clearance markdown, directly undercutting this price_type's own stated purpose (backing margin-erosion/dead-stock markdown proposals). Fixed: price_value BETWEEN -100 AND 1000. Negative values down to -100 express a markdown BELOW cost (e.g. -20 = 20% below avg_cost_cents, floored at -100 = free/$0, never negative-priced); 0 expresses selling exactly at cost; the 1000 ceiling (10x cost) is a deliberately generous but bounded guard against absurd values while still accommodating legitimately high-markup nursery specialty items. This specific ceiling is explicitly flagged as a business-policy judgment call, not an objectively "correct" number — revisitable, not settled. Live-tested: cost_plus_percent = -50 (a below-cost clearance markdown) succeeds; cost_plus_percent = 1500 (above the ceiling) is rejected by chk_price_rule_price_value.

Other fixes made along the way (same verification passes): chk_price_rule_reviewer_not_creator (closes a C8 "independently controlled" SoD gap — nothing previously stopped the same actor from both creating and approving a draft price change); the idempotency_key grain corrected to (tenant_id, created_by_actor_id, idempotency_key) (the original (tenant_id, idempotency_key) grain omitted the actor dimension, unlike ai.agent_execution's own precedent, so two different agents deriving the same business-meaningful key for the same variant would spuriously collide); and currency_code reversed from the original draft's "no currency column, derive live from item_variant" decision, since item_variant.currency_code is a live, updatable NOT NULL FK, not frozen at insert time — deriving live would let a later currency change on the variant silently reinterpret an existing rule's price_value under the new currency with zero audit trail.

8 live-tested regression scenarios, all passed (run inside BEGIN/ROLLBACK, no residual data): (1) cost_plus_percent=-50 succeeds; (2) cost_plus_percent=1500 rejected by chk_price_rule_price_value; (3) a row's superseded_by_id set to its own id rejected by chk_price_rule_no_self_supersede; (4) applies_all_sites=true AND site_id both set on a customer-scoped row rejected by chk_price_rule_applies_all_sites_xor_site_id; (5) a legitimate same-tenant/variant/scope supersession succeeds; (6) a cross-tenant supersession attempt rejected by trg_price_rule_validate_supersession; (7) two different pending price_types (fixed_price + cost_plus_percent) for the identical scope coexist (dedup index correctly does not collide them); (8) a second pending fixed_price proposal for the exact same scope as an existing pending fixed_price rejected by price_rule_pending_scope_dedup_unique (genuine duplicate correctly caught).

Third verification round — post-build Section 4 audit + adversarial verification against the live-built DDL, one more real bug found and fixed. After the migration was applied, an independent Section 4 audit (re-deriving PASS/FAIL/GAP from scratch against live Postgres, not the design draft's own self-assessment) found zero FAILs across all A–P+T items and empirically re-confirmed every CHECK/trigger/index behaves exactly as documented. A separate adversarial-verification pass then found: the idempotency-key grain's created_by_actor_id is nullable, and the original single-partial-unique let two NULL-actor rows share the same idempotency_key (Postgres NULL≠NULL, live-reproduced) — silently defeating idempotency for any unattributed/system/import caller. Fixed with the same two-partial-unique split already used for ai.ai_request's NULL-tenant fix (see price_rule_tenant_null_actor_idempotency_unique above, and the identical fix on price_list_assignment). A 9th regression test (B9) proves this live. The same adversarial pass re-confirmed the documented 2-cycle superseded_by_id risk is live-reproducible today (as the design already disclosed — not a translation bug, an accepted, documented write-path discipline) and separately surfaced a systemic, pre-existing, cross-module gap unrelated to this build: the authenticated Postgres role has no schema-level GRANT on pricing (also true of crm/inventory/multi_loc/shared — only platform has one, likely a bootstrapping artifact) — RLS policies are structurally correct but currently unreachable by any real authenticated connection. This predates and is unrelated to pricing's own build; logged to OPEN_ITEMS as a cross-cutting finding, not fixed here (would require touching 4 other already-locked modules' migrations, out of scope for this module's own lock).

applies_all_sites — full rationale

For scope_type IN ('customer','customer_group'), the author (human or agent) must make an EXPLICIT choice: either pin site_id to a specific site, or explicitly set applies_all_sites=true to declare "yes, this genuinely should sync/apply everywhere." default/price_level-scoped rules are unaffected — their own site_id=NULL already unambiguously means tenant-wide with no customer-identity/privacy stakes, so applies_all_sites stays false and is ignored for those scopes. chk_price_rule_customer_scope_site_explicit enforces the explicit-choice requirement; chk_price_rule_applies_all_sites_xor_site_id enforces that the two options are mutually exclusive, never both true. Build requirement, not DB-enforced (no CHECK can express "provenance mentions X"): when an agent sets applies_all_sites=true on a customer/customer_group-scoped row, decision_provenance MUST include an explicit justification for the all-sites declaration — closing a reviewer-inattention risk where a bundled applies_all_sites=true flag could ship in the same approval click as an eye-catching price change without being specifically scrutinized.

The currency-integer convention

A cross-table CHECK tying price_value's precision to shared.currency.decimal_places isn't expressible in Postgres without a trigger (same limitation already documented for ai_request.tenant_id/agent_identity_id and decision_provenance.memory_refs). The actionable half of this gap IS same-row-checkable: price_value for fixed_price/amount_off currency amounts must always be a whole-number integer count of the currency's smallest unit — exactly matching item_variant.base_price_cents's own established "cents" convention. This works uniformly across currencies without needing to know decimal_places at the CHECK level: a JPY price_value of 1500 means ¥1500 (JPY's smallest unit IS the yen, decimal_places=0); a USD price_value of 1500 means $15.00 (decimal_places=2). The difference between currencies is purely in how a human-readable amount is DISPLAYED (divide by 10^decimal_places), not in whether price_value must be a whole number. percent_off/cost_plus_percent are excluded (percentages, may be fractional, e.g. 12.5). Documented as a binding convention (not fully DB-enforced, since the "smallest unit" interpretation is itself a service-layer contract, matching base_price_cents's own existing informal-but-universal convention): whoever displays a resolved amount MUST divide by 10^shared.currency.decimal_places — part of PricingService's hard contract (see below).

tax_treatment

Every price_rule row states explicitly whether its price_value is tax-inclusive or tax-exclusive, closing a "global from day 1" gap. Defaults to 'exclusive' (current US-first reality) but is fully overridable per rule — different rules for the same tenant could legitimately carry different treatments (e.g. a negotiated B2B quote stated tax-exclusive even in an otherwise tax-inclusive jurisdiction). Known, explicitly-flagged residual gap: inventory.item_variant.base_price_cents (the fallback when no price_rule matches) has no tax_treatment concept at all — when resolution falls through to the bare base price, its tax treatment is undefined at the schema level. This is Inventory's schema, out of scope for this pass to fix unilaterally — logged to OPEN_ITEMS for whoever builds a Tax module or revisits item_variant.

cost_plus_percent — behavioral requirement beyond the schema

price_value is interpreted as a markup percentage over item_variant.avg_cost_cents (e.g. price_value=40 → resolved price = avg_cost_cents × 1.40, a 40% markup over cost). This is the mechanism that actually backs the "margin-erosion detection reuses price_rule in reverse" capability — without it, that capability had no real resolution path. Critical behavioral requirement, not just a schema note: unlike fixed_price/percent_off/amount_off (which resolve against a relatively stable anchor), cost_plus_percent MUST be resolved by joining item_variant.avg_cost_cents LIVE at resolution time, never cached/snapshotted — the entire point of this price_type is to track cost fluctuations dynamically. Documented as a binding PricingService requirement (see Hard Contracts below). See chk_price_rule_price_value's bound rationale above for why the range is [-100, 1000].

campaign_label

A lightweight tag, deliberately NOT a full Promotion entity. The outside critique separately flagged the absence of a full promotion/campaign HEADER object (usage caps, redemption limits, stacking rules, coupon codes) the way Shopify Discounts/Magento Cart Price Rules/SFCC Promotions have — that is a genuinely bigger feature than what's resolved here, and remains correctly out of scope (logged to OPEN_ITEMS, not silently dropped). campaign_label (text, nullable) is a cheap, informal grouping tag: Reporting can group price_rule/price_change_log rows by a human-assigned label (e.g. "spring_sale_2026") instead of fuzzy-matching free-text reason strings, and an agent proposing a batch of markdowns can tag them all under one campaign for later analysis — without inventing usage caps, coupon codes, or a stacking-rules engine this pass.

Hard contracts — binding on future builders, not vague deferrals

Three non-schema gaps were resolved as binding cross-module hard contracts rather than left as loose prose, since pos/orders/PricingService don't exist yet:

1. pos.sale_line MUST snapshot the resolved price at sale time. Recorded in docs/modules/CROSS_MODULE_CONTRACTS.md's Pricing section: pos.sale_line (and orders' equivalent line-item table) must snapshot resolved_amount_minor_units (bigint, NOT NULL, pre-rounding), charged_amount_minor_units (bigint, NOT NULL, post-rounding — equal to the resolved amount when no rounding applied), currency_code, tax_treatment, resolving_price_rule_id (nullable — NULL when no rule matched), and resolved_quantity (needed to verify which min_qty tier fired). This is the authoritative historical record of "what did the customer actually pay," distinct from price_rule's own supersede-based history.
2. ONE versioned resolvePrice() spec + cross-language golden test vectors, recorded in module_spec/pricing.md's Build Requirements section: the 5-step precedence algorithm must be specified in exactly one authoritative pseudocode document, and both the Node PricingService and the Flutter/Dart offline resolver must be tested against the SAME golden test-vector suite before either ships, with a named minimum coverage bar (specificity ties, site-specific-vs-tenant-wide precedence, the priority/created_at tiebreak, cost_plus_percent's live cost join, applies_all_sites resolution, and min_qty tier boundaries).
3. Display rounding (e.g. .99 psychological pricing) is a PricingService concern, never a schema mutation. Also in module_spec/pricing.md. Reconciled with hard contract 1's pre/post-rounding amount distinction: whatever rounding rule PricingService applies, the pre-rounding resolved amount and the post-rounding charged amount must both be captured distinctly at sale time.

Service layer

No PricingService exists yet — schema-only this pass, same pattern as every other module's deferred service layer (shared, multi_loc, crm, inventory, ai before their own service layers, where applicable).

JSONB columns

price_rule.decision_provenance, price_list_assignment.decision_provenance, price_change_log.old_value/new_value.

---

Open items carried forward (see OPEN_ITEMS.md for full text)

1. agent_duty_grant discount/margin-ceiling gap — spend_limit_cents/quantity_limit don't map onto "how much may this agent discount by." Trigger: "when a markdown-proposing agent actually runs and needs an enforced discount/margin ceiling beyond human review."
2. No overlap-prevention EXCLUDE constraint on price_list_assignment for time-windowed customer/group assignments. Trigger: "if/when overlapping assignment periods become a real support burden — would need btree_gist."
3. Full promotion/campaign header object (usage caps, coupon codes, stacking rules) — campaign_label is a lightweight tag only. Trigger: "when coupon-code or usage-capped promotions become a real product requirement."
4. tax_treatment's residual gap at the bare item_variant.base_price_cents fallback path (no price_rule matched) — tax treatment is undefined at the schema level for that case. Trigger: "when a Tax module is built or item_variant is revisited."
5. Hard Contract 1 (pos.sale_line snapshot requirement) — not yet satisfied, pos/orders don't exist yet. Trigger: "when pos/orders are built."
6. Hard Contract 2 (shared resolvePrice() spec + golden test vectors with the named minimum coverage bar) — not yet satisfied, neither PricingService nor the Dart resolver exist yet. Trigger: "when Node PricingService and/or the Dart offline resolver are first built."
7. Hard Contract 3 (display rounding is a PricingService-only concern) — not yet satisfied. Trigger: "when PricingService's display layer is built."
8. No PricingService yet — schema-only this pass, same pattern as every other module's deferred service layer. Trigger: "when PricingService is built."
9. price_change_log.old_value/new_value cannot reconstruct historical $ deltas for non-fixed-price rules — found by the 2026-07-07 erosion audit; jsonb stores raw rule parameters, no point-in-time base-price/cost snapshot exists. Trigger: "when pricing-change $-delta reporting is needed, or when a point-in-time cost snapshot mechanism exists."

Fixed 2026-07-07 (PROJECT_DECISIONS #28 — see DR-17/DR-18 above)

• Erosion closed: price_rule.rule_kind + price_rule_sale_expiry_idx restored (dropped entirely during the original v1→v2 build, never logged).
• Erosion closed: price_rule.name restored (dropped entirely, never logged).
• Erosion closed: price_list_assignment.is_active + the two open-ended-active-assignment uniqueness guarantees restored (dropped entirely, never logged) — item 2 above (overlap-prevention EXCLUDE constraint) remains a separate, still-open gap for time-windowed date-range overlaps; this fix only closes the open-ended (no end date) case.