Skip to content

Item Inventory Analytics

Date: 2026-06-03

Problem / motivation

The frontend product-detail page (ProductInventoryPage) renders a rich inventory dashboard — velocity, days-of-coverage, in-transit quantity, a typed movement ledger, top clients, and a ranged stock-history chart. The backend exposes the static configuration (getItemById → Item.inventory) and a few primitives (availableQuantity, stockReservationsByItem, getInventoryLotsByItemId, getIventoryLogsByItemId, getItemStockOverTime) but not the analytical surface the page needs. This feature fills that gap with five additive, item-scoped queries.

In scope

  • itemMovements — typed, paginated, filterable movement ledger enriched with the source document's reference number and counterparty.
  • itemStockMetrics — units sold in a window, daily velocity, days-of-coverage, projected stockout date.
  • itemIncomingQuantity — quantity on open purchase orders not yet received.
  • itemTopClients — top N clients for an item by net units sold in a window.
  • itemStockHistory — daily stock levels over a day-range with optional movement-event markers (generalises getItemStockOverTime, which is locked to a single month and has no event annotations).
  • A source_type column on inventory_logs so each log row is self-describing, plus a write-path fix so manual adjustments record their source_id.

Out of scope

  • An AJU-#### auto-sequence for manual adjustments (none exists today; adjustments are surfaced as AJUSTE with their description, no external reference number). Tracked under Future additions.
  • A preferred-supplier / brand field on Item (the page's "Medline" label) — separate data-model change, not part of this feature.
  • Smart reorder recommendation engine. The existing Item.inventory (reorderLevel, reorderQuantity, leadTime) + averageCost already let the frontend compose the "Reorden recomendado" card; no new query needed.

Data model changes

One migration: 20260603_add_source_type_to_inventory_logs.py (revision add_inv_log_source_type, down_revision legal_status_int_credits).

  • Add inventory_logs.source_type — nullable INTEGER, stores InventoryLogSourceType (a new dedicated IntEnum, kept separate from the global SourceType to avoid coupling). Comment documents the mapping.
  • upgrade() backfills existing rows from the free-text method column (invoice→INVOICE, purchase order→PURCHASE_ORDER, credit→CREDIT, supplier invoice→SUPPLIER_INVOICE, manual→MANUAL_ADJUSTMENT).
  • downgrade() drops the column.

No new tables, no new RBAC Path/Resource (these are additive reads on the existing item/inventory surface, consistent with getItemById/getIventoryLogsByItemId, which carry no path-permission decorator). No backfill of PathPermission rows required.

GraphQL surface

enum MovementType { VENTA COMPRA AJUSTE DEVOLUCION OTHER }

type ItemMovement {
  id: UUID!
  movementType: MovementType!
  referenceNumber: String      # FAC-…, OC-…, NC-… ; null for adjustments
  sourceType: Int              # InventoryLogSourceType
  sourceId: UUID
  counterpartyName: String     # client / supplier / null
  quantityChange: Decimal!     # signed
  balanceAfter: Decimal!       # running balance (inventory_logs.new_quantity)
  unitValue: Decimal           # item average cost at read time
  totalValue: Decimal          # |quantityChange| * unitValue
  createdBy: String!
  occurredAt: DateTime!
}
type ItemMovementPage { records: [ItemMovement!]!; totalCount: Int! }

type ItemStockMetrics {
  windowDays: Int!
  unitsSoldInWindow: Decimal!
  dailyVelocity: Decimal!
  availableQuantity: Decimal!
  daysOfCoverage: Decimal      # null when velocity == 0
  projectedStockoutDate: Date  # null when velocity == 0
}

type ItemTopClient { clientId: UUID!; clientName: String!; unitsSold: Decimal! }

type ItemStockHistoryPoint { value: Decimal!; date: DateTime! }
type ItemStockHistoryEvent {
  date: DateTime!; movementType: MovementType!; quantityChange: Decimal!; balanceAfter: Decimal!
}
type ItemStockHistory {
  points: [ItemStockHistoryPoint!]!
  events: [ItemStockHistoryEvent!]!
}

extend type Query {
  itemMovements(itemId: UUID!, locationId: UUID, types: [MovementType!], limit: Int! = 8, offset: Int! = 0): ItemMovementPage!
  itemStockMetrics(itemId: UUID!, windowDays: Int! = 30, locationId: UUID): ItemStockMetrics!
  itemIncomingQuantity(itemId: UUID!): Decimal!
  itemTopClients(itemId: UUID!, windowDays: Int! = 90, limit: Int! = 5): [ItemTopClient!]!
  itemStockHistory(itemId: UUID!, rangeDays: Int! = 60, withEvents: Boolean! = false, locationId: UUID): ItemStockHistory!
}

RBAC

None new — additive reads on existing item data.

Background tasks / cron

None.

Frontend contract

  • Stock metric cards (Velocidad / Cobertura): itemStockMetrics(itemId, windowDays: 30).
  • Posición de stock → "+N en tránsito": itemIncomingQuantity(itemId).
  • Movimientos recientes (+ Todos/Ventas/Compras/Ajustes/Devol. tabs): itemMovements(itemId, types: […], limit, offset). movementType drives the tab filter; balanceAfter is the running-balance column; referenceNumber/counterpartyName populate the row label.
  • Top clientes (90d): itemTopClients(itemId, windowDays: 90, limit: 4).
  • Histórico de stock (7D/30D/60D/90D): itemStockHistory(itemId, rangeDays, withEvents: true).
  • Static panels (Configuración de inventario, Stock actual/Físico/Disponible/Reservado, Reorden recomendado) keep using getItemById, availableQuantity, stockReservationsByItem as today.

What's being implemented

New / changed SQLAlchemy models: - app/graphql/inventories/models/inventory_log_source_type.py — new InventoryLogSourceType enum. - app/graphql/inventories/models/inventory_log.py — add source_type column.

New / changed repositories: - app/graphql/inventories/repositories/inventory_repository.py — update_quantity gains a source_type parameter, persisted on the log. - app/graphql/items/repositories/item_analytics_repository.py — new repo with the five analytical queries.

Changed write paths (so new logs carry source_type and adjustments carry source_id): - app/graphql/invoices/events/invoice_event_handler.py - app/graphql/invoices/repositories/invoice_balance_repository.py - app/graphql/purchase_orders/repositories/purchase_order_quantity_received_repository.py - app/graphql/inventories/repositories/manual_inventory_adjustment_repository.py

New services / GraphQL: - app/graphql/items/services/item_analytics_service.py - app/graphql/items/strawberry/item_analytics_response.py (types + MovementType enum) - app/graphql/items/queries/item_analytics_queries.py (auto-merged via *Queries)

Migration: - alembic/versions/20260603_add_source_type_to_inventory_logs.py

Tests: - tests/graphql/items/test_item_analytics.py

Open questions

None — resolved before coding: full-fidelity movements (migration), invoiced-sales basis for velocity/top-clients, open-PO basis for in-transit.

What shipped

All five queries shipped as specified, plus the inventory_logs.source_type column and write-path fixes:

  • itemMovements, itemStockMetrics, itemIncomingQuantity, itemTopClients, itemStockHistory — auto-mounted via the ItemAnalyticsQueries (*Queries) class.
  • InventoryLogSourceType enum + inventory_logs.source_type column (migration add_inv_log_source_type, with method→source_type backfill).
  • update_quantity now takes source_type; the invoice, credit, purchase-order and manual-adjustment write paths all populate it. The manual-adjustment path now records source_id (the adjustment's id) instead of None.
  • Tests: tests/graphql/items/test_item_analytics.py (9 cases — movement classification / filter / pagination, velocity+coverage, zero-velocity, void exclusion, top-client ranking, open-PO in-transit, stock-history points/events).
  • task all green; affected-area tests green.

Released in 1.8.0.

Future additions

  • AJU-#### adjustment numbering + counterparty for transfers — deferred: needs a new auto-sequence; low priority since adjustments are internal.
  • Preferred-supplier / brand on Item — deferred: separate data-model change.
  • Per-location movement/metric breakdown beyond the locationId filter — deferred until a multi-warehouse UI exists.