Chapter 713 min read
Inventory & Product Management Architecture for Khoji Nepal
Inventory & Product Management Architecture for Khoji Nepal
A synthesized report combining research from ChatGPT, Gemini Deep Research, and independent Claude analysis — distilled into a clear, actionable architecture for our Laravel + Next.js hyperlocal marketplace.
1. Where All Three Sources Agree (The Universal Truths)
All three research reports converge on the same foundational principles. This is where we should place our confidence:
✅ Model by BEHAVIOR, not by CATEGORY
[!IMPORTANT] Don't think: "grocery, fashion, electronics, service, digital" Think: "how is this item sold, fulfilled, priced, tracked, and delivered?"
This is the single most important insight. The word "electronics" or "grocery" is just a human label. What actually matters to the database is:
- Does this thing have physical stock? → affects checkout
- Does it expire? → affects fulfillment ordering
- Does each unit need individual tracking? → affects the database row structure
- Is it a time-slot? → affects availability UI
✅ Template → Variant → Offer (The Universal Data Model)
Every system studied (Odoo, ERPNext, Saleor, Medusa, Shopify, Magento) uses this 3-layer pattern:
ProductTemplate (the concept: "Nike Air Max")
└── ProductVariant (the purchasable thing: "Nike Air Max, Size 42, Black")
└── VendorOffer (what a specific vendor sells: price, stock, delivery zone)
✅ Hybrid Relational + JSON for Attributes
All three reports reject pure EAV (Entity-Attribute-Value) at scale. The consensus:
- Fixed columns for universal fields (name, price, status, vendor_id, category_id)
- JSON/JSONB column for dynamic, category-specific attributes (expiry_date, warranty_months, screen_size)
- Relational tables for inventory, variants, batches, and serials
2. The 5 Product Archetypes (What Can Be Sold)
These are the fundamental "types of things" your system needs to support. Every product in the world falls into one of these:
| # | Archetype | Stock Model | Fulfillment | Nepal Examples |
|---|---|---|---|---|
| 1 | Storable Physical | Integer count per location | Ship/Pickup | Clothing, utensils, stationery |
| 2 | Perishable Physical | Batch/Lot with expiry dates | Ship/Pickup (FEFO) | Fruits, vegetables, dairy, meat, medicines |
| 3 | Serialized Physical | Individual unit tracking | Ship/Pickup + Warranty | Laptops, phones, AC units, motorcycles |
| 4 | Service | Time-slot / Capacity | Schedule/Booking | Haircuts, plumbing, tutoring, photography |
| 5 | Digital | Infinite (or license-counted) | Download/Access | E-books, software, online courses |
[!NOTE] Bundles (combo packs, gift sets) and Rentals are not separate archetypes — they are modifiers on top of the base archetypes. A "Computer Set" bundle is just a virtual product that, upon sale, decrements the stock of its child storable products.
3. The 4 Tracking Behaviors (How Stock Is Counted)
This is orthogonal to the archetype. It answers: "When a sale happens, what happens in the database?"
Level 0: none — No Tracking
Used for: Services, Digital products
What happens on sale: Nothing inventory-related. Just creates an order.
DB impact: No inventory tables touched.
Level 1: quantity — Simple Count
Used for: Grocery items, stationery, clothing, most physical goods
What happens on sale: stock_on_hand -= quantity_ordered
DB impact: UPDATE inventory_items SET quantity_on_hand = quantity_on_hand - 1
Alert: When stock falls below reorder_point
This is the answer to your "grocery store" question. Nobody tracks individual noodle packets. They track the SKU count:
- Wai Wai Chicken 75g → Stock: 1,200 packets
- Customer buys 3 → Stock: 1,197 packets
- When stock hits 200 → Auto-alert: "Reorder 500 packets"
Level 2: batch — Lot/Batch Tracking
Used for: Perishable food, dairy, medicine, cosmetics
What happens on sale: System picks the OLDEST unexpired batch first (FEFO)
DB impact: UPDATE inventory_batches SET quantity = quantity - 1 WHERE expires_at > NOW() ORDER BY expires_at ASC
A fruit vendor doesn't track individual apples. They track batches:
- Batch #2025-06-15: 5 crates of apples, expires June 18
- Batch #2025-06-16: 3 crates of apples, expires June 19
- System always sells from Batch #2025-06-15 first
Level 3: serial — Individual Unit Tracking
Used for: Laptops, phones, AC units, expensive electronics
What happens on sale: A specific serial number is assigned to the order
DB impact: UPDATE inventory_serials SET status = 'sold', sold_at = NOW() WHERE serial_number = 'DELL-XPS-2025-0042'
Each physical unit has a unique identity. You know exactly which laptop went to which customer. Critical for warranty claims.
4. The Vendor Catalog Question: Per-Vendor vs. Shared
This is a critical marketplace architecture decision.
Our Recommendation: Per-Vendor Catalogs (with future shared-catalog possibility)
For Khoji Nepal's hyperlocal context, vendors are selling vastly different, often unique goods (a local farm's organic vegetables are NOT the same product as another farm's). This is not Amazon where 50 vendors sell the exact same iPhone.
Start with per-vendor catalogs:
- Each vendor creates and manages their own products
- Products belong to a vendor via
vendor_idon the product table - The public storefront aggregates all vendor products for search and discovery
- Categories are shared and admin-controlled (vendors pick from a fixed taxonomy)
Why not shared catalog now?
- Shared catalogs require complex deduplication logic (matching "the same product" across vendors)
- Our vendors sell highly heterogeneous goods (fresh produce, handcrafts, services)
- It adds significant complexity for marginal benefit at our scale
[!TIP] The schema we propose below is forward-compatible with a shared catalog. If you later want to add a
master_productstable and link multiple vendors to the same canonical product, thevendor_offerspattern slots right in.
5. The Recommended Database Schema for Khoji Nepal
This is the concrete, Laravel-ready schema that synthesizes all research:
Core Product Tables
┌─────────────────────────────────────────────────────────┐
│ product_families │
│ (Admin-defined templates: "Grocery", "Electronics") │
├─────────────────────────────────────────────────────────┤
│ id, name, slug │
│ product_archetype: enum(storable, perishable, │
│ serialized, service, digital) │
│ tracking_type: enum(none, quantity, batch, serial) │
│ attribute_schema: JSON ← defines what extra fields │
│ this family requires │
│ variant_attributes: JSON ← which attrs create variants │
│ e.g., ["size", "color"] │
│ requires_shipping: boolean │
│ is_active: boolean │
└─────────────────┬───────────────────────────────────────┘
│ 1:N
┌─────────────────▼───────────────────────────────────────┐
│ products (the canonical identity) │
├─────────────────────────────────────────────────────────┤
│ id, vendor_id (FK), product_family_id (FK) │
│ category_id (FK → categories) │
│ name, slug, description │
│ base_price: decimal │
│ compare_at_price: decimal (for showing "was Rs.X") │
│ attributes: JSON ← dynamic fields validated against │
│ product_family.attribute_schema │
│ media: JSON (array of image/video URLs) │
│ tags: JSON │
│ status: enum(draft, active, archived) │
│ is_featured: boolean │
│ timestamps │
└─────────────────┬───────────────────────────────────────┘
│ 1:N
┌─────────────────▼───────────────────────────────────────┐
│ product_variants (the purchasable unit) │
├─────────────────────────────────────────────────────────┤
│ id, product_id (FK) │
│ sku: string (unique) │
│ barcode: string (nullable, for POS scanning) │
│ variant_attributes: JSON ← {"size": "L", "color": "Red"}│
│ price_override: decimal (nullable, overrides base_price) │
│ weight_grams: integer (nullable) │
│ is_active: boolean │
│ timestamps │
└─────────────────┬───────────────────────────────────────┘
│ 1:1
▼
Inventory Tables (only for tracked products)
┌─────────────────────────────────────────────────────────┐
│ inventory_items (stock summary per variant per location) │
├─────────────────────────────────────────────────────────┤
│ id, product_variant_id (FK) │
│ location_id (FK → vendor_locations, for multi-store) │
│ quantity_on_hand: integer (default 0) │
│ quantity_reserved: integer (default 0) ← held for orders│
│ quantity_available: GENERATED (on_hand - reserved) │
│ reorder_point: integer (nullable) │
│ reorder_quantity: integer (nullable) │
│ low_stock_alert_sent: boolean │
│ timestamps │
└─────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────┐
│ inventory_batches (ONLY for tracking_type = 'batch') │
├─────────────────────────────────────────────────────────┤
│ id, inventory_item_id (FK) │
│ batch_number: string │
│ quantity: integer │
│ manufactured_at: date (nullable) │
│ expires_at: date │
│ cost_price: decimal (nullable, for FIFO costing) │
│ status: enum(active, expired, depleted) │
│ timestamps │
└─────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────┐
│ inventory_serials (ONLY for tracking_type = 'serial') │
├─────────────────────────────────────────────────────────┤
│ id, inventory_item_id (FK) │
│ serial_number: string (unique) │
│ status: enum(in_stock, reserved, sold, returned, │
│ defective, warranty_claim) │
│ sold_at: timestamp (nullable) │
│ order_id: FK (nullable, links to the sale) │
│ notes: text (nullable) │
│ timestamps │
└─────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────┐
│ inventory_movements (audit ledger — append-only) │
├─────────────────────────────────────────────────────────┤
│ id, inventory_item_id (FK) │
│ type: enum(purchase, sale, return, adjustment, │
│ transfer, damage, expired) │
│ quantity_change: integer (positive = in, negative = out) │
│ batch_id: FK (nullable) │
│ serial_id: FK (nullable) │
│ reference_type: string (e.g., "order", "manual") │
│ reference_id: integer (nullable) │
│ notes: text (nullable) │
│ performed_by: FK → users │
│ timestamps │
└─────────────────────────────────────────────────────────┘
Service-Specific Tables (for archetype = 'service')
┌─────────────────────────────────────────────────────────┐
│ service_slots (availability for services) │
├─────────────────────────────────────────────────────────┤
│ id, product_variant_id (FK) │
│ day_of_week: enum(mon, tue, wed, thu, fri, sat, sun) │
│ start_time: time │
│ end_time: time │
│ max_bookings: integer │
│ is_active: boolean │
│ timestamps │
└─────────────────────────────────────────────────────────┘
Supporting Tables
┌─────────────────────────────────────────────────────────┐
│ categories (admin-controlled taxonomy) │
├─────────────────────────────────────────────────────────┤
│ id, parent_id (self-referencing for tree structure) │
│ product_family_id (FK — links category to a family) │
│ name, slug, icon, image │
│ description │
│ sort_order: integer │
│ is_active: boolean │
│ timestamps │
└─────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────┐
│ vendor_locations (for hyperlocal multi-store vendors) │
├─────────────────────────────────────────────────────────┤
│ id, vendor_id (FK) │
│ name (e.g., "Main Shop", "Warehouse") │
│ address, latitude, longitude │
│ is_default: boolean │
│ is_active: boolean │
│ timestamps │
└─────────────────────────────────────────────────────────┘
6. How the Schema Handles Every Scenario
🥕 Scenario: Fruit & Vegetable Vendor
Product Family: "Fresh Produce" (archetype=perishable, tracking=batch)
Product: "Organic Fuji Apples"
Variant: "Organic Fuji Apples — 1kg" (no size/color variants)
Inventory Item: location=Main Shop, qty_on_hand=50 kg
Batch #1: batch="2025-06-15", qty=30kg, expires=June 18
Batch #2: batch="2025-06-16", qty=20kg, expires=June 19
On sale → system picks Batch #1 first (FEFO)
On June 18 → Batch #1 auto-marked "expired", remaining qty written off
🍜 Scenario: Grocery Store (Noodles, Biscuits)
Product Family: "Packaged Grocery" (archetype=storable, tracking=quantity)
Product: "Wai Wai Chicken Noodles 75g"
Variant: single variant (no size/color)
Inventory Item: location=Main Shop, qty_on_hand=1200, reorder_point=200
On sale of 3 → qty_on_hand becomes 1197
When qty hits 200 → vendor gets low-stock alert
Vendor restocks 500 → inventory_movement logged, qty becomes 697+500=1197
[!TIP] This is the answer to your grocery question. Nobody opens the carton. The vendor just says "I received 2 cartons = 48 packets" and the system adds 48 to stock. Barcode scanning at POS can automate the decrement later.
💻 Scenario: Electronics Shop
Product Family: "Consumer Electronics" (archetype=serialized, tracking=serial)
Product: "Dell XPS 15 Laptop"
Variants: "i7/16GB/512GB" and "i7/32GB/1TB"
Inventory Item: location=Showroom, qty_on_hand=5
Serial records:
SN: DELL-XPS-2025-0042 → status=in_stock
SN: DELL-XPS-2025-0043 → status=in_stock
SN: DELL-XPS-2025-0044 → status=sold (order #1234)
...
On sale → specific serial assigned to order → warranty starts
On return → serial status → "returned", then "in_stock" after inspection
✂️ Scenario: Salon / Service Provider
Product Family: "Personal Services" (archetype=service, tracking=none)
Product: "Men's Haircut"
Variant: "Standard Cut" and "Premium Cut"
No inventory tables. Instead:
Service Slots:
Mon-Sat: 10:00-18:00, max_bookings=3 per hour
Sun: closed
Customer books → slot count decremented
No physical stock involved at all
📚 Scenario: Digital Product Seller
Product Family: "Digital Downloads" (archetype=digital, tracking=none)
Product: "Nepali Typography Font Pack"
Variant: single
Price: Rs. 500
On purchase → system generates a download link (or sends file via email)
No stock, no shipping, no location
7. The Product Family → Schema-Driven UI Flow
This is where everything clicks for the vendor dashboard (Inertia app):
Step 1: Vendor picks a Category
"Fresh Produce → Fruits"
Step 2: System looks up the ProductFamily for that category
ProductFamily: "Fresh Produce"
archetype: perishable
tracking: batch
attribute_schema: {
"origin": { "type": "text", "required": false },
"storage_type": { "type": "select", "options": ["room_temp", "refrigerated", "frozen"] },
"is_organic": { "type": "boolean", "default": false }
}
variant_attributes: ["weight_option"]
Step 3: Frontend renders ONLY relevant fields:
✅ Name, Description, Price, Images (universal)
✅ Origin, Storage Type, Is Organic (from attribute_schema)
✅ Weight Option variants (from variant_attributes)
✅ Batch entry form (from tracking=batch): batch#, qty, expiry
❌ Serial Number field → HIDDEN
❌ Warranty field → HIDDEN
❌ Download URL field → HIDDEN
Step 4: Vendor submits → backend validates JSON against schema → saved
The frontend never hardcodes "if category is grocery, show expiry field". It reads the schema and renders dynamically. New categories can be added by an admin without any code changes.
8. What We Should NOT Build (Pragmatic Scoping)
Given our current stage, here's what to defer:
| Feature | Status | Reason |
|---|---|---|
| Multi-warehouse per vendor | ❌ Defer | Most Nepali vendors have 1 shop. Add later via vendor_locations. |
| FEFO auto-routing | ❌ Defer | Build batch tracking first, add auto-pick logic later. |
| Barcode/POS scanning | ❌ Defer | Nice-to-have; vendors can manually update stock initially. |
| Shared catalog / deduplication | ❌ Defer | Per-vendor catalogs are fine for our heterogeneous vendor base. |
| Advanced costing (FIFO valuation) | ❌ Defer | Vendors set their own prices; we don't need cost accounting now. |
| Product Families & attribute schemas | ✅ Build | This is the backbone. Without it, every future feature is painful. |
| Variant system | ✅ Build | Core to any e-commerce. |
| Quantity tracking | ✅ Build | The most common tracking mode. |
| Batch tracking | ✅ Build | Critical for the perishable vendors that are our core market. |
| Serial tracking | ⏸️ Stub | Create the table, but don't build the full UI yet. |
| Inventory movements ledger | ✅ Build | Essential for audit trail and debugging. |
| Service slots | ⏸️ Stub | Create the table, build the UI when we add service vendors. |
9. Implementation Roadmap (Laravel)
Phase 1: Foundation (Build Now)
- Create migrations for:
product_families,categories,products,product_variants - Create migrations for:
inventory_items,inventory_batches,inventory_movements - Build Eloquent models with proper relationships
- Seed
product_familieswith initial families (Fresh Produce, Packaged Grocery, Electronics, Fashion, Services, Digital) - Seed
categoriestree linked to families
Phase 2: Vendor Dashboard (Build Next)
- Product CRUD in Inertia (schema-driven forms)
- Inventory management UI (stock adjustments, batch entry)
- Low-stock alerts via Laravel Notifications
Phase 3: Public API (Build After)
- Product listing API for Next.js storefront
- Search & filter API (with category-aware attribute filtering)
- Stock availability checks during checkout
10. Key Disagreements Between Sources & Our Resolution
| Topic | ChatGPT Says | Gemini Says | My Assessment |
|---|---|---|---|
| Schema strategy | Hybrid JSON + relational | Hybrid JSONB + GIN indexes (PostgreSQL) | Agree on hybrid. We use MySQL with Laravel, so JSON column type works. We can add Meilisearch/Typesense for search later. |
| Dropship archetype | Include as core archetype | Not emphasized | Defer. Not relevant for hyperlocal Nepali vendors shipping from their own shops. |
| Frontend framework | Schema-driven forms | Suggests Astro.js | Use Inertia/React for vendor dashboard (schema-driven). Next.js for public. We already have this stack. |
| Microservices | Suggests separate Catalog + Inventory services | Agrees on modular separation | Monolith first. Laravel modules (Services/Repositories), not separate microservices. We're not at that scale yet. |
| Rental support | Mentions as special case | Detailed rental slot model | Defer. Very few rental vendors in initial launch. Can be added as a product family later. |