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 MfgLiteCrystal System DB for:

    • Authentication
    • Company Management
    • Subscription Plan Management
    • Pricing Policy Management
    • Client Subscription Management
    • Module Management
    • Client Module Linking
    • User Module Role Linking
    • Session Handling
    • Registration Workflow

  • Implement Email OTP based login similar to CHSLite.

  • Implement HTTP-only Cookie based session handling.

  • Implement role-based rendering.

  • Implement company/module-based rendering.

  • Implement license-based subscription management.

  • Implement flexible pricing policy management.

Phase 1 : Create System DB Tables

📦 Subscription Plan Table

Purpose

  • Defines subscription plan ranges based on license count.
  • Defines annual per-license pricing.
  • Automatically determines the applicable subscription plan based on the number of purchased licenses.
  • Does NOT define module combinations.
  • Module pricing is maintained separately through Pricing Policy.

Rules

  • Plans define minimum and maximum license limits.
  • One License = One Active ERP User.
  • Customers pay only for the licenses they purchase.
  • Modules are selected independently.
  • Subscription plans only define license pricing.
  • Module pricing is calculated separately.
  • Pricing calculation consists of:
    • License Cost
    • Module Cost
    • License Discount
    • Module Discount

Rules

  • Plans define minimum and maximum license limits.
  • One License = One Active ERP User.
  • Customers pay only for the licenses they purchase.
  • Modules are selected independently.
  • Pricing consists of:
    • License Cost
    • Module Cost
    • License Discount
    • Module Discount

Notes

  • One License represents one active ERP user account.
  • The system automatically selects the subscription plan based on the total purchased licenses.
  • Customers are charged only for the licenses purchased and not for the maximum licenses available under the selected plan.
  • Future pricing changes should be managed through the Pricing Policy table without changing application code.

Example

Plan License Range Annual License Rate
NOVA 1 - 5 ₹1,000
APEX 6 - 15 ₹900
IMPERIA 16 - 50 ₹800
TITAN 51 - 200 ₹700

SQL 

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

    code NVARCHAR(3),
    name NVARCHAR(25),

    minlicenses INT,
    maxlicenses INT,

    monthlylicenserate DECIMAL(10,2),

    isactive NVARCHAR(1)
);

Dummy Insert 

INSERT INTO subscriptionplan
(code, name, minlicenses, maxlicenses, monthlylicenserate, isactive)
VALUES
('001', 'Nova', 1, 5, 1000, 'Y'),
('002', 'Apex', 6, 15, 900, 'Y'),
('003', 'Imperia', 16, 50, 800, 'Y'),
('004', 'Titan', 51, 200, 700, 'Y');

📌 License Based Subscription Model

Purpose
  • Defines how subscriptions are calculated.
  • Explains the relationship between Licenses, Users and Subscription Plans.
Rules
  • One License = One Active ERP User.
  • Every active user consumes one license.
  • Users cannot exceed the purchased license count.
  • The subscription plan is automatically determined based on the total purchased licenses.
  • Customers pay only for the licenses they purchase.
Example

Purchased Licenses : 3
Applicable Plan : NOVA
License Cost : 3 × ₹1,000 = ₹3,000

🏢 Client Table

Purpose

  • Stores all companies using MFG Lite.

Rules

  • One company = One Client.
  • ClientId is used for tenant separation.
  • Subscription details are maintained separately in the Client Subscription table.
  • One client can have only one active subscription at a time.
  • Email & Mobile Number for each user should be unique

SQL 

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

    clientid NVARCHAR(6),
    name NVARCHAR(50),

    isactive NVARCHAR(1)
);

Dummy Insert 

INSERT INTO client
(clientid, name, isactive)
VALUES
('000001', 'ABC Manufacturing', 'Y'),
('000002', 'XYZ Industries', 'Y');

🏢 Client Subscription Table

Purpose

  • Stores client subscription details.
  • Maintains current and historical subscription information.
  • Used for subscription renewal, upgrade and downgrade.

Rules

  • One client can have only one active subscription.
  • Subscription Plan is determined automatically based on purchased licenses.
  • Subscription history should never be deleted.
  • New subscriptions, renewals and upgrades should create new records.
  • If expiry date is 27-Jul-26 and user is logging in on 28-Jul-26 then we will show access denied, renew plan message. If they are renewing it on 31-Jul-26 we will take date as expiry date + 1 month - 1 day.
    Example : Expiry date : 27-Feb-26
    Renewed Expiry Date : 26-Mar-26. It will not be based on number of days

SQL 

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

    clientid NVARCHAR(6),

    plancode NVARCHAR(3),

    purchasedlicenses INT,

    subscriptionstartdate DATETIME,
    subscriptionexpirydate DATETIME
);

Dummy Insert 

INSERT INTO clientsubscription
(clientid, plancode, purchasedlicenses, subscriptionstartdate, subscriptionexpirydate)
VALUES
('000001', '002', 10, '01-Jan-26', '31-Dec-26'),
('000002', '001', 3, '01-Apr-26', '31-Mar-27');

📦 Module Table

Purpose

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

Rules

  • Modules are global.
  • Companies can enable or disable modules independently.
  • Modules are independent of subscription plans.
  • Module pricing is maintained separately from license pricing.
  • For all modules pricing will be same.

SQL 

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

    code NVARCHAR(3),
    name NVARCHAR(25),

    isactive NVARCHAR(1)
);

Dummy Insert 

INSERT INTO module
(code, name, isactive)
VALUES
('001', 'Sales', 'Y'),
('002', 'Supply Chain', 'Y'),
('003', 'Manufacturing', 'Y'),
('004', 'Finance', 'Y'),
('005', 'Human Resource', 'Y'),
('006', 'CRM', 'Y'),
('007', 'System Tools', 'Y');

📦 Client Module Mapping Table

Purpose

  • Defines which modules are enabled for a client.

Rules

  • Modules are linked client-wise.
  • Subscription Plans do NOT define modules.
  • A client can be linked to multiple modules.
  • The same module can be linked only once for a client.

SQL 

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

    clientid NVARCHAR(6),

    modulecode NVARCHAR(3)
);

Dummy Insert 

INSERT INTO linkclientmodule
(clientid, modulecode)
VALUES
('000001','001'),
('000001','002'),
('000001','004'),
('000001','007'),
('000002','001'),
('000002','005'),
('000002','007');

👤 User Table

Purpose

  • Stores login users.

Rules

  • One user belongs to one client.
  • One active ERP user consumes one license.
  • Login is handled using Email OTP.
  • Single session enforcement is enabled.
  • Active users cannot exceed the purchased license count.
  • User permissions are maintained separately in the Link User Module Role table.
  • Client Id 000000 will be system users.

SQL 

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

    clientid NVARCHAR(6),

    name NVARCHAR(200),

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

    isactive NVARCHAR(1),

    currentsessionid NVARCHAR(200),
    sessionexpiresat DATETIME,

    otpcode NVARCHAR(10),
    otpexpiresat DATETIME
);

Dummy Insert 

INSERT INTO users
(clientid, name, mobile, email, isactive)
VALUES
('000001','System Admin','9999999999','system@mfglite.com','Y'),
('000001','Finance Admin','8888888888','finance@abc.com','Y'),
('000002','HR Manager','7777777777','hr@xyz.com','Y');

🔐 Role Master Table

Purpose

  • Stores all roles available in MFG Lite.
  • Allows new roles to be added without application changes.

Rules

  • Roles are global.
  • Roles are shared across all clients.
  • Role permissions are maintained centrally in the application.
  • Roles can be enabled or disabled.

SQL 

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

    code NVARCHAR(3),

    name NVARCHAR(25),

    isactive NVARCHAR(1)
);

Dummy Insert 

INSERT INTO rolemaster
(code, name, isactive)
VALUES
('001','System','Y'),
('002','Administrator','Y'),
('003','Supervisor','Y'),
('004','Manager','Y'),
('005','Staff','Y'),
('006','Read Only','Y');

🔐 Link User Module Role Table

Purpose

  • Defines module-wise user permissions.
  • Determines which modules a user can access.
  • Defines the user’s role within each module.

Rules

  • One user can be linked to multiple modules.
  • A user can be linked only to modules enabled for the client.
  • A user can have only one role per module.
  • The combination of ClientId + UserId + ModuleCode must be unique.
  • A user may have different roles in different modules.

Example

User Module Role
User 01 Sales ReadOnly
User 01 Finance Staff
User 02 Finance Manager

Invalid Example

User Module Role
User 01 Sales ReadOnly
User 01 Sales Staff ❌

SQL 

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

    clientid NVARCHAR(6),

    userid INT,

    modulecode NVARCHAR(3),

    rolecode NVARCHAR(3)
);

Dummy Insert 

INSERT INTO linkusermodulerole
(clientid, userid, modulecode, rolecode)
VALUES
('000001',2,'001','002'),
('000001',2,'004','005'),
('000001',2,'007','002'),
('000001',3,'004','002'),
('000002',4,'005','004'),
('000002',4,'007','006');

Phase 2A : Registration Request System

📋 Registration Request Table

Purpose

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

Rules

  • Registration does not create Client directly.
  • System team reviews the registration request.
  • Client and Subscription are created only after approval.
  • Selected modules are maintained separately.

SQL 

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

    companyname NVARCHAR(200),

    contactperson NVARCHAR(200),

    mobile NVARCHAR(20),

    email NVARCHAR(200),

    requestedlicenses INT,

    calculatedprice DECIMAL(18,2),

    remarks NVARCHAR(MAX),

    status NVARCHAR(50),

    createdat DATETIME
);

Status Values

  • PENDING
  • UNDER_DISCUSSION
  • APPROVED
  • REJECTED
  • CONVERTED

📋 Registration Request Module Table

Purpose

  • Stores modules selected during registration.

Rules

  • One registration request can contain multiple modules.
  • A module can be selected only once for a registration request.

SQL 

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

    registrationrequestid INT,

    modulecode NVARCHAR(20)
);

Dummy Insert 

INSERT INTO registrationrequestmodule
(registrationrequestid, modulecode)
VALUES
(1,'001'),
(1,'004'),
(1,'003'),

(2,'001'),
(2,'005');

Phase 2B : Subscription Pricing Calculation

Purpose

  • Calculate estimated annual subscription pricing.
  • Display estimated pricing during registration.
  • Automatically determine the applicable subscription plan based on the number of requested licenses.
  • Calculate pricing using the Pricing Policy.

Rules

  • Subscription Plan is automatically selected based on the number of requested licenses.
  • Customers pay only for the licenses they purchase.
  • Modules are selected independently.
  • Pricing is calculated using Subscription Plan, Module and Pricing Policy.
  • Discounts are applied through the Pricing Policy.
  • Pricing calculation should be fully data-driven without hardcoded business logic.

Pricing Formula 

Estimated Annual Subscription Amount
=
License Cost + Module Cost - License Discount - Module Discount

Pricing Policy

The pricing engine shall calculate subscription pricing using the following master tables:

  • Subscription Plan
  • Module
  • Pricing Policy

This allows pricing and discount offers to be modified without changing application code.

Example 

Requested Licenses

12

↓

Applicable Plan

APEX

↓

Annual License Rate

₹900

↓

License Cost

12 × ₹900

=

₹10,800

↓

Selected Modules

Sales
Finance

↓

Module Cost

Sales        ₹1,000
Finance      ₹1,200

=

₹2,200

↓

License Discount

As per Pricing Policy

↓

Module Discount

As per Pricing Policy

↓

Estimated Annual Subscription Amount

₹XXXX

Notes

  • The Subscription Plan is automatically selected based on the requested license count.
  • Customers are charged only for the requested licenses.
  • Discount percentages should not be hardcoded in the application.
  • All discounts should be maintained through the Pricing Policy table.
  • Any future pricing changes should require only database updates without application code changes.

Phase 3 : Prisma Schema Creation

Tasks

  • Create Prisma schema for:

    • subscriptionplan
    • pricingpolicy
    • client
    • clientsubscription
    • module
    • linkclientmodule
    • rolemaster
    • user
    • linkusermodulerole
    • registrationrequest
    • registrationrequestmodule

  • Generate Prisma Client.

Commands 

npx prisma init

npx prisma db pull

npx prisma generate

Phase 4 : Email OTP Login Implementation

Objective

  • Implement passwordless Email OTP login similar to CHSLite.
  • Implement HTTP-only Cookie based authentication.
  • Implement Single Session Login.
  • Implement module-based authorization.
  • Implement role-based authorization.

Login Flow 

Enter Client Id
Enter Email / Mobile
        ↓
Validate Client & User
        ↓
Send Email OTP
        ↓
Verify OTP
        ↓
Generate Session Id
        ↓
Generate JWT
        ↓
Store HTTP-only Cookie
        ↓
Load User Modules & Roles
        ↓
Redirect Dashboard

OTP Authentication Flow 

sequenceDiagram
    autonumber

    actor User
    participant API
    participant DB
    participant Email

    User->>API: Enter Client Id + Email / Mobile

    API->>DB: Validate Client

    API->>DB: Validate User

    API->>API: Generate 6 Digit OTP

    API->>DB: Update OTP + 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

    API->>API: Generate JWT

    API->>DB: Load User Modules & Roles

    API->>User: Set HTTP-only Cookie

    API-->>User: Login Successful

OTP Send API

Endpoint 

/api/auth/send-otp

Responsibilities

  • Accept:

    • ClientId
    • Email / Mobile

  • Validate:

    • Client exists
    • User exists
    • User is active

  • Generate 6-digit OTP.

  • Set OTP expiry time.

  • Save OTP in the User table.

  • Send OTP via Email.

Example 

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

OTP Verify API

Endpoint 

/api/auth/verify-otp

Responsibilities

  • Validate OTP.
  • Validate OTP expiry.
  • Generate unique Session Id.
  • Update Current Session.
  • Generate JWT.
  • Store HTTP-only Cookie.
  • Load User Modules.
  • Load User Roles.
  • Return authenticated user information.

Post Login Authorization

After successful login:

  • Load all modules assigned to the client.
  • Load all modules assigned to the user.
  • Load the user’s role for each assigned module.
  • Store the authorization details in the JWT/session.
  • The frontend should use a centralized permissions.ts helper for permission checks.

Phase 5 : JWT Session System

Session Structure 

type SessionData = {
    userId: number;

    clientId: string;

    name: string;

    mobile?: string;
    email?: string;

    sessionId: string;
};

Session Expiry

Rules

  • Every session has an expiry time.
  • Session expiry is stored in the User table.
  • JWT expiry and database session expiry must match.
  • Expired sessions are automatically invalidated.

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: '/',
});

Notes

  • JWT should contain only the minimum information required to identify the logged-in user.
  • User modules and roles should be loaded after successful authentication.
  • Permission validation should always use the centralized permissions.ts helper.
  • Role changes should take effect without requiring JWT regeneration.

Phase 6 : Session Validation

Objective

  • Validate the active session on every authenticated request.
  • Validate JWT.
  • Validate session expiry.
  • Validate user status.
  • Expired or invalid sessions are automatically logged out.

Validation Rules

  • Decode JWT.
  • Read Session Id.
  • Match Session Id against the User table.
  • Validate session expiry.
  • Validate user is active.

Invalid Session

If validation fails:

  • Clear HTTP-only Cookie.
  • Logout user.
  • Redirect to Login page.

Single Session Enforcement

Rules

  • One user can have only one active session.
  • A new login automatically invalidates the previous session.

Flow 

User Login
      ↓
Generate New Session Id
      ↓
Update User.CurrentSessionId
      ↓
Generate JWT
      ↓
Previous Session Becomes Invalid

Notes

  • Session validation should be performed through middleware before accessing protected pages.
  • Users with inactive accounts cannot access the application even if the JWT is valid.
  • Session expiry in the JWT and the database should always remain synchronized.

Session Middleware

Responsibilities

  • Validate JWT.
  • Validate Current Session Id.
  • Validate Session Expiry.
  • Validate User Status.
  • Redirect unauthenticated users to Login.
  • Allow public routes without authentication.

Suggested File 

middleware.ts

Phase 7 : HTTP-only Cookie & Client Handling

Objective

  • Store authenticated user information securely using HTTP-only Cookie.
  • Automatically identify the active client for every request.
  • Ensure complete tenant isolation.

Stored Values 

{
  "userId": 1,
  "clientId": "000001",
  "name": "System Admin",
  "sessionId": "SESSION_123456"
}

Rules

  • Frontend should NEVER manually send clientId.
  • Backend reads clientId from the authenticated session.
  • Backend automatically injects clientId into every database operation.
  • User modules and roles should NOT be stored in the cookie.
  • User permissions should be loaded after successful authentication.

ERP Table Rule

Every ERP table except system tables must contain:

clientid NVARCHAR(6)

Query Rule

All ERP queries except system table queries must filter by clientid.

Example 

SELECT *
FROM sales_order
WHERE clientid = '000001'

Notes

  • clientId is the primary tenant identifier.
  • Backend APIs should always validate clientId from the authenticated session.
  • Direct access to another client’s data must never be possible.

Phase 8 : Authentication & Authorization Provider

Objective

  • Create a centralized authentication and authorization provider.

Responsibilities

  • Read authenticated session.
  • Validate current session.
  • Store logged-in user globally.
  • Load client modules.
  • Load user module roles.
  • Redirect unauthorized users.
  • Handle module rendering.
  • Handle permission-based rendering.

Suggested Files 

/components/auth-provider.tsx

/lib/permissions.ts

Authorization Flow 

User Login
      ↓
Read Session
      ↓
Validate Session
      ↓
Load Client Modules
      ↓
Load User Module Roles
      ↓
Store User Context
      ↓
Render Authorized Pages

Notes

  • Authentication determines who the user is.
  • Authorization determines what the user can access.
  • All permission checks should be performed through permissions.ts.
  • UI components should never contain hardcoded role names.
  • Adding or modifying roles should require changes only in permissions.ts.

Phase 9 : Route Protection

Objective

  • Protect authenticated routes.
  • Prevent unauthorized access.
  • Redirect users based on authentication status.

Rules

Public Routes

Accessible without login:

/login
/register

Protected Routes

Authentication required:

/dashboard
/*

If user is not logged in 

Redirect → /login

If session is invalid or expired 

Clear HTTP-only Cookie
        ↓
Redirect → /login

If already logged in 

Redirect → /dashboard

Middleware Responsibilities

  • Validate JWT.
  • Validate Session Id.
  • Validate Session Expiry.
  • Validate User Status.
  • Redirect unauthenticated users.
  • Allow access to public routes.

Suggested File 

middleware.ts

Phase 10 : Permission Based Rendering

Objective

  • Render UI based on user permissions.
  • Centralize all permission checks.
  • Avoid hardcoded role names throughout the application.

Rules

  • All permission checks should be performed through permissions.ts.
  • UI components should never directly compare role names.
  • User permissions should be determined using the assigned module and role.
  • Changes to role permissions should require updates only in permissions.ts.

Suggested File 

/lib/permissions.ts

Example 

permissions.canView("SALES")

permissions.canCreate("SALES")

permissions.canModify("SALES")

permissions.canDelete("SALES")

permissions.canAuthorize("FINANCE")

permissions.canPrint("MFG")

Example Usage 

{permissions.canCreate("SALES") && (
    <Button>Create Sales Order</Button>
)}

{permissions.canDelete("FINANCE") && (
    <Button color="error">Delete Voucher</Button>
)}

{permissions.canAuthorize("QC") && (
    <Button>Approve QC</Button>
)}

Notes

  • Permission validation should always be module specific.
  • A user’s permissions are determined from the Link User Module Role table.
  • The same user may have different permissions in different modules.

Phase 11 : Module Based Rendering

Objective

  • Render application modules based on the client’s enabled modules.

Rules

  • Load enabled modules from the Link Client Module table.
  • Sidebar should display only enabled modules.
  • Reports and menus should be visible only for enabled modules.

Example 

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

Special Rules

  • System Tools should always be visible.
  • Settings should always be visible.
  • These modules should not depend on Link Client Module.
  • If a module is disabled, all menus, transactions and reports belonging to that module should also be hidden.

Example 

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

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

Report Rendering Example 

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

Notes

  • Module visibility should be determined only from the Link Client Module table.
  • Hidden modules should not appear in the Sidebar, Dashboard, Search or Reports.
  • Backend APIs must also validate module access. UI rendering alone must not be treated as security.

Phase 12 : Dashboard Rendering

Objective

  • Render the dashboard based on the client’s enabled modules and the user’s assigned permissions.

Rules

  • Display only modules enabled for the client.
  • Display widgets based on user permissions.
  • Hide unauthorized menus, reports and dashboard cards.
  • Backend APIs must also validate permissions.

Example 

if (permissions.canView("FINANCE")) {
    showFinanceDashboard();
}

if (!permissions.canDelete("SALES")) {
    hideDeleteButtons();
}

if (permissions.canAuthorize("QC")) {
    showPendingApprovals();
}

Notes

  • Dashboard rendering should always use permissions.ts.
  • Never check role names directly in dashboard components.
  • Module visibility and permissions should be evaluated independently.

Phase 13 : Suggested Folder Structure 

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

/components
    /auth

/lib
    /auth
    /session
    /jwt
    /permissions
    /pricing

/prisma

/types

/middleware.ts

Notes

  • permissions.ts contains all permission-related logic.
  • session.ts manages JWT and HTTP-only Cookie operations.
  • pricing.ts contains subscription pricing calculations.
  • middleware.ts validates authentication before protected routes are accessed.

🚀 Final Implementation Order 

System Database
        ↓
Prisma Schema
        ↓
Registration Request
        ↓
Subscription Pricing Engine
        ↓
Email OTP Authentication
        ↓
JWT & HTTP-only Cookie
        ↓
Session Validation
        ↓
Authentication Provider
        ↓
Permission Framework
        ↓
Route Protection
        ↓
Module Rendering
        ↓
Dashboard Rendering
        ↓
ERP Modules
MFG Lite - [Plan & Implementation]System Changes - [MFG - Integrate Bank Reco]