Logical design describes valid facts and relationships. Physical design chooses access structures for the queries those facts must support. A nonunique index can help find rows without changing which rows are legal. A UNIQUE index also imposes an integrity rule. Adding uniqueness merely to make a query “faster” can therefore reject legitimate data and change cardinality.
The allocation history query uses equality on customer_id, a lower bound on created_at and ordering by created_at,id. A composite index (customer_id,created_at,id) groups one customer's entries before traversing their time range. The final ID breaks equal-time ties. Index order is an authored design choice, not a bag of column names: putting the range column first does not provide the same equality prefix.
Retain ORDER BY in the query. An index may help implement it, but storage access is not the result-order contract. The planner can choose another strategy, and ordering by time alone leaves ties unspecified. The fixture checks key order and query results rather than requiring a particular EXPLAIN string or a wall-clock speedup. Real performance decisions need representative cardinalities, selectivity, statistics and measured workload. Extra indexes consume storage and add maintenance work to writes.
The allocation itself is one business operation: decrease one stock row and record the matching allocation. Those two writes belong in one transaction. A later duplicate ID or missing-parent failure must not leave a stock debit behind. An index contributes no atomicity; the operation boundary does.
CREATE TABLE stock(sku TEXT PRIMARY KEY,available INTEGER CHECK(available>=0));
CREATE TABLE allocations(id INTEGER PRIMARY KEY,sku TEXT REFERENCES stock(sku),qty INTEGER);
INSERT INTO stock VALUES('A',5),('B',9);
BEGIN;
UPDATE stock SET available=available-2 WHERE sku='A';
INSERT INTO allocations VALUES(7,'A',2);
COMMIT;
SELECT sku,available FROM stock ORDER BY sku;The graded task creates its access index before beginning the business transaction, so a failed allocation does not undo that independent schema preparation. Inside the transaction, the WHERE clause must target exactly the requested SKU. The INSERT records bound values; a missing SKU is rejected by the existing foreign key even though its UPDATE affected zero rows. A CHECK rejects an insufficient balance. The harness models a client which rolls back on those errors, then inspects both tables.
This is deliberate single-connection evidence about a complete write group. It does not exercise two competing inventory requests, PostgreSQL isolation, SQLite busy handling, file-backed crash recovery or production WAL behavior. Those require their actual engine, connections and failure conditions. The existing SQL transaction prerequisite supplies syntax; this lesson asks how the business invariant determines the boundary and how the workload determines an index.
Repair the broad UPDATE and the misordered index in the bugs. In the build, include old history, use an inclusive cutoff and preserve unrelated rows. A successful allocation with a wrong report or a failed allocation with an escaped debit is still a failed contract.