Problem & Challenge
E-commerce checkout pipelines suffer from concurrency bottlenecks. If multiple users attempt to purchase the final items in an inventory stock simultaneously, classic race conditions (dirty reads and phantom writes) can allow purchases that exceed actual inventory volumes, leading to database degradation and downstream fulfillment errors.
The engineering mandate was to build a multi-role storefront with robust administrative portals and a high-throughput, transaction-safe checkout system. The backend needed to validate user permissions, verify stock levels, process transactions, and update inventory under high concurrent session limits without visual lagging or connection overhead.
Technical Approach & Architecture
The system is built on a decoupled architecture. A **React.js client** handles UI state and interacts with a **FastAPI backend REST API** gateway. Real-time data validation runs through Pydantic schemas, and SQLAlchemy ORM maps relational database logic to a multi-indexed MySQL engine. A Redis cache layer intercepts incoming lookup operations, saving database cycles.
By enforcing security validation at the router level and moving catalog retrieval queries to the Redis cache-aside model, database transactions are isolated exclusively for critical writes (checkout, admin inventory changes), keeping the pool load low.
Engineering Challenges & Solutions
1. Inventory Race Conditions under Load
Under simulated flash-sale load testing (50 concurrent checkouts/sec for a single product with stock = 5), the standard SELECT and subsequent UPDATE database operations overlapped. The checkouts read the positive stock state before updates occurred, completing the order but setting inventory values to negative figures.
Solution: We wrapped the checkout logic inside an ACID-compliant SQLAlchemy database transaction block using an explicit **Pessimistic Locking** strategy. We modified the stock selection query to use with_for_update() (generating a native SQL SELECT ... FOR UPDATE statement). This locks target inventory rows for read-and-write operations from concurrent sessions until the current transaction commits or rolls back, ensuring stock integrity is preserved.
2. JWT Cryptographic Verification Overload
Initially, user role and permission validation occurred on every API route request by querying the database using the token sub-claim ID. This created massive connection queue blockages on the MySQL socket loop for read-only catalog routes.
Solution: We refactored our security module to inject cryptographic role parameters (e.g., {"roles": ["admin", "customer"]}) directly into the signed JWT payload. Our FastAPI dependency decodes and validates the HS256 JWT signature locally using the system secret key, identifying user roles in under 1.2ms without making database calls, thereby eliminating DB round-trips for permission audits.
Optimization Decisions
- Compound Catalog Indexing: To optimize public search and filtering routines, we created a composite MySQL database index on `(category_id, is_active, price)`. This eliminated temporary table sorting overhead and cut multi-filter query response times from 340ms to 28ms.
- Redis Cache-Aside Strategy: The product list is cached as structured JSON blocks in Redis with a 10-minute TTL. Any administrative action modifying catalog parameters automatically fires an eviction signal, purging the stale cache key to enforce visual updates.
- SQL Connection Pool Management: Configured SQLAlchemy engine parameters with `pool_size=20`, `max_overflow=10`, and `pool_recycle=3600` to prevent socket leakage and thread exhaustion during peak traffic events.
Deployment & Production Notes
The backend application is deployed inside containerized environments using Docker Compose to run isolated FastAPI nodes and a MySQL database cluster. Nginx acts as a reverse proxy managing incoming traffic and SSL certificate handshakes.
The frontend is compiled and served via **Vercel's global edge network** with caching optimizations. API requests are routed securely across an encrypted HTTPS transport network with custom CORS controls.
Lessons Learned
Application-level validation loops are insufficient for handling transactional state changes. Safety rules must be established within the database schema itself (e.g. unsigned database constraints, row locks). Combining JWT-encoded role variables with isolated query caches offers an excellent balance between latency controls and authorization scalability.