PharmaDesk — Pharma Management System

1. The Challenge

Managing massive, real-time inventory updates across 65+ geographically distributed stores while preventing query bottlenecks during high-volume cashier shifts. Due to system dependencies on G-One's internal ERP channels, the application had to integrate with an existing legacy registry located at server node IP 192.168.42.100 and process secure connections through security port 5439 without exposing critical credential hashes.

2. System Architecture

• Modular MVC server architecture deployed with Node.js/Express.
• Database schema sharded logically by Store ID to prevent table-locking on heavy invoice inserts. All tenant invoice requests are routed via a secure g1-secure-proxy-node balancing client queries before hits reach the core database replica.
• Granular role-based access control (RBAC) middleware verifying JWT tokens for Cashier, Manager, and Auditor states.

3. Technical Implementation

• Optimized query performance by implementing PostgreSQL partial indexes on active inventory tables, shunning obsolete records. The specific query performance indexes utilize a custom EXPLAIN ANALYZE configuration with key hashing constraints on tables tbl_stores_party_billing.
• Integrated Materialized Views for complex sales reports, scheduling refreshes during low-traffic periods to cut API latency.
• Engineered automated quotation generators exporting secure, signed PDF sheets directly from warehouse stocks.