Docs
Ongoing Tasks
MFG Lite - System DB and Login Implementation

MFG Lite - System DB and Login Implementation

System DB + Login System Implementation Plan

🧠 Objective

  • Setup System DB for:

    • Authentication
    • Company Management
    • Subscription Plan Management
    • Module Management
    • Client Module Linking
    • Session Handling
    • Registration Workflow

  • Implement OTP based login similar to CHSLite.

  • Implement HTTP Cookie based session handling.

  • Implement role-based rendering.

  • Implement company/module-based rendering.

Phase 1 : Create System DB Tables

📦 Subscription Plan Table

Purpose

  • Defines company subscription limits.
  • Defines yearly base subscription price.
  • Does NOT define module combinations.

Rules

  • Plans define maximum user count.
  • Modules are selected independently.
  • Pricing consists of:
    • Plan Price
    • Module Price
    • Module Discount
  • Example
    • BRONZE → 5 Users
    • SILVER → 15 Users
    • GOLD → 30 Users
    • PLATINUM → 100 Users
    • TITANIUM → 500 Users

SQL 

CREATE TABLE subscriptionplan (
    id INT IDENTITY(1,1) PRIMARY KEY,

    code NVARCHAR(50),
    name NVARCHAR(100),

    maxusers INT,
    yearlyprice DECIMAL(18,2),

    isactive NVARCHAR(10)
);

Dummy Insert 

INSERT INTO subscriptionplan
(code, name, maxusers, yearlyprice, isactive)
VALUES
('BRONZE', 'Bronze', 5, 1000, 'Y'),
('SILVER', 'Silver', 15, 2000, 'Y'),
('GOLD', 'Gold', 30, 3000, 'Y'),
('PLATINUM', 'Platinum', 100, 4000, 'Y'),
('TITANIUM', 'Titanium', 500, 5000, 'Y');

🏢 Client Table

Purpose

  • Stores all companies using MFG Lite.

Rules

  • One company = one client.
  • Clientid used for tenant separation.
  • Subscription plan linked to client.

SQL 

CREATE TABLE client (
    id INT IDENTITY(1,1) PRIMARY KEY,

    clientid NVARCHAR(20),
    name NVARCHAR(200),

    planid INT,

    isactive NVARCHAR(10)
);

Dummy Insert 

INSERT INTO client
(clientid, name, planid, isactive)
VALUES
('A0001', 'ABC Manufacturing', 3, 'Y'),
('A0002', 'XYZ Industries', 2, 'Y');

📦 Module Table

Purpose

  • Stores all ERP modules.
  • Stores yearly module pricing.

Rules

  • Modules are global.
  • Companies can enable/disable modules.
  • Modules are independent.
  • Modules are not controlled by subscription plans.

SQL 

CREATE TABLE module (
    id INT IDENTITY(1,1) PRIMARY KEY,

    code NVARCHAR(20),
    name NVARCHAR(100),
    yearlyprice DECIMAL(18,2),

    sortorder INT,

    isactive NVARCHAR(10)
);

Dummy Insert 

INSERT INTO module
(code, name, yearlyprice, sortorder, isactive)
VALUES
('SALES', 'Sales', 1000, 1, 'Y'),
('SCM', 'Supply Chain', 1500, 2, 'Y'),
('MFG', 'Manufacturing', 3000, 3, 'Y'),
('FINANCE', 'Finance', 1200, 4, 'Y'),
('HR', 'HR', 1000, 5, 'Y');

📦 Client Module Mapping Table

Purpose

  • Defines which modules are enabled for a company.

Rules

  • Modules are linked client-wise.
  • Plans do NOT define modules.

SQL 

CREATE TABLE linkclientmodule (
    id INT IDENTITY(1,1) PRIMARY KEY,

    clientid NVARCHAR(20),
    modulecode NVARCHAR(20),

    isactive NVARCHAR(10)
);

Dummy Insert 

INSERT INTO linkclientmodule
(clientid, modulecode, isactive)
VALUES
('A0001', 'SALES', 'Y'),
('A0001', 'SCM', 'Y'),
('A0001', 'FINANCE', 'Y'),

('A0002', 'SALES', 'Y'),
('A0002', 'HR', 'Y');

👤 User Table

Purpose

  • Stores login users.

Rules

  • One user belongs to one company.
  • One user has one role.
  • Login handled using OTP.
  • Single session enforcement enabled.
  • User count cannot exceed subscription plan limit.

SQL 

CREATE TABLE [user] (
    id INT IDENTITY(1,1) PRIMARY KEY,

    clientid NVARCHAR(20),

    name NVARCHAR(200),

    mobile NVARCHAR(20),
    email NVARCHAR(200),

    role NVARCHAR(50),

    isactive NVARCHAR(10),

    telegramid NVARCHAR(100),

    telegramlinked NVARCHAR(10),

    currentsessionid NVARCHAR(200),
    sessionexpiresat DATETIME,

    otpcode NVARCHAR(10),
    otpexpiresat DATETIME
);

Dummy Insert 

INSERT INTO [user]
(clientid, name, mobile, email, role, isactive, telegramlinked)
VALUES
('A0001', 'System Admin', '9999999999', 'system@mfglite.com', 'SYSTEM', 'Y', 'N'),

('A0001', 'Finance Admin', '8888888888', 'finance@abc.com', 'ADMIN', 'Y', 'N'),

('A0002', 'HR Manager', '7777777777', 'hr@xyz.com', 'MANAGER', 'Y', 'N');

Phase 2A : Registration Request System

📋 Registration Request Table

Purpose

  • Stores company registration requests.
  • Allows review before activation.
  • Supports pricing calculation.

Rules

  • Registration does not create client directly.
  • System team reviews request.
  • Client created only after approval.

SQL 

CREATE TABLE registrationrequest (
    id INT IDENTITY(1,1) PRIMARY KEY,

    companyname NVARCHAR(200),

    contactperson NVARCHAR(200),

    mobile NVARCHAR(20),

    email NVARCHAR(200),

    selectedplanid INT,

    estimatedusers INT,

    selectedmodules NVARCHAR(MAX),

    calculatedprice DECIMAL(18,2),

    remarks NVARCHAR(MAX),

    status NVARCHAR(50),

    createdat DATETIME
);

Status Values

  • PENDING
  • UNDER_DISCUSSION
  • APPROVED
  • REJECTED
  • CONVERTED

Phase 2B : Subscription Pricing Calculation

Purpose

  • Calculate estimated yearly pricing.
  • Display pricing during registration.

Rules

  • Total Price = Plan Price + Module Price - Discount

Module Discount Rules

Modules Discount
1 0%
2 5%
3 10%
4 15%
5+ 20%

Example 

Bronze Plan

Sales
Finance

Base Plan Price = X

Module Price = Y + Z

Discount = 5%

Total =
X + Y + Z - Discount

Phase 3 : Prisma Schema Creation

Tasks

  • Create Prisma schema for:

    • subscriptionplan
    • client
    • module
    • linkclientmodule
    • user

  • Generate Prisma client.

Commands 

npx prisma init

npx prisma db pull

npx prisma generate

Phase 4 : OTP Login Implementation

Objective

  • Implement passwordless OTP login similar to CHSLite.
  • Primary OTP delivery via Email.
  • Secondary OTP delivery via Telegram.

Login Flow 

Enter Client Id
Enter Mobile/Email
        ↓
Send OTP
        ↓
Verify OTP
        ↓
Generate Session Id
        ↓
Generate JWT
        ↓
Store HTTP Cookie
        ↓
Redirect Dashboard

OTP Authentication Flow 

sequenceDiagram
    autonumber

    actor User
    participant API
    participant DB
    participant Email

    User->>API: Send Mobile / Email

    API->>DB: Validate User

    API->>API: Generate 6 Digit OTP

    API->>DB: Update otp_code + expiry

    API->>Email: Send OTP

    Email-->>User: OTP

    User->>API: Verify OTP

    API->>DB: Validate OTP

    API->>API: Generate Session Id

    API->>DB: Update current_session_id

    API->>API: Generate JWT

    API->>User: Set HTTP Cookie

OTP Send API

Endpoint 

/api/auth/send-otp

Responsibilities

  • Accept:

    • clientid
    • mobile/email

  • Validate:

    • client exists
    • user exists
    • user active

  • Generate 6 digit OTP.

  • Set expiry time.

  • Save OTP in DB.

  • Send OTP via Email.

  • If Telegram linked, optionally send via Telegram.

Example 

const otp = Math.floor(100000 + Math.random() * 900000);

OTP Verify API

Endpoint 

/api/auth/verify-otp

Responsibilities

  • Validate OTP.
  • Validate expiry.
  • Generate unique session id.
  • Update current_session_id.
  • Generate JWT.
  • Store HTTP cookie.

Phase 5 : JWT Session System

Session Structure 

type SessionData = {
    userId: number;

    clientId: string;

    name: string;

    mobile?: string;
    email?: string;

    role: string;

    sessionId: string;
};

Session Expiry

Rules

  • Every session has an expiry time.
  • Session expiry stored in DB.
  • JWT expiry and DB expiry must match.

JWT Helper

Responsibilities

  • Create JWT
  • Verify JWT
  • Decode JWT

Suggested File 

/lib/session.ts

Cookie Name 

mfglite_session

Cookie Configuration 

cookieStore.set('mfglite_session', token, {
    httpOnly: true,
    secure: process.env.NODE_ENV === 'production',
    sameSite: 'lax',
    maxAge: 24 * 60 * 60,
    path: '/',
});

Phase 6 : Session Validation

Objective

  • Validate active session on every request.
  • Validate session expiry.
  • Expired sessions are automatically logged out.

Validation Rules

  • Decode JWT
  • Read sessionId
  • Match against DB current_session_id
  • Validate user active

Invalid Session

If invalid:

  • clear cookie
  • logout user
  • redirect login

Single Session Enforcement

Rules

  • One user = one active session.
  • New login invalidates previous login.

Flow 

Old Session
    ↓
New Login
    ↓
current_session_id Updated
    ↓
Old Session Invalid

Phase 7 : HTTP Cookie Client Handling

Objective

  • Store active client information in cookie/session.

Stored Values 

{
  "userId": 1,
  "clientId": "A0001",
  "name": "System Admin",
  "role": "SYSTEM",
  "sessionId": "SESSION_123456"
}

Rules

  • Frontend should NEVER manually send client_id
  • Backend reads clientId from cookie/session
  • Backend automatically injects client_id

ERP Table Rule

Every ERP table must contain:

client_id NVARCHAR(20)

Query Rule

All queries must filter by client_id.

Example 

SELECT *
FROM sales_order
WHERE client_id = 'A0001'

Phase 8 : Auth Provider

Objective

  • Create frontend auth context.

Responsibilities

  • Read session from API
  • Validate current session
  • Store user globally
  • Redirect unauthorized users
  • Handle role rendering
  • Handle module rendering

Suggested File 

/components/auth-provider.tsx

Phase 9 : Route Protection

Objective

  • Protect authenticated routes.

Rules

If user not logged in 

Redirect → /login

If already logged in 

Redirect → /dashboard

Phase 10 : Role Based Rendering

Objective

  • Show UI based on role.

Example 

const isSystem = user?.role === "SYSTEM";
const isAdmin = user?.role === "ADMIN";
const isManager = user?.role === "MANAGER";
const isStaff = user?.role === "STAFF";
const isReadOnly = user?.role === "READ_ONLY";

const canDelete = isSystem || isAdmin;

Phase 11 : Module Based Rendering

Objective

  • Show modules based on client_module mapping.

Rules

  • Load enabled modules from DB.
  • Sidebar should render only enabled modules.

Example 

const filteredModules = modules.filter((module) => {
    return clientModules.includes(module.code);
});

Special Rules

  • System Tools module should ALWAYS be visible.
  • Settings module should ALWAYS be visible.
  • These modules should NOT depend on linkclientmodule mapping. If a module is disabled, its reports should also be hidden.

Example 

const alwaysVisibleModules = ["SYS", "SETTINGS"];

const filteredModules = modules.filter((module) => {
    if (alwaysVisibleModules.includes(module.code)) {
        return true;
    }

    return clientModules.includes(module.code);
});

const reportMenus = [
    {
        module: "SALES",
        menus: [
            "Sales Order Analysis",
            "Sales Analysis"
        ]
    },
    {
        module: "SCM",
        menus: [
            "Indent Analysis",
            "GRN Analysis",
            "Purchase Analysis"
        ]
    },
    {
        module: "MFG",
        menus: [
            "BOM Reports",
            "MRP Report",
            "Consumption of Material"
        ]
    },
    {
        module: "FINANCE",
        menus: [
            "Cash Bank",
            "Financial Reports"
        ]
    }
];

const visibleReports = reportMenus.filter((report) => {
    return clientModules.includes(report.module);
});

Phase 12 : Dashboard Rendering

Objective

  • Render dashboard based on role + module access.

Example 

if (isAdmin) {
    showFinanceReports();
}

if (isManager) {
    hideDeleteButtons();
}

Phase 13 : Suggested Folder Structure 

/app
    /(auth)
    /(main)
    /api
    /actions

/components

/lib
    /auth
    /session
    /jwt
    /permissions

/prisma

/types

🚀 Final Implementation Order 

System DB
    ↓
Prisma
    ↓
Registration Request
    ↓
OTP Authentication
    ↓
Session Management
    ↓
Auth Provider
    ↓
Route Protection
    ↓
Module Rendering
    ↓
ERP Modules
MFG Lite - [Plan & Implementation]System Changes - [MFG - Integrate Bank Reco]