Wade Lee

CHALLENGE

Third-party callbacks can be retried, arrive concurrently or out of order, or stop midway through local processing. Promotion quotas and inventory can also oversell under contention.

WHAT I BUILT

• Combined PostgreSQL advisory locks, unique constraints, and INSERT ON CONFLICT to prevent duplicate orders from callback retries or concurrent delivery
• Queried the payment provider during each callback and verified payment status, amount, and TradeNo before creating an order
• Built an inbox-like durable callback log and reconciliation jobs with bounded retries, orphan takeover, and downstream redelivery so order creation or invoicing cannot remain permanently stuck
• Used a temporary payment snapshot and frozen cart to prevent cart changes from producing inconsistent orders during payment
• Applied conditional atomic SQL updates and compensation to prevent bundle inventory and flash-sale quotas from overselling during concurrent checkout
• Ran production containers with least privilege to reduce security risk
• Added migration fingerprints and a deployment health gate to block unhealthy releases and schema-incompatible rollbacks
• Uploaded encrypted PostgreSQL backups to GCS daily and before deployments to preserve recovery capability
• Added a GIN index so product search does not degrade into full-table scans as the dataset grows

TRADE-OFF

Raw SQL, PostgreSQL locks, and a GIN index increase database coupling and index-maintenance cost in exchange for concurrency correctness, recoverability, and deployment safety.

CHALLENGE

Popular teacher slots can receive concurrent bookings; payment and renewal callbacks may be delivered repeatedly; and large videos need reliable uploads, controlled access, and seekable playback.

WHAT I BUILT

• Processed payment and renewal callbacks inside database transactions with signature validation, amount checks, locked payment records, and state checks, ensuring retries take effect only once and never duplicate or extend a subscription twice
• Locked a teacher's available slot and rechecked conflicts during booking, then relied on database constraints to prevent duplicate reservations for the same teacher and time
• Implemented chunked uploads for large videos and protected MP4 playback through short-lived signed URLs; browsers use byte-range requests to load content on demand
• Built RBAC with Spatie Permission plus Laravel policies, gates, resource ownership, and subscription-status checks for courses, assignments, attachments, and videos
• Integrated stored-card setup and automatic renewal, encrypted the provider-issued card token, and handled renewal success, payment failure, retries, and grace periods

TRADE-OFF

A Laravel monolith and database queue reduce deployment and operational complexity for faster delivery while leaving room to scale high-load asynchronous work into independent workers later.

CHALLENGE

A scheduler can settle the same period twice, while gift issuance, transfers, and daily-report synchronization can create negative inventory or break the audit trail.

WHAT I BUILT

• Used transaction-level PostgreSQL advisory locks to mutually exclude settlement by week or date
• Recorded batches as running, completed, or failed and used deduplication keys to prevent duplicate notifications
• Created gift lots, warehouse balances, transfers, and a movement ledger
• Locked rows during mutations and combined nonnegative constraints, audit logs, and owner-based access

TRADE-OFF

PostgreSQL locks avoid the need for Redis distributed locks; as the domain grows, handler logic should gradually move into application services.