Columns that refuse what should never be stored: schema design exercise

A marketplace stores reviews of completed purchases.

Each review has three fields, and each has a rule the business already treats as non-negotiable:

A rating from 1 to 5. Whole stars only: no 3.5, no 0, no 7.
The amount paid for the purchase being reviewed. It is money: two decimals, and it has to add up exactly. A cent lost to rounding is a bug Finance will find.
When the review was posted. Reviewers are in different countries, and "which day was that" has to have one answer for everyone.

Plus the obvious: a review belongs to a purchase that exists, and the same purchase is reviewed at most once.

purchases already exists.

The one thing that is fixed

2 relations

The tests reference these names. Everything else is yours, and is what is being assessed: extra tables, extra columns, types, constraints, indexes.

purchases
id
reviews
idpurchase_idratingamount_paidposted_at

The schema that already exists

This runs before your submission. Do not repeat it, extend it.

prelude.sql

CREATE TABLE purchases (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

INSERT INTO purchases DEFAULT VALUES;
INSERT INTO purchases DEFAULT VALUES;
INSERT INTO purchases DEFAULT VALUES;
INSERT INTO purchases DEFAULT VALUES;

Your schemaPostgreSQL DDL: tables, constraints, indexes

schema.sql

CREATE TABLE reviews (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  purchase_id BIGINT NOT NULL,
  rating      INTEGER NOT NULL,
  amount_paid NUMERIC NOT NULL,
  posted_at   TIMESTAMP NOT NULL,
  body        TEXT NOT NULL DEFAULT ''
);

-- Three of these types accept values the business forbids. Find them.

Tab indents · ⌘/Ctrl + Enter runs

What will be checked

8 tests

····Three ordinary reviews are stored
····The amounts add up to exactly sixty cents
····A rating of 7 is refused
····A rating of 0 is refused
····A negative amount is refused
····The Tokyo review and the UTC review are the same moment
····A purchase cannot be reviewed twice
····A review cannot belong to a purchase that does not exist