Containerised Billing & Inventory Platform with Backups and DR for a Pharmacy Chain (Docker, PHP/MySQL)

ArogyaOps is a fifth-semester BCA project (BCU SEP 2024, slot 24BCAPJ, team of 3) that puts a small two-branch pharmacy's billing and inventory platform — ArogyaPOS — through everything that happens after the app is written: containerisation, deployment, monitoring, backup, disaster recovery and cost tracking. Most student projects die the day the demo laptop is switched off; this one is built to survive it.

The billing application itself is deliberately modest — PHP and MySQL, written in the Web Programming course's stack — so the project's real weight falls on the operations layer that the Cloud Computing course (24BCA62) is about. The whole stack (Nginx, PHP-FPM, MySQL) runs as a Docker Compose application on a free-tier cloud virtual machine (Oracle Cloud Always-Free, or any free-tier provider), configured entirely from one .env file and a version-controlled docker-compose.yml.

Around it, the team operates real reliability practice on student hardware: Prometheus and Grafana dashboards with alert rules for downtime and disk pressure (industry-standard extras, clearly labelled), nightly mysqldump backups that are compressed, checksummed and synced off-site with rclone, a written DR runbook proven by timed restore drills, and a cost worksheet that accounts for every rupee of running the service — free tier, egress, storage — month by month.

The viva story is concrete: here is the app, here is the graph showing last Tuesday's outage, here is the 14-minute restore that recovered it, and here is the bill: ₹0.

Small Indian businesses — pharmacies among the most demanding, because billing cannot pause — typically run billing software on individual PCs with no central view of stock, no backup that has ever been tested, no monitoring, and no idea of what their setup would cost to run properly in the cloud. When a disk fails or a PC is stolen, sales history and GST records go with it; when two branches work separately, the owner's stock decisions are guesses. Moving to paid SaaS solves availability but costs ₹500–2,000 every month and places data outside the owner's control.

What is missing is a demonstration, at student scale, that a small organisation can run its own service reliably: one application serving both branches from a central database, deployed as code, watched by real monitoring, backed up nightly with restores that are actually rehearsed and timed, and priced honestly — using only free tiers and open-source tools.

Abstract

ArogyaOps delivers ArogyaPOS, a PHP/MySQL billing and inventory platform for a fictional two-branch pharmacy (Arogya Medicals, Hubballi), not as a laptop demo but as an operated service: three containers behind Docker Compose on a free-tier cloud VM, Prometheus/Grafana monitoring with alert rules, nightly verified backups synced off-site, a disaster-recovery runbook validated by timed drills, and a monthly cost worksheet. Reliability, recoverability and cost are first-class deliverables alongside the application.

Introduction

A small pharmacy's billing system is invisible until it fails — at 9 p.m., when GST invoices must print and the counter is queued. Student projects answer 'does the app work?', but a real shop must answer 'does it work tomorrow, after a disk fills, an update breaks, or the VM is reclaimed?'. That question belongs to cloud operations — exactly what this slot's project should exercise.

Existing System

• Two branches run separate billing apps on standalone PCs; stock is reconciled by phone every few days.
• No central database: consolidated stock and sales need a branch visit.
• No backups that anyone has actually restored; a failed disk means re-entering a month of sales.
• No monitoring — outages are discovered by customers; no cost record — nobody knows what the 'free' setup really costs.
• Typical small-business fixes (a hosted SaaS) cost ₹500–2,000 per month and store data outside the owner's control.

Proposed System

• ArogyaPOS, a PHP/MySQL web app: GST-style invoicing, batch-and-expiry stock tracking per branch, low-stock alerts, day-close reports; a single central database with per-branch data.
• Containerised deployment: Nginx, PHP-FPM and MySQL as three services in one docker-compose.yml with healthchecks, named volumes and environment-file configuration — the same file runs laptop and cloud.
• Free-tier cloud deployment: Oracle Cloud Always-Free VM (or equivalent) behind HTTPS, redeployable from scratch in one script.
• Monitoring (industry-standard extras): Prometheus exporters for host, MySQL and containers; Grafana dashboards; alert rules for downtime, disk above 80% and failed backups, delivered to a Telegram group.
• Backup and DR: nightly mysqldump --single-transaction, gzip, SHA-256 checksum, rclone sync to free object storage with 30-day rotation; a DR runbook with RPO 24 h / RTO 30 min targets, proven by two timed restore drills.
• Cost tracking: a monthly worksheet listing every resource (VM, storage, egress, domain) and its price, showing the true cost of the 'free' deployment.

Feasibility Study

• Technical: Docker Desktop runs on an 8 GB laptop; the Always-Free VM (1–2 GB RAM) comfortably serves Nginx + PHP-FPM + MySQL at a two-branch shop's load, with the Prometheus and Grafana images alongside. Every component is free and open source.
• Economic: Baseline cost ₹0/month on the free tier; the worksheet also prices a realistic paid fallback (≈₹400/month small VM + ₹60 object storage) so the owner sees the exit path.
• Operational: Branch staff keep their current browser flow; the owner gets dashboards and alerts on a phone; every operation is documented so a non-author can run the restore.

In scope

• One fictional organisation (Arogya Medicals, two branches) with roles: Owner, Branch Pharmacist, (read-only) Auditor.
• Central MySQL database with per-branch stock and sales; consolidated owner dashboard.
• Full operations layer: Compose deployment, monitoring, alerts, backups, restore drills, DR runbook, cost worksheet, ops log.
• Deployment to one free-tier VM; laptop parity via the same Compose file.

Out of scope (v1)

• Kubernetes, multi-VM clustering or high-availability replication — one VM plus proven restore is the honest student-scale answer.
• Online payment gateway and e-invoice API integration.
• Pharmacy-drug licensing/regulatory workflow.
• Native mobile apps (the web app is responsive).

The project runs an iterative, DevOps-flavoured model with a weekly ops log as a formal deliverable: every week ends with something deployed, measured or rehearsed, and the log (incidents, decisions, times) becomes report evidence.

Testing approach: PHPUnit unit tests for billing and stock rules; black-box test cases for every screen; operations tests as scripts — verify-backup.sh (restore into a scratch DB and row-count/checksum compare), alert-injection tests (stop MySQL, assert the Telegram alert arrives within 5 minutes), and a chaos-lite test (docker kill the app container; healthcheck restarts it, the incident appears on the Grafana graph).

The system has a single deployment unit — one VM, one Compose application — deliberately simple, because the reliability story rests on monitoring and restore, not on clustering.

1. Containers: nginx (TLS termination, static files) → php-fpm (ArogyaPOS) → mysql (data volume). A fourth service, backup, is a cron container that dumps and syncs. Healthchecks let Compose restart unhealthy containers.
2. Operations plane: Prometheus scrapes node_exporter (host), mysqld-exporter (database) and cAdvisor (containers); Grafana renders dashboards; Alertmanager routes to Telegram.
3. Recovery plane: nightly dumps land on a host volume, are checksummed and rclone-synced to free object storage; the DR runbook rebuilds the VM from scratch: install Docker, git pull, docker compose up -d, restore latest verified dump, re-point DNS.

flowchart LR
  B1([Branch 1 browser]) -->|HTTPS| N[Nginx container]
  B2([Branch 2 browser]) -->|HTTPS| N
  B3([Owner / dashboard]) -->|HTTPS| N
  N --> P[PHP-FPM container: ArogyaPOS]
  P -->|PDO| M[(MySQL container + volume)]
  BK[Backup cron container] -->|mysqldump| M
  BK --> CHK[SHA-256 checksum] --> RC[rclone sync] --> OBJ[(Free object storage)]
  NE[node_exporter] -.-> PR[Prometheus]
  MX[mysqld-exporter] -.-> PR
  CA[cAdvisor] -.-> PR
  PR --> G[Grafana dashboards]
  PR --> AM[Alertmanager] -->|alerts| TG[Telegram group]

Deployment / DR flow

flowchart TD
  GIT[Git repo: compose + app + runbook] -->|deploy.sh| VM[Free-tier VM]
  VM --> UP[docker compose up -d]
  UP --> LIVE[Service live behind HTTPS]
  BK[(Nightly dump + checksum)] -->|rclone| OBJ[(Object storage)]
  DR{Disaster: VM lost} --> RB[Runbook: new VM, install Docker]
  RB --> PULL[git pull + compose up]
  PULL --> REST[Restore latest verified dump]
  REST --> VERIFY[Row-count + checksum verification]
  VERIFY --> LIVE2[Service restored — target under 30 min]

ER diagram (ArogyaPOS)

erDiagram
  USER ||--o{ SALE : bills
  BRANCH ||--o{ USER : employs
  BRANCH ||--o{ STOCK : holds
  PRODUCT ||--o{ BATCH : "expires in"
  BATCH ||--o{ STOCK : "is stocked as"
  SALE ||--o{ SALE_ITEM : contains
  PRODUCT ||--o{ SALE_ITEM : "is sold as"

  USER {
    string id PK
    string name
    string username UK
    string passwordHash
    string role
    string branchId FK
  }
  BRANCH {
    string id PK
    string name
    string address
  }
  PRODUCT {
    string id PK
    string name
    string composition
    string hsn
    decimal gstRate
  }
  BATCH {
    string id PK
    string productId FK
    string batchNo
    date expiryDate
    decimal mrp
  }
  STOCK {
    string id PK
    string branchId FK
    string batchId FK
    int quantity
  }
  SALE {
    string id PK
    string branchId FK
    string userId FK
    datetime at
    decimal subtotal
    decimal gstTotal
    decimal total
  }
  SALE_ITEM {
    string id PK
    string saleId FK
    string batchId FK
    int qty
    decimal price
  }

DFD — Level 1

flowchart TD
  PH([Pharmacist]) --> P1[1.0 Authenticate]
  P1 <--> D1[(D1 Users)]
  PH --> P2[2.0 Bill a sale]
  P2 <--> D2[(D2 Stock)]
  P2 --> D3[(D3 Sales)]
  OW([Owner]) --> P3[3.0 Manage products, batches, branches]
  P3 <--> D4[(D4 Products/Batches)]
  OW --> P4[4.0 View dashboards and reports]
  D3 --> P4
  D2 --> P4
  SYSB[5.0 Nightly backup job] --> D3
  D2 --> SYSB
  SYSB --> D5[(D5 Backup archive + off-site copy)]
  MON[6.0 Collect metrics and alert] -.-> P2
  MON --> D6[(D6 Metrics store)]
  MON --> OW

• Second VM + database replication for a warm standby, turning the 30-minute RTO into minutes.
• CI/CD with GitHub Actions: tests and compose up smoke-deploy on every push.
• Uptime certificate page: an external uptime monitor publishing the service's monthly availability — a nice SRE touch for the report.
• Automated expiry alerts to suppliers for near-expiry batches (WhatsApp Business API).
• Multi-tenant packaging: the same Compose file parameterised for other small shops, with a per-tenant cost sheet — a natural commercial follow-on.