SKU Management System

A full-stack application for managing hierarchical SKU (Stock Keeping Unit) data in a retail environment.

Overview

This system provides a complete solution for managing product hierarchies in retail environments. Users can organize products into locations, departments, categories, and subcategories, with full CRUD capabilities through both a REST API and web interface.

Technology Stack

• Backend: Python 3.11, FastAPI, SQLAlchemy ORM
• Frontend: React 18, Axios
• Database: PostgreSQL 15
• Containerization: Docker, Docker Compose

Architecture

The application follows a three-tier architecture:

• Database Layer: PostgreSQL with hierarchical relational schema
• API Layer: FastAPI REST endpoints with validation and error handling
• Presentation Layer: React single-page application with component-based UI

Data Model: Location -> Department -> Category -> SubCategory (four-level hierarchy)

Quick Start

Choose one of the following approaches:

Option 1: Docker Compose (Recommended)

Docker Compose orchestrates all three services (PostgreSQL, backend, frontend) in isolated containers.

Prerequisites:

• Docker Desktop (includes Docker and Docker Compose)

Automated Deployment:

For complete automated build and deployment:

python build.py

Or with shell script on Linux/macOS:

bash build.sh

This executes all build steps, starts services, and verifies health.

Manual Steps:

1. Clone repository and navigate to project root

   cd SKU_Management
   

2. Start all services

   docker-compose up --build
   

3. Access services

   • Frontend: http://localhost:3000
   • Backend API: http://localhost:8000
   • Database: localhost:5432 (postgres/postgres)

4. Stop services

   docker-compose down
   

The system automatically initializes the database schema and loads seed data on first run. All services are orchestrated through Docker Compose with proper dependency management.

Option 2: Local Development

Run each service independently on your local machine.

Prerequisites:

• Python 3.11 or higher
• Node.js 18 or higher
• PostgreSQL 15 or higher
• Git

Steps:

1. PostgreSQL Setup

   • Create database

     psql -U postgres -c "CREATE DATABASE sku_management;"
     

   • Load schema

     psql -U postgres -d sku_management < database/schema.sql
     

   • Load seed data

     psql -U postgres -d sku_management < database/seed.sql
     

2. Backend Setup

   cd backend
   

   Create and activate virtual environment:

   python -m venv venv
   

   On Windows:

   venv\Scripts\activate
   

   On macOS/Linux:

   source venv/bin/activate
   

   Install dependencies:

   pip install -r requirements.txt
   

   Set environment variables (create .env file):

   DATABASE_URL=postgresql://postgres:postgres@localhost:5432/sku_management
   

   Run server:

   python main.py
   

   Backend runs on http://localhost:8000

3. Frontend Setup

   cd frontend
   

   Install dependencies:

   npm install
   

   Start development server:

   npm start
   

   Frontend runs on http://localhost:3000

   For production build:

   npm run build
   

API Endpoints

All endpoints require Content-Type: application/json header for request bodies.

Locations:

• GET /api/locations - Retrieve all locations
• POST /api/locations - Create location
• GET /api/locations/{id} - Retrieve location
• PUT /api/locations/{id} - Update location
• DELETE /api/locations/{id} - Delete location

Departments:

• GET /api/departments - Retrieve all departments
• POST /api/departments - Create department
• GET /api/departments/{id} - Retrieve department
• PUT /api/departments/{id} - Update department
• DELETE /api/departments/{id} - Delete department

Categories:

• GET /api/categories - Retrieve all categories
• POST /api/categories - Create category
• GET /api/categories/{id} - Retrieve category
• PUT /api/categories/{id} - Update category
• DELETE /api/categories/{id} - Delete category

SubCategories:

• GET /api/subcategories - Retrieve all subcategories
• POST /api/subcategories - Create subcategory
• GET /api/subcategories/{id} - Retrieve subcategory
• PUT /api/subcategories/{id} - Update subcategory
• DELETE /api/subcategories/{id} - Delete subcategory

Example request:

POST /api/locations
Content-Type: application/json

{
  "name": "Back Storage",
  "description": "Warehouse storage area"
}

Example response:

{
  "id": 3,
  "name": "Back Storage",
  "description": "Warehouse storage area",
  "created_at": "2026-07-31T18:00:00.000000",
  "updated_at": "2026-07-31T18:00:00.000000"
}

Project Structure

SKU_Management/
  backend/
    main.py                 - Application entry point
    database.py             - Database connection and session management
    models.py               - SQLAlchemy ORM models
    schemas.py              - Pydantic request/response schemas
    requirements.txt        - Python dependencies
    Dockerfile              - Container image for backend
    routes/
      locations.py          - Location CRUD endpoints
      departments.py        - Department CRUD endpoints
      categories.py         - Category CRUD endpoints
      subcategories.py      - SubCategory CRUD endpoints
  
  frontend/
    public/
      index.html            - HTML entry point
    src/
      App.js                - Main React component
      App.css               - Styling
      api.js                - API client configuration
      index.js              - React DOM rendering
      components/
        Locations.js        - Locations management component
        Departments.js      - Departments management component
        Categories.js       - Categories management component
        SubCategories.js    - SubCategories management component
    package.json            - Node.js dependencies
    Dockerfile              - Container image for frontend
  
  database/
    schema.sql              - Database schema definition
    seed.sql                - Initial data seed
  
  docker-compose.yml        - Multi-container orchestration
  .gitignore                - Git ignore patterns
  README.md                 - This file

Development

The codebase follows these principles:

• Separation of concerns: Each component has single responsibility
• Clean code: No unnecessary complexity or comments
• Extensibility: New entities can be added by creating route and component files
• Type safety: Pydantic schemas enforce request/response contracts
• Error handling: Proper HTTP status codes and error messages

Adding a New Entity

To add a new entity type:

1. Add table to database/schema.sql
2. Create SQLAlchemy model in backend/models.py
3. Create Pydantic schemas in backend/schemas.py
4. Create route handler in backend/routes/entity.py
5. Include router in backend/main.py
6. Create React component in frontend/src/components/Entity.js
7. Add API client in frontend/src/api.js
8. Add component to frontend/src/App.js

Database Schema

The database uses a hierarchical four-level structure with proper constraints:

• Locations: Store areas (e.g., Perimeter, Center)
• Departments: Departmental groupings within locations
• Categories: Product categories within departments
• SubCategories: Specific product subcategories

Foreign keys enforce referential integrity. Cascade delete rules ensure data consistency. Indexes optimize query performance on common access patterns.

Troubleshooting

Docker Setup:

• If ports are already in use, modify docker-compose.yml port mappings
• Ensure Docker Desktop is running before executing docker-compose commands
• On Windows, WSL2 backend is recommended for Docker Desktop
• Check container logs with: docker logs container_name

Local Setup:

• Verify PostgreSQL is running and accessible
• Check Python and Node.js versions match requirements
• Ensure all dependencies installed without errors
• Verify environment variables are set correctly
• Check that frontend can reach backend at configured API URL

Performance Considerations

• Database indexes on foreign key columns improve join performance
• React components use efficient re-rendering through proper state management
• API implements pagination parameters (skip, limit) for large datasets
• Database connection pooling through SQLAlchemy
• Frontend production build includes minification and compression

Testing and Quality Assurance

The project implements comprehensive testing at multiple levels:

Unit Tests:

• Backend: pytest with database isolation
• Frontend: Jest with React Testing Library
• Focus on individual components and functions

Integration Tests:

• API + Database integration verification
• CRUD operation end-to-end testing
• Data persistence validation

Acceptance Tests:

• End-to-end tests with Docker containers
• Complete workflow verification
• Service health checks

Running Tests Locally:

Backend tests:

cd backend
pip install -r requirements.txt
pytest -v

Frontend tests:

cd frontend
npm install
npm test -- --coverage --watchAll=false

CI/CD Pipeline:

• Automated testing on every push to master
• Automated Docker image building
• Integration test suite execution
• Code quality checks
• See TESTING.md for complete documentation

Automated Build and Deployment

For reproducible deployment without IDE customization:

python build.py

This script:

• Builds Docker images
• Manages container lifecycle
• Verifies service health
• Displays deployment status

See TESTING.md for detailed deployment instructions and options.

Authentication

Exercise 3 adds JWT-based authentication to secure the application:

Login Flow

1. Access the Application

   http://localhost:3000
   

2. Login Page

   • Username and password form appears on first access
   • Test user credentials:
     • Username: testuser
     • Password: password123
   • Click "Fill Test Credentials" button for convenience

3. Authentication

   • Backend verifies credentials against PostgreSQL users table
   • Successful login generates a JWT token (30-minute expiration)
   • Token stored in browser localStorage
   • Token automatically sent with every API request

4. Protected Resources

   • All API endpoints receive JWT token in request parameters
   • Token verified on each request
   • Invalid/expired tokens result in 401 Unauthorized

User Registration

• Click "Register" link on login page
• Create new account with username, email, and password
• Username and email must be unique
• Passwords securely hashed with bcrypt (rounds: 12)
• New users automatically marked as active

Logout

• Click "Logout" button in top-right corner
• Token removed from localStorage
• Redirected to login page

API Authentication Endpoints

All endpoints require JWT token for authorization:

Authentication:

• POST /api/auth/register - Create new user account

  {
    "username": "newuser",
    "email": "user@example.com",
    "password": "securepassword"
  }

• POST /api/auth/login - Authenticate user and get JWT token

  {
    "username": "testuser",
    "password": "password123"
  }

  Returns:

  {
    "access_token": "eyJhbGciOiJIUzI1NiIs...",
    "token_type": "bearer",
    "user_id": 1,
    "username": "testuser"
  }

• GET /api/auth/me?token={jwt_token} - Get current user info

• POST /api/auth/logout - Logout and clear token

Security Features

• Password Hashing: bcrypt with 12 rounds
• Token Generation: HMAC-SHA256 (HS256)
• Token Expiration: 30 minutes
• CORS: Enabled for all origins (customize for production)
• Database: PostgreSQL with users table and bcrypt-hashed passwords

Health Monitoring

The application includes a health check endpoint:

GET http://localhost:8000/health

Response:

{
  "status": "ok",
  "timestamp": "2026-07-31T19:05:15.580055",
  "database": "connected",
  "version": "1.0.0"
}

This endpoint verifies:

• API is running
• Database connectivity
• Application health

Logging

All operations are logged to both console and file:

• Console: Real-time logging during execution
• File: /app/logs/app.log (persisted in container)
• Includes: Authentication attempts, API requests, errors, and system events

Logs are helpful for debugging and monitoring:

docker-compose logs backend

Security Notes

This is a demonstration application. For production use:

• Implement proper authentication and authorization ✓ (Added in Exercise 3)
• Use environment variables for sensitive configuration
• Enable HTTPS/TLS for all endpoints
• Implement rate limiting on API endpoints
• Add input validation beyond schema validation
• Use database credentials from secrets management system
• Implement CORS restrictions for specific origins
• Rotate JWT secret key regularly
• Monitor authentication logs for suspicious activity