ERP_Distribution/Distribution/docs/02_stock_ledger.md

02 — Stock Ledger

Status: Done
Depends On: 01 — Item Catalog

---

Goal

Introduce stock tracking per location. Every warehouse and vehicle holds a quantity of each item. Stock is never edited directly — all changes happen through movement transactions. The current balance is always the materialized sum kept in sync with the transaction log.

---

Background / Context

Two tables work together:

• StockBalance — one row per (ItemId, LocationId). Updated atomically on every movement. Used for fast reads and enforcing stock limits (e.g. driver can't sell more than loaded).
• StockMovement — append-only transaction log. Source of truth. Used for audit, reconciliation at ride close (Step 07), and re-deriving balance if drift is ever suspected.

Both are updated in the same UoW commit. They must always agree.

Location abstraction: A single LocationId (+ LocationType enum) covers both warehouses and vehicles. Same query pattern regardless of location type.

---

Data Model Changes

New Enum — LocationType

public enum LocationType { Warehouse, Vehicle }

New Enum — MovementType

public enum MovementType
{
    Sale,
    CustomerReturn,
    Transfer,       // covers: warehouse→vehicle (load), vehicle→warehouse (driver return), warehouse→warehouse
    Damage,
    Adjustment
}

New Entity — StockBalance

One row per (ItemId, LocationId). Updated on every movement.

Column	Type	Notes
Id	Guid	PK
FK_ItemId	Guid	FK → Item
LocationId	Guid	FK → Warehouse or Vehicle
LocationType	LocationType	Warehouse or Vehicle
Quantity	decimal	Current balance. Never goes below 0
UpdatedAt	DateTime	Last movement time

No IsActive / no soft delete — balance rows are permanent once created.
Composite unique index on (FK_ItemId, LocationId).

New Entity — StockMovement

Append-only. Never updated or deleted.

Column	Type	Notes
Id	Guid	PK
FK_ItemId	Guid	FK → Item
FromLocationId	Guid?	Null for Sale, Damage, Adjustment (single-location)
FromLocationType	LocationType?	Null when FromLocationId is null
ToLocationId	Guid?	Null for Sale, Damage (stock leaves system)
ToLocationType	LocationType?	Null when ToLocationId is null
Quantity	decimal	Always positive — direction implied by movement type
Type	MovementType
ReasonCode	string?	Required for Adjustment and Damage
ReferenceId	Guid?	Links to the originating business event (OrderId, RideId, etc.)
CreatedAt	DateTime
CreatedBy	Guid	UserId of who triggered this

---

Category Configuration

Category gets one new flag to support configurable customer returns:

Column	Type	Notes
AllowCustomerReturn	bool	Default false. If false, CustomerReturn movements are rejected for items in this category

---

API Endpoints

Base route: /api/stock

Query

Method	Route	Description
GET	/balances	Get all stock balances (paginated). Filter by locationId, locationType, itemId
GET	/balances/{itemId}/total	Total stock of one item across all locations
GET	/movements	Get movement history (paginated). Filter by locationId, itemId, type, from, to

Write

Method	Route	Description
POST	/transfer	Move stock between two locations
POST	/damage	Record damaged stock at a location
POST	/customer-return	Record a customer return to a vehicle
POST	/adjustment	Manual correction at a location (requires reason code)

---

Request / Response Shapes

GET /balances response (paginated)

{
  "page": 1,
  "pageSize": 20,
  "total": 85,
  "items": [
    {
      "itemId": "...",
      "itemName": "Sprite 330ml",
      "locationId": "...",
      "locationType": "Warehouse",
      "quantity": 120,
      "updatedAt": "2026-08-19T10:00:00"
    }
  ]
}

GET /balances/{itemId}/total response

{
  "itemId": "...",
  "itemName": "Sprite 330ml",
  "totalQuantity": 340,
  "breakdown": [
    { "locationId": "...", "locationType": "Warehouse", "quantity": 200 },
    { "locationId": "...", "locationType": "Vehicle",   "quantity": 140 }
  ]
}

GET /movements response (paginated)

{
  "page": 1,
  "pageSize": 20,
  "total": 412,
  "items": [
    {
      "id": "...",
      "itemId": "...",
      "itemName": "Sprite 330ml",
      "fromLocationId": "...",
      "fromLocationType": "Warehouse",
      "toLocationId": "...",
      "toLocationType": "Vehicle",
      "quantity": 50,
      "type": "Transfer",
      "reasonCode": null,
      "referenceId": "...",
      "createdAt": "2026-08-19T08:00:00",
      "createdBy": "..."
    }
  ]
}

POST /transfer request

{
  "itemId": "...",
  "fromLocationId": "...",
  "fromLocationType": "Warehouse",
  "toLocationId": "...",
  "toLocationType": "Vehicle",
  "quantity": 50,
  "referenceId": "..."
}

POST /damage request

{
  "itemId": "...",
  "locationId": "...",
  "locationType": "Warehouse",
  "quantity": 3,
  "reasonCode": "Broken during handling"
}

POST /customer-return request

{
  "itemId": "...",
  "vehicleId": "...",
  "quantity": 2,
  "referenceId": "..."
}

POST /adjustment request

{
  "itemId": "...",
  "locationId": "...",
  "locationType": "Warehouse",
  "quantity": -2,
  "reasonCode": "Counting error corrected after audit"
}

---

Business Rules

1. StockBalance.Quantity must never go below 0. Any movement that would result in negative stock is rejected with a 400.
2. Adjustment and Damage require a non-empty ReasonCode.
3. CustomerReturn is rejected if Category.AllowCustomerReturn = false for the item's category.
4. Transfer debits FromLocation and credits ToLocation atomically.
5. Both StockBalance update and StockMovement insert happen in the same transaction — never one without the other.
6. StockMovement rows are never updated or deleted. Wrong entries are corrected by a counter-movement.
7. All list endpoints return paginated responses.

---

Open Questions

None — all design decisions resolved in planning discussion.

---

Implementation Notes

Feature folder: Features/Stock/
Entities: StockBalance.cs, StockMovement.cs in Models/
Migration: Add after entities and DbContext are updated
Service layer: One StockService handles all movement types. Each write method: begins transaction → validates stock → inserts movement → updates balance → commits.
Category change: Add AllowCustomerReturn column to existing Category entity and migration.