Skip to content
BranchesTags

Repository files navigation

Retail — Inventory Optimisation

A decision support system (DSS) for retail inventory management: a normalized multi-store, multi-supplier inventory schema with live stock-health monitoring, reorder alerts, and a management UI for every entity in the model.

Dashboard

Project Overview

Retail inventory decisions — when to reorder, which items are at risk of a stockout, which are about to expire — depend on data that usually lives in several disconnected spreadsheets: products, store-level stock, supplier lead times, and reorder thresholds. This project brings that data into a single normalized schema and computes the decision-relevant signals (stock status, reorder alerts, days until expiry, available capacity) from it automatically, surfacing them on a live dashboard instead of a static report.

It is a full-stack CRUD + decision-support application: a Next.js frontend, an Express REST API, and a PostgreSQL database, with the derived/decision logic implemented once on the backend and re-used (not re-implemented) on the frontend for live previews.

System Architecture

flowchart LR
    subgraph Client["Browser"]
        UI["Next.js App Router UI"]
    end
    subgraph Server["Node.js"]
        API["Express REST API"]
        VAL["Joi validation + numeric ID guards"]
        DERIVE["Derived-field engine\n(stock status, reorder alert, expiry, capacity)"]
    end
    DB[("PostgreSQL\n7 normalized tables")]

    UI -- "fetch() JSON" --> API
    API --> VAL --> DB
    DB --> DERIVE --> API
    API -- "JSON" --> UI
Loading

The same derived-field functions (backend/utils/derivedFields.js) are imported directly by the frontend's inventory form for a live preview while typing, so the UI can never compute a status the API would disagree with.

Entity-relationship model

erDiagram
    PRODUCTS ||--o{ SUPPLIER_PRODUCTS : "supplied via"
    SUPPLIERS ||--o{ SUPPLIER_PRODUCTS : "supplies"
    PRODUCTS ||--o{ INVENTORY : "stocked as"
    STORES ||--o{ INVENTORY : "holds"
    PRODUCTS ||--o{ INVENTORY_METRICS : "measured for"
    STORES ||--o{ INVENTORY_METRICS : "measured at"
    PRODUCTS ||--o{ REPLENISHMENT_RULES : "governed by"
    STORES ||--o{ REPLENISHMENT_RULES : "governed by"

    PRODUCTS {
        bigint product_id PK
        text product_name
        timestamp created_at
    }
    STORES {
        bigint store_id PK
        text store_name
        timestamp created_at
    }
    SUPPLIERS {
        bigint supplier_id PK
        text supplier_name
        timestamp created_at
    }
    SUPPLIER_PRODUCTS {
        bigint supplier_product_id PK
        bigint supplier_id FK
        bigint product_id FK
        int lead_time_days
    }
    INVENTORY {
        bigint inventory_id PK
        bigint product_id FK
        bigint store_id FK
        int stock_level
        int warehouse_capacity
        date expiry_date
    }
    INVENTORY_METRICS {
        bigint metric_id PK
        bigint product_id FK
        bigint store_id FK
        int stockout_frequency
        int order_fulfillment_time_days
    }
    REPLENISHMENT_RULES {
        bigint rule_id PK
        bigint product_id FK
        bigint store_id FK
        int reorder_point
    }
Loading

Features

  • Live KPI dashboard — in-stock / low-stock / out-of-stock / reorder-triggered counts, master-data counts, and a "Needs Attention" list ranking the most urgent inventory records (stockouts, low stock, reorder triggers, and expiring/expired items) by an urgency score.
  • Normalized 7-table schema — products, stores, suppliers, supplier-products (many-to-many with lead time), inventory, inventory metrics, and replenishment rules, with foreign keys and check constraints enforced at the database level.
  • Derived decision fields, computed once — available capacity, days until expiry, stock status (IN_STOCK / LOW_STOCK / OUT_OF_STOCK), and reorder alert are computed from raw fields by a single pure-function module the backend and frontend both import.
  • Full CRUD management UI for every table, with dynamic dropdowns (store/product/supplier selects are populated from the database, not hardcoded) and consistent status badges across the dashboard and tables.
  • Server-side validation — Joi schema validation plus a shared numeric-ID parser (parseId / parseNonNegativeInt) so malformed input returns a clean 400 instead of crashing the request.
  • Upsert-style inventory intake — submitting an inventory record auto-creates the product/store/supplier if new, or updates the existing relationship if not, inside a single database transaction.

Technologies Used

Layer Technology
Frontend Next.js 14 (App Router), React 18, Tailwind CSS, react-hook-form
Backend Node.js, Express 4
Database PostgreSQL (via pg), normalized relational schema
Validation Joi
Dev tooling ESLint, PostCSS

Project Structure

DSS-Frontend/
├── app/                        # Next.js App Router pages (one per entity + dashboard)
│   ├── page.js                 # KPI dashboard
│   ├── inventory/
│   ├── products/
│   ├── stores/
│   ├── suppliers/
│   ├── supplier-products/
│   ├── inventory-metrics/
│   └── replenishment-rules/
├── components/                 # Shared UI: forms, tables, stat tiles, status badges
├── backend/
│   ├── server.js               # Express app entry point
│   ├── routes/                 # One router per resource
│   ├── middleware/validation.js
│   ├── utils/
│   │   ├── derivedFields.js    # Shared decision-logic (imported by the frontend too)
│   │   └── parseId.js          # Numeric ID / non-negative-int guards
│   └── database/
│       ├── schema.sql          # Table definitions, constraints, indexes, trigger
│       └── setup.js            # Runs schema.sql against the configured database
└── docs/screenshots/           # Images used in this README

Installation

Prerequisites

  • Node.js 18+
  • A running PostgreSQL instance

1. Clone and install dependencies

git clone <this-repo-url>
cd DSS-Frontend
npm install
cd backend
npm install
cd ..

2. Configure environment variables

cp .env.local.example .env.local           # frontend: NEXT_PUBLIC_API_URL
cp backend/.env.example backend/.env       # backend: PORT + PostgreSQL credentials

Edit backend/.env with your PostgreSQL host/port/database/user/password.

3. Create the database schema

Create the database named in backend/.env (DB_NAME), then run:

cd backend
npm run setup     # executes database/schema.sql against your database

4. Run the app

# terminal 1 - backend (http://localhost:3001)
cd backend
npm start          # or: npm run dev (auto-restart on change)

# terminal 2 - frontend (http://localhost:3000)
npm run dev

Open http://localhost:3000.

Methodologies

  • Schema-first design. The relational schema (backend/database/schema.sql) was designed before the UI, with foreign keys, UNIQUE and CHECK constraints (e.g. warehouse_capacity >= stock_level) enforced at the database level rather than only in application code.
  • Single source of truth for decision logic. Stock status and reorder alerts are business rules, not display formatting, so they are implemented once (backend/utils/derivedFields.js) and imported by both the API layer and the frontend's live form preview, rather than re-implemented per consumer.
  • Validate at the boundary. Every write path parses and range-checks numeric IDs and quantities server-side (Joi for the inventory intake route, a shared parseId helper elsewhere) instead of trusting client-supplied types.
  • Verify the UI by driving it, not just building it. Functional changes were checked by running the app against seeded data in a headless browser and inspecting the rendered output and console for errors, in addition to next build passing cleanly.

Benchmark Evaluation

No formal load-testing or production-scale benchmark has been run yet (see Limitations) — this project has not been exercised against a production-sized dataset or concurrent-user load. What has been measured:

Frontend build output (next build, production mode):

Route Page size First Load JS
/ (dashboard) 2.71 kB 91.6 kB
/inventory 3.25 kB 93.9 kB
/products, /stores, /suppliers 2.42 kB 93 kB
/inventory-metrics 2.67 kB 93.3 kB
/supplier-products 2.60 kB 93.2 kB
/replenishment-rules 2.61 kB 93.2 kB
Shared JS (all routes) — 81.9 kB

All routes are statically prerendered by Next.js; data is fetched client-side at runtime.

Experimental Results

The derived-field engine was validated against a set of hand-constructed scenarios (seeded via a mock API for repeatable inspection) covering the boundary conditions of the stock-status and reorder-alert rules:

Scenario Stock Reorder point Expiry Expected status Expected alert Result
Zero stock 0 10 +60 days OUT_OF_STOCK Triggered matched
Below reorder point 5 10 +10 days LOW_STOCK Triggered matched
Healthy stock 80 10 +200 days IN_STOCK Not Triggered matched
Healthy stock, past expiry 40 10 −2 days IN_STOCK Not Triggered matched

The last row is the interesting case: an item can be fully stocked and still need attention because it's expired. The dashboard's urgency ranking accounts for this — that item still surfaced in "Needs Attention" purely on the expiry signal, confirming the ranking doesn't rely on stock status alone.

Limitations

  • No authentication or authorization — every API route is open; there is no concept of a logged-in user or role.
  • No automated test suite — validation so far has been manual/exploratory (build checks + browser-driven inspection), not unit or integration tests.
  • No pagination, search, or sorting on management tables — every GET returns the full table, which will not scale to large datasets.
  • One supplier per product-store row in the inventory list — GET /api/inventory picks a single supplier per product via DISTINCT ON; a product legitimately supplied by multiple suppliers will only show one in that view.
  • Derived-field logic lives in two module graphs — the frontend imports the backend's calculation functions directly (a relative import across components/ → backend/utils/), which avoids logic drift but is an unconventional layering for a deployed (rather than monorepo-tooled) app.
  • Not deployed — the app currently only runs locally against a developer-configured PostgreSQL instance.

Future Improvements

  • Reorder recommendation engine — turn the currently-collected supplier_lead_time_days and stockout_frequency into an actual safety-stock / reorder-quantity recommendation, rather than only a binary alert.
  • Authentication & role-based access control.
  • Automated tests — unit tests for the derived-field module (pure functions, easy to cover), integration tests for the API routes.
  • Charts/analytics — stock-health trends over time, expiry-risk timeline.
  • CSV import/export for bulk inventory intake.
  • Pagination and search on management tables.
  • TypeScript migration (typescript/@types/* are already project dependencies).
  • Deployment (e.g. Vercel for the frontend, Railway/Render/Neon for PostgreSQL) with a real load/performance benchmark once there's a production-representative dataset.

License

Released under the MIT License.

Author

Yashu Bopanna P D GitHub: @Bopanna012

About

No description, website, or topics provided.

Resources

Readme
MIT license
Activity

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

Packages

Contributors

Languages