Data first: model the data, the rest follows

A shop's orders table holds each order's status. An order may go from pending to paid, from paid to shipped, and from paid or shipped to refunded; no other move is allowed. The checkout API, the warehouse app and a support console that runs SQL by hand all update status. Yesterday the console ran this on order o-81, which had shipped, and checkout then charged it a second time:

UPDATE orders SET status = 'pending' WHERE id = 'o-81';

A wallet app keeps saved cards in cards(card_id, customer_id, is_default), and each customer should have at most one default card. A bug, now fixed in the app, gave two customers two default cards each:

card_id | customer_id | is_default
k-1     | cu-1        | true
k-2     | cu-1        | true
k-3     | cu-2        | true
k-4     | cu-2        | true
k-5     | cu-3        | true
k-6     | cu-3        | false

The team then runs these two statements, one after the other:

CREATE UNIQUE INDEX one_default ON cards (customer_id) WHERE is_default;
INSERT INTO cards VALUES ('k-7', 'cu-3', true);

A ticket shop sells guided museum tours and keeps them in tours(guide_id, during), where during is a time range of 30, 60 or 90 minutes. No guide may lead two tours whose times overlap. Tours are written by the box office, by a support console that can move a tour, and by a partner's nightly import, and any two of them can write at the same moment.

A shop's gift cards can each be redeemed once. On every checkout that uses a card, the redeem handler runs:

Two shoppers check out with card GC-77 at the same moment, on two servers using PostgreSQL's default isolation level, and both checks run before either insert.

A shop's products(sku, name, price) table holds 40 products priced 0, left by an old import; merchandising will reprice them next week. From today, no path may store a product priced 0 or below: not the admin form, not the supplier import, which runs plain INSERT statements, and not the support console, which runs UPDATE statements by hand.

A film-streaming service's schema for subscriptions:

CREATE TABLE subscriptions (
  id text PRIMARY KEY,
  user_id text NOT NULL,
  status text NOT NULL CHECK (status IN ('trial', 'active', 'cancelled')),
  ends_on date,
  CHECK (status <> 'cancelled' OR ends_on IS NOT NULL)
);
CREATE UNIQUE INDEX one_live ON subscriptions (user_id) WHERE status <> 'cancelled';

It holds three rows:

id | user_id | status    | ends_on
s1 | u1      | cancelled | 2026-05-01
s2 | u2      | active    |
s4 | u3      | cancelled | 2026-06-30

Four statements then run in this order, each in its own transaction, so a refused one changes nothing:

INSERT INTO subscriptions VALUES ('s3', 'u1', 'active', NULL);
UPDATE subscriptions SET status = 'trial', ends_on = NULL WHERE id = 's1';
UPDATE subscriptions SET status = 'cancelled' WHERE id = 's2';
UPDATE subscriptions SET status = 'active', ends_on = NULL WHERE id = 's4';