The analytics layer is the part I would defend hardest. Fourteen endpoints
answer the questions a pharmacist actually asks at the counter: what is about
to expire, what is carrying no expiry date at all, what is not moving, what to
reorder given observed velocity, which supplier is quietly more expensive, and
where the gross margin really sits.
Choosing no framework was deliberate. The system runs on a single machine in a
pharmacy that cannot assume reliable internet, and it has to be recoverable by
someone who is not me.
That same constraint produced the part I am most pleased with. Between one
nightly dump and the next, a day of trading exists in exactly one place: the
disk in the back office. So there is a second MySQL database on another
server, one-way, brought up to date every minute there is a connection and
perfectly happy when there is not.
Triggers on each table queue nothing but a table name, a primary key and
whether the row was written or deleted. The sync job reads that queue and
fetches the current row at send time rather than a copy captured at
change time, which is what makes it safe to retry and self-repairing. Triggers
rather than application code because the app writes through its own helpers,
through raw SQL in sixteen places, and through command-line scripts that never
load a controller. A trigger sees all of them, and it fires inside the
caller’s transaction, so a sale that rolls back takes its queue entry
with it.
Local dumps run hourly while the shop is open, taken with
--single-transaction so a backup during trading never blocks a
sale. The off-site upload runs once at night, refuses to re-send a dump
already recorded as sent, and fails loudly if it finds no local dump at all.
Off-site targets are Cloudflare R2 and Google Drive; local disk is deliberately
not among them, because a copy on the same machine is not a backup.
- Role
- Sole architect and developer
- Built with
- PHP 7.3 · MySQL / MariaDB · Tailwind CSS · PDO. No framework, by choice
- Modules
- POS · inventory · CRM and patient records · analytics · purchases · refunds · expenses · payments and invoices · shifts and reconciliation · staff and suppliers · internal messaging
- Storage
- Cloudflare R2 over the S3 API, SigV4 signed by hand, with Google Drive and local disk as fallbacks
- Replication
- One-way sync to a remote MySQL mirror, queued through database triggers
- Backups
- Hourly local dumps, nightly off-site upload, GFS rotation, alerting when a copy goes stale
- Governance
- Role-based permissions, full activity audit log, reference numbering
- Status
- In production use. Internal system, so there is no public URL or repository
