Brew is launching a loyalty program: a member earns points, a barista grants bonuses, interest accrues overnight. It sounds like a couple of tables and three queries — but it's exactly the place where the course comes together. You can't store money in a float, or a fraction-of-a-cent drift accumulates over a thousand accruals. A duplicate email or a negative balance must not reach the database — no matter which of our two backends writes it. A point lookup by member must not scan the whole accrual ledger. And when two processes touch one balance at the same time — a barista hits "grant bonus" while the nightly job accrues interest in the very same second — the total must reconcile to the cent, not get lost.

We've already covered each of these problems separately. The capstone assembles them into one small subservice and shows how they work together.

The business rule lives in the DB, not the app

The loyalty program is two tables. cap_members holds a member: id, email, tier, balance. cap_ledger is the accrual journal: who, how much, why. And the whole mechanics of loyalty is a careful choice of types and constraints on these two tables.

Money is balance_cents bigint: the balance in cents as an integer (module 01). No numeric for money on the hot path, and certainly no float: 15.00 is 1500, added and compared exactly. The "cents → currency" formatting is done by the app on output.

Next, the constraints that turn a business rule into a DB guarantee (module 02): PRIMARY KEY on id, UNIQUE on email (one person, one account), CHECK (tier IN ('bronze','silver','gold')) (tier from the set only), CHECK (balance_cents >= 0) (never go negative), NOT NULL on the required fields. cap_ledger.member_id is a foreign key REFERENCES cap_members(id): an accrual can't dangle on a nonexistent member.

The point is that these checks live in the database, not in one of the services. Brew has two backends writing to one DB; a rule baked only into the Go code of one of them, the other will bypass. A constraint in the schema rejects bad data from any writer — even from a hand-typed psql at three in the morning.

The race for one balance and the retry loop

The capstone's last scene — two processes touch one balance at the same time. A barista hits "grant bonus" under SERIALIZABLE while the nightly job accrues interest in the very same second. The conflict here isn't accidental: the demo sets it up deterministically (as in 05-05), so 40001 falls in the same place on every run:

Start: Alice's balance 15.00
 
  ① bonus: BEGIN, reads balance 15.00
  ② interest: +1.00, COMMIT ──► balance 16.00          (the nightly job commits first)
  ③ bonus: writes +5.00 over the 15.00 snapshot, COMMIT ──► ✗ 40001 (read/write conflict)
  ④ withRetry: opens a fresh snapshot, reads 16.00
  ⑤ bonus: writes +5.00, COMMIT ──► ✓ 21.00
 
  21.00 = 15.00 + 1.00 (interest) + 5.00 (bonus) — nothing was lost

SERIALIZABLE catches that the bonus's snapshot went stale between read and write, and rejects the transaction with 40001. The withRetry loop replays it on a fresh snapshot — and the balance reconciles to the cent, even though both processes fought over one row.

What our code shows

This is an escape-hatch unit on raw pgx (there's a go.mod, but sqlc isn't the protagonist here): the capstone builds the schema with DDL, reads plans via EXPLAIN, and runs a retry loop — that needs interactivity sqlc doesn't give. We work on the lab tables cap_members / cap_ledger: buildSchema does DROP ... CASCADE + CREATE, so the build is idempotent, and we don't touch the Brew base schema.

cmd/demo/main.go walks the subservice end to end in five steps. (1) It builds the schema. (2) Creates three members via INSERT ... RETURNING id — the id is generated by GENERATED ALWAYS AS IDENTITY and returned by the INSERT itself, with no second query (module 03). (3) Throws four deliberately bad rows at the DB and prints not the error text but the SQLSTATE of each rejection. (4) Compares the plan of a point lookup with EXPLAIN before and after CREATE INDEX. (5) Grants Alice a bonus under SERIALIZABLE and survives a serialization conflict via retry.

The conflict in step 5 is created deterministically (as in 05-05): after the bonus transaction has read the balance, nightlyInterest synchronously cuts in with a separate transaction — "nightly interest accrual +1.00" — and commits first. The pair of read/write dependencies yields 40001, and the withRetry loop replays the bonus on a fresh snapshot.

Running it

docker compose up -d
make lecture L=10-use-cases/10-01-build-the-brew-schema-capstone T=db-reset
make lecture L=10-use-cases/10-01-build-the-brew-schema-capstone

T=run is the default and can be omitted. From inside the unit directory it is shorter: make db-reset, then make run. And make test runs the asserted integration test against the sandbox — it checks that the final balance actually reconciles and that the rejections produce exactly the SQLSTATEs printed below.

1) Схема собрана: cap_members (типы + PK + UNIQUE + CHECK), cap_ledger (FK на члена).
 
2) CRUD с RETURNING: завели трёх членов клуба, id вернул сам INSERT:
   id  email               уровень  баланс, ₽
   1   alice@brew.example  gold     15.00
   2   bob@brew.example    silver   3.00
   3   carol@brew.example  bronze   0.00
 
3) Ограничения отбивают мусор (печатаем SQLSTATE, а не текст ошибки):
   попытка                               результат
   дубль email (UNIQUE)                  отбито, SQLSTATE 23505
   уровень вне набора (CHECK tier)       отбито, SQLSTATE 23514
   отрицательный баланс (CHECK balance)  отбито, SQLSTATE 23514
   запись на несуществующего члена (FK)  отбито, SQLSTATE 23503
 
4) Индекс, проверенный планом (EXPLAIN до и после CREATE INDEX):
   до индекса:    выборка по member_id=2 → Seq Scan
   после индекса: та же выборка        → Index Scan
 
5) Транзакция с ретраем на 40001 (начисление бонуса под SERIALIZABLE):
   старт: баланс Алисы 15.00 ₽
   (параллельно: ночное начисление +1.00 ₽ закоммитило первым — конфликт назревает)
   попытка 1: прочитал 15.00 ₽, пишу +5.00 ₽
   ↻ упало: 40001 (serialization_failure) — повторяю на свежем снимке
   попытка 2: прочитал 16.00 ₽, пишу +5.00 ₽
   ✓ COMMIT успешен за 2 попытки; итог 21.00 ₽ (старт +1.00 от процентов +5.00 бонус)

(The demo prints in Russian.) Steps 1–3 read straight: three members created, the INSERT returned the id, and four bad rows rejected — 23505 for the duplicate email, 23514 for the out-of-set tier and the negative balance, 23503 for an accrual on a nonexistent member. These codes are a contract: the app catches them and shows a human a clear error instead of crashing.

In step 4 the same lookup WHERE member_id = 2 goes Seq Scan before CREATE INDEX (the DB reads all 20003 ledger rows), and Index Scan after (it finds Bob's three rows by the index). In step 5 the first attempt read 15.00, but while it was writing, interest had already committed — the DB rejects it with 40001; the second attempt sees a fresh 16.00 and commits 21.00. Start 15.00 + 1.00 interest + 5.00 bonus = 21.00: nothing was lost, even though two processes touched the balance at the same time.

The fence

• The retry loop under SERIALIZABLE is mandatory, not optional. The DB may reject a transaction with 40001 at any moment, and without withRetry you simply lose the accrual — it's part of the isolation level's contract (we covered it in 05-04 and ran the same loop in 05-05). In production this loop is usually provided by a pool wrapper or an ORM, but the logic is identical: catch 40001, open a fresh transaction, replay.
• EXPLAIN explains the plan of one query — and only that. It doesn't tell you whether the index is healthy overall, whether the table is bloating, whether autovacuum is keeping up. Those are DB-health questions: you look at them via pg_stat_statements and the system views, and that's your DBA's territory, not our demo's (we touched EXPLAIN in module 06).
• Index selectivity decides whether it wins. Here member_id picks three rows out of twenty thousand, so the index wins; on a low-selectivity column it might not pay off.
• The cap_* lab tables are a simplification for the capstone's isolation. We don't touch the real Brew base tables (orders, outbox, drinks) here, so the DROP can't hit the tables that ship into CDC in 10-05.

Takeaways

The small loyalty subservice walked through the whole course. Money is bigint-cents (01). The business rule lives as a PK/UNIQUE/CHECK/FK/NOT NULL constraint in the DB itself, so bad data can't get in, whoever writes it (02). INSERT ... RETURNING hands back the generated id in one query (03). A point lookup is solved by an index, and EXPLAIN proves it — Seq Scan before, Index Scan after (06). And concurrent writes to one balance are protected by SERIALIZABLE plus the mandatory retry loop on 40001, and the balance reconciles to the cent (05). Tying it all together is the skill itself: separate techniques add up to a system where bad data and lost accruals simply have no place.

Next — the second capstone, 10-02: a price-and-promo engine. There we'll assemble the calculation of an order's final price with discounts and promotions — again at the junction of types, constraints, and queries, but around a different business problem: how to compute what to charge a customer, and not let a promo code drive the price below zero.