returns — module #27, the customer RMA (return merchandise authorization) layer

9 tables, 153 columns (152→153, 2026-07-18, Phase 3 stored-value build — return_resolution.store_credit_transaction_id, PROJECT_DECISIONS #71) — schema-locked 2026-07-11 (PROJECT_DECISIONS #61), built as Pass 2 of a same-day 2-pass session (Pass 1, PROJECT_DECISIONS #60, reopened rewards/offers to add proportional/cumulative-cap partial-reversal — a prerequisite this module's own clawback seam consumes). returns owns the buy-side-mirror-image of a sale: RMA authorization, the frozen discount/tax allocation a return refunds against, an aggregate-cap tracker preventing over-return across multiple separate RMAs against one source line, the physical return-receipt/inventory-posting event, the resolution outcome (refund/store credit/replacement/repair/warranty credit/reject), and a revived plant-warranty-claim instance table. Migration: packages/db/migrations/20260711040000_returns_module.sql. The current 153-column count is confirmed across Drizzle and the live DB; the Inventory Core companion adds no Returns column.

Inventory Core companion reopen — 2026-07-15 task run (PROJECT_DECISIONS #75). trg_return_receipt_line_post_and_cap now fires AFTER INSERT, so ordinary-caller RLS, FKs, and row constraints validate the receipt line before the privileged posting wrapper executes. The wrapper then re-reads the persisted line and locks/derives its authoritative receipt and authorization facts. returns_invariant_owner has only the required SELECT/UPDATE policies on return_receipt_line; current Returns policy count is 17. This companion change adds no Returns table or column.

returns is the first module built entirely under the corrected, 3-lens-adversarially-verified design (returns-module-design-proposal-2026-07-11.md) — the design draft itself was NOT built as originally scoped: Architect Decision 2 (per PROJECT_DECISIONS #61) deliberately deviates from the design's own Block 4, which had deferred proportional loyalty/offer clawback as out-of-scope for v1 because rewards/offers' reversal triggers, at design time, supported only exact-full-negation (rewards) or unvalidated-magnitude (offers) reversal. Pass 1 (#60), run immediately before this build in the same session, closed that exact gap — so this build implements proportional clawback as a real, working capability from day one, not a deferred placeholder.

Depends on platform (tenant), identity (actor), multi_loc (site), crm (customer — bare FK, matching the universal codebase-wide convention for every reference to crm.customer), pos (sale/sale_line/sale_refund/sale_refund_line — the latter two gained a new prerequisite UNIQUE(id, tenant_id) in this same migration, both were PK-only before), orders (order_header/order_line — same new prerequisite, both were PK-only before), inventory (item_variant/lot/stock_movement/stock_movement_line — read/write via the posting trigger, no new prerequisite needed), rewards (loyalty_point_ledger — the first-ever business-layer→rewards direction FK in this codebase; every prior FK between the two schemas ran the other way), and offers (offer_redemption — same first-ever-direction note). returns never re-implements refund, loyalty, or offer math: pos.sale_refund stays the one source of truth for refunded money, and return_resolution.loyalty_reversal_ledger_id/.offer_reversal_redemption_id link to 'reverse'-typed rows written using rewards/offers' own (now-proportional) mechanisms.

PROJECT_DECISIONS entry: #61 (and its companion Pass-1 entry, #60, for the rewards/offers proportional-reversal fix this module's clawback seam depends on).

Groups: Authorization (return_authorization, return_authorization_line, return_source_line_tracker) / Resolution (return_resolution, return_resolution_line) / Physical Receipt (return_receipt, return_receipt_line) / Catalogs (return_reason, warranty).

Global rules for this schema:

• Uniform tenant-scoping — all 9 tables carry tenant_id NOT NULL FK → platform.tenant, standard RLS <table>_tenant_isolation policy (FOR ALL TO authenticated, USING/WITH CHECK both tenant_id = current_setting('app.current_tenant_id')::uuid), a plain tenant_id index. Cross-tenant INSERT is live-rejected with 42501 (live-reproduced at lock gate). No nullable tenant_id anywhere in this module (Section 4 Item C: N/A).
• updated_at trigger-maintained via platform.set_updated_at() (shared, reused) on 8 of 9 tables — every table except return_resolution_line, the one genuinely append-only fact table.
• Soft delete (deleted_at) on 7 of 9 tables — return_reason, warranty, return_authorization, return_authorization_line, return_resolution, return_receipt, return_receipt_line. Not on return_source_line_tracker (a small mutable counter row with no lifecycle to soft-delete, mirroring offers.offer_redemption_reversal_tracker's own precedent exactly) and not on return_resolution_line (append-only, mirroring billing.ar_charge_line's own precedent). Every _number-style uniqueness constraint (rma_number) and every soft-deletable catalog uniqueness (return_reason.code) is WHERE deleted_at IS NULL.
• Append-only, 1 table: return_resolution_line — a write-once financial fact, mirroring billing.ar_charge_line/purchasing.vendor_credit_line exactly (REVOKE UPDATE/DELETE + platform.reject_append_only_mutation() reused verbatim).
• Agent-as-actor — every *_actor_id targets identity.actor, bare FK (codebase-wide convention).
• Autonomy tiers, non-uniform by design. return_authorization and return_authorization_line both get the FULL pack (created_by_actor_id + automation_source + the full review seam — review_status/review_reason/reviewed_by_actor_id/reviewed_at — + decision_provenance); this is the genuine risk-tiering/escalation judgment surface for the module, duplicated onto the line table because a line-level allocation decision can itself need review independent of the header. return_resolution also gets the FULL pack — the refund/store-credit/replacement/repair/warranty-credit/reject decision is exactly the kind of judgment this codebase reserves FULL for. return_reason and warranty carry only created_by_actor_id (no automation_source, no review seam) — plain catalog/revived-v1 shapes, mirroring inventory.stock_adjustment_reason's and v1 pos.guarantee's own precedents respectively. return_source_line_tracker and return_resolution_line carry zero autonomy columns at all — deterministic, maintained-counter/ledger-fact shapes with no judgment surface of their own. return_receipt gets LIGHT (received_by_actor_id/voided_by_actor_id/void_reason — attribution only, no automation_source, no review seam) and return_receipt_line gets NONE (zero actor attribution at all) — a deliberately lighter pairing than receiving.goods_receipt(FULL)/goods_receipt_line(LIGHT): the RMA-approval judgment already happened upstream on the FULL return_authorization/return_authorization_line, so the physical receiving event itself needs no independent review seam duplicated onto it.
• Composite (col, tenant_id) FK convention, total on every cross-schema reference that can support it. The 2 disclosed, deliberate exceptions match the rest of the codebase: customer_id → crm.customer stays a plain bare FK (the universal convention — crm.customer itself has never been given the UNIQUE(id, tenant_id) prerequisite, and all 22-plus live FKs anywhere in this codebase targeting it are plain; this design does not introduce a new gap), and every *_actor_id → identity.actor stays bare (codebase-wide actor-FK convention).
• The rewards/offers seam is the first-ever business-layer FK pointing INTO either schema in this codebase. Every prior FK between returns' siblings and rewards/offers ran the other direction (rewards.loyalty_point_ledger.sale_id/offers.offer_redemption.sale_id/.sale_line_id are composite FKs FROM those schemas INTO pos). return_resolution.loyalty_reversal_ledger_id/.offer_reversal_redemption_id are the reverse — permitted per SCHEMA_CONVENTIONS.md §13/§14 (rewards/offers are fully tenant-scoped, same as any business schema; only consumer's own core identity tables get the SECURITY-DEFINER wall), disclosed here as the non-obvious precedent it is.
• Schema-bootstrap GRANTs. A brand-new schema needs its own bootstrap — GRANT USAGE ON SCHEMA returns, GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES, and ALTER DEFAULT PRIVILEGES — the same 3-statement template every prior new-schema build in this codebase (approvals, receiving, consumer/rewards/offers, files) used.
• Money never re-derived where an owning module already computed it. return_authorization_line freezes a per-unit discount/tax allocation at RA-creation time (§ below) rather than re-querying pos/offers/rewards later; return_resolution_line.resolved_amount_cents reconciles against pos.sale_refund_line's own authoritative amount when a refund is involved, never restating an independent figure for that case.

Cross-Phase / Cross-Module Foreign Keys (returns)

Column	Target	Notes
*.tenant_id (all 9 tables)	platform.tenant	NOT NULL
return_authorization.site_id	multi_loc.site (id, tenant_id)	NOT NULL, composite
return_authorization.source_sale_id	pos.sale (id, tenant_id)	nullable, composite — NULL for an unreferenced/anonymous walk-in return
return_authorization.source_order_id	orders.order_header (id, tenant_id)	nullable, composite
return_authorization.customer_id	crm.customer.id	nullable, bare (universal codebase-wide convention for every crm.customer reference)
return_authorization.warranty_id	returns.warranty (id, tenant_id)	nullable, composite — gated by chk_return_authorization_warranty_requires_type
return_authorization.reason_id, return_authorization_line.reason_id	returns.return_reason (id, tenant_id)	nullable, composite (same-schema, composite for consistency, mirroring receiving.goods_receipt_line.goods_receipt_id's own same-schema-composite precedent)
return_authorization_line.return_authorization_id	returns.return_authorization (id, tenant_id)	NOT NULL, composite
return_authorization_line.sale_line_id	pos.sale_line (id, tenant_id)	nullable, composite
return_authorization_line.order_line_id	orders.order_line (id, tenant_id)	nullable, composite
return_authorization_line.item_variant_id	inventory.item_variant (id, tenant_id)	nullable, composite — the no-receipt/unreferenced fallback, mirrors pos.sale_refund_line's own fallback shape
return_source_line_tracker.sale_line_id	pos.sale_line (id, tenant_id)	nullable, composite — exactly one of sale_line_id/order_line_id set, CHECK-enforced
return_source_line_tracker.order_line_id	orders.order_line (id, tenant_id)	nullable, composite
return_resolution.return_authorization_id	returns.return_authorization (id, tenant_id)	NOT NULL, composite
return_resolution.pos_sale_refund_id	pos.sale_refund (id, tenant_id)	nullable, composite — required when resolution_type='refund' (CHECK)
return_resolution.replacement_sale_id	pos.sale (id, tenant_id)	nullable, composite — the exchange-as-two-transactions path
return_resolution.loyalty_reversal_ledger_id	rewards.loyalty_point_ledger (id, tenant_id)	nullable, composite — first-ever business→rewards direction FK in this codebase; links to a 'reverse'-typed row written via rewards's own proportional mechanism (Pass 1, #60)
return_resolution.offer_reversal_redemption_id	offers.offer_redemption (id, tenant_id)	nullable, composite — same first-ever-direction note, into offers
return_resolution_line.return_resolution_id	returns.return_resolution (id, tenant_id)	NOT NULL, composite
return_resolution_line.return_authorization_line_id	returns.return_authorization_line (id, tenant_id)	NOT NULL, composite — the resolution-line cap trigger's own grouping key
return_resolution_line.sale_refund_line_id	pos.sale_refund_line (id, tenant_id)	nullable, composite — populated only when the parent resolution is a refund; reconciliation trigger requires exact equality when set
return_receipt.return_authorization_id	returns.return_authorization (id, tenant_id)	NOT NULL, composite
return_receipt.site_id	multi_loc.site (id, tenant_id)	NOT NULL, composite
return_receipt_line.return_receipt_id	returns.return_receipt (id, tenant_id)	NOT NULL, composite
return_receipt_line.return_authorization_line_id	returns.return_authorization_line (id, tenant_id)	NOT NULL, composite
return_receipt_line.lot_id	inventory.lot (id, tenant_id)	nullable, composite
return_receipt_line.stock_movement_id	inventory.stock_movement (id, tenant_id)	nullable, composite — the posting seam, reusing receiving's mechanism
return_receipt_line.stock_movement_line_id	inventory.stock_movement_line (id, tenant_id)	nullable, composite — line-grain movement linkage
return_receipt_line.reversal_of_return_receipt_line_id	returns.return_receipt_line (id, tenant_id)	nullable, self-referencing composite — mirrors goods_receipt_line.reversal_of_goods_receipt_line_id exactly
warranty.item_variant_id	inventory.item_variant (id, tenant_id)	composite
warranty.sale_id	pos.sale (id, tenant_id)	composite
warranty.sale_line_id	pos.sale_line (id, tenant_id)	composite
warranty.claimed_via_resolution_id	returns.return_resolution (id, tenant_id)	nullable, composite — retargeted from v1's claimed_refund_id → pos.sale_refund (Block 2's one capability gain: a warranty claim can now resolve as a replacement, not just a refund)
*.created_by_actor_id, .reviewed_by_actor_id, .received_by_actor_id, .voided_by_actor_id	identity.actor	nullable, bare (codebase-wide actor-FK convention)

---

returns.return_reason (10 cols) — tenant-scoped catalog, mirrors inventory.stock_adjustment_reason exactly

A plain reason-code catalog, FK'd from return_authorization.reason_id/return_authorization_line.reason_id rather than a CHECK-enum — the same "real catalog table, not a hardcoded list" pattern this codebase uses everywhere a reason needs to be tenant-configurable and orderable in a UI.

Tenant-scoped. RLS enabled — return_reason_tenant_isolation. Soft delete: deleted_at. updated_at: trigger-maintained via platform.set_updated_at(). No autonomy pack beyond created_by_actor_id.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
code	text	NOT NULL	—	UNIQUE per (tenant_id) WHERE not deleted
name	text	NOT NULL	—
is_active	boolean	NOT NULL	true
sort_order	integer	NOT NULL	0	UI ordering
created_by_actor_id	UUID	nullable	—	FK → identity.actor
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained
deleted_at	timestamptz	nullable	—	Soft delete

Indexes (3): PK on id; return_reason_id_tenant_id_unique UNIQUE(id,tenant_id) (prerequisite for return_authorization/return_authorization_line's own composite reason_id FKs); return_reason_tenant_id_code_unique UNIQUE(tenant_id,code) WHERE deleted_at IS NULL.

---

returns.warranty (16 cols) — REVIVED v1 pos.guarantee, warranty claim instance tracking

Inspected 2026-07-18, Phase 2 (Nursery Vertical Extraction, PROJECT_DECISIONS #70), 'V7' of that phase's own extraction plan — confirmed already fully vertical-neutral. Nothing extracted; this note is documentation-language-only.

Revived from v1's fully-specified-but-never-built pos.guarantee (16 cols), long held up in this codebase's own history as "the canonical example of the right approach" for vertical-specific extensibility — not because the table itself is plant-specific in design, but because a nursery-vertical case (plant_guarantee) is handled as one CHECK-constrained enum value on a single generic warranty table, alongside manufacturer_warranty/extended_warranty, rather than requiring its own dedicated vertical-only table or column shape. 13 columns carry over verbatim, 2 are cosmetic renames (variant_id→item_variant_id, guarantee_type→warranty_type), and 1 is retargeted (Block 2's one capability gain): v1's claimed_refund_id → pos.sale_refund becomes claimed_via_resolution_id → returns.return_resolution, since v1 wrongly assumed every guarantee claim resolves as a refund — this module's own differentiator flow (a dead-plant warranty claim resolving as a replacement, never a refund) makes that assumption wrong on its face, and return_resolution represents every outcome pos.sale_refund could plus 4 more (replacement/repair/warranty_credit/store_credit). Zero columns dropped.

Tenant-scoped. RLS enabled — warranty_tenant_isolation. Soft delete: deleted_at. updated_at: trigger-maintained via platform.set_updated_at(). No autonomy pack — a straight revival of v1's pre-autonomy-era shape, matching the codebase's own "verbatim revival, don't retrofit unrelated conventions" practice for this class of table.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
item_variant_id	UUID	NOT NULL	—	Renamed from v1's variant_id; composite FK → inventory.item_variant
sale_id	UUID	NOT NULL	—	Composite FK → pos.sale — the guarantee is issued at time of sale
sale_line_id	UUID	NOT NULL	—	Composite FK → pos.sale_line
customer_id	UUID	nullable	—	Plain FK → crm.customer
warranty_type	text	NOT NULL	—	Renamed from v1's guarantee_type
issued_date	date	NOT NULL	—
expires_date	date	nullable	—
terms_snapshot	jsonb	nullable	—	Frozen copy of inventory.item_variant.guarantee_terms at issuance, e.g. {"duration_days":365,"type":"plant_guarantee"}
signature_ref	text	nullable	—	Deferred Files forward-ref → files.file.id, folded into the existing Files FK-wiring bundle (retargeted from pos.guarantee's own identical, pre-existing gap)
status	text	NOT NULL	'active'	CHECK IN (active,claimed,expired,void)
claimed_via_resolution_id	UUID	nullable	—	Retargeted from v1's claimed_refund_id → pos.sale_refund; composite FK → returns.return_resolution
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained
deleted_at	timestamptz	nullable	—	Soft delete

CHECK constraints (1): chk_warranty_status.

Indexes (4): PK on id; warranty_id_tenant_id_unique UNIQUE(id,tenant_id) (prerequisite for return_authorization.warranty_id's own composite FK); warranty_tenant_id_item_variant_id_idx on (tenant_id,item_variant_id); warranty_tenant_id_status_idx on (tenant_id,status) WHERE status='active' — the active-warranty lookup, confirmed valid against this table's own CHECK enum (Section 4 Item E).

---

returns.return_authorization (29 cols, FULL autonomy) — the RMA header

One row per return-merchandise-authorization event. This is the genuine risk-tiering/escalation judgment surface of the module — every column past risk_factors is the standard FULL autonomy pack.

Tenant-scoped. RLS enabled — return_authorization_tenant_isolation. Soft delete: deleted_at. updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
site_id	UUID	NOT NULL	—	Composite FK → multi_loc.site
rma_number	text	NOT NULL	—	UNIQUE per (tenant_id) WHERE not deleted — the exceptionless "every _number header column gets this constraint" codebase convention
source_sale_id	UUID	nullable	—	Composite FK → pos.sale; NULL for an unreferenced/anonymous walk-in return
source_order_id	UUID	nullable	—	Composite FK → orders.order_header
customer_id	UUID	nullable	—	Plain FK → crm.customer; NULL for an anonymous walk-in
return_type	text	NOT NULL	—	CHECK IN (standard,warranty_claim,unreferenced)
channel	text	NOT NULL	—	CHECK IN (pos,online)
original_channel	text	nullable	—	CHECK IN (pos,online) — buy-online-return-in-store (BORIS) tracking
status	text	NOT NULL	'draft'	CHECK IN (draft,authorized,partially_received,fully_received,resolved,cancelled,expired,closed)
expiry_date	date	nullable	—
physical_receipt_required	boolean	NOT NULL	true	false for warranty claims resolving with no physical return (the no-receipt path, see warranty above)
warranty_id	UUID	nullable	—	Composite FK → returns.warranty. CHECK chk_return_authorization_warranty_requires_type: warranty_id IS NULL OR return_type = 'warranty_claim' — NULL-safe by construction
reason_id	UUID	nullable	—	Composite FK → returns.return_reason
policy_reference	text	nullable	—	Descriptive only, no dedicated policy table exists
risk_score	numeric	nullable	—	Computed from live raw joins each time — no cached fraud-signal counter exists yet (deferred to a future crm/reporting surface)
risk_tier	text	nullable	—	CHECK IN (low,medium,high)
risk_factors	jsonb	nullable	—	e.g. {"return_velocity_30d": 3, "days_since_purchase": 2, "anonymous": false}
created_by_actor_id	UUID	nullable	—	FK → identity.actor — FULL-pack
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	—	Project-wide reason/evidence/confidence/memory_refs convention
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained
deleted_at	timestamptz	nullable	—	Soft delete

CHECK constraints (6): chk_return_authorization_return_type; chk_return_authorization_channel; chk_return_authorization_original_channel; chk_return_authorization_status; chk_return_authorization_risk_tier; chk_return_authorization_warranty_requires_type (detailed above).

Indexes (4): PK on id; return_authorization_id_tenant_id_unique UNIQUE(id,tenant_id) (prerequisite for return_authorization_line/return_resolution/return_receipt's own composite FKs back to this header); return_authorization_tenant_id_rma_number_unique UNIQUE(tenant_id,rma_number) WHERE not deleted; return_authorization_tenant_id_status_idx on (tenant_id,status) partial for the open-RMA sweep.

Autonomy: FULL — the escalation/risk-tiering judgment surface, riding identity.agent_duty_grant verbatim (no new authority mechanism was built for this module).

Unreferenced/blind return, live-reproduced (per #61): an RA accepted with NO source_sale_id/source_order_id/customer_id at all, falling back to return_authorization_line.item_variant_id, with full risk-tiering (risk_score=75, risk_tier='high') — anonymous walk-in returns are structurally supported, consistent with this codebase's own Phase 4 ALLOW decision (PROJECT_DECISIONS #40).

---

returns.return_authorization_line (28 cols, FULL autonomy) — freezes the discount/tax allocation at RA-creation time

The module's central design decision, per PROJECT_DECISIONS #61's Architect Decision 1: pos.sale_line carries pure per-SKU pricing-rule resolution with no discount/allocation column of any kind (write-once, append-only, Pricing's Hard Contract 1), and a basket-wide offers/rewards discount has zero per-line breakdown anywhere in the system — if this table doesn't reconstruct-and-freeze the allocation at RA-creation time, it can never correctly know it later. allocation_source discloses which reconstruction path produced the frozen figures, since only line_scoped_offer rests on solid, directly-attributable data — the other two paths rest on a proportional-by-pre-discount-extended-price allocation policy this design newly defines.

Tenant-scoped. RLS enabled — return_authorization_line_tenant_isolation. Soft delete: deleted_at. updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—
return_authorization_id	UUID	NOT NULL	—	Composite FK → returns.return_authorization
sale_line_id	UUID	nullable	—	Composite FK → pos.sale_line
order_line_id	UUID	nullable	—	Composite FK → orders.order_line
item_variant_id	UUID	nullable	—	No-receipt/unreferenced fallback, mirrors pos.sale_refund_line's own fallback shape
reason_id	UUID	nullable	—	Composite FK → returns.return_reason
requested_disposition	text	nullable	—	CHECK IN (restock,scrap,repair,quarantine,return_to_vendor)
authorized_qty	numeric	NOT NULL	—	Never trust this column alone to bound total returns of a source line — capped in aggregate against the source line via return_source_line_tracker (below), across potentially multiple separate RMAs
received_qty	numeric	NOT NULL	0	CHECK chk_return_authorization_line_qty_rollup: received_qty <= authorized_qty AND credited_qty <= authorized_qty — 2 independent peer counters, not a chain (mirrors purchasing.purchase_order_line's own several-independent-bounded-counters shape). Maintained by returns.post_and_cap_return_receipt_line() (below), never a caller-supplied value
credited_qty	numeric	NOT NULL	0	Peer counter to received_qty (see CHECK above) — this is what lets credited_qty reach authorized_qty while received_qty stays 0 forever on the no-physical-receipt warranty path. Maintained by returns.validate_and_apply_resolution_line() (below)
restocking_fee_cents	bigint	NOT NULL	0	No codebase precedent existed before this module (grep -ri restock returned nothing codebase-wide); invented from scratch, taxability left undecided (OPEN_ITEMS)
pre_discount_extended_price_cents	bigint	NOT NULL	—	Frozen at RA-creation time
allocated_discount_cents	bigint	NOT NULL	0	Frozen at RA-creation time per the proportional-by-pre-discount-extended-price allocation policy
effective_unit_price_cents	bigint	NOT NULL	—	(pre_discount_extended_price_cents − allocated_discount_cents) / authorized_qty
allocated_tax_cents	bigint	NOT NULL	0	Frozen proportional tax share
eligible_refund_cents	bigint	NOT NULL	—	The frozen ceiling this line can ever resolve up to, in aggregate across every return_resolution_line referencing it — effective_unit_price_cents minus restocking_fee_cents, never separately taxed
allocation_source	text	NOT NULL	—	CHECK IN (line_scoped_offer,basket_allocated_offer,loyalty_allocated,none) — discloses which reconstruction path produced allocated_discount_cents
created_by_actor_id	UUID	nullable	—	FK → identity.actor — FULL-pack
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
deleted_at	timestamptz	nullable	—	Soft delete

CHECK constraints (3): chk_return_authorization_line_requested_disposition; chk_return_authorization_line_qty_rollup (detailed above); chk_return_authorization_line_allocation_source.

Indexes (3): PK on id; return_authorization_line_id_tenant_id_unique UNIQUE(id,tenant_id) (prerequisite for return_resolution_line/return_receipt_line's own composite FKs back to this line); return_authorization_line_tenant_id_return_authorization_id_idx on (tenant_id,return_authorization_id).

Triggers (2): set_updated_at (standard) and trg_return_authorization_line_check_and_reserve (BEFORE INSERT) → returns.check_and_reserve_source_line() — see "The aggregate-cap seam" below.

Autonomy: FULL — duplicated from the header, since a line-level allocation decision can itself need independent review.

Discount allocation, live-reproduced (per #61, Architect Decision 1): a $150 basket (2 lines, $100 + $50) with a 20%-off basket-wide offer — returning the $100 line correctly refunds $80, not $100 (allocated_discount_cents = 10000/15000 × 3000 = 2000; effective_unit_price_cents = (10000−2000)/1 = 8000). Restocking fee arithmetic: a $40.00 effective price minus a $5.00 restocking fee correctly yields $35.00 eligible refund (not $40 or $50) — the fee reduces the refund directly and is never separately taxed.

Disclosed limitation, not a gap unique to this table: the per-line derivation formulas above (allocated_discount_cents, effective_unit_price_cents, eligible_refund_cents, the restocking-fee arithmetic) are not themselves DB-enforced — only non-negativity (chk_return_authorization_line_money_nonneg) and the aggregate ceiling against the original source line (return_source_line_tracker) exist as backstops, so a caller could in principle store a wrong-but-in-bounds combination today. This matches the codebase-wide convention already established on every other line-level pricing table (pos.sale_line, orders.order_line — both non-negativity-only, no derivation CHECK anywhere), not a regression specific to returns. Flagged at lock-gate verification (PROJECT_DECISIONS #61 addendum) precisely because no ReturnsService exists yet to compute these correctly — whoever builds it must implement and unit-test this arithmetic directly, since the DB will not catch a formula error.

---

returns.return_source_line_tracker (10 cols, NEW) — the aggregate-cap cache, no autonomy pack

Closes a real, live-verification-caught gap (Lens A Finding 1 of the design's own 3-lens adversarial pass, per PROJECT_DECISIONS #61): pos.sale_line/orders.order_line are append-only and structurally off-limits for a maintained counter column, so "remaining returnable quantity" cannot live as a column on the source line itself. This table is a small, purpose-built cache mirroring offers.offer_redemption_reversal_tracker's own shape exactly — one row per source line, created lazily on first touch, capping the running total atomically. It exists specifically to prevent cumulative over-return across MULTIPLE separate RMAs against the same source line — a gap a naive single-RA-scoped check would miss entirely.

Tenant-scoped. RLS enabled — return_source_line_tracker_tenant_isolation. updated_at: trigger-maintained via platform.set_updated_at(). No soft delete — one row per source line, created lazily, never removed (mirrors offers.offer/rewards.loyalty_account's own cached-running-total shape, not a soft-deletable business record). No autonomy pack — a deterministic maintained-counter shape.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—
sale_line_id	UUID	nullable	—	Composite FK → pos.sale_line. Exactly one of sale_line_id/order_line_id set
order_line_id	UUID	nullable	—	Composite FK → orders.order_line
original_qty	numeric	NOT NULL	—	Snapshotted from the source line at first-touch (row created on the first return_authorization_line insert referencing this source line)
original_charged_amount_cents	bigint	NOT NULL	—	Snapshotted at first-touch
total_authorized_qty	numeric	NOT NULL	0	Running total, atomically capped against original_qty
total_eligible_refund_cents	bigint	NOT NULL	0	Running total, atomically capped against original_charged_amount_cents
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained

CHECK constraints (1): chk_return_source_line_tracker_exactly_one_source — (sale_line_id IS NOT NULL) != (order_line_id IS NOT NULL), an explicit XOR-shaped disjunction, NULL-safe by construction.

Indexes (3): PK on id; return_source_line_tracker_tenant_id_sale_line_id_unique UNIQUE(tenant_id,sale_line_id) WHERE sale_line_id IS NOT NULL; return_source_line_tracker_tenant_id_order_line_id_unique UNIQUE(tenant_id,order_line_id) WHERE order_line_id IS NOT NULL — both partial uniques double as the ON CONFLICT target for the lazy-create insert and as one-row-per-source-line enforcement.

Trigger function: returns.check_and_reserve_source_line(), fired BEFORE INSERT on return_authorization_line as trg_return_authorization_line_check_and_reserve. Lazily creates the tracker row (INSERT ... ON CONFLICT (tenant_id, sale_line_id) WHERE sale_line_id IS NOT NULL DO NOTHING, or the order_line_id equivalent), then caps total_authorized_qty/total_eligible_refund_cents atomically via the same UPDATE ... WHERE total + NEW <= ceiling ... RETURNING shape offers.check_and_sync_offer_budget() already established — zero rows returned means the cap was exceeded (or the row didn't resolve), and the whole insert is rejected. A real bug was caught and fixed during this build's own live-reproduction pass: the first draft's ON CONFLICT clause omitted the WHERE predicate needed to match a partial unique index — unlike Pass 1's own single-nullable-column trackers (rewards.loyalty_point_ledger_reversal_tracker/offers.offer_redemption_reversal_tracker, each keyed on exactly one non-nullable FK column), this tracker has two mutually-exclusive nullable alternative columns (sale_line_id/order_line_id), so the ON CONFLICT target has to specify which partial index it's matching.

Aggregate over-return cap, live-reproduced (per #61): a second, separate RMA against an already-fully-returned line is rejected; a genuine 3-way concurrent race (3 backgrounded psql processes, real simultaneous connections) against a fresh 5-unit line, each requesting qty=2 — exactly 2 of 3 succeeded (cumulative 4, correctly capped under 5), 1 rejected; the tracker's own total_authorized_qty confirmed at exactly 4, never 6.

---

returns.return_resolution (21 cols, FULL autonomy) — the outcome header

One row per resolved-outcome event against an RA: refund, store credit, replacement, repair, warranty credit, or reject. pos_sale_refund_id/replacement_sale_id reference execution that already happened elsewhere — this table never duplicates money, only links to and reconciles against it. loyalty_reversal_ledger_id/offer_reversal_redemption_id link to 'reverse'-typed rows written using rewards/offers' own proportional mechanism (Pass 1, #60) — returns never re-implements loyalty/offer math, exactly as it never re-implements refund math.

Tenant-scoped. RLS enabled — return_resolution_tenant_isolation. Soft delete: deleted_at. updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—
return_authorization_id	UUID	NOT NULL	—	Composite FK → returns.return_authorization
resolution_type	text	NOT NULL	—	CHECK IN (refund,store_credit,replacement,repair,warranty_credit,reject)
pos_sale_refund_id	UUID	nullable	—	Composite FK → pos.sale_refund. CHECK: resolution_type != 'refund' OR pos_sale_refund_id IS NOT NULL — NULL-safe by construction
replacement_sale_id	UUID	nullable	—	Composite FK → pos.sale — the exchange-as-two-transactions path
store_credit_reference	text	nullable	—	DEPRECATED IN PLACE (2026-07-18, Phase 3 stored-value build, PROJECT_DECISIONS #71) — the loose free-text placeholder this column always was; superseded by store_credit_transaction_id below. 0 non-NULL rows at deprecation. Drop-trigger: after ReturnsService ships reading only the new column
store_credit_transaction_id	UUID	nullable	—	NEW 2026-07-18. Composite FK → billing.store_credit_transaction (id, tenant_id) — the real 'issue'-typed ledger entry this resolution created (link-don't-reimplement, same direction as loyalty_reversal_ledger_id). CHECK: resolution_type NOT IN ('store_credit','warranty_credit') OR store_credit_transaction_id IS NOT NULL — required for those two outcomes, mirroring pos_sale_refund_id's own enforced pattern
loyalty_reversal_ledger_id	UUID	nullable	—	Composite FK → rewards.loyalty_point_ledger — first-ever business→rewards direction FK
offer_reversal_redemption_id	UUID	nullable	—	Composite FK → offers.offer_redemption — first-ever business→offers direction FK
status	text	NOT NULL	'pending'	CHECK IN (pending,approved,executed,rejected,cancelled)
rejection_reason	text	nullable	—
created_by_actor_id	UUID	nullable	—	FK → identity.actor — FULL-pack
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
deleted_at	timestamptz	nullable	—	Soft delete

CHECK constraints (3): chk_return_resolution_resolution_type; chk_return_resolution_status; chk_return_resolution_refund_requires_sale_refund (detailed above).

Indexes (3): PK on id; return_resolution_id_tenant_id_unique UNIQUE(id,tenant_id) (prerequisite for return_resolution_line's own composite FK); return_resolution_tenant_id_return_authorization_id_idx on (tenant_id,return_authorization_id).

Autonomy: FULL — the refund/store-credit/replacement/repair/warranty-credit/reject decision.

sale_refund stays referenced, not absorbed — live-reproduced (per #61): every resolution's pos_sale_refund_id points at a genuine pos.sale_refund row created independently, confirmed live.

---

returns.return_resolution_line (8 cols, APPEND-ONLY, no autonomy pack)

Write-once financial fact, mirroring billing.ar_charge_line/purchasing.vendor_credit_line exactly — no created_by_actor_id/automation_source of its own (attribution lives on the parent return_resolution header, which is FULL).

Tenant-scoped, append-only (REVOKE UPDATE/DELETE + platform.reject_append_only_mutation() reused verbatim). RLS enabled — return_resolution_line_tenant_isolation. No updated_at/deleted_at.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—
return_resolution_id	UUID	NOT NULL	—	Composite FK → returns.return_resolution
return_authorization_line_id	UUID	NOT NULL	—	Composite FK → returns.return_authorization_line — the cap trigger's own grouping key, shared across possibly-multiple resolution events over time (e.g. partial store credit now, more later)
sale_refund_line_id	UUID	nullable	—	Composite FK → pos.sale_refund_line, populated only when the parent return_resolution.resolution_type='refund'
resolved_qty	numeric	NOT NULL	—
resolved_amount_cents	bigint	NOT NULL	—	Capped per the trigger below; when sale_refund_line_id is populated, must equal sale_refund_line.refunded_amount_minor_units for the same row (reconciliation, not restatement)
created_at	timestamptz	NOT NULL	now()

Indexes (2): PK on id; return_resolution_line_tenant_id_return_authorization_line_id_idx on (tenant_id,return_authorization_line_id) — the trigger's own aggregation key. No UNIQUE(id, tenant_id) — this table is not itself an FK target from any other table in this module.

Trigger function: returns.validate_and_apply_resolution_line(), fired BEFORE INSERT as the module's cap-and-reconcile-and-maintain trigger, doing 4 things in one invocation:

1. An explicit NULL guard — rejects resolved_qty IS NULL OR resolved_amount_cents IS NULL outright before any comparison runs. Added at lock-gate verification: the two IF ... > ... THEN RAISE cap checks below are plain comparisons, not the NULL-safe UPDATE ... WHERE ... RETURNING idiom check_and_reserve_source_line() uses — Postgres treats IF <NULL> as false, so without this guard a NULL input would silently skip both caps. Column-level NOT NULL on both operands already made this unreachable in practice, but the guard makes the trigger's own logic independently correct rather than relying on that coincidence (see PROJECT_DECISIONS #61 addendum).
2. The cap — sums resolved_amount_cents across every sibling return_resolution_line row sharing the same return_authorization_line_id, regardless of which return_resolution header each belongs to, against that line's own frozen eligible_refund_cents ceiling. Race-free via an explicit SELECT ... FOR UPDATE row lock taken on the RA line before the SUM, serializing concurrent resolution-line inserts against the same line — the correct analogue of purchasing.vendor_credit_line's own grouping-by-child-FK cap logic, re-keyed to the field that actually needs capping.
3. sale_refund_line reconciliation — when sale_refund_line_id is populated, requires resolved_amount_cents to exactly equal sale_refund_line.refunded_amount_minor_units for that row, keeping sale_refund_line the one authoritative source for a true refund amount rather than letting this table restate an independent, drift-prone figure.
4. credited_qty maintenance — atomically writes back to return_authorization_line.credited_qty (capped against authorized_qty, the peer counter to received_qty), in the same invocation, mirroring the capped-write-back pattern receiving.trg_goods_receipt_line_check_over_receipt_tolerance() established for purchase_order_line.received_qty.

Proportional clawback, live-reproduced (per #61, Architect Decision 2 — the capability this whole 2-pass session exists for): buy 5 shrubs with 100 loyalty points earned + a $10 line-scoped offer redeemed → return 2 of 5 → exactly 40 points clawed back (2/5 of 100) and exactly $4.00 of offer budget released (2/5 of $10), via the Pass-1-built proportional mechanism; a second partial return (1 more, cumulative 3/5) correctly clawed back an ADDITIONAL 20 points + $2.00, cumulative 60 points/$6.00 — proving no double-clawback; an over-clawback attempt (70 more, would total 130 > 100) was correctly rejected by Pass 1's own cumulative-cap tracker.

---

returns.return_receipt (14 cols, LIGHT autonomy pack) — mirrors receiving.goods_receipt

The physical return-receipt header — one row per physical delivery event of returned goods against an RA. Carries LIGHT attribution only (received_by_actor_id/voided_by_actor_id/void_reason — no created_by_actor_id, no automation_source, no review seam) — a lighter footprint than receiving.goods_receipt's own FULL pack, since the RMA-approval judgment already happened upstream on the FULL return_authorization.

Tenant-scoped. RLS enabled — return_receipt_tenant_isolation. Soft delete: deleted_at. updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—
return_authorization_id	UUID	NOT NULL	—	Composite FK → returns.return_authorization
site_id	UUID	NOT NULL	—	Composite FK → multi_loc.site
received_at	timestamptz	NOT NULL	now()
received_by_actor_id	UUID	nullable	—	FK → identity.actor
idempotency_key	text	nullable	—	NULL-safe UNIQUE per tenant_id — dedup for a future ReturnsService
status	text	NOT NULL	'draft'	CHECK IN (draft,posted,voided)
voided_at	timestamptz	nullable	—	Void attribution
voided_by_actor_id	UUID	nullable	—	FK → identity.actor
void_reason	text	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained
deleted_at	timestamptz	nullable	—	Soft delete

CHECK constraints (1): chk_return_receipt_status.

Indexes (3): PK on id; return_receipt_id_tenant_id_unique UNIQUE(id,tenant_id) (prerequisite for return_receipt_line's own composite FK); return_receipt_tenant_id_idempotency_key_unique UNIQUE(tenant_id,idempotency_key) WHERE idempotency_key IS NOT NULL AND deleted_at IS NULL (NULL-safe, matches receiving.goods_receipt's own idempotency pattern).

---

returns.return_receipt_line (16 cols, no autonomy pack, mutable) — mirrors receiving.goods_receipt_line

Mutable — disposition/condition can be revised pre-finalization, mirroring goods_receipt_line. This is the corrected, second-round shape of receiving's own posting trigger (which needed 2 independent verification passes to get right) — applied here from day one, not repeating receiving's own first, weaker draft.

Tenant-scoped, mutable. RLS enabled — return_receipt_line_tenant_isolation. updated_at: trigger-maintained via platform.set_updated_at(). Soft delete: deleted_at.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—
return_receipt_id	UUID	NOT NULL	—	Composite FK → returns.return_receipt
return_authorization_line_id	UUID	NOT NULL	—	Composite FK → returns.return_authorization_line
received_qty	numeric	NOT NULL	—	Caller-supplied request — the trigger derives the actual absorbed amount, never trusting this raw value directly
disposition	text	NOT NULL	—	CHECK IN (restock,scrap,repair,quarantine,return_to_vendor) — the actual outcome, vs. the RA line's requested one
condition_assessment	jsonb	nullable	—	e.g. {"grade": "B", "notes": "pot cracked, plant healthy"}
lot_id	UUID	nullable	—	Composite FK → inventory.lot
unit_cost_cents	bigint	NOT NULL	—
return_cost_override_cents	bigint	nullable	—	The "original lot no longer exists" case
stock_movement_id	UUID	nullable	—	Composite FK → inventory.stock_movement
stock_movement_line_id	UUID	nullable	—	Composite FK → inventory.stock_movement_line — the real line-grain movement linkage
reversal_of_return_receipt_line_id	UUID	nullable	—	Self-referencing composite FK → returns.return_receipt_line, mirrors goods_receipt_line exactly
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained
deleted_at	timestamptz	nullable	—	Soft delete

CHECK constraints (1): chk_return_receipt_line_disposition.

Indexes (3): PK on id; return_receipt_line_id_tenant_id_unique UNIQUE(id,tenant_id) (prerequisite for this table's own self-referencing reversal FK); return_receipt_line_tenant_id_return_receipt_id_idx on (tenant_id,return_receipt_id). No dedicated index on lot_id/stock_movement_id/stock_movement_line_id/reversal_of_return_receipt_line_id — a disclosed gap, matching receiving.goods_receipt_line's own identical carried-forward gap, not asserted otherwise.

Trigger function: returns.post_and_cap_return_receipt_line(), fired AFTER INSERT as trg_return_receipt_line_post_and_cap. The persisted line has already passed ordinary-caller RLS, FKs, and row constraints. The definer wrapper re-reads it by ID, locks and derives the authoritative receipt/authorization/source facts, calculates the absorbable quantity (v_absorbed = LEAST(received_qty request, authorized_qty − received_qty already posted)), posts inventory.stock_movement/stock_movement_line (movement_type='returned', source_module='returns') atomically using that derived quantity, and writes the derived links/counters back through the narrow owner policy. It never trusts caller-supplied tenant or source attribution as authority.

Tolerance policy: BLOCK, not flag — a deliberate, disclosed deviation from receiving.goods_receipt_line's own "flag" default: a customer physically returning goods twice against the same authorization is a materially more dangerous default to leave un-gated pending review than a vendor's over-shipment, so an over-tolerance receipt attempt here is rejected outright rather than merely flagged for later review.

Idempotent inventory posting, live-reproduced (per #61): a return_receipt_line insert posts a real inventory.stock_movement; a replayed/duplicate receipt attempt against an already-fully-received line is BLOCKED (not flagged), confirmed exactly 1 stock_movement row exists afterward — never double-posted. The reversal_of_return_receipt_line_id self-referencing FK (mirroring goods_receipt_line's own precedent) resolves correctly.

---

The seams

The discount-allocation freeze (return_authorization_line's reason for existing). pos.sale_line carries pure per-SKU pricing-rule resolution — no discount/allocation column exists there at all (write-once, append-only, Pricing's Hard Contract 1). A basket-wide offers/rewards discount has zero per-line breakdown anywhere in the codebase before this module: offers.check_and_sync_offer_budget()'s own trigger body literally gives up attributing a basket-wide discount to a line (RAISE EXCEPTION ... 'has no sale_line_id to verify cost against'), and rewards.loyalty_point_ledger has no sale_line_id column at all. returns never touches pos.sale_line and never re-derives this later — it reconstructs and freezes a per-unit eligible-refund ceiling once, at RA-creation time, using the documented proportional-by-pre-discount-extended-price allocation policy, and discloses which reconstruction path was used per line via allocation_source.

The aggregate-cap seam (return_source_line_tracker). pos.sale_line/orders.order_line are append-only and off-limits for a maintained counter column — the same structural constraint offers/rewards already solved once via a dedicated tracker table. return_source_line_tracker mirrors offers.offer_redemption_reversal_tracker's shape exactly, generalized to 2 mutually-exclusive nullable source columns instead of 1 non-nullable one (the real ON CONFLICT-predicate bug this generalization introduced, and its fix, are documented under the tracker's own table section above).

The proportional loyalty/offer clawback seam. return_resolution.loyalty_reversal_ledger_id/.offer_reversal_redemption_id link to 'reverse'-typed rows written using rewards/offers' own now-proportional mechanism (Pass 1, PROJECT_DECISIONS #60) — returns never re-implements loyalty/offer math, exactly as it never re-implements refund math. This capability was originally deferred by the design proposal's own Block 4 as out of scope for v1, since at design time rewards.sync_loyalty_account_balance() hard-enforced exact-full-negation-only and offers.check_and_sync_offer_budget() had no magnitude check at all on a reversal. Pass 1 closed both gaps in the same session, immediately before this build, so this module implements proportional clawback as a real, working capability from day one (Architect Decision 2) — not the deferred placeholder the original design proposed.

The no-physical-receipt warranty path. A return_type='warranty_claim' RA with physical_receipt_required=false never gets a return_receipt/return_receipt_line row at all. Its return_authorization_line.received_qty stays 0 forever — fine, since received_qty/credited_qty are independent peer counters (chk_return_authorization_line_qty_rollup), not a chain. Resolution proceeds directly via return_resolution.resolution_type='replacement'; credited_qty reaches authorized_qty via returns.validate_and_apply_resolution_line()'s own write-back while received_qty never leaves 0. Live-reproduced (per #61): a return_type='warranty_claim' RA with physical_receipt_required=false gets NO return_receipt at all, resolves directly via resolution_type='replacement', and credited_qty reached authorized_qty (1) while received_qty stayed 0 forever.

The inventory-posting seam. Both return_source_line_tracker's aggregate cap and returns.post_and_cap_return_receipt_line()'s derive-and-post trigger reuse this codebase's own proven atomic patterns verbatim — the UPDATE ... WHERE ... <= ceiling RETURNING shape (offers.check_and_sync_offer_budget()'s own precedent) and the derive-then-write-back-atomically shape (receiving's own corrected, second-round trigger). returns never writes inventory.stock directly — only inventory.stock_movement/stock_movement_line, exactly like every other module that posts inventory movements.

Cross-tenant RLS rejection, live-reproduced (per #61): a crafted cross-tenant return_authorization insert (claiming a different tenant_id) is rejected with 42501.

---

Companion reopens (5, bundled into this same migration — zero column/table impact on any of the 5)

Per PROJECT_DECISIONS #61: pos.sale_refund/sale_refund_line and orders.order_header/order_line each gained UNIQUE(id, tenant_id) (all 4 were PK-only before this build — the prerequisite for returns' own composite FKs into them). inventory.stock_movement.source_module, approvals.approval_request.source_module, and files.attachment.entity_type CHECKs were each widened to accept a returns-related value ('returns', 'returns', and 'return_authorization' respectively). All 5 reopens are constraint/CHECK-shape only — pos, orders, inventory, approvals, and files are all re-locked with zero table/column count change.

The design's own originally-planned rewards/offers reopens are obsolete and were NOT applied. The design proposal (pre-Pass-1) had planned 2 new partial-unique indexes on rewards/offers (UNIQUE (reversed_ledger_id) WHERE entry_type='reverse' and the offers equivalent) to close same-row double-reversal. Pass 1 (#60) already built the correct, more general mechanism (the cumulative-cap tracker tables), and the design's own proposed unique constraints would have been actively wrong given proportional reversal is now legal — a unique constraint on the reference column itself would reject a second legitimate partial reversal outright.

---

returns — Design Patterns Summary

The real bug found and fixed during this build's own live-reproduction pass

return_source_line_tracker's lazy-creation INSERT ... ON CONFLICT clause, in its first draft, omitted the WHERE predicate needed to match a partial unique index. Unlike Pass 1's own single-nullable-column trackers (each keyed on exactly one non-nullable FK column), this tracker has 2 mutually-exclusive nullable alternative columns (sale_line_id/order_line_id), so ON CONFLICT needed an explicit WHERE sale_line_id IS NOT NULL (or the order_line_id equivalent) to correctly target the matching partial index. Found and fixed during this build's own live-reproduction pass, not by a separate design-phase verification lens.

NULL-in-CHECK sweep

Every new CHECK constraint across all 9 tables references only NOT NULL columns or uses an explicit IS NULL/IS NOT NULL-disjunctive form — confirmed none is reachable via a NULL-bypass (per PROJECT_DECISIONS #61). Notable examples: chk_return_authorization_warranty_requires_type (warranty_id IS NULL OR return_type = 'warranty_claim'); chk_return_source_line_tracker_exactly_one_source ((sale_line_id IS NOT NULL) != (order_line_id IS NOT NULL)); chk_return_resolution_refund_requires_sale_refund (resolution_type != 'refund' OR pos_sale_refund_id IS NOT NULL).

Column-count reconciliation

Table	Cols
return_authorization	29
return_authorization_line	28
return_source_line_tracker	10
return_resolution	21
return_resolution_line	8
return_receipt	14
return_receipt_line	16
return_reason	10
warranty	16
Total	152

Confirmed identical across the Drizzle schema files, the migration, and the live DB via information_schema.columns, schema returns — zero drift (PROJECT_DECISIONS #61's own Section 4 Item A: "column drift: zero, 152/152 confirmed live").

Service layer

No ReturnsService yet — schema-only this pass, matching every other module's own established schema-only-at-build-time pattern. The aggregate cap (return_source_line_tracker), the resolution-line cap/reconciliation/write-back (returns.validate_and_apply_resolution_line()), and the inventory-posting derive-and-cap (returns.post_and_cap_return_receipt_line()) are, however, not waiting on that service layer to be correct — all 3 triggers enforce their own invariants directly and atomically on every relevant insert, regardless of whether any service ever calls it via raw SQL or through a future ReturnsService method.

JSONB columns

• return_authorization.risk_factors — e.g. {"return_velocity_30d": 3, "days_since_purchase": 2, "anonymous": false}.
• return_receipt_line.condition_assessment — e.g. {"grade": "B", "notes": "pot cracked, plant healthy"}.
• warranty.terms_snapshot — frozen copy of inventory.item_variant.guarantee_terms at issuance, e.g. {"duration_days":365,"type":"plant_guarantee"}.
• return_authorization.decision_provenance, return_authorization_line.decision_provenance, return_resolution.decision_provenance — the project-wide reason/evidence/confidence/memory_refs convention, same shape as every other FULL-tier table's decision_provenance column codebase-wide.

Triggers, full list (3 new functions + reused shared triggers)

Trigger	Table	Fires	Function
set_updated_at	8 of 9 tables (all except return_resolution_line)	BEFORE UPDATE	platform.set_updated_at() (shared, reused)
trg_return_resolution_line_append_only	return_resolution_line	BEFORE UPDATE OR DELETE	platform.reject_append_only_mutation() (shared, reused)
trg_return_authorization_line_check_and_reserve	return_authorization_line	BEFORE INSERT	returns.check_and_reserve_source_line() (NEW)
trg_return_resolution_line_validate_against_authorization_line	return_resolution_line	BEFORE INSERT	returns.validate_and_apply_resolution_line() (NEW)
trg_return_receipt_line_post_and_cap	return_receipt_line	AFTER INSERT	returns.post_and_cap_return_receipt_line() (Inventory Core companion)

Regression tests

apps/api/src/returns/__tests__/returns-schema.spec.ts — new file, 31/31 passing, covering table/RLS/column-count existence, discount allocation, sequential + concurrent aggregate cap, proportional clawback + over-clawback rejection, idempotent posting + BLOCK-not-flag over-receipt, warranty no-receipt flow + the conditional-warranty CHECK, restocking fee arithmetic, unreferenced return, cross-tenant RLS, and all 5 companion-reopen constraint/CHECK confirmations (PROJECT_DECISIONS #61). Full apps/api suite: 1083/1083 passing, confirmed serially via --runInBand — one pre-existing, already-disclosed, unrelated cross-file concurrency flake in admin-tenants.spec.ts intermittently reproduces under parallel Jest workers, not caused by this build.

cd apps/api && npx jest src/returns/__tests__/returns-schema.spec.ts --forceExit

Open items carried forward

No ReturnsService yet — the accept/resolve/void workflow and any future AI risk-tiering agent surfaces are still to be designed at the service layer. return_resolution.store_credit_reference is now deprecated in place (2026-07-18) — billing.store_credit_account/store_credit_transaction exist and store_credit_transaction_id is the real, enforced pointer (see the table section above). Restocking-fee taxability is undecided — a human product decision, not a schema gap (restocking_fee_cents is stored either way). return_authorization.risk_score is computed from live raw joins each time — no cached customer-return-velocity/fraud-signal counter exists yet, deferred to a future crm/reporting surface. warranty.signature_ref remains a deferred Files forward-ref (plain text, no FK), folded into the existing Files FK-wiring bundle already logged in OPEN_ITEMS. crm.customer stays a bare (non-composite) FK from return_authorization.customer_id — matches the universal existing convention, not a gap introduced by this module. return_source_line_tracker rows are never soft-deleted or cleaned up — a theoretical, not practical, orphan concern, since pos.sale_line/orders.order_line are both append-only/soft-delete-only and will never be hard-purged. No dedicated index exists yet on return_receipt_line.lot_id/.stock_movement_id/.stock_movement_line_id/.reversal_of_return_receipt_line_id — matches receiving.goods_receipt_line's own identical carried-forward gap; add if a future query pattern needs it.

Inventory Core companion reopen — 2026-07-15 task run

The receipt-line posting trigger is now a narrow protected schema path. returns.post_and_cap_return_receipt_line() is SECURITY DEFINER, owned by NOLOGIN/non-BYPASSRLS returns_invariant_owner, uses search_path=pg_catalog, and derives tenant, receipt identity, source timestamp, variant, capped quantity, cost, actor, and idempotency from locked Returns rows. It calls the private Inventory primitive, which creates exactly one posting_integrity='complete' header and one child line while updating stock atomically. PUBLIC and all ordinary/executor-role EXECUTE paths are revoked. source_module='returns' remains present in both live PostgreSQL and Drizzle.