One painful lesson I learned while building high traffic systems !
If you are still handling critical data consistency only inside handlers, repositories, or service logic eventually your data will break.
At first everything feels safe.
You add checks like: "if record exists, skip insert" inside your application code.
Works perfectly in local development. Works in staging. Works under light traffic.
Then scale happens.
Now the same system gets hit from:
• realtime ingestion • retries • queues • background backfilling
And suddenly duplicate entries haha !
We faced this exact issue in our attribution purchases table the table powering reporting and analytics because it was designed to be the single source of truth for fast queries. The handler logic looked correct.
But under concurrency, multiple workers passed the existence check at the exact same time and inserted duplicate rows. Because application level checks are NOT atomic.
Then comes the common answer: "Just use transactions."
Yes, transactions help.
But transactions also come with cost:
• locking • extra resource usage • longer execution time • higher contention under scale • more complexity for simple operations
Sometimes people add heavy transactional flows for something a simple database constraint could solve instantly.
If the database already knows the rule: "One purchaseId must be unique" then let the database enforce it.
A single UNIQUE constraint protects against:
• race conditions • concurrent inserts • retries • parallel execution
without trusting every handler to behave perfectly forever.
Application code can fail. Workers can retry. Servers can scale horizontally.
But constraints remain consistent. The bigger the system becomes, the less you trust distributed execution timing and the more you trust the database.
Scalable systems are not built by hoping every service behaves correctly.
They are built by designing layers that preserve correctness even when everything else fails.