Database

A table called campaign_product_variants had no foreign key, no ON DELETE CASCADE, and no deleted_at. Before the shared pool existed, that was a cosmetic problem: a handful of rows pointing at SKUs that no longer existed made a report look a few units smaller than reality. I never saw anyone treat that as a bug, and I did not go back through old tickets to check. Then we shipped a shared stock pool — several campaigns drawing quota from one pool per SKU — and the same orphaned rows stopped being cosmetic. On a dev database I could still poke at, 14 orphan rows were locking 294 units out of sale, permanently, with no admin screen that could select and release them. The missing foreign key had turned pre-existing technical debt into a lowered sales ceiling. ...

The Challenge of Identifiers In software engineering, choosing a Primary Key type seems simple, but it profoundly affects both security and system performance as you scale. We typically start with two familiar choices: Auto-increment Integers or UUIDv4. 1. Limitations of Traditional Methods Option 1: Auto-increment Integer -- Example: Sequential IDs over time 1, 2, 3, 4, 5... Pros: Extremely fast and memory-efficient. Cons: Lack of security. If your URL is https://api.myapp.com/v1/invoice/1001, a competitor can easily guess that the next invoice is 1002, or even count your total customer base just by incrementing these numbers. Option 2: UUIDv4 (Random String) -- Example: No discernible order f47ac10b-58cc-4372-a567-0e02b2c3d479 0e58c3d4-a567-4372-58cc-f47ac10b0e02 Pros: Maximum security; impossible to predict. Cons: Database Fragmentation. Because it is entirely random, the Database must work hard to sort these into memory, slowing down the system as your data grows. 2. UUIDv7: The Best of Both Worlds UUIDv7 solves these problems by combining: [Current Timestamp] + [Random Bits]. ...