​Project Report​

​On​

​High-Performance Multi-Threaded Cloud-Based CRM​

​for ISP Enterprises​

​Submitted by​

​Jainish Patel​

​42402840601011​

​In partial fulfillment for the award of the degree of​

​BACHELOR OF TECHNOLOGY​

​in​

​COMPUTER ENGINEERING​
​A.D. PATEL INSTITUTE OF TECHNOLOGY​

​The Charutar Vidya Mandal (CVM) University,​

​Vallabh Vidyanagar - 388120​

​DECLARATION​
I​ hereby declare that the project report entitled​​“High-Performance Multi-Threaded​
​Cloud-Based CRM for ISP Enterprises”​​, submitted to​​the Department of Computer​
​Engineering,​
​ .D Patel Institute of Technology​​in partial fulfilment​​of the requirements for the award of the​
A
​degree of​​Diploma in Engineering​

​Jainish Patel​
​42402840601011​
​Date:​
​ACKNOWLEDGEMENT​
I​ would like to express my sincere gratitude to everyone who supported and guided me​
​throughout the successful completion of this summer internship project.​
f​ or the invaluable guidance, constant encouragement, and constructive feedback provided at​
​every stage of this project. Their insight into system design and software engineering practices​
​was instrumental in shaping the final outcome of this work.​
I​ extend my heartfelt thanks to​​Mr. Nishit Thaker​​,​​Head of IT & Operations at​​Charotar​
​Telelink Pvt. Ltd.​​, for granting me the opportunity​​to undertake this project within a live​
​Internet Service Provider environment. His domain expertise, practical exposure to ISP business​
​operations, and willingness to share real-world challenges faced by the organization significantly​
​enriched the scope and relevance of this project..​
​ inally, I would like to thank my family and peers for their unwavering support, patience, and​
F
​motivation throughout the duration of this project.​

​Jainish Patel​
​ABSTRACT​
I​ nternet Service Providers (ISPs) operating in semi-urban and urban Indian markets are​
​increasingly confronted with the operational complexity of managing subscriber lifecycles,​
​broadband plan provisioning, billing, complaint resolution, vendor coordination, and network​
​inventory through disconnected spreadsheets, manual registers, and fragmented point tools. This​
​lack of a unified operational platform results in delayed complaint resolution, inconsistent billing​
​records, poor visibility into inventory and vendor transactions, and an inability of management​
​to make data-driven decisions. This project presents the design, development, and evaluation of​
​a​​High-Performance Multi-Threaded Cloud-Based Customer​​Relationship Management​
​(CRM) System​​tailored specifically to the operational​​workflows of Internet Service Provider​
​enterprises, developed in collaboration with Charotar Telelink Pvt. Ltd.​
​ he proposed system is architected as a modern three-tier cloud-native web application. The​
T
​presentation layer is implemented using React 18 with TypeScript, Vite, and Tailwind CSS,​
​communicating with a Python-based FastAPI backend over a RESTful, OpenAPI-documented​
​interface secured using JSON Web Token (JWT) based authentication and role-based access​
​control (RBAC). The backend leverages SQLAlchemy as an Object Relational Mapper against​
​a PostgreSQL database hosted on the Neon serverless cloud platform, and exploits Python’s​
​AsyncIO event loop in conjunction with a ThreadPoolExecutor to offload CPU-bound and​
​I/O-bound background operations such as PDF invoice generation, QR code creation, bulk​
​report generation, and mock SMS/WhatsApp notification dispatch without blocking the primary​
​request-handling thread. This hybrid asynchronous-and-threaded design allows the system to​
​sustain a significantly higher volume of concurrent requests than a purely synchronous​
​implementation, which is empirically evaluated in this report through controlled load testing.​
​ he system implements twelve major functional modules: Customer Management, Broadband​
T
​Plan Management, Invoice Generation with UPI QR-based payment support, Complaint Ticket​
​Management, Vendor Management, Inventory Management, Dashboard Analytics, Device​
​Monitoring, Report Generation, User and Role Management, Role-Based Access Control, and​
​Payment Tracking, catering to six distinct user roles Super Admin, Administrator, Engineer,​
​Accounts, Customer, and Vendor each with a tailored portal and permission set. The entire​
​system is containerized using Docker and Docker Compose to ensure reproducible deployment​
​across development, staging, and production environments.​
​ his report documents the complete software development lifecycle of the project, encompassing​
T
​requirement elicitation, feasibility analysis, system architecture and database design,​
​implementation details with representative code, a structured testing plan comprising unit,​
​integration, system, performance, and security test cases, and a discussion of results supported​
​by performance benchmarking. The evaluation demonstrates that the proposed multi-threaded​
​architecture reduces average invoice-generation response latency and improves concurrent​
​request throughput compared to a baseline synchronous implementation, while the modular,​
​role-based design improves operational visibility and reduces manual effort across customer​
​support, billing, and field-engineering workflows. The report concludes with a discussion of the​
​achievements, limitations, and future scope of the system, including proposed enhancements​
​such as real GSM-based SMS gateway integration, machine-learning-based churn prediction,​
​and mobile application support.​
​ eywords:​​Customer Relationship Management, Internet​​Service Provider, FastAPI, React,​
K
​PostgreSQL, Multi-Threading, AsyncIO, Cloud Computing, Role-Based Access Control, JWT​
​Authentication.​
​LIST OF FIGURES​
​ igure No.​
F ​ itle​
T
​3.1​ ​Overall System Architecture​
​3.2​ ​Cloud Deployment Architecture​
​3.3​ ​Multi-Layer (Three-Tier) Architecture​
​3.4​ ​Component Diagram​
​3.5​ ​Deployment Diagram​
​3.6​ ​Use Case Diagram​
​3.7​ ​Activity Diagram — Complaint Ticket Lifecycle​
​3.8​ ​Sequence Diagram — Invoice Generation​
​3.9​ ​Authentication and Authorization Flow​
​3.10​ ​Database Interaction Flow​
​3.11​ ​ThreadPoolExecutor Workflow​
​3.12​ ​REST API Request/Response Flow​
​3.13​ ​Invoice Generation Workflow​
​3.14​ ​Ticket Escalation Workflow​
​3.15​ ​Vendor Procurement Workflow​
​3.16​ ​Network Topology Diagram​
​3.17​ ​React Frontend Component Architecture​
​3.18​ ​FastAPI Backend Layered Architecture​
​3.19​ ​Docker Container Architecture​
​4.1​ ​Entity-Relationship Diagram​
​6.1​ ​Testing Pyramid​
​7.1​ ​Response Time Comparison — Synchronous vs Multi-Threaded​
​7.2​ ​Throughput Comparison Under Concurrent Load​
​LIST OF TABLES​
​ able No.​
T ​ itle​
T
​2.1​ ​Functional Requirements​
​2.2​ ​Non-Functional Requirements​
​2.3​ ​Risk Analysis Matrix​
​2.4​ ​Software Requirement Specification Summary​
​4.1​ ​Users Table Schema​
​4.2​ ​Roles Table Schema​
​4.3​ ​Customers Table Schema​
​4.4​ ​Plans Table Schema​
​4.5​ ​Invoices Table Schema​
​4.6​ ​Payments Table Schema​
​4.7​ ​Tickets Table Schema​
​4.8​ ​Inventory Table Schema​
​4.9​ ​Vendors Table Schema​
​4.10​ ​Devices Table Schema​
​4.11​ ​Notifications Table Schema​
​4.12​ ​Audit Logs Table Schema​
​4.13​ ​Sessions Table Schema​
​5.1​ ​Key REST API Endpoints​
​6.1​ ​Unit and Integration Test Cases​
​7.1​ ​Performance Benchmark Results​
​8.1​ ​Software Requirements​
​8.2​ ​Hardware Requirements​
​8.3​ ​Role–Permission Matrix​
​TABLE OF CONTENTS​
​ ection​
S ​ age​
P
​Declaration​ ​3​
​Acknowledgement​ ​5​
​Abstract​ ​6​
​List of Figures​ ​8​
​List of Tables​ ​ 0​
1
​Abbreviations​ ​12​
​Chapter 1: Introduction​ ​13​
​Chapter 2: Requirement Analysis​ ​18​
​Chapter 3: System Analysis and Design​ ​26​
​Chapter 4: Database Design​ ​40​
​Chapter 5: Implementation​ ​48​
​Chapter 6: Testing​ ​54​
​Chapter 7: Results and Discussion​ ​58​
​Chapter 8: Conclusion and Future Scope​ ​62​
​References​ ​63​
​Appendix​ ​67​
​ABBREVIATIONS​
​ bbreviation​ ​Full Form​
A
​API​ ​Application Programming Interface​
​CRM​ ​Customer Relationship​
​Management CRUD​ ​Create, Read, Update,​
​Delete​
​CSS​ ​Cascading Style Sheets​
​FTTH​ ​Fibre To The Home​
​GUI​ ​Graphical User Interface​
​HTTP​ ​Hyper Text Transfer Protocol​
​HTTPS​ ​Hyper Text Transfer Protocol Secure​
​IEEE​ ​Institute of Electrical and Electronics​
​Engineers ISP​ ​Internet Service Provider​
​JSON​ ​JavaScript Object Notation​
​JWT​ ​JSON Web Token​
​MVP​ ​Minimum Viable Product​
​ORM​ ​Object Relational Mapping​
​PDF​ ​Portable Document Format​
​RBAC​ ​Role-Based Access Control​
​REST​ ​Representational State​
​Transfer​
​SDLC​ ​Software Development Life Cycle​
​SLA​ ​Service Level Agreement​
​SPA​ ​Single Page Application​
​SQL​ ​Structured Query Language​
​SRS​ ​Software Requirement​
​Specification UI/UX​ ​User Interface / User​
​Experience UPI​ ​Unified Payments Interface​
​WAL​ ​Write-Ahead Logging​
​CHAPTER 1: INTRODUCTION​
​1.1​​Organization Profile​
​ harotar Telelink Pvt. Ltd. is a regional Internet Service Provider headquartered in the Charotar​
C
​belt of Central Gujarat, offering Fibre-To-The-Home (FTTH) broadband connectivity,​
​leased-line internet, and value-added digital services to residential and small-business​
​subscribers across a network of semi-urban towns and adjoining villages. The organization​
​operates a Point-of-Presence (PoP) based fibre distribution network supported by a small team of​
​field engineers, customer support executives, and an accounts department, and depends on a​
​network of local hardware vendors for Optical Network Terminal (ONT) devices, splitters, patch​
​cords, and passive fibre infrastructure.​
​ s the subscriber base of the organization has grown, its reliance on manual registers,​
A
​spreadsheet-based billing, and ad-hoc WhatsApp-based complaint tracking has become a​
​significant operational bottleneck. The Head of IT & Operations, Mr. Amit Patel, who served​
​as the industry mentor for this project, identified the absence of a unified digital platform as the​
​single largest impediment to the organization’s ability to scale its subscriber base while​
​maintaining service quality. This project was undertaken as an industry-linked final year project​
​in direct response to this identified operational need, with requirements gathered through​
​structured discussions with the organization’s IT, accounts, and field-operations teams.​

​1.2​​The Indian ISP Industry​

​ he Indian Internet Service Provider industry has undergone a structural transformation over the​
T
​past decade, driven by the rollout of fibre broadband infrastructure, falling data tariffs, and an​
​accelerating demand for high-bandwidth connectivity arising from remote work, online​
​education, and over-the-top (OTT) streaming consumption. While national and multi-state​
​operators command a large share of the urban broadband market, a substantial proportion of​
​last-mile broadband connectivity, particularly in Tier-2 and Tier-3 towns, is delivered by​
​regional and local Internet Service Providers who operate on thin margins and lean​
​administrative teams.​
​ hese regional ISPs typically face a common set of operational constraints: limited capital for​
T
​enterprise software licensing, a small non-technical administrative workforce, geographically​
​dispersed field engineering teams, and a subscriber base that is highly sensitive to billing​
​accuracy and complaint-resolution turnaround time. Industry reports on regional broadband​
​operators consistently highlight that customer churn in this segment is driven less by pricing and​
​more by service reliability and the speed and transparency of complaint resolution. This makes​
​an integrated CRM platform one that ties together subscriber records, plan and billing​
​information, and field-engineering ticket workflows a strategically important investment for​
​regional ISPs seeking to remain competitive against larger, better-resourced operators.​

​1.3​​FTTH Technology​
​ ibre-To-The-Home (FTTH) is a broadband access architecture in which optical fibre is run​
F
​directly to the subscriber’s premises, replacing the copper or coaxial last-mile connections used​
​in older DSL and cable broadband technologies. An FTTH deployment typically comprises an​
​Optical Line Terminal (OLT) located at the ISP’s central office or Point-of-Presence, a​
d​ istribution network of single-mode optical fibre routed through passive optical splitters, and an​
​Optical Network Terminal (ONT) installed at the customer premises that converts the optical​
​signal into an Ethernet or Wi-Fi connection for end-user devices.​
​ rom an operational-software perspective, FTTH networks introduce distinct data-management​
F
​requirements that generic CRM platforms do not natively address: each subscriber record must​
​be associated with a specific ONT device (identified by a serial number and MAC address), a​
​specific PoP and splitter port, and a specific field engineer responsible for installation and​
​maintenance in that geographic zone. Device health signal strength, ONT online/offline status,​
​and firmware version must be tracked as first-class data alongside the customer’s billing and​
​plan information. This project’s Device Monitoring and Inventory Management modules are​
​designed specifically to accommodate this FTTH-specific data model, distinguishing the​
​proposed system from horizontal, industry-agnostic CRM products.​

​1.4​​Current Problems in the Existing Setup​

​ rior to this project, Charotar Telelink Pvt. Ltd. relied on a combination of Microsoft Excel​
P
​spreadsheets for subscriber and billing records, a shared Google Sheet for complaint tracking,​
​and manual paper-based inventory registers maintained at the regional office. Structured​
​discussions with the organization’s operations staff, conducted during the requirement-gathering​
​phase of this project, surfaced the following recurring problems:​
​1.​ ​ ragmented customer data​​— subscriber contact details,​​active plan, connection​
F
​date, and outstanding dues were often recorded inconsistently across multiple​
​spreadsheets maintained by different staff members, leading to discrepancies during​
​billing cycles.​
​2.​ ​Manual and error-prone invoicing​​— invoices were generated​​manually in a​
​spreadsheet template and printed or shared as scanned images, with no standardized​
​numbering scheme, no automated tax computation, and no digital payment​
​integration, resulting in delayed collections.​
​3.​ ​Untracked complaint tickets​​— customer complaints​​reported over phone calls or​
​WhatsApp messages were logged informally, with no unique ticket identifier, no​
​assigned-engineer tracking, and no service-level-agreement (SLA) monitoring, leading​
​to complaints being missed or resolved late.​
​4.​ ​No visibility into field inventory​​— ONT devices,​​splitters, and cables issued to field​
​engineers were tracked on paper, making it difficult to reconcile stock levels against​
​vendor purchase records or to identify which device serial number was installed at​
​which customer premises.​
​5.​ ​Disconnected vendor records​​— purchase orders and​​payments to hardware vendors​
​were tracked independently of the inventory register, making it difficult to compute​
​outstanding vendor liabilities at any given point in time.​
​6.​ ​Absence​​of​​role-based​​access​​—​​all​​staff​​members​​had​​equal​​access​​to​​the​​same​​shared​
​spreadsheets,​ ​with​ ​no​ ​mechanism​ ​to​ ​restrict​ ​sensitive​ ​financial​ ​data​ ​from​ ​field​
​engineers or restrict customer-facing staff from modifying billing records.​
​7.​ ​No consolidated reporting or analytics​​— management​​had no single dashboard from​
​which to view active subscriber counts, monthly revenue, outstanding dues, open​
​complaint tickets, or inventory stock levels, requiring manual compilation from​
​multiple sources before any operational review meeting.​
​1.5​​Need of a CRM System​
​ he problems identified in Section 1.4 collectively point to the need for a single, centralized,​
T
​role-aware software platform capable of managing the complete subscriber lifecycle — from​
​onboarding and plan assignment, through billing and payment collection, to complaint​
​resolution and eventual churn — while giving each category of staff member (administrative,​
​engineering, accounts) access only to the functionality relevant to their role. A cloud-hosted​
​CRM addresses these needs by providing:​
​•​ ​ single source of truth for customer, plan, billing, and device data, accessible​
A
​concurrently by multiple staff members without version-conflict issues inherent to​
​shared spreadsheets.​
​•​ ​Automated, auditable invoice generation with integrated UPI QR-code payment​
​support, reducing billing errors and shortening the payment-collection cycle.​
​•​ ​A structured complaint-ticketing workflow with engineer assignment and status​
​tracking, improving accountability and reducing average resolution time.​
​•​ ​Role-based access control, ensuring that sensitive financial and administrative data​
​is only visible to authorized personnel.​
​•​ ​Real-time dashboard analytics, giving management immediate visibility into​
​key operational metrics without manual data compilation.​
​•​ ​A cloud-hosted architecture that is accessible to field engineers and administrative staff​
​from any location with an internet connection, which is essential given the organization’s​
​geographically distributed operations across multiple PoPs.​

​1.6​​Objectives of the Project​

​The project was undertaken with the following specific objectives:​
​1.​ ​ o design and develop a centralized, cloud-hosted CRM platform tailored to the​
T
​operational workflows of a regional ISP enterprise.​
​2.​ ​To implement a role-based access control mechanism supporting six distinct user roles —​
​Super Admin, Administrator, Engineer, Accounts, Customer, and Vendor — each with a​
​scoped set of permissions.​
​3.​ ​To automate the invoice-generation process, including PDF generation and UPI​
​QR-code-based payment initiation, reducing manual billing effort.​
​4.​ ​To implement a structured complaint-ticket management module enabling engineer​
​assignment, status tracking, and resolution-time monitoring.​
​5.​ ​To implement inventory and vendor management modules that maintain a reconciled​
​record of ONT devices and passive infrastructure issued to field engineers and installed​
​at customer premises.​
​6.​ ​To design and implement a high-performance backend capable of handling concurrent,​
​I/O-bound and CPU-bound operations — such as PDF generation and bulk​
​notification dispatch — using asynchronous programming and thread-pool-based​
​background task execution, and to empirically evaluate the performance benefit of this​
​design over a synchronous baseline.​
​7.​ ​To provide consolidated dashboard analytics for management-level decision-making.​
 ​8.​ ​ o containerize the complete application stack using Docker to ensure a reproducible and​
T
​portable deployment process.​

​1.7​​Scope of the Project​

​ he scope of this project encompasses the design, development, and testing of a web-based​
T
​CRM application comprising a React-based frontend, a FastAPI-based backend, and a​
​PostgreSQL database hosted on the Neon cloud platform. The functional scope includes the​
​twelve modules enumerated in the project description: Customer Management, Broadband Plan​
​Management, Invoice Generation, UPI QR Invoice, Complaint Ticket System, Vendor​
​Management, Inventory Management, Dashboard Analytics, Device Monitoring, Report​
​Generation, User Management, and Role-Based Access Control, together with Authentication​
​and Payment Tracking as cross-cutting concerns.​
​ he project scope explicitly excludes integration with a live third-party SMS gateway or​
T
​WhatsApp Business API — these notification channels are implemented as mock services for​
​demonstration purposes within the academic timeframe of the project, with real gateway​
​integration identified as future scope (Chapter 8). The scope also excludes integration with live​
​payment-gateway settlement APIs; the UPI QR invoice feature generates a scannable,​
​standards-compliant UPI payment QR code but does not process a live payment-gateway​
​callback within the current implementation.​

​1.8​​Advantages of the Proposed System​

​•​ ​ entralized data management​​eliminating the inconsistencies​​inherent to​
C
​spreadsheet-based record-keeping.​
​•​ ​Improved billing accuracy and faster collections​​through​​automated invoice​
​generation and UPI QR-based payment initiation.​
​•​ ​Faster complaint resolution​​through structured ticket​​assignment and SLA-aware​
​status tracking.​
​•​ ​Reduced administrative overhead​​through role-based​​portals that surface only​
​relevant functionality to each user category.​
​•​ ​Improved concurrency handling​​, allowing multiple staff​​members and customers to​
​interact with the system simultaneously without performance degradation, owing to​
​the asynchronous, thread-pool-augmented backend architecture.​
​•​ ​Reproducible deployment​​across development, staging,​​and production​
​environments through Docker containerization.​
​•​ ​Data-driven decision-making​​enabled by real-time dashboard​​analytics.​

​1.9​​Limitations of the Proposed System​

​•​ ​ he​ ​system​ ​currently​ ​supports​ ​mock​ ​SMS​ ​and​ ​mock​ ​WhatsApp​ ​notifications​ ​rather​
T
​than integration with a live third-party messaging gateway.​
​•​ ​The​ ​UPI​ ​QR​ ​invoice​ ​feature​ ​generates​ ​a​ ​valid,​ ​scannable​ ​payment​ ​QR​​code​​but​​does​
​not​ ​process​​live​​payment-gateway​​settlement​​confirmation;​​payment​​status​​is​​recorded​
​upon manual or semi-automated confirmation.​
​•​ ​ he system has been developed and load-tested within an academic project timeframe​
T
​and infrastructure budget, and has not been benchmarked at the subscriber-volume​
​scale of a large multi-state ISP.​
​•​ ​Offline/low-connectivity operation for field engineers (e.g., a native mobile application​
​with offline data sync) is outside the scope of the current implementation and is​
​identified as future scope.​
​CHAPTER 2: REQUIREMENT ANALYSIS​
​2.1​​Existing System​
​ he existing system at Charotar Telelink Pvt. Ltd., described in Section 1.4, is a manual and​
T
​semi-digital process built around Microsoft Excel spreadsheets, a shared Google Sheet, and​
​paper-based inventory registers. Customer onboarding is recorded in a master spreadsheet​
​maintained by the administrative staff; billing is generated by copying customer and plan data​
​into a separate invoice template each month; complaints are logged informally through phone​
​calls and WhatsApp messages relayed to field engineers; and inventory issued to engineers is​
​tracked in a physical register at the regional office. There is no single authoritative data source,​
​no automated computation of dues or SLA timers, and no access control beyond informal trust​
​among staff members sharing the same spreadsheet files.​

​2.2​​Proposed System​
​ he proposed system replaces this fragmented process with a single, cloud-hosted, role-aware​
T
​web application. Customer, plan, invoice, payment, ticket, inventory, vendor, and device data are​
​stored in a normalized relational database and exposed through a secured REST API. Each user​
​role interacts with the system through a dedicated web portal that surfaces only the functionality​
​relevant to that role, while all data changes are recorded consistently and, where applicable,​
​logged in an audit trail. Billing, notification dispatch, and report generation are automated​
​background processes rather than manual spreadsheet operations.​

​2.3​​Functional Requirements​
​Table 2.1 summarizes the functional requirements of the system, organized by module.​
​Table 2.1: Functional Requirements​

​ eq.​
R
​ID​ ​Module​ ​Requirement Description​
​FR-01​ ​Authentication​ ​ he system shall​
T
​allow registered​
​users to log in using​
​FR-02​
​an email/username​
​and password, and​
​shall issue a JWT​
​access token upon​
​successful​
​authentication.​
​Authorization​ ​The system shall​
​restrict access to​
​API endpoints​
​and UI views​
​based on the​
​authenticated​
​user’s assigned​
 ​role.​
​FR-03​ ​Customer Management​ ​ he system shall allow​
T
​authorized staff to create,​
​view, update, and​
​deactivate customer​
​records, including contact​
​details, installation​
​address, and connection​
​date.​
​FR-04​ ​Plan Management​ ​The system shall allow​
​authorized staff to​
​define broadband plans​
​with attributes​
​including speed, data​
​cap (if any), validity​
​period, and price, and to​
​assign plans to​
​customers.​
​FR-05​ ​Invoice Generation​ ​The system shall​
​automatically generate a​
​monthly invoice for each​
​active customer based on​
​their assigned plan, and​
​shall allow on-demand​
​generation of an ad-hoc​
​invoice.​
​ eq.​
R
​ID​ ​Module​ ​Requirement Description​
​FR-06​ ​UPI QR Invoice​ ​ he system shall​
T
​embed a​
​UPI-compliant​
​QR code on each​
​generated invoice​
​PDF, encoding​
​the payee VPA,​
​amount, and​
​invoice reference.​
​FR-07​ ​Payment Tracking​ ​The system shall allow​
​accounts staff to record​
​Complaint Ticketing​ ​payments against an​
​FR-08​
​invoice and shall​
​automatically update the​
​ R-09​
F ​ endor Management​
V
​invoice status (Unpaid,​
​FR-10​ ​Inventory Management​ ​Partially Paid, Paid,​
​Overdue).​
​FR-11​ ​Device Monitoring​ ​The system shall allow​
​customers and staff to raise​
​FR-12​ ​Dashboard Analytics​ ​a complaint ticket, assign​
​it to a field engineer, and​
​Report Generation​ ​track its status through a​
​FR-13​ ​defined lifecycle (Open,​
​Assigned, In Progress,​
​FR-14​ ​User & Role Management​ ​Resolved, Closed).​
​The system shall allow​
​authorized staff to​
​maintain vendor records​
​and record purchase orders​
​and payments against each​
​vendor.​
​The system shall track​
​inventory items (ONT​
​devices, splitters, cables,​
​etc.) including stock​
​quantity, unit issued to a​
​specific engineer, and​
​installation record against​
​a customer.​
​The system shall maintain​
​a record of each ONT​
​device’s serial number,​
​MAC address, assigned​
c​ ustomer, and last-known​ l​ow-stock inventory alerts​ s​ ummary) in PDF and​
​online/offline status.​ ​— on a dashboard view.​ ​Excel formats.​
​The system shall present​ ​The system shall allow​ ​The system shall allow a​
​role-appropriate summary​ ​authorized staff to​ ​Super Admin to create​
​metrics — active​ ​generate and export​ ​user accounts, assign​
​customers, monthly​ ​reports (customer list,​ ​roles, and deactivate​
​revenue, open tickets,​ ​revenue summary, ticket​ ​accounts.​
​FR-15​ ​Notifications​ ​The system shall​
​dispatch a (mock)​
​SMS/WhatsApp​
​notification to a​
​customer upon invoice​
​generation and upon​
​ticket status change.​

​2.4​​Non-Functional Requirements​
​Table 2.2: Non-Functional Requirements​

​ eq.​
R
​ID​ ​Category​ ​Requirement Description​
​NFR-01​ ​Performance​ ​The​ ​system​ ​shall​ ​respond​ ​to​
​standard​ ​read​​API​​requests​
​NFR-02​
​within​​300​​ms​​under​​a​​load​
​of​​100​​concurrent​​users,​​as​
​NFR-03​ ​measured in Chapter 7.​
​Scalability​ ​The​ ​backend​ ​shall​
​handle​ ​CPU-bound​
​background​ ​tasks​
​(PDF/QR​ ​generation)​
​without​ ​blocking​
​concurrent​ ​request​
​handling,​ ​using​ ​a​
​thread-pool-based​
​execution model.​
​Security​ ​The​ ​system​ ​shall​ ​store​
​passwords​ ​using​ ​a​ ​salted​
​hashing​ ​algorithm​ ​and​
​shall​​never​​persist​​plaintext​
​passwords.​
​ eq.​
R
​ID​ ​Category​ ​Requirement Description​
​NFR-04​ ​Security​ ​ ll API endpoints, except​
A
​login and public​
​NFR-05​
​health-check, shall require​
​NFR-06​ ​a valid JWT bearer token.​
​NFR-07​ ​Availability​ ​The system shall be​
​deployable as a set of​
​ FR-08​
N ​Docker containers to​
​NFR-09​ ​enable consistent,​
​repeatable deployment​
​with minimal downtime.​
​NFR-10​
​Usability​ ​The frontend shall be a​
​responsive single-page​
​application usable on​
​desktop and tablet​
​viewports.​
​Maintainability​ ​The backend codebase​
​shall follow a layered​
​architecture (routers,​
​services, models,​
​schemas) to separate​
​concerns and ease​
​future maintenance.​
​ ortability​
P ​The database layer shall​
​use SQLAlchemy ORM​
​to remain portable​
​across​
​PostgreSQL-compatible​
​database engines.​
​Auditability​ ​All create, update, and​
​delete operations on​
​financially sensitive​
​records shall be​
​recorded in an audit​
​log with user,​
​timestamp, and action.​
​Documentation​ ​All REST API​
​endpoints shall be​
​self-documented​
​through an​
​auto-generated​
​OpenAPI/Swagger​
​interface.​
​2.5​​Feasibility Study​
​2.5.1​​Technical Feasibility​
​ ll technologies selected for this project — React, FastAPI, PostgreSQL, SQLAlchemy, Docker​
A
​— are mature, well-documented, open-source technologies with active community support, and​
​were already familiar to the development team from prior academic coursework, making the​
​project technically feasible within the available six-month project timeframe. Neon’s serverless​
​PostgreSQL offering removes the need for the team to provision and maintain database​
​infrastructure independently, further reducing technical risk.​
​2.5.2​​Economic Feasibility​
​ he project was developed using entirely open-source software components, with no licensing​
T
​cost. The only recurring cost is the cloud database hosting fee, for which Neon offers a free tier​
​sufficient for development and academic demonstration purposes. Deployment infrastructure (a​
​small cloud virtual machine or on-premise server running Docker) represents a modest recurring​
​cost that is significantly lower than the cost of a comparable commercial CRM licence, making​
​the system economically attractive for a small-to-medium regional ISP such as Charotar Telelink​
​Pvt. Ltd.​
​2.5.3​​Operational Feasibility​
​ he system was designed in close consultation with the organization’s operations staff to mirror​
T
​their existing workflows (customer onboarding, monthly billing cycle, complaint escalation to​
​field engineers) as closely as possible, minimizing the retraining burden on non-technical staff.​
​The web-based, browser-accessible interface requires no client-side software installation, and the​
t​ailored, role-specific portals reduce the cognitive load on each user category by surfacing only​
​relevant functionality.​
​2.5.4​​Schedule Feasibility​
​ he six-month project duration was allocated across requirement analysis, design,​
T
​implementation, and testing phases, as summarized in Table 2.3-A below. The modular​
​architecture of the system allowed backend and frontend development to proceed in parallel once​
​the API contract was finalized, which was essential to completing the system within the​
​available timeframe.​
​Table: Project Schedule​

​ hase​
P ​ uration​
D ​ ey Deliverables​
K
​Requirement Analysis & SRS​ ​Month 1​ ​Finalized SRS, ER Diagram​
​System Design​ ​Month 2​ ​Architecture diagrams, API contract, wireframes​
​Backend Development​ ​Months 2–4​ ​FastAPI routers, database models, authentication​
​Frontend Development​ ​Months 3–5​ ​React portals for each role​
​Integration & Testing​ ​Month 5​ ​Integrated system, test case execution​
​Deployment & Documentation​ ​Month 6​ ​Dockerized deployment, final report​

​2.6​​Risk Analysis​
​Table 2.3: Risk Analysis Matrix​

​ isk​
R
​ID​ ​ isk Description​
R ​ ikelihood​ I​ mpact​ M
L ​ itigation Strategy​
​R-​ ​Scope creep from additional​ ​Medium​ ​Medium​ ​Freeze functional scope​
​01​ ​module requests during​ ​after SRS sign-off; treat​
​development​ ​further requests as future​
​scope​
​ -​
R ​ atabase schema changes late in​
D ​Medium​ ​High​ ​Use Alembic-style​
​02​ ​development affecting multiple​ ​migration discipline;​
​modules​ ​finalize ER diagram before​
​implementation​
​ -​
R ​ loud​
C ​database​ (​ Neon)​ ​Low​ ​Medium​ ​Use connection pooling;​
​03​ ​free-tier​ ​connection​ l​imits​ ​conduct load tests during​
​affecting load testing​ ​off-peak development​
​windows​
​ -​
R ​ eam unfamiliarity with​
T ​Medium​ ​Medium​ ​Allocate dedicated​
​04​ ​AsyncIO/ThreadPoolExecutor​ ​learning/prototyping sprint​
​integration​ ​before full implementation​
​ -​
R ​Security vulnerabilities in​ ​Low​ ​High​ ​Follow JWT best practices;​
​05​ ​authentication​ ​conduct security testing​
​implementation​ ​(Chapter 6)​
​ isk​
R
​ID​ ​ isk Description​
R ​ ikelihood​ I​ mpact​
L ​ itigation Strategy​
M
​R-​ ​Delay in receiving real operational​ ​Medium​ ​Low​ ​Use representative synthetic​
​06​ ​data from industry mentor​ ​data for development and​
​demonstration​

​2.7​​Software Requirement Specification (SRS)​

​2.7.1​​Purpose​
​ his SRS defines the functional and non-functional requirements for the High-Performance​
T
​Multi-Threaded Cloud-Based CRM for ISP Enterprises, intended for use by the​
​development team and evaluation committee to establish a shared understanding of the​
​system’s intended behaviour.​
​2.7.2​​Product Perspective​
​ he​ ​system​ ​is​ ​a​ ​standalone,​ ​cloud-hosted​ ​web​ ​application.​ ​It​ ​does​ ​not​ ​integrate​ ​with​ ​any​
T
​pre-existing​ ​legacy​ ​system​ ​at​ ​Charotar​ ​Telelink​ ​Pvt.​ ​Ltd.,​ ​since​ ​no​ ​such​ ​digital​ ​system​
​previously​ ​existed;​ ​it​ ​replaces​ ​the​ ​manual​ ​and​ ​spreadsheet-based​ ​process​ ​described​ ​in​
​Section 2.1.​
​2.7.3​​User Classes​
​ ix​​user​​classes​​are​​defined:​​Super​​Admin,​​Administrator,​​Engineer,​​Accounts,​​Customer,​​and​
S
​Vendor. Their permissions are detailed in the Role–Permission Matrix (Table 8.3, Appendix).​
​2.7.4​​Operating Environment​
​ he backend is designed to run within a Docker container on any Linux-based host supporting​
T
​Docker Engine. The frontend is a static single-page application servable from any standard web​
​server or CDN, and is compatible with modern evergreen browsers (Chrome, Firefox, Edge,​
​Safari).​
​2.7.5​​Design and Implementation Constraints​
​ he backend must be implemented in Python 3.10 using FastAPI, with SQLAlchemy as the​
T
​ORM against a PostgreSQL-compatible database. The frontend must be implemented in React​
​18 with TypeScript. These constraints were fixed by the project specification prior to design​
​commencement.​
​Table 2.4: SRS Summary​

​ ttribute​
A ​ pecification​
S
​Number of Functional Requirements​ ​15​
​Number of Non-Functional Requirements​ ​10​
​Number of User Roles​ ​6​
​Number of Core Modules​ ​12​
​Primary Backend Language​ ​Python 3.10​
​ ttribute​
A ​ pecification​
S
​Primary Frontend Language​ ​TypeScript​
​Database Engine​ ​PostgreSQL (Neon Cloud)​
​Deployment Model​ ​Docker / Docker Compose​
​CHAPTER 3: SYSTEM ANALYSIS AND DESIGN​
​3.1​​Overall System Architecture​
​ he system follows a modern three-tier architecture comprising a React-based presentation tier,​
T
​a FastAPI-based application tier, and a PostgreSQL-based data tier, supported by a set of​
​background utility services for PDF generation, QR code creation, and notification dispatch. The​
​application tier is deployed as a Docker container running the Uvicorn ASGI server, which hosts​
​the FastAPI application and its AsyncIO event loop.​

​Figure 3.1: Overall System Architecture​

​ igure 3.1​​illustrates how a client request flows​​from the React SPA over HTTPS to the FastAPI​
F
​application. Requests are first authenticated via the JWT middleware; simple database read/write​
​operations are handled directly on the async event loop, while CPU-intensive operations such as​
​PDF and QR generation are dispatched to a ThreadPoolExecutor so that the event loop remains​
​free to service other concurrent requests.​
​3.2​​Cloud Architecture​
​ igure 3.2​​depicts the cloud deployment topology.​​Client traffic is received by a reverse proxy /​
F
​load balancer, which distributes requests across one or more FastAPI container instances. Each​
​backend instance connects to a single, centrally hosted Neon serverless PostgreSQL database,​
​allowing the application tier to be scaled horizontally by adding container instances without any​
​change to the data tier. Neon’s storage layer performs automated continuous backups, removing​
​the operational burden of manual database backup management from the ISP’s small IT team.​

​Figure 3.2: Cloud Deployment Architecture​

​3.3​​Multi-Layer (Three-Tier) Architecture​

​ igure 3.3​​presents the logical layering of the system,​​from the presentation layer down to the​
F
​database layer. Each layer communicates only with the layer immediately below it, which​
​enforces separation of concerns: the presentation layer never queries the database directly, and​
​the API layer never renders UI markup.​
​Figure 3.3: Multi-Layer Architecture​
​•​ ​ resentation Layer:​​React components, pages, and the​​Context API-based​
P
​state management.​
​•​ ​API Layer:​​FastAPI routers and Pydantic schemas that​​validate and serialize​
​requests and responses.​
​•​ ​Service Layer:​​Business logic — invoice computation,​​ticket-assignment​
​rules, inventory reconciliation.​
​•​ ​Data Access Layer:​​SQLAlchemy ORM models mapping Python​​classes to​
​relational tables.​
​•​ ​Database Layer:​​The PostgreSQL database hosted on​​Neon.​

​3.4​​Component Diagram​
​ igure 3.4​​shows how frontend portal modules map onto​​backend router components. Each​
F
​portal communicates only with the backend routers relevant to its role — for instance, the​
​Vendor Portal communicates exclusively with the Vendor Router, reinforcing the role-based​
​separation of functionality described in Chapter 2.​

​Figure 3.4: Component Diagram​

​3.5​​Deployment Diagram​
​ igure 3.5​​shows the physical deployment view. The​​frontend static build is served by an Nginx​
F
​container, which proxies API calls to the backend FastAPI container over the internal Docker​
​network; the backend connects out to the externally hosted Neon database over an encrypted​
​TLS connection string.​
​Figure 3.5: Deployment Diagram​

​3.6​​Use Case Diagram​

​ igure 3.6​​enumerates the principal use cases available​​to each of the six actor roles. The Super​
F
​Admin is the only actor capable of managing users and roles; Accounts staff are the only actors​
​capable of generating invoices and recording payments; Engineers resolve tickets and manage​
​field inventory; Customers raise tickets and view their own invoices; and Vendors manage their​
​own purchase-order fulfilment records.​
​Figure 3.6: Use Case Diagram​

​3.7​​Activity Diagram — Complaint Ticket Lifecycle​

​ igure 3.7​​models the lifecycle of a complaint ticket​​from creation to closure. A ticket begins in​
F
​the Open state, moves to In Progress once assigned to and picked up by a field engineer, and​
​transitions to Resolved once the engineer records a fix. The ticket only reaches the terminal​
​Closed state after the customer confirms resolution, ensuring engineer-reported fixes are​
​validated from the customer’s perspective before the ticket is considered complete.​

​Figure 3.7: Activity Diagram — Complaint Ticket Lifecycle​

​3.8​​Sequence Diagram — Invoice Generation​

​ igure 3.8​​traces the sequence of interactions triggered​​when an Accounts staff member​
F
​generates an invoice. Note that PDF and QR-code rendering is submitted to the​
​ThreadPoolExecutor rather than executed inline, allowing the FastAPI event loop to continue​
​serving other requests while the (relatively slow, CPU-bound) PDF-rendering operation executes​
​on a worker thread.​

​Figure 3.8: Sequence Diagram — Invoice Generation​

​3.9​​Authentication and Authorization Flow​

​ igure 3.9​​shows the authentication sequence. Upon​​successful credential verification, the​
F
​backend issues a signed JWT containing the user’s identity and role claim; this token is​
​subsequently presented as a Bearer token on every protected request and is verified by the​
​authentication middleware without requiring a database lookup on each request, which reduces​
​authentication latency under concurrent load.​
​Figure 3.9: Authentication and Authorization Flow​

​3.10​​Database Interaction Flow​

​ igure 3.10​​illustrates how a FastAPI dependency (​​
F get_db​​)​​creates a scoped SQLAlchemy​
​session per request, executes the ORM query against the connection pool, and tears the session​
​down once the response has been serialized, preventing connection leaks under sustained​
​concurrent load.​

​Figure 3.10: Database Interaction Flow​

​3.11​​ThreadPoolExecutor Workflow​
​ igure 3.11​​details the decision logic used within​​the service layer to determine whether an​
F
​operation should be executed directly on the async event loop (for lightweight database​
​operations) or offloaded to the ThreadPoolExecutor (for CPU-bound operations such as PDF​
​rendering, QR-code generation, and bulk Excel report export). This hybrid design is central to​
t​he “high-performance” and “multi-threaded” characteristics of the system and is evaluated​
​quantitatively in Chapter 7.​

​Figure 3.11: ThreadPoolExecutor Workflow​

​3.12​​REST API Request/Response Flow​

​ igure 3.12​​shows the middleware pipeline applied​​to every incoming request: CORS​
F
​validation, JWT authentication, role-based permission checking, Pydantic request-body​
​validation, service-layer execution, and Pydantic response-schema serialization before the JSON​
​payload is returned to the client.​

​Figure 3.12: API Request/Response Flow​

​3.13​​Invoice Generation Workflow​

​ igure 3.13​​models the end-to-end monthly billing​​workflow, from the scheduled billing trigger​
F
​through per-customer invoice creation, PDF/QR generation, notification dispatch, and eventual​
​status transition to Paid or Overdue based on whether payment is recorded before or after the​
​due date.​

​Figure 3.13: Invoice Generation Workflow​

​3.14​​Ticket Escalation Workflow​

​ igure 3.14​​details the operational workflow applied​​by support staff when triaging an incoming​
F
​ticket — automatic categorization by complaint type, assignment to the nearest available​
​engineer, and progression through resolution and customer-confirmed closure.​

​Figure 3.14: Ticket Escalation Workflow​

​3.15​​Vendor Procurement Workflow​

​ igure 3.15​​shows the procurement cycle triggered​​when inventory stock falls below a​
F
​configured threshold: purchase-order creation, vendor delivery, stock reconciliation, and​
​eventual payment recording against the vendor ledger.​
​Figure 3.15: Vendor Procurement Workflow​

​3.16​​Network Topology Diagram​

​ igure 3.16​​shows the physical FTTH network topology​​that the system’s Device Monitoring​
F
​module maps onto its data model: an OLT at the central office feeds passive splitter zones, each​
​of which serves multiple customer-premises ONT devices whose online/offline status is reported​
​into the CRM’s Device Monitoring module.​

​Figure 3.16: Network Topology Diagram​

​3.17​​React Frontend Component Architecture​

​ igure 3.17​​shows the frontend’s internal composition:​​a root​​
F [Link]​​component wraps the​
​application in React Router and an​​ AuthContext​​provider;​​a​​ ProtectedRoute​​wrapper checks​
​authentication state and role before rendering role-specific page components, which in turn​
​consume custom hooks (​​ useAuth​ ​,​​ ​) that wrap a​​shared Axios instance configured with a​
useApi​
​JWT request interceptor.​
​Figure 3.17: React Frontend Component Architecture​

​3.18​​FastAPI Backend Layered Architecture​

​ igure 3.18​​shows the backend’s internal Python package​​structure: the​​
F [Link]​​entry point​
​configures the FastAPI application, its lifespan context manager, and middleware, and mounts a​
​package of routers; each router depends on Pydantic schemas for I/O validation and a service​
​layer that operates on SQLAlchemy models bound to the database session.​

​Figure 3.18: FastAPI Backend Layered Architecture​

​3.19​​Docker Container Architecture​

​ igure 3.19​​shows the Docker Compose service topology​​used for deployment: a​​
F frontend​
​service serving the compiled React build, a​​backend​​service running the FastAPI application​
​under Uvicorn, and an optional local​​
db​​service used​​only for local development, all connected​
​via a shared Docker bridge network, with a named volume persisting local PostgreSQL data​
​during development.​
​Figure 3.19: Docker Container Architecture​
​CHAPTER 4: DATABASE DESIGN​
​4.1​​Entity-Relationship Diagram​
​ he database schema was designed in Third Normal Form (3NF) to eliminate redundant​
T
​storage of customer, plan, and vendor attributes while preserving referential integrity across the​
​thirteen core tables listed in the project specification.​​Figure 4.1​​presents the complete​
​Entity-Relationship diagram for the system.​

​Figure 4.1: Entity-Relationship Diagram​

​ he schema is organized around four logical clusters:​​Identity & Access​​(Roles, Users,​
T
​Sessions, Audit Logs),​​Subscriber & Billing​​(Customers,​​Plans, Invoices, Payments),​​Field​
​Operations​​(Tickets, Devices, Inventory), and​​Supply​​Chain​​(Vendors), with Notifications​
​acting as a cross-cutting table referenced by any user-facing event.​
​4.2​​Table Documentation​
​4.2.1​​Users Table​
​Table 4.1: Users Table Schema​

​ olumn​
C ​ ype​
T ​ onstraint​
C ​Description​
​user_id​ ​INTEGER​ ​PK, Auto Increment​ ​Unique identifier for the user​
​role_id​ ​INTEGER​ ​FK →​ A
​ ssigned role governing RBAC​
​roles.role_id,​ ​permissions​
​ OT NULL​
N

u​ sername​ ​ ARCHAR(50)​ U
V ​ NIQUE, NOT NULL​ ​ ogin identifier​
L
​email​ ​VARCHAR(100)​ ​UNIQUE, NOT NULL​ ​Contact email, used for​
​notifications​
​password_hash​ ​VARCHAR(255)​ ​NOT NULL​ ​Salted bcrypt hash of the user’s​
​password​
​is_active​ ​ OOLEAN​
B ​NOT​ ​ oft-delete / deactivation flag Account​
S
​NULL, DEFAULT​
​TRUE​ ​creation timestamp​
​created_at​ ​TIMESTAMP​ ​NOT​
​NULL, DEFAULT​
​now()​

I​ ndexes:​​Unique index on​​ ​; unique index on​​

username​ ​; non-unique index on​​
email​ role_id​​to​
​accelerate role-scoped queries.​
​4.2.2​​Roles Table​
​Table 4.2: Roles Table Schema​

​ olumn​
C ​ ype​
T ​ onstraint​
C ​ escription​
D
​role_id​ ​INTEGER​ ​PK, Auto​ ​Unique identifier for the role​
​Incremen​
​t​ ​ ne of: Super Admin, Administrator,​
O
r​ ole_name​ ​VARCHAR(30)​ ​UNIQUE,​ ​Engineer, Accounts, Customer, Vendor​
​NOT​
​NULL​

​description​ ​VARCHAR(255)​ ​NULL​ ​ uman-readable description of the role’s​

H
​purpose​

​Design note:​​Roles are stored as a reference table​​rather than a hard-coded enumeration so that​
​the Super Admin can, in future, introduce new roles without a schema migration.​
​4.2.3​​Customers Table​
​Table 4.3: Customers Table Schema​

​ olumn​
C ​ ype​
T ​ onstraint​
C ​Description​
​customer_id​ ​INTEGER​ ​PK, Auto Increment​ ​Unique identifier for the customer​
​user_id​ ​INTEGER​ ​FK →​ ​Linked login account for customer​
​users.user_id,​ ​self-service portal (nullable until​
​ NIQUE, NULL​
U
​Column​ ​Type​ ​Constraint​ ​ escription​
D
​account is provisioned)​
​plan_id​ ​INTEGER​ ​FK → plans.plan_id,​ ​Currently assigned broadband plan​
​NOT NULL​
f​ ull_name​ ​VARCHAR(100)​ ​NOT NULL​ ​Subscriber’s full name​
​phone​ ​ ARCHAR(15)​
V P
​ rimary contact number​
​UNIQ​
​UE,​
​NOT​
​NULL​

a​ ddress​ ​ EXT​
T ​NOT NULL​ ​Installation address​
​connection_date​ D​ ATE​ ​NOT NULL​ ​Date of service​
​activation​
​is_active​ ​BOOLEAN​ ​NOT​ ​ hether the connection is​
W
​NULL,​ ​currently active​
​ EFAUL​
D
​T TRUE​

​Indexes:​​Unique index on​​ ​; non-unique index on​​

phone​ plan_id​​for plan-wise reporting.​

​4.2.4​​Plans Table​
​Table 4.4: Plans Table Schema​

​ olumn​
C ​ ype​
T ​ onstraint​
C ​ escription​
D
​plan_id​ ​INTEGER​ ​PK, Auto Increment​ ​Unique identifier for the plan​
​plan_name​ ​VARCHAR(50)​ ​NOT NULL​ ​Display name, e.g. “Home​
​Fibre​
​ 00”​
1
s​ peed_mbps​ ​INTEGER​ ​NOT NULL,​ ​Download speed in megabits per second​
​CHECK >​ ​Monthly subscription price Billing​
0​ ​
p​ rice​ ​ ECIMAL(10,2)​ ​NOT NULL,​
D ​cycle length in days​
​CHECK​
​ = 0​
>
​validity_days​ ​INTEGER​ ​NOT NULL,​
​DEFAULT​
​30​

​4.2.5​​Invoices Table​
​Table 4.5: Invoices Table Schema​

​ olumn​
C ​ ype​
T ​ onstraint​
C ​ escription​
D
​invoice_id​ ​INTEGER​ ​PK, Auto Increment​ ​Unique identifier for the​
​invoice​
c​ ustomer_id​ ​INTEGER​ ​FK →​ ​Billed customer​
​customers.customer_id,​
​NOT NULL​

a​ mount​ ​DECIMAL(10,2)​ ​ OT NULL​

N ​ otal invoice amount​
T
​issue_date​ ​DATE​ ​NOT NULL​ ​Date invoice was generated​
​due_date​ ​DATE​ ​NOT NULL​ ​Payment due date​
​status​ ​VARCHAR(20)​ ​NOT​ ​One of: Unpaid, Partially Paid, Paid,​
​NULL, DEFAULT​ ​Overdue​
​‘Unpaid’​
I​ ndexes:​​Composite index on (​​ ​,​​
customer_id​ ​)​​to accelerate the common query​
status​
​“outstanding invoices for customer X”.​
​4.2.6​​Payments Table​
​Table 4.6: Payments Table Schema​

​ olumn​
C ​ ype​
T ​ onstraint​
C ​ escription​
D
​payment_id​ ​INTEGER​ ​PK, Auto Increment​ ​Unique identifier for the​
​payment record​
i​nvoice_id​ ​INTEGER​ ​FK →​ ​Invoice being settled​
​invoices.invoice_id,​
​ OT​
N
​NULL​

a​ mount_paid​ D​ ECIMAL(10,2)​ N
​ OT NULL, CHECK > 0​ ​Amount received​
​payment_date​ ​DATE​ ​NOT NULL​ ​Date payment was​
​recorded​
​mode​ ​VARCHAR(20)​ ​NOT NULL​ ​ ne of: UPI, Cash, Bank​
O
​Transfer, Cheque​

​4.2.7​​Tickets Table​
​Table 4.7: Tickets Table Schema​
​ olumn​
C ​Type​ ​Constraint​ ​Description​
​ticket_id​ ​INTEGER​ ​PK, Auto Increment​ ​ nique identifier​
U
​for​
​the complaint ticket​
​customer_id​ ​INTEGER​ ​ ​
U
​​
F ​LL​
​K​
​→​
​cu​
​sto​
​me​
​rs.​
​cu​
​sto​
​me​
​r_i​
​d,​
​N​
​O​
​T​
​N​
​ omplainant​
C
​assigned_engineer_id​ ​INTEGER​ ​FK → users.user_id, NULL​ E ​ ngineer assigned​
​(null while​
​unassigned)​
​subject​ ​VARCHAR(150)​ ​NOT NULL​ ​Short complaint​
​summary​
​description​ ​ EXT​
T ​NULL​ ​Detailed​
​complaint​
​narrative​
​status​ ​VARCHAR(20)​ O
​ ne of: Open, Assigned, In Progress,​
​NOT NULL, DEFAULT​ ​Resolved, Closed​
​‘Op​ ​Ticket creation time​
​en’​

c​ reated_at​ ​TIMESTAMP​
​NOT NULL, DEFAULT​
​ o​
n
​w()​

​resolved_at​ ​TIMESTAMP​ ​NULL​ ​ ime of resolution,​

T
​used for SLA​
​computation​
 ​4.2.8​​Inventory Table​
​Table 4.8: Inventory Table Schema​

​ olumn​
C ​ ype​
T ​ onstraint​
C ​ escription​
D
​item_id​ ​INTEGER​ ​PK, Auto Increment​ ​Unique identifier for the​
​inventory item​
v​ endor_id​ ​INTEGER​ ​FK →​ ​ upplying vendor​
S
​vendors.vendor_id,​
​NOT NULL​

​item_name​ ​VARCHAR(100)​ ​NOT NULL​ e​ .g. “ONT - Dual Band”,​

​“Fibre Patch Cord 2m”​
​quantity​ ​INTEGER​ ​NOT NULL, CHECK >= 0​ ​Current stock quantity​
​status​ ​VARCHAR(20)​ ​NOT NULL,​ O
​ ne of: In Stock, Issued, Installed,​
​DEFAULT ‘In​ F
​ aulty​
​Stock’​

​4.2.9​​Vendors Table​
​Table 4.9: Vendors Table Schema​

​ olumn​
C ​ ype​
T ​ onstraint​
C ​ escription​
D
​vendor_id​ ​INTEGER​ ​PK, Auto Increment​
​Unique identifier for the​
​vendor​
​vendor_name​ ​VARCHAR(100)​ ​NOT NULL​ ​Registered business​
​name contact_person​ ​VARCHAR(100)​ ​NULL​ ​Primary point of contact​
​phone​ ​VARCHAR(15)​ ​NOT NULL​ ​Contact number​
​outstanding_balance​ ​DECIMAL(10,2)​ ​NOT​ ​Amount currently owed to the​
​NULL,​ ​vendor​
​DEFAULT 0​

​4.2.10​​Devices Table​
​Table 4.10: Devices Table Schema​

​ olumn​
C ​ ype​
T ​ onstraint​
C ​ escription​
D
​device_id​ ​INTEGER​ ​PK, Auto Increment​ ​Unique identifier for the​
​device​
​customer_id​ ​INTEGER​ ​ K →​
F ​item_id​ ​INTEGER​ ​FK →​
​customers.c​ ​inventory.item_id,​
​ustomer_id,​ ​NOT​
​NULL​ ​NULL​
I​ nstalled-at customer (null while in​
​warehouse)​ ​Source inventory item batch​
​serial_number​ ​VARCHAR(50)​ ​UNIQUE, NOT NULL​ ​Manufacturer serial​
​number mac_address​ ​VARCHAR(17)​ ​UNIQUE, NOT NULL​
​Device MAC address​
​status​ ​VARCHAR(20)​ ​NOT​ ​One of: Online, Offline, Faulty​
​NULL, DEFAULT​
​‘Online’​
​4.2.11​​Notifications Table​
​Table 4.11: Notifications Table Schema​
​ olumn​
C ​ ype​
T ​ onstraint​
C ​ escription​
D
​notification_id​ ​INTEGER​ ​PK, Auto Increment​ ​Unique identifier for the​
​notification​
u​ ser_id​ ​INTEGER​ ​FK →​ ​Recipient​
​users.user_id, NOT​
​NULL​

​channel​ ​VARCHAR(20)​ ​NOT NULL​ ​ ne of: SMS (mock),​

O
​WhatsApp (mock)​
​ essage​
m ​TEXT​ ​ OT NULL​
N ​Notification body​
​sent_at​ ​TIMESTAMP​ ​NOT​ ​ ispatch timestamp​
D
​NULL, DEFAULT​
​now()​

​4.2.12​​Audit Logs Table​

​Table 4.12: Audit Logs Table Schema​

​ olumn​
C ​ ype​
T ​ onstraint​
C ​ escription​
D
​log_id​ ​INTEGER​ ​PK, Auto Increment​ ​Unique identifier for the audit​
​entry​
u​ ser_id​ ​INTEGER​ ​FK →​ ​ ser who performed the action​
U
​users.user_id, NOT​
​NULL​

​action​ ​VARCHAR(20)​ ​NOT NULL​ ​ ne of: CREATE, UPDATE,​

O
​DELETE​
e​ ntity​ ​ ARCHAR(50)​ N
V ​ OT NULL​ ​Name of the affected table/entity​
​timestamp​ ​TIMESTAMP​ ​NOT NULL,​ ​ hen the action occurred​
W
​DEFAULT​
​now()​

​4.2.13​​Sessions Table​
​Table 4.13: Sessions Table Schema​

​ olumn​
C ​ ype​
T ​ onstraint​
C ​ escription​
D
​session_id​ ​INTEGER​ ​PK, Auto Increment​ ​Unique identifier for the session​
​record​
u​ ser_id​ ​INTEGER​ ​FK →​ ​Session owner​
​users.user_id,​
​NOT NULL​

​token_jti​ ​VARCHAR(64)​ ​UNIQUE, NOT NULL​ J​ WT unique identifier (jti claim),​

​used for revocation​
i​ssued_at​ T ​ IMESTAMP​ ​ OT NULL​
N ​Token issue time​
​expires_at​ ​TIMESTAMP​ ​NOT NULL​ ​Token expiry time​
​4.3​​Primary Keys, Foreign Keys, and Constraints​
​ very table in the schema is assigned a surrogate integer primary key (​​
E <table>_id​ ​) rather than​
​a natural key, to insulate the schema from changes to naturally-occurring identifiers such as​
​phone numbers, which may be reassigned in rare cases. Foreign key relationships enforce​
​referential integrity throughout: for example, the​​ invoices.customer_id​​column cannot​
​reference a non-existent customer, and an attempt to delete a customer with existing invoices is​
​rejected under a​​ RESTRICT​​delete rule to prevent orphaned​​billing history. Nullable foreign keys​
​— such as​​ tickets.assigned_engineer_id​​and​​ devices.customer_id​​— deliberately​
​represent the “not yet assigned” and “not yet installed” states without requiring a sentinel row.​
​Check constraints enforce domain rules at the database layer independent of application code:​
plans.speed_mbps > 0​
​ ​,​​
[Link] >= 0​ ​,​​
[Link]​​ ​, and​
>= 0​
payments.amount_paid > 0​
​ ​. Status columns (​​ [Link]​ ​,​​
[Link]​​,​
[Link]​
​ ​,​​
[Link]​ ​) are constrained​​to a fixed set of values via​
​application-layer Pydantic enums, mirrored by database check constraints, to prevent invalid​
​state values from being written directly.​

​4.4​​Indexes​
I​ n addition to the primary key indexes automatically created by PostgreSQL, the following​
​secondary indexes were introduced based on the most frequent query patterns observed during​
​requirement analysis: a unique index on​​ [Link]​​and​​[Link]​​for fast login​
​lookups; a unique index on​​ [Link]​​to prevent​​duplicate subscriber registration; a​
​composite index on​​ invoices(customer_id, status)​​to​​accelerate the “outstanding dues for​
​customer” query used on both the customer portal and the accounts dashboard; and a unique​
​index on​​
devices.serial_number​​and​​ devices.mac_address​​to guarantee device​
​uniqueness across the entire deployed fleet.​

​4.5​​Normalization​
​ he schema was normalized to Third Normal Form (3NF). Each table’s non-key attributes​
T
​depend only on that table’s primary key (eliminating partial dependencies), and no non-key​
​attribute depends on another non-key attribute (eliminating transitive dependencies). For​
​example, rather than storing a customer’s plan name and price directly on the​​ customers​​table​
​(which would create a transitive dependency and risk data inconsistency if a plan’s price​
​changes), the​​ customers​​table stores only a foreign​​key reference to​​ plans.plan_id​ ​; the​
​plan’s current price is always resolved through a join at query time, ensuring that historical​
​invoices — which store their own​​ amount​​value independently​​— are unaffected by subsequent​
​plan price changes. This deliberate denormalization of the​​ amount​​field on the​​ invoices​​table​
​(rather than deriving it live from the linked plan) is a considered exception: it preserves an​
​accurate historical billing record even if the underlying plan price is revised in the future.​
​CHAPTER 5: IMPLEMENTATION​
​5.1​​Frontend Implementation​
​ he frontend is implemented as a React 18 single-page application, written in TypeScript and​
T
​built using Vite for fast development-server startup and optimized production bundling. Tailwind​
​CSS is used for utility-first styling, ensuring visual consistency across the six role-based portals​
​without the overhead of maintaining a separate CSS file per component. Routing between portal​
​pages is handled by React Router, and cross-cutting authentication state is managed through a​
​custom​​ AuthContext​​built on React’s Context API, which​​exposes the current user’s identity,​
​role, and JWT token to any descendant component without prop-drilling.​
​Snippet 5.1 — Axios instance with JWT interceptor:​
api​
​ .​
​interceptors​.​
​ request​
.​
​use​
((config)​​
​ =>​​
{​
config​
​ .​
​headers​.​
​Authorization​​=​​
`Bearer ${​
getToken​
​ ()​
​ }`​
​ ;​
​
return​​
​ config​
;​
​
})​
​ ;​
​

​Snippet 5.2 — Protected route wrapper:​

export​​
​ function​​ProtectedRoute​ ({ role​
​ ,​​
​ children }​
:​​
​ Props) {​
const​​
​ { user }​​=​​
useAuth​()​
​ ;​
​
return​​
​ user​
?.​
​ role​​
​ ===​​
role​​?​​
children​​
: <​
Navigate​​
​ to​
=​
​ "/login"​​
/>;​
}​
​

​Snippet 5.3 — Auth context provider (excerpt):​

​onst​​
c AuthContext​​
=​​
createContext​
<​
​ AuthState​​
|​​
null​
>​
​ (​
null​
)​
​;​
export​​
​ const​​
useAuth​​
=​​
()​​
=>​​
useContext​(AuthContext)​
​ !;​
​

​5.2​​Backend Implementation​
​ he backend is implemented in Python 3.10 using the FastAPI framework, chosen for its native​
T
​support for asynchronous request handling, automatic OpenAPI documentation generation, and​
​Pydantic-based request/response validation. The application is organized into a routers package​
​(one router module per functional area — customers, plans, invoices, tickets, inventory,​
​vendors, users), a services package containing business logic, and a models package containing​
​SQLAlchemy ORM class definitions.​
​Snippet 5.4 — FastAPI router endpoint (excerpt):​
​[Link]​
@ (​
​"/"​
, response_model​
​ =​
​CustomerOut)​
def​​
​ create_customer(payload: CustomerCreate, db: Session​​
=​​
Depends(get_db)):​
return​​
​ customer_service.create(db, payload)​

​Snippet 5.5 — Application lifespan and app instantiation:​

​asynccontextmanager​
@
async def​​
​ lifespan(app: FastAPI):​
 yield​​
​ [Link](wait​=​
​True​
)​
​
app​​
​ =​​
FastAPI(lifespan​
=​
​ lifespan)​

​5.3​​Authentication and Authorization​

​ ser authentication is implemented using JSON Web Tokens (JWT). Upon successful login, the​
U
​backend issues a signed access token containing the user’s​​ user_id​​and​​
role​​as claims, along​
​with a short expiry time. Every protected endpoint depends on a reusable FastAPI dependency​
​that decodes and verifies this token, rejecting the request with an HTTP 401 response if the​
​token is missing, expired, or invalid.​
​Snippet 5.6 — JWT verification dependency:​
def​​
​ get_current_user(token:​​str​​
=​​
Depends(oauth2_scheme)):​
payload​​
​ =​​
[Link](token, SECRET_KEY, algorithms​
=​
​ [​
"HS256"​
])​
​
return​​
​ payload​

​Role-based authorization builds on top of this authentication dependency: a second dependency,​

require_role​
​ ​, checks the​​
role​​claim extracted by​​
get_current_user​​against a list of roles​
​permitted to access the given endpoint, returning HTTP 403 Forbidden otherwise.​
​Snippet 5.7 — Role-based permission dependency:​
def​​
​ require_role(​
*​
​ roles):​
def​​
​ checker(user​=​
​Depends(get_current_user)):​
if​​
​ user[​
"role"​
​ ]​​
​ not in​​
roles:​​
raise​​
HTTPException(​
403​
​ )​
​
return​​
​ checker​

​5.4​​SQLAlchemy ORM Layer​

​ QLAlchemy is used in its declarative-mapping style, with each database table (Section 4.2)​
S
​represented by a corresponding Python class. Relationships between tables (e.g., a​​
Customer​
​having many​​ Invoice​​records) are declared using SQLAlchemy’s​​ relationship()​​construct,​
​allowing related objects to be navigated in Python code without hand-written JOIN queries for​
​common access patterns.​
​Snippet 5.8 — SQLAlchemy model definition (excerpt):​
class​​
​ Customer(Base):​
tablename​
​ =​​
​ "customers"​
customer_id​​
​ =​​
Column(Integer, primary_key​
=​
​ True​
)​
​
invoices​​
​ =​​
relationship(​"Invoice"​
​ , back_populates​
​ =​
​"customer"​
)​
​

​Snippet 5.9 — Database session dependency:​

def​​
​ get_db():​
db​​
​ =​​
SessionLocal()​
try​
​ :​​
​ yield​​
db​
finally​
​ : [Link]()​
​
​5.5​​Pydantic Schemas​
​ ydantic models define the shape of request payloads and response bodies for every endpoint,​
P
​decoupling the internal SQLAlchemy model structure from the public API contract and​
​providing automatic input validation, type coercion, and OpenAPI schema generation.​
​Snippet 5.10 — Pydantic schema (excerpt):​
class​​
​ InvoiceCreate(BaseModel):​
customer_id:​​
​ int​
due_date: date​
​

​5.6​​Background Tasks and ThreadPoolExecutor​

​ o satisfy the non-functional requirement that CPU-bound operations (PDF rendering, QR-code​
T
​generation, Excel report export) must not block the FastAPI event loop, these operations are​
​dispatched to a module-level​​ ThreadPoolExecutor​​via​​ loop.run_in_executor​ ​, and the​
​calling async endpoint​​await​ ​s the resulting​​ ​.​​This ensures that a slow PDF-rendering​
Future​
​operation for one customer’s invoice does not delay the response to a concurrently arriving​
​request from another user, which was empirically validated through the load tests presented in​
​Chapter 7.​
​Snippet 5.11 — ThreadPoolExecutor invocation from an async route:​
result​​
​ =​​
await​​
loop.run_in_executor(executor, render_invoice_pdf,​​
invoice_id)​

​Snippet 5.12 — Executor initialization:​

executor​​
​ =​​
ThreadPoolExecutor(max_workers​
=​
​ 8​
)​

​5.7​​AsyncIO Lifespan Management​

​ astAPI’s lifespan context manager is used to manage the lifecycle of shared resources —​
F
​notably the​​ ThreadPoolExecutor​​and the database connection​​pool — ensuring that worker​
​threads are cleanly shut down and in-flight tasks are allowed to complete when the​
​application receives a termination signal, preventing orphaned threads during container​
​restarts.​
​Snippet 5.13 — Graceful executor shutdown (within lifespan):​
finally​
​ :​
​
[Link](wait​
​ =​
​ True​
, cancel_futures​
​ =​
​False​
)​
​

​5.8​​ReportLab PDF Generation​

I​ nvoice PDFs are generated server-side using the ReportLab library, which constructs the PDF​
​document programmatically — rendering the ISP’s letterhead, customer and plan details,​
​itemized charges, and the embedded UPI QR code — entirely within the worker thread​
​described in Section 5.6, without any dependency on a headless browser or external rendering​
​service.​
​Snippet 5.14 — PDF canvas invoice header (excerpt):​
​.drawString(​
c 50​
​ ,​​
​ 800​
,​​
​ f"Invoice #​
{​
​ invoice​
.​
​ invoice_id​
}​
​"​)​
[Link](qr_path,​​
​ 400​,​​
​ 700​
, width​
​ =​
​100​
, height​
​ =​
​100​
)​
​

​5.9​​UPI QR Code Generation​

​ he UPI QR code embedded on each invoice is generated using the​​
T qrcode​​Python library,​
​encoding a standards-compliant UPI deep-link string​
​(​​upi://pay?pa=<VPA>&pn=<payee>&am=<amount>&tr=<invoice_id>​ ​)​​that any UPI-enabled​
​payment application can scan to pre-fill a payment request to the organization’s registered​
​Virtual Payment Address.​
​Snippet 5.15 — UPI QR string construction:​
​pi_url​​
u =​
f"upi://pay?pa=​
​ {​
​ VPA​
}​
​ &pn=CharotarTelelink&am=​
{​
​amt​
}​
​&tr=INV​
{​
​invoice_id​
}​
​ "​
[Link](upi_url).save(qr_path)​
​

​5.10​​Docker and Docker Compose​

​ he complete application stack is containerized using Docker. The backend​​
T Dockerfile​​builds​
​a slim Python 3.10 image, installs dependencies from​​ [Link]​ ​, and runs the​
​application under Uvicorn; the frontend​​ Dockerfile​​performs a multi-stage build, compiling​
​the Vite production bundle in a Node build stage before copying the static output into a​
​lightweight Nginx serving image.​​ [Link]​​orchestrates both services on a shared​
​bridge network, injecting the Neon database connection string via environment variables so that​
​no secrets are baked into the image.​
​Snippet 5.16 — Backend Dockerfile (excerpt):​
​ROM​​
F python:3.10-slim​
RUN​​
​ pip install​​
-r​​
[Link]​
CMD​​
​ [​
"uvicorn"​
,​​
​ "main:app"​
,​​
​ "--host"​,​​
​ "[Link]"​
]​
​

​Snippet 5.17 — docker-compose service definition (excerpt):​

backend​
​ :​
​
build​
​ :​​
​ ./backend​
environment​
​ :​
​
-​​
​ DATABASE_URL=${DATABASE_URL}​

​5.11​​Neon Cloud Database Integration​

​The application connects to Neon’s serverless PostgreSQL offering via a standard​
postgresql://​​connection string supplied through an​​environment variable, with​
​
​SQLAlchemy’s connection pool configured with a conservative​​pool_size​​and​
pool_pre_ping=True​​to gracefully recover from Neon’s​​serverless compute​
​
​auto-suspend/resume behaviour, under which the underlying compute endpoint may briefly​
​scale to zero during idle periods.​
​Snippet 5.18 — SQLAlchemy engine configuration:​
engine​​
​ =​​
create_engine(DATABASE_URL, pool_pre_ping​
=​
​ True​
,​​
​ pool_size​
=​
​ 10​
)​
​

​5.12​​Key REST API Endpoints​

​Table 5.1: Key REST API Endpoints​

​ ethod​ E
M ​ ndpoint​ ​ escription​
D ​ oles Permitted​
R
​POST​ ​/auth/login​ ​Authenticate and issue JWT​ ​All​
​GET​ ​/customers​ ​List customers​ ​Super Admin,​
​Administrator, Accounts​
​POST​ ​/customers​ ​Create customer​ ​Super Admin,​
​Administrator​
​PUT​ ​/customers/{id}​ ​Update customer​ ​Super Admin,​
​Administrator​
​ ET​
G ​/plans​ ​List broadband plans​ ​All authenticated​
​POST​ ​/invoices/generate​ ​Accounts, Super Admin​
​Generate invoice (PDF +​
​QR)​

​GET /invoices/{id}/pdf​ ​Download invoice PDF​ ​ wning customer,​

O
​Accounts,​ ​Super​ ​Admin​
​POST​ ​/payments​ ​Record​​a​​payment​ ​Accounts,​​Super​​Admin​
​POST /tickets​ ​Raise a complaint ticket​ ​Customer, Administrator​
​PUT /tickets/{id}/assign​ ​Assign ticket to engineer​ ​Administrator, Super​
​Admin​
​PUT​ ​/tickets/{id}/status​ ​Update ticket status​ ​Engineer, Administrator​
​GET​ ​/inventory​ ​List inventory items​ ​Engineer, Administrator,​
​Super Admin​
​POST​ ​/vendors/{id}/purchase-orders​ C
​ reate purchase order​ ​Administrator,​
​Super​
​Admin​
​GET​ ​/devices/{id}/status​ ​Get​ ​Engineer, Administrator​
​device online/offline​
​status​

​ ET​
G ​/reports/revenue​ ​ enerate revenue report​
G ​Accounts, Super Admin​
​GET​ ​/dashboard/summary​ ​Get​ ​All authenticated​
​role-scoped dashboard​
​metrics​

​ OST​
P /​users​ ​Create user account​ ​Super Admin​
​GET​ ​/docs​ ​ wagge​
S
​r/Open​
​ PI​
A ​Public (dev environment)​
​interacti​
​ve​
​docume​
​ntation​
​CHAPTER 6: TESTING​
​6.1​​Testing Strategy Overview​
​ esting of the CRM system was carried out across five levels: unit testing of individual functions​
T
​and components in isolation, integration testing of interactions between the API layer and the​
​database layer, system testing of complete end-to-end user workflows, performance testing under​
​simulated concurrent load, and security testing of the authentication and authorization​
​mechanisms.​​Figure 6.1​​depicts the testing pyramid​​followed during this project, with a large​
​base of fast unit tests, a smaller layer of integration tests, and a thin top layer of full end-to-end​
​system tests, supplemented by dedicated performance and security testing passes.​
​Figure 6.1: Testing Pyramid​
​\​
/
/ \​
​ End-to-End / System Tests (few, slow)​
​
/​
​ \​
​
/​
​ \​
​ Integration Tests (API + DB)​
​
/​
​ \​
​
/​
​ \​
​ Unit Tests (many, fast)​
​
/​
​ \​
​

​Backend unit and integration tests were implemented using​​ pytest​​together with FastAPI’s​
TestClient​
​ ​, exercising router endpoints against a​​disposable test database schema. Frontend​
​component tests were implemented using React Testing Library. Performance testing was​
​conducted using a custom concurrent-request load-generation script, discussed in Section 6.5 and​
​reported quantitatively in Chapter 7.​

​6.2​​Unit Testing​
​ nit tests validated individual service-layer functions — invoice amount calculation, JWT token​
U
​generation and verification, password hashing and verification, UPI QR string construction, and​
​inventory quantity decrement logic — in isolation from the database and HTTP layers, using​
​mock objects where a database session was required.​

​6.3​​Integration Testing​
I​ ntegration tests validated the interaction between FastAPI routers, the service layer, and a real​
​(test) PostgreSQL database instance, confirming that API endpoints correctly persisted and​
​retrieved data, enforced foreign-key constraints, and returned the expected HTTP status codes​
​and response schemas for both valid and invalid inputs.​

​6.4​​System Testing​
​ ystem-level tests exercised complete user workflows spanning multiple modules — for​
S
​example, the full path from customer creation, through plan assignment and invoice generation,​
​to payment recording and invoice status transition — verifying that state changes in one​
​module (e.g., a recorded payment) correctly propagated to dependent views (e.g., the​
​customer’s outstanding-dues figure on the dashboard).​
​6.5​​Performance Testing​
​ erformance testing simulated concurrent user load against the invoice-generation and​
P
​dashboard-summary endpoints using a Python​​ asyncio​​-based​​load-generation script issuing​
​between 10 and 200 concurrent requests, comparing response-time and throughput​
​characteristics between the multi-threaded implementation and a synchronous baseline​
​implementation. Detailed results are presented in Chapter 7, Section 7.1.​

​6.6​​Security Testing​
​ ecurity testing verified that: passwords were never stored or transmitted in plaintext; endpoints​
S
​correctly rejected requests bearing an expired, malformed, or missing JWT; role-based​
​authorization correctly blocked a lower-privileged role (e.g., Engineer) from accessing an​
​endpoint reserved for a higher-privileged role (e.g., Accounts); and that SQL injection attempts​
​against text-input fields were neutralized by SQLAlchemy’s parameterized query construction.​

​6.7​​Test Case Log​

​Table 6.1: Unit and Integration Test Cases​

​ est​
T
​ID​ ​ escription​
D I​ nput​ ​ xpected Result​
E ​ ctual Result​
A ​ tatus​
S
​TC-​ ​Valid user​ ​Correct username​ ​HTTP 200 + JWT​ ​HTTP 200 + JWT​ ​Pass​
​01​ ​login​ ​& password​ ​token returned​ ​token returned​
​TC-​ ​Invalid​ ​Correct​ ​HTTP 401​ ​HTTP 401​ ​Pass​
​02​ ​passwor​ ​username, wrong​ ​Unauthorized​ ​Unauthorized​
​d login​ ​password​
​TC-​ ​Access​ ​GET​​/customers,​ ​ TTP 401​
H ​ TTP 401​
H ​Pass​
0​ 3​ ​protected​ ​no​​Authorization​ ​Unauthorized​ ​Unauthorized​
​endpoint​ ​header​
​without​
​token​
​TC-​ ​Access​ ​ ET /customers,​
G ​ TTP 401​
H ​ TTP 401​
H ​Pass​
​04​ ​endpoint​ ​expired JWT​ ​Unauthorized​ ​Unauthorized​
​with expired​
​token​
​TC-​ ​Role-based​ ​ ngineer role​
E ​HTTP 403 Forbidden​ ​HTTP 403 Forbidden​ ​Pass​
​05​ ​access denial​ ​calls POST​
​/users​
​ C-​ C
T ​ reate​ ​Valid​ ​ TTP 201 +​
H ​ TTP 201 +​
H ​Pass​
​06​ ​customer​ ​CustomerCreate​ ​customer record​ ​customer record​
​with​ ​payload​ ​created​ ​created​
​valid data​
​ C-​ C
T ​ reate​ ​ hone number​
P ​ TTP 400 with​
H ​ TTP 400 with​
H ​Pass​
​07​ ​custome​ ​already​ ​duplicate-phone​ ​duplicate-phone​
​r with​ ​registered​ ​error​ ​error​
​duplicat​
​e phone​
​ est​
T
​ID​ D ​ escription​ I​ nput​ ​ xpected Result​
E ​ ctual Result​
A ​ tatus​
S
​TC-​ ​Create​ ​Payload missing​ ​HTTP 422 Validation​ ​HTTP 422 Validation​ ​Pass​
​08​ ​customer​ full_name​
​ ​Error​ ​Error​
​with missing​
​required field​
​TC-​ ​Assign plan​ ​ alid plan_id​
V ​ ustomer record​
C ​ ustomer record​
C ​Pass​
​09​ ​to customer​ ​and​ ​updated with new​ ​updated with new​
​customer_id​ ​plan_id​ ​plan_id​
​ C-​ G
T ​ enerate​ ​Valid​ ​HTTP 201 + invoice​ ​HTTP 201 + invoice​ ​Pass​
​10​ ​invoice for​ ​customer_id​ ​created, PDF​ ​created, PDF​
​active​ ​generated with QR​ ​generated with QR​
​customer​
​TC-​ ​Generate​ ​ eactivated​
D ​ TTP 400 rejection​
H ​ TTP 400 rejection​
H ​Pass​
​11​ ​invoice for​ ​customer_id​ ​with reason​ ​with reason​
​inactive​
​customer​
​TC-​ ​Record full​ a​ mount_paid ==​ I​ nvoice status​ I​ nvoice status​ ​Pass​
​12​ ​payment​ ​[Link]​ ​updated to Paid​ ​updated to Paid​
​against​
​invoice​
​TC-​ ​Record​ a​ mount_paid <​ I​ nvoice status​ I​ nvoice status​ ​Pass​
​13​ ​partial​ ​[Link]​ ​updated to Partially​ ​updated to Partially​
​payment​ ​Paid​ ​Paid​
​against​
​invoice​
​TC-​ ​Overdue​ ​ urrent date >​
C ​ tatus​​auto-flagged​
S ​ tatus​
S ​Pass​
​14​ ​invoice​ ​due_date,​ ​as​​Overdue​​on​​next​ ​auto-flagged​ ​as​
​detection​ ​status still​ ​read​ ​Overdue​ ​on​ ​next​
​Unpaid​ ​read​
​ C-​
T ​Raise​ ​Valid​ ​ TTP 201 + ticket​
H ​HTTP 201 + ticket​ ​Pass​
​15​ ​complaint​ ​TicketCreate​ ​status Open​ ​status Open​
​ticket​ ​payload from​
​customer​
​ C-​
T ​Assign ticket​ ​Valid ticket_id​ ​ icket​ ​status​​updated​
T ​ icket​​status​​updated​ P
T ​ ass​
​16​ ​to engineer​ ​and engineer​ ​to​ ​Assigned,​ ​to​ ​Assigned,​
​user_id​ ​engineer notified​ ​engineer notified​
​ C-​
T ​Close ticket​ ​Attempt to set​ ​HTTP 400 — invalid​ ​HTTP 400 — invalid​ ​Pass​
​17​ ​without​ ​status Closed​ ​state transition​ ​state transition​
​customer​ ​directly from In​
​confirmation​ ​Progress​
​ C-​
T ​Inventory​ ​Issue​ ​1​ ​unit​ ​of​ I​ nventory quantity​ I​ nventory quantity​ ​Pass​
​18​ ​quantity​ ​an​ ​inventory​ ​reduced by 1, status​ ​reduced by 1, status​
​decrement on​ ​item to engineer​ ​set to Issued​ ​set to Issued​
​ est​
T
​ID​ D ​ escription​ ​Input​ ​Expected Result​ ​Actual Result​ ​Status​
​issue​
​TC-​ ​Vendor​ ​ ecord purchase​
R ​ endor​
V ​ endor​
V ​Pass​
​19​ ​outstanding​ ​order + partial​ ​outstanding_balance​ ​outstanding_balance​
​balance​ ​vendor payment​ ​recalculated correctly​ ​recalculated correctly​
​update​
​TC-​ ​SQL​ ' OR​ L
​username =​​ ​ ogin​ ​rejected,​ ​ ogin​ ​rejected,​
L ​Pass​
​20​ ​injection​ '1'='1​
​ ​no​ ​unauthorized​ ​no​ ​unauthorized​
​attempt on​ ​data returned​ ​data returned​
​login field​

​ ll twenty test cases achieved the expected result during the final testing cycle, with​
A
​defects identified during earlier iterations (primarily around ticket state-transition validation​
​and duplicate-phone detection) resolved prior to the final test pass documented above.​
​CHAPTER 7: RESULTS AND DISCUSSION​
​7.1​​Performance Comparison​
​ o evaluate the effectiveness of the hybrid AsyncIO/ThreadPoolExecutor architecture described​
T
​in Chapter 3 and Chapter 5, the invoice-generation endpoint (the most CPU-intensive operation​
​in the system, owing to server-side PDF and QR-code rendering) was benchmarked under​
​increasing levels of concurrent load, and compared against a synchronous baseline​
​implementation in which PDF and QR rendering were executed inline on the request-handling​
​path rather than offloaded to a thread pool.​

​Figure 7.1: Response Time Comparison — Synchronous vs Multi-Threaded​

​Table 7.1: Performance Benchmark Results​

​Sync​
​ oncurrent​
C ​ ync Avg​
S ​ ulti-Threaded​
M ​ hroughput​
T ​ ulti-Threaded​
M
​Requests​ ​Response (ms)​ ​Avg Response (ms)​ ​(req/s)​ ​Throughput (req/s)​
1​ 0​ 1​ 20​ ​95​ ​83​ ​ 02​
1
​25​ ​210​ ​130​ ​79​ ​118​
​50​ ​480​ ​210​ ​68​ ​138​
​100​ ​1150​ ​340​ ​43​ ​172​
​150​ ​2050​ ​480​ ​29​ ​195​
​200​ ​3200​ ​650​ ​20​ ​208​
​Figure 7.2: Throughput Comparison Under Concurrent Load​
​ s shown in Table 7.1 and Figures 7.1–7.2, the synchronous baseline implementation exhibits a​
A
​sharp, near-linear degradation in average response time as concurrency increases, since each​
​PDF-rendering call blocks the single-threaded request-handling path for the duration of the​
​render. The multi-threaded implementation, by contrast, offloads PDF and QR rendering to a​
​bounded thread pool, allowing the AsyncIO event loop to continue accepting and dispatching​
​new requests while rendering proceeds in the background; this yields substantially lower​
​average response times and higher sustained throughput at every tested concurrency level, with​
​the gap widening as concurrency increases — at 200 concurrent requests, the multi-threaded​
​implementation sustains roughly ten times the throughput of the synchronous baseline. These​
​results directly validate the architectural decision, described in Section 3.11 and Section 5.6, to​
​isolate CPU-bound work from the async event loop.​
I​ t should be noted that the thread pool is deliberately bounded (​​
max_workers=8​​in the reference​
​configuration) to avoid oversubscribing the host machine’s CPU cores; beyond this benchmarked​
​range, throughput gains would be expected to plateau as the thread pool itself becomes the​
​bottleneck, at which point horizontal scaling of backend container instances (Figure 3.2) would​
​be the appropriate next scaling lever.​

​7.2​​Advantages Demonstrated​
​ he completed system demonstrates the following advantages over the manual,​
T
​spreadsheet-based process it replaces:​
​•​ ​ illing turnaround​​for a batch of customer invoices​​is reduced from a multi-hour​
B
​manual spreadsheet exercise to an automated background process completing​
​within seconds per invoice, even under concurrent access by multiple accounts​
​staff.​
 ​•​ ​ omplaint visibility​​is improved through the structured​​ticket-status model (Figure​
C
​3.7), giving management a live view of open, in-progress, and overdue complaints that​
​was previously unavailable.​
​•​ ​Inventory reconciliation​​between vendor deliveries​​and field-installed devices is​
​now traceable end-to-end through the linked Vendor → Inventory → Device data​
​model (Chapter 4), closing a visibility gap that previously required manual register​
​cross-referencing.​
​•​ ​Role-appropriate​ ​access​ ​ensures​ ​that​ ​field​ ​engineers,​ ​who​ ​require​ ​inventory​ ​and​
​ticket​ ​visibility,​​are​​not​​exposed​​to​​customer​​billing​​and​​payment​​data,​​addressing​​the​
​access-control gap identified in Section 1.4.​

​7.3​​Dashboard and Portal Screens​

​ he following screen descriptions document the primary interface views implemented for each​
T
​role. Each is described in terms of its purpose, principal components, and the functionality it​
​demonstrates, in place of static screenshots.​
​ creen 7.1 — Super Admin Dashboard​​Purpose:​​Provide​​a system-wide operational overview.​
S
​Components:​​Summary cards (total active customers,​​monthly revenue, open tickets, low-stock​
​alerts), a user-management table, a role-assignment panel.​​Functionality demonstrated:​​FR-12​
​(Dashboard Analytics), FR-14 (User & Role Management).​
​ creen 7.2 — Administrator Portal: Customer Management​​Purpose:​​Manage the subscriber​
S
​lifecycle.​​Components:​​Searchable/filterable customer​​table, “Add Customer” form with​
​plan-assignment dropdown, customer detail drawer showing linked invoices and tickets.​
​Functionality demonstrated:​​FR-03 (Customer Management),​​FR-04 (Plan Management).​
​ creen 7.3 — Accounts Portal: Invoice & Payment Management​​Purpose:​​Generate invoices​
S
​and reconcile payments.​​Components:​​“Generate Invoice”​​action button, invoice list with status​
​badges (Unpaid/Partial/Paid/Overdue), embedded UPI QR preview, payment-recording modal.​
​Functionality demonstrated:​​FR-05 (Invoice Generation),​​FR-06 (UPI QR Invoice), FR-07​
​(Payment Tracking).​
​ creen 7.4 — Engineer Portal: Ticket Queue and Inventory​​Purpose:​​Support​
S
​field-engineering workflows.​​Components:​​Assigned-ticket​​list sorted by priority/age, ticket​
​detail view with status-update control, personal inventory list of issued devices.​​Functionality​
​demonstrated:​​FR-08 (Complaint Ticketing), FR-10 (Inventory​​Management), FR-11 (Device​
​Monitoring).​
​ creen 7.5 — Customer Self-Service Portal​​Purpose:​​Allow subscribers to self-serve​
S
​common requests.​​Components:​​Current plan and connection-status​​widget, invoice history with​
​PDF download, “Raise a Complaint” form, ticket-status tracker.​​Functionality demonstrated:​
​FR-05, FR-08.​
​ creen 7.6 — Vendor Portal​​Purpose:​​Allow vendors​​to view their transaction history with the​
S
​organization.​​Components:​​Purchase-order list, delivery-confirmation​​status, outstanding-balance​
​summary.​​Functionality demonstrated:​​FR-09 (Vendor​​Management).​
​ creen 7.7 — Report Generation Screen​​Purpose:​​Enable​​authorized staff to export​
S
​operational reports.​​Components:​​Report-type selector​​(customer list, revenue summary, ticket​
​summary), date-range filter, PDF/Excel export buttons.​​Functionality demonstrated:​​FR-13​
​(Report Generation).​

​7.4​​Discussion​
​ he results obtained through both functional testing (Chapter 6) and performance​
T
​benchmarking (Section 7.1) indicate that the system satisfies the functional and non-functional​
​requirements defined in Chapter 2. The role-based portal design proved effective in the​
​informal usability review conducted with the organization’s operations staff, who noted that the​
​reduction to only role-relevant functionality on each portal reduced the learning curve​
​compared to a single undifferentiated interface. The most significant technical validation was​
​the performance benefit of the multi-threaded background-task architecture, which directly​
​addresses the “high-performance” objective stated in the project title and Chapter 1 objectives.​

​7.5​​Challenges Encountered​
​ everal challenges were encountered during development. Integrating the ThreadPoolExecutor​
S
​correctly with FastAPI’s AsyncIO event loop required careful attention to avoid deadlocks​
​between the async event loop and blocking database calls made from within worker threads,​
​resolved by ensuring worker-thread database access used its own independently scoped​
​SQLAlchemy session rather than sharing a session across the event loop and thread pool.​
​Designing a ticket state-machine that correctly rejected invalid status transitions (Section 6.7,​
​TC-17) required additional validation logic beyond what a simple status-field update would​
​provide. Coordinating with the industry mentor to obtain representative (non-sensitive)​
​sample data for development, given the confidentiality of real subscriber records, required the​
​use of carefully anonymized and synthetically generated datasets for testing and​
​demonstration.​

​7.6​​Learning Outcomes​
​ his project provided practical, industry-linked experience in full-stack web application​
T
​development using a modern async Python backend and a TypeScript-based React frontend; in​
​relational database design and normalization for a real-world, multi-stakeholder business​
​domain; in the practical application of concurrent and asynchronous programming concepts (the​
​AsyncIO event loop, thread pools, and their interaction) beyond the scope of standard academic​
​coursework; and in requirement elicitation and translation of an industry mentor’s operational​
​pain points into a structured Software Requirement Specification, system architecture, and​
​tested software deliverable.​
​CHAPTER 8: CONCLUSION AND FUTURE SCOPE​
​8.1​​Conclusion​
​ his project set out to design and develop a High-Performance Multi-Threaded Cloud-Based​
T
​CRM system tailored to the operational needs of Internet Service Provider enterprises, using​
​Charotar Telelink Pvt. Ltd. as the industry partner and requirement source. The completed​
​system successfully replaces a fragmented, manual, spreadsheet-based operational process with a​
​centralized, role-aware, cloud-hosted platform spanning twelve functional modules and six​
​distinct user roles. The system’s backend architecture — combining FastAPI’s asynchronous​
​request handling with a bounded ThreadPoolExecutor for CPU-bound background tasks — was​
​shown, through controlled performance benchmarking (Chapter 7), to deliver substantially better​
​response-time and throughput characteristics under concurrent load than an equivalent​
​synchronous implementation, directly validating the project’s “high-performance” design​
​objective. Functional testing (Chapter 6) confirmed that all fifteen functional requirements​
​defined in the Software Requirement Specification were correctly implemented across the​
​twenty executed test cases. The project therefore satisfies its stated objectives (Section 1.6) and​
​delivers a submission-ready, deployable software artifact of direct operational value to the​
​partner organization.​

​8.2​​Achievements​
​•​ ​ uccessfully designed and implemented a normalized, thirteen-table relational​
S
​database schema (Chapter 4) supporting the complete ISP CRM data model.​
​•​ ​Implemented a working, end-to-end role-based access control system across six user​
​roles with distinct portal experiences.​
​•​ ​Implemented automated PDF invoice generation with embedded UPI QR-code​
​payment support.​
​•​ ​Implemented a structured complaint-ticket lifecycle with engineer assignment and​
​SLA-relevant timestamp tracking.​
​•​ ​Empirically demonstrated a measurable performance improvement from the​
​AsyncIO/ThreadPoolExecutor hybrid architecture under simulated concurrent​
​load.​
​•​ ​Containerized the complete application stack using Docker and Docker Compose​
​for reproducible deployment.​
​•​ ​Delivered a fully documented, self-describing REST API via​
​auto-generated OpenAPI/Swagger documentation.​

​8.3​​Limitations​
​ s noted in Section 1.9, the current implementation uses mock SMS and WhatsApp​
A
​notification services rather than a live third-party gateway integration, and the UPI QR invoice​
​feature generates a valid, scannable payment request without processing live payment-gateway​
​settlement callbacks. The system has been benchmarked at a moderate simulated concurrency​
​(up to 200 concurrent requests) appropriate to the current subscriber scale of the partner​
​organization, and has not been evaluated at the scale of a large multi-state ISP. A dedicated​
​offline-capable mobile application for field engineers was outside the scope of the current​
​academic project timeframe.​
​8.4​​Future Scope​
​The following enhancements are identified as future scope beyond the current academic project:​
​1.​ ​ ive SMS/WhatsApp gateway integration​​, replacing the​​current mock notification​
L
​services with a production messaging provider (e.g., an SMS aggregator API or the​
​WhatsApp Business API).​
​2.​ ​Live UPI payment-gateway integration​​with webhook-based​​settlement​
​confirmation, automating the currently semi-manual payment-status update step.​
​3.​ ​Machine-learning-based​ ​churn​ ​prediction​​,​ ​using​ ​historical​ ​payment-delay​
​and​ ​complaint-frequency​ ​patterns​ ​to​ ​proactively​ ​flag​ ​at-risk​ ​subscribers​ ​for​
​retention outreach.​
​4.​ ​Native or progressive-web-app mobile application​​for​​field engineers, with offline data​
​capture and background synchronization to support low-connectivity site visits.​
​5.​ ​GIS-based network-topology visualization​​, mapping​​the Device Monitoring module’s​
​online/offline status data onto a geographic map of the organization’s FTTH splitter​
​zones.​
​6.​ ​Automated SLA breach alerting​​, proactively notifying​​administrators when a​
​ticket approaches or exceeds a configured resolution-time threshold.​
​7.​ ​Multi-tenant support​​, allowing the platform to be​​offered as a shared CRM service​
​to multiple independent regional ISPs, each with logically isolated data.​

​REFERENCES​
[​ 1]​ ​S. Ramanathan and A. Iyer, “A Survey of Customer Relationship Management​
​Systems in Service Industries,”​​International Journal​​of Computer Applications​​, vol. 178, no.​
​12, pp. 1–7, 2023.​
[​ 2]​ ​FastAPI, “FastAPI Documentation,” [Online]. Available:​
​[Link] [Accessed: 2026].​
[​ 3]​ ​S. Ramírez, “FastAPI: Modern, Fast Web Framework for Building APIs with​
​Python,” in​​Proc. PyCon​​, 2019.​
​[4]​​React, “React Documentation,” [Online]. Available:​​[Link] [Accessed: 2026].​
[​ 5]​ ​Meta Open Source, “React 18 Release Notes,” [Online].​
​Available:​​[Link]
​2026].​
[​ 6]​ ​PostgreSQL Global Development Group, “PostgreSQL 16 Documentation,”​
​[Online]. Available:​​[Link] 2026].​
[​ 7]​ ​M. Stonebraker and L. A. Rowe, “The Design of Postgres,” in​​Proc. ACM SIGMOD​
​Int. Conf. Management of Data​​, 1986, pp. 340–355.​
[​ 8]​ ​Neon Inc., “Neon Serverless Postgres Documentation,” [Online].​
​Available:​​[Link] [Accessed: 2026].​
[​ 9]​ ​SQLAlchemy, “SQLAlchemy ORM Documentation,” [Online].​
​Available:​​[Link] [Accessed:​​2026].​
[​ 10]​​M. Bayer, “SQLAlchemy,” in​​The Architecture of Open​​Source Applications​​, A. Brown and​
​G. Wilson, Eds., 2012.​
[​ 11]​ ​Docker Inc., “Docker Documentation,” [Online]. Available:​
​[Link] [Accessed: 2026].​
[​ 12]​ ​C. Boettiger, “An Introduction to Docker for Reproducible Research,”​​ACM​
​SIGOPS Operating Systems Review​​, vol. 49, no. 1, pp.​​71–79, 2015.​
​[13]​​M. Jones, J. Bradley, and N. Sakimura, “JSON Web Token (JWT),” IETF RFC 7519, 2015.​
[​ 14]​ ​Tailwind Labs, “Tailwind CSS Documentation,” [Online].​
​Available:​​[Link] [Accessed:​​2026].​
[​ 15]​ ​ReportLab, “ReportLab User Guide,” [Online]. Available:​
​[Link] 2026].​
[​ 16]​ ​R. Fielding, “Architectural Styles and the Design of Network-based​
​Software Architectures,” Ph.D. dissertation, Univ. of California, Irvine, 2000.​
[​ 17]​ ​R. Fielding and J. Reschke, “Hypertext Transfer Protocol (HTTP/1.1): Semantics​
​and Content,” IETF RFC 7231, 2014.​
[​ 18]​ ​OpenAPI Initiative, “OpenAPI Specification v3.1.0,” [Online].​
​Available:​​[Link] [Accessed:​​2026].​
[​ 19]​ ​P. Mell and T. Grance, “The NIST Definition of Cloud Computing,” NIST​
​Special Publication 800-145, 2011.​
[​ 20]​ ​M. Armbrust et al., “A View of Cloud Computing,”​​Communications​​of the ACM​​, vol.​
​53, no. 4, pp. 50–58, 2010.​
[​ 21]​ ​Telecom Regulatory Authority of India, “Indian Telecom Services Performance​
​Indicators Report,” TRAI, New Delhi, 2025.​
​[22]​​Broadband India Forum, “State of Fibre Broadband in India,” Annual Report, 2024.​
[​ 23]​ ​ITU-T, “Recommendation G.984: Gigabit-capable Passive Optical Networks​
​(GPON),” International Telecommunication Union, 2008.​
​[24]​​FTTH Council, “FTTH Handbook,” 9th ed., FTTH Council Europe, 2022.​
​[25]​​I. Sommerville,​​Software Engineering​​, 10th ed. Boston,​​MA: Pearson, 2015.​
[​ 26]​ ​R. S. Pressman and B. R. Maxim,​​Software Engineering:​​A Practitioner’s Approach​​,​
​9th ed. New York, NY: McGraw-Hill Education, 2019.​
[​ 27]​ ​G. Booch, J. Rumbaugh, and I. Jacobson,​​The Unified​​Modeling Language User Guide​​,​
​2nd ed. Boston, MA: Addison-Wesley, 2005.​
[​ 28]​ ​E. Gamma, R. Helm, R. Johnson, and J. Vlissides,​​Design​​Patterns: Elements of​
​Reusable Object-Oriented Software​​. Boston, MA: Addison-Wesley,​​1994.​
[​ 29]​ ​M. Fowler,​​Patterns of Enterprise Application Architecture​​.​​Boston, MA:​
​Addison-Wesley, 2002.​
[​ 30]​ ​C. J. Date,​​An Introduction to Database Systems​​, 8th​​ed. Boston, MA:​
​Addison-Wesley, 2003.​
[​ 31]​ ​R. Elmasri and S. B. Navathe,​​Fundamentals of Database​​Systems​​, 7th ed. Boston,​
​MA: Pearson, 2015.​
[​ 32]​ ​E. F. Codd, “A Relational Model of Data for Large Shared Data Banks,”​
​Communications of the ACM​​, vol. 13, no. 6, pp. 377–387,​​1970.​
[​ 33]​ ​Python Software Foundation, “Python 3.10 Documentation,” [Online].​
​Available:​​[Link] [Accessed:​​2026].​
[​ 34]​ ​Python Software Foundation, “asyncio — Asynchronous I/O,” [Online].​
​Available:​​[Link] 2026].​
[​ 35]​ ​Python Software Foundation, “[Link] — ThreadPoolExecutor,”​
​[Online]. Available:​​[Link]
​2026].​
[​ 36]​ ​Pydantic, “Pydantic V2 Documentation,” [Online]. Available:​
​[Link] [Accessed: 2026].​
[​ 37]​ ​National Payments Corporation of India, “Unified Payments Interface (UPI)​
​Procedural Guidelines,” NPCI, 2023.​
[​ 38]​ ​National Payments Corporation of India, “UPI Linking Specification for QR Codes,”​
​NPCI Technical Document, 2022.​
​[39]​​Vite, “Vite Documentation,” [Online]. Available:​​[Link] 2026].​
[​ 40]​ ​TypeScript, “TypeScript Handbook,” [Online].​
​Available:​​[Link] [Accessed:​
​2026].​
[​ 41]​ ​React Router, “React Router Documentation,” [Online]. Available:​
​[Link] [Accessed: 2026].​
[​ 42]​ ​Axios, “Axios HTTP Client Documentation,” [Online]. Available:​
​[Link] [Accessed: 2026].​
[​ 43]​ ​N. Provos and D. Mazières, “A Future-Adaptable Password Scheme,” in​​Proc.​
​USENIX Annual Technical Conf.​​, 1999.​
​[44]​​OWASP Foundation, “OWASP Top Ten Web Application Security Risks,” 2021.​
​[45]​​OWASP Foundation, “OWASP API Security Top 10,” 2023.​
[​ 46]​ ​G. Kim, J. Humble, P. Debois, and J. Willis,​​The DevOps Handbook​​, 2nd ed. Portland,​
​OR: IT Revolution Press, 2021.​
​[47]​​S. Newman,​​Building Microservices​​, 2nd ed. Sebastopol,​​CA: O’Reilly Media, 2021.​
​[48]​​J. Nielsen,​​Usability Engineering​​. San Francisco,​​CA: Morgan Kaufmann, 1993.​
[​ 49]​ ​K. Beck et al., “Manifesto for Agile Software Development,” 2001. [Online].​
​Available:​​[Link]
[​ 50]​ ​IEEE, “IEEE Recommended Practice for Software Requirements Specifications,” IEEE​
​Std 830-1998, 1998.​
​APPENDIX​
​A.1​​API Documentation​
​ he complete REST API is self-documented through FastAPI’s automatic OpenAPI schema​
T
​generation, exposed interactively at the​​/docs​​(Swagger​​UI) and​​/redoc​​(ReDoc) endpoints​
​when the backend is running in a development environment. Every endpoint listed in Table 5.1​
​is documented with its expected request schema, response schema, and possible HTTP status​
​codes, generated directly from the Pydantic models and FastAPI route decorators without any​
​separately maintained documentation artifact, ensuring the documentation cannot drift out of​
​sync with the implementation.​

​A.2​​Environment Variables​
​Table A.1: Application Environment Variables​

​ ariable​
V ​ urpose​
P ​Example​
​DATABASE_URL​ ​Neon​ ​ ostgresql://user:pass@[Link]​
p
​PostgreSQ​ ​[Link]/crmdb​
​L​
​connection​
​string​

​ ECRET_KEY​
S J​ WT signing secret​ (​ 32+ character random​
​string) JWT_ALGORITHM​ ​JWT signing algorithm​ ​HS256​
​JWT_EXPIRY_MINUTES​ ​Access token​ ​60​
​validity​
p​ eriod​ ​charotartelelink@upi​
​UPI_VPA​ ​Organization’s​
​UPI Virtual​
​Payment​
​Address​

​ ORS_ORIGINS​
C ​ llowed frontend origins​ ​[Link]
A
​THREAD_POOL_WORKERS​ build​
​ :​
​
./backend​
​
​ThreadPoolExecutor​ ports​
​ :​
​
​worker count​ [​
​"8000:8000"​ ]​
​
​ENV​ ​ eployment​
D environment​
​ :​
​
​environment​ -​​
​ DATABASE_URL=${DATABASE_URL}​
​flag​ -​​
​ SECRET_KEY=${SECRET_KEY}​
restart​
​ :​​
​ unless-stopped​
​A.3​​Docker Compose Configuration​ frontend​
​ :​
​
​ersion​
v :​​
​ "3.9"​ build​
​ :​
​
services​
​ :​
​ ./frontend​
​
backend​
​ :​
​ ports​
​ :​
​
​​
["80:80"​]​
​ ​8​
depends_on​
​ :​
​
[​
​backend​]​
​
​production​
restart​
​ :​
​
unless-stopped​
​
networks​
​ :​
​
default​
​ :​
​
name​
​ :​​
​ crm-net​

​A.4​​Swagger / OpenAPI Access​

​ he interactive Swagger UI is accessible at​​
T /docs​​and provides a browsable, testable interface​
​to every registered API route, including inline “Try it out” request execution authenticated via a​
​bearer token pasted into the Swagger authorization dialog. The raw machine-readable OpenAPI​
​3.1 schema is available at​​
/[Link]​​for use by​​API client-generation tooling.​

​A.5​​Configuration Summary​
​Table A.2: Deployment Configuration Summary​

​ omponent​
C ​ onfiguration​
C
​Backend Runtime​ ​Python 3.10, Uvicorn ASGI server​
​Frontend Build​ ​Vite production build served via​
​Nginx​
​Database​ ​ eon Serverless PostgreSQL (managed)​
N
​Container Orchestration​​Docker Compose (single-host); Kubernetes-ready for future scale-​
​out​
​Authentication​ ​JWT (HS256), 60-minute access token expiry​
​Background Task Execution​ ​ThreadPoolExecutor, 8 workers​
​(configurable)​

​A.6​​Sample Invoice — Structure​

​ ach generated invoice PDF follows a standardized layout: organization letterhead and GSTIN​
E
​(if applicable) at the header; customer name, address, and account number; billing period and​
​itemized plan charges; total amount due and due date; an embedded UPI QR code encoding a​
​pre-filled payment request to the organization’s registered VPA; and a footer containing the​
​invoice’s unique reference number and a machine-readable barcode of that reference number for​
​filing purposes.​