Why Python validation is not enough and how PostgreSQL constraints, transactions and migration tests protect business invariants under concurrency and partial failure.
Express invariants at the most durable layer that can enforce them and translate violations into intentional application behaviour.
Validation and constraints solve different problems
Pydantic and domain validation can give an immediate, helpful response before unnecessary work reaches the database. They cannot prevent two concurrent requests from both passing a check and writing incompatible state.
Unique, foreign-key, check and exclusion constraints are the final authority for data rules. They also protect against scripts, migrations and future services that bypass today’s application path.
Design for races and partial work
A read-then-insert uniqueness check is a race unless the database enforces uniqueness. Let the constraint decide, then handle the specific integrity error as a defined API or workflow result.
Keep related durable changes within a transaction, but do not confuse a transaction with a complete distributed workflow. External effects require explicit intent, recovery state and idempotency beyond the database boundary.
Migrate rules safely
Adding a constraint to existing data requires a plan for invalid rows, lock impact and validation time. Clean or quarantine data deliberately; do not weaken the invariant simply because historical data is inconvenient.
Integration tests should exercise the real database constraint and verify the application maps expected violations to stable error behaviour. Unit tests alone cannot prove the schema is protecting production state.
Evidence to take into review
- Important business invariants have database enforcement.
- Concurrency races rely on constraints rather than pre-checks.
- Constraint violations map to intentional application outcomes.
- Migrations account for existing invalid data and lock impact.
- Integration tests run against the production database engine.
Explore all engineering notes.
Use PRODUCTION-7 to connect this concern with the other dimensions of a trustworthy backend.
View all articles Get the checklist