Put Your Invariants in the Database

There is a predictable argument against database constraints: business rules belong in the domain layer, the database is just storage, and constraints make migrations harder.

The first two points are defensible as design philosophy. The third is true. And none of them survive contact with a system that has more than one way to write data.

Every system grows a second write path

You start with one service method. Then you add a bulk import. Then an admin override. Then a scheduled reconciliation job. Then a data fix someone runs against production during an incident.

Application validation covers the paths that go through the application. The database covers all of them. That difference is the entire argument.

What actually belongs down there

Not everything. The rule I use: a constraint belongs in the database when violating it produces data that is wrong independent of context.

A duplicate exam seat allocation is wrong in every context. A completion date before a start date is wrong in every context. Those are database constraints.

Whether a project is ready to publish depends on editorial workflow and changes with the business. That is application logic.

Here is the split in practice:

CREATE TABLE projects (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    slug            VARCHAR(160) NOT NULL,
    title           VARCHAR(200) NOT NULL,
    status          VARCHAR(24)  NOT NULL DEFAULT 'IN_PROGRESS',
    start_date      DATE,
    completion_date DATE,
    published       BOOLEAN      NOT NULL DEFAULT FALSE,
    published_at    TIMESTAMPTZ,

    CONSTRAINT uq_projects_slug UNIQUE (slug),
    CONSTRAINT ck_projects_status CHECK (
        status IN ('PLANNED','IN_PROGRESS','COMPLETED','MAINTAINED','ARCHIVED')),
    CONSTRAINT ck_projects_dates CHECK (
        completion_date IS NULL OR start_date IS NULL OR completion_date >= start_date),
    CONSTRAINT ck_projects_published_at CHECK (
        published = FALSE OR published_at IS NOT NULL)
);

That last constraint is my favourite kind. It makes a whole category of bug — a published record with no publication timestamp, which then sorts unpredictably or breaks a sitemap — structurally impossible. No test needed, because the state cannot exist.

Partial indexes carry rules too

An index is not only a performance tool. A partial unique index expresses a rule that would otherwise live in a service method and get forgotten:

-- At most one featured post at a time, among published posts only.
CREATE UNIQUE INDEX uq_single_featured_post
    ON blog_posts (featured)
    WHERE featured = TRUE AND status = 'PUBLISHED';

Try to feature a second post and the write fails. The rule is now enforced against the admin UI, the API, the import script and the psql session equally.

Translate the failure, do not leak it

The legitimate objection is that constraint violations produce terrible error messages. So translate them — once, centrally:

@ExceptionHandler(DataIntegrityViolationException.class)
ResponseEntity<ApiError> handleIntegrity(DataIntegrityViolationException ex,
                                         HttpServletRequest request) {
    String constraint = extractConstraintName(ex);
    String message = switch (constraint) {
        case "uq_projects_slug"  -> "A project with this slug already exists.";
        case "ck_projects_dates" -> "Completion date cannot be earlier than the start date.";
        default -> "The request conflicts with existing data.";
    };
    log.warn("Constraint violation {} on {}", constraint, request.getRequestURI());
    return ResponseEntity.status(HttpStatus.CONFLICT)
        .body(ApiError.of(HttpStatus.CONFLICT, message, request.getRequestURI()));
}

You still validate in the application for good UX — fast feedback, field-level messages, no round trip. The database is not the user interface. It is the backstop that is still there when the interface is bypassed.

About migration cost

Constraints genuinely do make migrations harder, and that is not a bug in the argument. Adding a constraint to a table with existing violating rows fails, loudly, in your migration pipeline.

That is the constraint doing its job. You have bad data. You found out during a controlled deployment with a rollback available, rather than during a support escalation. The alternative was not "no problem" — it was "the same problem, discovered later, by a user."

Take the failure early. It is always cheaper.