inventory — Phase 7

Locked at 21 tables, 238 cols (232 at lock 2026-06-09; +2 cols added 2026-06-10 for POS guarantee support — has_guarantee + guarantee_terms on item_variant; +1 col added 2026-06-10 for Stripe Tax — stripe_tax_code on item; +1 col added 2026-06-11 for Files lifecycle — file_id on item_image; +2 cols added 2026-06-11 for Search FTS — search_vector on item + search_vector on item_variant. See Search module). Owns the full item/catalog model plus all stock-tracking operations. The item layer is vertical-neutral per PROJECT_DECISIONS "Inventory Item Model (Locked 2026-06-09)": one inventory.item table with item_type discriminator and JSONB attributes for type-specific data. Operational sub-schemas (stock, lots, counts) are nursery-first.

Invariants

1. All quantity changes go ONLY through InventoryService, transactionally. No direct writes to stock.on_hand_qty outside the movement service.
2. Lot coherence: when lots are in use, stock.on_hand_qty = SUM(stock_lot.quantity) for the same (tenant_id, site_id, variant_id, inventory_location_id).
3. Reservation coherence: stock.reserved_qty = SUM(active stock_reservation.quantity) for the same (tenant_id, site_id, variant_id, inventory_location_id).
4. Append-only audit trail: stock_movement + stock_movement_line are immutable — no updated_at, no deleted_at. Once written, never modified or removed.

Cross-Phase Foreign Keys (inventory)

All FK targets exist at Phase 7 migration time — all enforced at migration, no deferred constraints.

Column	Target	Status
*.tenant_id	platform.tenant	Phase 1 exists — enforced.
item_variant.sell_uom_id	shared.unit_of_measure	Phase 2 exists — enforced.
item_variant.stock_uom_id	shared.unit_of_measure	Phase 2 exists — enforced.
item_variant.purchase_uom_id	shared.unit_of_measure	Phase 2 exists — enforced (nullable).
inventory_location.capacity_uom_id	shared.unit_of_measure	Phase 2 exists — enforced (nullable).
*.site_id	multi_loc.site	Phase 4 exists — enforced.
stock_movement.performed_by	identity.identity_user	Phase 3 exists — enforced (nullable).
stock_count.started_by	identity.identity_user	Phase 3 exists — enforced (nullable).
stock_count.reconciled_by	identity.identity_user	Phase 3 exists — enforced (nullable).

---

CATALOG GROUP

inventory.item

Master item record. Single table for all product types: plants, hard goods, services, kits. item_type discriminator; type-specific data in attributes JSONB. Vertical-neutral per PROJECT_DECISIONS "Inventory Item Model (Locked 2026-06-09)".

Tenant-scoped. Master data — no site_id.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
item_type	text	NOT NULL	—	CHECK IN ('plant','hard_good','service','kit')
name	text	NOT NULL	—	Display name
slug	text	NOT NULL	—	URL-safe identifier; normalized lowercase in service layer
description	text	nullable	—	Rich-text description
brand	text	nullable	—	Brand / manufacturer name
status	text	NOT NULL	'active'	CHECK IN ('active','discontinued','draft')
attributes	JSONB	nullable	'{}'	Type-specific data. Shape varies by item_type: plants include botanical facts; hard goods include manufacturer/model; kits include assembly notes. Graduation rule: promote to column only when query performance demands.
stripe_tax_code	text	nullable	—	Stripe Tax product tax code (e.g. 'txcd_99999999'). Used by PaymentsService to pass product-level tax classification to Stripe Tax. NULL = inherits tenant default.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
search_vector	tsvector	NOT NULL	—	GENERATED ALWAYS AS (to_tsvector('english', coalesce(name,'') || ' ' || coalesce(description,'') || ' ' || coalesce(item_type,''))) STORED. (FTS touch 2026-06-11 — Search module.) Maintained by Postgres; never written directly.

14 columns. (FTS touch 2026-06-11: search_vector added. Was 13 cols.)

Indexes:

• PK on id
• on (tenant_id)
• UNIQUE on (tenant_id, slug) WHERE deleted_at IS NULL
• GIN on (search_vector) — full-text search (tsvector)
• GIN on (name gin_trgm_ops) — fuzzy / partial / prefix name search (pg_trgm)

---

inventory.item_variant

A sellable / stockable variant of an item — the unit customers buy, POS rings up, and inventory tracks. Every item has at least one variant. Carries pricing, UoMs, weight, dimensions, and the kit flag.

Tenant-scoped. Master data — no site_id.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
item_id	UUID	NOT NULL	—	FK → inventory.item
sku	text	NOT NULL	—	Tenant-unique SKU
name	text	NOT NULL	—	Variant display name, e.g. "4-inch pot"
base_price_cents	bigint	NOT NULL	—	List price in cents
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
avg_cost_cents	bigint	NOT NULL	0	Running weighted-average cost. Recalculated by InventoryService on every receiving movement: new_avg = (prev_avg × existing_qty + unit_cost × received_qty) / (existing_qty + received_qty).
sell_uom_id	UUID	NOT NULL	—	FK → shared.unit_of_measure — UoM for sales / POS
stock_uom_id	UUID	NOT NULL	—	FK → shared.unit_of_measure — UoM for stock counts and movements
purchase_uom_id	UUID	nullable	—	FK → shared.unit_of_measure — UoM for purchase orders; NULL = same as stock_uom
purchase_to_stock_factor	numeric	nullable	—	Conversion: 1 purchase UoM = N stock UoM units (e.g. 1 case = 24 each)
sell_to_stock_factor	numeric	nullable	—	Conversion: 1 sell UoM = N stock UoM units
weight	numeric	nullable	—	Item weight (numeric value; unit in weight_uom)
weight_uom	text	nullable	—	Free text: 'lb', 'kg', etc. (display only — not FK to shared.unit_of_measure)
dimensions	JSONB	nullable	—	{"length": 4, "width": 4, "height": 6, "unit": "in"}
is_kit	boolean	NOT NULL	false	True when this variant is a kit assembled from components in kit_component
kit_stock_mode	text	nullable	—	CHECK IN ('explode_at_sale','stocked_kit'). NULL iff is_kit = false; required iff is_kit = true.
track_inventory	boolean	NOT NULL	true	False for services or free promotional items with no stock tracking
status	text	NOT NULL	'active'	CHECK IN ('active','discontinued')
attributes	JSONB	nullable	'{}'	Variant-specific type data (e.g. pot size, color) beyond item-level attributes
has_guarantee	boolean	NOT NULL	false	True when this variant ships with a product guarantee. Set at catalog time; POS reads this to trigger guarantee creation at sale. (Added 2026-06-10 for POS guarantee support — see pos.guarantee.)
guarantee_terms	JSONB	nullable	—	Product-level guarantee configuration. NULL when has_guarantee = false. Shape: {"duration_days": 365, "type": "plant_guarantee", "notes": "..."}. POS copies this snapshot into pos.guarantee at sale time so terms are preserved even if the catalog-level config changes later. (Added 2026-06-10 for POS guarantee support.)
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
search_vector	tsvector	NOT NULL	—	GENERATED ALWAYS AS (to_tsvector('english', coalesce(sku,'') || ' ' || coalesce(name,''))) STORED. (FTS touch 2026-06-11 — Search module.) Maintained by Postgres; never written directly.
(table CHECK)	—	—	—	CHECK (is_kit = false AND kit_stock_mode IS NULL) OR (is_kit = true AND kit_stock_mode IS NOT NULL)
(table CHECK)	—	—	—	CHECK (has_guarantee = false OR guarantee_terms IS NOT NULL) — prevents POS from copying a NULL snapshot into pos.guarantee.terms_snapshot at sale time

27 columns. (Updated 2026-06-10: +2 cols has_guarantee + guarantee_terms for POS guarantee support. FTS touch 2026-06-11: +1 search_vector. Was 26 cols.)

Indexes:

• PK on id
• on (tenant_id)
• on (item_id)
• UNIQUE on (tenant_id, sku) WHERE deleted_at IS NULL
• GIN on (search_vector) — full-text search (tsvector)
• GIN on (sku gin_trgm_ops) — fuzzy / partial / prefix SKU search (pg_trgm); POS ring-up hot path
• GIN on (name gin_trgm_ops) — fuzzy / partial / prefix variant name search (pg_trgm). (F1 fix at Search lock 2026-06-11: partial variant-name search — e.g. "4-in" → "4-inch pot" — requires this; tsvector alone misses partial matches.)

---

inventory.category

Hierarchical product category tree. Self-referencing parent_id supports multi-level nesting. Categories are tenant-defined.

Tenant-scoped. Master data — no site_id.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
parent_id	UUID	nullable	—	FK → inventory.category (self) — NULL = root category
name	text	NOT NULL	—	Display name
slug	text	NOT NULL	—	URL-safe identifier
sort_order	integer	NOT NULL	0	UI ordering within parent
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id)
• on (parent_id)
• UNIQUE on (tenant_id, parent_id, slug) WHERE deleted_at IS NULL

---

inventory.item_category

M:N join between items and categories. Hard-delete (no soft delete) — removing an item from a category is a real deletion.

Tenant-scoped. Join table — no updated_at, no deleted_at.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
item_id	UUID	NOT NULL	—	FK → inventory.item
category_id	UUID	NOT NULL	—	FK → inventory.category
created_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (tenant_id)
• UNIQUE on (item_id, category_id) — hard-delete table; standard unique is correct (no soft-delete collisions)
• on (category_id)

---

inventory.tag

Tenant-defined free-form tags for items. Supports flexible grouping beyond the category tree.

Tenant-scoped. Master data — no site_id.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
name	text	NOT NULL	—	Display name
slug	text	NOT NULL	—	URL-safe identifier
color	text	nullable	—	Hex color for UI display, e.g. '#4CAF50'
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id)
• UNIQUE on (tenant_id, slug) WHERE deleted_at IS NULL

---

inventory.item_tag

M:N join between items and tags. Hard-delete — removing a tag from an item is a real deletion.

Tenant-scoped. Join table — no updated_at, no deleted_at.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
item_id	UUID	NOT NULL	—	FK → inventory.item
tag_id	UUID	NOT NULL	—	FK → inventory.tag
created_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (tenant_id)
• UNIQUE on (item_id, tag_id) — hard-delete table; standard unique is correct
• on (tag_id)

---

inventory.barcode

Barcodes associated with a variant — UPC, EAN, Code128, or tenant-assigned. A variant may have multiple barcodes (e.g. supplier UPC + tenant label); at most one is is_primary.

Tenant-scoped. Master data — no site_id.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
variant_id	UUID	NOT NULL	—	FK → inventory.item_variant
barcode	text	NOT NULL	—	Barcode value
barcode_type	text	nullable	—	CHECK IN ('UPC','EAN','CODE128','own') — NULL = unspecified
is_primary	boolean	NOT NULL	false	POS scanner preference; at most one per variant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id)
• on (variant_id)
• UNIQUE on (tenant_id, barcode) WHERE deleted_at IS NULL — barcode values globally unique per tenant
• UNIQUE on (variant_id) WHERE is_primary = true AND deleted_at IS NULL — at most one primary barcode per variant

---

inventory.item_image

Product images stored in Cloudflare R2 (via storage_key). An image belongs to either an item (item-level hero) or a variant (variant-specific), never both — enforced by a table CHECK. At most one is_primary per item; at most one per variant.

Locked-inventory additive touch 2026-06-11: file_id added when Files module was locked. item_image retains display ownership (primary flag, sort order, alt text, storage_key/url for direct CDN access); files.file owns the R2 object metadata + lifecycle + quota. item_image.storage_key and files.file.r2_key should match on the same R2 object — file_id is the authoritative storage record pointer. 13 cols (was 12).

Tenant-scoped. Master data — no site_id.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
item_id	UUID	nullable	—	FK → inventory.item — NULL iff variant_id IS NOT NULL
variant_id	UUID	nullable	—	FK → inventory.item_variant — NULL iff item_id IS NOT NULL
storage_key	text	NOT NULL	—	Cloudflare R2 object key — matches files.file.r2_key for the same object
url	text	nullable	—	CDN URL; may be derived from storage_key at query time
alt_text	text	nullable	—	Accessibility alt text
sort_order	integer	NOT NULL	0	Display ordering within item / variant image set
is_primary	boolean	NOT NULL	false	Hero image flag
file_id	UUID	nullable	—	FK → files.file — FORWARD-REF (Files module). The files.file row for this R2 object; owns lifecycle, quota, and signed-URL generation. Nullable: existing rows pre-Files lock do not have a files.file row yet; back-fill when Files service is wired up.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
(table CHECK)	—	—	—	CHECK ((item_id IS NOT NULL AND variant_id IS NULL) OR (item_id IS NULL AND variant_id IS NOT NULL)) — exactly one of item_id / variant_id must be set

Indexes:

• PK on id
• on (tenant_id)
• on (item_id)
• on (variant_id)
• UNIQUE on (item_id) WHERE item_id IS NOT NULL AND is_primary = true AND deleted_at IS NULL — at most one primary image per item
• UNIQUE on (variant_id) WHERE variant_id IS NOT NULL AND is_primary = true AND deleted_at IS NULL — at most one primary image per variant

---

VARIANT STRUCTURE GROUP

inventory.option_type

Defines the option dimensions for a variant matrix — e.g. "Size", "Color", "Pot Type". Each option type belongs to a single item.

Tenant-scoped. Master data — no site_id.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
item_id	UUID	NOT NULL	—	FK → inventory.item
name	text	NOT NULL	—	Option dimension name, e.g. 'Size', 'Color'
sort_order	integer	NOT NULL	0
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id)
• on (item_id)
• UNIQUE on (item_id, name) WHERE deleted_at IS NULL

---

inventory.variant_option

Assigns a specific option value to a variant for one option type — e.g. variant "4-inch Blue Pot" has option_type = Size, value = "4-inch" and option_type = Color, value = "Blue".

Tenant-scoped. Master data — no site_id.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
variant_id	UUID	NOT NULL	—	FK → inventory.item_variant
option_type_id	UUID	NOT NULL	—	FK → inventory.option_type
value	text	NOT NULL	—	Option value, e.g. '4-inch', 'Red'
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id)
• on (variant_id)
• on (option_type_id)
• UNIQUE on (variant_id, option_type_id) WHERE deleted_at IS NULL — each variant has at most one value per option dimension

---

LOCATIONS GROUP

inventory.inventory_location

A named storage location within a site — zones, bins, benches, rows, shelves. Self-referencing parent_id allows location hierarchies (zone → row → bin). Referenced by stock, stock_lot, stock_movement_line, and stock_count_line.

Tenant-scoped. Transactional — carries site_id.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
site_id	UUID	NOT NULL	—	FK → multi_loc.site
parent_id	UUID	nullable	—	FK → inventory.inventory_location (self) — NULL = top-level location
name	text	NOT NULL	—	Display name, e.g. "Zone A", "Bin 12"
location_type	text	NOT NULL	—	CHECK IN ('zone','bin','bench','row','shelf','other')
capacity	numeric	nullable	—	Maximum capacity (in capacity_uom)
capacity_uom_id	UUID	nullable	—	FK → shared.unit_of_measure — NULL iff capacity IS NULL
status	text	NOT NULL	'active'	CHECK IN ('active','inactive')
sort_order	integer	NOT NULL	0
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
(table CHECK)	—	—	—	CHECK ((capacity IS NULL AND capacity_uom_id IS NULL) OR (capacity IS NOT NULL AND capacity_uom_id IS NOT NULL)) — capacity_uom_id must be set iff capacity is set

Indexes:

• PK on id
• on (tenant_id)
• on (site_id)
• on (parent_id)
• UNIQUE on (site_id, parent_id, name) WHERE deleted_at IS NULL

---

STOCK GROUP

inventory.stock

Current stock level per variant per site per location slot. No deleted_at — rows are created on first receipt and updated by InventoryService; a zero-quantity row is retained, not deleted. available_qty is a generated column (on_hand_qty − reserved_qty).

Tenant-scoped. Transactional — carries site_id. No deleted_at.

RLS: enabled — tenant isolation policy on tenant_id.

Two partial uniques cover the nullable inventory_location_id case — Postgres NULL ≠ NULL prevents a standard 5-column unique from correctly constraining unlocated rows. See design note below.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
site_id	UUID	NOT NULL	—	FK → multi_loc.site
variant_id	UUID	NOT NULL	—	FK → inventory.item_variant
inventory_location_id	UUID	nullable	—	FK → inventory.inventory_location — NULL = unlocated stock at site level
on_hand_qty	numeric	NOT NULL	0	Current physical quantity
reserved_qty	numeric	NOT NULL	0	Quantity committed to open orders / reservations
available_qty	numeric	NOT NULL	—	GENERATED ALWAYS AS (on_hand_qty - reserved_qty) STORED
reorder_point	numeric	nullable	—	Trigger threshold for reorder alerts
reorder_qty	numeric	nullable	—	Suggested reorder quantity
min_qty	numeric	nullable	—	Minimum acceptable stock level
max_qty	numeric	nullable	—	Maximum stock level (capacity guard)
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (tenant_id)
• on (variant_id)
• on (site_id)
• UNIQUE on (tenant_id, site_id, variant_id, inventory_location_id) WHERE inventory_location_id IS NOT NULL — located stock rows
• UNIQUE on (tenant_id, site_id, variant_id) WHERE inventory_location_id IS NULL — unlocated stock rows

NULL location pattern: inventory_location_id = NULL means "unlocated — tracked at site level." A standard 5-column unique (tenant_id, site_id, variant_id, inventory_location_id) would permit duplicate unlocated rows because NULL ≠ NULL in Postgres. Two partial uniques (one with, one without location) are the correct fix. stock_lot uses the same two-partial-unique pattern for the same reason.

---

inventory.stock_movement

Header record for a stock quantity event. Immutable — append-only audit trail. One header, one or more lines (stock_movement_line). movement_type = 'adjusted' requires a reason_id (enforced by table CHECK).

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

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
site_id	UUID	NOT NULL	—	FK → multi_loc.site
movement_type	text	NOT NULL	—	CHECK IN ('received','sold','transferred','adjusted','counted','returned')
movement_date	timestamptz	NOT NULL	—	Business date of the movement (may differ from created_at)
reason_id	UUID	nullable	—	FK → inventory.stock_adjustment_reason — required when movement_type = 'adjusted'
source_module	text	NOT NULL	—	CHECK IN ('pos','orders','purchasing','inventory','production','system') — 'production' added for Production module movements via InventoryService
source_type	text	nullable	—	Type of originating record, e.g. 'sale', 'purchase_order'
source_id	UUID	nullable	—	PK of the originating record (no enforced FK — cross-module)
correlation_id	UUID	nullable	—	Groups related movements (e.g. all lines of a transfer)
idempotency_key	text	nullable	—	Client-supplied key to prevent duplicate movement submissions
performed_by	UUID	nullable	—	FK → identity.identity_user — NULL for system-generated movements
notes	text	nullable	—
created_at	timestamptz	NOT NULL	now()
(table CHECK)	—	—	—	CHECK (movement_type != 'adjusted' OR reason_id IS NOT NULL)

Indexes:

• PK on id
• on (tenant_id)
• on (site_id, movement_date) — date-range queries per site
• UNIQUE on (tenant_id, idempotency_key) WHERE idempotency_key IS NOT NULL — deduplication
• on (correlation_id) — group lookups

---

inventory.stock_movement_line

Line-level detail for a stock movement: which variant, which locations, which lot, quantity delta, and cost accounting. Immutable — append-only.

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

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
movement_id	UUID	NOT NULL	—	FK → inventory.stock_movement
variant_id	UUID	NOT NULL	—	FK → inventory.item_variant
from_location_id	UUID	nullable	—	FK → inventory.inventory_location — NULL for inbound movements
to_location_id	UUID	nullable	—	FK → inventory.inventory_location — NULL for outbound movements
lot_id	UUID	nullable	—	FK → inventory.lot — NULL when lot tracking not used
quantity_delta	numeric	NOT NULL	—	Signed quantity change (positive = stock in, negative = stock out)
unit_cost_cents	bigint	nullable	—	Cost per unit for this line (bigint, ISO 4217 cents)
previous_avg_cost_cents	bigint	nullable	—	item_variant.avg_cost_cents snapshot before this movement
new_avg_cost_cents	bigint	nullable	—	item_variant.avg_cost_cents after this movement
cost_impact_cents	bigint	nullable	—	quantity_delta × unit_cost_cents — net COGS impact of this line
created_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (tenant_id)
• on (movement_id)
• on (variant_id)
• on (lot_id)

---

inventory.stock_reservation

Reserves stock for open orders or holds, preventing oversell. No deleted_at — lifecycle managed via status. Nightly expiry sweep releases status = 'active' rows past expires_at.

Tenant-scoped. Transactional — carries site_id. No deleted_at.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
site_id	UUID	NOT NULL	—	FK → multi_loc.site
variant_id	UUID	NOT NULL	—	FK → inventory.item_variant
inventory_location_id	UUID	nullable	—	FK → inventory.inventory_location — NULL = site-level reservation
quantity	numeric	NOT NULL	—	Reserved quantity
source_type	text	NOT NULL	—	CHECK IN ('order','hold','transfer')
source_id	UUID	NOT NULL	—	PK of the originating record (no enforced FK — cross-module)
source_line_id	UUID	nullable	—	PK of the line within the originating record
status	text	NOT NULL	'active'	CHECK IN ('active','released','fulfilled','expired','cancelled')
expires_at	timestamptz	nullable	—	When reservation auto-expires; NULL = no expiry
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (tenant_id)
• on (variant_id, status) — available-stock calculation query
• on (source_type, source_id) — look up reservations by originating record
• on (expires_at) WHERE status = 'active' — expiry sweep

---

inventory.stock_adjustment_reason

Lookup table for adjustment reason codes — shrinkage, damage, cycle_count_correction, etc. Referenced by stock_movement.reason_id when movement_type = 'adjusted'.

Tenant-scoped. Master data — no site_id.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
code	text	NOT NULL	—	Short code, e.g. 'shrinkage', 'damage'
name	text	NOT NULL	—	Display name
is_active	boolean	NOT NULL	true
sort_order	integer	NOT NULL	0
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id)
• UNIQUE on (tenant_id, code) WHERE deleted_at IS NULL

---

COUNTS GROUP

inventory.stock_count

A stock-count session (cycle count, full count, or spot check). Lifecycle via status; no deleted_at — cancelled counts use status = 'cancelled'.

Tenant-scoped. Transactional — carries site_id. No deleted_at.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
site_id	UUID	NOT NULL	—	FK → multi_loc.site
inventory_location_id	UUID	nullable	—	FK → inventory.inventory_location — NULL = whole-site count
status	text	NOT NULL	'open'	CHECK IN ('open','counting','review','reconciled','cancelled')
count_type	text	NOT NULL	—	CHECK IN ('full','cycle','spot')
started_at	timestamptz	nullable	—	When counting began
completed_at	timestamptz	nullable	—	When all lines were counted
started_by	UUID	nullable	—	FK → identity.identity_user
reconciled_by	UUID	nullable	—	FK → identity.identity_user
notes	text	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (tenant_id)
• on (site_id, status) — open count lookups per site

---

inventory.stock_count_line

One line in a stock count — expected system quantity vs. actual counted quantity. variance is a generated column; it materializes once counted_qty is populated.

Tenant-scoped. No deleted_at — lines are not removed; cancel at the stock_count header level.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
count_id	UUID	NOT NULL	—	FK → inventory.stock_count
variant_id	UUID	NOT NULL	—	FK → inventory.item_variant
inventory_location_id	UUID	nullable	—	FK → inventory.inventory_location — NULL = unlocated
lot_id	UUID	nullable	—	FK → inventory.lot — NULL when lot tracking not in use
system_qty	numeric	NOT NULL	—	Snapshot of stock.on_hand_qty when count was created
counted_qty	numeric	nullable	—	Actual physical count; NULL until the line is counted
variance	numeric	nullable	—	GENERATED ALWAYS AS (counted_qty - system_qty) STORED — NULL until counted_qty is populated
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (tenant_id)
• on (count_id)
• on (variant_id)

---

LOTS GROUP

inventory.lot

A lot (batch) of received inventory — tracks provenance, expiry, and supplier lot numbers. Referenced by stock_lot, stock_movement_line, and stock_count_line. Must be created before those tables in the Phase 7 migration (see migration ordering below).

Tenant-scoped. Master data — no site_id.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
variant_id	UUID	NOT NULL	—	FK → inventory.item_variant
lot_number	text	NOT NULL	—	Tenant-assigned lot number
supplier_lot_number	text	nullable	—	Supplier's lot / batch number
received_at	timestamptz	nullable	—	Date received
expiry_date	date	nullable	—	Expiry / best-before date (date precision; not timestamptz)
source_type	text	nullable	—	CHECK IN ('purchase','production','adjustment')
source_id	UUID	nullable	—	PK of the originating record (no enforced FK — cross-module)
unit_cost_cents	bigint	nullable	—	Cost per unit when this lot was received (bigint, ISO 4217 cents)
status	text	NOT NULL	'active'	CHECK IN ('active','depleted','expired','quarantined')
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id)
• on (variant_id)
• UNIQUE on (tenant_id, variant_id, lot_number) WHERE deleted_at IS NULL
• on (expiry_date) WHERE status = 'active' — expiry sweep

---

inventory.stock_lot

Quantity of a specific lot at a specific site + location slot. SUM(stock_lot.quantity) for (tenant_id, site_id, variant_id, inventory_location_id) must equal stock.on_hand_qty. No deleted_at — zero-quantity rows are retained for audit.

Tenant-scoped. Transactional — carries site_id. No deleted_at.

RLS: enabled — tenant isolation policy on tenant_id.

Same two-partial-unique pattern as stock: nullable inventory_location_id requires separate partial uniques for located vs. unlocated rows (Postgres NULL ≠ NULL trap).

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
site_id	UUID	NOT NULL	—	FK → multi_loc.site
variant_id	UUID	NOT NULL	—	FK → inventory.item_variant
inventory_location_id	UUID	nullable	—	FK → inventory.inventory_location — NULL = unlocated
lot_id	UUID	NOT NULL	—	FK → inventory.lot
quantity	numeric	NOT NULL	—	Current lot quantity at this slot
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (tenant_id)
• on (lot_id)
• UNIQUE on (tenant_id, site_id, variant_id, inventory_location_id, lot_id) WHERE inventory_location_id IS NOT NULL — located lot rows
• UNIQUE on (tenant_id, site_id, variant_id, lot_id) WHERE inventory_location_id IS NULL — unlocated lot rows

---

KITS GROUP

inventory.kit_component

Bill-of-materials for a kit variant. Each row is one component and its required quantity. A kit variant (item_variant.is_kit = true) has one or more rows here. kit_variant_id must reference an item_variant with is_kit = true (enforced at service layer).

Tenant-scoped. Master data — no site_id.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
kit_variant_id	UUID	NOT NULL	—	FK → inventory.item_variant — must have is_kit = true
component_variant_id	UUID	NOT NULL	—	FK → inventory.item_variant — the component
quantity	numeric	NOT NULL	—	Required quantity per kit
sort_order	integer	NOT NULL	0
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id)
• on (kit_variant_id)
• on (component_variant_id) — "which kits contain this variant?" / delete-protection lookups
• UNIQUE on (kit_variant_id, component_variant_id) WHERE deleted_at IS NULL

---

inventory — Design Notes

Migration ordering (Phase 7)

lot must be created before stock_movement_line and stock_count_line (both carry lot_id FK → inventory.lot). Suggested order within Phase 7:

1. item → item_variant
2. category → item_category → tag → item_tag → barcode → item_image
3. option_type → variant_option
4. inventory_location
5. stock_adjustment_reason
6. lot ← must precede movement lines and count lines
7. stock → stock_lot
8. stock_movement → stock_movement_line
9. stock_reservation
10. stock_count → stock_count_line
11. kit_component

v1.0 scope vs. v1.5 deferrals

v1.0 (built now)	v1.5 (deferred — do NOT build now)
All 21 tables	variant_uom_conversion — line-level UoM conversion table
Two-partial-unique NULL-location pattern	stock_movement_line.from_site_id / to_site_id — line-level site for multi-site transfer accounting
Kit BOM via kit_component	—
Lot tracking via lot + stock_lot	—

Patterns recap

• Tenant-scoped throughout — tenant_id NOT NULL, RLS enabled on all 21 tables.
• Immutable audit tables (stock_movement, stock_movement_line) — no updated_at, no deleted_at. Append-only.
• Hard-delete join tables (item_category, item_tag) — no updated_at, no deleted_at. Removal = actual delete; standard UNIQUE (not partial) is correct for these.
• No-deleted_at operational tables (stock, stock_lot, stock_reservation, stock_count, stock_count_line) — lifecycle via status column or zero-quantity retention.
• Two-partial-unique pattern on stock and stock_lot: nullable inventory_location_id requires separate partial uniques for located vs. unlocated rows (Postgres NULL ≠ NULL trap).
• Generated columns: stock.available_qty = on_hand_qty - reserved_qty; stock_count_line.variance = counted_qty - system_qty.
• Cross-module source references (stock_movement.source_id, stock_reservation.source_id, lot.source_id) — plain UUID, no enforced FK. Cross-module lookups go through service layer.
• JSONB shapes: item.attributes and item_variant.attributes vary by item_type (see PROJECT_DECISIONS "Inventory Item Model"); item_variant.dimensions = {"length":4,"width":4,"height":6,"unit":"in"}.

Deferred notes (low-priority GAPs with written triggers)

Deferred item	Trigger for resolution
stock_movement.movement_type — add 'produced'	When Production module schema is designed — check whether a distinct movement type is needed vs. reusing 'received'
barcode.barcode_type — add 'QR'	When QR scanning is spec'd in the Inventory or POS module
item.attributes / item_variant.attributes — add per-item_type example JSONB shapes (plant / hard_good / service / kit)	When the plant data model receives a detailed spec (field-level JSONB schemas)
stock_movement_line — add composite reporting index (variant + date range via movement join)	When Reporting module queries against this table are defined

---