Capstone 2: Data Modeling and API Contracts

bee2026-10-0872 min read0 views

Turn the domain model into schema: full DDL, indexes and constraints, with reasons for every type choice; then turn the API list into contracts with DTO/VO layering, a response envelope, error codes, idempotency and paging.

#Project #RESTful

1 / 170

Section

0. The 30-second version

2 / 170

The previous article froze what to build. This one delivers two artefacts you can actually work from: a schema that runs and an API specification someone can code against. They sound humble; in practice these are the two most failure-prone steps on the whole site — pick the wrong column type and finance cannot reconcile half a year later, refuse to change objects between layers and one schema edit detonates the frontend. Every decision here comes with the live scene of what breaks if it is wrong, and every claim can be run yourself.

3 / 170

Five terms, one line each:

4 / 170

DDL: the whole CREATE TABLE statement — it tells the database which columns exist, what type each holds and which values may never repeat
Index: the alphabetical directory at the front of a dictionary. Reads get fast, but every insert and update has to maintain that directory too, so writes get slower
DTO / Entity / VO: three look-alike objects with different jobs — the DTO receives what the user sent, the Entity maps one database row, the VO is what the frontend is allowed to see
Idempotent: doing the same thing ten times yields exactly the result of doing it once. The single test: does a repeat execution cause any extra side effect?
Contract: the request/response specification both sides sign, fixing field names, types, requiredness and which error code a failure carries

5 / 170

类比｜Analogy

the same batch of ingredients, three shapes. You buy the makings of "tomato and egg stir-fry" and the vendor hands you a purchase slip — muddy tomatoes, a whole carton of eggs, price tag still attached. That is the DTO: things arrive shaped by the outside world's rules. In the kitchen they get washed, chopped and packed into a labelled prep box, gaining a batch number and an expiry date. That is the Entity: sorted into the storehouse's own slots. What reaches the table is a finished dish with no mud and no price tag. That is the VO: only the presentable form leaves. Projects that "pass one entity class all the way through" effectively carry that muddy crate, box and all, onto the customer's table — internal flags, delete markers and password hashes on open display. Section 6 turns this chain into real code.

6 / 170

类比｜Analogy

one cinema ticket must never be printed twice. The window print fails, so you ask the staff to "run it again". A correct system does not hand you a second seat with the same number; it hands back the one it already issued, because it keys on the ticket number, not on "you happened to ask again". BeeOrder's checkout idempotency has exactly this shape: the client generates one idempotentKey per checkout intent, the database puts a unique index on it, a duplicate submit collides with that index, and the service catches the collision and returns the first order unchanged. The clever-looking alternative — "query first whether it exists, then decide to insert" — lets two people both read "it does not exist" and both write an order. Two voices shouting "print it again" at the window really do lose the seat. Section 8 is devoted to this.

7 / 170

Diagram

Figure · The seven elements of an API contract

8 / 170

This map doubles as the article's table of contents: the resource/path and method branches plus the status-code and error-code branches land in Sections 5 and 7, paging and idempotency live in Sections 9 and 8, and the bottom "versioning" branch is that repeated rule "endpoints may only grow, never change". As you read, keep one thought in mind: any element left unstated guarantees somebody knocks on your door on integration day — the error-code table in Section 7 and the quick-reference in Section 13 are those answers written down in advance.

9 / 170

After this article you should be able to answer three questions:

10 / 170

Why must order items redundantly store the product name and unit price? What happens to historical orders once a product is renamed?
Why must money be DECIMAL(12,2) plus BigDecimal rather than double? Give one concrete miscalculation.
What is wrong with "check for a duplicate first, then decide whether to insert"? What would make it genuinely idempotent?

11 / 170

Section

1. Modeling principles: domain first, tables second

12 / 170

The previous article froze BeeOrder's domain model, state machine and API list. This one turns them into two deliverables: the database schema and the API contract. But before writing a single CREATE TABLE, get the order right — the schema is the consequence of the domain model, never its starting point.

13 / 170

Many code smells begin with "design the tables first": take a requirement, draw an ER diagram, add whatever fields are convenient, and the business rules end up with nowhere to live. The correct order is: think through the business rules and aggregate boundaries, then let tables carry them.

14 / 170

Three modeling principles:

15 / 170

Domain first, tables second: domain objects (User / Order / Inventory) are the subject; tables are only their persisted form. A concept that does not exist in the domain should not appear out of nowhere in a table.
Model per aggregate: one aggregate root equals one main table plus several child tables; child tables point at the root and may only be changed through it. order and order_item form one aggregate; user and order are two.
Reference other aggregates by ID, not by a hard foreign key: order.user_id stores only the user ID, with no FOREIGN KEY constraint. This is not laziness; it buys decoupling and room to scale — hard foreign keys add locking and cascade risk under high concurrency.

16 / 170

Section

1.1 Third normal form vs denormalization: why order snapshots must be redundant

17 / 170

Third normal form (3NF) says eliminate redundancy: store each fact once. Yet for orders we deliberately break 3NF — order_item redundantly stores product_name and product_price.

18 / 170

Table

Scenario	Normal form choice	Reason
Basic user and product info	follow 3NF	maintain once, reference everywhere; one edit applies globally
Item name and unit price	denormalize (snapshot)	a historical order must freeze the facts as of checkout
Order total total_amount	redundant aggregate	avoids SUM(order_item) on every read, and the total is itself a business fact

19 / 170

To make this concrete, imagine a scene: on May 1 a user buys a "Hive Smart Speaker" for 5999. On June 1 marketing renames it "Hive Speaker Pro" and drops the price to 4999. If order_item stored only product_id, then when the user views the order in July, when the platform settles June refunds, and when finance issues invoices, the program joins product and reads the new name and new price — the user objects that they paid 5999, and reconciliation falls apart.

20 / 170

A snapshot is not "duplicate data"; it is "the facts as of that moment". Whenever something "was once this price, this name, this address", it must be frozen at the instant the business event happens. Understand this and you understand why order systems are naturally a hotspot for denormalization.

21 / 170

Put both designs on the table and settle the bill in one go — strict 3NF on the left, BeeOrder's targeted redundancy on the right:

22 / 170

Diagram

Figure · Schema: strict normal form versus snapshots

23 / 170

Do not pick a side yet; just compare two ledgers: what the left saves is storage, and what it forfeits is history plus query speed; what the right spends is two extra columns, and what it buys is history that never mutates plus one query per page. Reduced to a single judgement: should this field still read as it did three years from now? If yes, snapshot it; if no (marketing copy, for instance), join it live and honestly.

24 / 170

Tip

denormalization is not license to duplicate at will — only duplicate fields that change over time and whose historical values must be preserved. A product's marketing copy can be joined live; its checkout price must be snapshotted.

25 / 170

Section

2. The full DDL

26 / 170

Eight tables spread across two aggregate domains; see the whole picture first, then land each table:

27 / 170

Diagram

28 / 170

BeeOrder uses MySQL 8, always with the InnoDB engine (we need transactions and row locks) and the utf8mb4 charset (we need emoji and Chinese), with DATETIME(3) for millisecond precision. Three of those land inside the DDL; the fourth — connection and runtime configuration — lands in application.yml. Have the generator lay out its skeleton first:

29 / 170

Generator

GeneratorLay out the application.yml skeleton firstapplication.yml1 / 5

Start with datasource ticked, then add server, jpa, logging and profile one by one and watch five blocks become one file; its timezone parameter is only a starting point that Section 10 turns into a UTC convention

App name

Output

server:
  port: 8080

spring:
  application:
    name: beeorder
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/bee_order?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
    username: ${DB_USER:root}          # ${} 占位符：环境变量优先，冒号后是默认值
    password: ${DB_PASS:}
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 30000
      max-lifetime: 1740000            # 必须小于 MySQL 的 wait_timeout
      pool-name: beeHikari

Why each choice matters

datasourcePool settings only apply here; constructing HikariDataSource in code ignores every one of them.

30 / 170

Note two things about the output: the millisecond precision of DATETIME(3) survives only if the connection parameters preserve it, and the serverTimezone=Asia/Shanghai the generator writes is merely a default — leave it alone for now; Section 10 explains why the whole project converges on UTC.

31 / 170

Section

2.1 The user table

32 / 170

sql

CREATE TABLE `user` (  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'user id, auto-increment primary key',  `phone`       VARCHAR(20)     NOT NULL                COMMENT 'phone number, the login account',  `password`    CHAR(60)        NOT NULL                COMMENT 'BCrypt hash, fixed length 60, never plaintext',  `nickname`    VARCHAR(32)     NOT NULL DEFAULT ''     COMMENT 'display name',  `status`      TINYINT         NOT NULL DEFAULT 1      COMMENT '1 active, 0 disabled',  `deleted`     TINYINT         NOT NULL DEFAULT 0      COMMENT 'soft delete: 0 no, 1 yes',  `create_time` DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT 'created at',  `update_time` DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3)                                ON UPDATE CURRENT_TIMESTAMP(3)        COMMENT 'updated at',  PRIMARY KEY (`id`),  UNIQUE KEY `uk_user_phone` (`phone`)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4  COLLATE = utf8mb4_0900_ai_ci COMMENT = 'user table';

33 / 170

Table

Field	Type	Why this choice
id	BIGINT UNSIGNED	auto-increment primary key is the clustered index; ordered writes mean fewer page splits, and unsigned widens the range
phone	VARCHAR(20) + unique index	the login account must be unique; 20 chars covers international numbers
password	CHAR(60)	BCrypt outputs a fixed 60 chars, so CHAR avoids a length prefix; never plaintext
status / deleted	TINYINT	only a couple of values, one byte is plenty, smaller than VARCHAR
create_time	DATETIME(3)	millisecond precision helps debug concurrency and gives stable ordering

34 / 170

Section

2.2 The product table

35 / 170

sql

CREATE TABLE `product` (  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'product id',  `name`        VARCHAR(128)    NOT NULL                COMMENT 'product name',  `subtitle`    VARCHAR(255)    NOT NULL DEFAULT ''     COMMENT 'subtitle',  `price`       DECIMAL(12,2)   NOT NULL                COMMENT 'unit price in CNY, exact money type',  `status`      TINYINT         NOT NULL DEFAULT 1      COMMENT '1 listed, 0 delisted',  `extra_info`  JSON            NULL                    COMMENT 'extra info (tags/images), open structure',  `deleted`     TINYINT         NOT NULL DEFAULT 0      COMMENT 'soft delete',  `create_time` DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT 'created at',  `update_time` DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3)                                ON UPDATE CURRENT_TIMESTAMP(3)        COMMENT 'updated at',  PRIMARY KEY (`id`),  KEY `idx_product_status` (`status`)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = 'product table';

36 / 170

Table

Field	Type	Why this choice
price	DECIMAL(12,2)	money must be exact; `double` silently loses precision in its binary form (see the demo in Section 4)
extra_info	JSON	tags and images have an open shape; MySQL 8's native JSON validates and offers query functions
status	TINYINT	listed/delisted is binary, ideal for a low-cardinality filter

37 / 170

Section

2.3 The inventory table

38 / 170

sql

CREATE TABLE `inventory` (  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'inventory id',  `product_id`  BIGINT UNSIGNED NOT NULL                COMMENT 'product id',  `available`   INT UNSIGNED    NOT NULL DEFAULT 0      COMMENT 'sellable quantity; unsigned rejects negatives',  `locked`      INT UNSIGNED    NOT NULL DEFAULT 0      COMMENT 'locked quantity (ordered, unpaid)',  `version`     INT UNSIGNED    NOT NULL DEFAULT 0      COMMENT 'optimistic-lock version',  `update_time` DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3)                                ON UPDATE CURRENT_TIMESTAMP(3)        COMMENT 'updated at',  PRIMARY KEY (`id`),  UNIQUE KEY `uk_inventory_product` (`product_id`)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = 'inventory table';

39 / 170

Table

Field	Type	Why this choice
product_id	BIGINT UNSIGNED + unique index	one row per product; the unique index is both a constraint and a fast lookup path
available	INT UNSIGNED	under strict mode an unsigned column errors out before going negative — a database-level backstop
locked	INT UNSIGNED	lock on checkout, convert on payment, release on timeout — all revolve around it
version	INT UNSIGNED	a spare optimistic-lock counter, leaving room for later concurrency schemes

40 / 170

Section

2.4 The inventory_log table

41 / 170

sql

CREATE TABLE `inventory_log` (  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ledger id',  `product_id`  BIGINT UNSIGNED NOT NULL                COMMENT 'product id',  `change_qty`  INT             NOT NULL                COMMENT 'delta: negative on deduct, positive on restore',  `type`        VARCHAR(16)     NOT NULL                COMMENT 'DEDUCT / RELEASE / RESTORE',  `ref_id`      BIGINT UNSIGNED NOT NULL                COMMENT 'related id (order id and so on)',  `create_time` DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT 'created at',  PRIMARY KEY (`id`),  KEY `idx_invlog_product_time` (`product_id`, `create_time`),  UNIQUE KEY `uk_invlog_ref_type` (`ref_id`, `type`)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = 'inventory ledger';

42 / 170

Table

Field	Type	Why this choice
change_qty	INT (signed)	one entry encodes direction: negative to deduct, positive to restore
type	VARCHAR(16)	readable enum names make debugging obvious at a glance
(ref_id, type)	composite unique key	one id, one action can be written only once — deduction idempotency for free

43 / 170

Section

2.5 The order table

44 / 170

order is a SQL keyword, so the table name must be back-quoted — the first trap this article insists you remember (see Section 10).

45 / 170

sql

CREATE TABLE `order` (  `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'order id',  `order_no`       VARCHAR(32)     NOT NULL                COMMENT 'public business order number',  `user_id`        BIGINT UNSIGNED NOT NULL                COMMENT 'buyer user id',  `total_amount`   DECIMAL(12,2)   NOT NULL                COMMENT 'order total in CNY',  `status`         VARCHAR(16)     NOT NULL                COMMENT 'CREATED/PAID/SHIPPED/COMPLETED/CLOSED/REFUNDED',  `idempotent_key` VARCHAR(64)     NOT NULL                COMMENT 'client-supplied idempotency key',  `receiver_name`  VARCHAR(32)     NOT NULL DEFAULT ''     COMMENT 'recipient name',  `receiver_phone` VARCHAR(20)     NOT NULL DEFAULT ''     COMMENT 'recipient phone',  `receiver_addr`  VARCHAR(255)    NOT NULL DEFAULT ''     COMMENT 'shipping address',  `pay_time`       DATETIME(3)     NULL                    COMMENT 'paid at',  `ship_time`      DATETIME(3)     NULL                    COMMENT 'shipped at',  `close_time`     DATETIME(3)     NULL                    COMMENT 'closed at',  `expire_time`    DATETIME(3)     NOT NULL                COMMENT 'auto-close at = created + 30 minutes',  `deleted`        TINYINT         NOT NULL DEFAULT 0      COMMENT 'soft delete',  `create_time`    DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT 'created at',  `update_time`    DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3)                                   ON UPDATE CURRENT_TIMESTAMP(3)        COMMENT 'updated at',  PRIMARY KEY (`id`),  UNIQUE KEY `uk_order_no` (`order_no`),  UNIQUE KEY `uk_order_idem` (`user_id`, `idempotent_key`),  KEY `idx_order_user_status_time` (`user_id`, `status`, `create_time`),  KEY `idx_order_status_expire` (`status`, `expire_time`)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = 'order table';

46 / 170

Table

Field	Type	Why this choice
order_no	VARCHAR(32) + unique index	the public order number; never expose the auto-increment ID's size or growth rate
status	VARCHAR(16)	stores enum names, matching the state machine exactly, so debugging is readable
idempotent_key	VARCHAR(64)	the client key; the unique `(user_id, idempotent_key)` makes checkout idempotent
expire_time	DATETIME(3) + composite index	the scan basis for auto-close, stored so it need not be recomputed each time
total_amount	DECIMAL(12,2)	the total is a business fact; storing it avoids summing items on every read

47 / 170

Section

2.6 The order_item table

48 / 170

sql

CREATE TABLE `order_item` (  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'item id',  `order_id`      BIGINT UNSIGNED NOT NULL                COMMENT 'order id',  `product_id`    BIGINT UNSIGNED NOT NULL                COMMENT 'product id',  `product_name`  VARCHAR(128)    NOT NULL                COMMENT 'snapshot of product name at checkout',  `product_price` DECIMAL(12,2)   NOT NULL                COMMENT 'snapshot of unit price at checkout',  `quantity`      INT             NOT NULL                COMMENT 'quantity',  `amount`        DECIMAL(12,2)   NOT NULL                COMMENT 'line total = price x quantity',  `create_time`   DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT 'created at',  PRIMARY KEY (`id`),  KEY `idx_item_order` (`order_id`)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = 'order item table';

49 / 170

Table

Field	Type	Why this choice
product_name / product_price	VARCHAR + DECIMAL (snapshot)	a historical order must not shift when a product is renamed or repriced
amount	DECIMAL(12,2)	line total = price x quantity; storing it avoids recomputation
order_id	plain index	loading items by order is the hottest read path and needs an index

50 / 170

Section

2.7 The payment table

51 / 170

sql

CREATE TABLE `payment` (  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'payment id',  `order_id`      BIGINT UNSIGNED NOT NULL                COMMENT 'order id',  `trade_no`      VARCHAR(64)     NOT NULL                COMMENT 'gateway trade number, the idempotency key',  `amount`        DECIMAL(12,2)   NOT NULL                COMMENT 'paid amount',  `channel`       VARCHAR(16)     NOT NULL DEFAULT 'MOCK' COMMENT 'payment channel',  `status`        VARCHAR(16)     NOT NULL                COMMENT 'INIT/SUCCESS/FAILED',  `callback_time` DATETIME(3)     NULL                    COMMENT 'callback arrival time',  `create_time`   DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT 'created at',  `update_time`   DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3)                                  ON UPDATE CURRENT_TIMESTAMP(3)        COMMENT 'updated at',  PRIMARY KEY (`id`),  UNIQUE KEY `uk_payment_trade_no` (`trade_no`),  KEY `idx_payment_order` (`order_id`)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = 'payment table';

52 / 170

Table

Field	Type	Why this choice
trade_no	VARCHAR(64) + unique index	the gateway's trade number is unique and is the core line of defence for callback idempotency
status	VARCHAR(16)	INIT/SUCCESS/FAILED, working with the order state machine to guarantee "succeed once"
callback_time	DATETIME(3)	records when the callback actually arrived, useful for reconciliation

53 / 170

All eight tables are in place. Before touching indexes, watch one real checkout walk between the tables: which step lands on which table, and which step must keep its hands off the others. This animation is the shared blueprint for Sections 3 through 9:

54 / 170

Animation

55 / 170

Section

3. Index design: every index must justify itself

56 / 170

An index is not "add one to every commonly used field". Each secondary index slows writes (every insert/update maintains another B+ tree), so the rule is: serve only the genuinely hot query paths.

57 / 170

Here is the actual index DDL — beyond the ones inlined above, the order table needs two composite indexes:

58 / 170

sql

-- My order list: by user + status + time, descendingALTER TABLE `order`  ADD KEY `idx_order_user_status_time` (`user_id`, `status`, `create_time`);-- Auto-close scan: filter by status, then take the earliest expirationsALTER TABLE `order`  ADD KEY `idx_order_status_expire` (`status`, `expire_time`);

59 / 170

Section

3.1 The leftmost prefix: why (user_id, status, create_time) replaces two indexes

60 / 170

A composite index (user_id, status, create_time) is sorted "by user_id first, then by status within a user, then by create_time within that". So it can only be used by conditions that start at the leftmost column and stay contiguous:

61 / 170

Table

Query condition	Uses the index?	Note
`WHERE user_id = ?`	yes	uses the leftmost column
`WHERE user_id = ? AND status = ?`	yes	first two columns, contiguous
`WHERE user_id = ? AND status = ? ORDER BY create_time DESC`	perfect	all three columns, and the sort rides the index
`WHERE status = ?`	no	skips the leftmost user_id; the index is no longer ordered by status

62 / 170

That last row is the iron law of the leftmost prefix: a composite index's column order decides which queries it can serve. Put the most selective column (user_id) leftmost so more queries benefit.

63 / 170

Trap

a WHERE status = ? query on a single low-cardinality column cannot lean on the composite index. If you truly need to filter by status alone, give it its own (status, ...) index, or accept a full scan.

64 / 170

Section

3.2 Fields that should not be indexed

65 / 170

Table

Field that should not be indexed	Reason
deleted	cardinality of 2 is nearly useless; make it a subordinate column of a composite index, not a standalone one
status (alone)	in millions of orders it has low selectivity; a single-column index barely helps
create_time (alone)	range scans cross many rows; better as the last column of a composite index
extra_info (JSON)	indexing JSON bloats the index and restricts usage; extract a real column if you must search it
frequently updated fields	every UPDATE maintains the index, causing write amplification for little benefit

66 / 170

Tip

a good rule of thumb — indexes serve "read-heavy + high-selectivity" conditions. Conversely, if a field changes on nearly every write, or a query matches more than ~20% of the table, the index will not help.

67 / 170

Section

4. Field-choice checklist

68 / 170

Here is the per-table reasoning collected into one table:

69 / 170

Table

Business field	Choice	Alternative	Why (and why not the alternative)
Money	`DECIMAL(12,2)`	DOUBLE / FLOAT	floats represent decimals in binary and cannot express 0.1 exactly; sums drift
Status	`VARCHAR(16)` enum name	TINYINT number	readable, self-explaining, easy to debug; costs a few extra bytes
Time	`DATETIME(3)` (stored UTC)	TIMESTAMP / BIGINT	decoupled from business time zone, no 2038 problem, millisecond precision
Primary ID	`BIGINT UNSIGNED` auto-increment	UUID / snowflake	ordered writes, clustered-index friendly; expose order_no externally
Extension	`JSON` type	VARCHAR / TEXT	MySQL 8 native JSON validates, offers functions, and reads clearly
Delete flag	`deleted TINYINT`	physical delete	users/products may soft-delete; orders are financial records and never do
Optimistic lock	`version INT`	no version, row locks only	leaves room for read-modify-write paths; row locks and optimistic locks both available

70 / 170

Section

4.1 The float-error demo: why money must be DECIMAL

71 / 170

You do not need to be intimidated by the word "precision" — one line of code explains it:

72 / 170

Code

Codejava

System.out.println(0.1 + 0.2);                                        // 0.30000000000000004System.out.println(new java.math.BigDecimal("0.1")        .add(new java.math.BigDecimal("0.2")));                       // 0.3System.out.println(2.99 * 100);                                       // 298.99999999999994

Notes

Line 1: double cannot represent 0.1 or 0.2 exactly, so the sum is 0.30000000000000004
Line 2: constructing BigDecimal from strings yields exactly 0.3
Line 3: even the ubiquitous "yuan to cents" conversion goes wrong in floating point

73 / 170

The database-side answer is DECIMAL(12,2): 12 total digits with 2 decimals reaches 99,999,999,999.99, plenty for business, and maps to BigDecimal in Java. Any stray double in money-handling code is a potential source of financial loss.

74 / 170

Warning

use float / double only where an approximation is inherently expected — coordinates, ratings, ratios. For money, stock and quantities, always use exact types.

75 / 170

Section

5. API contract design: turning the endpoint list into a contract

76 / 170

Animation

77 / 170

The previous article listed 15 endpoints; this one adds "what the request looks like, what the response looks like, what the auth is", so it becomes a contract both sides can code against. URLs follow REST conventions: plural nouns, hierarchy for ownership, filtering and paging in the query string, and no verbs in the path.

78 / 170

Table

Method	Path	Auth	Request focus	Response focus
POST	`/api/v1/auth/register`	public	phone + password	new user id
POST	`/api/v1/auth/login`	public	phone + password	JWT + user info
GET	`/api/v1/auth/me`	auth	none (read from JWT)	current user DTO
GET	`/api/v1/products`	public	page / size / keyword	paged product VOs
GET	`/api/v1/products/{id}`	public	path param id	product detail VO
GET	`/api/v1/inventory/{productId}`	public	path param productId	available / locked
POST	`/api/v1/orders`	auth	CreateOrderRequest + idempotency key	new order VO
GET	`/api/v1/orders`	auth	OrderQuery (status/time/paging)	paged order VOs
GET	`/api/v1/orders/{id}`	auth	path param id	order detail VO
POST	`/api/v1/orders/{id}/cancel`	auth	path param id	updated order VO
POST	`/api/v1/payments`	auth	orderId + channel	payment record and redirect info
POST	`/api/v1/payments/callback`	public (signed)	gateway callback payload	fixed success ack
GET	`/api/v1/payments/{orderId}`	auth	path param orderId	payment record VO
GET	`/api/v1/notifications`	auth	page / size	paged notification VOs
GET	`/api/v1/admin/stats/orders`	admin	begin / end date	daily aggregate VOs

79 / 170

A contract with only a table is not enough; the caller should grasp it at a glance. Here are the three most critical endpoints as full payloads.

80 / 170

The checkout request — note the client idempotency key idempotentKey is required:

81 / 170

json

POST /api/v1/ordersAuthorization: Bearer eyJhbGciOiJIUzI1NiJ9...Content-Type: application/json{  "receiverName": "Zhang San",  "receiverPhone": "13800138000",  "receiverAddr": "Building 1, 802, Science Park, Nanshan, Shenzhen",  "idempotentKey": "9f2c1e0a-3b7d-4c11-8e2a-create-order",  "items": [    { "productId": 1001, "quantity": 2 }  ]}

82 / 170

The success response — data holds an OrderVO with no userId, no deleted and no internal fields:

83 / 170

json

{  "code": 0,  "message": "ok",  "data": {    "orderNo": "202610071530001234",    "status": "CREATED",    "totalAmount": 11998.00,    "expireTime": "2026-10-07T07:30:00Z",    "items": [      { "productId": 1001, "productName": "Hive Smart Speaker",        "productPrice": 5999.00, "quantity": 2, "amount": 11998.00 }    ]  },  "traceId": "a1b2c3d4",  "timestamp": 1759827000000}

84 / 170

The failure response — insufficient stock returns error code 1001, and data carries diagnostics for the frontend and for debugging:

85 / 170

json

{  "code": 1001,  "message": "insufficient stock",  "data": { "productId": 1001, "available": 1, "requested": 2 },  "traceId": "e5f6a7b8",  "timestamp": 1759827004321}

86 / 170

Kernel lab

TeaVM契约落地成一等公民：请求如何被分派idle

用 /users/42 对照理解 @PathVariable 与 DTO 请求体的解析差异

Scenario

Click “Run demo” to execute the AOT-compiled Java kernel right in your browser, step by step.

87 / 170

Once the contract is frozen, the "parse → validate → dispatch" chain that a request travels through Spring takes a definite shape: path parameters go through @PathVariable, the body goes through @RequestBody plus Bean Validation, and both merge on the controller method signature.

88 / 170

Four labs take that chain apart. Lab 1: the stop where validation fails — swap the success payload above for one missing a required field and watch where the request dies and what comes back:

89 / 170

Kernel lab

TeaVMWhere a request with a bad payload stopsidle

Pick the 'validation fails' position: notice it turns around before ever reaching the Service, then switch back to the happy path and compare the number of stops

Scenario

Click “Run demo” to execute the AOT-compiled Java kernel right in your browser, step by step.

90 / 170

Lab 2: where objects change vehicles. The "purchase slip → prep box → plated dish" picture from Section 6 is not decoration; it maps onto real type conversions. Switch to the cost-of-skipping-a-layer position to see what happens when they never change:

91 / 170

Kernel lab

TeaVMThe DTO / Entity / VO hand-off in actionidle

Start on 'object shapes' to compare the three types within one checkout, then 'where the tx boundary sits' to confirm why orchestration must live in the service

Scenario

Click “Run demo” to execute the AOT-compiled Java kernel right in your browser, step by step.

92 / 170

Lab 3: how a Java object becomes the JSON you saw. The response body does not appear by magic — the message converter (HttpMessageConverter, the component that translates a return value into JSON or a string according to the Accept header) decides its shape:

93 / 170

Kernel lab

TeaVMHow the response body gets translatedidle

Cycle through 'JSON' and 'Accept header' to see one controller method produce different formats, then 'where 406 comes from' to meet the case where nobody picks up the translation

Scenario

Click “Run demo” to execute the AOT-compiled Java kernel right in your browser, step by step.

94 / 170

Lab 4: the error exit. That is the whole point of the error-code table in Section 7 — a business exception needs exactly one exit, otherwise the frontend receives an HTML stack trace instead of an envelope:

95 / 170

Kernel lab

TeaVMFrom a validation failure to code=4001idle

Choose 'validation 400' to see who catches what Bean Validation throws, then '@ControllerAdvice' to understand why an uncaught exception never reaches the user naked

Scenario

Click “Run demo” to execute the AOT-compiled Java kernel right in your browser, step by step.

96 / 170

Key point

the value of a contract is that it is fixed before coding. Once frozen, the frontend can start immediately with mock data and the backend implements to the contract; if they disagree during integration, read the contract instead of assigning blame — precisely the "contract first" idea the animation's six steps convey.

97 / 170

Now replay those three payloads against the animation a second time; each frame lands on one concrete artefact: frame 1's use case is the "submit an order" story card from #43 Section 2; frame 2 produces the CreateOrderRequest in Section 5 (fields, types and constraints fixed together); frame 3 is the OrderVO plus the error-code table; frame 4 is the endpoint list in this very section; frames 5 and 6 are both teams building separately and finally matching over curl. Whichever frame feels empty tells you which deliverable has not been written yet:

98 / 170

Animation

99 / 170

The contract is on paper; the tooling is alive. Boot the app into the kernel console and run the paths that matter most in this contract: one idempotency key arriving twice, the paging SQL rewrite, and the message converter translating the same response:

100 / 170

Console

101 / 170

Section

6. DTO / VO / Entity: why you must not return an Entity

102 / 170

Many projects cut corners by returning the database entity Order straight from the controller. It is fine in a demo; in production it is three landmines:

103 / 170

Table

Risk of returning an Entity	Consequence
Security leak	the entity carries `password`, `deleted` and internal flags; serializing it leaks them
Tight coupling	change the schema (add/rename a column) and the frontend API breaks
Circular reference	`Order` and `OrderItem` reference each other; Jackson serialization overflows the stack

104 / 170

So BeeOrder strictly separates three layers: Entity maps the database, DTO receives requests, VO emits responses. Their fields may look nearly identical, but their responsibilities and lifecycles differ completely.

105 / 170

Click the transfer points of the three objects in one checkout, and you see exactly what the separation fences off. Note the last stop: skipping a transfer does not raise an error — it walks internal fields out of the building:

106 / 170

Diagram

FlowOne request, three object types, one transfer per layer1 / 5

→

Client JSON

the raw request body; to the backend it is merely text

All clearChange outfit at every layer: the DTO never reaches the database, the Entity never leaves the building — one less leak and one less coupling each time

107 / 170

The request-side DTO carries "shape and validation rules", stopping bad input at the controller door:

108 / 170

java

package com.beeorder.order.dto;import jakarta.validation.Valid;import jakarta.validation.constraints.*;import java.util.List;public class CreateOrderRequest {    @NotBlank(message = "recipient name is required")    @Size(max = 32)    private String receiverName;    @NotBlank(message = "recipient phone is required")    @Pattern(regexp = "^1[3-9]\\d{9}$", message = "invalid phone format")    private String receiverPhone;    @NotBlank(message = "shipping address is required")    @Size(max = 255)    private String receiverAddr;    @NotEmpty(message = "order items are required")    @Size(max = 50, message = "at most 50 products per order")    @Valid    private List<Item> items;    @NotBlank(message = "idempotency key is required")    @Size(max = 64)    private String idempotentKey;    public static class Item {        @NotNull(message = "product id is required")        private Long productId;        @NotNull @Min(1) @Max(999)        private Integer quantity;        // getters / setters omitted    }    // getters / setters omitted}

109 / 170

The response-side VO exposes only "fields the frontend needs and that are safe", and does the masking here:

110 / 170

java

package com.beeorder.order.vo;import java.math.BigDecimal;import java.time.LocalDateTime;import java.util.List;public class OrderVO {    private String orderNo;          // business number only, never the auto-increment id    private String status;           // state-machine enum name    private BigDecimal totalAmount;    private List<OrderItemVO> items;    private String receiverName;    private String receiverPhone;    // masked to 138****8000 on output    private LocalDateTime createTime;    private LocalDateTime payTime;    // note: no userId (frontend does not need it), no deleted, no version}

111 / 170

A thin, hand-written converter maps Entity to VO — visible, controllable, debuggable:

112 / 170

java

package com.beeorder.order.converter;public final class OrderConverter {    private OrderConverter() {}    public static OrderVO toVO(Order order, List<OrderItem> items) {        OrderVO vo = new OrderVO();        vo.setOrderNo(order.getOrderNo());        vo.setStatus(order.getStatus());        vo.setTotalAmount(order.getTotalAmount());        vo.setReceiverName(order.getReceiverName());        vo.setReceiverPhone(MaskUtil.phone(order.getReceiverPhone()));  // mask        vo.setCreateTime(order.getCreateTime());        vo.setPayTime(order.getPayTime());        vo.setItems(items.stream().map(OrderConverter::toItemVO).toList());        return vo;    }}

113 / 170

Table

Mapping option	Pros	Cons	Fits
Hand-written converter	zero dependencies, visible logic, breakpoint-friendly	repetitive when there are many fields	BeeOrder: few fields with masking and custom logic
MapStruct	generated at compile time, effortless with many fields	needs an annotation processor; complex mappings still need custom code	large projects where DTO and Entity align closely

114 / 170

Note

BeeOrder writes converters by hand. The reason is practical — our mapping carries masking, status translation and field assembly, which MapStruct would still handle with custom methods. Hand-writing keeps it simple and direct.

115 / 170

Section

7. A uniform response and error codes

116 / 170

Every endpoint wraps its payload in a Result<T> envelope, so the frontend can branch on a single code:

117 / 170

java

package com.beeorder.common.response;public class Result<T> {    private int code;          // 0 means success    private String message;    private T data;    private String traceId;    // trace id for debugging    private long timestamp;    public static <T> Result<T> ok(T data) {        Result<T> r = new Result<>();        r.code = 0;        r.message = "ok";        r.data = data;        r.timestamp = System.currentTimeMillis();        return r;    }    public static <T> Result<T> fail(ErrorCode ec) {        Result<T> r = new Result<>();        r.code = ec.getCode();        r.message = ec.getMessage();        r.timestamp = System.currentTimeMillis();        return r;    }    // getters / setters omitted}

118 / 170

Error codes live in one enum, each bound to an HTTP status, so monitoring, the gateway and logs all understand them:

119 / 170

java

package com.beeorder.common.response;public enum ErrorCode {    OK(0, "ok", 200),    STOCK_NOT_ENOUGH(1001, "insufficient stock", 409),    ORDER_NOT_FOUND(1002, "order not found", 404),    ORDER_STATUS_ILLEGAL(1003, "order status does not allow this action", 409),    DUP_PAY_CALLBACK(2001, "duplicate payment callback, ignored", 200),    PAY_AMOUNT_MISMATCH(2002, "payment amount does not match the order", 400),    UNAUTHORIZED(3001, "not authenticated or token expired", 401),    FORBIDDEN(3002, "no permission for this resource", 403),    PARAM_INVALID(4001, "parameter validation failed", 400),    INTERNAL_ERROR(5000, "system busy, please retry later", 500);    private final int code;    private final String message;    private final int httpStatus;    ErrorCode(int code, String message, int httpStatus) {        this.code = code;        this.message = message;        this.httpStatus = httpStatus;    }    // getters omitted}

120 / 170

BeeOrder divides codes into segments, so a prefix tells you the family at a glance: 1xxx business (orders/stock/payment), 2xxx payment callbacks, 3xxx auth, 4xxx validation, 5xxx system.

121 / 170

Table

Code	Meaning	HTTP	Trigger
1001	insufficient stock	409	at checkout, `available < requested`
1002	order not found	404	query or cancel a non-existent order
1003	illegal order status	409	start a payment on a closed order
2001	duplicate payment callback	200	a second callback for the same `trade_no` (idempotent, returns success)
2002	payment amount mismatch	400	callback amount differs from the order amount
3001	unauthenticated	401	missing or expired token
3002	forbidden	403	accessing someone else's order
4001	validation failed	400	Bean Validation fails
5000	internal error	500	an uncaught runtime exception

122 / 170

Trap

"always 200, hide the error in the body" is a common legacy habit. It saves the frontend a little work but blinds the gateway's retry policy, monitoring's error-rate stats and cache invalidation. Give a business error the HTTP status it deserves; the code in the envelope only adds finer business semantics.

123 / 170

Section

8. Idempotency design: repeated requests with no side effects (the key part)

124 / 170

Idempotency is one of the easiest places in a distributed system to get wrong and one of the easiest to overlook. BeeOrder has two writes that must be idempotent: checkout and the payment callback.

125 / 170

First see what "arriving twice" looks like in the system: the second request carrying the same idempotency key bounces off the unique index for its whole journey. Every frame of this animation maps onto a line of code in 8.1 below:

126 / 170

Animation

127 / 170

Section

8.1 Checkout idempotency: a client key plus a unique index

128 / 170

A user double-clicks "submit order", or the client retries after a timeout, and the same checkout intent arrives twice. The most reliable defence is not "query first to see if it exists" but letting a database unique constraint shut the duplicate out:

129 / 170

java

@Transactionalpublic OrderVO createOrder(Long userId, CreateOrderRequest req) {    try {        // assemble and insert; uk_order_idem on (user_id, idempotent_key) guards it        Order order = assemble(userId, req);        orderMapper.insert(order);        return doCheckout(order, req);          // deduct stock, write items    } catch (DuplicateKeyException e) {        // unique-key conflict = a duplicate submit: fetch and return the first result        Order exist = orderMapper.selectByUserIdAndIdemKey(userId, req.getIdempotentKey());        log.info("duplicate checkout, returning first result orderNo={}", exist.getOrderNo());        return OrderConverter.toVO(exist, orderItemMapper.selectByOrderId(exist.getId()));    }}

130 / 170

Code

Codesql

-- the foundation of idempotency: one user, one idempotency key, one rowUNIQUE KEY `uk_order_idem` (`user_id`, `idempotent_key`)

Notes

idempotent_key is generated by the client (a UUID), one key per checkout intent, reused on retry
the unique (user_id, idempotent_key) guarantees the duplicate insert fails, and DuplicateKeyException is the "duplicate" signal
on conflict, do not error out — return the first result; to the user, two clicks yield the same order, and that is idempotency

类比｜Analogy: the idempotency key is the ticket number printed on the corner. Before reprinting, the window reads the number and honours only the first request for it — so "print it again" returns the same seat rather than a second one. Moving that rule from paper into the database costs exactly the two lines above: a unique index nails duplicates down. Remove it and two people shouting "print it again" at once really do get two tickets: one extra order row plus one extra stock deduction.

131 / 170

But do not turn the page yet: the snippet above catches the signal after the fact, and the intuitive rewrite many people reach for is check-then-insert. Walk that version line by line with two threads, and the millisecond-wide window cannot hide any more:

132 / 170

Stepper

StepperWhy check-then-insert cannot stop concurrent double submits1 / 6

Two threads run the same snippet in true interleaved order — watch line 7: both threads reach it

Code under debug

1String key = req.getIdempotentKey();

2Order exist = orderMapper.selectByUserIdAndIdemKey(userId, key);

3if (exist != null) {

4 return OrderConverter.toVO(exist, orderItemMapper.selectByOrderId(exist.getId()));

5}

6Order order = assemble(userId, req);

7orderMapper.insert(order);

8return doCheckout(order, req);

Variables now

—

Call stack

1OrderService.createOrder(OrderService.java:38)

2OrderController.create(OrderController.java:38)

1Thread A arrives first and reads the idempotency key out of the request. Right now it is a bare string; the database knows nothing about it.

133 / 170

The verdict stands next to the code: every line of check-then-insert is fine on its own, but together they make the database a promise the database never made. Section 8.2 applies the same idea to payment callbacks, with trade_no as the line of defence.

134 / 170

Section

8.2 Payment callback idempotency: a unique constraint plus the state machine

135 / 170

A payment gateway resends callbacks often (it retries when it never receives your success ack). Two lines of defence: the unique payment.trade_no blocks a duplicate row, and the order state machine blocks a duplicate transition:

136 / 170

java

@Transactionalpublic void handleCallback(PayCallbackDTO dto) {    Payment pay = paymentMapper.selectByTradeNo(dto.getTradeNo());    if (pay != null && PayStatus.SUCCESS.name().equals(pay.getStatus())) {        log.info("duplicate payment callback, ignored tradeNo={}", dto.getTradeNo());        return;                                   // first line: already succeeded, return    }    // second line: conditional update, only CREATED may move to PAID    int rows = orderMapper.markPaid(dto.getOrderId(), OrderStatus.CREATED, OrderStatus.PAID);    if (rows == 0) {        log.warn("order status does not allow payment, ignoring orderId={}", dto.getOrderId());        return;    }    paymentMapper.updateStatus(dto.getTradeNo(), PayStatus.SUCCESS);    inventoryService.commitLocked(dto.getOrderId());   // convert locked stock to deducted}

137 / 170

The three idempotency points and their key fields:

138 / 170

Table

Scenario	Idempotency key	Constraint
Checkout	`order.idempotent_key` (client UUID)	unique `(user_id, idempotent_key)`
Payment callback	`payment.trade_no` (gateway number)	unique `uk_payment_trade_no` + status check
Stock deduction	`inventory_log` `(ref_id, type)`	unique `uk_invlog_ref_type`

139 / 170

Key point

idempotency is essentially "constrain a mutable operation with an immutable fact". trade_no and idempotent_key are externally given and inherently unique; make them unique indexes and you get a defence that is concurrency-safe and independent of application logic.

140 / 170

Section

9. Paging and query conventions

141 / 170

List endpoints return a PageResult<T>, never a bare array — the frontend needs total to render a pager:

142 / 170

java

package com.beeorder.common.response;import java.util.List;public class PageResult<T> {    private List<T> list;    private long total;        // total rows    private int page;          // current page, starting at 1    private int size;          // page size    private int pages;         // total pages    private boolean hasNext;   // whether a next page exists    public static <T> PageResult<T> of(List<T> list, long total, int page, int size) {        PageResult<T> r = new PageResult<>();        r.list = list;        r.total = total;        r.page = page;        r.size = size;        r.pages = (int) ((total + size - 1) / size);        r.hasNext = (long) page * size < total;        return r;    }    // getters / setters omitted}

143 / 170

Conditional queries gather into a single query object, keeping controller signatures free of scattered parameters:

144 / 170

java

package com.beeorder.order.dto;import java.time.LocalDateTime;public class OrderQuery {    private String status;             // optional: filter by status    private LocalDateTime beginTime;   // optional: created-at lower bound    private LocalDateTime endTime;     // optional: created-at upper bound    private Integer page = 1;    private Integer size = 20;         // server-side cap 100    private Long userId;               // injected from JWT, never from the client!    public int getOffset() {        return (Math.max(page, 1) - 1) * Math.min(size, 100);    }    // getters / setters omitted}

145 / 170

Paging parameters, fixed as a team convention:

146 / 170

Table

Parameter	Default	Cap	Note
page	1	—	starts at 1; anything below 1 is treated as 1
size	20	100	anything above 100 is truncated to 100
sort	create_time desc	whitelist	only whitelisted fields, to block injection and inefficient sorts

147 / 170

Trap

without a size cap, an attacker's single ?size=1000000 drags the whole table out and blows up both the database and your memory. The server must cap the page size; on any public endpoint this is not optional.

148 / 170

Section

10. Three traps that must go into team conventions

149 / 170

Section

10.1 order is a SQL keyword, so the table name needs back-quotes

150 / 170

sql

-- wrong: MySQL parses order as part of ORDER BY and reports a syntax errorSELECT * FROM order WHERE id = 1;      -- ERROR 1064-- right: wrap it in back-quotes to declare it an identifierSELECT * FROM `order` WHERE id = 1;

151 / 170

The same applies to MyBatis: the @TableName("order") on the entity, and every hand-written SQL in XML, must carry the back-quotes. Miss one place and you may one day see You have an error in your SQL syntax in production.

152 / 170

Section

10.2 Never use double for money

153 / 170

Section 4 demonstrated the float error. The team rule: money is always DECIMAL plus BigDecimal, and yuan-to-cent conversions use BigDecimal.movePointLeft/Right — no double anywhere.

154 / 170

Section

10.3 UTC or local time zone?

155 / 170

This is where teams argue most, so BeeOrder fixes the convention: store UTC in the database, use LocalDateTime (regarded as UTC) in the application, and render in the user's time zone at the display layer:

156 / 170

Database: set the MySQL session time_zone = '+00:00' and add connectionTimeZone=UTC to the JDBC URL
Application: use LocalDateTime/Instant consistently, never a mix with java.util.Date
Display: the frontend receives ISO-8601 strings with Z and renders in the browser's local zone

157 / 170

The payoff: a multi-region deployment never shows "the same order with different times on different machines", and daylight-saving switches do not shift anything.

158 / 170

Decision

Decisionshould BeeOrder's order table have a soft delete?

159 / 170

Section

11. Sandbox: the page-size cap as a spring

160 / 170

Section 9 settled on "cap size at 100 on the server". But where exactly should that number sit? Too small and ordinary users complain; too large and the database does your overtime — and attackers always check first whether you left it uncapped. The sandbox below turns "rows fetched per request" into three positions, and each shows response time, connection occupancy and the cost of computing total on one screen:

161 / 170

Sandbox

SandboxHow high should the page-size cap be

Server-side cap on size

Result

GET /api/v1/orders?size=100 -> 21ms
Application peak memory: 96MB
Uses idx_order_user_status_time, so the sort avoids filesort
COUNT(*) hits the covering index: 1.8ms
Anything above 100 is truncated and echoed back as size=100, so the frontend can tell

BeeOrder's choice: at most a hundred rows per page, which no one can read in one screen anyway. Truncation must be visible in the response, never silent.

162 / 170

Tip

the criterion for the cap is not "how much am I willing to give", it is "how much can one screen render, plus how much can the network carry". When a bulk export is genuinely needed, generate a file asynchronously — never turn it into a synchronous query with a large size.

163 / 170

Section

12. Check yourself

164 / 170

A warm-up on the float trap from Section 4:

165 / 170

Quiz

Check yourselfA product costs 0.1 yuan and the cart holds 3 of them. Someone sums them with `double` and gets 0.30000000000000004; switching to `BigDecimal` but writing `new BigDecimal(0.1).add(...)` still produces a long tail of decimals. Why?

Pick one — you get feedback right away

166 / 170

Then a comprehensive one, tying Sections 2, 3 and 8 together:

167 / 170

Quiz

Check yourselfA load script fires 50 concurrent POST /api/v1/orders reusing one idempotentKey with the same userId, two of which carry different items. The log shows 49 DuplicateKeyException lines. Which handling is correct?

Pick one — you get feedback right away

168 / 170

One last piece of evidence before the wrap-up. The duplicate checkout below is the log line every newcomer fears — red text, a stack trace, and yet no extra order. Treat it as an on-site inspection: who blocks, whether the block is right, and which layer of your code must catch the signal:

169 / 170

Triage

Error triageDuplicateKeyException: Duplicate entry '10015-9f2c1e0a-3b7d-4c11-8e2a' for key 'order.uk_order_idem'

The checkout endpoint throws DuplicateKeyException, yet only one order exists

The user double-clicked submit, so the second request arrives carrying the same idempotency key. This stack shows up in the log while the order count stays put — an on-site inspection, not a panic.

org.springframework.dao.DuplicateKeyException:

### Error updating database. Cause: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '10015-9f2c1e0a-3b7d-4c11-8e2a' for key 'order.uk_order_idem'

### The error may exist in com/beeorder/order/mapper/OrderMapper.java

### SQL: INSERT INTO `order` (order_no, user_id, status, idempotent_key, total_amount) VALUES (?, ?, ?, ?, ?)

at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:97)

at com.beeorder.order.service.OrderService.createOrder(OrderService.java:42)

at com.beeorder.order.web.OrderController.create(OrderController.java:38)

Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '10015-9f2c1e0a-3b7d-4c11-8e2a' for key 'order.uk_order_idem'

Click the frame you blame — guessing is allowed

No pressure: guess the exception first, then which line actually made the call.

170 / 170

Summary

this article turned the previous "contract" into two deliverables. On the schema side, we built eight tables per aggregate and explained why order items snapshot prices, why money must be DECIMAL, and why indexes only work from the leftmost prefix. On the API contract side, we completed all 15 endpoints with request/response/auth, a uniform Result<T> envelope, segmented error codes, and idempotency for checkout and payment callbacks via unique indexes plus the state machine. The foundation of BeeOrder is now complete — next article we write the core business that actually runs on top of these tables and contracts: checkout, atomic stock deduction, payment callbacks and auto-close on timeout.