How do you decide which business rules belong in database constraints, which belong in application code, and which need a different mechanism entirely?
answer
- Name the enforcement point for every invariant
- Stability: 'what the data is' vs 'what the business allows now'
- Scope ladder: row → table → cross-table → aggregate → cross-service
- Reshape the schema so hard invariants become per-row
- No engine spans services → reconcile and alert
basics
~20 sPut stable, structural, row- or table-scoped invariants in the schema; put volatile or contextual policy in code. Rules the engine cannot express — cross-service, aggregate, temporal — need an explicit mechanism such as index-based enforcement, serialisable transactions, or asynchronous reconciliation with alerting.
solid answer
~1 minI sort rules along three axes: **stability**, **scope**, and **cost of violation**. - **Stable and structural** — identity, referential integrity, mandatory fields, value domains, conditional uniqueness. These go in the schema. They are cheap, universal across writers, and self-documenting. Conditional uniqueness in particular becomes a filtered unique index rather than code. - **Volatile or contextual** — policy that changes quarterly, rules depending on the caller, on a feature flag, or on a remote call. These go in application code; encoding them in the schema turns every policy change into a migration and strands historical rows in violation. - **Beyond the engine** — aggregates across rows, invariants spanning services or databases, rules involving time. Here I name the mechanism explicitly: reshape the schema so the rule becomes per-row (materialise a total on the parent and constrain it), use serialisable isolation with retries, or accept eventual consistency with a reconciliation job and an alert on drift. The rule I hold teams to: every invariant the system depends on has a named enforcement point and, if that point is asynchronous, a monitor. 'The service checks it' is not an enforcement point when other writers exist. Costs — write overhead, migration friction, error translation — shape *which* rules I encode, never whether the layer exists.
go deeper
Give the simple split — structural rules in the schema, changing business policy in code — with one example of each.
Add the scope ladder from row to cross-table and note that time-dependent and cross-service rules cannot be constraints at all.
Bring concrete mechanisms per shape — filtered unique index, exclusion constraint, materialised aggregate plus parent-row lock — and be explicit about the concurrency reasoning behind each.
Make it a governance position: every invariant has a named enforcement point, asynchronous enforcement implies a monitor, and the costs of write overhead and migration friction decide which rules are encoded, not whether the schema layer exists.
## The decision is about where an invariant is guaranteed Every meaningful rule in a system has an enforcement point: the single place where a violation becomes impossible rather than unlikely. Most production data problems trace back to a rule whose enforcement point was never actually decided — everyone assumed some other layer had it. So the design question is not 'database or application' but 'name the enforcement point for each invariant, and if it is asynchronous, name the monitor'. ## Axis one: stability How often does the rule change, and what happens to existing rows when it does? A schema constraint is a statement about **all** rows, including rows written years ago. If tightening the rule would invalidate historical data, it does not belong in the schema — or it belongs there in a version-scoped form. 'Orders must have a positive total' is stable forever. 'Discounts may not exceed 30%' is a policy that will be 40% next quarter, and last quarter's 35% orders are legitimately historical. Encoding the second as a CHECK guarantees a painful migration and a temptation to disable the constraint. Rule of thumb: if the rule would appear in a description of what the data *is*, it is structural. If it would appear in a description of what the business currently *allows*, it is policy. ## Axis two: scope What does the engine need to see to evaluate the rule? - **Single row** — a CHECK or NOT NULL. Cheapest and safest. - **Single table, cross-row equality** — unique constraint, or a filtered unique index for conditional uniqueness. Still declarative, still safe under concurrency because enforcement is physical in the index. - **Cross-row, non-equality (overlaps, ranges)** — exclusion constraints where available, otherwise explicit locking on a parent row so writers serialise on a single point. - **Cross-table reference** — foreign key. The engine protects the referenced row against concurrent removal, which application code cannot do without taking the same locks. - **Aggregate over many rows** — not directly expressible. Either materialise the aggregate onto a parent row so its lock serialises writers and a plain CHECK does the work, or use serialisable isolation and retry, or enforce asynchronously. - **Across services or databases** — no engine can help. This is a distributed-systems problem: idempotency keys, outbox patterns, sagas with compensations, and reconciliation. The senior instinct worth demonstrating is **reshaping the schema so a hard invariant becomes an easy one**. Introducing a table whose primary key *is* the rule, or denormalising a running total onto the parent, converts an invariant that depends on isolation-level discipline into one the engine enforces for free. That is usually a better trade than a trigger. ## Axis three: cost of violation How bad is a violated row, and how expensive is it to detect and repair later? Financial ledgers, entitlements and anything regulated justify strict, synchronous enforcement even at a write-throughput cost. A denormalised counter that drives a badge in the UI does not: eventual correction by a periodic job is proportionate. Costs to weigh on the other side: extra work per write for indexes and referential checks, contention introduced by locking a shared parent row, migration friction on very large tables, and the engineering cost of translating violations into good error messages. Note that enforcement cost and *detection* cost trade off. Skipping a constraint pushes the cost into audits, incident response and data repair, which are far more expensive per occurrence and arrive at the worst time. ## The layered posture For rules that live in the schema, the application still validates — for the user, not for truth. It gives immediate, field-level, localised feedback and reports several problems at once. The constraint is what makes the bad outcome impossible for every other writer. The write path must therefore also handle the violation gracefully, because rare is not never. For rules that live in code, the reverse question is worth asking explicitly: what stops a migration script or a second service from violating this? If the honest answer is 'nothing', either the rule is not important enough to protect, or it needs a schema-level backstop, or it needs a detection job. Say which. ## Rules that need a third mechanism **Temporal rules** — 'must be in the future', 'not more than 30 days old'. These cannot be constraints, because a constraint must be a stable function of the stored values; the row would silently become invalid. Enforce at write time in code, and if it matters, detect drift with a query. **Cross-service invariants** — 'a user with an active subscription in billing must have entitlements in access'. No constraint spans the boundary. The tools are idempotent operations keyed on a unique constraint, an outbox for reliable propagation, and a reconciliation job that compares the two sides and alerts on divergence. The reconciliation job is not optional; it is the enforcement point. **Rules requiring external data** — anything needing a remote lookup or the caller's identity. Application layer, necessarily. ## Anti-patterns to call out *Triggers as a general-purpose constraint engine.* They are invisible to readers, hard to test, and inherit every concurrency problem unless they take explicit locks. Use them when nothing declarative fits, and document why. *Constraints as the user-facing validation layer.* Users should not meet a raw violation error; that is a symptom of a missing UX layer. *Invariants that exist only in a code review culture.* If the guarantee depends on every future engineer remembering, it is not a guarantee. ## What I would say in one minute Sort by stability and scope: structural, stable, row- or table-scoped rules go in the schema because they are cheap and universal; volatile or contextual policy goes in code because schema migrations are the wrong change unit for it. Anything the engine cannot express gets an explicit named mechanism — reshaped schema, serialisable transaction, or asynchronous reconciliation with an alert — and I will not ship an invariant whose enforcement point nobody can name.
- Give a rule that looks like a CHECK constraint but must not be one, and say where it goes instead.'The scheduled date must be in the future' is the classic. A constraint must be a deterministic function of the stored values, and this one depends on the clock, so a row that was valid at insert becomes one the system itself could not re-insert — any restore, table rewrite or revalidation can fail on data the system wrote. Enforce it in application code at write time, and if it matters operationally, add a query that detects violations and alerts.
- How do you enforce 'total allocations for a project must not exceed its budget' when allocations are many rows?The cheapest correct approach is to materialise the running total on the project row and constrain that with a CHECK, updating it in the same transaction as the allocation — the parent row's lock serialises concurrent writers, so the invariant holds without isolation-level reasoning, at the cost of contention on hot projects. The alternatives are serialisable isolation with retry logic, which is correct but costs throughput and needs every writer to comply, or asynchronous reconciliation with alerting where a brief overshoot is acceptable. A trigger that runs SELECT SUM under the default isolation level is the common wrong answer, because two concurrent allocations each see a pre-conflict snapshot.
saying these in an interview costs you the question
- Answering 'put everything in the database' or 'put everything in the application' without a sorting principle
- Encoding current business policy as constraints and then disabling them when policy changes
- Treating a trigger as equivalent to a declarative constraint under concurrency
- Assuming a constraint can span services or databases
- Leaving asynchronous enforcement without a reconciliation job or an alert