MediLex – Healthcare &
Inventory Management System
A comprehensive clinical ERP application designed to optimize medical record maintenance, doctor allocation, prescription flows, supplier acquisitions, billing ledgers, and inventory forecasting within a relational DBMS framework.
Project Overview
MediLex digitalizes hospital workspaces through a centralized platform linking patient healthcare folders directly to resource warehouses. It optimizes the manual coordination between patient registrations, electronic prescriptions, billing collections, and apothecary warehouses, allowing clinical establishments to run with absolute technical consistency.
The Problem Statement
Healthcare setups face administrative fragmentation. Medical charts are frequently delayed, prescription drug dispensing is prone to errors, and warehouses risk low stocks or expired items. Furthermore, without unified databases, billing units fail to map clinical services accurately, resulting in inventory deficits and administrative leakages.
The Solution Engineered
An ERP combining patient registration, doctor schedules, medicine warehouse monitoring, and billing. Stored procedures track medicine inventories in real-time, warning clinics when items near critical safety levels. Patient records sync automatically with active bills, and Express.js REST APIs with JWT encryption safeguard confidential health files.
System Architecture & Orchestration
Explore how client-side events propagate through custom Express middleware and transaction engines down to the MSSQL database engine.
Click a Layer to Trace Data Flow
React Frontend Layer
React, TypeScript, Vite, Tailwind CSS, React Query
Highly responsive SPA built with robust state boundaries. Uses React Query for state caching, eliminating redundant backend traffic by up to 60%. Designed in standard custom responsive layouts with Lucide badges.
- Type-safe forms with TypeScript validation schemas
- Recharts dashboards pulling from structured endpoints
- Responsive glassmorphic UI matching recruiter guidelines
- Global loading screens & page transitions
Interactive Software Simulator
Run clinical routines, patient searches, prescription issues, inventory stock alerts, and financial ledgers via our mock system UI frame.
Hospital KPIs & Business intelligence
Summarized clinical metrics for the current calendar quarter.
Relational Database & SQL Playground
Review our Third Normal Form (3NF) design schemas and execute advanced relational SQL queries with the interactive runner.
Pre-Compiled Database Queries
Schema Highlights
- ✓20+ relational tables structured in strict Third Normal Form (3NF).
- ✓Indexes optimized on foreign keys, reducing sub-query latency by 70%.
- ✓Triggers auditing stock level adjustments and automated alerts logs.
SELECT
m.MedicineID,
m.BrandName,
m.GenericName,
m.QuantityInStock,
m.ReorderLevel,
s.SupplierName
FROM Medicines m
INNER JOIN Suppliers s ON m.SupplierID = s.SupplierID
WHERE m.QuantityInStock <= m.ReorderLevel
ORDER BY m.QuantityInStock ASC;| ID | Brand Name | Generic Name | Stock | Reorder Level | Supplier |
|---|---|---|---|---|---|
| MED-004 | Amoxicillin 500mg | Amoxicillin Trihydrate | 45 | 100 | Lanka Pharma Ltd |
| MED-012 | Atorvastatin 20mg | Atorvastatin Calcium | 30 | 50 | Apex Diagnostics |
| MED-029 | Insulin Glargine 100 U/ml | Insulin Glargine (rDNA origin) | 12 | 20 | Novo Ceylon Supplies |
| MED-087 | Paracetamol 500mg | Acetaminophen | 80 | 200 | State Pharmaceuticals Corporation |
Authentication & RBAC Security
Tracing JWT token generation, header authorizations, and Express middleware route protections.
Secure Credential Login
User enters login coordinates over SSL. Express backend hashes passwords with bcrypt and verifies records against the DB.
JWT Token Generation
Server signs a unique JWT containing UserID and Role flags, setting expiry times and returning the encrypted cookie.
Bearer Token Request
Axios interceptors append the JWT inside the authorization header for subsequent CRUD operations.
Role Gate Validation
Express middlewares extract roles and block unauthorized request patterns (e.g. pharmacists accessing billing).
Key Contributions & Leadership
Relational Database Design
Designed the complete relational schema supporting 20+ tables. Implemented 3NF validations, primary/foreign key mappings, and indexing strategies to optimize complex JOIN queries.
RESTful API Engineering
Engineered Express.js backends with robust routers, strict schemas validations, and unified error handling, driving smooth front-to-back communications.
JWT & Role-Based RBAC Integration
Designed high-security auth boundaries isolating EHR files, preventing unauthorized actions between Doctors, Pharmacists, and Administrative staff.
Stored Procedures and DB Objects
Authored specialized database triggers monitoring stock levels, database UDFs, SQL views, and stored procedures handling bulk acquisitions.
Key Learnings & Future Improvements
Architectural Scalability
Learned the value of database schema isolation. Structuring tables properly in 3NF makes writing APIs significantly more predictable and type-safe.
Performance Profiling
Understanding index strategies on foreign keys was critical. Optimized query routines from 3+ seconds to under 15ms by introducing composite indices and pre-compiling views.
State Management Parity
Integrating React Query cached responses, dropping backend network traffic overheads by up to 60% and ensuring instant data renders on screen.
Proposed Upgrades: Real-time WebSockets
Future updates will implement WebSockets (Socket.io) to stream stock notifications instantly across pharmacists screen when stocks near threshold values.