🧭 SC Navigator Demo Data Generator

A comprehensive web application for generating Supply Chain Navigator Excel templates with multi-user authentication, environment management, and dynamic data visualization. Built for supply chain professionals to create and manage demo data efficiently.

🌟 Key Features

👥 User Authentication & Multi-Tenancy

User Registration & Login: Secure JWT-based authentication
Environment Management: Multi-tenant architecture with isolated data environments
Role-Based Access Control: Admin and standard user roles
User Profile Management: Personal settings and environment switching
Session Management: Persistent sessions with automatic renewal

📊 Excel Template Generation

Multi-step Wizard: Guided template creation process
Region & City Selection: Choose specific locations for analysis with country filtering
Product Selection: Raw materials and finished goods with category filtering
Custom Routes: Define custom supply chain routes and configurations
Period Configuration: Multiple reporting timeframes
Professional Excel Export: Generate formatted Excel templates with ExcelJS

🗺️ Data Relationships

Interactive Visualization: Visual product relationship mapping (under development)
Category Filtering: Filter by product categories and types
Product Dependencies: Map raw materials to finished goods
Dynamic Layout: Relationship network visualization

🛠️ Data Management Console

Master Data CRUD: Manage regions, cities, products, and periods
Environment Isolation: Each environment has its own data + shared system data
Pagination & Filtering: Efficient data browsing (25 items/page)
Bulk Operations: Import multiple records via CSV
Data Cleanup: Detect and resolve duplicates/inconsistencies
Analytics Dashboard: Visual insights into data distribution

📈 Analytics & Insights

Data Distribution Charts: Visualize products by category and type
Environment Statistics: Track data across environments
Real-time Metrics: Active users and system health

🏗️ Architecture

Frontend (React + Vite + TypeScript)

Framework: React 18 with TypeScript
Build Tool: Vite for fast development and HMR
UI Library: Shadcn/ui components with Radix UI primitives
Styling: Tailwind CSS with custom theme
State Management: React Context (Auth, Data Visibility, WebSocket)
Routing: React Router v6 for SPA navigation
Forms: React Hook Form with Zod validation
Charts: Recharts for analytics visualization

Backend (Node.js + Express)

Runtime: Node.js with ES modules
Framework: Express.js RESTful API
Database: SQLite with better-sqlite3 (synchronous)
Authentication: JWT tokens + bcrypt password hashing
Excel Generation: ExcelJS for template creation
Session Storage: File-based admin sessions with expiration

🚀 Quick Start

Development Setup

Clone the repository

git clone <repository-url>
cd SCNavigatorDemoDataGenerator

Install frontend dependencies

npm install

Setup backend

cd backend
npm install
npm run init-db-safe

Start development servers

Backend (Terminal 1):

cd backend
npm run dev

Frontend (Terminal 2):

npm run dev

Access the application

Frontend: http://localhost:5173 (Vite default) or http://localhost:8080
Backend API: http://localhost:3001
Health Check: http://localhost:3001/api/health

Initial User Setup

Users must be created by administrators through the User Management interface
Use the admin authentication for initial access to create the first users
Default admin password can be set during database initialization

🌐 Production Deployment

Ubuntu/Nginx Deployment

The application is configured for Ubuntu with Nginx reverse proxy:

Build the application

npm run build

Deploy using Ubuntu script

chmod +x deploy-ubuntu.sh
sudo ./deploy-ubuntu.sh

Configure Nginx

The nginx-sc-nav-template-generator.conf file is provided
Update domain name and SSL certificates as needed
Copy to /etc/nginx/sites-available/
Create symlink in /etc/nginx/sites-enabled/
Enable required modules (proxy_pass, headers)

sudo ln -s /etc/nginx/sites-available/nginx-sc-nav-template-generator.conf /etc/nginx/sites-enabled/
sudo nginx -t
sudo systemctl restart nginx

Start backend in production

cd backend
npm start
# Or use PM2 for process management:
pm2 start backend/start-production.js --name sc-navigator-api

📁 Project Structure

SCNavigatorDemoDataGenerator/
├── src/                          # Frontend source code
│   ├── components/              # React components
│   │   ├── admin/              # Admin console data tables
│   │   │   ├── CitiesTable.tsx
│   │   │   ├── RegionsTable.tsx
│   │   │   ├── ProductsTable.tsx
│   │   │   ├── PeriodsTable.tsx
│   │   │   ├── EnvironmentManagement.tsx
│   │   │   └── UserManagement.tsx
│   │   ├── auth/               # Authentication components
│   │   │   └── LoginForm.tsx
│   │   ├── ui/                 # Shadcn UI components
│   │   ├── ReportGenerator.tsx # Main Excel generator
│   │   ├── DataRelationships.tsx # Product relationships
│   │   ├── AdminConsole.tsx    # Data management console
│   │   ├── AnalyticsDashboard.tsx # Analytics & charts
│   │   ├── DataCleanup.tsx     # Data quality tools
│   │   ├── UserProfile.tsx     # User profile management
│   │   └── ExcelPreview.tsx    # Excel preview component
│   ├── contexts/               # React contexts
│   │   ├── AuthContext.tsx     # Authentication state
│   │   ├── DataVisibilityContext.tsx # Data visibility
│   │   └── WebSocketContext.tsx # WebSocket connections
│   ├── lib/                    # Utilities and API client
│   │   ├── api.ts             # API client
│   │   ├── bulk-utils.ts      # Bulk operations
│   │   └── utils.ts           # Utility functions
│   ├── hooks/                  # Custom React hooks
│   ├── pages/                  # Page components
│   └── main.tsx               # Application entry point
├── backend/                    # Backend API server
│   ├── lib/                   # Core libraries
│   │   ├── database.js        # Main database queries
│   │   ├── user-database.js   # User & environment queries
│   │   ├── schema-checker.js  # Safe DB initialization
│   │   └── excel-generator.js # Excel template generation
│   ├── routes/                # API routes
│   │   ├── auth.js           # Authentication endpoints
│   │   ├── auth-simple.js    # Simple auth (legacy)
│   │   ├── users.js          # User management
│   │   └── environments.js   # Environment management
│   ├── middleware/           # Express middleware
│   │   └── auth.js          # JWT authentication middleware
│   ├── scripts/             # Database scripts
│   │   ├── init-db.js      # Initialize database
│   │   ├── init-db-safe.js # Safe init (recommended)
│   │   └── add-cities.js   # Add city data
│   ├── server.js           # Main Express server
│   ├── start-production.js # Production startup
│   └── database.sqlite     # SQLite database file
├── public/                  # Static assets
│   └── Template SC Navigator.xlsx # Excel template
├── dist/                   # Built frontend (generated)
├── deploy-ubuntu.sh       # Ubuntu deployment script
├── nginx-sc-nav-template-generator.conf # Nginx config
└── database.sqlite       # SQLite database (root - optional)

🔌 API Endpoints

Authentication

Login: POST /api/auth/login - User login (returns session token)
Current User: GET /api/auth/me - Get current user info (requires auth)
Logout: POST /api/auth/logout - User logout (requires auth)
Update Profile: PUT /api/auth/profile - Update user profile (requires auth)
Change Password: POST /api/auth/change-password - Change password (requires auth)
Cleanup Sessions: POST /api/auth/cleanup-sessions - Remove expired sessions

Admin Authentication (Legacy/Simple)

Admin Login: POST /api/admin/auth - Admin password authentication
Admin Session: GET /api/admin/me - Validate admin session
Admin Logout: POST /api/admin/logout - Clear admin session

User Management (Admin only)

List Users: GET /api/users - Get all users
Get User: GET /api/users/:id - Get user by ID
Create User: POST /api/users - Create new user
Update User: PUT /api/users/:id - Update user
Delete User: DELETE /api/users/:id - Delete user
Toggle User: PUT /api/users/:id/toggle - Activate/deactivate user

Environment Management (Authenticated)

List All: GET /api/environments - Get all environments (admin)
My Environments: GET /api/environments/my - Get user's environments
Get Environment: GET /api/environments/:id - Get environment details
Create: POST /api/environments - Create new environment (admin)
Update: PUT /api/environments/:id - Update environment (admin)
Delete: DELETE /api/environments/:id - Soft delete (admin)
Hard Delete: DELETE /api/environments/:id/permanent - Permanent delete (admin)
Assign User: POST /api/environments/:id/users/:userId - Assign user to environment
Remove User: DELETE /api/environments/:id/users/:userId - Remove user from environment

Core Data Entities (Environment-scoped)

Regions: GET /POST /PUT /DELETE /api/regions[/:id]
Cities: GET /POST /PUT /DELETE /api/cities[/:id]
- Get by region: GET /api/cities/region/:regionId
- Random cities: GET /api/cities/region/:regionId/random/:count?country=X
Products: GET /POST /PUT /DELETE /api/products[/:id]
- By category: GET /api/products/category/:category
- By product category: GET /api/products/product-category/:productCategory
- By type: GET /api/products/product-type/:productType
Periods: GET /POST /PUT /DELETE /api/periods[/:id]
Product Categories: GET /api/product-categories

Excel Template Generation

Generate Template: POST /api/generate-excel
- Body: { regionId, cityIds, productIds, rawMaterialIds, finishedGoodIds, periodIds, cityCount, cdcCount, ldcCount, fgCount, rmCount, prodCount, includeBaseCase, customRoutes }
- Returns: Excel file (.xlsx)

Data Cleanup (Admin)

Merge Duplicates: POST /api/admin/merge-duplicates
Dismiss Duplicates: POST /api/admin/dismiss-duplicates
Get Dismissed: GET /api/admin/dismissed-duplicates

System

Health Check: GET /api/health - Server status

🛠️ Technologies Used

Frontend Stack

React 18 + TypeScript
Vite - Lightning-fast build tool with HMR
Tailwind CSS - Utility-first CSS framework
Shadcn/ui - High-quality accessible components
Radix UI - Unstyled accessible primitives
Lucide React - Beautiful icon library
React Router v6 - Client-side routing
React Hook Form - Performant form validation
Zod - TypeScript-first schema validation
Recharts - Composable charting library
TanStack Query - Data fetching and caching

Backend Stack

Node.js (ES Modules)
Express.js - Web framework
SQLite + better-sqlite3 - Embedded database
ExcelJS - Excel file generation
bcrypt - Password hashing
jsonwebtoken - JWT authentication
CORS - Cross-origin resource sharing

Development Tools

ESLint - Code linting
TypeScript - Type safety
Nodemon - Auto-restart dev server
PostCSS - CSS processing
Autoprefixer - CSS vendor prefixing

🔒 Security Features

Session Authentication: Secure token-based authentication with 24-hour expiration
Password Hashing: bcrypt with salt rounds for password security
Admin Sessions: File-based sessions with 8-hour expiration
User Sessions: Database-stored sessions with automatic cleanup
Role-Based Access Control: Admin and user roles with granular permissions
Environment Isolation: Multi-tenant data separation for secure data segregation
Input Validation: Server-side validation for all inputs
SQL Injection Protection: Prepared statements with better-sqlite3
CORS Configuration: Configurable origin restrictions
Secure Middleware: Authentication middleware for protected routes

📊 Database Schema

The application uses SQLite with the following tables:

User System

users - User accounts (id, username, email, password_hash, role, is_active)
environments - Data environments (id, name, description, is_deleted)
user_environments - User-environment relationships with roles

Core Data (Environment-scoped)

regions - Geographic regions (id, name, code, environment_id, is_system_data)
cities - Cities (id, name, region_id, latitude, longitude, country, environment_id, is_system_data)
products - Product catalog (id, name, product_category, product_type, environment_id, is_system_data)
periods - Reporting periods (id, label, start_date, end_date, environment_id, is_system_data)
product_categories - Product categorization metadata

Relationships & Cleanup

product_relationships - Product dependencies and connections
dismissed_duplicates - Data cleanup decisions (entity_type, entity_ids)

Legacy/Admin

admin_auth - Admin password authentication (for simple auth mode)

🎯 Key Features in Detail

Multi-User Authentication

Secure user registration and login
JWT-based authentication with automatic token refresh
Role-based permissions (Admin, User)
User profile management
Active/inactive user toggling
Session persistence across browser restarts

Environment Management

Multi-tenant architecture for data isolation
Users can be assigned to multiple environments
Each environment has its own data + shared system data
Environment-scoped CRUD operations
Admin can create, manage, and delete environments
Seamless environment switching from user profile

Excel Template Generator

Multi-step wizard with validation
Region selection with country filtering
Dynamic city selection (manual or auto-count)
Raw materials and finished goods selection
Multiple period support
Custom route definition
Base case inclusion option
Professional Excel output with formatted sheets

Data Management Console

Full CRUD operations for all entities
Pagination (25 items/page) with search
Environment-scoped data visibility
Bulk CSV import (under development)
Real-time data analytics
Product relationship visualization
Data cleanup tools for duplicates

Analytics Dashboard

Product distribution by category and type
Environment statistics
Visual charts and graphs
Data quality insights
System health metrics

Data Cleanup

Automatic duplicate detection
Bulk selection and merging
Master record selection
Dismiss false positives
Persistent cleanup decisions
Cross-entity duplicate detection

🚀 Performance Optimizations

Frontend: Code splitting, lazy loading, Vite optimized bundles
Backend: Synchronous SQLite (better-sqlite3), efficient queries
Database: Proper indexing on foreign keys and search fields
Caching: JWT token caching, API response optimization
Build: Production mode with minification and tree-shaking

🧪 Testing

Backend API Testing

The backend includes comprehensive endpoints for all features. Test using:

Postman/Insomnia for API testing
Browser DevTools for debugging
Check backend console for detailed logs

Database Scripts

# Initialize database safely (recommended)
npm run init-db-safe

# Full initialization (drops existing data)
npm run init-db

# Add city data
node backend/scripts/add-cities.js

🐛 Troubleshooting

Common Issues

Port already in use

# Windows
netstat -ano | findstr :3001
taskkill /PID <PID> /F

# Linux/Mac
lsof -ti:3001 | xargs kill -9

Database locked errors
- Close any database browsers (DB Browser for SQLite)
- Restart the backend server
Session token expired
- User sessions expire after 24 hours
- Admin sessions expire after 8 hours
- Simply log in again to get a new session token
Environment data not showing
- Ensure user is assigned to the environment
- Check environment_id in user context
- System data (environment_id = 1) is shared across all environments
Excel generation fails
- Verify region, cities, and products are selected
- Check backend console for detailed error messages
- Ensure at least one product is selected

📝 Configuration

Backend Environment Variables

PORT=3001                    # Server port (default: 3001)
NODE_ENV=production          # Environment mode

Frontend Configuration

API endpoint is configured in src/lib/api.ts:

const API_URL = 'http://localhost:3001/api';

📚 Documentation

Backend README: See backend/README.md for detailed API documentation
API Endpoints: Comprehensive list above
Database Schema: Defined in initialization scripts
Component Library: Shadcn/ui documentation at ui.shadcn.com

🔄 Database Migrations

The schema is managed through initialization scripts:

backend/scripts/init-db-safe.js - Safe schema updates (recommended)
backend/scripts/create-user-system-schema.sql - User system SQL schema

🎨 UI Components

Built with Shadcn/ui - a collection of re-usable components:

Fully accessible (Radix UI primitives)
Customizable with Tailwind CSS
Consistent design system
Located in src/components/ui/

📦 Build & Deployment

Frontend Build

npm run build           # Production build
npm run build:dev       # Development build
npm run preview         # Preview production build

Backend Deployment

cd backend
npm start              # Start server
# Or use PM2:
pm2 start start-production.js --name sc-navigator
pm2 save
pm2 startup

📄 License

This project is provided "as is" without ongoing support or warranties. See LICENSE.md for full terms and conditions.

🤝 Support

For issues, questions, or feature requests, please contact the development team.

SC Navigator Demo Data Generator - Empowering supply chain professionals with multi-tenant data management and professional Excel template generation. 🧭✨

Name		Name	Last commit message	Last commit date
Latest commit History 8 Commits
backend		backend
public		public
src		src
LICENSE.md		LICENSE.md
README.md		README.md
bun.lockb		bun.lockb
components.json		components.json
database.sqlite		database.sqlite
eslint.config.js		eslint.config.js
index.html		index.html
package-lock.json		package-lock.json
package.json		package.json
postcss.config.js		postcss.config.js
tailwind.config.ts		tailwind.config.ts
tsconfig.app.json		tsconfig.app.json
tsconfig.json		tsconfig.json
tsconfig.node.json		tsconfig.node.json
vite.config.ts		vite.config.ts

Folders and files

Latest commit

History

Repository files navigation

🧭 SC Navigator Demo Data Generator

🌟 Key Features

👥 User Authentication & Multi-Tenancy

📊 Excel Template Generation

🗺️ Data Relationships

🛠️ Data Management Console

📈 Analytics & Insights

🏗️ Architecture

Frontend (React + Vite + TypeScript)

Backend (Node.js + Express)

🚀 Quick Start

Development Setup

🌐 Production Deployment

Ubuntu/Nginx Deployment

📁 Project Structure

🔌 API Endpoints

Authentication

Admin Authentication (Legacy/Simple)

User Management (Admin only)

Environment Management (Authenticated)

Core Data Entities (Environment-scoped)

Excel Template Generation

Data Cleanup (Admin)

System

🛠️ Technologies Used

Frontend Stack

Backend Stack

Development Tools

🔒 Security Features

📊 Database Schema

User System

Core Data (Environment-scoped)

Relationships & Cleanup

Legacy/Admin

🎯 Key Features in Detail

Multi-User Authentication

Environment Management

Excel Template Generator

Data Management Console

Analytics Dashboard

Data Cleanup

🚀 Performance Optimizations

🧪 Testing

Backend API Testing

Database Scripts

🐛 Troubleshooting

Common Issues

📝 Configuration

Backend Environment Variables

Frontend Configuration

📚 Documentation

🔄 Database Migrations

🎨 UI Components

📦 Build & Deployment

Frontend Build

Backend Deployment

📄 License

🤝 Support

About

Resources

License

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Uh oh!

Contributors

Uh oh!

Languages

Packages