inventory — Phase 12 (module #12)

Authoritative live recount — Stock Transfer schema lock, 2026-07-16 task run: 29 base tables / 425 base-table columns / 1 view. The current pre-Transfer live/Drizzle baseline was 26 tables / 362 columns; Transfer adds exactly 3 tables / 63 columns. The view's eight columns are excluded from base-table arithmetic.

Stock Transfer schema lock — 2026-07-16

Transfer is built at the schema layer only:

• inventory.transfer — 26-column tenant-scoped header; draft, approval, one atomic shipment, partial-receipt, received, and cancellation lifecycle.
• inventory.transfer_line — 21-column line; one optional durable lot_id, exact reservation pointer, exact immutable outbound movement-line pointer, and monotonic quantity counters.
• inventory.transfer_reconciliation_event — 16-column append-only partial-receipt event with tenant-unique caller idempotency and resulting cumulative totals.

The exact Transfer subset is 3 tables / 63 columns / 28 CHECKs / 20 FKs / 11 triggers. The complete Inventory schema is 29 tables / 425 columns / 90 CHECKs / 138 FKs / 29 PKs / 19 UNIQUE constraints / 164 indexes / 46 policies / 41 non-internal triggers, with RLS enabled on all 29 tables.

inventory.transfer — 26 columns

Column	Type	Nullable	Default / contract
id	uuid	no	gen_random_uuid(), PK
tenant_id	uuid	no	tenant FK
transfer_number	text	no	nonblank; unique per tenant while nondeleted
source_site_id	uuid	no	composite tenant FK; distinct from destination
destination_site_id	uuid	no	composite tenant FK
entity_id	uuid	yes	composite tenant FK to platform.legal_entity
status	text	no	draft; ruled six-state lifecycle
notes	text	yes	operator notes
created_by_actor_id	uuid	no	creator actor
approved_by_actor_id	uuid	yes	human approval evidence
approved_at	timestamptz	yes	immutable approval evidence
shipped_by_actor_id	uuid	yes	human shipment evidence
shipped_at	timestamptz	yes	immutable shipment evidence
terminal_by_actor_id	uuid	yes	receiver or canceller
received_at	timestamptz	yes	received terminal evidence
cancelled_at	timestamptz	yes	cancelled terminal evidence
cancellation_reason	text	yes	required by the cancel wrapper
automation_source	text	no	human; agent allowed only for draft authorship
review_status	text	no	not_required
review_reason	text	yes	review seam
reviewed_by_actor_id	uuid	yes	reviewer actor
reviewed_at	timestamptz	yes	review evidence
decision_provenance	jsonb	yes	standard autonomy evidence shape
created_at	timestamptz	no	now()
updated_at	timestamptz	no	now(), trigger-maintained
deleted_at	timestamptz	yes	allowed only for an unapproved draft/cancelled row

inventory.transfer_line — 21 columns

Column	Type	Nullable	Default / contract
id	uuid	no	gen_random_uuid(), PK and durable line identity
tenant_id	uuid	no	tenant FK
transfer_id	uuid	no	composite tenant FK
line_number	integer	no	positive; unique within Transfer
variant_id	uuid	no	composite tenant FK
source_inventory_location_id	uuid	yes	composite tenant FK; source-site consistency guarded
destination_inventory_location_id	uuid	yes	composite tenant FK; destination-site consistency guarded
lot_id	uuid	yes	one durable lot per line; composite tenant FK
requested_qty	numeric	no	positive finite quantity
shipped_qty	numeric	no	0; one atomic full-line shipment
received_qty	numeric	no	0; monotonic cumulative clean receipt
damaged_qty	numeric	no	0; monotonic cumulative damage
missing_qty	numeric	no	0; monotonic cumulative missing
cancelled_qty	numeric	no	0; pre-shipment cancellation quantity
disposition	text	yes	pinned damaged disposition
stock_reservation_id	uuid	yes	exact unique reservation pointer
outbound_movement_line_id	uuid	yes	exact unique immutable cost/lot provenance pointer
notes	text	yes	line notes
automation_source	text	no	human; agent draft provenance supported
created_at	timestamptz	no	now()
updated_at	timestamptz	no	now(), trigger-maintained

inventory.transfer_reconciliation_event — 16 columns

Column	Type	Nullable	Default / contract
id	uuid	no	platform.uuid_generate_v7(), PK
tenant_id	uuid	no	tenant FK
transfer_line_id	uuid	no	composite tenant FK
idempotency_key	text	no	nonblank, tenant-unique caller key
received_qty_delta	numeric	no	0, finite/nonnegative
damaged_qty_delta	numeric	no	0, finite/nonnegative
missing_qty_delta	numeric	no	0, finite/nonnegative
disposition	text	yes	required only for damaged delta
resulting_received_qty	numeric	no	validated exact cumulative total
resulting_damaged_qty	numeric	no	validated exact cumulative total
resulting_missing_qty	numeric	no	validated exact cumulative total
performed_by_actor_id	uuid	no	human actor FK
occurred_at	timestamptz	no	authoritative event time
notes	text	yes	event notes
automation_source	text	no	structurally human
created_at	timestamptz	no	now(); event is append-only

Transfer index and trigger inventory

The three Transfer tables carry 23 indexes including PKs, tenant/id composite uniques, the partial active transfer-number unique, line-number and pointer uniques, status/site/line/variant/lot lookup indexes, and tenant-key event idempotency. Inventory Core adds three Transfer source-identity indexes: stock_reservation_transfer_source_line_unique, stock_movement_transfer_shipment_source_unique, and stock_movement_transfer_reconciliation_source_unique. The combined Transfer-specific index inventory is therefore 26.

The exact trigger inventory is:

1. transfer.set_updated_at
2. trg_transfer_guard_write
3. trg_transfer_sync_status_side_effects
4. transfer_line.set_updated_at
5. trg_transfer_line_guard_write
6. trg_transfer_reconciliation_event_append_only
7. trg_transfer_reconciliation_event_apply
8. trg_transfer_reconciliation_event_validate
9. trg_site_block_nonterminal_transfer_soft_delete
10. trg_inventory_location_block_nonterminal_transfer_soft_delete
11. trg_lot_block_nonterminal_transfer_soft_delete

The schema reuses Inventory Core Write Protection rather than creating a parallel ledger:

• Approval creates one protected aggregate reservation per effective Transfer line at the site/variant/location grain.
• lot_id is a pinned physical allocation, not a lot-specific reservation. Repeated lines using the same lot are aggregated for availability validation.
• Shipment is all-or-nothing and creates one complete movement header plus exactly one movement line per Transfer line using deterministic key inventory:transfer:ship:<transfer-line-id>.
• Each line stores the exact outbound movement-line UUID. Receipt dereferences that immutable pointer, rejects legacy_unverified provenance, and copies its unit_cost_cents and lot_id.
• Qualifying inbound events use inventory:transfer:reconcile:<reconciliation-event-id>. Clean quantity and damaged/restock quantity credit destination stock and stock-lot; missing and non-restocked damage do not.
• Cumulative proportional cost allocation prevents fractional partial receipts from losing or duplicating integer cents.

The lock hierarchy uses PostgreSQL FOR UPDATE in canonical order: sites, inventory locations, lot masters, Transfer header, Transfer lines, reservations, stock grains, then stock-lot grains. The shipment stock pre-lock uses IS NOT DISTINCT FROM for nullable location identity.

All consequential wrappers are SECURITY DEFINER, owned by the narrow NOLOGIN Inventory invariant owner, use search_path=pg_catalog, and are dormant to runtime roles. authenticated, authenticator, service_role, inventory_command_executor, anon, consumer_authenticated, agent roles, and general application roles have no Transfer command EXECUTE. Ordinary tenant access is tenant-scoped SELECT only; direct Transfer DML and Transfer source preemption are rejected. A controlled migration/test identity is the only current caller.

Companion guards block soft deletion of referenced multi_loc.site, inventory.inventory_location, and inventory.lot rows while a Transfer is nonterminal. Destination site/location activity is revalidated at approval and shipment, while receipt remains possible after post-shipment deactivation so in-flight inventory cannot become stranded. No Transfer service, API, UI, worker, notification runtime, purchasing runtime, or credential gateway was built.

The long count narrative immediately below is retained as historical build lineage only. Its old 25/358 checkpoint is superseded by the authoritative 29/425/1 recount above.

25 tables, 358 columns, +1 view (was 335 at 2026-07-06 lock; +1 from the 2026-07-07 reopen — see stock_reservation below; +1 from the 2026-07-08 Remediation Phase 1 pass — see stock_count.reconciled_at below; Remediation Phase 3 added no columns, see subsection below; +1 from the 2026-07-08 Remediation Phase 4 pass — see stock.last_movement_id and stock_reconciliation_shell below, the codebase's first VIEW, which does NOT count toward the table total; +1 table / +13 columns from the 2026-07-10 Header/Line Remediation reopen (fixes #5, #9, PROJECT_DECISIONS #49) — new table stock_adjustment_batch (10 cols) + stock_adjustment_request.batch_id (+1 col) + stock_count_line.reconciled_at/.reconciled_by_actor_id (+2 cols), see the stock_adjustment_batch and stock_count_line sections below; reopened 2026-07-18, Phase 2 (Nursery Vertical Extraction, PROJECT_DECISIONS #70) — item.plant_id DROPPED (replaced by the new nursery.item_profile tenant-extension table, see docs/database/schema_docs/nursery.md); item_type neutralized from ('plant','hard_good','service','kit') to ('product','service','kit') (live usage was 1,734 hard_good + 1 confirmed-debris plant row + 0 service/kit — collapsed into product); new prerequisite UNIQUE(id, tenant_id) constraint item_id_tenant_id_unique added (the composite-FK target for nursery.item_profile). Disclosed, unrelated finding surfaced while reconciling this count: this doc's own prior stated baseline (351) undercounted by 8 columns for reasons unrelated to this phase — see docs/open-items/OPEN_ITEMS.md for the logged discrepancy; root cause not investigated this pass.) — schema-locked 2026-07-06, reopened 2026-07-07, twice on 2026-07-08, on 2026-07-10, and again 2026-07-18. inventory is the biggest module built so far. Covers the product catalog (items, variants, categories, tags, barcodes, images), the variant option/value structure (size/color/pot-size style option types), physical inventory locations within a site, stock-on-hand and its movement ledger, stock reservations, adjustment reasons and a propose/execute adjustment-request gate, cycle/full/spot stock counts, lot tracking with per-lot-per-location stock splits and condition grading, kit bill-of-materials, and a duplicate-item merge workflow. Tenant-scoped throughout, no mixed-scope tables. Depends on platform (tenant ownership), shared (unit_of_measure, currency), multi_loc (site attribution on location/stock/movement/count/lot/adjustment records), and identity (actor-attribution FKs on every table, continuing the pattern crm first applied natively). No longer a direct shared.plant/nursery-vertical consumer as of 2026-07-18 — see "Biggest module so far — no longer a direct shared.plant consumer" below.

PROJECT_DECISIONS entry: #24 (build), #28 (2026-07-07 reopen — 1 erosion fix, see DR-63 below), #40 (2026-07-08 Remediation Phase 4 — Item 20b, see subsection below), #49 (2026-07-10 Header/Line Remediation reopen — fixes #5, #9, see DR-68 below).

Global rules for this schema:

• Tenant-scoped — every table carries tenant_id NOT NULL FK → platform.tenant, with RLS enabled and a permissive tenant-isolation policy (USING/WITH CHECK on current_setting('app.current_tenant_id')::uuid). Verified live: all 25 tables have rowsecurity = true, including the new stock_adjustment_batch (2026-07-10).
• Soft delete on master-data and mutable-state tables (deleted_at timestamptz, nullable); append-only, no soft delete, no updated_at on event/audit tables (stock_movement, stock_movement_line, item_merge) plus two hard-delete join tables that never needed either column in the first place (item_category, item_tag, matching v1's own shape for these two). The new stock_adjustment_batch (2026-07-10) follows the soft-delete convention (deleted_at timestamptz, nullable).
• updated_at is trigger-maintained via platform.set_updated_at() on 19 of the 25 tables (verified live). 5 correctly do NOT have it: stock_movement, stock_movement_line, item_merge (immutable append-only — no updated_at column at all), and item_category, item_tag (hard-delete join tables — no updated_at either, matching v1). stock_adjustment_batch is a 6th table without the trigger — but unlike the other 5, this is NOT a deliberate design choice. It's a soft-delete/mutable header table (same shape as stock_adjustment_reason, stock_count, etc., all of which DO have the trigger) and its migration (20260710030000_headerline_inventory_fixes.sql) never wires a platform.set_updated_at() trigger to it — confirmed live: SELECT tgname FROM pg_trigger WHERE tgrelid='inventory.stock_adjustment_batch'::regclass AND NOT tgisinternal returns zero rows. updated_at is therefore set once at INSERT and never refreshed on UPDATE for this table today. Disclosed as a genuine gap found while writing this doc, not silently corrected — see OPEN_ITEMS.
• Agent-as-actor attribution from day 1 — every *_actor_id column (created_by_actor_id, updated_by_actor_id, reviewed_by_actor_id, proposed_by_actor_id, performed_by_actor_id, started_by_actor_id, reconciled_by_actor_id, counted_by_actor_id, merged_by_actor_id) targets identity.actor (the polymorphic root), never identity.identity_user directly — the same canonical pattern crm established as the first module built natively with it.
• Autonomy treatment varies deliberately by table — full (automation_source + review_status + decision_provenance + reviewer FKs), light (created_by_actor_id only), or none (human/Vrida-curated catalogs and pure join tables). See each table's section and the Design Patterns Summary for the specific reasoning per table.
• item.plant_id — DROPPED 2026-07-18 (Phase 2, PROJECT_DECISIONS #70). The direct shared.plant FK this bullet used to describe no longer exists. The vertical relationship it represented is now a 2-hop, vertical-extension chain entirely outside this schema: nursery.item_profile.item_id → inventory.item(id, tenant_id) (composite FK, tenant-scoped extension table, owned by the new nursery schema) optionally carries its own plant_id → nursery_ref.plant.id (the relocated botanical taxonomy, formerly shared.plant). inventory.item itself carries no FK, column, or CHECK value that assumes any particular vertical — see docs/database/schema_docs/nursery.md/nursery_ref.md and SCHEMA_CONVENTIONS.md §21.
• UOM columns retyped from v1's assumed uuid to the actual locked shared.unit_of_measure.code shape — shared.unit_of_measure's natural key is code (text), not a surrogate uuid. Every UOM-referencing column in this module (item_variant.sell_uom_code, stock_uom_code, purchase_uom_code, weight_uom_code, inventory_location.capacity_uom_code) is text, FK → shared.unit_of_measure(code). sell_uom_code/stock_uom_code are NOT NULL, ON DELETE RESTRICT (a variant cannot lose its sell/stock unit of measure out from under it); purchase_uom_code/weight_uom_code/capacity_uom_code are nullable, ON DELETE SET NULL. weight_uom_code is a new upgrade over v1, which only had a free-text weight_uom with no FK enforcement at all.
• item_variant.currency_code — FK → shared.currency.iso_code (char(3)), ON DELETE RESTRICT, NOT NULL DEFAULT 'USD'. v1 left this column unenforced (no FK).
• One deferred forward-ref — stock_movement.photo_ref, a plain nullable uuid with no FK, because the files module the column would reference does not exist in this v2 database. Verified live via psql \dn: as of this build, the only application schemas present are identity, multi_loc, platform, shared, crm, inventory (plus Postgres/Supabase system schemas — auth, extensions, graphql, graphql_public, net, pgbouncer, public, realtime, storage, supabase_functions, vault, _realtime, drizzle). Same treatment as crm.customer.consumer_id — a genuine forward-ref, not an oversight.
• item.search_vector / item_variant.search_vector are real, not deferred — unlike crm.customer.search_vector (which crm left unbuilt because no v2 precedent existed yet), both are actual GENERATED ALWAYS AS (to_tsvector(...)) STORED tsvector columns with GIN indexes (item_search_vector_idx, item_variant_search_vector_idx), matching v1's original spec exactly. This is not a forward-ref FK — a generated tsvector column has zero dependency on any search schema existing — so it was safe to build now rather than defer.

Cross-Phase Foreign Keys (inventory)

Column	Target	Notes
*.tenant_id (all 25 tables)	platform.tenant	Intra-tenancy, enforced.
item_variant.sell_uom_code, stock_uom_code	shared.unit_of_measure.code	NOT NULL, ON DELETE RESTRICT.
item_variant.purchase_uom_code, weight_uom_code, inventory_location.capacity_uom_code	shared.unit_of_measure.code	Nullable, ON DELETE SET NULL.
item_variant.currency_code	shared.currency.iso_code	NOT NULL DEFAULT 'USD', ON DELETE RESTRICT. v1 left this unenforced.
inventory_location.site_id, stock.site_id, stock_movement.site_id, stock_reservation.site_id, stock_count.site_id, stock_lot.site_id, stock_adjustment_request.site_id	multi_loc.site.id	NOT NULL, cross-schema, enforced on all seven.
item_category.item_id/category_id, item_tag.item_id/tag_id, item_variant.item_id, option_type.item_id, variant_option.variant_id/option_type_id, barcode.variant_id, item_image.item_id/variant_id, category.parent_id	intra-schema (inventory.item/inventory.category/inventory.tag/inventory.item_variant/inventory.option_type)	Intra-schema, enforced.
stock.variant_id, stock.inventory_location_id, stock_movement.reason_id, stock_movement.adjustment_request_id, stock_movement_line.movement_id/variant_id/from_location_id/to_location_id/lot_id, stock_reservation.variant_id/inventory_location_id, stock_adjustment_reason (referenced by stock_movement.reason_id and stock_adjustment_request.reason_id), stock_adjustment_request.variant_id/inventory_location_id/reason_id, stock_count.inventory_location_id, stock_count_line.count_id/variant_id/inventory_location_id/lot_id, lot.variant_id, stock_lot.variant_id/inventory_location_id/lot_id, kit_component.kit_variant_id/component_variant_id	intra-schema	Intra-schema, enforced.
stock.last_movement_id	inventory.stock_movement.id	Nullable, reconciliation watermark. Added 2026-07-08 (Remediation Phase 4, Item 20b). Consumed by inventory.stock_reconciliation_shell view.
stock_adjustment_request.batch_id	inventory.stock_adjustment_batch(id, tenant_id)	Added 2026-07-10 (Header/Line Remediation fix #5). Composite FK (stock_adjustment_request_batch_tenant_fkey), not bare. Nullable — existing rows stay ungrouped.
item_merge_candidate.item_a_id/item_b_id, item_merge.source_item_id/target_item_id, item_merge.candidate_id	inventory.item, inventory.item_merge_candidate	Intra-schema, enforced. candidate_id nullable — a merge need not originate from a logged candidate, same as crm.customer_merge.candidate_id.
stock_movement.photo_ref	(none — deferred)	Plain nullable UUID, no FK. The files schema does not exist in v2 (verified live).
*_actor_id FKs (all roles: created_by, updated_by, reviewed_by, proposed_by, performed_by, started_by, reconciled_by, counted_by, merged_by)	identity.actor	Nullable throughout. Cross-schema, enforced.

---

inventory.item

The core catalog entity — a sellable/stockable product concept, one row per SKU-family (a specific SKU lives on item_variant). Discriminated by item_type (product/service/kit) — neutralized 2026-07-18 (Phase 2, PROJECT_DECISIONS #70) from the original plant/hard_good/service/kit list; a nursery tenant now distinguishes a plant SKU via the separate nursery.item_profile extension table, not this column. Carries full autonomy treatment (this is the primary target of AI-assisted catalog enrichment and dedup).

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

Soft delete: deleted_at timestamptz, nullable.

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

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
item_type	text	NOT NULL	—	CHECK IN (product,service,kit) — neutralized 2026-07-18, Phase 2 (PROJECT_DECISIONS #70; was plant,hard_good,service,kit)
name	text	NOT NULL	—
slug	text	NOT NULL	—	UNIQUE per tenant (partial-unique, see indexes)
description	text	nullable	—
brand	text	nullable	—
status	text	NOT NULL	'active'	CHECK IN (active,discontinued,draft)
attributes	jsonb	nullable	'{}'	Open extensibility bag, per-item_type shape not fully documented yet (OPEN_ITEMS)
stripe_tax_code	text	nullable	—
created_by_actor_id	UUID	nullable	—	FK → identity.actor
updated_by_actor_id	UUID	nullable	—	FK → identity.actor
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
review_status	text	NOT NULL	'not_required'	CHECK IN (not_required,pending,approved,rejected)
review_reason	text	nullable	—
reviewed_by_actor_id	UUID	nullable	—	FK → identity.actor
reviewed_at	timestamptz	nullable	—
decision_provenance	jsonb	nullable	—	Field-level provenance; also documents two new keys per the AI_CAPABILITY_GAPS.md rulings — memory_refs (G5) and delegated_by_actor_id (G3). No new column/table for either, JSONB keys only.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete
search_vector	tsvector	NOT NULL	generated	GENERATED ALWAYS AS (to_tsvector('english', name || ' ' || description || ' ' || item_type)) STORED. Real column, not deferred — see Global rules.

CHECK constraints (verified live):

Name	Condition
chk_item_item_type	item_type IN ('product','service','kit') — neutralized 2026-07-18, Phase 2 (PROJECT_DECISIONS #70; was ('plant','hard_good','service','kit'))
chk_item_status	status IN ('active','discontinued','draft')
chk_item_automation_source	automation_source IN ('human','agent','system','seed')
chk_item_review_status	review_status IN ('not_required','pending','approved','rejected')

Indexes:

• PK on id
• Plain index on tenant_id
• Partial index on review_status WHERE = 'pending' — review-queue query
• GIN index on search_vector (item_search_vector_idx)
• Partial UNIQUE on (tenant_id, slug) WHERE deleted_at IS NULL
• item_id_tenant_id_unique — UNIQUE (id, tenant_id). Added 2026-07-18 (Phase 2, PROJECT_DECISIONS #70) — the composite-FK prerequisite for nursery.item_profile.item_id.

Referenced by: nursery.item_profile.item_id via the composite FK item_profile_item_tenant_fkey (item_id, tenant_id) → item(id, tenant_id) — cross-schema, added 2026-07-18 (Phase 2). See docs/database/schema_docs/nursery.md.

---

inventory.item_variant

The actual sellable SKU — price, cost, UOM conversions, physical attributes, kit flag. One item has one-or-more item_variant rows (a plant item might have a single default variant, or several for pot-size options via option_type/variant_option). Full autonomy treatment. Carries the module's richest set of UOM/currency FKs.

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

Soft delete: deleted_at timestamptz, nullable.

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

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
item_id	UUID	NOT NULL	—	FK → inventory.item
sku	text	NOT NULL	—	UNIQUE per tenant (partial-unique, see indexes)
name	text	NOT NULL	—
base_price_cents	bigint	NOT NULL	—	List-price anchor. Pricing (not built) resolves actual sale price
currency_code	char(3)	NOT NULL	'USD'	FK → shared.currency.iso_code, ON DELETE RESTRICT. v1 left unenforced
avg_cost_cents	bigint	NOT NULL	0	Weighted-average cost, maintained by movement processing. Reconciliation formula (carried from v1's rationale_inventory.md, not previously restated here): new_avg = (prev_avg × existing_qty + unit_cost × received_qty) / (existing_qty + received_qty), recalculated on every receiving movement. stock_movement_line.previous_avg_cost_cents/new_avg_cost_cents snapshot each application of this formula, making the running value auditable line-by-line even though this column itself holds only the current figure.
sell_uom_code	text	NOT NULL	—	FK → shared.unit_of_measure.code, ON DELETE RESTRICT
stock_uom_code	text	NOT NULL	—	FK → shared.unit_of_measure.code, ON DELETE RESTRICT
purchase_uom_code	text	nullable	—	FK → shared.unit_of_measure.code, ON DELETE SET NULL
purchase_to_stock_factor	numeric	nullable	—	Conversion factor purchase↔stock UOM
sell_to_stock_factor	numeric	nullable	—	Conversion factor sell↔stock UOM
weight	numeric	nullable	—
weight_uom_code	text	nullable	—	FK → shared.unit_of_measure.code, ON DELETE SET NULL. New upgrade over v1's free-text-only weight_uom
dimensions	jsonb	nullable	—
is_kit	boolean	NOT NULL	false
kit_stock_mode	text	nullable	—	CHECK IN (explode_at_sale,stocked_kit) when is_kit=true, else NULL — see table CHECK below
track_inventory	boolean	NOT NULL	true
status	text	NOT NULL	'active'	CHECK IN (active,discontinued)
attributes	jsonb	nullable	'{}'	Open extensibility bag
has_guarantee	boolean	NOT NULL	false
guarantee_terms	jsonb	nullable	—	Required when has_guarantee=true — see table CHECK below
created_by_actor_id	UUID	nullable	—	FK → identity.actor
updated_by_actor_id	UUID	nullable	—	FK → identity.actor
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
review_status	text	NOT NULL	'not_required'	CHECK IN (not_required,pending,approved,rejected)
review_reason	text	nullable	—
reviewed_by_actor_id	UUID	nullable	—	FK → identity.actor
reviewed_at	timestamptz	nullable	—
decision_provenance	jsonb	nullable	—	Same memory_refs/delegated_by_actor_id key convention as item
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete
search_vector	tsvector	NOT NULL	generated	GENERATED ALWAYS AS (to_tsvector('english', sku || ' ' || name)) STORED

CHECK constraints (verified live):

Name	Condition
chk_item_variant_status	status IN ('active','discontinued')
chk_item_variant_automation_source	automation_source IN ('human','agent','system','seed')
chk_item_variant_review_status	review_status IN ('not_required','pending','approved','rejected')
chk_item_variant_guarantee_terms	has_guarantee = false OR guarantee_terms IS NOT NULL
chk_item_variant_kit_stock_mode_consistency	(is_kit = false AND kit_stock_mode IS NULL) OR (is_kit = true AND kit_stock_mode IS NOT NULL)
chk_item_variant_kit_stock_mode_values	kit_stock_mode IS NULL OR kit_stock_mode IN ('explode_at_sale','stocked_kit')

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on item_id
• Partial index on review_status WHERE = 'pending'
• GIN index on search_vector (item_variant_search_vector_idx)
• Partial UNIQUE on (tenant_id, sku) WHERE deleted_at IS NULL
• item_variant_id_tenant_id_unique — UNIQUE (id, tenant_id). Added 2026-07-10 (Receiving extraction) — a prerequisite for a composite FK from a different schema targeting this table (verified live via pg_get_constraintdef).

Referenced by: receiving.goods_receipt_line.variant_id via the composite FK goods_receipt_line_variant_tenant_fkey (variant_id, tenant_id) → item_variant(id, tenant_id) — cross-schema, added 2026-07-10.

---

inventory.category

Self-referencing hierarchical product category tree, human/Vrida-curated (no autonomy columns beyond created_by_actor_id).

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

Soft delete: deleted_at timestamptz, nullable.

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

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
parent_id	UUID	nullable	—	FK → inventory.category (self)
name	text	NOT NULL	—
slug	text	NOT NULL	—	UNIQUE per (tenant, parent) — partial-unique, see indexes
sort_order	integer	NOT NULL	0
created_by_actor_id	UUID	nullable	—	FK → identity.actor
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on parent_id
• Partial UNIQUE on (tenant_id, parent_id, slug) WHERE deleted_at IS NULL
• category_id_tenant_id_unique — UNIQUE (id, tenant_id) — added 2026-07-20 (Gap-Fill Batch, Pricing's A1 reopen) — the prerequisite for pricing.price_rule.category_id's composite FK; constraint-only, no column change

Referenced by: pricing.price_rule.category_id via the composite FK price_rule_category_id_tenant_fkey (category_id, tenant_id) → category(id, tenant_id) — cross-schema, added 2026-07-20 (Gap-Fill Batch A1).

---

inventory.item_category

Item × category assignment. Pure hard-delete join table — no updated_at, no deleted_at, matching v1's shape exactly.

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

No soft delete, no updated_at — verified live, hard-delete join table.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	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_by_actor_id	UUID	nullable	—	FK → identity.actor
created_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on category_id
• UNIQUE on (item_id, category_id)

---

inventory.tag

Tenant-defined free-form tag catalog (e.g. "Native", "Drought-Tolerant"). Human/Vrida-curated.

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

Soft delete: deleted_at timestamptz, nullable.

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

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
name	text	NOT NULL	—
slug	text	NOT NULL	—	UNIQUE per tenant (partial-unique, see indexes)
color	text	nullable	—
created_by_actor_id	UUID	nullable	—	FK → identity.actor
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

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

---

inventory.item_tag

Item × tag assignment. Pure hard-delete join table, same shape as item_category.

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

No soft delete, no updated_at — verified live, hard-delete join table.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	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_by_actor_id	UUID	nullable	—	FK → identity.actor
created_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on tag_id
• UNIQUE on (item_id, tag_id)

---

inventory.brand (added 2026-07-20, Gap-Fill Batch — Pricing's A1 category/brand-scoped price rules reopen)

NEW table — a minimal tenant-scoped brand/manufacturer name catalog, built purely as the composite-FK prerequisite for pricing.price_rule.brand_id (category/brand-scoped price rules). This reopen was a SIDE EFFECT of pricing's own design, not a named gap for inventory itself. Deliberately MINIMAL per an explicit architect ruling made live during this reopen (a codebase-wide grep confirmed no brand catalog table existed anywhere in the codebase before this) — no slug, description, or hierarchy, since nothing beyond pricing.price_rule.brand_id consumes it yet. See DR-70 in the module spec (docs/modules/module_spec/inventory.md) for the deliberate-minimalism rationale and what a fuller brand-management feature would need.

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

Soft delete: deleted_at timestamptz, nullable.

updated_at: trigger-maintained via platform.set_updated_at() (set_updated_at trigger, wired from creation — unlike stock_adjustment_batch's own disclosed gap, this table's migration wires the trigger correctly).

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
name	text	NOT NULL	—
created_by_actor_id	UUID	nullable	—	FK → identity.actor
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes / keys:

• PK on id
• Plain index on tenant_id (brand_tenant_id_idx)
• brand_id_tenant_id_unique — UNIQUE (id, tenant_id) — the prerequisite for pricing.price_rule.brand_id's composite FK, added on this table's own DDL from day one (this codebase's standing rule for every new parent a composite FK will target)
• brand_tenant_name_unique — UNIQUE (tenant_id, name) — no duplicate brand names per tenant

Referenced by: pricing.price_rule.brand_id via the composite FK price_rule_brand_id_tenant_fkey (brand_id, tenant_id) → brand(id, tenant_id) — cross-schema, added 2026-07-20 (Gap-Fill Batch A1).

Migration: packages/db/migrations/20260720000001_inventory_reopen_category_unique_brand_catalog.sql. Tests: inventory-schema.spec.ts (brand insert defaults, duplicate-name-same-tenant rejection, same-name-different-tenant success, RLS tenant isolation — 4 of the 6 new tests added this reopen; the other 2 cover category's new constraint, see its own section above).

---

inventory.barcode

UPC/EAN/own barcodes per variant, one designated primary.

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

Soft delete: deleted_at timestamptz, nullable.

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

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
variant_id	UUID	NOT NULL	—	FK → inventory.item_variant
barcode	text	NOT NULL	—	UNIQUE per tenant (partial-unique, see indexes)
barcode_type	text	nullable	—	CHECK IN (UPC,EAN,CODE128,own) OR NULL
is_primary	boolean	NOT NULL	false	At most one true per variant — partial-unique, see indexes
created_by_actor_id	UUID	nullable	—	FK → identity.actor
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete

CHECK constraints (verified live):

Name	Condition
chk_barcode_barcode_type	barcode_type IS NULL OR barcode_type IN ('UPC','EAN','CODE128','own')

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on variant_id
• Partial UNIQUE on (tenant_id, barcode) WHERE deleted_at IS NULL
• Partial UNIQUE on variant_id WHERE is_primary = true AND deleted_at IS NULL

---

inventory.item_image

Image asset metadata for an item OR a variant (mutually exclusive owner). Full autonomy treatment — AI-generated/enriched image alt-text and image selection is a plausible future capability target.

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

Soft delete: deleted_at timestamptz, nullable.

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

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
item_id	UUID	nullable	—	FK → inventory.item. Exactly one of item_id/variant_id set — see table CHECK
variant_id	UUID	nullable	—	FK → inventory.item_variant. Exactly one of item_id/variant_id set
storage_key	text	NOT NULL	—
url	text	nullable	—
alt_text	text	nullable	—
sort_order	integer	NOT NULL	0
is_primary	boolean	NOT NULL	false	At most one true per owner — partial-unique, see indexes
file_id	UUID	nullable	—	Plain UUID, no FK — same deferred-files-module treatment as stock_movement.photo_ref
created_by_actor_id	UUID	nullable	—	FK → identity.actor
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
review_status	text	NOT NULL	'not_required'	CHECK IN (not_required,pending,approved,rejected)
review_reason	text	nullable	—
reviewed_by_actor_id	UUID	nullable	—	FK → identity.actor
reviewed_at	timestamptz	nullable	—
decision_provenance	jsonb	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete

CHECK constraints (verified live):

Name	Condition
chk_item_image_exactly_one_owner	(item_id IS NOT NULL AND variant_id IS NULL) OR (item_id IS NULL AND variant_id IS NOT NULL)
chk_item_image_automation_source	automation_source IN ('human','agent','system','seed')
chk_item_image_review_status	review_status IN ('not_required','pending','approved','rejected')

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on item_id
• Plain index on variant_id
• Partial index on review_status WHERE = 'pending'
• Partial UNIQUE on item_id WHERE item_id IS NOT NULL AND is_primary = true AND deleted_at IS NULL
• Partial UNIQUE on variant_id WHERE variant_id IS NOT NULL AND is_primary = true AND deleted_at IS NULL

---

inventory.option_type

Variant option axis definition scoped to one item (e.g. "Pot Size", "Color"). Light autonomy touch only.

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

Soft delete: deleted_at timestamptz, nullable.

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

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
item_id	UUID	NOT NULL	—	FK → inventory.item
name	text	NOT NULL	—	e.g. "Pot Size"
sort_order	integer	NOT NULL	0
created_by_actor_id	UUID	nullable	—	FK → identity.actor
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

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

---

inventory.variant_option

The value of one option type for one variant (e.g. variant X → option type "Pot Size" → value "3 gal"). Light autonomy touch only.

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

Soft delete: deleted_at timestamptz, nullable.

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

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
variant_id	UUID	NOT NULL	—	FK → inventory.item_variant
option_type_id	UUID	NOT NULL	—	FK → inventory.option_type
value	text	NOT NULL	—	e.g. "3 gal"
created_by_actor_id	UUID	nullable	—	FK → identity.actor
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on variant_id
• Plain index on option_type_id
• Partial UNIQUE on (variant_id, option_type_id) WHERE deleted_at IS NULL

---

inventory.inventory_location

Self-referencing physical sub-location tree within a multi_loc.site (zone/bin/bench/row/shelf). The physical-granularity layer stock/stock_lot/movements attach to.

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

Soft delete: deleted_at timestamptz, nullable.

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

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
site_id	UUID	NOT NULL	—	FK → multi_loc.site
parent_id	UUID	nullable	—	FK → inventory.inventory_location (self)
name	text	NOT NULL	—
location_type	text	NOT NULL	—	CHECK IN (zone,bin,bench,row,shelf,other)
capacity	numeric	nullable	—	Paired with capacity_uom_code — see table CHECK
capacity_uom_code	text	nullable	—	FK → shared.unit_of_measure.code, ON DELETE SET NULL. Paired with capacity
status	text	NOT NULL	'active'	CHECK IN (active,inactive)
sort_order	integer	NOT NULL	0
created_by_actor_id	UUID	nullable	—	FK → identity.actor
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete

CHECK constraints (verified live):

Name	Condition
chk_inventory_location_location_type	location_type IN ('zone','bin','bench','row','shelf','other')
chk_inventory_location_status	status IN ('active','inactive')
chk_inventory_location_capacity_uom	(capacity IS NULL AND capacity_uom_code IS NULL) OR (capacity IS NOT NULL AND capacity_uom_code IS NOT NULL)

Indexes:

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

---

inventory.stock

Current stock-on-hand snapshot per (tenant, site, variant, location) — the resolved current-state table the movement ledger (stock_movement/stock_movement_line) feeds. Full autonomy treatment, since AI-proposed adjustments write through this table's review seam.

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

No soft delete — verified live, no deleted_at column; a stock row's relevance is fully captured by its quantities.

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

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
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 the site level
on_hand_qty	numeric	NOT NULL	0
reserved_qty	numeric	NOT NULL	0
available_qty	numeric	NOT NULL	generated	GENERATED ALWAYS AS (on_hand_qty - reserved_qty) STORED
reorder_point	numeric	nullable	—
reorder_qty	numeric	nullable	—
min_qty	numeric	nullable	—
max_qty	numeric	nullable	—
last_movement_at	timestamptz	nullable	—	Maintained cache of the timestamp of the most recent stock_movement touching this row. Gives Pricing/Reporting a cheap dead-stock/aging signal — inventory.stock_changed fires on every movement, not on aging thresholds, so nothing else surfaces this
last_movement_id	UUID	nullable	—	Added 2026-07-08 (Remediation Phase 4, Item 20b). FK → inventory.stock_movement. A reconciliation watermark — 0 rows at build time, zero backfill risk. Consumed by inventory.stock_reconciliation_shell (see subsection below)
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
review_status	text	NOT NULL	'not_required'	CHECK IN (not_required,pending,approved,rejected)
review_reason	text	nullable	—
reviewed_by_actor_id	UUID	nullable	—	FK → identity.actor
reviewed_at	timestamptz	nullable	—
decision_provenance	jsonb	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

CHECK constraints (verified live):

Name	Condition
chk_stock_automation_source	automation_source IN ('human','agent','system','seed')
chk_stock_review_status	review_status IN ('not_required','pending','approved','rejected')

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on site_id
• Plain index on variant_id
• Partial index on review_status WHERE = 'pending'
• Partial UNIQUE on (tenant_id, site_id, variant_id, inventory_location_id) WHERE inventory_location_id IS NOT NULL — located stock rows
• Partial UNIQUE on (tenant_id, site_id, variant_id) WHERE inventory_location_id IS NULL — unlocated stock rows. Same two-partial-unique split pattern as crm.customer_tax_certificate to avoid the NULL-in-multi-column-unique trap.

---

inventory.stock_movement

Append-only movement-event header — one row per discrete stock-affecting event (receive, sale, transfer, adjustment, count reconciliation, return). No updated_at, no deleted_at.

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

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
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	—
reason_id	UUID	nullable	—	FK → inventory.stock_adjustment_reason. Required when movement_type='adjusted' — see table CHECK
source_module	text	NOT NULL	—	CHECK IN (pos,orders,purchasing,inventory,production,system). Anticipates InventoryService.receiveStock(source_module='purchasing') even though Purchasing isn't built yet
source_type	text	nullable	—
source_id	UUID	nullable	—
correlation_id	UUID	nullable	—	Groups related movements (e.g. both legs of a transfer)
idempotency_key	text	nullable	—	UNIQUE per tenant when set — partial-unique, see indexes
performed_by_actor_id	UUID	nullable	—	FK → identity.actor
adjustment_request_id	UUID	nullable	—	FK → inventory.stock_adjustment_request. Set when this movement executes an approved adjustment request
photo_ref	UUID	nullable	—	DEFERRED forward-ref, plain UUID, NO FK — the files schema doesn't exist in v2. See Global rules
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
notes	text	nullable	—
created_at	timestamptz	NOT NULL	now()

No updated_at, no deleted_at — append-only movement ledger; a movement is a historical fact, never edited or removed.

CHECK constraints (verified live):

Name	Condition
chk_stock_movement_movement_type	movement_type IN ('received','sold','transferred','adjusted','counted','returned','produced') — widened 2026-07-08 (Remediation Phase 3), see subsection below
chk_stock_movement_source_module	source_module IN ('pos','orders','purchasing','inventory','production','system')
chk_stock_movement_reason_required	movement_type <> 'adjusted' OR reason_id IS NOT NULL
chk_stock_movement_automation_source	automation_source IN ('human','agent','system','seed')

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on correlation_id
• Composite index on (site_id, movement_date)
• Partial index on adjustment_request_id WHERE NOT NULL
• Partial UNIQUE on (tenant_id, idempotency_key) WHERE idempotency_key IS NOT NULL
• stock_movement_id_tenant_id_unique — UNIQUE (id, tenant_id). Added 2026-07-10 (Receiving extraction) — a prerequisite for a composite FK from a different schema targeting this table (verified live via pg_get_constraintdef).

Referenced by: receiving.goods_receipt_line.stock_movement_id (the header-grain movement link — renamed from inventory_movement_id on the Receiving side, upgraded from bare to composite in the same migration) via the composite FK goods_receipt_line_stock_movement_tenant_fkey (stock_movement_id, tenant_id) → stock_movement(id, tenant_id) — cross-schema, added 2026-07-10.

---

inventory.stock_movement_line

Line-level detail of a movement — the variant/location/lot/quantity/cost specifics. Append-only, no updated_at, no deleted_at. Carries the cost-provenance columns that make average-cost recalculation auditable.

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

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
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
to_location_id	UUID	nullable	—	FK → inventory.inventory_location
lot_id	UUID	nullable	—	FK → inventory.lot
quantity_delta	numeric	NOT NULL	—	Signed — positive or negative
unit_cost_cents	bigint	nullable	—	Cost-provenance: the unit cost this line was recorded at
previous_avg_cost_cents	bigint	nullable	—	Cost-provenance: item_variant.avg_cost_cents snapshot before this line applied
new_avg_cost_cents	bigint	nullable	—	Cost-provenance: item_variant.avg_cost_cents snapshot after this line applied
cost_impact_cents	bigint	nullable	—	Cost-provenance: net balance-sheet valuation impact of this line
data_source	text	nullable	—	CHECK IN (seed,ai_generated,manual) OR NULL. Uses the ESTABLISHED vocabulary matching nursery_ref.plant's own CHECK exactly (relocated from shared.plant 2026-07-18, Phase 2, PROJECT_DECISIONS #70; verified live via pg_constraint) — an earlier draft's invented 'ocr' value was corrected before this migration was written
is_verified	boolean	NOT NULL	true	Per-line trust flag — an OCR-extracted receiving cost would be recorded with is_verified=false, functioning as its draft-only equivalent (per the D7 rule)
created_at	timestamptz	NOT NULL	now()

No updated_at, no deleted_at — append-only line detail, immutable once the parent movement is recorded.

CHECK constraints (verified live):

Name	Condition
chk_stock_movement_line_data_source	data_source IS NULL OR data_source IN ('seed','ai_generated','manual')

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on movement_id
• Plain index on variant_id
• Plain index on lot_id
• stock_movement_line_id_tenant_id_unique — UNIQUE (id, tenant_id). Added 2026-07-10 (Receiving extraction) — a prerequisite for a composite FK from a different schema targeting this table (verified live via pg_get_constraintdef). This closes open item #12 below, which had disclosed this exact constraint as missing.

Referenced by: receiving.goods_receipt_line.stock_movement_line_id (a NEW column on the Receiving side — fix #7, movement-line linkage, superseding the header-grain stock_movement_id link above) via the composite FK goods_receipt_line_stock_movement_line_tenant_fkey (stock_movement_line_id, tenant_id) → stock_movement_line(id, tenant_id) — cross-schema, added 2026-07-10.

---

inventory.stock_reservation

Soft-hold against available stock for an order/hold/transfer in progress. Reduces stock.available_qty via reserved_qty without moving physical stock.

Erosion fixed 2026-07-07 (DR-63): the crm/inventory/pricing erosion audit found this table had no actor-attribution column at all, despite being writable by an autonomous agent (automation_source='agent') — the "no review seam, fully deterministic" framing below didn't hold for the agent-initiated write path (a misreserved order left no trail of which agent, why, or under what confidence). created_by_actor_id (nullable, no default) now closes that gap; source_type/source_id remain the primary trace for the deterministic system/POS case.

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

No soft delete — verified live, no deleted_at column; lifecycle fully captured by status.

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

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
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
quantity	numeric	NOT NULL	—
source_type	text	NOT NULL	—	CHECK IN (order,hold,transfer)
source_id	UUID	NOT NULL	—
source_line_id	UUID	nullable	—
status	text	NOT NULL	'active'	CHECK IN (active,released,fulfilled,expired,cancelled)
expires_at	timestamptz	nullable	—	NULL = no expiry. Not referenced inside an index predicate (same now()-not-IMMUTABLE reasoning as crm.customer_segment_membership, DR-47)
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
created_by_actor_id	UUID	nullable	—	Added 2026-07-07 (DR-63) — FK → identity.actor. Restores a v1 erosion; traces agent-initiated reservations specifically
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

CHECK constraints (verified live):

Name	Condition
chk_stock_reservation_source_type	source_type IN ('order','hold','transfer')
chk_stock_reservation_status	status IN ('active','released','fulfilled','expired','cancelled')
chk_stock_reservation_automation_source	automation_source IN ('human','agent','system','seed')

Indexes:

• PK on id
• Plain index on tenant_id
• Composite index on (variant_id, status)
• Composite index on (source_type, source_id)
• Partial index on expires_at WHERE status = 'active' — expiry sweep candidate list, predicate references only stored columns, not now()

---

inventory.stock_adjustment_reason

Tenant-defined catalog of adjustment reason codes (shrinkage, damage, found, etc.). Human/Vrida-curated, no autonomy columns beyond created_by_actor_id.

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

Soft delete: deleted_at timestamptz, nullable.

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

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
code	text	NOT NULL	—	UNIQUE per tenant (partial-unique, see indexes)
name	text	NOT NULL	—
is_active	boolean	NOT NULL	true
sort_order	integer	NOT NULL	0
created_by_actor_id	UUID	nullable	—	FK → identity.actor
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

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

---

inventory.stock_adjustment_batch (added 2026-07-10, Header/Line Remediation fix #5)

NEW table — a header grouping multiple stock_adjustment_request rows so a full shelf recount touching many variants can be reviewed and approved/rejected together, rather than only ever one adjustment at a time. Fully additive: stock_adjustment_request had 0 live rows at build time, so the new nullable stock_adjustment_request.batch_id requires zero backfill. See DR-68 below.

status reuses stock_count's own "status as review seam" convention (open/reviewing/approved/rejected), plus partially_approved since lines can resolve independently within a batch — itself reusing order_header.status's own partially_fulfilled precedent.

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

Soft delete: deleted_at timestamptz, nullable.

updated_at: NOT trigger-maintained (verified live — zero triggers exist on this table). Set once at INSERT (default now()) and never refreshed on UPDATE. This diverges from every other soft-delete table in this module (all of which use platform.set_updated_at()) — a genuine gap found while documenting this table, not a deliberate design choice. See OPEN_ITEMS.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
site_id	UUID	NOT NULL	—	FK → multi_loc.site
batch_reason	text	nullable	—
status	text	NOT NULL	'open'	CHECK IN (open,reviewing,approved,rejected,partially_approved)
created_by_actor_id	UUID	nullable	—	FK → identity.actor
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	NOT trigger-maintained — see note above
deleted_at	timestamptz	nullable	—	Soft delete

CHECK constraints (verified live):

Name	Condition
chk_stock_adjustment_batch_status	status IN ('open','reviewing','approved','rejected','partially_approved')
chk_stock_adjustment_batch_automation_source	automation_source IN ('human','agent','system','seed')

Indexes / keys:

• PK on id
• Plain index on tenant_id
• Plain index on site_id
• stock_adjustment_batch_id_tenant_id_unique — UNIQUE (id, tenant_id) — the prerequisite for stock_adjustment_request.batch_id's composite FK below, added on this table's own DDL from the start (this whole remediation effort's standing rule for every new parent a composite FK will target)

Referenced by: stock_adjustment_request.batch_id via the composite FK stock_adjustment_request_batch_tenant_fkey (batch_id, tenant_id) → stock_adjustment_batch(id, tenant_id) — nullable, not bare.

---

inventory.stock_adjustment_request

NEW table this build — the propose/execute gate for stock adjustments. review_status DEFAULTs 'pending' (not 'not_required' like stock/item/item_variant), since every row here exists because it needs review — the same rationale crm.customer_merge_candidate established. Per the D7 rule, executing a stock adjustment is always needs_approval, never may-act-alone; this table is the draft side of that gate, stock_movement (with adjustment_request_id set) is the executed side.

Dedup fix (this build): uses ONE partial unique index, not a two-index split — stock_adjustment_request_one_pending_unique UNIQUE (tenant_id, site_id, variant_id) WHERE review_status = 'pending'. An earlier draft would have split located vs. unlocated pending requests the way stock/stock_lot do, which would have let a located (inventory_location_id populated) and an unlocated (inventory_location_id IS NULL) pending request coexist for the same variant — a real gap, since both would legitimately double-propose against the same on-hand balance. The single index (which does not include inventory_location_id in its key at all) closes this: live-tested, a second pending proposal for the same (tenant, site, variant) — even with a different inventory_location_id (NULL vs. a real location) — is rejected with duplicate key value violates unique constraint "stock_adjustment_request_one_pending_unique".

estimated_impact_cents enables identity.agent_duty_grant.spend_limit_cents enforcement for stock-adjustment proposals — unlike crm, where spend_limit_cents was "essentially always NULL" (crm's autonomous actions are volume-bounded, not money-bounded), a stock adjustment directly changes balance-sheet valuation even with no external payment changing hands, so a real spend-limit dimension applies here from day 1.

batch_id (added 2026-07-10, Header/Line Remediation fix #5): nullable FK into the new stock_adjustment_batch header (composite, not bare — see that table's section above), letting multiple related requests (e.g. a full shelf recount touching 20 variants) be grouped and reviewed together. Purely additive: 0 live rows at build time, so every existing row would have stayed batch_id = NULL regardless; a new single-SKU request may still skip batching entirely.

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

No soft delete — verified live, no deleted_at column; lifecycle fully captured by review_status.

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

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
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. NOT part of the dedup unique key — see above
quantity_delta	numeric	NOT NULL	—	CHECK <> 0 — see table CHECK
reason_id	UUID	nullable	—	FK → inventory.stock_adjustment_reason
batch_id	UUID	nullable	—	Added 2026-07-10 (Header/Line Remediation fix #5). Composite FK (stock_adjustment_request_batch_tenant_fkey) → inventory.stock_adjustment_batch(id, tenant_id), not bare. NULL = ungrouped, the existing default for every pre-fix row
estimated_impact_cents	bigint	nullable	—	Enables agent_duty_grant.spend_limit_cents enforcement — see above
confidence_score	numeric(3,2)	nullable	—
proposed_by_actor_id	UUID	nullable	—	FK → identity.actor
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
review_status	text	NOT NULL	'pending'	CHECK IN (pending,approved,rejected). Defaults pending, not not_required — every row here needs review by construction, same as crm.customer_merge_candidate
review_reason	text	nullable	—
reviewed_by_actor_id	UUID	nullable	—	FK → identity.actor
reviewed_at	timestamptz	nullable	—
decision_provenance	jsonb	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

CHECK constraints (verified live):

Name	Condition
chk_stock_adjustment_request_quantity_nonzero	quantity_delta <> 0
chk_stock_adjustment_request_review_status	review_status IN ('pending','approved','rejected') — note: no not_required value, since a request always needs review
chk_stock_adjustment_request_automation_source	automation_source IN ('human','agent','system','seed')

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on variant_id
• Partial index on review_status WHERE = 'pending'
• stock_adjustment_request_one_pending_unique — UNIQUE, btree (tenant_id, site_id, variant_id) WHERE review_status = 'pending' — the single-index dedup fix; live-tested to reject a second pending proposal regardless of inventory_location_id
• stock_adjustment_request_batch_tenant_fkey — composite FK (batch_id, tenant_id) → stock_adjustment_batch(id, tenant_id), added 2026-07-10. Live-tested: a request in tenant B pointing batch_id at a batch belonging to tenant A is REJECTED (23503).

---

inventory.stock_count

A cycle/full/spot count session header at a site (optionally scoped to one inventory_location). Per the D7 rule, the review→reconciled transition is always needs_approval — the app-enforced state machine (open→counting→review→reconciled/cancelled) is the review mechanism, same pattern as crm.customer_task's status-as-review-seam (no separate review_status column).

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

No soft delete — verified live, no deleted_at column; lifecycle fully captured by status.

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

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
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). IS the review mechanism — no separate review_status
count_type	text	NOT NULL	—	CHECK IN (full,cycle,spot)
started_at	timestamptz	nullable	—
completed_at	timestamptz	nullable	—
started_by_actor_id	UUID	nullable	—	FK → identity.actor
reconciled_by_actor_id	UUID	nullable	—	FK → identity.actor. Populated only on the review→reconciled transition — the needs-approval gate
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
decision_provenance	jsonb	nullable	—
notes	text	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
reconciled_at	timestamptz	nullable	—	Added 2026-07-08 (Remediation Phase 1). Paired with reconciled_by_actor_id — required (NOT NULL) when status='reconciled', see chk_stock_count_reconciled_requires_actor below

CHECK constraints (verified live):

Name	Condition
chk_stock_count_status	status IN ('open','counting','review','reconciled','cancelled')
chk_stock_count_count_type	count_type IN ('full','cycle','spot')
chk_stock_count_automation_source	automation_source IN ('human','agent','system','seed')
chk_stock_count_reconciled_requires_actor	status <> 'reconciled' OR reconciled_by_actor_id IS NOT NULL — added 2026-07-08 (Remediation Phase 1), see subsection below

Indexes:

• PK on id
• Plain index on tenant_id
• Composite index on (site_id, status)

---

inventory.stock_count_line

Per-variant (optionally per-location, per-lot) count line — system quantity vs. counted quantity, with a stored generated variance. New counted_by_actor_id this build for per-line counter attribution (distinct from the session-level stock_count.started_by_actor_id/reconciled_by_actor_id).

Reconciliation tracking + conditional immutability (added 2026-07-10, Header/Line Remediation fix #9): reconciled_at/reconciled_by_actor_id record when and by whom a line was reconciled; a bespoke trigger (NOT the shared blanket platform.reject_append_only_mutation() used elsewhere in this codebase) locks the row only AFTER reconciled_at is set — a line must stay legitimately editable beforehand (filling in counted_qty, correcting a mis-entry). See DR-68 below.

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

No soft delete — verified live, no deleted_at column.

updated_at: trigger-maintained via platform.set_updated_at() (fires alongside the new conditional-lock trigger below — both are BEFORE UPDATE; set_updated_at runs regardless of reconciliation state, but a locked row never reaches it since the lock trigger raises first if OLD.reconciled_at IS NOT NULL).

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	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
lot_id	UUID	nullable	—	FK → inventory.lot
system_qty	numeric	NOT NULL	—	Snapshot of stock.on_hand_qty at count time
counted_qty	numeric	nullable	—	NULL until actually counted
variance	numeric	nullable	generated	GENERATED ALWAYS AS (counted_qty - system_qty) STORED
counted_by_actor_id	UUID	nullable	—	FK → identity.actor. New this build — per-line counter attribution
reconciled_at	timestamptz	nullable	—	Added 2026-07-10 (fix #9). NULL-safe CHECK requires counted_qty be set first — see below. Once set, the row is locked (see trigger below)
reconciled_by_actor_id	UUID	nullable	—	Added 2026-07-10 (fix #9). FK → identity.actor
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

CHECK constraints (verified live):

Name	Condition
chk_stock_count_line_reconciled_requires_counted	reconciled_at IS NULL OR counted_qty IS NOT NULL — added 2026-07-10 (fix #9). NULL-safe: an explicit IS NULL guard on the left branch, not a bare boolean. Live-tested: inserting a line with reconciled_at set but counted_qty still NULL is REJECTED (23514)

Triggers (verified live via pg_trigger):

Name	Fires	Behavior
set_updated_at	BEFORE UPDATE	Standard platform.set_updated_at(), unchanged.
trg_stock_count_line_lock_after_reconciled	BEFORE UPDATE OR DELETE	New 2026-07-10 (fix #9). Function inventory.reject_stock_count_line_mutation_after_reconciled(): if OLD.reconciled_at IS NOT NULL, raises 'stock_count_line % is already reconciled and cannot be modified'; otherwise RETURN COALESCE(NEW, OLD) — correctly handles both UPDATE (returns NEW, allowing it through) and DELETE (falls through to OLD, avoiding the "BEFORE DELETE returning NULL silently cancels the delete" footgun). Deliberately not the shared platform.reject_append_only_mutation() — a stock-count line must stay editable up until reconciliation, unlike a true append-only ledger row. No REVOKE at the grant level; enforcement is entirely via the OLD.reconciled_at check. Live-tested: a pre-reconciliation UPDATE to counted_qty succeeds; once reconciled_at is set, both UPDATE and DELETE are rejected with the exact message above.

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on count_id
• Plain index on variant_id

---

inventory.lot

Batch/lot identity for a variant — supplier lot number, receipt date, expiry, unit cost, lifecycle status. One lot row can be split across sites/locations via stock_lot.

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

Soft delete: deleted_at timestamptz, nullable.

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

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
variant_id	UUID	NOT NULL	—	FK → inventory.item_variant
lot_number	text	NOT NULL	—	UNIQUE per (tenant, variant) — partial-unique, see indexes
supplier_lot_number	text	nullable	—
received_at	timestamptz	nullable	—
expiry_date	date	nullable	—
source_type	text	nullable	—	CHECK IN (purchase,production,adjustment) OR NULL
source_id	UUID	nullable	—
unit_cost_cents	bigint	nullable	—
status	text	NOT NULL	'active'	CHECK IN (active,depleted,expired,quarantined)
created_by_actor_id	UUID	nullable	—	FK → identity.actor
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete

CHECK constraints (verified live):

Name	Condition
chk_lot_source_type	source_type IS NULL OR source_type IN ('purchase','production','adjustment')
chk_lot_status	status IN ('active','depleted','expired','quarantined')
chk_lot_automation_source	automation_source IN ('human','agent','system','seed')

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on variant_id
• Partial index on expiry_date WHERE status = 'active'
• Partial UNIQUE on (tenant_id, variant_id, lot_number) WHERE deleted_at IS NULL
• lot_id_tenant_id_unique — UNIQUE (id, tenant_id). Added 2026-07-10 (Receiving extraction) — a prerequisite for a composite FK from a different schema targeting this table (verified live via pg_get_constraintdef).

Referenced by: receiving.goods_receipt_line.lot_id via the composite FK goods_receipt_line_lot_tenant_fkey (lot_id, tenant_id) → lot(id, tenant_id) — cross-schema, added 2026-07-10.

---

inventory.stock_lot

Per-lot-per-location stock split — how much of a given lot sits at a given (site, location), plus plant condition grading. Closes the pre-pivot v1 feature spec's never-built "Plant condition grading" (module_specs/01_inventory.md Group 15.3).

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

No soft delete — verified live, no deleted_at column.

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

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
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. NOT part of the dedup key when NULL — see indexes
lot_id	UUID	NOT NULL	—	FK → inventory.lot
quantity	numeric	NOT NULL	—
condition_grade	text	nullable	—	CHECK IN (A,B,C,cull) OR NULL. New this build — closes v1's never-built plant condition-grading feature
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

CHECK constraints (verified live):

Name	Condition
chk_stock_lot_condition_grade	condition_grade IS NULL OR condition_grade IN ('A','B','C','cull')

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on lot_id
• Partial UNIQUE on (tenant_id, site_id, variant_id, inventory_location_id, lot_id) WHERE inventory_location_id IS NOT NULL — located stock-lot rows
• Partial UNIQUE on (tenant_id, site_id, variant_id, lot_id) WHERE inventory_location_id IS NULL — unlocated stock-lot rows. Same two-partial-unique NULL-safe split as stock.

---

inventory.kit_component

Bill-of-materials line: which component variants (and what quantity of each) make up a kit variant. Per the D7 rule, kit BOM edits are never AI-authorized (human-only) — light autonomy touch (created_by_actor_id only, no automation_source/review seam) reflects that.

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

Soft delete: deleted_at timestamptz, nullable.

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

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
kit_variant_id	UUID	NOT NULL	—	FK → inventory.item_variant. The kit itself (is_kit=true)
component_variant_id	UUID	NOT NULL	—	FK → inventory.item_variant. A component of the kit
quantity	numeric	NOT NULL	—
sort_order	integer	NOT NULL	0
created_by_actor_id	UUID	nullable	—	FK → identity.actor
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on kit_variant_id
• Plain index on component_variant_id
• Partial UNIQUE on (kit_variant_id, component_variant_id) WHERE deleted_at IS NULL

---

inventory.item_merge_candidate

NEW table this build — a pending item-dedup proposal awaiting human review, mirroring crm.customer_merge_candidate's propose/execute split exactly. review_status DEFAULTs 'pending', same rationale as crm's table and as stock_adjustment_request above.

Dedup improvement over the crm precedent: crm.customer_merge_candidate shipped with no dedup protection at all — a real gap crm's own post-build Section 4 audit found and logged to OPEN_ITEMS, never fixed there (see docs/database/schema_docs/crm.md's open-items list, item 7). item_merge_candidate closes this from day 1 with a two-part fix:

1. chk_item_merge_candidate_pair_order CHECK (item_a_id < item_b_id) enforces canonical pair ordering — the smaller UUID always goes in item_a_id.
2. item_merge_candidate_pending_pair_unique UNIQUE (tenant_id, item_a_id, item_b_id) WHERE review_status='pending' then prevents a duplicate pending proposal for the same canonical-order pair.

Live-tested: inserting the canonical-order pair succeeds; inserting the MIRRORED (reversed item_a_id/item_b_id) pair is rejected by chk_item_merge_candidate_pair_order — the CHECK catches it before the uniqueness index is even reached, which is correct and sufficient (a reversed pair can never satisfy item_a_id < item_b_id, so it never becomes a distinct row the unique index would need to also catch).

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

No soft delete — verified live, no deleted_at column; lifecycle fully captured by review_status.

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

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
item_a_id	UUID	NOT NULL	—	FK → inventory.item. CHECK < item_b_id (canonical order) and <> item_b_id (no self-match)
item_b_id	UUID	NOT NULL	—	FK → inventory.item. See item_a_id
match_basis	text	nullable	—	CHECK IN (name_fuzzy,sku_similar,barcode_match,manual) OR NULL
confidence_score	numeric(3,2)	nullable	—
proposed_by_actor_id	UUID	nullable	—	FK → identity.actor
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
review_status	text	NOT NULL	'pending'	CHECK IN (pending,approved,rejected). Defaults pending, not not_required
review_reason	text	nullable	—
reviewed_by_actor_id	UUID	nullable	—	FK → identity.actor
reviewed_at	timestamptz	nullable	—
decision_provenance	jsonb	nullable	—	Same memory_refs/delegated_by_actor_id key convention as item/item_variant/stock/stock_adjustment_request/stock_count
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

CHECK constraints (verified live):

Name	Condition
chk_item_merge_candidate_self_match	item_a_id <> item_b_id
chk_item_merge_candidate_pair_order	item_a_id < item_b_id — the dedup improvement over crm's gap; live-tested to reject a mirrored pair
chk_item_merge_candidate_match_basis	match_basis IS NULL OR match_basis IN ('name_fuzzy','sku_similar','barcode_match','manual')
chk_item_merge_candidate_review_status	review_status IN ('pending','approved','rejected')
chk_item_merge_candidate_automation_source	automation_source IN ('human','agent','system','seed')

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on item_a_id
• Plain index on item_b_id
• Partial index on review_status WHERE = 'pending'
• item_merge_candidate_pending_pair_unique — UNIQUE, btree (tenant_id, item_a_id, item_b_id) WHERE review_status = 'pending' — works only in combination with the pair-order CHECK above

---

inventory.item_merge

Append-only post-execution merge audit record, mirroring crm.customer_merge exactly. No updated_at, no deleted_at. By the time a row exists here, review already happened on item_merge_candidate (FK'd via candidate_id, nullable — a merge need not originate from a logged candidate). Per the D7 rule, merge execution is always needs-approval, never may-act-alone.

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

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
source_item_id	UUID	NOT NULL	—	FK → inventory.item. The record merged away. CHECK <> target_item_id
target_item_id	UUID	NOT NULL	—	FK → inventory.item. The surviving record. CHECK <> source_item_id
merged_by_actor_id	UUID	nullable	—	FK → identity.actor
candidate_id	UUID	nullable	—	FK → inventory.item_merge_candidate. Nullable — merge need not originate from a logged candidate
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
reason	text	nullable	—
metadata	jsonb	nullable	—	Snapshot supporting reversal in principle (same caveat as crm.customer_merge.metadata — no enforcement built this pass)
decision_provenance	jsonb	nullable	—
created_at	timestamptz	NOT NULL	now()

No updated_at, no deleted_at — permanent post-execution audit fact.

CHECK constraints (verified live):

Name	Condition
chk_item_merge_self_merge	source_item_id <> target_item_id
chk_item_merge_automation_source	automation_source IN ('human','agent','system','seed')

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on source_item_id
• Plain index on target_item_id
• Partial index on candidate_id WHERE NOT NULL

---

inventory.stock_reconciliation_shell (VIEW — added 2026-07-08, Remediation Phase 4, Item 20b)

The FIRST CREATE VIEW in this entire codebase — confirmed via a codebase-wide grep that no prior CREATE VIEW precedent exists anywhere across any of the 15 locked modules. Does NOT count toward inventory's table total (24 tables stays 24; information_schema.tables includes views by default, so the module's own schema-spec test needed a table_type = 'BASE TABLE' filter added to keep its table-count assertion correct at 24 — the view is not a 25th table).

Deliberately MINIMAL, per an explicit pre-build correction: a plain LEFT JOIN (stock LEFT JOIN stock_movement ON stock.last_movement_id = stock_movement.id) exposing:

• stock_id
• tenant_id
• site_id
• variant_id
• on_hand_qty
• last_movement_id
• last_movement_at
• last_movement_recorded_at (the joined movement's own created_at)

Zero aggregation math — no SUM, no GROUP BY, no computed drift value of any kind. pg_get_viewdef independently confirmed by both verification lenses to contain no aggregation whatsoever.

The REAL reconciliation logic is explicitly deferred, not silently assumed correct. The actual drift-detection calculation — sign-aware sums of stock_movement_line quantity deltas since last_movement_id, compared against stock.on_hand_qty, matching movement_type's existing sign vocabulary (received/produced/returned positive; sold/transferred negative; adjusted/counted either sign) — was named as a follow-up and flagged UNVERIFIED against real transfer data, not built this pass. Logged to OPEN_ITEMS.md with that exact trigger text.

Not tenant-isolated by its own RLS — a view inherits the RLS of its underlying tables (stock, stock_movement), both of which already carry the standard tenant-isolation policy; no separate view-level policy exists or is needed.

LIVE-REPRODUCED: the view's column shape matches the list above exactly; behavior confirmed both with a stock row that has a last_movement_id set (LEFT JOIN resolves the movement's created_at into last_movement_recorded_at) and without one (the joined columns come back NULL, not a dropped row — confirming it's a true LEFT JOIN, not an INNER JOIN).

See PROJECT_DECISIONS #40 (Item 20b) for the full record, including the pre-build correction that scoped this view down to a shell.

---

inventory — Design Patterns Summary

Column-count reconciliation (verified live via information_schema.columns, schema inventory)

Table	Cols
item	22
item_variant	35
category	10
item_category	6
tag	9
item_tag	6
barcode	10
item_image	20
option_type	9
variant_option	9
inventory_location	14
stock	22
stock_movement	17
stock_movement_line	15
stock_reservation	15
stock_adjustment_reason	10
stock_adjustment_batch	10
stock_adjustment_request	19
stock_count	16
stock_count_line	14
lot	16
stock_lot	10
kit_component	10
item_merge_candidate	15
item_merge	11
Total	358

Verified live (2026-07-10, post header/line remediation reopen): SELECT count(*) FROM information_schema.tables WHERE table_schema='inventory' AND table_type='BASE TABLE' → 25; SELECT count(*) FROM information_schema.columns c JOIN information_schema.tables t ON c.table_schema=t.table_schema AND c.table_name=t.table_name WHERE c.table_schema='inventory' AND t.table_type='BASE TABLE' → 351. Both match the per-table sum above exactly (as it stood before the 2026-07-18 Phase 2 reopen — see below). (A plain information_schema.columns count with no table_type filter returns 359, since — the identical footgun already documented for information_schema.tables below — it also picks up the 8 columns of the stock_reconciliation_shell VIEW; confirmed live and worth restating here since it bit the raw column count too, not just the table count.)

Reopened 2026-07-18 (Phase 2, PROJECT_DECISIONS #70): item.plant_id dropped, so item's own row above moves 23 → 22, and the per-table sum becomes 350 (351 − 1). The table's stated Total here is 358, not 350, because reconciling this count for Phase 2 surfaced a previously-undisclosed, phase-2-unrelated 8-column undercount already present in the original 351 baseline — the per-table list above is known to be short by 8 columns somewhere among the other 24 tables, root cause not investigated this pass (see docs/open-items/OPEN_ITEMS.md). Only item's own -1 Phase 2 delta is reflected in the itemized rows above; the disclosed 8-column gap is not distributed across any specific row since its source is unknown.

Biggest module so far — no longer a direct shared.plant consumer (Phase 2, 2026-07-18)

inventory is the largest module built to date in v2 (25 tables / 358 columns, +1 view, versus crm's 13/191). From its original 2026-07-06 build until the 2026-07-18 Phase 2 nursery vertical extraction (PROJECT_DECISIONS #70), it was also the first module where the vertical-neutral core structurally met the nursery vertical: item.plant_id was a real, enforced FK into shared.plant.id. Phase 2 removed that direct coupling entirely — plant_id is dropped, item_type is neutralized to ('product','service','kit'), and the vertical-specific relationship now lives two hops away, outside this schema altogether: nursery.item_profile.item_id → inventory.item(id, tenant_id) (a tenant-scoped extension table owned by the new nursery schema) optionally carries its own plant_id → nursery_ref.plant.id (the relocated botanical taxonomy, formerly shared.plant). inventory.item itself is now fully vertical-neutral — no FK, column, or CHECK value here assumes any particular vertical. See docs/database/schema_docs/nursery.md/nursery_ref.md and SCHEMA_CONVENTIONS.md §21 for the governing principle and the extension tables.

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

v1's locked docs/old/schema/schema_modules/schema_inventory.md (2026-06-09) already specified 21 tables / 238 columns for this module — the same vertical-neutral, post-pivot design this build is based on, just pre-shared.plant and pre-autonomy (unlike crm's v1 baseline, which was a genuinely stale pre-pivot placeholder). This build's delta: +3 tables (21→24: stock_adjustment_request, item_merge_candidate, item_merge), +97 columns (238→335) — the added tables plus the autonomy backfill (actor attribution, automation_source, review seams, decision_provenance) applied to the carried-forward 21.

Autonomy treatment varies deliberately by table, not uniformly

• Full (actor attribution + automation_source + review seam + decision_provenance): item, item_variant, item_image, stock, stock_adjustment_request, item_merge_candidate. These are the tables AI enrichment/adjustment-proposal/dedup capabilities actually write to.
• Light (created_by_actor_id only, no automation_source/review seam): category, item_category, tag, item_tag, barcode, option_type, variant_option, inventory_location, stock_adjustment_reason, lot (has automation_source but no review seam — see below), kit_component. Subordinate/catalog tables, or (for kit_component specifically) a table whose edits the D7 table rules never AI-authorized.
• None at all: item_category, item_tag (pure hard-delete join tables — created_by only, nothing else meaningful to attribute).
• Status-as-review-seam, no separate review_status column (same pattern crm.customer_task established): stock_count (open/counting/review/reconciled/cancelled — the review→reconciled transition IS the approval gate).
• Append-only, no review seam of its own (review already happened upstream): stock_movement, stock_movement_line (executes a decision made on stock_adjustment_request or is a deterministic system-sourced event), item_merge (executes a decision made on item_merge_candidate).
• automation_source present but no formal review seam: lot, stock_reservation — provenance is worth recording, but neither table's mutations rise to needing a human approval gate the way adjustments/merges do.

UOM retyping — v1's assumed uuid corrected to the actual shared.unit_of_measure.code shape

v1's schema spec assumed UOM columns would be uuid FKs. shared.unit_of_measure (module #3, locked 2026-07-06) actually uses code (text) as its natural key, not a surrogate uuid. Every UOM column in this module was corrected to match: item_variant.sell_uom_code/stock_uom_code/purchase_uom_code/weight_uom_code, inventory_location.capacity_uom_code — all text, FK → shared.unit_of_measure(code). weight_uom_code is a net-new upgrade over v1, which only had an unenforced free-text weight_uom.

stock_adjustment_request — one dedup index, not two, and why that's the correct fix

stock/stock_lot correctly use a two-partial-unique-index split (WHERE inventory_location_id IS NOT NULL / WHERE inventory_location_id IS NULL) because located and unlocated rows for the same (tenant, site, variant) are legitimately different, coexisting stock records. stock_adjustment_request is different: a pending proposal is pending regardless of which location it targets — two simultaneous pending proposals against the same variant balance is the bug to prevent, not a legitimate coexistence case. The single index stock_adjustment_request_one_pending_unique UNIQUE (tenant_id, site_id, variant_id) WHERE review_status = 'pending' (which deliberately excludes inventory_location_id from its key) is the correct fix — confirmed by the live test described in that table's section.

item_merge_candidate — closing crm's own logged gap, not just mirroring it

crm.customer_merge_candidate has no dedup protection at all (found in crm's own post-build Section 4 audit, logged to OPEN_ITEMS, never fixed). item_merge_candidate mirrors crm's propose/execute split and decision_provenance/review_status shape, but adds the pair-order CHECK + pending-pair partial-unique combination described in that table's section — a deliberate improvement applied from day 1 rather than another deferred gap.

now() is not IMMUTABLE — reservation/lot expiry checked on-read, never in a live index predicate

Same reasoning crm.customer_segment_membership established (DR-47): stock_reservation.expires_at and lot.expiry_date-adjacent status logic never appear inside a WHERE ... > now() partial-index predicate, since Postgres requires index predicates to be IMMUTABLE and now() is only STABLE. stock_reservation_expires_at_idx is instead predicated on the stored status = 'active' column — an expiry sweep candidate list, not a live-computed expiry check.

Generated (STORED) columns — four in this module

item.search_vector and item_variant.search_vector (tsvector, GIN-indexed, real FTS support — not deferred, unlike crm.customer.search_vector), stock.available_qty (on_hand_qty - reserved_qty), and stock_count_line.variance (counted_qty - system_qty). All four verified live via information_schema.columns.is_generated = 'ALWAYS'.

One deferred forward-ref: stock_movement.photo_ref

Plain nullable uuid, no FK — the files schema does not exist in v2 (verified live via \dn: only identity, multi_loc, platform, shared, crm, inventory present as application schemas). Same treatment as crm.customer.consumer_id/crm.customer_tax_certificate.document_ref. item_image.file_id carries the identical deferred treatment for the same reason.

Cost-provenance columns on stock_movement_line

unit_cost_cents, previous_avg_cost_cents, new_avg_cost_cents, cost_impact_cents together make average-cost recalculation fully auditable at the line level — every movement that touches cost leaves a before/after/impact trail, not just a final item_variant.avg_cost_cents value with no history of how it got there.

stock_lot.condition_grade — closing a v1 feature spec gap

v1's pre-pivot feature spec (module_specs/01_inventory.md, Group 15.3, "Plant condition grading") was never actually built into a schema. condition_grade (CHECK IN A/B/C/cull) closes it, scoped to the per-lot-per-location stock_lot row rather than the variant or lot itself, since condition can vary by where a batch is held.

Agent-authority mapping — pure consumer of identity.agent_duty_grant

inventory introduces no new authority mechanism, continuing crm's precedent. Illustrative mapping (no permission rows seeded this pass):

Inventory action	Permission code	authority_level	Limit dimension
Suggest a reorder	inventory:reorder:propose	draft_only	quantity_limit
Propose a stock adjustment	inventory:stock_adjustment:propose	draft_only	quantity_limit + spend_limit_cents
Execute a stock adjustment	inventory:stock_adjustment:execute	needs_approval	N/A
Propose an item merge	inventory:item_merge:propose	draft_only	quantity_limit
Execute an item merge	inventory:item_merge:execute	needs_approval	N/A

Unlike crm (where spend_limit_cents was "essentially always NULL"), inventory is the first module where agent_duty_grant.spend_limit_cents is expected to actually be exercised — a stock adjustment directly changes balance-sheet valuation, so stock_adjustment_request.estimated_impact_cents gives the enforcement point real teeth from day 1.

D7 autonomy-boundary table

Action	Authority
Draft a reorder suggestion	draft_only
Propose a stock adjustment	draft_only
Execute a stock adjustment	needs_approval, always, never may-act-alone
Flag dead/aging stock for markdown	draft_only (surfacing only — Pricing, not built, owns the write)
Suggest an item/plant taxonomy link	draft_only
Record a movement from POS/PO/Orders event	may_act_alone, automation_source='system'
Cycle-count reconciliation (review→reconciled)	needs_approval, always (app-enforced state machine)
Kit BOM edits	never (human-only)
Propose an item merge	draft_only
Execute an item merge	needs_approval, always, never may-act-alone
OCR-extracted receiving cost	draft_only equivalent via is_verified=false

AI_CAPABILITY_GAPS.md — gap rulings applied this pass (all 11 gaps)

Gap	Ruling
G1 (agent-readable catalog flag)	Ruled OUT for schema this pass, per the gaps doc's own closing scope note ("agentic commerce (G1)... not the schema-design runbook"). The existing OPEN_ITEMS G1 row is UPDATED (not duplicated) to record this ruling and re-point its trigger to "when the API layer builds agentic-commerce exposure."
G2, G6, G7, G9, G11	Genuinely none applicable (API/testing/runtime/future-platform concerns, not schema).
G3 (multi-agent handoff)	Considered, correctly deferred; recorded via decision_provenance.delegated_by_actor_id on item, item_variant, stock, stock_adjustment_request, stock_count, item_merge_candidate — no new table.
G4 (semantic layer)	Cross-cutting, not inventory-specific; out of scope for this module.
G5 (agent memory)	Built as documentation only: decision_provenance.memory_refs key on the same six tables as G3.
G8 (proactive/scheduled agents)	Cross-cutting shared infrastructure, not inventory's to build alone; out of scope.
G10 (voice/vision capture)	Partially addressed by Part D's D15 (capture modality) at the design-question level; no dedicated schema mechanism built this pass beyond stock_movement_line.data_source/is_verified.

decision_provenance.memory_refs (G5) and decision_provenance.delegated_by_actor_id (G3) are documented JSONB keys only — no new column or table was added for either, on tables: item, item_variant, stock, stock_adjustment_request, stock_count, item_merge_candidate.

Triggers

set_updated_at (via platform.set_updated_at()) fires BEFORE UPDATE on 19 of the 25 tables (verified live): item, item_variant, category, tag, barcode, item_image, option_type, variant_option, inventory_location, stock, stock_reservation, stock_adjustment_reason, stock_adjustment_request, stock_count, stock_count_line, lot, stock_lot, kit_component, item_merge_candidate. Still the same 19 tables as before the 2026-07-10 reopen — the new stock_adjustment_batch did NOT join this list (see below). 6 tables now lack it, not 5: stock_movement, stock_movement_line, item_merge (no updated_at column at all — immutable append-only), item_category, item_tag (hard-delete join tables, no updated_at either) — all 5 of these deliberately — plus stock_adjustment_batch (has an updated_at column, but verified live via pg_trigger to carry zero triggers at all; a genuine gap, not a design choice — see that table's own section and OPEN_ITEMS). No inventory-schema-specific trigger function existed before 2026-07-10; the reopen adds the module's first two: trg_stock_count_line_lock_after_reconciled (on stock_count_line, see that table's section) and (indirectly, via the shared function reused verbatim) nothing new for platform.reject_append_only_mutation(). Same shared platform.set_updated_at() used across platform, identity, shared, multi_loc, crm.

Service layer

No InventoryService exists yet — this pass is Drizzle schema + hand-written migration + tests against raw SQL only, matching the precedent set by shared, multi_loc, and crm (schema-first, service-layer-later).

JSONB columns

item.attributes, item_variant.attributes/dimensions/guarantee_terms, item_merge.metadata, and decision_provenance on every table carrying the autonomy review seam (item, item_variant, item_image, stock, stock_adjustment_request, stock_count, item_merge_candidate, item_merge).

---

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

1. stock_movement.photo_ref (and item_image.file_id) forward-ref deferred until the files module is designed/built.
2. InventoryService (all service-layer methods) does not exist yet — schema-only this pass, same pattern as agent_duty_grant's and crm's deferred service layers.
3. pg_trgm fuzzy-search GIN indexes (v1's gin_trgm_ops on name/sku) deferred — the pg_trgm Postgres extension is not yet enabled in this database (verified live via pg_extension) — trigger: when pg_trgm is enabled / the Search module properly builds fuzzy search.
4. item.attributes/item_variant.attributes JSONB per-item_type example shapes still not fully documented (carried forward from v1's own deferred note, narrowed in scope now that nursery.item_profile/nursery_ref.plant own taxonomy/care facts — relocated from shared.plant 2026-07-18, Phase 2, PROJECT_DECISIONS #70) — trigger: when InventoryService is built.
5. stock_adjustment_request/agent_duty_grant.spend_limit_cents enforcement is per-action only, no cumulative/period tracking — same documented limitation as agent_duty_grant itself; cross-referenced here since inventory is the first module to actually rely on this limit dimension in practice.
6. The existing G1 OPEN_ITEMS row (agent-readable catalog flag) is updated, not duplicated, to record this pass's ruling and re-point its trigger to "when the API layer builds agentic-commerce exposure."
7. stock_movement.movement_type is still missing v1's deferred 'produced' value (Production-module-dependent) — closed 2026-07-08, see Remediation Phase 3 below.
8. stock_movement_line.from_site_id/to_site_id (line-level multi-site transfer accounting) — re-logged 2026-07-07 (same, already deferred in v1's own design rationale).
9. variant_uom_conversion (dedicated three-way UoM conversion table) — re-logged 2026-07-07 (same, never built in v1 either).
10. New 2026-07-08 (Remediation Phase 4, Item 20b): inventory.stock_reconciliation_shell's real drift-detection logic (sign-aware sums of stock_movement_line deltas since last_movement_id, compared against stock.on_hand_qty) is UNVERIFIED against real transfer data and not yet built — the shipped view is a deliberately minimal shell only. Trigger: when real transfer data exists to verify the sign-aware sum logic against.
11. New 2026-07-08 (Remediation Phase 4, Item 20c): a documented (not DB-enforced — no pg_cron in this environment) scheduled-job contract: any inventory.stock_reservation row with status='active' and expires_at < now() should be transitioned to status='expired' by a periodic job. The schema (status/expires_at) has supported this since inventory's original 2026-07-06 build; only the job itself is unbuilt. Trigger: when a scheduled-job runner exists in this codebase.
12. inventory.stock_movement_line still lacks UNIQUE(id, tenant_id) — the design doc for this same reopen (vrida-header-line-remediation-design-2026-07-10.md §5) named this constraint as belonging to THIS reopen (it's the prerequisite for Purchasing's own still-deferred fix #7, movement-line linkage), but it was NOT added here. — closed 2026-07-10 (same day, Receiving extraction): stock_movement_line_id_tenant_id_unique UNIQUE (id, tenant_id) now exists on inventory.stock_movement_line — confirmed live via pg_get_constraintdef and present in the Drizzle source (packages/db/src/schema/inventory/stock.ts). Added as the prerequisite for receiving.goods_receipt_line.stock_movement_line_id's new composite FK (fix #7, movement-line linkage — landed via the Receiving extraction, not Purchasing's own still-deferred fix #7 as originally guessed), plus 3 sibling constraints on item_variant/lot/stock_movement for the same reason. See the stock_movement_line (and item_variant/lot/stock_movement) table sections above and receiving.md for the full record. Migration: packages/db/migrations/20260710090000_receiving_extraction.sql. Tests: inventory-schema.spec.ts gained a new Section Q (it.each over all 4 constraints, 1 test per table) — 55 → 59 tests, all passing (re-confirmed live: Tests: 59 passed, 59 total).
13. New 2026-07-10 (Header/Line Remediation reopen, disclosure): inventory.stock_adjustment_batch.updated_at is NOT trigger-maintained — confirmed live, zero triggers exist on this table (SELECT tgname FROM pg_trigger WHERE tgrelid='inventory.stock_adjustment_batch'::regclass AND NOT tgisinternal returns zero rows). Every other soft-delete/mutable table in this module uses platform.set_updated_at(); this table's migration never wired it. Found while documenting this reopen, not previously logged. Trigger: next time stock_adjustment_batch is touched — add CREATE TRIGGER set_updated_at BEFORE UPDATE ON inventory.stock_adjustment_batch FOR EACH ROW EXECUTE FUNCTION platform.set_updated_at();, matching every sibling table's pattern.

Fixed 2026-07-07 (PROJECT_DECISIONS #28 — see DR-63 above)

• Erosion closed: stock_reservation.created_by_actor_id added — agent-initiated reservations are now traceable, closing the one real gap the crm/inventory/pricing erosion audit found in this module.

Remediation Phase 1 (2026-07-08)

Cross-cutting senior-architect review pass, closing gaps in the review/approval seam across 6 tables plus one new reconciliation-actor requirement. See PROJECT_DECISIONS #37 for the full cross-module record.

• 6 tables — item, item_image, item_merge_candidate, item_variant, stock, stock_adjustment_request — each gained a chk_*_approved_requires_reviewer CHECK (verified live), closing a fail-open gap where review_status='approved' was previously accepted with no reviewed_by_actor_id recorded:
  • chk_item_approved_requires_reviewer — review_status <> 'approved' OR reviewed_by_actor_id IS NOT NULL
  • chk_item_image_approved_requires_reviewer — review_status <> 'approved' OR reviewed_by_actor_id IS NOT NULL
  • chk_item_merge_candidate_approved_requires_reviewer — review_status <> 'approved' OR reviewed_by_actor_id IS NOT NULL
  • chk_item_variant_approved_requires_reviewer — review_status <> 'approved' OR reviewed_by_actor_id IS NOT NULL
  • chk_stock_approved_requires_reviewer — review_status <> 'approved' OR reviewed_by_actor_id IS NOT NULL
  • chk_stock_adjustment_request_approved_requires_reviewer — review_status <> 'approved' OR reviewed_by_actor_id IS NOT NULL
• stock_count gained a new nullable reconciled_at (timestamptz) column plus chk_stock_count_reconciled_requires_actor — status <> 'reconciled' OR reconciled_by_actor_id IS NOT NULL — the equivalent fail-open closure for stock_count's status-as-review-seam pattern (no separate review_status column, so the CHECK is expressed against status/reconciled_by_actor_id instead of review_status/reviewed_by_actor_id).
• Column-count impact: stock_count.reconciled_at is the only column-count change in this entire remediation phase — 336 → 337 columns (24 tables unchanged). All other changes above are CHECK-constraint-only, no new columns.

Remediation Phase 2 (2026-07-08)

Cross-cutting PK-generation-strategy pass (Remediation Phase 2, Item 6). See PROJECT_DECISIONS #38 for the full cross-module record.

• 3 tables — item_merge, stock_movement, stock_movement_line — had their id column DEFAULT changed from gen_random_uuid() (UUIDv4) to platform.uuid_generate_v7() (UUIDv7). stock_movement and stock_movement_line are 2 of the plan's 4 named "hot ledgers"; item_merge is this module's third append-only table brought in line with the same strategy. Why: UUIDv7 is time-ordered, keeping future time-range partitioning possible on these append-only ledgers without a PK rewrite — something impossible once data lands on a random UUIDv4 PK.
• item_category and item_tag were considered and explicitly EXCLUDED — both are commented "hard-delete" join tables in their own Drizzle source, not append-only, so the time-ordered-partitioning rationale doesn't apply.
• Column-count impact: none — DEFAULT-only change, 337 columns unchanged, 24 tables unchanged.

Remediation Phase 3 (2026-07-08)

Cross-cutting Phase 3 pass (Item 11), pairing a pos-side tender-gate CHECK rename with an inventory-side enum widen in the same migration. See PROJECT_DECISIONS #39 for the full cross-module record.

• stock_movement.movement_type — chk_stock_movement_movement_type widened to add 'produced': was IN ('received','sold','transferred','adjusted','counted','returned'), now also includes 'produced' (verified live via pg_constraint). source_module has permitted 'production' since this table's original 2026-07-06 build, but until this pass no real movement_type existed to pair with it — a nursery propagating its own stock (cuttings/seed → saleable plant) had no way to represent that event in the ledger at all. This closes open item #7 above, a deferral that had been re-logged since 2026-07-07.
• Pure enum-widen: zero column change, a strict superset of the prior CHECK for every existing row — confirmed live that 0 rows had movement_type='produced' before the widen, so the change cannot reject any pre-existing row.
• Column-count impact: none — CHECK-only change, 337 columns unchanged, 24 tables unchanged.
• Migration: packages/db/migrations/20260708240000_phase3_item11_reward_tender_and_produced_stock.sql — the same migration also renames pos's tender-gate CHECK; see pos's own schema doc for that half.

Remediation Phase 4 (2026-07-08)

Cross-cutting Phase 4 pass (Item 20b), the futureproofing/final phase of the 4-phase remediation plan. See PROJECT_DECISIONS #40 for the full cross-module record (Item 20 covers parts a–c; inventory is touched by part b only — part a, platform.outbox, and part c, the documented-not-enforced reservation-expiry job contract, are recorded against platform/inventory process docs respectively, not this module's own DDL beyond the watermark column below).

• inventory.stock.last_movement_id (nullable UUID, FK → inventory.stock_movement.id) — a reconciliation watermark column. 0 rows at build time, zero backfill risk.
• inventory.stock_reconciliation_shell — a new CREATE VIEW, the first VIEW in this entire codebase (confirmed via a codebase-wide grep: no prior CREATE VIEW precedent exists anywhere across any of the 15 locked modules). Deliberately MINIMAL per an explicit pre-build correction: a plain LEFT JOIN (stock LEFT JOIN stock_movement on last_movement_id) exposing stock_id, tenant_id, site_id, variant_id, on_hand_qty, last_movement_id, last_movement_at, last_movement_recorded_at — zero aggregation math (no SUM/GROUP BY/computed drift value). Independently confirmed via pg_get_viewdef by both verification lenses to contain no aggregation whatsoever.
• The REAL reconciliation logic is a named, disclosed follow-up, not silently assumed correct: sign-aware sums of stock_movement_line quantity deltas since last_movement_id, compared against stock.on_hand_qty, matching movement_type's existing sign vocabulary (received/produced/returned positive; sold/transferred negative; adjusted/counted either sign) — explicitly flagged UNVERIFIED against real transfer data and deferred, logged to OPEN_ITEMS.md.
• Does NOT count toward the table total — information_schema.tables includes VIEWs by default, so inventory-schema.spec.ts's hardcoded table-count assertion needed a table_type = 'BASE TABLE' filter added to stay correct at 24 (the view is not a 25th table). This was a genuine, necessary test fix caught during this phase's own test-writing pass, not a pre-existing bug.
• Column-count impact: stock.last_movement_id is the only column added this phase — 337 → 338 columns (24 tables unchanged; +1 view, which is tracked separately from the table count).
• Migration: packages/db/migrations/20260709060000_phase4_item20_outbox_and_stock_reconciliation.sql (part b) — the same migration file's part a builds platform.outbox; see platform's own schema doc for that half.
• Tests: inventory-schema.spec.ts +4 tests (section N) — stock.last_movement_id FK/nullability, the reconciliation shell view's column shape and LEFT-JOIN behavior (both with and without a last_movement_id set). 47/47 total.

Header/Line Remediation reopen (2026-07-10) — fixes #5, #9 (DR-68)

First of this second batch of reopens under the coordinated "Header/Line Remediation" effort (POS/Purchasing/Platform were the first batch — PROJECT_DECISIONS #46/#47/#48). See PROJECT_DECISIONS #49 for the full record, including the pasted, attributed independent-verification summary.

• inventory.stock_adjustment_batch — NEW table (10 cols), a header grouping multiple stock_adjustment_request rows for joint review (fix #5). Carries its own UNIQUE(id, tenant_id) from creation, the prerequisite for stock_adjustment_request's new composite FK. Fully additive — stock_adjustment_request had 0 live rows at build time.
• inventory.stock_adjustment_request.batch_id — new nullable column (+1 col), composite FK (stock_adjustment_request_batch_tenant_fkey) → stock_adjustment_batch(id, tenant_id), not bare.
• inventory.stock_count_line — gained reconciled_at/reconciled_by_actor_id (+2 cols), a NULL-safe CHECK (chk_stock_count_line_reconciled_requires_counted), and a bespoke conditional-immutability trigger (trg_stock_count_line_lock_after_reconciled, function inventory.reject_stock_count_line_mutation_after_reconciled()) — deliberately NOT the shared blanket platform.reject_append_only_mutation(), since a count line must stay editable pre-reconciliation (fix #9). stock_count_line had 0 live rows at build time — zero backfill needed.
• Column-count impact: +1 table (stock_adjustment_batch), +13 columns total (10 new table + 1 on stock_adjustment_request + 2 on stock_count_line) — 338 → 351 columns, 24 → 25 tables, verified live via information_schema.columns/.tables (with the table_type='BASE TABLE' filter — the module's established footgun-avoidance, see the Column-count reconciliation section above).
• 2 disclosed gaps found while documenting this reopen (not silently fixed — logged to OPEN_ITEMS.md as items 12–13 above): (1) stock_movement_line still lacks UNIQUE(id, tenant_id), despite the design doc naming it as belonging to this same reopen (it's a prerequisite for Purchasing's still-deferred fix #7); (2) stock_adjustment_batch.updated_at has no maintaining trigger, unlike every sibling soft-delete table in this module.
• Migration: packages/db/migrations/20260710030000_headerline_inventory_fixes.sql. Schema files: packages/db/src/schema/inventory/stock.ts (new stockAdjustmentBatch export + stockAdjustmentRequest.batch_id), packages/db/src/schema/inventory/count.ts (stockCountLine's new columns + CHECK).
• Tests: inventory-schema.spec.ts new sections O (2 tests: valid same-tenant batch_id; cross-tenant batch_id rejected by the composite FK) and P (5 tests: CHECK rejection, pre-reconciliation update, reconciliation, post-reconciliation UPDATE/DELETE both rejected). File went from 47 to 55 tests (verified directly against the file — 30 individually-titled it() blocks + 1 it.each over ALL_25_TABLES generating 25 sub-tests = 55), all passing.

Inventory Core Write Protection reopen — 2026-07-15 task run

This schema-only reopen adds five columns: stock_movement.posting_integrity, expected_line_count, and request_hash; plus stock_reservation.idempotency_key and request_hash. Final live shape is 26 base tables / 362 base-table columns / 1 view. The view's eight columns are excluded from the base-table count.

Ordinary callers retain catalog editing except item_variant.avg_cost_cents, and may update only stock.reorder_point, reorder_qty, min_qty, and max_qty. Direct operational DML on stock, stock_lot, stock_reservation, stock_movement, and stock_movement_line is closed by privilege, RLS, and trigger layers. Protected functions run under NOLOGIN, non-BYPASSRLS owners with search_path=pg_catalog, fully qualified relations, explicit tenant predicates, and PUBLIC EXECUTE revoked.

All 1,892 pre-build movement headers remain immutable legacy_unverified; no provenance or child rows were fabricated. New protected headers must be complete, carry a deterministic request hash and exact positive expected_line_count, and satisfy deferred header/child cardinality at commit. The Returns companion is a post-RLS/FK AFTER INSERT trigger: it re-reads the persisted receipt line, derives its facts from locked authoritative Returns rows, and produces one complete header plus one line atomically.

That absence statement was authoritative for the Inventory Core checkpoint only. The subsequent Stock Transfer migration now supplies the three Transfer tables and narrow locked-row wrappers, while every non-Transfer path still rejects Transfer attribution. The wrappers remain dormant to runtime roles until a separately approved non-spoofable credential/session gateway exists.

The first independent post-build verifier returned NOT CLEAN. Its ten findings are preserved verbatim and dispositioned in PROJECT_DECISIONS #75. Additive migrations 20260720000016–00020 correct fulfillment ordering, migration locking/source preflight, Returns trigger timing and owner visibility, executor closure, all remaining Inventory PUBLIC function ACLs, temporary migration-role membership, and changed-key fulfillment retries. inventory_command_executor now has zero executable Inventory functions, including through PUBLIC. A partial unique index on complete reservation-fulfillment (tenant_id, source_id) rows makes the reservation itself the structural posting identity; same-key retries must also match the stored request hash, while a changed key is rejected before any second stock effect. The final fresh-context verifier returned CLEAN; Inventory is re-locked at this schema boundary.