Skip to main content
Pharmacy Management System
Desktop ApplicationProduction

Building an Offline-First Pharmacy Management System

Full-Stack Engineer
4 months
ElectronReactNext.jsPrismaSQLiteTypeScriptTailwind CSSNode.jsTanStack Query
Executive Summary

Eliminated the risk of data loss and operational downtime for independent pharmacies in unstable network environments by building a high-performance, offline-first desktop application. The system manages inventory, billing, and customer relationships with zero dependency on the internet, ensuring 100% uptime for critical pharmacy operations.

By implementing a peer-to-peer LAN synchronization engine, the platform allows multiple pharmacy stations to share a single source of truth without requiring a dedicated server or stable cloud connection. This architecture reduces daily reconciliation time from 1 hour to under 5 minutes, enabling pharmacists to focus on patient care rather than manual data entry.

Designed for the specific constraints of the Pakistani pharmaceutical market, the application balances the power of a modern tech stack (Electron, React, SQLite) with a UX that matches the speed and muscle memory of traditional paper-based workflows.

Business Problem

Independent pharmacies in developing markets operate in high-pressure environments with unreliable internet and frequent power outages. For these businesses, cloud-dependency is a liability: if the internet goes down, billing stops, and the pharmacy loses revenue.

Existing software falls into two failed categories: expensive enterprise ERPs that are priced out of reach, or cloud-based SaaS that becomes unusable during outages. This forced pharmacies to rely on hybrid systems—part digital, part paper—leading to massive data fragmentation and reconciliation headaches at the end of every day.

The 'Data Island' problem was the primary bottleneck. With 2-4 computers per pharmacy (billing, inventory, back office), each machine often became an island of data. Without a shared local database, staff were forced into duplicate data entry, increasing the risk of human error and inventory leakage.

The core challenge was delivering enterprise-grade data integrity and operational efficiency—including ACID-compliant transactions and sub-second search—without requiring internet connectivity, dedicated IT staff, or expensive server hardware.

Goals

Business Goals

  • Provide a complete pharmacy management solution at an affordable one-time license fee (no recurring SaaS costs)
  • Eliminate revenue leakage from misplaced inventory, expired stock, and manual billing errors
  • Reduce daily reconciliation time from 1 hour to under 5 minutes
  • Enable pharmacies to make data-driven restocking decisions based on sales trends

Technical Goals

  • Build an offline-first application that functions fully without internet connectivity
  • Implement LAN-based synchronization with conflict resolution for multi-computer setups
  • Achieve sub-100ms response times for billing and inventory search operations
  • Support up to 10,000 SKUs without degradation in search or billing performance
  • Ensure data integrity through ACID-compliant transactions on the local database

User Goals

  • Pharmacists need to look up medicines by name, category, or barcode in under 2 seconds
  • Cashiers need to complete a billing transaction in under 30 seconds, including printing a receipt
  • Inventory managers need real-time stock levels with automated low-stock and expiry alerts
  • Owners need daily, weekly, and monthly sales reports without manual data compilation
Requirements

Functional

Medicine inventory management with batch number and expiry date tracking
Billing system with barcode scanning, prescription validation, and receipt printing
Customer management with purchase history, loyalty points, and credit tracking
Supplier management with purchase orders, stock receipt, and payment tracking
Sales reporting with filters by date, product, category, and cashier
Expiry management with automated alerts for medicines expiring within configurable thresholds
Low stock alerts with reorder level configuration per product
User management with role-based access (owner, pharmacist, cashier)
Data export for tax reporting and accounting software integration
Multi-computer LAN synchronization with conflict resolution

Non‑Functional

Performance

  • • Medicine search returns results in under 500ms for 10,000 SKUs
  • • Billing transaction (item scan → payment → receipt) completes in under 30 seconds
  • • Daily sales report generates in under 2 seconds for 500+ transactions
  • • Application cold start time under 3 seconds on standard hardware

Security

  • • Local database encryption using SQLCipher or similar technology
  • • User authentication with bcrypt password hashing
  • • Session timeout after 30 minutes of inactivity
  • • Audit logging for all inventory adjustments and financial transactions
  • • Automatic database backup on application close

Scalability

  • • Support for up to 5 concurrent users on the local network
  • • Sync engine handles up to 10,000 new records per day across all connected machines
  • • Database size of up to 500MB without performance degradation

Maintainability

  • • Auto-update mechanism for seamless application updates
  • • Centralized error logging with opt-in crash reporting
  • • Database migration system for schema updates across versions
Architecture
The application follows an offline-first architecture where each computer runs a complete local instance with its own SQLite database, application logic, and UI. A synchronization layer manages data exchange between computers on the local network using a peer-to-peer model with conflict resolution. The Electron shell provides the desktop runtime, React handles the UI, and Prisma ORM manages the local SQLite database.

System Flow

  1. 1On application startup, Electron launches the React UI and initializes the local SQLite database through Prisma. If this is the first run, the database schema is created and seeded with default configuration.
  2. 2All operations - billing, inventory management, customer lookup - read from and write to the local SQLite database directly. There is no network dependency for core functionality. Every transaction is ACID-compliant through SQLite's transaction support.
  3. 3When a transaction is completed locally, it is recorded in a sync queue table with a timestamp, machine ID, and sync status. The sync engine runs as a background process, periodically checking for new records to synchronize.
  4. 4The sync engine discovers other instances on the local network via UDP broadcast. Once peers are discovered, it establishes TCP connections and exchanges pending changes using a last-writer-wins conflict resolution strategy based on timestamps.
  5. 5Each machine maintains a full copy of the data. Synchronization is additive - records are never deleted during sync, only added or updated. Deletion is handled through soft-delete flags that propagate during sync.

Request Flow

  1. 1A cashier starts a new bill by scanning a medicine's barcode. The React frontend sends a search query to the Electron main process via IPC. The main process queries the local SQLite database through Prisma, filtering by barcode or name.
  2. 2Search results are returned to the renderer process within milliseconds. The cashier selects the item, enters the quantity, and the frontend calculates the line total with applicable taxes and discounts.
  3. 3When the customer pays, the cashier finalizes the bill. The frontend sends the complete transaction data to the main process, which writes it to the SQLite database within a transaction: inserting the transaction header, line items, and updating inventory levels atomically.
  4. 4After the database write succeeds, the main process sends the receipt data to a printer via Electron's printer API. Simultaneously, the sync engine adds the transaction to the outgoing sync queue.
  5. 5The sync engine processes the queue every 30 seconds, sending pending changes to connected peers on the LAN. Each peer applies incoming changes to its local database, resolving any conflicts using the last-writer-wins strategy.

Deployment Flow

  1. 1The application is packaged as an Electron executable using electron-builder. The installer includes the Node.js runtime, React bundle, Prisma client, and SQLite native bindings - all bundled into a single `.exe` or `.dmg` file.
  2. 2Installation requires no administrator privileges and no separate database setup. The application creates its data directory on first run and initializes the SQLite database automatically.
  3. 3Updates are delivered through an auto-update mechanism. The application checks for updates on startup by querying a lightweight update server. If a new version is available, it downloads the update in the background and prompts the user to install on next restart.
  4. 4Database backups are created automatically on every application close. The backup file is timestamped and stored in a separate backup directory. The application retains the last 30 backups and rotates older ones.
  5. 5For the rare case of database corruption, the application includes a recovery tool that can rebuild the database from the last valid backup and the sync queues of other machines on the network.

Database Design

  • The database uses SQLite, which is embedded within the Electron application. Each machine has its own SQLite file stored in the user's application data directory. SQLite was chosen specifically because it requires no separate database server process.
  • The schema is organized around core pharmacy domains: Products (medicines with batch, expiry, pricing), Inventory (stock levels per location), Transactions (billing records with line items), Customers (with purchase history), Suppliers (with purchase orders), and Users.
  • Each table includes a `machineId` and `updatedAt` column for synchronization. The composite key `(localId, machineId)` uniquely identifies every record across all machines, preventing primary key conflicts during sync.
  • Full-text search indexes are created on product names, categories, and manufacturer fields. SQLite's FTS5 extension provides sub-millisecond search performance even on 10,000+ SKUs.
  • The sync queue table stores outgoing changes: each row contains the affected table name, record ID, operation type (INSERT/UPDATE/DELETE), timestamp, and a serialized snapshot of the changed data. This queue acts as a write-ahead log for synchronization.

Architectural Decisions

Electron vs. Tauri vs. React Native Desktop

I evaluated Tauri for its smaller bundle size, but chose Electron due to its superior ecosystem for raw ESC/POS printer integration—a non-negotiable requirement for pharmacy receipts. While the bundle size is larger (≈150MB), the ability to interface directly with native printer drivers and barcode scanners without unstable wrappers was the primary driver.

SQLite vs. PostgreSQL vs. local JSON storage

SQLite was the only viable choice for an offline-first application. It provided ACID compliance without requiring a separate server process, ensuring that if power is lost mid-transaction, the database remains consistent. I chose SQLite over JSON storage to leverage SQL's powerful aggregation for daily sales reports and inventory audits.

Peer-to-peer sync vs. central server sync

I implemented a P2P LAN synchronization model because pharmacies lack dedicated server hardware. By allowing any machine to initiate sync via UDP discovery, I removed the single point of failure inherent in server-client models. The trade-off was a more complex conflict resolution logic, which I solved using a timestamp-based last-writer-wins strategy.

Prisma with SQLite vs. Drizzle ORM

Prisma was selected for its type-safe query builder and robust migration system, which is critical when updating databases on remote client machines. While Drizzle offers a smaller footprint, the safety provided by Prisma's schema validation ensured that database updates never corrupted client data during the deployment phase.

IPC communication pattern in Electron

I adopted a request-response IPC pattern for CRUD operations and a streaming pattern for real-time sync events. By treating the main process as the exclusive database gateway, I created a strict security boundary that prevents the renderer process from directly accessing the filesystem, simplifying the overall security audit.

Technology Decisions
Electron

Electron provides the desktop runtime necessary for local file system access (SQLite database), printer integration (receipt printing), and native OS integration (system tray, auto-start). Its Chromium-based renderer ensures consistent CSS and JavaScript behavior across Windows and macOS, eliminating browser compatibility concerns that would plague a web-based solution.

Trade-off

Electron's memory usage (150-300MB idle) is high for a pharmacy management application. On older pharmacy computers with 2GB RAM, this is noticeable. Tauri would use significantly less memory but lacks the printer and barcode ecosystem maturity.
SQLite

SQLite is embedded directly in the application - no database server to install, configure, or maintain. A pharmacy installs one application and everything works. SQLite's ACID compliance ensures that even if the power goes out during a billing transaction, the database remains consistent. Zero configuration was the deciding factor.

Trade-off

SQLite does not support concurrent writes well (≈50 concurrent writers max). For a multi-location pharmacy chain, this would be a scaling bottleneck. The application also needs careful management of database file size - without VACUUM and proper cleanup, the database can grow to multiple GB over years of use.
React

React provides the component model needed for the complex, stateful UI of a pharmacy application - where multiple panels (billing, inventory search, customer lookup) are simultaneously visible and interactive. React's unidirectional data flow makes it predictable to reason about the application state as the user moves between billing, inventory, and reporting.

Trade-off

React's bundle size and runtime overhead are noticeable on low-end hardware compared to lighter alternatives like Svelte or Preact. For a desktop application where initial load time matters, Svelte would have provided a faster first-paint experience.
Prisma

Prisma provides type-safe database access that catches schema mismatches at compile time. In a desktop application where database migrations must be carefully managed (no rollback is possible once deployed to a user's machine), Prisma's migration system provides the safety net needed for confident updates.

Trade-off

Prisma adds approximately 15MB to the Electron application bundle, much of which is the Prisma engine binary. For a desktop application that users download once, this is a one-time cost, but it does increase download time and disk usage.
Features

Barcode-Based Billing

Problem

Pharmacies dispense hundreds of medicines daily. Manual entry of medicine names is slow, error-prone, and impractical during rush hours. Cashiers need to process a customer in under 60 seconds including payment and receipt.

Solution

The billing module supports barcode scanning using USB barcode scanners (which appear as HID keyboards). Scanning a medicine immediately looks up the product by barcode, retrieves the current selling price, checks stock availability, and adds it to the bill. The cashier enters quantity and the system auto-calculates totals, taxes, and discounts.

Engineering Challenges

Medicine barcodes in Pakistan are not standardized. Some medicines use GTIN-12, others use GTIN-13, and some use custom pharmacy-assigned codes. We implemented a flexible barcode matching system that normalizes input codes by stripping non-numeric characters and matching against multiple barcode formats stored per product. Pharmacies can also print their own barcode labels for products without manufacturer barcodes.

Expiry Date Management

Problem

Expired medicine is a significant financial loss for pharmacies. Manual expiry tracking is impractical for thousands of SKUs. Pharmacies need to know which medicines are approaching expiry so they can return them to suppliers, offer discounts, or stop ordering.

Solution

The system tracks expiry dates at the batch level. When receiving stock from a supplier, the pharmacist enters the batch number and expiry date. The system automatically generates alerts for medicines expiring within configurable thresholds (60/30/7 days). Expired stock is automatically quarantined in the system and flagged in red during inventory lookups.

Engineering Challenges

The most complex challenge was handling batch-level inventory. The same medicine arriving on different dates has different batches with different expiry dates. When a cashier sells a medicine, which batch should be dispatched? We implemented a FIFO (First-In-First-Out) picking algorithm that automatically selects the batch closest to expiry for each sale, minimizing the risk of expiry losses.

Challenges

Synchronization conflict resolution

With multiple computers operating independently, conflicts are inevitable. Two cashiers might adjust the stock of the same medicine simultaneously on different machines. We implemented a timestamp-based last-writer-wins strategy: each record carries a `updatedAt` timestamp and the sync engine compares timestamps when merging changes. For inventory count conflicts (both machines adjust stock of the same item), we use a merge strategy that takes the delta approach: instead of syncing the absolute stock count, we sync inventory adjustment transactions and replay them on each machine.

Trade-off

Last-writer-wins can silently overwrite valid data if clock skew exists between machines. We mitigate this by syncing system time via NTP on application startup and displaying a warning if clock skew exceeds 30 seconds. The delta approach for inventory prevents lost updates but can result in negative stock counts if reconciliation is delayed.

Printer compatibility across Windows versions

Receipt printing in pharmacies is notoriously inconsistent. Different pharmacies use different printers (thermal receipt printers, dot matrix, laser), each with unique driver requirements. Electron's built-in printing API does not handle receipt printers well because they require raw ESC/POS commands rather than standard paper formatting. We implemented a dual printing approach: for standard printers, we use Electron's webContents.print() with a formatted HTML template. For thermal receipt printers, we generate ESC/POS commands directly and send them to the printer port using a Node.js native addon.

Trade-off

ESC/POS direct printing requires a native Node.js addon that must be compiled for each platform. This added significant complexity to the build pipeline. We also had to maintain a database of printer configurations for different thermal printer models, which is difficult to keep comprehensive.

Performance of full-text search on large datasets

Barcode scanning requires instant results. SQLite's LIKE-based search on medicine names was taking 2-3 seconds on 8,000+ SKUs - unacceptable for a fast-paced billing environment. We implemented SQLite FTS5 full-text search indexes on product names, categories, and manufacturer fields. This reduced search latency from 2 seconds to under 50ms. The FTS index is rebuilt incrementally on each product insert/update to keep it current without expensive full rebuilds.

Trade-off

FTS indexes increase database size by approximately 30% and add latency to write operations (each product insert triggers an index update). For a read-heavy workload like pharmacy billing, this trade-off is strongly in favor of read performance. The incremental index rebuild means there is a brief window where newly added products are not searchable via FTS - we fall back to LIKE queries during this window.
Performance
  • ▸SQLite FTS5 full-text search indexes reduced medicine lookup times from 2+ seconds to under 50ms for 10,000 SKU databases.
  • ▸The React UI uses virtualized lists (TanStack Virtual) for inventory browsing, rendering only visible rows. This reduced DOM nodes from 10,000+ to approximately 20, improving scroll performance dramatically.
  • ▸Database transactions are batched during sync operations. Instead of inserting 1,000 records in 1,000 separate transactions (which would take minutes), the sync engine batches records in groups of 100 within a single transaction. This reduced sync time by 20x.
  • ▸Receipt generation uses a pre-compiled HTML template with Handlebars. Template compilation happens once on application startup, not per receipt. Receipt rendering takes under 100ms after the first print.
  • ▸The Electron main process runs database operations on a worker thread using Node.js worker_threads, preventing database-heavy operations from blocking the UI thread.
Security
  • ▸The SQLite database file is encrypted using SQLCipher, a SQLite extension that provides 256-bit AES encryption of the entire database file. The encryption key is derived from the user's login password using PBKDF2.
  • ▸User passwords are hashed using bcrypt with a cost factor of 12 before storage. Passwords are never stored in plaintext or transmitted over the network.
  • ▸The application enforces session timeouts: after 30 minutes of inactivity, the user is automatically logged out and the UI returns to the login screen.
  • ▸All financial transactions are immutable - once recorded, a billing transaction cannot be deleted or modified. Corrections must be made through void/refund transactions that create an audit trail.
  • ▸Database backups are encrypted using the same SQLCipher key before being written to disk.
  • ▸Network communication during LAN sync is encrypted using TLS 1.3 with self-signed certificates generated per machine.
Lessons Learned
  • 1Offline-first development requires a fundamentally different testing approach. We had to simulate network partitions, clock skew, and concurrent writes - scenarios that are edge cases in web applications but core behavior in offline-first systems. Property-based testing with fast-check was invaluable for finding sync bugs.
  • 2Printer integration remains the most fragile part of the application. Despite our best efforts, printer compatibility varies across Windows versions, printer drivers, and USB configurations. For future desktop projects, I would evaluate cloud-printing solutions or dedicated receipt printer hardware partnerships.
  • 3The delta-based sync approach for inventory was the right architectural decision, but its complexity was underestimated. The inventory adjustment log became a source of truth that was harder to debug than expected. Better tooling for inspecting and replaying the adjustment log would have saved debugging time.
  • 4SQLite is remarkably capable for a desktop application. The database size after 6 months of real usage in a pilot pharmacy was only 120MB for 50,000+ transactions and 8,000 products. SQLite performance degrades gracefully as the database grows.
  • 5Pharmacists and cashiers have very specific workflow expectations. The billing screen layout, keyboard shortcuts, and print behavior must match their muscle memory from years of using legacy systems. User testing with real pharmacists during development was essential for adoption.
Future Improvements

Roadmap

These improvements are prioritized based on user feedback and business impact.
  • 1Web-based reporting dashboard that aggregates data from multiple pharmacy installations. With owner consent, the sync engine can push anonymized data to a cloud endpoint for generating comparative analytics across pharmacy locations.
  • 2Integration with the Drugs Regulatory Authority of Pakistan (DRAP) database for automatic medicine verification and regulatory compliance checks.
  • 3Supplier portal where pharmaceutical distributors can submit digital invoices and receive automatic stock receipt confirmation.
  • 4Mobile companion app for pharmacists to receive low-stock alerts, view daily sales summaries, and approve purchase orders remotely.
  • 5AI-powered demand forecasting that analyzes historical sales data to predict future stock requirements, reducing both stockouts and excess inventory.
  • 6Enhanced barcode support for scanning prescription QR codes (common in modern healthcare systems) and automatically populating patient and prescription details.

Want to discuss this project?

I am always happy to talk about engineering decisions, architecture, or how I can help with your next project.

Chat on WhatsApp