Skip to content

Inventory safety — Tier 1

Backend domain: app/graphql/inventories/, app/graphql/stock_reservations/ Migration: add_inventory_safety_tier_1 RBAC Path / Resource: STOCK_RESERVATIONS

Problem / motivation

The inventory module has solid plumbing (multi-location, lot/serial, FIFO/LIFO) but no safety rails on the write path. Three correctness gaps stand out and are bundled here because they touch the same hot path in InventoryRepository.update_quantity and InvoiceEventHandler.post_insert / post_update:

  1. Overselling. Invoices deduct without checking availability. There is no reservation layer, so two concurrent invoices can both draw from the same unit.
  2. Silent negative stock. update_quantity() happily drives inventory.quantity below zero. CostingService.consume() then back-fills cost from Item.last_unit_cost, masking the problem in COGS.
  3. Location/lot opacity in invoicing. Invoices always deduct from the default location regardless of where the goods actually shipped from. Lot-tracked items can't honour lot assignment per line.

Tier 1 also adds the missing inventory_logs(source_id) index so the reversal path on invoice update/void stays O(log n).

In scope

  • New table stock_reservations plus a full resource (model, repo, service, GraphQL).
  • New column items.allow_negative_stock (defaults false) plus InsufficientStockError raised inside update_quantity().
  • New columns invoice_details.location_id and invoice_details.lot_id (nullable; fall back to default location).
  • New index ix_inventory_logs_source_id.
  • New Path.STOCK_RESERVATIONS / Resource.STOCK_RESERVATIONS RBAC enums plus a backfill script.
  • Threading of location_id / lot_number from InvoiceDetailInput → InvoiceFactory.to_event → InvoiceDetailEventDTO → InvoiceEventHandler → InventoryRepository.update_quantity.

Out of scope

  • Order-time reservations (orders still don't reserve — reservations are a primitive other features will build on).
  • Cycle counts, transfers, reorder automation (Tier 2).
  • Approval workflow for adjustments (Tier 3).
  • Migrating existing inventory.quantity < 0 rows — operators must reconcile manually.

Data model changes

Table Change Why
stock_reservations new Soft reservations: inventory_id, source_type, source_id, quantity, status (ACTIVE/CONSUMED/RELEASED/EXPIRED), location_id, expires_at.
items + allow_negative_stock BOOLEAN NOT NULL DEFAULT false Per-item policy switch. Defaults false so existing rows immediately get the safety net.
invoice_details + location_id UUID NULL FK inventory_locations.id, + lot_id UUID NULL FK inventory_lots.id Per-line dimension support. Null means "use default location".
inventory_logs + ix_inventory_logs_source_id Reversal queries (delete_inventory_logs(source_id)) currently scan; this fixes O(n).

Alembic revision: add_inventory_safety_tier_1, down_revision add_unit_cost_snapshot.

A separate concern bundled into this release (not a migration — a one-off script): the original 20250702_rev1.py declared inventory.reorder_level / reorder_quantity / lead_time / min_stock / max_stock as UUID. The revision is patched in place so fresh tenants get INTEGER; existing tenants run scripts/fix_inventory_reorder_column_types.py to convert. These fields are unused, so the cast is safe.

GraphQL surface

New queries

stockReservationsByItem(itemId: UUID!, locationId: UUID): [StockReservation!]!
stockReservationsBySource(sourceType: String!, sourceId: UUID!): [StockReservation!]!
availableQuantity(itemId: UUID!, locationId: UUID): Decimal!

availableQuantity returns on_hand − sum(active_reservations) for the resolved location.

New mutations

reserveStock(reservation: StockReservationInput!): StockReservation!
releaseStockReservation(reservationId: UUID!): StockReservation!

StockReservationInput: itemId, quantity, sourceType (free-form string the caller owns — e.g. "quote", "order"), sourceId, locationId?, expiresAt?.

Changed inputs

InvoiceDetailInput: new optional locationId: UUID, lotId: UUID. InvoiceDetailResponse: new fields locationId, lotId. ItemInput / ItemResponse: new field allowNegativeStock: Boolean! (defaults false).

New enum

StockReservationStatus: ACTIVE | CONSUMED | RELEASED | EXPIRED.

Behaviour rules

  • Negative stock check. Every call into update_quantity() with a quantity < 0 computes projected = inventory.quantity + quantity (in the resolved location). If projected < 0 and item.allow_negative_stock = false, raise InsufficientStockError(message). Available stock excludes active reservations for negative draws originating outside the source that owns those reservations — invoices created from an order consume that order's reservations first.
  • Reservation lifecycle. reserveStock writes an ACTIVE row. releaseStockReservation flips it to RELEASED. Reservations are advisory (not enforced as DB-level locks); availableQuantity and the negative-stock guard read them.
  • Per-line location/lot. When an invoice line carries location_id, the deduction targets that location's inventory row. Same for lot_id (resolved to lot_number for the repository call). null keeps the existing default-location behaviour.
  • Reversal. delete_inventory_logs(source_id) already restores quantities; with the new index it stays fast and unchanged.

RBAC

  • New Path.STOCK_RESERVATIONS and Resource.STOCK_RESERVATIONS enums.
  • Backfill: scripts/backfill_stock_reservations_path_permissions.py adds PathPermission(role, path=STOCK_RESERVATIONS, can_view=True) for every role of every tenant.

Tests

  • tests/graphql/inventories/test_negative_stock_guard.py — guard raises when allow_negative_stock=False, allows when True, applies per-location.
  • tests/graphql/stock_reservations/test_stock_reservation_repository.py — create, release, list-by-source, available-quantity arithmetic.

What's being implemented

New files:

  • alembic/versions/20260524_add_inventory_safety_tier_1.py
  • app/graphql/stock_reservations/__init__.py
  • app/graphql/stock_reservations/models/__init__.py
  • app/graphql/stock_reservations/models/stock_reservation.py
  • app/graphql/stock_reservations/repositories/__init__.py
  • app/graphql/stock_reservations/repositories/stock_reservation_repository.py
  • app/graphql/stock_reservations/services/__init__.py
  • app/graphql/stock_reservations/services/stock_reservation_service.py
  • app/graphql/stock_reservations/strawberry/__init__.py
  • app/graphql/stock_reservations/strawberry/stock_reservation_input.py
  • app/graphql/stock_reservations/strawberry/stock_reservation_response.py
  • app/graphql/stock_reservations/queries/__init__.py
  • app/graphql/stock_reservations/queries/stock_reservation_queries.py
  • app/graphql/stock_reservations/mutations/__init__.py
  • app/graphql/stock_reservations/mutations/stock_reservation_mutations.py
  • scripts/backfill_stock_reservations_path_permissions.py
  • tests/graphql/stock_reservations/__init__.py
  • tests/graphql/stock_reservations/test_stock_reservation_repository.py
  • tests/graphql/inventories/__init__.py
  • tests/graphql/inventories/test_negative_stock_guard.py

Modified files:

  • app/core/exceptions.py — InsufficientStockError.
  • app/graphql/items/models/item.py — allow_negative_stock column.
  • app/graphql/items/strawberry/item_input.py / item_response.py — surface the field.
  • app/graphql/inventories/repositories/inventory_repository.py — get_available_quantity(), negative-stock guard in update_quantity().
  • app/graphql/invoices/models/invoice_detail.py — location_id, lot_id columns.
  • app/graphql/invoices/strawberry/invoice_detail_input.py / invoice_detail_response.py — surface the fields.
  • app/graphql/invoices/factories/invoice_factory.py — thread location/lot in to_event.
  • app/events/common/models/detail_event_dto.py — location_id, lot_id fields.
  • app/graphql/invoices/events/invoice_event_handler.py — pass location/lot to update_quantity.
  • app/graphql/rbac/models/models.py — Path.STOCK_RESERVATIONS, Resource.STOCK_RESERVATIONS.
  • mkdocs.yml — wire this page.

Frontend contract

  • Item form — new checkbox "Allow selling below zero stock" (defaults off). Tooltip: "When enabled, this item can be invoiced beyond on-hand quantity."
  • Invoice line editor — optional Location picker per line (defaults to invoice-level location or Main) and, for LOT/SERIAL items, a Lot picker fed from getInventoryLotsByItemId. Both omit on legacy invoices.
  • Invoice creation error handling — surface InsufficientStockError as a per-line validation error referencing the item name and available qty.
  • Stock reservation API — reserveStock / releaseStockReservation are exposed but not auto-consumed in this tier; downstream quote/order screens can opt in when ready.
  • Available qty hint — invoice/quote line editors can call availableQuantity to show "X available" inline.

Future additions

  • Auto-consume reservations on invoice creation when the invoice was generated from a reserved order (Tier 2 — needs the order workflow to opt into reservations first).
  • Expire stale ACTIVE reservations via a nightly taskiq job; the expires_at column is in place.
  • Stock transfers, cycle counts, reorder automation, approval workflows (Tiers 2–3).