Phase 02: Inventory & Stock Ledger

Duration: 1 week Goal: Implement stock management with SQL-based ledger (TigerBeetle optional)

---

1. Stock Entry DocType

1.1 Stock Entry (Movement Document)

class StockEntry(BaseDocType):
    """Record of stock movement."""

    # Identity
    entry_no: str = Field(max_length=50, unique=True)

    # Type
    entry_type: str  # Receipt, Issue, Transfer, Adjustment

    # Source/Target
    source_warehouse: str | None = None
    target_warehouse: str | None = None

    # Reference
    supplier: str | None = None  # For receipts
    urc: str | None = None  # For issues
    reference_doc_type: str | None = None  # PO, SO, etc.
    reference_doc_id: str | None = None

    # Dates
    posting_date: date
    posting_time: time | None = None

    # Status
    docstatus: int = 0  # 0=Draft, 1=Submitted, 2=Cancelled

    # Items (child table)
    items: list["StockEntryItem"] = []

    class Meta:
        table_name = "stock_entry"
        indexes = ["entry_type", "posting_date", "docstatus"]

1.2 Stock Entry Item (Child)

class StockEntryItem(BaseDocType):
    """Line item in a Stock Entry."""

    parent: str  # FK to StockEntry

    item: str  # FK to Item
    item_name: str  # Denormalized for display

    qty: Decimal
    uom: str = "Nos"

    # Valuation
    rate: Decimal = Decimal("0")
    amount: Decimal = Decimal("0")

    # Batch/Serial
    batch_no: str | None = None
    serial_nos: str | None = None  # Comma-separated

    # Warehouse (for transfers)
    source_warehouse: str | None = None
    target_warehouse: str | None = None

    class Meta:
        table_name = "stock_entry_item"
        indexes = ["parent", "item"]

1.3 Tasks

• Create StockEntry DocType
• Create StockEntryItem child DocType
• Implement submit() controller method
• Implement cancel() controller method

---

2. Stock Ledger Entry

2.1 Stock Ledger Entry DocType

class StockLedgerEntry(BaseDocType):
    """Immutable record of stock change. Append-only."""

    # Reference
    voucher_type: str  # StockEntry, PurchaseReceipt, etc.
    voucher_no: str
    voucher_detail_no: str | None = None

    # What moved
    item: str  # FK to Item
    warehouse: str  # FK to Warehouse

    # Quantity
    actual_qty: Decimal  # Positive = In, Negative = Out

    # Valuation (FIFO/Weighted Avg)
    incoming_rate: Decimal = Decimal("0")
    outgoing_rate: Decimal = Decimal("0")
    stock_value: Decimal = Decimal("0")
    stock_value_difference: Decimal = Decimal("0")

    # Balance after this entry
    qty_after_transaction: Decimal
    valuation_rate: Decimal = Decimal("0")

    # Batch/Serial
    batch_no: str | None = None
    serial_no: str | None = None

    # Timestamps
    posting_date: date
    posting_time: time

    class Meta:
        table_name = "stock_ledger_entry"
        indexes = [
            "item",
            "warehouse",
            ("item", "warehouse"),
            "posting_date",
            "voucher_no"
        ]

2.2 Tasks

• Create StockLedgerEntry DocType
• Make it append-only (no updates/deletes)
• Index for fast balance queries

---

3. Bin (Current Stock Balance)

3.1 Bin DocType

class Bin(BaseDocType):
    """Current stock balance per Item + Warehouse. Denormalized cache."""

    item: str  # FK to Item
    warehouse: str  # FK to Warehouse

    # Current Balance
    actual_qty: Decimal = Decimal("0")
    reserved_qty: Decimal = Decimal("0")  # For pending orders
    ordered_qty: Decimal = Decimal("0")  # Incoming POs

    # Derived
    projected_qty: Decimal = Decimal("0")  # actual - reserved + ordered

    # Valuation
    stock_value: Decimal = Decimal("0")
    valuation_rate: Decimal = Decimal("0")

    class Meta:
        table_name = "bin"
        unique_together = [("item", "warehouse")]

3.2 Tasks

• Create Bin DocType
• Update Bin on every StockLedgerEntry
• get_stock_balance(item, warehouse) API

---

4. Stock Controller Logic

4.1 Stock Entry Controller

class StockEntryController(BaseController[StockEntry]):
    """Business logic for stock movements."""

    async def before_submit(self, doc: StockEntry) -> None:
        """Validate before submission."""
        await self.validate_warehouses(doc)
        await self.validate_stock_availability(doc)

    async def on_submit(self, doc: StockEntry) -> None:
        """Create Stock Ledger Entries."""
        for item in doc.items:
            await self.create_sle(doc, item)
            await self.update_bin(item)

    async def on_cancel(self, doc: StockEntry) -> None:
        """Reverse Stock Ledger Entries."""
        for item in doc.items:
            await self.create_reverse_sle(doc, item)
            await self.update_bin(item)

    async def validate_stock_availability(self, doc: StockEntry) -> None:
        """Check stock before issue."""
        if doc.entry_type == "Issue":
            for item in doc.items:
                balance = await self.get_stock_balance(
                    item.item,
                    item.source_warehouse
                )
                if balance < item.qty:
                    raise ValidationError(
                        f"Insufficient stock for {item.item_name}"
                    )

4.2 Tasks

• Implement StockEntryController
• Stock validation on Issue
• SLE creation logic
• Bin update logic
• Cancellation with reversal

---

5. Stock Query APIs

5.1 Endpoints

Endpoint	Description
GET /api/v1/stock/balance?item=X&warehouse=Y	Current stock
GET /api/v1/stock/ledger?item=X	Stock movement history
GET /api/v1/stock/projected?item=X	Projected availability
GET /api/v1/stock/warehouse-summary?warehouse=Y	All items in warehouse

5.2 Tasks

• Implement stock balance API
• Implement stock ledger query API
• Implement warehouse summary API
• Add pagination for large results

---

6. TigerBeetle Integration (Optional/Later)

6.1 Stock-Keeper Adapter

class StockKeeperAdapter:
    """TigerBeetle-based stock ledger for high throughput."""

    async def create_account(self, item: str, warehouse: str) -> str:
        """Create TigerBeetle account for Item+Warehouse combo."""
        ...

    async def transfer(
        self,
        item: str,
        from_warehouse: str | None,
        to_warehouse: str | None,
        qty: Decimal
    ) -> str:
        """Atomic stock transfer."""
        ...

    async def reserve(
        self,
        item: str,
        warehouse: str,
        qty: Decimal,
        timeout_seconds: int = 300
    ) -> str:
        """Create pending reservation."""
        ...

6.2 Tasks (Phase 2 or Later)

• Define StockLedgerProtocol
• Implement SQLStockLedgerAdapter (default)
• Implement TigerBeetleStockLedgerAdapter (optional)
• Config switch between adapters

---

7. Verification Checklist

• Stock Receipt flow working
• Stock Issue with validation working
• Stock Transfer between warehouses working
• Bin balances updating correctly
• Stock balance API returning correct values
• Cancellation reversing entries correctly
• Unit tests for all flows

---

Sample Test Cases

async def test_stock_receipt():
    """Test receiving goods into warehouse."""
    entry = await create_stock_entry(
        entry_type="Receipt",
        target_warehouse="WH-MUMBAI-MAIN",
        items=[
            {"item": "PHONE-001", "qty": 100, "rate": 15000}
        ]
    )
    await entry.submit()

    balance = await get_stock_balance("PHONE-001", "WH-MUMBAI-MAIN")
    assert balance == 100

async def test_stock_issue_insufficient():
    """Test issue fails with insufficient stock."""
    entry = await create_stock_entry(
        entry_type="Issue",
        source_warehouse="WH-MUMBAI-MAIN",
        items=[
            {"item": "PHONE-001", "qty": 200}  # Only 100 in stock
        ]
    )

    with pytest.raises(ValidationError, match="Insufficient stock"):
        await entry.submit()