Most data failures do not begin with a broken database. They begin earlier, when an application accepts an ambiguous request, retries a side effect without context, or lets two valid operations race each other. A database constraint can catch some of these mistakes, but data integrity at scale is an architectural property: it emerges from how APIs, workers, transactions, schemas, and deployment practices work together.

That distinction matters because a system can have a well-designed schema and still create duplicate orders, lose updates, expose stale balances, or publish events for transactions that later roll back. The database remains essential, but it cannot be the only place where correctness lives.

Start by defining the invariants

An invariant is a condition that must remain true regardless of request timing, retries, or failures. “An email address is unique” is a familiar example. More useful business invariants are often more specific: an invoice may be paid once, stock may never fall below zero, and a user must not receive access before a successful payment is recorded.

Write these rules in plain language before deciding where to enforce them. Then place each rule at the lowest reliable layer that has enough context to enforce it.

• Use database constraints for facts that must always be true in stored data.
• Use application services for workflows that span multiple tables or external systems.
• Use API contracts to make client intent explicit.
• Use asynchronous processing for work that can happen later, while preserving a durable record of what must happen.

This layered approach avoids a common trap: putting every rule in PHP because it feels convenient. Application validation improves user feedback, but it is not a substitute for a unique index, foreign key, check constraint, or transaction. Other code paths will eventually bypass it: imports, admin tools, background jobs, or a future service.

Design APIs for retries, not ideal requests

Networks fail in ways users cannot see. A client may send a request, the server may complete it, and the response may be lost. If the client retries a “create payment” request, the backend must distinguish a retry from a second instruction.

For operations with important side effects, accept an idempotency key. Store the key with the request’s outcome under a uniqueness constraint, then return the original result when the same key is used again. The key must be scoped appropriately, such as to an account and operation type, rather than treated as a global string.

DB::transaction(function () use ($accountId, $key, $payload) {
    $existing = IdempotencyRequest::query()
        ->where('account_id', $accountId)
        ->where('key', $key)
        ->lockForUpdate()
        ->first();

    if ($existing) {
        return $existing->response;
    }

    $payment = Payment::create($payload);

    IdempotencyRequest::create([
        'account_id' => $accountId,
        'key' => $key,
        'response' => ['payment_id' => $payment->id],
    ]);

    return ['payment_id' => $payment->id];
});

The exact implementation depends on the framework and database, but the important idea is atomicity. A lookup followed by an insert outside a transaction can allow concurrent requests to both conclude that no record exists. The database must still reject the duplicate key, and the application must handle that rejection by loading the already-recorded result.

Protect against lost updates

Read-modify-write logic is deceptively dangerous. Consider decrementing inventory. Two workers can both read a quantity of one, both decide it is available, and both write zero. The database sees valid values; the business sees an oversold item.

Prefer a conditional update when the rule can be expressed directly in SQL:

UPDATE inventory
SET quantity = quantity - 1
WHERE sku = :sku
  AND quantity > 0;

If the affected-row count is zero, there was no available stock. This is usually simpler and safer than reading the row into PHP first. For more complex workflows, use a transaction with an appropriate locking strategy, keep the transaction short, and avoid network calls while locks are held.

Optimistic locking is another useful option when contention is low. Add a version column, update only when the version matches, and make the caller retry or resolve the conflict when it does not. It turns a silent overwrite into an explicit decision.

Make asynchronous work durable

Sending email, charging a provider, indexing search documents, and notifying other services should rarely occur inside the transaction that changes primary data. External calls are slow, can fail unpredictably, and cannot be rolled back by your database.

The transactional outbox pattern gives this boundary a durable shape. In the same transaction that creates or changes a business record, insert an outbox row describing the event. A worker later delivers the event and records its delivery state. If the process crashes after commit, the outbox entry remains available for retry.

Consumers must also be idempotent. Delivery systems can produce duplicates, especially after a timeout or worker restart. A consumer should record a stable event identifier or otherwise make repeated processing harmless. “At least once” delivery becomes manageable when every handler is designed for it.

Use the database as a partner, not a dumping ground

Database constraints are executable documentation. A foreign key says an orphaned record is invalid. A unique index says duplicates are not merely inconvenient; they are forbidden. A non-null column says absence is not a valid state. These guarantees make downstream code simpler because fewer impossible states can enter the system.

Still, avoid turning stored procedures, triggers, and opaque database behavior into the sole home of business workflows. They can be appropriate for tightly local integrity rules, but logic that coordinates APIs, queues, authorization, and observable failures is usually easier to test and maintain in the application layer. The goal is not to minimize database logic; it is to make each rule visible, enforceable, and owned by the right boundary.

Build observability around integrity failures

Integrity incidents are often discovered indirectly: support reports a duplicate charge, a reconciliation job finds a mismatch, or a queue backlog reveals a stuck workflow. Treat these as first-class operational signals.

• Log stable request, entity, and event identifiers.
• Measure constraint violations, retry rates, dead-lettered jobs, and outbox age.
• Keep audit fields that explain who or what changed a record and when.
• Run reconciliation checks for critical derived data, such as balances or external payment states.

Auditing is not an excuse to tolerate corruption. Its value is diagnostic: when an invariant is challenged, engineers need enough evidence to understand the sequence and repair it safely.

Integrity is a system design habit

At scale, correctness is not achieved by one perfect transaction or one carefully named table. It comes from assuming that requests will be duplicated, workers will restart, deployments will overlap, and concurrent users will make valid demands at the same time.

Design the invariant first. Enforce it at the strongest practical boundary. Make side effects replayable, make consumers idempotent, and make failures observable. The database then becomes what it should be: a powerful foundation in a system deliberately built to keep its promises.