Case Study: Gym Management System

Problem & Challenge

Local fitness facilities often track operational states (such as active memberships, attendance logs, and invoice histories) using fragmented spreadsheets or paper ledger grids. As customer scales expand, tracking files quickly degenerate, leading to sync conflicts, inaccurate plan expiries, missed billing invoices, and severe transaction tracking gaps.

The client's primary engineering requirement was to eliminate spreadsheet synchronization failures. We needed to design an operations panel that manages member logs, enforces strict plan expirations, records check-in metrics, and automates daily billing runs safely—all without running expensive hosting models.

Technical Approach & Architecture

We selected an **on-premise Docker container environment** to run FastAPI and MySQL backend servers on a dedicated local Linux host, cutting hosting costs to zero. Database interactions are structured via SQLAlchemy with schema migrations managed by Alembic, ensuring schema integrity. Daily operations rely on normalized tables and unique indexes to prevent double-billing.

Daily Automated Invoicing Flow

Cron Job Scheduler Trigger

Fires shell script at 00:00 every day to execute FastAPI billing route

↓

Chunked Membership Scan

Loads active memberships in batches of 100 to conserve RAM bounds

↓

Unique Constraint Check

Queries DB to verify if invoice for current cycle already exists

↓

SQL transaction Commit

Inserts invoice records in bulk, commits state, and updates plan status

This localized database architecture ensures that gym operations run with near-zero latency, remaining independent of external internet outages. A secondary automated backup routine runs in the background, syncing critical backups offsite.

Engineering Challenges & Solutions

1. Data Normalization & Integrity Inconsistencies

The initial spreadsheets combined billing amounts, check-ins, member profiles, and plan details inside a single flat sheet. This structure led to orphan records, plan durations conflicting with invoice values, and no historical audit trail for member visits.

Solution: We designed a fully normalized relational database schema matching **Third Normal Form (3NF)** specifications. We isolated entities into discrete tables: members, plans, memberships, invoices, and check_ins. Database foreign key mappings enforce strict relational rules (e.g. ON DELETE RESTRICT on memberships linked to invoices), preventing accidental record deletions and preserving billing history.

2. Preventing Double-Billing in Daily Batch Runs

Automated invoicing scripts run on daily crons. If a cron script execution timed out or was accidentally triggered twice, it could generate duplicate invoices for the same member in the same billing cycle, leading to customer disputes.

Solution: We resolved this by introducing a multi-tiered validation approach. In the database schema, we added a compound unique index constraint on the `invoices` table mapping `(membership_id, billing_cycle_date)`. When the invoicing runner processes transactions, MySQL automatically blocks any insertion attempts that violate this constraint, throwing an integrity exception that halts duplicate generation without corrupting the state.

Optimization Decisions

Batch Processing of Invoices: Instead of executing database write commands inside loops for each customer, the backend queries active memberships in chunked batches of 100 and applies SQLAlchemy's bulk_save_objects utility, cutting memory overhead by 70%.
Aggregated Attendance Views: To populate hourly check-in telemetry charts on the manager dashboard, we created a pre-compiled MySQL **Database View**. This view indexes check-in timestamps to return aggregated logs in under 4ms, avoiding expensive JOIN lookups on every page reload.
Local Log Rotation: Configured Linux log rotation utilities to compress, archive, and purge operational and API engine logs every 7 days, maintaining disk space boundaries on the host machine.

Deployment & Production Notes

The application is deployed on a physical mini-PC running a secured **Ubuntu Server** distribution within the gym's local area network (LAN). Docker containerization isolates backend routing nodes from the relational database container, keeping maintenance simple.

An automated backup cron job runs at 01:00 AM daily, converting the MySQL state into a compressed dump file, encrypting the output, and securely syncing it to a remote storage server for absolute disaster recovery compliance.

Lessons Learned

On-premise hardware deployments must be engineered defensively to survive network fluctuations and localized power outages. Restructuring flat spreadsheet files into strict normalized relational schemas immediately simplifies application logic and prevents billing errors. Database-level constraints are the absolute foundation of application reliability.