Two requests both check that an operation does not exist. Both then create it. The application code followed its rules, but the check and write were not one enforceable boundary.
Database constraints can protect invariants that must remain true even when requests run concurrently or arrive through different code paths.
Choose an invariant the database can enforce
For a fictional integration, “one receipt per provider event within a tenant” is a candidate uniqueness rule. “Every customer should have only one enquiry” is probably wrong because repeat enquiries can be legitimate.
Write the business meaning before choosing the columns. The PostgreSQL constraint documentation covers unique, not-null, foreign-key, check, and exclusion constraints. Their exact suitability depends on the schema and database version.
Scope uniqueness to the right identity
| Intended rule | Design concern |
|---|---|
| One provider event receipt | Include the relevant provider account or tenant |
| One operation per request | Use a stable operation reference across retries |
| Required business identifier | Decide whether null or empty values are valid |
| Valid relationship | Keep referenced records within the intended boundary |
Do not choose a customer name or approximate timestamp as an operation key merely because it is convenient. Those values can collide across legitimate work.
Also review null semantics. A nullable component of a uniqueness rule may behave differently from the business expectation. Define absence explicitly and verify the chosen constraint's behaviour.
Handle a rejected duplicate as a known outcome
When a constraint rejects a repeat, distinguish it from an unrelated database error. The application may be able to return the existing operation's result after verifying identity and permissions.
Do not catch every database exception and label it “already processed.” That would hide connection failures, invalid references, and other defects.
For an external write, a unique local operation row still does not prove exactly-once execution in the remote system. The webhook-retry guide and timeout guide explain the remaining recovery work.
Test concurrency, not just sequential retries
Use isolated fictional fixtures. Submit the same operation concurrently and verify the intended number of rows. Then submit two legitimately different operations for the same customer and verify that both remain possible.
Test missing key fields, changed tenant context, and a worker restart after claiming work. Review both accepted and rejected paths.
Adding a constraint to existing data requires a migration plan. Identify duplicates first, agree how to resolve them, and assess the deployment impact. Do not assume a schema change is safe merely because it works on an empty development database.
Database integrity questions
Can application validation replace every constraint?
It improves error messages, but concurrent requests still need an enforceable data boundary.
Does a unique event key prevent every duplicate business action?
No. Define operation identity and external-side behaviour separately.
What should a review include?
The invariant, schema rule, concurrent test, and recovery path. Bring those to a database integration discussion.