Inventory Logic

Stock system, FIFO lots, guards, expiry, adjustments, and alerts

Three-Level Stock System

AquaDealers uses a three-tiered inventory architecture. Each level serves a distinct purpose — from the master stock count down to the immutable audit trail.

Level 1: `inventory` — Master Stock Level

Purpose

One row per (dealer, branch, product) combination. This is the source of truth for current stock quantity and default pricing.

Column	Type	Description
`id`	UUID	Primary key
`dealer_id`	UUID	Owning dealer
`branch_id`	UUID	Branch location
`product_id`	UUID	Product reference
`quantity_in_stock`	numeric	Current available quantity
`cost_price`	numeric	Latest cost price (from most recent purchase)
`selling_price`	numeric	Default selling rate for feed products
`mrp`	numeric	Maximum retail price (medicines)
`medicine_discount_percentage`	numeric	Default discount % for medicine products
`min_stock_alert`	numeric	Low-stock alert threshold
`track_expiry`	boolean	Whether expiry tracking is enabled (typically true for medicines)
`expiry_date`	date	Legacy expiry field (lot-level expiry preferred)

Unique constraint: (dealer_id, branch_id, product_id) — ensures exactly one inventory row per product per branch per dealer. Duplicate rows are prevented at the database level.

Level 2: `inventory_lots` — FIFO Lot Tracking

Purpose

Each stock purchase creates one lot. Lots are consumed in FIFO order (earliest expiry first, then earliest received). This enables accurate COGS calculation and expiry management.

Column	Type	Description
`id`	UUID	Primary key
`inventory_id`	UUID	FK to inventory table
`stock_purchase_id`	UUID	FK to the purchase that created this lot
`batch_number`	text	Manufacturer batch/lot number
`expiry_date`	date	Lot expiry date (null for non-expiry-tracked items)
`quantity_received`	numeric	Original quantity when the lot was created
`remaining_quantity`	numeric	Current remaining quantity (decreases as lots are consumed)
`cost_price`	numeric	Cost price at time of purchase (for COGS)
`selling_price`	numeric	Selling price at time of purchase
`mrp`	numeric	MRP at time of purchase
`received_at`	timestamptz	When the lot was received
`is_expired`	boolean	Whether the lot has been marked as expired
`expired_at`	timestamptz	When the lot was marked expired

Consumption order: expiry_date ASC, received_at ASC — earliest expiry first, then earliest received.

Level 3: `inventory_movements` — Immutable Audit Trail

Purpose

Every stock change — regardless of source — creates an immutable movement record. This is the complete audit trail for all inventory changes.

Column	Type	Description
`id`	UUID	Primary key
`inventory_id`	UUID	FK to inventory table
`product_id`	UUID	Product reference (denormalized for queries)
`lot_id`	UUID	FK to inventory_lots (if lot-level operation)
`reference_type`	text	Type of operation that caused the movement
`reference_id`	UUID	ID of the originating record
`quantity_change`	numeric	Signed quantity change (+increase, −decrease)
`notes`	text	Optional description
`created_at`	timestamptz	Timestamp of the movement

Reference Types

reference_type	Direction	Description
`bill`	− decrease	Stock sold to a farmer
`purchase`	+ increase	Stock received from a supplier
`bill_cancellation`	+ increase	Stock returned from a cancelled bill
`bill_edit`	± either	Stock adjustment from editing a bill’s quantities
`manual_adjustment`	± either	Manual stock correction by dealer
`bill_return`	+ increase	Stock returned by farmer
`transfer_out`	− decrease	Stock transferred to another branch
`transfer_in`	+ increase	Stock received from another branch

FIFO Lot Consumption

How Lots Are Consumed During Billing

Bill is created via create_bill_v2 RPC
For each bill item, consume_inventory_lots is called
Lots are fetched ordered by expiry_date ASC, received_at ASC
Lots are deducted one by one until the requested quantity is fulfilled
Each lot deduction creates a bill_item_lot_allocations record (lot_id, allocated_quantity)
Each lot deduction creates an inventory_movements record
The parent inventory.quantity_in_stock is decremented

Bill Item Lot Allocations

The bill_item_lot_allocations table records exactly which lots were consumed for each bill item. This is critical for:

COGS accuracy: Profit reports use the actual lot cost_price, not the average
Bill cancellation: Lots can be restored precisely when a bill is cancelled
Audit: Full traceability from bill item back to purchase lot

FIFO Preview

The preview_fifo_bill_lines function provides a read-only preview of which lots would be consumed before the bill is actually saved. This lets the dealer see the cost basis and lot details before committing.

Stock Guards

Multiple layers of protection prevent inventory from going negative or being corrupted by concurrent operations.

Negative Stock Prevention Hard Block

create_bill_v2 raises an "Insufficient stock" error if quantity_in_stock < requested quantity. The bill is not created.

Adjustment Guard Hard Block

adjust_inventory_stock_v1 checks that the resulting quantity (new_qty) won't be negative. Rejects the adjustment if new_qty < 0.

Row-Level Locking Concurrency

During bill creation, the inventory row is locked with FOR UPDATE. This prevents race conditions where two simultaneous bills could both read the same stock level and both succeed, resulting in negative stock.

How row locking works

PostgreSQL's SELECT ... FOR UPDATE acquires a row-level exclusive lock. If two transactions try to lock the same inventory row simultaneously, the second one waits until the first commits or rolls back. This serializes concurrent stock deductions for the same product and guarantees the stock check is atomic with the deduction.

Auto-Fanout Triggers

When new products or branches are created, inventory rows are automatically scaffolded so that every branch always has an inventory entry for every product.

`trg_fanout_product_to_branches`

Fires on: New product created

Action: Creates empty inventory rows (quantity = 0) in all active branches for the new product.

`trg_fanout_branch_to_products`

Fires on: New branch created

Action: Creates empty inventory rows (quantity = 0) for all existing products in the new branch.

These triggers ensure the inventory matrix is always complete. Dealers never need to manually "add" a product to a branch — it's there as soon as the product or branch is created, ready for stock to be added via purchases.

Expiry Management

How Expiry Tracking Works

Products with track_expiry = true (typically medicines) have expiry dates on their inventory lots
Expiry is tracked at the lot level, not the inventory level
Each lot has its own expiry_date from the manufacturer

Expiry Processing

RPC	Purpose
`process_expired_inventory_lots`	Batch job to scan and flag expired lots
`mark_lot_as_expired`	Marks a specific lot as expired (sets `is_expired = true`, `expired_at = now()`)

Expired lots are skipped during FIFO consumption. When consume_inventory_lots fetches lots for deduction, it filters out lots where is_expired = true. This prevents selling expired medicines to farmers.

Dashboard Expiry Widgets

The dashboard shows medicines approaching expiry in configurable windows:

30 days Expiring within 30 days — urgent
60 days Expiring within 60 days — warning
90 days Expiring within 90 days — plan ahead

Stock Adjustments

`adjust_inventory_stock_v1` RPC

Allows dealers to manually correct stock quantities outside of normal purchase/sale flows.

Adjustment Types

Type	Direction	Use Case
Increase	+ stock	Found damaged items intact, physical count correction (more than system)
Decrease	− stock	Damaged goods, lost items, expired stock, physical count correction (less than system)

Creates an inventory_movement with reference_type = 'manual_adjustment'
Updates inventory.quantity_in_stock directly
Guard: rejects if resulting quantity would be negative

Rate Adjustments

Bulk Rate Change Route: /inventory/rate-adjustment

Dealers can update pricing for multiple products at once.

Change selling_price (feed products)
Change mrp (medicine products)
Change medicine_discount_percentage
Rate adjustment history is tracked for audit

Rate change impact on existing bills

Rate changes only affect future bills. Existing bills retain the rates they were created with. The per-farmer rate memory (upsertFarmerProductDiscount) will still hold the old rate until the farmer's next bill overwrites it with the new rate.

Low Stock Alerts

How Low Stock Alerts Work

Each inventory row has a configurable min_stock_alert threshold
Dashboard widget shows all items where quantity_in_stock ≤ min_stock_alert
Threshold is per-product, per-branch (set on the inventory row)
Setting min_stock_alert = 0 effectively disables the alert for that product

Low stock alerts are passive — they appear on the dashboard as a list of items below threshold. There are no push notifications or automatic reorder functionality. Dealers check the dashboard to see what needs restocking.

Inventory Logic

Three-Level Stock System

Level 1: inventory — Master Stock Level

Purpose

Level 2: inventory_lots — FIFO Lot Tracking

Purpose

Level 3: inventory_movements — Immutable Audit Trail

Purpose

Reference Types

FIFO Lot Consumption

How Lots Are Consumed During Billing

Bill Item Lot Allocations

FIFO Preview

Stock Guards

Negative Stock Prevention Hard Block

Adjustment Guard Hard Block

Row-Level Locking Concurrency

Auto-Fanout Triggers

trg_fanout_product_to_branches

trg_fanout_branch_to_products

Expiry Management

How Expiry Tracking Works

Expiry Processing

Dashboard Expiry Widgets

Stock Adjustments

adjust_inventory_stock_v1 RPC

Adjustment Types

Rate Adjustments

Bulk Rate Change Route: /inventory/rate-adjustment

Low Stock Alerts

How Low Stock Alerts Work

Level 1: `inventory` — Master Stock Level

Level 2: `inventory_lots` — FIFO Lot Tracking

Level 3: `inventory_movements` — Immutable Audit Trail

`trg_fanout_product_to_branches`

`trg_fanout_branch_to_products`

`adjust_inventory_stock_v1` RPC