MFG Lite - Phase 0 Development

This document provides a comprehensive technical breakdown of all features, database tables, Prisma models, security enhancements, server actions, role-based access control (RBAC) rules, and UI components implemented in Phase 0 of the MFG Lite ERP platform.

Executive Summary & Database Scope

Phase 0 establishes the multi-tenant architecture, authentication, role-based authorization (RBAC), security defenses, and enterprise master data isolation across two isolated databases:

• MfgLiteCrystal (Auth Database — prisma/schema.auth.prisma): Manages tenant user credentials, hashed OTP generation, single-session enforcement, role assignments, and tenant subscriptions.
  • Database Tables & Models: client (Client), linkclientmodule (LinkClientModule), linkusermodulerole (LinkUserModuleRole), clientsubscription (ClientSubscription), users (User).
• MfgLite (Main ERP Database — prisma/schema.prisma): Manages enterprise organization profiles, manufacturing units/branches, and GST master data.
  • Database Tables & Models: Company (Company), CompanyBranch (CompanyBranch), GstState (GstState).

🚀 Additional Value-Add Features & Infrastructure Enhancements

In addition to the core Phase 0 requirement items, the following 5 critical infrastructure, performance, and security enhancements were implemented:

1. Brute-Force Lockout & IP Rate Limiting:
   • 5-failed-attempt lockout threshold on OTP verification (otpattempts >= 5).
   • In-memory sliding-window IP rate limiter restricting /api/auth/send-otp to a maximum of 3 requests per 60 seconds per IP address.
2. Single-Session Takeover Modal Dialog:
   • Real-time session UUID checking. When a second device logs in, the original device detects the takeover during polling and displays a popup dialog blocking UI access until the user re-authenticates.
3. Dual Prisma Client Build Automation (postinstall):
   • Automated dual-schema client generation script in package.json ensuring seamless compilation for both MfgLite and MfgLiteCrystal databases.
4. Full Dark Mode Styling Support:
   • Complete Tailwind dark: styling across the Login page, textboxes, modals, layout headers, and settings, with hydration safety inline scripts.
5. Zero-Downtime Session Cookie Expiration Handling:
   • Clean cookie deletion helper (clearSession()) using explicit Max-Age=0 and Path=/ HTTP headers upon token expiry or 30-minute idle inactivity, eliminating stuck loading screens.

Architecture Diagram

graph TD
    Client["Next.js App Router (Client / UI)"] --> Edge["Edge Middleware (src/middleware.ts)"]
    Edge --> AuthDB["MfgLiteCrystal (User, Client, RBAC)"]
    Edge --> MainDB["MfgLite (Company, CompanyBranch, GstState)"]

MfgLiteCrystal (Authentication & Security Database)

Feature 1: Multi-Tenant Architecture & Auth DB Setup

Feature / Change Summary

Configured the primary authentication database MfgLiteCrystal (prisma/schema.auth.prisma) containing tenant registration, module assignment, role mapping, user accounts, and subscription tiers.

Database Tables & Purpose Summaries

1. client (Client): Stores master multi-tenant client profile records (Client ID, name, active status).
2. linkclientmodule (LinkClientModule): Maps which ERP feature modules (e.g. Sales, Finance, System Tools 007) are purchased and enabled for a specific tenant client.
3. linkusermodulerole (LinkUserModuleRole): Maps specific user IDs to module codes (modulecode) and role permission levels (rolecode e.g. 001-006).
4. clientsubscription (ClientSubscription): Tracks tenant subscription plans, purchased license seat counts, and plan expiry dates.
5. users (User): Manages user credentials, active session UUIDs, SHA-256 hashed OTPs, OTP expiration timestamps, and failed attempt counters.

Prisma Models (prisma/schema.auth.prisma)

model Client {
  id        Int     @id @default(autoincrement())
  clientid  String? @db.NVarChar(6)
  name      String? @db.NVarChar(50)
  isactive  String? @db.NVarChar(1)

  @@index([clientid])
  @@map("client")
}

model LinkClientModule {
  id          Int     @id @default(autoincrement())
  clientid    String? @db.NVarChar(6)
  modulecode  String? @db.NVarChar(3)

  @@index([clientid])
  @@map("linkclientmodule")
}

model LinkUserModuleRole {
  id          Int     @id @default(autoincrement())
  clientid    String? @db.NVarChar(6)
  userid      Int?
  modulecode  String? @db.NVarChar(3)
  rolecode    String? @db.NVarChar(3)

  @@index([clientid, userid])
  @@map("linkusermodulerole")
}

model ClientSubscription {
  id                     Int       @id @default(autoincrement())
  clientid               String?   @db.NVarChar(6)
  plancode               String?   @db.NVarChar(3)
  purchasedlicenses      Int?
  subscriptionstartdate  DateTime? @db.DateTime
  subscriptionexpirydate DateTime? @db.DateTime

  @@index([clientid])
  @@map("clientsubscription")
}

model User {
  id                Int       @id @default(autoincrement())
  clientid          String?   @db.NVarChar(6)
  name              String?   @db.NVarChar(200)
  mobile            String?   @db.NVarChar(20)
  email             String?   @db.NVarChar(200)
  isactive          String?   @db.NVarChar(1)
  currentsessionid  String?   @db.NVarChar(200)
  sessionexpiresat  DateTime? @db.DateTime
  otpcode           String?   @db.NVarChar(200)
  otpexpiresat      DateTime? @db.DateTime
  otpattempts       Int       @default(0)

  @@index([clientid])
  @@index([email])
  @@index([mobile])
  @@map("users")
}

Feature 2: SHA-256 Hashed OTP Authentication & Brute-Force Lockout

Feature / Change Summary

Replaced plain-text OTP generation with cryptographically secure crypto.randomInt 6-digit OTP generation, SHA-256 password hashing, atomic single-transaction verification, constant-time hash comparison (crypto.timingSafeEqual), and a 5-failed-attempt lockout threshold. Added sliding-window IP rate limiting (3 requests / 60 seconds) and AWS SES email dispatch.

Functional / Workflow Changes

• Users enter their Client ID and their registered Email address or 10-digit Mobile number to identify their account.
• The database searches for a matching user record by clientid and either email or mobile.
• Once verified, the 6-digit OTP is generated and dispatched exclusively to the user’s registered Email address via AWS SES.
• OTPs expire after 3 minutes.
• Entering 5 consecutive incorrect OTPs locks verification attempts until a new OTP is requested.
• IP rate limiter prevents bot spamming by blocking >3 OTP requests within a 60-second window.

Files / Components Affected or Added

• src/app/api/auth/send-otp/route.ts
• src/app/api/auth/verify-otp/route.ts
• src/app/login/page.tsx
• lib/email.ts

API / Server-Side Changes

• POST /api/auth/send-otp: Validates Zod input (clientId 6-digit regex, identifier email), applies sliding IP rate limit, generates SHA-256 hashed OTP, and sends email via AWS SES client singleton.
• POST /api/auth/verify-otp: Validates Zod input, checks otpattempts >= 5, performs constant-time hash comparison (crypto.timingSafeEqual), increments otpattempts on failure, and clears OTP / sets session atomically inside a Prisma $transaction.

Important Validations / Business Rules

• OTP code is stored only as a SHA-256 hex digest (NVARCHAR(200)).
• OTP input string comparison uses crypto.timingSafeEqual to prevent side-channel timing leaks.
• Rate limit: Max 3 OTP generation requests per minute per IP address.
• Lockout: otpattempts >= 5 blocks verification returning 429 Too Many Requests.

Feature 3: Single Session Enforcement & Session Takeover Detection

Feature / Change Summary

Implemented single active session enforcement per user. If a user logs in on a second device, the active session ID in the database is rotated using crypto.randomUUID(). The original device detects the takeover during polling and presents a “Session Taken Over” modal dialog.

Functional / Workflow Changes

• System issues 24-hour absolute session tokens with rolling 30-minute idle activity refresh (lastActivityAt).
• Second device login prompts user: “An active session was found on another device. Do you want to take over this session?”
• If confirmed, the original session is invalidated, and the original browser displays a modal blocking UI access until the user clicks “Sign In Again”.
• Expiry or revocation explicitly deletes browser cookies using maxAge: 0 and path: '/'.

Files / Components Affected or Added

• lib/constants.ts (Edge-safe SESSION_COOKIE_NAME)
• lib/session.ts (createSession, getSessionDetail, clearSession)
• components/auth-provider.tsx (60s session polling + 5s visibilitychange debounce + AbortController timeout)
• src/middleware.ts

API / Server-Side Changes

• GET /api/auth/session: Returns sessionTakenOver: true with status 401 when user.currentsessionid !== decoded.sessionId.
• lib/session.ts: clearSession() explicitly sets Set-Cookie: mfglite_session=; Max-Age=0; Path=/ to purge stale browser cookies.

Feature 4: Role-Based Access Control (RBAC) & Tenant Scope Gating

Feature / Change Summary

Integrated role-based permission evaluation using module role codes stored in linkusermodulerole mapped against client modules in linkclientmodule.

Roles Introduced or Modified

Features / Pages / Actions Shown or Restricted Based on Roles

• System Tools (/systemtools/company):
  • Visible only to users assigned module 007 (or System Admin 001).
  • Main Level: Displays Index list, Details view, and Modify view.
  • Company Creation: Hidden from main level per enterprise workflow requirements.
• Client Switcher (/api/auth/switch-client):
  • Restrictive API guard: Returns 403 Forbidden unless dbUser.clientid === '000000'. Target client must exist and have isactive === 'Y'.

MfgLite (Main ERP Database)

Feature 5: Multi-Tenant Tenant Isolation & Company Branch Management

Feature / Change Summary

Hardened multi-tenant data isolation across Company, CompanyBranch, and GstState repositories and services. Enforced tenant-scoped validation, transactional unitCode sequence generation, and input sanitization (numeric-only pincodes).

Functional / Workflow Changes

• All Server Actions automatically extract session.clientId from server-side JWT claims (never trusting client-submitted clientid arguments).
• Branch unitCode sequence (e.g. '01', '02') is automatically computed inside a single Prisma $transaction ordering by unitCode: 'desc'.
• Pincode inputs enforce a 6-digit numeric regex (/^\d{6}$/) with auto-sanitization (e.target.value.replace(/\D/g, '')).

Files / Components Affected or Added

• prisma/schema.prisma
• repositories/company/company.repository.ts
• repositories/company-branch/company-branch.repository.ts
• services/company/company.service.ts
• services/company-branch/company-branch.service.ts
• src/app/actions/company.ts
• src/app/actions/company-branch.ts
• src/app/systemtools/company/[id]/page.tsx
• lib/dto.ts (Prisma Decimal / Date safe plain object serialization)

API / Server-Side Changes

• CompanyRepository & CompanyBranchRepository: update and delete execute findFirst({ where: { id, clientid } }) validation prior to executing primary key operations (where: { id }).
• Server Actions map Prisma P2002 unique constraint violations to user-friendly 409 Conflict error messages (e.g. “A company with this panno, companyemail already exists”).
• Pagination parameters cap limit to a maximum of 50 items (Math.min(Math.max(limit, 1), 50)).

Database Tables & Purpose Summaries (MfgLite)

1. Company (Company): Stores core tenant company profile information (PAN, email, signatory, shift hours).
2. CompanyBranch (CompanyBranch): Stores manufacturing plant/unit branch details (GSTIN, unit code, state code, registered office flag, notes).
3. GstState (GstState): Master table for GST state codes and state names used during branch creation.

Prisma Models (prisma/schema.prisma)

model Company {
  id            Int             @id @default(autoincrement())
  clientid      String          @db.NVarChar(6)
  companyname   String          @db.NVarChar(50)
  panno         String          @db.NChar(10)
  companyemail  String          @db.NVarChar(50)
  SignatoryType String?         @db.NVarChar(50)
  Signatoryname String?         @db.NVarChar(100)
  AuditTrail    String?         @db.NVarChar(1)
  shifthrs      Decimal         @db.Decimal(4, 2)

  branches      CompanyBranch[]

  @@unique([clientid, panno])
  @@unique([clientid, companyemail])
  @@index([clientid])
  @@map("Company")
}

model CompanyBranch {
  id                 Int       @id @default(autoincrement())
  clientid           String    @db.NVarChar(6)
  companyId          Int
  gstin              String    @db.NVarChar(20)
  homeStateCode      String    @db.NChar(2)
  unitCode           String    @db.NChar(2)
  unitName           String    @db.NVarChar(20)
  isActive           String    @db.NChar(1)
  addline1           String    @db.NVarChar(100)
  pincode            String    @db.NChar(6)
  isRegdOffice       String    @db.NVarChar(1)
  notes              String?   @db.NVarChar(250)
  company            Company   @relation(fields: [companyId], references: [id], onDelete: Cascade)

  @@unique([clientid, unitCode])
  @@index([clientid])
  @@index([companyId])
  @@index([clientid, isActive])
  @@map("CompanyBranch")
}

model GstState {
  id        Int    @id @default(autoincrement())
  stateCode String @db.NChar(2)
  stateName String @db.NVarChar(50)
  isActive  String @db.NChar(1)

  @@index([stateCode])
  @@map("GstState")
}

🎨 UI & Design Enhancements

Feature 6: Dark Mode UI & Theme Hydration Safety

Feature / Change Summary

Integrated dark mode across main layouts, system tools pages, and authentication pages (/login). Corrected SSR hydration script evaluation to prevent dark screen loading hangs.

Functional / Workflow Changes

• Theme toggle in /settings updates root <html> class and persists choice in localStorage.setItem('theme', 'dark' | 'light').
• Root layout includes Edge-safe inline script initializing theme before render.
• Login page (src/app/login/page.tsx) features full dark mode styling (dark:bg-slate-950, dark:border-slate-800, dark:text-white).

Files / Components Affected or Added

• src/app/layout.tsx
• src/app/login/page.tsx
• src/app/settings/page.tsx
• src/index.css

📦 Build & Dependency Summary

Package & Generated Client Updates

• package.json: Updated postinstall script to generate both Prisma clients automatically on build:

  "scripts": {
    "postinstall": "prisma generate --schema=prisma/schema.prisma && prisma generate --schema=prisma/schema.auth.prisma"
  }
• .gitignore: Added src/generated/ to prevent committing platform-native binaries (query_engine-windows.dll.node).
• Prune Unused Packages: Removed unused framer-motion dependency (~45KB gzipped) and moved @types/jsonwebtoken to devDependencies.