The Schema Is the Last Line of Defence

One-line summary

A production data contract must be preserved all the way down by PostgreSQL constraints, not by an if in the application. For an order API, the database itself must guarantee a positive amount, an owner and an organization, an idempotency key that is unique within the organization, and a creation timestamp.

Why application checks are not enough

A system may start with a single write path, but batch jobs, admin tools, recovery scripts, and new services are soon added. Even if the first API validates the amount, another path can insert a negative value and the data is already corrupted. NOT NULL, CHECK, UNIQUE, and foreign keys apply the same rules no matter which client connects. In particular, the idempotency key must be scoped to the organization rather than global, so different customers can use the same key without colliding. That is why UNIQUE (org_id, idempotency_key) expresses the business boundary more accurately.

GENERATED ALWAYS AS IDENTITY in PostgreSQL 16 is real production syntax, unlike the integer primary key in SQLite. TIMESTAMPTZ lets you compare stored moments on a single time axis. Design indexes around query patterns, in the order org_id, owner_id, created_at. Run the migration between BEGIN and COMMIT so that no intermediate state is exposed.

How to verify in practice

Do not just check that the DDL text contains the right words. Apply the migration to an empty, isolated schema, insert a valid row, and confirm that a second row with the same organization and the same key raises unique_violation. Also run the case where a negative amount raises check_violation. To avoid mixing with production data, create a temporary schema and remove it when you are done. Only if the same verification can be repeated in CI can "it worked on my machine" become deployment evidence.

Practical judgment criteria

A constraint is not a barrier that delays errors; it is a contract that keeps invalid state from being stored. Translate error codes into meaningful 409 or 400 responses in the API, but never remove the database constraint itself. You should also think about a rollback strategy, but first prove that the forward migration works both on a fresh empty database and against the conditions of existing data. The next lesson connects this invariant to transactions and retry semantics.