The constraints your ORM cannot express

Most schema DSLs describe shape: this column is a decimal, that one references another table, this pair is unique together. That is enough to stop a typo. It is not enough to stop a wrong state.

The rules that keep a domain honest tend to be conditional, and conditional is exactly where the DSL runs out.

One active price per product per outlet

The retail POS model keeps price history, so a product has many price rows and exactly one of them is active per outlet at any time. A plain unique index on (product, outlet) is wrong — it forbids history. A unique index on (product, outlet, active) is also wrong, because it only permits one inactive row too.

What you want is a partial unique index:

CREATE UNIQUE INDEX one_active_price_per_outlet
  ON product_prices (product_id, outlet_id)
  WHERE active;

The predicate restricts the index to the rows that matter. History is unlimited; contradiction is impossible. Most ORM DSLs cannot express the WHERE clause, so this arrives as a raw migration and stays there.

Sign conventions on a ledger

Stock is an append-only ledger of movements. A receipt is positive, a sale is negative, and a transfer is a pair. Nothing in a type system stops a client writing a positive quantity on a sale — the type is just an integer.

A check constraint does:

ALTER TABLE stock_movements ADD CONSTRAINT movement_sign
  CHECK (
    (kind = 'sale'    AND quantity < 0) OR
    (kind = 'receipt' AND quantity > 0) OR
    (kind = 'adjustment')
  );

Now the invariant holds for every writer, including the reporting job someone adds in eighteen months and the psql session someone opens at midnight.

Non-overlapping validity windows

Effective-dated tax rules are only correct if their windows do not overlap. Two rules covering the same day means an invoice total depends on row order, which is a bug that appears once a year and is never reproducible.

Postgres has an exclusion constraint for exactly this:

ALTER TABLE tax_rules ADD CONSTRAINT no_overlapping_windows
  EXCLUDE USING gist (
    tax_code WITH =,
    validity WITH &&
  );

Why not in application code

The usual objection is that this logic belongs in the domain layer, where it can be tested and produce good error messages. Both things are true, and neither is an argument against also putting it in the schema.

Application-level validation protects one path. A schema constraint protects every path: the admin tool, the import script, the migration, the analytics job with write access that nobody remembers granting. The application check gives the user a good message. The constraint makes the bad state unrepresentable.

You want both. The one you can skip is not the one in the database.

The cost

Constraints have to be maintained, and a constraint that has drifted from the domain is worse than none — people learn to work around it. They also make some migrations slower, because adding a check to a large table takes a scan.

That cost is real and it is smaller than the alternative: reconstructing what a total should have been, six months later, from rows that are already wrong.