Inventory Management — Implementation Blueprint

Architect Agent 4 of the Pancha · 2026-05-02 · Module 5 of 17 in the Aayojana catalog.

1. Module summary

• Name: Inventory Management
• Slug: inventory
• Kind: ERP
• Status: planned (subpackage src/aayojana/inventory/ to be created).

The flow of consumables and stock through the institution. Daily operations consume samagri (rice, ghee, flowers, dhoop, oils), kitchen supplies, books, prasadam ingredients. Some come via purchase, some as donations-in-kind, some as gifts. The module models the master catalog, vendor-side (POs and goods receipts), donor-side (donations-in-kind with 80G applicability), and the dispensing side (event indents, kitchen draws, samagri kits). Stock levels are computed materialised view; samagri kit templates are the operational glue between Events and procurement.

2. Data model

# src/aayojana/inventory/models.py
from datetime import date
from sqlalchemy import (
    Boolean, Date, ForeignKey, Index, Integer, JSON, Numeric, String, Text,
    UniqueConstraint,
)
from sqlalchemy.orm import Mapped, mapped_column, relationship
from aayojana.models.base import AuditMixin, Base, TenantMixin, uuid4_str


class InventoryItem(Base, AuditMixin, TenantMixin):
    """Master catalog of stock-keeping units."""

    __tablename__ = "inventory_items"
    __table_args__ = (
        UniqueConstraint("tenant_id", "sku", name="uq_inv_sku"),
    )
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    sku: Mapped[str] = mapped_column(String(40), nullable=False)
    name: Mapped[str] = mapped_column(String(255), nullable=False)
    name_sanskrit: Mapped[str | None] = mapped_column(Text, nullable=True)
    category: Mapped[str] = mapped_column(String(40), nullable=False)
    # 'samagri' | 'kitchen-raw' | 'kitchen-spice' | 'flowers' | 'dhoop-incense' |
    # 'oils-ghee' | 'books' | 'cleaning' | 'office-supply' | 'prasadam-ingredient' | 'other'
    sub_category: Mapped[str | None] = mapped_column(String(40), nullable=True)
    unit_of_measure: Mapped[str] = mapped_column(String(20), nullable=False)
    # 'kg' | 'g' | 'litre' | 'ml' | 'piece' | 'packet' | 'metre' | 'bundle' | 'dozen'
    secondary_uom: Mapped[str | None] = mapped_column(String(20), nullable=True)
    secondary_conversion_factor: Mapped[float | None] = mapped_column(
        Numeric(12, 4), nullable=True
    )
    # e.g. 1 packet = 0.5 kg → primary kg, secondary packet, factor 0.5
    is_perishable: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    shelf_life_days: Mapped[int | None] = mapped_column(Integer, nullable=True)
    hsn_code: Mapped[str | None] = mapped_column(String(20), nullable=True)
    gst_rate: Mapped[float | None] = mapped_column(Numeric(5, 2), nullable=True)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)


class VendorCatalog(Base, AuditMixin, TenantMixin):
    """Per-vendor rate card for a given inventory item.
    Vendor identity is the same `vendors` table Vitta Vendor Payables uses.
    """

    __tablename__ = "vendor_catalog"
    __table_args__ = (
        UniqueConstraint("vendor_id", "item_id", name="uq_vendor_item"),
    )
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    vendor_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("vendors.id"), nullable=False, index=True
    )  # Agent 1's Vitta vendor master
    item_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("inventory_items.id"), nullable=False, index=True
    )
    is_preferred: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    rate_per_uom: Mapped[float] = mapped_column(Numeric(12, 4), nullable=False)
    minimum_order_qty: Mapped[float | None] = mapped_column(Numeric(12, 3), nullable=True)
    lead_time_days: Mapped[int | None] = mapped_column(Integer, nullable=True)
    last_purchased_on: Mapped[date | None] = mapped_column(Date, nullable=True)
    payment_terms: Mapped[str | None] = mapped_column(String(40), nullable=True)
    # 'advance' | 'net-7' | 'net-15' | 'net-30' | 'cod'


class PurchaseOrder(Base, AuditMixin, TenantMixin):
    __tablename__ = "purchase_orders"
    __table_args__ = (
        UniqueConstraint("tenant_id", "po_number", name="uq_po_number"),
    )
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    po_number: Mapped[str] = mapped_column(String(40), nullable=False)
    branch_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("branches.id"), nullable=True
    )
    vendor_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("vendors.id"), nullable=False
    )
    po_date: Mapped[date] = mapped_column(Date, nullable=False)
    expected_delivery_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    status: Mapped[str] = mapped_column(String(20), default="draft", nullable=False)
    # 'draft' | 'approved' | 'sent' | 'partially-received' | 'completed' | 'cancelled'
    fund_tag: Mapped[str] = mapped_column(String(20), nullable=False)
    # mirrors Vitta's fund segregation: General | Annadana | Building | FCRA | Endowment
    triggered_by_event_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("event_instances.id"), nullable=True
    )
    triggered_by_indent_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("event_samagri_indents.id"), nullable=True
    )
    subtotal: Mapped[float] = mapped_column(Numeric(14, 2), default=0, nullable=False)
    tax_amount: Mapped[float] = mapped_column(Numeric(14, 2), default=0, nullable=False)
    total_amount: Mapped[float] = mapped_column(Numeric(14, 2), default=0, nullable=False)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    approved_by_user_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("users.id"), nullable=True
    )


class PurchaseOrderLine(Base, AuditMixin):
    __tablename__ = "purchase_order_lines"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    po_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("purchase_orders.id", ondelete="CASCADE"), nullable=False
    )
    item_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("inventory_items.id"), nullable=False
    )
    quantity: Mapped[float] = mapped_column(Numeric(14, 3), nullable=False)
    uom: Mapped[str] = mapped_column(String(20), nullable=False)
    rate: Mapped[float] = mapped_column(Numeric(12, 4), nullable=False)
    line_total: Mapped[float] = mapped_column(Numeric(14, 2), nullable=False)
    received_quantity: Mapped[float] = mapped_column(Numeric(14, 3), default=0, nullable=False)
    line_status: Mapped[str] = mapped_column(String(20), default="pending", nullable=False)


class GoodsReceipt(Base, AuditMixin, TenantMixin):
    __tablename__ = "goods_receipts"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    grn_number: Mapped[str] = mapped_column(String(40), nullable=False)
    po_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("purchase_orders.id"), nullable=False
    )
    receipt_date: Mapped[date] = mapped_column(Date, nullable=False)
    received_by_member_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("members.id"), nullable=True
    )
    inspected_ok: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    rejection_notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    vendor_invoice_no: Mapped[str | None] = mapped_column(String(63), nullable=True)
    vendor_invoice_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    vitta_payable_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("vendor_payables.id"), nullable=True
    )
    line_items: Mapped[list | None] = mapped_column(JSON, nullable=True)
    # [{po_line_id, item_id, quantity_received, condition, batch_no, expiry_date}]


class DonationInKind(Base, AuditMixin, TenantMixin):
    __tablename__ = "donations_in_kind"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    donor_member_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("members.id"), nullable=False
    )
    donation_date: Mapped[date] = mapped_column(Date, nullable=False)
    item_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("inventory_items.id"), nullable=False
    )
    quantity: Mapped[float] = mapped_column(Numeric(14, 3), nullable=False)
    uom: Mapped[str] = mapped_column(String(20), nullable=False)
    fair_market_value: Mapped[float | None] = mapped_column(Numeric(14, 2), nullable=True)
    is_eligible_80g: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    receipt_number: Mapped[str | None] = mapped_column(String(40), nullable=True)
    occasion: Mapped[str | None] = mapped_column(String(127), nullable=True)
    branch_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("branches.id"), nullable=True
    )
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)


class DispenseRecord(Base, AuditMixin, TenantMixin):
    __tablename__ = "dispense_records"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    item_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("inventory_items.id"), nullable=False, index=True
    )
    branch_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("branches.id"), nullable=True
    )
    dispense_date: Mapped[date] = mapped_column(Date, nullable=False)
    quantity: Mapped[float] = mapped_column(Numeric(14, 3), nullable=False)
    uom: Mapped[str] = mapped_column(String(20), nullable=False)
    purpose: Mapped[str] = mapped_column(String(40), nullable=False)
    # 'event' | 'daily-nitya' | 'kitchen' | 'gift' | 'sale' | 'maintenance' | 'wastage'
    event_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("event_instances.id"), nullable=True
    )
    indent_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("event_samagri_indents.id"), nullable=True
    )
    issued_to_member_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("members.id"), nullable=True
    )
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)


class StockLevel(Base, AuditMixin, TenantMixin):
    """Materialised current stock per (item, branch). Updated by service layer
    on every receipt / dispense / donation-in-kind / adjustment.
    """

    __tablename__ = "stock_levels"
    __table_args__ = (
        UniqueConstraint("tenant_id", "branch_id", "item_id", name="uq_stock_level_per_branch"),
    )
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    branch_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("branches.id"), nullable=True
    )
    item_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("inventory_items.id"), nullable=False
    )
    current_quantity: Mapped[float] = mapped_column(Numeric(14, 3), default=0, nullable=False)
    uom: Mapped[str] = mapped_column(String(20), nullable=False)
    minimum_threshold: Mapped[float | None] = mapped_column(Numeric(14, 3), nullable=True)
    maximum_threshold: Mapped[float | None] = mapped_column(Numeric(14, 3), nullable=True)
    reorder_quantity: Mapped[float | None] = mapped_column(Numeric(14, 3), nullable=True)
    last_movement_at: Mapped[date | None] = mapped_column(Date, nullable=True)


class StockAdjustment(Base, AuditMixin, TenantMixin):
    """Manual adjustments — physical stock take, breakage, expiry write-off."""

    __tablename__ = "stock_adjustments"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    item_id: Mapped[str] = mapped_column(String(36), ForeignKey("inventory_items.id"))
    branch_id: Mapped[str | None] = mapped_column(String(36), ForeignKey("branches.id"))
    adjustment_date: Mapped[date] = mapped_column(Date, nullable=False)
    delta_quantity: Mapped[float] = mapped_column(Numeric(14, 3), nullable=False)
    # signed: + adds, - removes
    reason: Mapped[str] = mapped_column(String(40), nullable=False)
    # 'physical-count' | 'breakage' | 'expiry' | 'theft' | 'correction' | 'opening-balance'
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    approved_by_user_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("users.id"), nullable=True
    )


class SamagriKitTemplate(Base, AuditMixin, TenantMixin):
    """Templates such as 'Maha Ganapati Homa Kit', 'Marriage Kit Bride-Side',
    'Maha Shivaratri Abhisheka'. When an Event is booked, the template
    drives an indent → dispense or PO.
    """

    __tablename__ = "samagri_kit_templates"
    __table_args__ = (
        UniqueConstraint("tenant_id", "code", name="uq_samagri_kit_code"),
    )
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    code: Mapped[str] = mapped_column(String(40), nullable=False)
    name: Mapped[str] = mapped_column(String(255), nullable=False)
    name_sanskrit: Mapped[str | None] = mapped_column(Text, nullable=True)
    description: Mapped[str | None] = mapped_column(Text, nullable=True)
    seva_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("sevas.id"), nullable=True
    )
    # Optional bind to a DISA Seva — kit can also be event-agnostic.
    seva_category_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("sevaCategories.id"), nullable=True
    )
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)


class SamagriKitLine(Base, AuditMixin):
    __tablename__ = "samagri_kit_lines"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    kit_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("samagri_kit_templates.id", ondelete="CASCADE"), nullable=False
    )
    item_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("inventory_items.id"), nullable=False
    )
    quantity_per_unit: Mapped[float] = mapped_column(Numeric(14, 3), nullable=False)
    uom: Mapped[str] = mapped_column(String(20), nullable=False)
    is_critical: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    # critical = if missing, ritual cannot proceed
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)

3. Reuse map

4. API surface

Routes under /api/inventory/.

5. Service layer

src/aayojana/inventory/services.py:

• record_goods_receipt(po_id, line_quantities, vendor_invoice_no, received_by) -> GoodsReceipt — increments stock_levels, posts a vendor_payable in Vitta, updates PO line received_quantity and PO status.
• record_dispense(item_id, branch_id, quantity, purpose, event_id=None, indent_id=None) -> DispenseRecord — decrements stock_levels; rejects on negative balance unless force_negative=True (audit-logged).
• record_donation_in_kind(donor_member_id, item_id, qty, fmv, is_80g) -> DonationInKind — increments stock; if is_80g, generates 80G receipt via Vitta.
• record_stock_adjustment(item_id, branch_id, delta, reason, approver) -> StockAdjustment — manual correction; mandatory reason; audit-logged.
• generate_samagri_indent_for_event(event_id) -> EventSamagriIndent — reads kit linked to seva on event; multiplies by event's expected_units (e.g. 50 kalyanotsavams × 1 kit each); creates indent with line-by-line shortfall against current stock.
• convert_indent_to_po(indent_id, vendor_id) -> PurchaseOrder — for items not in stock; uses vendor catalog rates.
• convert_indent_to_dispense(indent_id) -> list[DispenseRecord] — for items in stock; depletes.
• recompute_stock_level(item_id, branch_id) — full recompute from movements (audit/reconciliation use only).
• low_stock_alerts(branch_id) -> list[StockLevel] — items below minimum_threshold; feeds dashboard + Comms reminder.
• vendor_rate_compare(item_id) -> list[VendorRateRow] — show rates across vendors for buyer comparison.

6. UI / Templates

• Items list with category filter chips, low-stock badge.
• Item detail — current stock per branch, movement ledger, vendor rate compare panel.
• PO list + new-PO form with vendor dropdown that auto-populates rate; PDF print template for "Purchase Order on Letterhead".
• GRN form — fed from PO, shows expected vs received qty, batch/expiry capture for perishables.
• Donations-in-kind register — donor lookup (autocomplete from members), 80G toggle, receipt number reservation.
• Dispense quick-form — used by kitchen and event-runner roles; barcode/QR scan optional later.
• Samagri Kit template editor — drag-and-drop lines from item catalog; quantity per unit-event input; mark critical items; preview "what gets bought when this kit fires".
• Low-stock dashboard widget — list with reorder-now button.
• Indent worksheet — for an event, show: kit lines × multiplier, current stock, shortfall, suggested PO vendor, suggested dispense.
• Stock adjustment ledger — append-only; reason filter.

7. Migration plan

Single migration. Rationale: all tables interlock via FKs. Vendor master (Vitta) and event tables (Events module) FKs are forward references: 0021 creates them as nullable strings with deferred FK constraints filled in later migrations (0023-events, Vitta vendor master at its own rev). Use Alembic op.create_foreign_key(..., use_alter=True) for the cycle.

8. Cross-module dependencies

Reads from

• Members (Agent 3): members.id — donor identity, receiver-on-GRN, dispense-recipient.
• Sevas (existing DISA): sevas.id, sevaCategories.id — kit binding.
• Vitta (Agent 1): vendors.id master.
• Branches: branches.id.

Writes to

• Vitta (Agent 1): every GRN posts a vendor_payables row (to flow into Accounts Payable); fund_tag from PO carries through. 80G donations-in-kind generate certificate via Vitta.
• Audit Trail (Agent 2): high-value PO approval, stock adjustments, force-negative dispenses → audit_event.
• Comms (Agent 5): PO sent → email to vendor; low-stock alert → email to procurement custodian; donation-in-kind → 80G acknowledgement to donor.
• Reports (Agent 5): samagri burn-rate, PO ageing, donor-of-the-month for the trustee pack.

Read by

• Events module (this agent): kit lookup; indent generation; dispense recording.
• Maintenance module (this agent): consumable usage during maintenance (e.g. lubricant, filters) goes through dispense with purpose='maintenance'.

9. Implementation phases

• Phase A — Schemas + basic CRUD. Migration 0021. Items, vendors-catalog, PO/GRN basic flow, donations-in-kind, dispense, stock_levels recompute, kits CRUD.
• Phase B — Cross-module integrations. Wire Vitta vendor master + payables; wire Sevas binding; emit Comms PO email; 80G certificate path.
• Phase C — Workflows + dashboards. Indent worksheet, low-stock alerts cron, vendor rate-compare, samagri burn-rate report, kit editor drag-drop.

10. Open questions

1. Samagri unit conversions — store both UoMs and a conversion factor? Or compute on read from a global UoM table? Recommend: store secondary UoM + factor on item; central UoM table optional later.
2. Branch-less items — some items (e.g. publications) have a tenant-wide stock not per branch. Allow stock_levels.branch_id NULL? Yes — already nullable.
3. Batch / expiry tracking — perishables need lot-level tracking. Should stock_levels decompose into stock_batches? Defer to Phase C; capture batch in GRN line_items JSON for now.
4. Fund tag inheritance — does dispense carry the same fund tag as the PO that originally funded the stock? Recommend: yes, propagate through stock_level audit chain. Hard for donations-in-kind; default to General there.
5. Vendor master ownership — Inventory's vendor_catalog references vendors. Where is vendors created — Vitta or here? Recommend: Vitta owns it (vendor is a payable counterparty). Confirm with Agent 1.
6. 80G on donations-in-kind — tax law restricts 80G to specific item categories (no jewellery, no perishables). Validate is_eligible_80g against item category? Yes.
7. Reorder POs from low-stock alert — auto-create draft PO or only suggest? Recommend: suggest only; require human approval.
8. Samagri kit hierarchy — can kits include sub-kits? E.g. "Marriage Kit" includes "Ganapati Homa Kit". Recommend: defer; flatten in Phase A.
9. Vendor invoice OCR / GST validation — out of scope for v1?
10. Stock-take workflow — periodic physical count produces stock_adjustment rows. Need a structured stock_take_sessions parent table? Defer to Phase C.