- Overview
- Features
- Technology Stack
- System Architecture
- Prerequisites
- Installation
- Database Setup with Docker
- Environment Configuration
- Running the Application
- API Documentation
- User Guide
- Project Structure
- Contributing
- License
The Inventory Management System is a comprehensive, full-stack web application designed for businesses to efficiently manage their inventory, track stock movements, record sales and purchases, and generate detailed reports. Built with Next.js 16 and PostgreSQL, this system provides real-time inventory tracking with FIFO (First-In-First-Out) valuation methodology, multi-warehouse support, and complete audit trails.
This enterprise-grade solution features role-based access control, automated stock calculations, customer and supplier management, and extensive reporting capabilities including profit/loss analysis, sales trends, and purchase history.
- Product Management: Create, edit, and delete products with SKU generation, categorization, barcode/QR code support, and multi-warehouse stock tracking
- Purchase Management: Record supplier purchases with automatic stock batch creation and FIFO cost tracking
- Sales Management: Process customer sales with automatic FIFO cost calculation and stock deduction
- Multi-Warehouse Support: Track inventory across multiple warehouse locations with real-time stock levels
- Stock Movement Tracking: Complete audit trail of all inventory movements with timestamps and user attribution
- Customer & Supplier Management: Maintain detailed records of business partners with contact information and transaction history
- User Management: Role-based access control with admin privileges for system configuration
- Activity Logging: Comprehensive logging of all system activities for compliance and troubleshooting
- Password Management: Secure password reset functionality with email verification
- QR Code Generation: Generate QR codes for products to facilitate barcode scanning
- FIFO Inventory Valuation: Accurate cost of goods sold calculation using First-In-First-Out methodology
- Real-time Stock Updates: Immediate inventory updates across all warehouses upon transactions
- Data Validation: Comprehensive input validation to ensure data integrity
- Responsive Design: Mobile-friendly interface accessible on all devices
- API-First Architecture: RESTful API endpoints for integration with external systems
- Database Transactions: ACID-compliant database operations for data consistency
- Email Notifications: Automated email alerts for password resets and system notifications
- Low Stock Alerts: Automatic notifications when product quantities fall below minimum thresholds
- Batch Stock Tracking: Detailed tracking of stock batches with expiration dates and lot numbers
- Sales Reports: Detailed sales analysis by date range, customer, and product
- Purchase Reports: Purchase history with supplier and date filtering
- Profit & Loss Reports: Financial analysis with gross profit calculations
- Product Reports: Inventory valuation, stock levels, and movement history
- Supplier Reports: Supplier performance and purchase analytics
- Customer Reports: Customer purchase history and revenue analysis
- User Activity Reports: System usage and user action tracking
- Daily Profit/Loss Bar Chart: Visual representation of daily profitability trends
- Next.js 16.0.7: React framework with App Router and Turbopack for fast development
- React 19.2.0: Modern UI library with hooks and concurrent features
- TypeScript 5.x: Static type checking for enhanced code quality
- Tailwind CSS 3.4.1: Utility-first CSS framework for responsive design
- Recharts 2.15.0: Data visualization library for charts and graphs
- React Hook Form: Form state management and validation
- Lucide React: Icon library for consistent UI elements
- Next.js API Routes: Serverless API endpoints built into Next.js
- Prisma 6.19.0: Next-generation ORM for type-safe database access
- PostgreSQL 16: Advanced open-source relational database
- bcrypt 5.1.1: Password hashing for secure authentication
- Nodemailer 6.9.17: Email sending for password resets and notifications
- QRCode 1.5.4: QR code generation for product labeling
- Docker & Docker Compose: Containerization for PostgreSQL database
- ESLint: Code linting for JavaScript/TypeScript
- PostCSS: CSS transformation and optimization
- XLSX 0.18.5: Excel file generation for report exports
The application follows a modern three-tier architecture with client layer (Next.js frontend), application layer (API routes), and data layer (PostgreSQL database).
The system uses 10 interconnected tables:
- User: System users with authentication credentials and role management
- Warehouse: Physical locations for inventory storage
- Supplier: Vendors from whom products are purchased
- Customer: Buyers of products
- Product: Items in inventory with SKU, pricing, and stock thresholds
- Purchase: Purchase transactions from suppliers
- Sale: Sales transactions to customers
- StockMovement: Historical record of all stock changes
- StockBatch: FIFO cost tracking with batch-level inventory management
- ActivityLog: System-wide audit trail of user actions
Before installing the application, ensure you have the following installed:
- Node.js: Version 18.x or higher
- npm: Version 9.x or higher (comes with Node.js)
- Docker: Version 20.x or higher
- PostgreSQL: Version 14+ (if not using Docker)
git clone <repository-url>
cd stackflownpm installYou can set up PostgreSQL either using Docker (recommended) or as a native installation on your system.
1. Install Docker and Docker Compose
# Update package index
sudo apt update
# Install prerequisites
sudo apt install -y ca-certificates curl gnupg lsb-release
# Add Docker's official GPG key
sudo mkdir -p /etc/apt/keyrings
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo gpg --dearmor -o /etc/apt/keyrings/docker.gpg
# Set up the repository
echo \
"deb [arch=$(dpkg --print-architecture) signed-by=/etc/apt/keyrings/docker.gpg] https://download.docker.com/linux/ubuntu \
$(lsb_release -cs) stable" | sudo tee /etc/apt/sources.list.d/docker.list > /dev/null
# Install Docker Engine
sudo apt update
sudo apt install -y docker-ce docker-ce-cli containerd.io docker-compose-plugin
# Add your user to the docker group (to run without sudo)
sudo usermod -aG docker $USER
# Apply group changes (or log out and back in)
newgrp docker2. Start PostgreSQL Container
# Remove any existing container with the same name
sudo docker rm -f mypostgresql 2>/dev/null || true
# Start PostgreSQL container
sudo docker run --name mypostgresql \
-e POSTGRES_USER=inventory_user \
-e POSTGRES_PASSWORD=1234 \
-e POSTGRES_DB=inventory_db \
-p 5433:5432 \
-d postgres
# Verify container is running
sudo docker ps3. Create PostgREST Role (for REST API access)
sudo docker exec -i mypostgresql psql -U inventory_user -d inventory_db <<'EOF'
DO $$
BEGIN
IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'web_anon') THEN
CREATE ROLE web_anon NOLOGIN;
END IF;
END
$$;
GRANT USAGE ON SCHEMA public TO web_anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO web_anon;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO web_anon;
EOF4. Start PostgREST (Optional - for REST API)
# Start PostgREST using docker-compose
sudo docker compose up -d1. Install Docker Desktop
- Download Docker Desktop from: https://www.docker.com/products/docker-desktop/
- Run the installer and follow the setup wizard
- Enable WSL 2 backend when prompted
- Restart your computer if required
- Start Docker Desktop from the Start menu
2. Open PowerShell or Command Prompt and verify Docker
docker --version
docker compose version3. Start PostgreSQL Container
# Remove any existing container with the same name
docker rm -f mypostgresql 2>$null
# Start PostgreSQL container
docker run --name mypostgresql `
-e POSTGRES_USER=inventory_user `
-e POSTGRES_PASSWORD=1234 `
-e POSTGRES_DB=inventory_db `
-p 5433:5432 `
-d postgres
# Verify container is running
docker ps4. Create PostgREST Role (for REST API access)
docker exec -i mypostgresql psql -U inventory_user -d inventory_dbThen in the PostgreSQL prompt, run:
DO $$
BEGIN
IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'web_anon') THEN
CREATE ROLE web_anon NOLOGIN;
END IF;
END
$$;
GRANT USAGE ON SCHEMA public TO web_anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO web_anon;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO web_anon;
\q5. Start PostgREST (Optional - for REST API)
docker compose up -d# Install PostgreSQL
sudo apt update
sudo apt install -y postgresql postgresql-contrib
# Start PostgreSQL service
sudo systemctl start postgresql
sudo systemctl enable postgresql
# Create database and user
sudo -u postgres psql <<EOF
CREATE USER inventory_user WITH PASSWORD '1234';
CREATE DATABASE inventory_db OWNER inventory_user;
GRANT ALL PRIVILEGES ON DATABASE inventory_db TO inventory_user;
EOFUpdate your .env file to use:
DATABASE_URL="postgresql://inventory_user:1234@localhost:5432/inventory_db?schema=public"- Download PostgreSQL installer from: https://www.postgresql.org/download/windows/
- Run the installer and follow the setup wizard
- Set a password for the
postgresuser during installation - Use pgAdmin (installed with PostgreSQL) or psql to create the database:
CREATE USER inventory_user WITH PASSWORD '1234';
CREATE DATABASE inventory_db OWNER inventory_user;
GRANT ALL PRIVILEGES ON DATABASE inventory_db TO inventory_user;Update your .env file to use:
DATABASE_URL="postgresql://inventory_user:1234@localhost:5432/inventory_db?schema=public"After setting up PostgreSQL (Docker or native), run these commands:
1. Generate Prisma Client
npx prisma generate2. Run Database Migrations
npx prisma migrate deploy3. Seed the Database (Optional)
npx prisma db seedThis creates a default admin user:
- Email:
admin@example.com - Password:
admin123
Docker Commands:
# Start PostgreSQL
sudo docker start mypostgresql
# Stop PostgreSQL
sudo docker stop mypostgresql
# View logs
sudo docker logs mypostgresql
# Access PostgreSQL CLI
sudo docker exec -it mypostgresql psql -U inventory_user -d inventory_db
# Remove container (WARNING: deletes data)
sudo docker rm -f mypostgresqlNative PostgreSQL (Ubuntu):
# Start/Stop service
sudo systemctl start postgresql
sudo systemctl stop postgresql
# Access PostgreSQL CLI
sudo -u postgres psql -d inventory_dbNative PostgreSQL (Windows):
Use pgAdmin GUI or:
psql -U inventory_user -d inventory_dbPrisma Studio (Database GUI) - All Platforms:
npx prisma studioCreate a .env file in the project root directory.
# Create .env file
nano .env
# or
vim .env
# or use any text editor# Create .env file using Notepad
notepad .env
# or use VS Code, any text editorAdd the following to your .env file:
# Database Connection
# For Docker setup (port 5433):
DATABASE_URL="postgresql://inventory_user:1234@localhost:5433/inventory_db?schema=public"
# For native PostgreSQL (port 5432):
# DATABASE_URL="postgresql://inventory_user:1234@localhost:5432/inventory_db?schema=public"
# Application Settings
NODE_ENV="development"
NEXT_PUBLIC_APP_URL="http://localhost:3000"
# Email Configuration (for password reset)
EMAIL_HOST="smtp.gmail.com"
EMAIL_PORT="587"
EMAIL_USER="your-email@gmail.com"
EMAIL_PASSWORD="your-app-password"
EMAIL_FROM="Inventory System <your-email@gmail.com>"
# Session Secret
SESSION_SECRET="your-random-secret-key-change-this-in-production"Important Notes:
- For Docker setup: Use port
5433(as shown above) - For native PostgreSQL: Use port
5432 - Update email settings with your actual SMTP credentials
- Change
SESSION_SECRETto a secure random string in production
Ubuntu/Linux:
npm run devWindows (PowerShell/CMD):
npm run devThe application will be available at: http://localhost:3000
Ubuntu/Linux:
npm run build
npm startWindows (PowerShell/CMD):
npm run build
npm startIf you've set up PostgREST (optional), you can access your database via REST API at http://localhost:3001
Ubuntu/Linux:
# View all available tables/endpoints
curl http://localhost:3001/
# Query users table
curl http://localhost:3001/users
# Query products table
curl http://localhost:3001/products
# Query with filters
curl "http://localhost:3001/products?category=eq.Electronics"
# Insert a new record
curl -X POST http://localhost:3001/products \
-H "Content-Type: application/json" \
-d '{"product_name":"New Product","sku":"PROD-001","unit_price":99.99}'Windows (PowerShell):
# View all available tables/endpoints
Invoke-WebRequest -Uri http://localhost:3001/
# Query users table
Invoke-WebRequest -Uri http://localhost:3001/users
# Query products table
Invoke-WebRequest -Uri http://localhost:3001/products
# Query with filters
Invoke-WebRequest -Uri "http://localhost:3001/products?category=eq.Electronics"
# Insert a new record
$body = @{
product_name = "New Product"
sku = "PROD-001"
unit_price = 99.99
} | ConvertTo-Json
Invoke-WebRequest -Uri http://localhost:3001/products `
-Method POST `
-ContentType "application/json" `
-Body $bodyUsing a Web Browser:
- Simply navigate to:
http://localhost:3001/users - Or use tools like Postman, Insomnia, or Thunder Client (VS Code extension)
Ubuntu/Linux:
# View PostgREST logs
sudo docker logs inventory_postgrest
# Restart PostgREST
sudo docker restart inventory_postgrest
# Stop PostgREST
sudo docker stop inventory_postgrest
# Start PostgREST
sudo docker start inventory_postgrestWindows (PowerShell):
# View PostgREST logs
docker logs inventory_postgrest
# Restart PostgREST
docker restart inventory_postgrest
# Stop PostgREST
docker stop inventory_postgrest
# Start PostgREST
docker start inventory_postgrestThe application provides a comprehensive RESTful API. All endpoints return responses in the format:
{
"success": true|false,
"data": {...},
"error": "error message"
}Authenticate a user.
Request:
{
"email": "admin@example.com",
"password": "admin123"
}Response:
{
"success": true,
"data": {
"userId": 1,
"email": "admin@example.com",
"name": "Admin User",
"role": "ADMIN"
}
}Request a password reset email.
Request:
{
"email": "user@example.com"
}Reset password using token.
Request:
{
"token": "reset-token",
"password": "newPassword123"
}Retrieve all users.
Create a new user (Admin only).
Request:
{
"email": "newuser@example.com",
"name": "New User",
"password": "password123",
"role": "USER"
}Retrieve a specific user by ID.
Update a user by ID (Admin only).
Delete a user by ID.
Reset another user's password (Admin only).
Request:
{
"userId": 2,
"newPassword": "newPassword123"
}Change current user's password.
Request:
{
"currentPassword": "oldPassword",
"newPassword": "newPassword"
}Retrieve all products with optional filtering.
Query Parameters:
warehouseId: Filter by warehousesupplierId: Filter by suppliercategory: Filter by categorylowStock: Show only low stock products
Create a new product.
Request:
{
"name": "New Product",
"sku": "PROD-002",
"category": "Electronics",
"barcode": "0987654321",
"buyingPrice": 80.00,
"sellingPrice": 120.00,
"quantity": 30,
"minQuantity": 5,
"maxQuantity": 50,
"supplierId": 1,
"warehouseId": 1
}Retrieve a single product by ID.
Update a product.
Delete a product.
Retrieve current stock level for a product.
Get stock trend data for charting.
Query Parameters:
days(default: 30): Number of days
Retrieve all purchases with optional filtering.
Query Parameters:
supplierId: Filter by supplierstartDate: Filter from dateendDate: Filter to date
Record a new purchase.
Request:
{
"productId": 1,
"supplierId": 1,
"quantity": 20,
"unitPrice": 100.00,
"totalPrice": 2000.00,
"purchaseDate": "2024-01-15"
}Side Effects:
- Creates stock batch for FIFO tracking
- Increases product quantity
- Creates stock movement record
- Logs activity
Retrieve all sales with optional filtering.
Query Parameters:
customerId: Filter by customerstartDate: Filter from dateendDate: Filter to date
Record a new sale.
Request:
{
"productId": 1,
"customerId": 1,
"quantity": 5,
"unitPrice": 150.00,
"totalPrice": 750.00,
"saleDate": "2024-01-20"
}Side Effects:
- Calculates COGS using FIFO
- Decreases product quantity
- Updates stock batches
- Creates stock movement record
- Logs activity
Retrieve all warehouses.
Create a new warehouse.
Request:
{
"name": "Secondary Warehouse",
"location": "456 Second Ave",
"capacity": 5000
}Retrieve a specific warehouse by ID.
Update a warehouse.
Delete a warehouse.
Retrieve all suppliers.
Create a new supplier.
Request:
{
"name": "New Supplier",
"contact": "Jane Smith",
"email": "contact@supplier.com",
"phone": "098-765-4321",
"address": "321 Vendor St"
}Retrieve all customers.
Create a new customer.
Request:
{
"name": "New Customer",
"email": "customer@example.com",
"phone": "444-555-6666",
"address": "789 Buyer Blvd"
}Retrieve stock batches for FIFO tracking.
Query Parameters:
productId(required): Product ID
Retrieve dashboard statistics.
Response includes:
- Total products
- Total sales
- Total purchases
- Total profit
- Low stock products
- Recent sales
- Top products
Retrieve API documentation and system information.
Retrieve support information and contact details.
Generate sales report.
Query Parameters:
startDate: Start dateendDate: End datecustomerId: Customer filterproductId: Product filter
Generate purchase report.
Query Parameters:
startDate: Start dateendDate: End datesupplierId: Supplier filterproductId: Product filter
Generate profit and loss report.
Query Parameters:
days(default: 30): Number of days
Generate product inventory report.
Generate supplier performance report.
Generate customer analytics report.
Generate user activity report.
Retrieve activity logs with pagination.
Query Parameters:
page(default: 1): Page numberlimit(default: 50): Items per pageuserId: Filter by userentity: Filter by entity typeaction: Filter by action (CREATE, UPDATE, DELETE)
Access the application at http://localhost:3000 using default credentials:
- Email: admin@example.com
- Password: admin123
Change the admin password immediately after first login.
- Navigate to Products page
- Click Add Product button
- Fill in product details (name, SKU, prices, quantities)
- Select supplier and warehouse
- Click Save
Editing: Click Edit icon next to product, modify fields, click Update
Deleting: Click Delete icon, confirm deletion
Filtering: Use warehouse, supplier, category, or low stock filters
QR Codes: Click QR Code button to generate printable codes
- Navigate to Purchases page
- Click Add Purchase
- Select product and supplier
- Enter quantity and unit price
- Click Save
Automatically creates stock batch and increases inventory.
- Navigate to Sales page
- Click Add Sale
- Select product and customer
- Enter quantity and selling price
- Click Save
Automatically calculates COGS using FIFO and decreases inventory.
- Navigate to Warehouses page
- Click Add Warehouse
- Enter name, location, and capacity
- Click Save
- Navigate to Suppliers page
- Click Add Supplier
- Enter company details and contact information
- Click Save
- Navigate to Customers page
- Click Add Customer
- Enter customer details
- Click Save
- Go to Reports page
- Select report type (Sales, Purchases, Profit & Loss, etc.)
- Choose filters and date range
- Click Generate Report
- Export to Excel if needed
- Navigate to Users page
- Click Add User
- Enter name, email, password, and role (USER or ADMIN)
- Click Save
- Navigate to Logs page
- View comprehensive activity log
- Filter by user, entity type, or action
- Use pagination for large datasets
User-Initiated:
- Click "Forgot Password" on login page
- Enter email address
- Check email for reset link
- Click link and enter new password
stackflow/
app/ # Next.js App Router
api/ # API endpoints
auth/ # Authentication
products/ # Product CRUD
purchases/ # Purchase operations
sales/ # Sales operations
warehouses/ # Warehouse management
suppliers/ # Supplier management
customers/ # Customer management
reports/ # Report generation
logs/ # Activity logs
dashboard/ # Dashboard stats
components/ # React components
context/ # React Context
(pages)/ # Application pages
lib/ # Utilities
prisma.ts # Prisma client
emailService.ts # Email service
activityLogger.ts # Activity logging
prisma/ # Database
schema.prisma # Schema definition
migrations/ # Migration history
seed.ts # Database seeding
docker-compose.yml # PostgreSQL container
package.json # Dependencies
.env # Environment variables
Contributions are welcome. Please follow these guidelines:
- Fork the repository
- Create a feature branch
- Make your changes with proper type annotations
- Test thoroughly
- Submit a pull request
- Use TypeScript for all code
- Follow ESLint rules
- Add JSDoc comments for functions
- Use consistent API response format
This project is licensed under the MIT License.
For questions or issues, please open an issue on the GitHub repository.