Project Spec — Aurora Commerce

Single source of truth for the Aurora Commerce course module. The course (src/content/projects/aurora-commerce.mdx) must teach toward exactly this runnable result. Spotlight: PostgreSQL (ACID checkout). Backends: Go (default) + Spring Boot/Kotlin — same contract.

1. Overview & definition of done

Aurora Commerce is the order pipeline for a small store: a product catalog (with categories and images), a server-side cart (created anonymously on demand, identified by a token, claimed at checkout), and a checkout that reads the cart, decrements inventory, and creates an order in one ACID transaction. The thesis is that overselling, partial orders, and wrong totals are made structurally impossible by the database, not merely unlikely — and that this is true whether the API is written in Go or Kotlin.

Definition of done — a learner has finished when, on a clean machine with Docker + their chosen toolchain:

1. docker compose up -d brings up Postgres; migrations apply; the seed creates 3 categories, 3 products (each with a category and one placeholder image) and one customer.
2. The API (Go or Spring) starts, composing routes + middleware + graceful shutdown in one entrypoint.
3. GET /products returns the 3 seeded products as JSON, each with its category object and images list.
4. POST /checkout with the X-Cart-Token of a non-empty cart (+ {"customerId":1}) returns 200 {"orderId": N}; the order + items exist, stock is decremented by exactly the purchased quantity, and the cart is claimed (customer_id set, status = 'converted').
5. POST /checkout for more than available stock returns 409 {"error":"out_of_stock"} and leaves stock and the cart unchanged (the whole transaction rolled back; the cart stays active).
6. Re-sending the same POST /checkout with the same Idempotency-Key returns the original order, not a second one — even though the successful attempt already converted the cart (§5.3 replay ordering).
7. GET /orders/{id} returns the created order with its line items.
8. The concurrency test (20 parallel buyers, each checking out their own single-line cart, stock = 1) yields exactly 1 success + 19 out-of-stock, final stock = 0.
9. The integration test (go test / ./gradlew test) passes against a real Postgres.
10. The cart round-trip works: POST /cart/items with no X-Cart-Token creates an anonymous cart and returns its token in the X-Cart-Token response header; re-adding the same productId merges (quantities sum); GET /cart prices the lines live with correct integer-cent totals (lineTotal = unitPrice * qty, total = sum(lineTotal)); an unknown productId is a 404 and a qty below 1 a 422 (§5.2).

How they see it run locally, for $0: everything is local Docker Postgres (postgres:16) and a free toolchain. No cloud account is required to reach “done” — the Cloud Run + Cloud SQL step is an optional, clearly-marked deploy. The AI feature modules use a free Google AI Studio key. Costs nothing.

2. Architecture (diagram-in-prose)

[ Mobile client ]                 (Jetpack Compose | Flutter | SwiftUI — one of)
   GET /products  ──────────────┐
   POST/GET/PUT/DELETE /cart… ──┤  (X-Cart-Token header)
   POST /checkout ──────────────┤  (+Idem-Key, X-Cart-Token)
   GET /orders/{id} ────────────┤
                                ▼
                       [ HTTP API: Go net/http  |  Spring Boot/Kotlin ]
                         router + trace-id middleware + JSON edge
                                │  calls the Store/Service contract
                                ▼
                       [ Store (Go) / CheckoutService (Spring) ]
                         checkout = ONE transaction (BEGIN…COMMIT)
                                │  pgx (Go) | JdbcTemplate (Spring)
                                ▼
                       [ PostgreSQL 16 ]  ◀── the load-bearing part
                         categories, products, product_images,
                         customers, carts, cart_items, orders, order_items
                         CHECK / FOREIGN KEY / UNIQUE constraints
                         conditional UPDATE … RETURNING = oversell guard

The database is the lesson; the backend language is a swappable shell. The catalog read, the cart endpoints, and the order read are thin. The checkout is the spotlight: a single transaction in which the conditional UPDATE … WHERE stock >= qty RETURNING unit_price both guards oversell and captures price atomically (one guarded UPDATE per cart line), the cart is claimed (customer_id set, status = 'converted'), the order + items are inserted, the idempotency key is persisted inside the same tx, and either everything commits or everything rolls back.

The contract (§5) is identical across both backends. A mobile client written against one backend works unchanged against the other.

3. Runnable structure (the repo the learner ends with)

3.1 Go (default)

aurora-api/
  go.mod                      # module github.com/you/aurora-api  (Go 1.25+; current pgx/v5 requires it — main.go's cmp.Or only needs 1.22, the floor comes entirely from pgx)
  docker-compose.yml          # postgres:16, POSTGRES_PASSWORD=dev, POSTGRES_DB=aurora
  db/
    migrations/
      0001_init.up.sql        # categories, products, product_images, customers, carts, cart_items, orders, order_items (+ orders.idempotency_key + partial unique index)
      0001_init.down.sql
      0002_seed.up.sql        # 3 categories + 3 products (+1 placeholder image each) + 1 customer (the prerequisite FK row)
      0002_seed.down.sql
  internal/
    store/
      store.go                # type Store (wraps a pgxpool); Products; cart ops; Order; Checkout
      errors.go               # var ErrOutOfStock; var ErrNotFound; var ErrCartNotFound
    httpapi/
      router.go               # NewRouter(store) *http.ServeMux  — all routes
      products.go             # GET /products
      cart.go                 # POST/GET/PUT/DELETE cart endpoints (X-Cart-Token header)
      checkout.go             # POST /checkout (decode {customerId}, X-Cart-Token + Idempotency-Key, map errors)
      orders.go               # GET /orders/{id}
      middleware.go           # trace-id middleware (context-carried)
      json.go                 # writeJSON helper
  cmd/api/
    main.go                   # func main → run(): compose pool+store+router, ListenAndServe, graceful shutdown
  store_integration_test.go   # checkout money/stock + concurrency (real Postgres)

The container image is produced by Cloud Buildpacks when the course deploys with gcloud run deploy --source . — no Dockerfile is required. A hand-written multi-stage Dockerfile (→ distroless static) is an optional refinement, not a step the course takes.

Entrypoint that COMPOSES everything (cmd/api/main.go):

func main() { if err := run(); err != nil { slog.Error("fatal", "err", err); os.Exit(1) } }

func run() error {
    ctx, stop := signal.NotifyContext(context.Background(), os.Interrupt, syscall.SIGTERM)
    defer stop()

    logger := slog.New(slog.NewJSONHandler(os.Stdout, nil))
    slog.SetDefault(logger)

    pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))   // open the pool once
    if err != nil { return err }
    defer pool.Close()

    srv := &http.Server{
        Addr:    ":" + cmp.Or(os.Getenv("PORT"), "8080"),       // Cloud Run injects PORT
        Handler: httpapi.NewRouter(&store.Store{Pool: pool}),   // import "github.com/you/aurora-api/internal/httpapi"; routes + trace-id middleware
    }
    go func() {
        if err := srv.ListenAndServe(); err != nil && !errors.Is(err, http.ErrServerClosed) {
            slog.Error("listen", "err", err)
        }
    }()
    <-ctx.Done()
    sc, cancel := context.WithTimeout(context.Background(), 10*time.Second)
    defer cancel()
    return srv.Shutdown(sc)
}

Key Go type — the Store contract (a Store type wrapping a pgxpool, with these methods; consumed by the handlers and the spotlight step). An interface is optional — the course uses the concrete type directly:

type Line struct { ProductID int64; Qty int }   // internal: a cart line as read inside the checkout tx

// Store has these methods (here, a concrete type over a pgxpool.Pool):
//   Products(ctx) ([]Product, error)                                 // catalog + category + images
//   AddCartItem(ctx, token string, productID int64, qty int) (Cart, string, error)  // "" token → create an anonymous cart; returns the cart + its token
//   CartByToken(ctx, token string) (Cart, error)                     // GET /cart (live advisory prices)
//   SetCartItem(ctx, token string, productID int64, qty int) (Cart, error)          // PUT: idempotent absolute set
//   RemoveCartItem(ctx, token string, productID int64) (Cart, error) // DELETE: idempotent remove
//   Order(ctx, id int64) (Order, error)                              // GET /orders/{id}
//   // Checkout is ONE transaction: read the cart's lines, run the guarded UPDATE per line, claim the
//   // cart (customer_id, status='converted'), insert order + items, persist the idempotency key.
//   // idemKey may be ""; if set, a replay returns the original order (§5.3 replay ordering).
//   Checkout(ctx, customerID int64, cartToken string, idemKey string) (int64, error)

Checkout returns ErrOutOfStock (→ 409) when any line can’t be satisfied, and ErrCartNotFound (→ 404 cart_not_found) when the token resolves no active cart; the handler maps them. An empty cart is a 422 before the transaction opens. A replay with a known idemKey returns the original orderID (no second order) — the handler checks the key before resolving the cart (§5.3).

Base JSON edge — the byte-parity writeJSON helper (internal/httpapi/json.go): every response (the base §5.1–§5.4 bodies and every §8 module) is written through this one helper, which disables HTML escaping and trims the encoder’s trailing newline so the bytes match Spring/Jackson exactly (Jackson emits literal &/</> and no trailing newline by default). This is a base requirement, not a module refinement: the base GET /products already carries the free-text name (§5.1), so a store named Tees & Mugs <50% off> diverges under Go’s default json.NewEncoder(w).Encode(v), which HTML-escapes to \u0026/\u003c/\u003e and appends \n (json.Marshal alone drops the newline but still escapes, so it is not sufficient). Compile-verified go1.26.4 (see §7 “JSON edge” row, §8.s4):

func writeJSON(w http.ResponseWriter, status int, v any) {
    var buf bytes.Buffer
    enc := json.NewEncoder(&buf)
    enc.SetEscapeHTML(false)
    _ = enc.Encode(v)
    w.Header().Set("Content-Type", "application/json")
    w.WriteHeader(status)
    _, _ = w.Write(bytes.TrimRight(buf.Bytes(), "\n"))
}

3.2 Spring Boot / Kotlin (2nd backend, same contract)

aurora-api/
  build.gradle.kts            # spring-boot-starter-web, -jdbc, postgresql, flyway-core; test: testcontainers
  src/main/resources/
    application.properties     # spring.datasource.url/username/password from env; flyway on
    db/migration/
      V1__init.sql             # categories, products, product_images, customers, carts, cart_items, orders, order_items (+ orders.idempotency_key + partial unique index)
      V2__seed.sql             # 3 categories + 3 products (+1 placeholder image each) + 1 customer  (or a CommandLineRunner seeder)
  src/main/kotlin/.../
    AuroraApplication.kt       # @SpringBootApplication entrypoint (Spring composes the server + shutdown)
    web/
      ProductController.kt     # GET /products
      CartController.kt        # POST/GET/PUT/DELETE cart endpoints (X-Cart-Token header)
      CheckoutController.kt    # POST /checkout ({customerId} + X-Cart-Token + Idempotency-Key headers → service → 200/409)
      OrderController.kt       # GET /orders/{id}
      ApiExceptionHandler.kt   # @RestControllerAdvice: OutOfStockException→409, NotFound→404
      TraceIdFilter.kt         # OncePerRequestFilter → MDC traceId
    service/
      CheckoutService.kt       # @Transactional checkout(customerId, cartToken, idemKey): Long
    OutOfStockException.kt
  src/test/kotlin/.../
    CheckoutIntegrationTest.kt # @SpringBootTest + Testcontainers postgres:16

Entrypoint: Spring Boot’s @SpringBootApplication main composes the embedded server, the DispatcherServlet, the TraceIdFilter, and graceful shutdown (server.shutdown=graceful, on by default in Boot 3). @Transactional is the declarative transaction boundary — the DB does the same work as Go.

Version floor for structured logging: the §7 trace-id mechanism logging.structured.format.console=ecs requires Spring Boot 3.4+ (built-in structured logging — the ECS/Logstash/GELF formatters — was introduced in 3.4.0; on 3.0–3.3 the property is silently ignored, no error, no structured output). On Spring Boot < 3.4, use net.logstash.logback:logstash-logback-encoder + a logback-spring.xml instead.

Parity note: the Spring CheckoutService.checkout(customerId, cartToken, idemKey) signature mirrors the Go Store.Checkout; the controller maps OutOfStockException→409, CartNotFoundException→404 cart_not_found, and a duplicate idemKey→the original order.

4. Data model

All money is BIGINT cents (never float). Identity PKs. created_at is timestamptz.

4.1 Tables

CREATE TABLE categories (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name      TEXT NOT NULL,
  slug      TEXT NOT NULL UNIQUE,                          -- UNIQUE so the seed's ON CONFLICT (slug) is idempotent
  image_url TEXT                                           -- the category's single image (§5.1); seeded with the placeholder
);

CREATE TABLE products (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        TEXT    NOT NULL UNIQUE,                     -- UNIQUE so the seed's ON CONFLICT (name) is idempotent
  unit_price  BIGINT  NOT NULL CHECK (unit_price >= 0),    -- cents
  stock       INTEGER NOT NULL CHECK (stock >= 0),
  category_id BIGINT  NOT NULL REFERENCES categories(id)   -- every product has a category → categories are seeded first (§4.3)
);

CREATE TABLE product_images (
  id         BIGINT  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id BIGINT  NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  url        TEXT    NOT NULL,                             -- a reference the client fetches; the base never serves image bytes (§5.1 note)
  position   INTEGER NOT NULL DEFAULT 0,                   -- gallery order (§5.1 images sorted ascending)
  UNIQUE (product_id, position)                            -- also the seed's ON CONFLICT guard
);

CREATE TABLE customers (
  id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT UNIQUE NOT NULL
);

CREATE TABLE carts (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  token       UUID   NOT NULL DEFAULT gen_random_uuid() UNIQUE,  -- the X-Cart-Token identity (§5.2); gen_random_uuid() is core PostgreSQL (13+) — no extension
  customer_id BIGINT NULL REFERENCES customers(id),        -- NULL while anonymous; set when checkout claims the cart (§5.3)
  status      TEXT   NOT NULL DEFAULT 'active'
                CHECK (status IN ('active','converted','abandoned')),  -- 'abandoned' is reserved: no base producer, like orders' 'shipped'
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()           -- touched by every cart write; wire rule §5.2/§7
);

CREATE TABLE cart_items (
  cart_id    BIGINT  NOT NULL REFERENCES carts(id) ON DELETE CASCADE,
  product_id BIGINT  NOT NULL REFERENCES products(id),     -- FK: a cart can only ever hold real products (§5.2 404 mapping)
  qty        INTEGER NOT NULL CHECK (qty > 0),
  PRIMARY KEY (cart_id, product_id)                        -- one line per product; POST /cart/items merges via ON CONFLICT (§5.2)
);

CREATE TABLE orders (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id),   -- prerequisite FK row MUST exist
  total       BIGINT NOT NULL CHECK (total >= 0),         -- cents
  status      TEXT NOT NULL DEFAULT 'pending'
                CHECK (status IN ('pending','shipped','cancelled')),
  idempotency_key TEXT,                                   -- nullable; partial unique index (§4.2) allows many NULLs
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  order_id    BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id  BIGINT NOT NULL REFERENCES products(id),
  quantity    INTEGER NOT NULL CHECK (quantity > 0),
  unit_price  BIGINT  NOT NULL CHECK (unit_price >= 0),   -- price captured AT purchase time
  PRIMARY KEY (order_id, product_id)
);

4.2 Idempotency (part of the init migration: 0001 / V1)

The idempotency_key column and its partial unique index ship inside the init migration (0001_init / V1__init) alongside the tables — the checkout transaction needs to write the key in the same statement that creates the order, so the column exists from the first migration:

-- orders.idempotency_key TEXT (declared on the orders table in 0001_init / V1__init):
CREATE UNIQUE INDEX orders_idem_key ON orders (idempotency_key)
  WHERE idempotency_key IS NOT NULL;   -- partial: many NULLs allowed, set keys unique

This column holds only the checkout key that created the order. The cancel endpoint (§5.6) stores no key — its replay-safety is derived from order state (a second cancel finds the order already 'cancelled'), so there is no second idempotency store and the checkout key is never overwritten. The cart endpoints (§5.2) likewise store no key: PUT /cart/items/{productId} is an absolute set (a retry converges on the same state — it is the cart’s idempotent op), and the additive POST /cart/items merge is deliberate (a re-add means “one more”), so the checkout key remains the only idempotency store.

4.3 Prerequisite / seed rows (REQUIRED — the path breaks without them)

orders.customer_id is NOT NULL REFERENCES customers(id), so a customer row must exist before the first checkout — and products.category_id is NOT NULL REFERENCES categories(id), so categories are seeded before products. The seed creates the customer, the category tiles, a small catalog, and one placeholder image per product (verified against postgres:16; idempotent on a re-run — counts stay 3/3/3/1):

-- seed migration  (Go: 0002_seed; Spring: V2__seed.sql, or a CommandLineRunner)
INSERT INTO customers (email) VALUES ('demo@aurora.test')
  ON CONFLICT (email) DO NOTHING;

-- categories FIRST: products.category_id is NOT NULL (§4.1). All images share the one placeholder URL —
-- a plain migration file has no variables, so the literal repeats.
INSERT INTO categories (name, slug, image_url) VALUES
  ('Drinkware',   'drinkware',   'https://upload.wikimedia.org/wikipedia/commons/thumb/3/37/Schenker_VIA14_Laptop_asv2021-01.jpg/500px-Schenker_VIA14_Laptop_asv2021-01.jpg'),
  ('Apparel',     'apparel',     'https://upload.wikimedia.org/wikipedia/commons/thumb/3/37/Schenker_VIA14_Laptop_asv2021-01.jpg/500px-Schenker_VIA14_Laptop_asv2021-01.jpg'),
  ('Accessories', 'accessories', 'https://upload.wikimedia.org/wikipedia/commons/thumb/3/37/Schenker_VIA14_Laptop_asv2021-01.jpg/500px-Schenker_VIA14_Laptop_asv2021-01.jpg')
  ON CONFLICT (slug) DO NOTHING;

-- products resolve their category BY SLUG (never a hardcoded id); a re-run (e.g. a CommandLineRunner on
-- every boot) keeps exactly 3 products (DoD #3)
INSERT INTO products (name, unit_price, stock, category_id)
SELECT v.name, v.unit_price, v.stock, c.id
FROM (VALUES
  ('Aurora Mug',          1499,  50, 'drinkware'),
  ('Aurora Tee',          2999,  12, 'apparel'),
  ('Aurora Sticker Pack',  499, 200, 'accessories')
) AS v(name, unit_price, stock, slug)
JOIN categories c ON c.slug = v.slug
ON CONFLICT (name) DO NOTHING;

-- one placeholder image per product at position 0 (the §5.1 images list)
INSERT INTO product_images (product_id, url, position)
SELECT p.id, 'https://upload.wikimedia.org/wikipedia/commons/thumb/3/37/Schenker_VIA14_Laptop_asv2021-01.jpg/500px-Schenker_VIA14_Laptop_asv2021-01.jpg', 0
FROM products p
ON CONFLICT (product_id, position) DO NOTHING;

The demo customer’s id (1 on a fresh DB) is the customerId the checkout body sends (§5.3). Production would create or look up the customer; for the course the seeded demo customer is the canonical customerId.

4.4 Indexes

• PKs cover the catalog and order reads.
• orders_idem_key (partial unique) enforces idempotency.
• order_items (order_id, product_id) PK covers the order-read join.
• carts.token UNIQUE backs the X-Cart-Token lookup; cart_items (cart_id, product_id) PK covers the cart read join and is the ON CONFLICT target of the §5.2 upserts.
• product_images (product_id, position) UNIQUE covers the ordered per-product image read (§5.1). The base declares no index on products.category_id (a 3-row catalog); the discovery module adds one for its category facet (§8.d1).
• (Optional, ai-recipe) CREATE EXTENSION pg_trgm; + a GIN trigram index on products.name for fuzzy match.

4.5 Migrations

Numbered, immutable files. Go: golang-migrate (db/migrations/000N_*.up.sql / .down.sql) — 0001_init (tables + the idempotency column and index) then 0002_seed. Spring: Flyway (db/migration/V1__init.sql with the idempotency column and index, then V2__seed.sql). Never edit an applied migration — add a new one.

5. API & event contract (canonical — every step, client, and test shares this)

Base URL local: http://localhost:8080. All bodies are JSON; money is integer cents; field names are camelCase on the wire.

5.1 GET /products

• 200 → array of products. Each product carries its category (always present — products.category_id is NOT NULL, §4.1; the object is {id, slug, name, imageUrl}) and its images, ordered by position ascending ([] for a product with no image rows — the §7 empty-array rule):

[ { "id": 1, "name": "Aurora Mug", "unitPrice": 1499, "stock": 50,
    "category": { "id": 1, "slug": "drinkware", "name": "Drinkware",
                  "imageUrl": "https://upload.wikimedia.org/wikipedia/commons/thumb/3/37/Schenker_VIA14_Laptop_asv2021-01.jpg/500px-Schenker_VIA14_Laptop_asv2021-01.jpg" },
    "images": [ { "url": "https://upload.wikimedia.org/wikipedia/commons/thumb/3/37/Schenker_VIA14_Laptop_asv2021-01.jpg/500px-Schenker_VIA14_Laptop_asv2021-01.jpg" } ] } ]

OPTIONAL — serving the images yourself (not a base step, like the Cloud Run deploy in §6 step 15): the base only stores and returns the image reference (url); the seeded placeholder points at an existing public image, so nothing more is required to reach the DoD. As a clearly-marked extension, the backend can itself be the image origin — a GET /images/{key} endpoint serving a file bundled with the app — with a CDN in front, so clients fetch the CDN URL (that is what goes in product_images.url) and the origin is hit only on a CDN miss; swapping the bundled file for an object-storage read is the scale variant. None of this changes the contract above: clients always just fetch url.

5.2 The cart — server-side, anonymous-on-demand, claimed at checkout

The cart lives in Postgres (carts + cart_items, §4.1) and is identified by an opaque UUID token carried in the X-Cart-Token request header. There is no login: a cart starts anonymous (customer_id NULL) and checkout claims it (§5.3). A token resolves only an active cart — once checkout converts it, the token stops resolving (404 cart_not_found, below). Cart reads are advisory: prices are re-resolved live from the catalog on every read and may go stale a moment later — the checkout (§5.3) is where truth is fixed atomically. The cart endpoints take no Idempotency-Key (§4.2): PUT is the idempotent op; POST is deliberately additive.

The cart representation — the 200 body of every cart endpoint. total and lineTotal are server-computed integer cents (lineTotal = unitPrice * qty, total = sum(lineTotal)); an empty cart is a valid 200 with "items": [] (the §7 empty-array rule); updatedAt follows the §5.4 wire rule (RFC3339 UTC Z, no sub-second) and moves on every cart write:

{ "total": 2998, "updatedAt": "2026-01-01T00:00:00Z",
  "items": [ { "productId": 1, "name": "Aurora Mug", "qty": 2, "unitPrice": 1499, "lineTotal": 2998 } ] }

The token travels in headers only: requests send X-Cart-Token, and every cart response repeats the cart’s token in an X-Cart-Token response header — never in the JSON body, which stays uniform. On creation, that response header is where the client first learns its token; the client persists it and sends it on every later cart/checkout call.

• POST /cart/items — add a line (and create the cart when needed). Body { "productId": 1, "qty": 2 }; the X-Cart-Token header is optional here (only here): absent → the server creates a new anonymous cart — the token is minted by the column default gen_random_uuid() (§4.1), never by app code — and adds the line to it; present → the line is added to that cart. A re-add of the same productId merges (quantities sum) in one statement, no read-modify-write:

INSERT INTO cart_items (cart_id, product_id, qty) VALUES ($1, $2, $3)
ON CONFLICT (cart_id, product_id) DO UPDATE SET qty = cart_items.qty + EXCLUDED.qty;

• GET /cart — read the cart (X-Cart-Token required): the items with product name and current catalog price plus the server-computed totals — the representation above. One join, prices live:

SELECT ci.product_id, p.name, ci.qty, p.unit_price, ci.qty * p.unit_price AS line_total
FROM carts c
JOIN cart_items ci ON ci.cart_id = c.id
JOIN products   p  ON p.id = ci.product_id
WHERE c.token = $1 AND c.status = 'active'
ORDER BY ci.product_id;

• PUT /cart/items/{productId} — body { "qty": 3 } (qty ≥ 1): the idempotent absolute set — a retry converges on the same state, which is why the cart needs no Idempotency-Key (§4.2). It upserts, so it also works for a line not yet in the cart:

INSERT INTO cart_items (cart_id, product_id, qty) VALUES ($1, $2, $3)
ON CONFLICT (cart_id, product_id) DO UPDATE SET qty = EXCLUDED.qty;

• DELETE /cart/items/{productId} — remove the line. Naturally idempotent: deleting a line that is not in the cart is a no-op 200 (the requested state already holds). A line leaves the cart only through this endpoint — PUT with qty: 0 is a 422, matching the cart_items.qty > 0 CHECK (§4.1).

Every cart write also touches the cart row (UPDATE carts SET updated_at = now() WHERE id = $1) in the same transaction as the item write, so updatedAt moves on every mutation.

Errors (all four endpoints):

5.3 POST /checkout

• Request headers: X-Cart-Token: <the cart's token> (required — the cart being checked out, §5.2); Idempotency-Key: <client-generated UUID> (optional but recommended; one key per logical attempt, reused across retries).
• Request body:

{ "customerId": 1 }

The client sends no lines — the server reads the cart’s cart_items as the checkout lines. In production customerId would come from the authenticated session; the course builds no auth, so the seeded demo customer’s id (§4.3) is sent explicitly.

One transaction. The server resolves the active cart by token, then in a single transaction: (1) per cart line, the unchanged guarded UPDATE products SET stock = stock - $1 WHERE id = $2 AND stock >= $1 RETURNING unit_price — the oversell guard and the atomic true-price capture (the spotlight, untouched); (2) claims the cart — UPDATE carts SET customer_id = $1, status = 'converted', updated_at = now() WHERE id = $2 AND status = 'active' — so the anonymous cart becomes the customer’s at the same instant the stock moves; (3) inserts the order + order_items (line prices from the RETURNING, never re-read); (4) persists the Idempotency-Key on the order row. Everything commits or rolls back together.

Duplicate lines cannot occur: cart_items holds one row per product (PRIMARY KEY (cart_id, product_id), §4.1) — merging happened at add time (§5.2) — so the order_items (order_id, product_id) PK is safe by construction and no aggregation step is needed.

• 200 → order created (or the original order, on an idempotent replay):

{ "orderId": 4821 }

• 409 → at least one line exceeds available stock; nothing was written (whole tx rolled back — the stock, the order tables, and the cart are all untouched; the cart stays active so the shopper can adjust and retry):

{ "error": "out_of_stock" }

• 422 → the cart is empty (the resolved cart has no cart_items rows), or customerId is missing:

{ "error": "invalid_request" }

• 404 (cart_not_found) → the X-Cart-Token header is absent, unknown, or resolves a cart that is not active (e.g. already converted): { "error": "cart_not_found" }. See the replay ordering below for why a retry of an already-successful checkout does not land here.

• 404 (not_found) → customerId does not exist: { "error": "not_found" }. The canonical checkout writes the order with customer_id and relies on the orders.customer_id foreign key (§4.1); a missing customer surfaces as a 23503 FK violation, which both backends map to this 404 (see §7 “Unknown customer → 404”). An unknown productId, by contrast, can no longer reach checkout in the base flow: every checkout line comes from cart_items, whose product_id is itself a foreign key onto products (§4.1), so the missing-product case is caught where the client supplies the id — POST /cart/items (404 via the same 23503 mapping, §5.2). The guarded UPDATE’s semantics are unchanged: it touches 0 rows for a missing product exactly as it does for insufficient stock, so any caller that drives the guard directly with client-supplied ids (e.g. the order-lifecycle reservations, §8.o2) still folds that case into 409 out_of_stock.

• 500 → an unmapped server error: { "error": "internal" } (see §5.5). Both backends return this exact body on any failure not covered above.

Idempotency semantics — and the replay ordering, load-bearing now that checkout consumes the cart: if the Idempotency-Key matches an existing order, return that order’s id with 200 and make no DB change. Because a successful checkout converts the cart, a retry of that same request would no longer resolve the token — so when a key is present, the handler looks it up first and replays the original order before resolving the cart; only a key miss proceeds to the cart and the transaction (this is what makes DoD #6 hold after the cart is claimed). The in-tx key persist stays the race-safe backstop: two concurrent first attempts both reach the insert, the loser hits orders_idem_key (23505), rolls back — releasing its stock decrement and cart claim — and replays the winner’s order (§4.2, §7 “Idempotency”).

5.4 GET /orders/{id}

• 200 → the order with its line items:

{
  "id": 4821,
  "customerId": 1,
  "total": 5998,
  "status": "pending",
  "createdAt": "2026-01-01T00:00:00Z",
  "items": [ { "productId": 2, "quantity": 2, "unitPrice": 2999 } ]
}

• 404 → no such order, or a non-numeric {id} (e.g. /orders/abc): { "error": "not_found" }. Both backends’ path matchers must agree on this — a non-numeric {id} is a 404, never a 400 or a 500. Go returns 404 naturally: http.ServeMux’s GET /orders/{id} matches any segment (including abc), so the handler owns strconv.ParseInt and returns 404 not_found on a parse failure (compile-verified go1.26.4: the mux + r.PathValue("id") builds and matches /orders/abc). Spring’s default @GetMapping("/orders/{id}") @PathVariable Long id binding deviates — a non-numeric segment throws MethodArgumentTypeMismatchException, which Spring MVC’s DefaultHandlerExceptionResolver maps to 400 Bad Request (Spring’s documented default), not 404. The default therefore MUST be overridden to match Go’s 404, via one of two sanctioned mechanisms: (a) bind @PathVariable String id and parse to Long in the controller, returning 404 {"error":"not_found"} on parse failure (mirroring Go’s parse-in-handler); or (b) register @ExceptionHandler(MethodArgumentTypeMismatchException::class) in ApiExceptionHandler returning 404 {"error":"not_found"}. (The Spring mechanism is reasoned — no JVM in this authoring env — so no compiled Spring snippet is emitted beyond these two idioms; the Go 404 is compile-verified.) This same non-numeric-{id}→404 pin applies to every {id} path: the cart’s PUT/DELETE /cart/items/{productId} (§5.2), POST /orders/{id}/cancel (§5.6), the order-lifecycle transitions (§8.o2/§8.o3), and discovery’s GET /products/{id}/related (§8.d4) — see the §7 “Non-numeric {id} → 404” parity row.

• createdAt wire format: an RFC3339 / ISO-8601 UTC instant with a trailing Z and no sub-second digits, e.g. 2026-01-01T00:00:00Z. The cart’s updatedAt (§5.2) follows this exact same rule. Both backends emit this exact form (see §7 “Order read / createdAt wire format”): Go forces UTC and second precision before marshal — t.UTC().Format(time.RFC3339) as a string field (plain .UTC() + default time.Time JSON marshals as RFC3339Nano and keeps the fraction, breaking this rule); Spring reads the column as OffsetDateTime/Instant, truncates to seconds (instant.truncatedTo(java.time.temporal.ChronoUnit.SECONDS)), and serializes in UTC with the Z designator (UTC config alone keeps the fraction).

• items is a non-nil array (the base [] rule): the Go handler initialises items as []OrderItem{} before the scan, so any array-valued response serializes as [], never null — matching Spring’s non-null List (§7 “Empty arrays” rule). (An order always has ≥ 1 line, so items is non-empty in practice; the rule fixes the wire type regardless.)

5.5 Error code table (shared)

5.6 Optional feature endpoints

• ai-recipe: POST /cart/from-recipe { "recipe": string } → { "items":[{ "productId": int, "name": string, "quantity": int, "confidenceX1000": int }], "unmatched":[string] }. Returns a proposal the client confirms — confirmed items enter the cart through the normal POST /cart/items (§5.2); this endpoint itself never writes to the cart.

  • quantity is a cart-ready integer ≥ 1. The Gemini parse step types the free-text recipe amount as a string (e.g. "2 eggs", "a pinch"), but everything downstream — the cart (§5.2, cart_items.qty INTEGER NOT NULL CHECK (qty > 0)), checkout (§5.3), order items (§5.4), and order_items.quantity (§4.1, INTEGER NOT NULL CHECK (quantity > 0)) — requires an integer. So the server normalises the parsed string amount to an integer quantity before the item can enter the cart: a leading numeric amount is rounded to its integer count; a non-numeric amount ("a pinch") defaults to 1 for the shopper to adjust. This closes the string→integer gap on the recipe → cart → checkout path; quantity never reaches the cart as a string.
  • confidenceX1000 is the pg_trgm trigram similarity of the matched products.name scaled to an integer — confidenceX1000 = round(similarity(products.name, $ingredient) * 1000)::bigint in the trigram query (the same scaled-integer construction as the §8.i8 supportX1000/liftX1000 ratios), so no raw float reaches the wire on either backend: Go marshals the int64 field, Spring/Kotlin the Long field — byte-identical by construction. A raw [0, 1] float would be a Go/Jackson byte-parity hazard (compile evidence, go1.26.4: json.Marshal of the same trigram value as float32 vs float64 diverges — 0.42857143 vs 0.4285714328289032), exactly the divergence §8.i8 avoids by scaling every ratio ×1000; hence confidence is not emitted as a bare float. A match threshold compares the scaled integer (e.g. keep matches with confidenceX1000 >= 300, i.e. similarity ≥ 0.3). (This is one contract change spanning BOTH backends and both course paths — the Go trigram query and the Kotlin field type — applied together, not split.)
  • 422 → empty/blank recipe: { "error": "invalid_request" }.
  • 502 → the upstream Gemini call fails or times out: { "error": "upstream_unavailable" }.

• ai-support: POST /support { "question": string } (Gemini function calling over three read-only tools). The 200 body is a union of two shapes, discriminated by the presence of requiresConfirmation:

  • answer — a plain reply: { "answer": string }. requiresConfirmation is absent.
  • proposal — a cancel the shopper must confirm: { "action": "cancel", "orderId": number, "summary": string, "requiresConfirmation": true }. requiresConfirmation is always true when present. action is "cancel" only — the sole write this module can confirm is POST /orders/{id}/cancel (which returns 422 on any action != "cancel", below), so a proposal never carries an action the module cannot resolve. Refunds are not proposed here: the refund write lives in the separate order-lifecycle module’s POST /orders/{id}/refund (§8.o3) and is out of scope for base ai-support.

  A client distinguishes the two by checking for requiresConfirmation: present (true) → render the proposal and ask for confirmation; absent → render answer. Cancel is not a callable tool — the agent only proposes it here; the real mutation lives behind the separate, idempotent, human-confirmed POST /orders/{id}/cancel below.

  • Serialization pin (presence-based discriminator). Because the client discriminates on the presence of requiresConfirmation, both backends MUST omit it from the answer shape and emit it only on a proposal — a present "requiresConfirmation":false must never occur on either backend. Go emits it only for proposals: RequiresConfirmation bool tagged json:"requiresConfirmation,omitempty" (or a *bool) — omitempty drops the false answer case. Spring uses a nullable Boolean requiresConfirmation with @JsonInclude(JsonInclude.Include.NON_NULL), not a primitive boolean (a primitive always serializes "requiresConfirmation":false, which the presence-based discriminator misreads as a proposal — a parity break with Go). (Go’s omitempty-omits-false is the verified idiom; the Spring @JsonInclude mechanism is reasoned — no JVM here — so no compiled Spring snippet beyond the struct-tag idiom is emitted.)
  • 422 → empty/blank question: { "error": "invalid_request" }.
  • 502 → the upstream Gemini call fails or times out: { "error": "upstream_unavailable" }.
  • Byte-parity (ai-recipe + ai-support free-text; deterministic fields only). Both endpoints echo free-text that can contain &, <, > — ai-recipe’s product name and the unmatched[] echoes, ai-support’s answer/summary prose. Their Go writers use the base writeJSON (SetEscapeHTML(false)
    • bytes.TrimRight(…, "\n") — the base JSON-edge fix), so all JSON structure and the deterministic free-text (the product name and the unmatched[] echoes) are byte-identical to Spring/Jackson, which emits literal &/</> and no trailing newline by default (compile-verified go1.26.4, per §8.s4: the default json.Marshal escapes Tees & Mugs <50% off> to Tees \u0026 Mugs \u003c50% off\u003e, while SetEscapeHTML(false) + trailing-\n trim emits it literally with no newline). Byte-parity is explicitly scoped OUT for non-deterministic model prose — ai-support answer/summary and the recipe amounts the model paraphrases — because the model output is not reproducible, so its bytes are not a parity target; the deterministic fields above still MUST match. (Same encoder discipline §8.s4/§8.d5 apply to storefront/discovery free-text, stated here because ai-recipe/ai-support appear earlier in the inventory.)

• ai-support write boundary: POST /orders/{id}/cancel — the only place a cancel mutates state, run only after a human confirms the agent’s proposal.

  • Request headers: Idempotency-Key: <client-generated UUID> (reused across retries, exactly like POST /checkout).

  • Request body: the confirmed proposal returned by /support:

    { "action": "cancel", "orderId": 4821, "summary": "Cancel order 4821 (Aurora Tee ×2)" }

  • Request validation (the path {id} is authoritative): the URL path {id} is the single source of truth for which order is cancelled; the body orderId is a confirmation echo and MUST equal the path {id}. A mismatch is rejected — the endpoint never cancels an order other than the one the URL names.

    • 422 → { "error": "invalid_request" } when the body is missing/malformed, when action != "cancel", or when the body orderId disagrees with the path {id}. This mirrors the 422 invalid_request guard on the sibling mutating endpoint POST /checkout (§5.3); a state-mutating cancel never proceeds on an unvalidated body, and the two order-id sources are reconciled (path wins) before any state change.

  • 200 → the cancellation applied (or the same result replayed for a duplicate Idempotency-Key): { "orderId": 4821, "status": "cancelled" }.

  • 404 → no such order: { "error": "not_found" }.

  • 409 → the order cannot be cancelled in its current state (e.g. already shipped): { "error": "not_cancellable" }.

  • State transition (pins the 200/409 branch on the enumerated orders.status of §4.1): cancel succeeds (200, status → 'cancelled') only from 'pending'; a 'shipped' order returns 409 not_cancellable; a replay returns the existing cancelled result. The branch is a function of an enumerated, model-defined value set, identical on both backends. Testing the 409 not_cancellable branch requires seeding or manually setting an order to status='shipped' (e.g. UPDATE orders SET status='shipped' WHERE id=<a seeded pending order id>), because no API path — including the order-lifecycle module (§8.o3), which drives authorized/paid/fulfilled/refunded and never sets 'shipped' — ever produces a 'shipped' order; the enumerated value has no API producer and is otherwise unreachable in any documented build/test path.

  • Inventory effect (restock): cancelling an order in 'pending' restocks each line — products.stock += order_items.quantity for every order_items row of the order — inside the same transaction that sets status = 'cancelled'. The quantities come from the existing order_items.quantity column (§4.1); no schema change is needed, only this defined rule, so the cancel’s effect on the central inventory invariant is byte-for-byte identical on Go and Spring (§7 parity). The replay path (a second cancel finding the order already 'cancelled') restocks nothing — it is a no-op read of the existing result — so a double-tapped confirm cannot double-restock.

  • Idempotency semantics: a replay with the same Idempotency-Key returns the original result and makes no second state change, so a double-tapped confirm can’t double-cancel. Replay-safety is based on order state: a second cancel finds the order already 'cancelled' and returns the same { "orderId": 4821, "status": "cancelled" } 200; the Idempotency-Key is accepted for symmetry with POST /checkout but is not the safety mechanism, and no cancel key is stored (the single orders.idempotency_key column + orders_idem_key index hold only the checkout key, §4.2).

6. Build order (dependency-ordered — each step’s prerequisites already exist)

1. Stand up Postgres locally (Docker Compose, DATABASE_URL). (common)
2. Design the schema around invariants — categories, products, product_images, customers, carts, cart_items, orders, order_items. Author it directly as the init migration file (0001_init.up.sql / V1__init.sql, §4.5), including the orders.idempotency_key column and its partial unique index (§4.2) — there is no throwaway hand-run SQL intermediate; this file is the one schema artifact consumed everywhere downstream. Apply it to the running Postgres now so the schema is live for steps 4–7: Go — psql "$DATABASE_URL" -f db/migrations/0001_init.up.sql (or docker compose exec -T db psql -U postgres -d aurora < db/migrations/0001_init.up.sql); Spring — Flyway applies V1 automatically on first app start (step 4). Step 11 later formalises this behind the runner. (common)
3. Seed the categories, the catalog (+ placeholder images) AND a customer — the prerequisite FK rows before anything else: the customer before any checkout, the categories before the products (products.category_id is NOT NULL, §4.3). Author it directly as the seed migration file (0002_seed.up.sql / V2__seed.sql, §4.5). Apply it now too (same as step 2, so the seed rows are live for steps 4–7): Go — psql "$DATABASE_URL" -f db/migrations/0002_seed.up.sql (or the docker compose exec -T db psql … form); Spring — Flyway applies V2 automatically on first app start (step 4). (common)
4. Scaffold the API + GET /products + a composed main — pool/store, router, trace-id middleware, graceful shutdown. (backend: go / spring)
5. Build the server-side cart — POST /cart/items (anonymous cart on demand; the token via the X-Cart-Token response header; the ON CONFLICT merge), GET /cart (live advisory prices + server-computed totals), PUT /cart/items/{productId} (idempotent absolute set), and DELETE /cart/items/{productId} (§5.2); acceptance-checked by DoD #10 (token round-trip, merge, integer-cent totals, 404 on unknown productId). (backend: go / spring)
6. Watch oversell become impossible — by hand — first set a product to one unit (UPDATE products SET stock = 1 WHERE id = 1;; the §4.3 seed stocks 50/12/200, none of them 1), then in two psql sessions race the conditional UPDATE on that last unit. (common)
7. ★ Checkout as one transaction — read the cart’s lines, conditional UPDATE … RETURNING unit_price per line, claim the cart (customer_id, status = 'converted'), insert order + items, persist the idempotency key in-tx. (backend: go / spring)
8. Build POST /checkout — decode {customerId}, read X-Cart-Token + Idempotency-Key, replay a known key first (§5.3), call Checkout, map 200/409/422/404 (cart_not_found + not_found); on duplicate key return the original order. (backend: go / spring)
9. GET /orders/{id} — read an order with its items. (backend: go / spring — mostly common via prompt)
10. Beat the concurrency / write-skew — when the conditional UPDATE suffices vs when SERIALIZABLE + 40001 retry is required. (common)
11. Migrations — the ordered files were first applied by hand in steps 2/3 (so the schema + seed were live for steps 4–7); this step formalises the repeatable path, it does not first-apply the schema. Wire the same files into the migration tooling and make them the repeatable path: run golang-migrate up/down (Go) or let Flyway apply V1/V2 on startup (Spring), confirm a clean up→down→up cycle, and treat applied files as immutable (add a new file, never edit one — §4.5). The schema/seed are not re-authored here; this step formalises running the single artifact from steps 2/3, not recreating it. (common)
12. Integration-test the money path — real Postgres, exact totals/stock + the 20-buyer concurrency test. (backend: go / spring)
13. Show the catalog — list screen against GET /products. (frontend: compose / flutter / swiftui)
14. Wire the cart + the checkout button — add lines via POST /cart/items and persist the X-Cart-Token from the response header; the checkout button POSTs {customerId} with the token + Idempotency-Key; on 200 navigate to confirmation (reads GET /orders/{id}); on 409 show out-of-stock. (frontend)
15. Deploy to Cloud Run + Cloud SQL — connector socket (authenticated), with the secret sourced per backend to match the §9 datasource split (the §7 “Datasource env” row): Spring sets JDBC_DATABASE_URL to the Cloud SQL socket-factory connection string — jdbc:postgresql:///aurora?cloudSqlInstance=PROJECT:us-central1:aurora-pg&socketFactory=com.google.cloud.sql.postgres.SocketFactory (application.properties reads spring.datasource.url from this var, §3.2, so a Spring learner following step 15 alone actually has a datasource URL) — plus DB_USER and the password as a standalone Secret-Manager env var, --set-secrets DB_PASSWORD=aurora-db-password:latest (separate user/password, as the JDBC driver requires), matching §9’s “JDBC_DATABASE_URL + separate DB_USER/DB_PASSWORD via the Cloud SQL JDBC SocketFactory”; there is no DB_NAME var — the database name (aurora) is carried inside JDBC_DATABASE_URL; Go takes the whole libpq DATABASE_URL (user:password embedded in the DSN) from Secret-Manager as one secret — --set-secrets DATABASE_URL=aurora-dsn:latest — and reads only that var, not a standalone DB_PASSWORD. Cloud SQL bills continuously and does not scale to zero — when done, gcloud sql instances delete aurora-pg to stop the only recurring charge (§9). (common, optional) 16+. Feature modules (off by default): ai-recipe, ai-support, observability. (feature steps)

Combining modules — migration order. Each optional module owns a fixed ascending migration range (storefront 0003/0004, order-lifecycle 0005/0006, discovery 0007/0008, sales-insights 0009/0010). When enabling more than one, add all chosen modules’ migration files before the first migrate up / app start, or enable them lowest-migration-number first — golang-migrate applies only versions above the current applied version, and Flyway (default outOfOrder=false) rejects a pending migration whose version is below the latest applied. So enabling storefront (0003/0004) after discovery (0007/0008) has already applied would leave storefront silently unapplied on Go and fail Flyway validation on Spring.

Each backend-specific milestone (4, 5, 7, 8, 9, 12) has a Go variant and a Spring variant — every chosen path is a complete build (branching-paths.md Rule 1).

7. Backends — parity points (Go default + Spring, SAME contract)

Critical parity invariant: both implement the §5 contract byte-for-byte (same paths, JSON shapes, status codes). The Go path is not a stub — it includes the HTTP /checkout, /orders/{id}, out-of-stock→409 mapping, and end-to-end idempotency, exactly like Spring.

8. Optional feature modules (off by default; extend the spec)

• ai-recipe — POST /cart/from-recipe: Gemini structured output (responseSchema) extracts ingredients → Postgres pg_trgm fuzzy match against products.name → returns a cart proposal + unmatched[]. No server-side mutation. Key server-side. Free AI Studio key.
• ai-support — POST /support: Gemini function calling over three read-only tools (get_order_status, check_stock, estimate_restock), manual loop so the server controls execution. Writes (cancel/refund) are gated behind an explicit human-confirmed POST /orders/{id}/cancel (reuses the Idempotency-Key). Free AI Studio key.
• observability — structured JSON logs + a trace id threaded from the edge into each checkout phase (decrement, total, order insert, commit/rollback, out-of-stock, idempotency hit); optional local OpenTelemetry + Grafana/Tempo (all local Docker, free). Adds a one-line “logging through the transaction” detail to the ★ checkout steps; the trace-id concept stays out of the spotlight’s basic instruction.

Each is backend-agnostic where possible (the prompt/algorithm), with a Go + Spring variant only where real backend code differs.

storefront — Storefront & Flash Sales (optional module)

Self-contained: this module stands alone on the base build, assumes no other module, and nothing in the core §4/§5 depends on it. Its schema is additive and created by the module’s own first step; the base tables (products, categories, orders, order_items, customers) are not modified — the module reads the base categories for the home page’s category tiles but creates no category objects of its own.

Summary. A real store’s home page plus the flash-sale mechanic. The module adds a denormalized read model served by one endpoint, GET /storefront, that returns the home page in a single response: active banners, the base category tiles (§4.1 categories), featured products, and live deals. A deals table carries a temporal validity window (starts_at/ends_at) and a sale_price_cents, and the effective display price is resolved in SQL with now() BETWEEN starts_at AND ends_at. The load-bearing beat: the sale changes what the shopper sees on the storefront (advisory, like the cart read §5.2), but checkout re-resolves the true price atomically inside the guarded UPDATE … RETURNING — so order_items.unit_price is the price at the transaction instant, never the stale display price (mirroring the base “price at purchase time” invariant, §4.1). The flash-sale contention extends the base guarded checkout with a global allocation cap and a per-customer purchase cap, both enforced inside the one transaction — a time-boxed drop is the base oversell race at maximum stakes (thousands racing for N units), and the caps hold with zero oversell.

Course scope note. Go is compiled (pgx v5.10.0 / Go 1.26) and the SQL is run against postgres:16; Spring/Kotlin is reasoned against Spring 6 / Boot 3.4 / JdbcTemplate (no JVM toolchain in the authoring environment). The one frontend built is a Jetpack Compose home screen (reasoned, not compiled). The module does not implement order cancellation/restock (that lives in the separate ai-support module), so a deal reservation is released only by the checkout transaction rolling back.

8.s1 Tables (module-owned; additive, idempotent CREATE TABLE IF NOT EXISTS)

Created by the module’s first step. All money is BIGINT cents; timestamps are TIMESTAMPTZ. The deals.sold <= allocation_cap CHECK is a structural backstop: even a stray UPDATE cannot oversell the drop, exactly as products.stock >= 0 backstops the catalog (§4.1).

-- Marketing banners for the home page. Banners keep their own image_url (module-owned banner art);
-- the category tiles need no module table — they are the BASE categories (§4.1), image_url included.
CREATE TABLE IF NOT EXISTS storefront_banners (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  headline   TEXT    NOT NULL,
  subhead    TEXT    NOT NULL DEFAULT '',
  cta_href   TEXT    NOT NULL DEFAULT '',
  image_url  TEXT    NOT NULL DEFAULT '',
  active     BOOLEAN NOT NULL DEFAULT true,
  sort_order INTEGER NOT NULL DEFAULT 0
);

-- Curated featured products (a thin join onto the base catalog).
CREATE TABLE IF NOT EXISTS storefront_featured (
  product_id BIGINT PRIMARY KEY REFERENCES products(id) ON DELETE CASCADE,
  sort_order INTEGER NOT NULL DEFAULT 0
);

-- Temporal deals: a time-boxed sale price + a global allocation cap for a flash drop.
CREATE TABLE IF NOT EXISTS deals (
  id               BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id       BIGINT      NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  sale_price_cents BIGINT      NOT NULL CHECK (sale_price_cents >= 0),  -- cents
  starts_at        TIMESTAMPTZ NOT NULL,
  ends_at          TIMESTAMPTZ NOT NULL,
  allocation_cap   INTEGER     NOT NULL CHECK (allocation_cap >= 0),    -- global units the drop may sell
  per_customer_cap INTEGER     NOT NULL CHECK (per_customer_cap >= 1),  -- units one customer may buy
  sold             INTEGER     NOT NULL DEFAULT 0 CHECK (sold >= 0),    -- running allocation counter
  CHECK (ends_at > starts_at),
  CHECK (sold <= allocation_cap)                                        -- oversell of the drop is impossible
);
CREATE INDEX IF NOT EXISTS deals_product_window ON deals (product_id, starts_at, ends_at);

-- Per-customer purchase ledger for a deal (enforces per_customer_cap in-transaction).
CREATE TABLE IF NOT EXISTS deal_purchases (
  deal_id     BIGINT  NOT NULL REFERENCES deals(id) ON DELETE CASCADE,
  customer_id BIGINT  NOT NULL REFERENCES customers(id),
  qty         INTEGER NOT NULL CHECK (qty > 0),
  PRIMARY KEY (deal_id, customer_id)
);

Migration placement: as the module’s own numbered files, after the base 0001/0002 — Go 0003_storefront.up.sql / .down.sql (tables) then 0004_storefront_seed.up.sql / .down.sql (demo data) for golang-migrate; Spring V3__storefront.sql then V4__storefront_seed.sql for Flyway. Each Go/Spring pair is a byte-for-byte copy (the in-course “keep the two copies from drifting” rule).

The seed migration (0004_storefront_seed / V4__storefront_seed) fills the exact home-page rows the §8.s2 example is computed from — one active banner (Aurora Summer Drop, carrying the placeholder image), one featured base product (Aurora Sticker Pack, id 3 on a fresh base seed), and one live flash deal on it (sale_price_cents 299, allocation_cap 50, per_customer_cap 2, sold 0, window open now). The module seeds no categories — the home page’s category tiles are the three base categories rows the base seed created (§4.3). It references base products by name (a subquery), never a hardcoded id, and is idempotent on re-run (verified against postgres:16): the banner and the deal guard on NOT EXISTS (neither table has a natural unique key), and the featured row uses ON CONFLICT (product_id) DO NOTHING.

-- 0004_storefront_seed.up.sql (Go) / V4__storefront_seed.sql (Spring). Idempotent on re-run.

-- (a) One active marketing banner (module-owned image_url = the placeholder).
--     No natural unique key → guard on the headline with NOT EXISTS.
INSERT INTO storefront_banners (headline, subhead, cta_href, image_url, active, sort_order)
SELECT 'Aurora Summer Drop', 'Flash deals live now — limited units', '/deals',
       'https://upload.wikimedia.org/wikipedia/commons/thumb/3/37/Schenker_VIA14_Laptop_asv2021-01.jpg/500px-Schenker_VIA14_Laptop_asv2021-01.jpg',
       true, 0
WHERE NOT EXISTS (SELECT 1 FROM storefront_banners WHERE headline = 'Aurora Summer Drop');

-- (b) Feature the base 'Aurora Sticker Pack' (product 3 on a fresh base seed). product_id is the PK.
INSERT INTO storefront_featured (product_id, sort_order)
SELECT p.id, 0 FROM products p WHERE p.name = 'Aurora Sticker Pack'
ON CONFLICT (product_id) DO NOTHING;

-- (c) One LIVE flash deal on the featured product: sale 299, cap 50, open now (starts_at < now() < ends_at).
--     No natural unique key → guard on product_id with NOT EXISTS.
INSERT INTO deals (product_id, sale_price_cents, starts_at, ends_at, allocation_cap, per_customer_cap, sold)
SELECT p.id, 299, now() - INTERVAL '1 hour', now() + INTERVAL '7 days', 50, 2, 0
FROM products p
WHERE p.name = 'Aurora Sticker Pack'
  AND NOT EXISTS (SELECT 1 FROM deals d WHERE d.product_id = p.id);

The down migration (golang-migrate only; Flyway has no down files) removes only these demo rows and leaves the base catalog (including the base categories) untouched: DELETE FROM deals WHERE product_id = (SELECT id FROM products WHERE name = 'Aurora Sticker Pack'), DELETE FROM storefront_featured, DELETE FROM storefront_banners WHERE headline = 'Aurora Summer Drop'.

8.s2 GET /storefront — the denormalized home page (new endpoint)

One read that assembles the whole home page. The effective display price is resolved in SQL and is advisory (may be slightly stale, like the cart read §5.2). Money is integer cents; field names are camelCase on the wire; endsAt follows the base createdAt wire rule (§5.4/§7): RFC3339 UTC, trailing Z, no sub-second digits.

• 200 →

{
  "banners": [
    { "id": 1, "headline": "Aurora Summer Drop", "subhead": "Flash deals live now — limited units",
      "ctaHref": "/deals",
      "imageUrl": "https://upload.wikimedia.org/wikipedia/commons/thumb/3/37/Schenker_VIA14_Laptop_asv2021-01.jpg/500px-Schenker_VIA14_Laptop_asv2021-01.jpg" }
  ],
  "categories": [
    { "id": 1, "slug": "drinkware", "name": "Drinkware",
      "imageUrl": "https://upload.wikimedia.org/wikipedia/commons/thumb/3/37/Schenker_VIA14_Laptop_asv2021-01.jpg/500px-Schenker_VIA14_Laptop_asv2021-01.jpg" },
    { "id": 2, "slug": "apparel", "name": "Apparel",
      "imageUrl": "https://upload.wikimedia.org/wikipedia/commons/thumb/3/37/Schenker_VIA14_Laptop_asv2021-01.jpg/500px-Schenker_VIA14_Laptop_asv2021-01.jpg" },
    { "id": 3, "slug": "accessories", "name": "Accessories",
      "imageUrl": "https://upload.wikimedia.org/wikipedia/commons/thumb/3/37/Schenker_VIA14_Laptop_asv2021-01.jpg/500px-Schenker_VIA14_Laptop_asv2021-01.jpg" }
  ],
  "featured": [
    { "id": 3, "name": "Aurora Sticker Pack", "unitPrice": 499, "displayPrice": 299, "onSale": true, "stock": 200 }
  ],
  "deals": [
    { "productId": 3, "name": "Aurora Sticker Pack", "unitPrice": 499, "salePrice": 299,
      "endsAt": "2026-07-14T00:03:16Z", "remaining": 50, "soldOut": false }
  ]
}

The four sections come from four reads. The categories section is a plain read of the base categories table (§4.1) — the same {id, slug, name, imageUrl} object shape as the §5.1 nested category — ordered by id (base categories carry no sort_order):

SELECT id, slug, name, image_url FROM categories ORDER BY id;

The effective/display price is one LEFT JOIN on the live-deal predicate:

SELECT p.id, p.name, p.unit_price,
       COALESCE(d.sale_price_cents, p.unit_price) AS display_price,
       (d.id IS NOT NULL)                          AS on_sale,
       p.stock
FROM storefront_featured f
JOIN products p ON p.id = f.product_id
LEFT JOIN deals d
  ON d.product_id = p.id AND now() BETWEEN d.starts_at AND d.ends_at
ORDER BY f.sort_order, p.id;

The deals section adds the countdown + remaining allocation:

SELECT p.id AS product_id, p.name, p.unit_price, d.sale_price_cents AS sale_price,
       d.ends_at,                              -- backend formats per §5.4 (trailing Z, no sub-second)
       (d.allocation_cap - d.sold) AS remaining,
       (d.sold >= d.allocation_cap) AS sold_out
FROM deals d
JOIN products p ON p.id = d.product_id
WHERE now() BETWEEN d.starts_at AND d.ends_at
ORDER BY d.ends_at;

GET /storefront errors:

400 / 404 / 422 do not arise on GET /storefront: it takes no path parameter and no request body, so there is no malformed-input (422) or bad-id (404) path, and — consistent with the base, which never returns 400 (malformed input is 422, a bad path is 404, §5.5) — the module introduces no 400.

8.s3 POST /checkout — extended with the flash-sale caps (in-transaction)

The POST /checkout request contract is unchanged (X-Cart-Token + {customerId} + optional Idempotency-Key, §5.3); a deal applies automatically to any cart line whose product has a live deal at the transaction instant. The extension adds, inside the one base transaction, per line:

1. The base guard is unchanged — UPDATE products SET stock = stock - $1 WHERE id = $2 AND stock >= $1 RETURNING unit_price captures the base price; 0 rows → out_of_stock (409), exactly as §5.3.

2. A global allocation guard — reserve on the live deal and capture the sale price in one statement; the cap lives in the WHERE, so the row lock serialises concurrent buyers (the same mechanism as the stock guard):

   UPDATE deals SET sold = sold + $1
     WHERE product_id = $2
       AND now() BETWEEN starts_at AND ends_at
       AND sold + $1 <= allocation_cap
   RETURNING id, sale_price_cents, per_customer_cap;

   0 rows means either no live deal (charge the base price) or the drop is exhausted. A cheap classification probe (SELECT EXISTS(SELECT 1 FROM deals WHERE product_id=$1 AND now() BETWEEN starts_at AND ends_at)) distinguishes them: EXISTS = true → deal_sold_out (409); false → not on sale, the effective price stays the base price. The probe is a routing decision, not a price/stock read — the price is only ever captured via RETURNING.

3. A per-customer guard — a guarded upsert into the ledger, the cap in the WHERE:

   INSERT INTO deal_purchases (deal_id, customer_id, qty)
     SELECT $1, $2, $3::int WHERE $3::int <= $4::int          -- $4 = per_customer_cap (guards the first buy)
   ON CONFLICT (deal_id, customer_id)
     DO UPDATE SET qty = deal_purchases.qty + EXCLUDED.qty
     WHERE deal_purchases.qty + EXCLUDED.qty <= $4::int        -- guards subsequent buys
   RETURNING qty;

   0 rows → the buy would exceed the per-customer cap → purchase_limit (409).

The effective price (sale price iff a live deal was reserved; otherwise the base price) is what the order totals and each order_items.unit_price capture. Because every guard is inside the one transaction, a rejected line rolls the whole checkout back — the stock decrement, the allocation reservation, and the ledger increment are all undone. Idempotent replay is unaffected: a duplicate Idempotency-Key still hits orders_idem_key (23505) and the transaction rolls back before any reservation commits, so a retry never double-reserves (§5.3 idempotency semantics).

New POST /checkout error bodies (additive; the base §5.3/§5.5 codes are unchanged):

Both are 409 (a contention conflict, like out_of_stock), byte-identical on both backends, Content-Type: application/json, framed exactly like the base error bodies.

8.s4 Byte-parity, money, time (module-specific)

• Free-text JSON escaping (the base parity requirement, restated — not a storefront-only trap). The base GET /products already carries the free-text name (§5.1), so this is a base requirement met by the base writeJSON (§3.1, §7 “JSON edge” row), not something the storefront introduces. Go’s json.Marshal and the default json.Encoder escape <, >, and & to the unicode escapes \u003c, \u003e, \u0026. The storefront carries marketing free-text (headline, subhead, name), where a & or < in the copy diverges: Go emits \u0026 while Spring/Jackson emits a literal &. The storefront reuses the identical base writer (§3.1), which disables HTML escaping and drops the encoder’s trailing newline:

  var buf bytes.Buffer
  enc := json.NewEncoder(&buf)
  enc.SetEscapeHTML(false)
  _ = enc.Encode(v)
  w.Header().Set("Content-Type", "application/json")
  w.Write(bytes.TrimRight(buf.Bytes(), "\n"))   // Jackson emits no trailing newline either

  Verified against Go 1.26 for the value Tees & Mugs <50% off>: default json.Marshal → {"headline":"Tees \u0026 Mugs \u003c50% off\u003e"}; the no-escape encoder above → {"headline":"Tees & Mugs <50% off>"} (byte-identical to Jackson).

• Empty arrays (the base non-nil-slice rule, §7): the four storefront sections (banners, categories, featured, deals) MUST each serialize as [] when empty — Go initialises []T{} before the scan; Spring returns a non-null List (already []).

• Money is integer cents everywhere (unitPrice, displayPrice, salePrice, sale_price_cents).

• Time — endsAt reuses the base createdAt rule (§5.4/§7): Go t.UTC().Format(time.RFC3339) as a string field; Spring reads OffsetDateTime/Instant, truncatedTo(ChronoUnit.SECONDS), serialises UTC with Z. No sub-second digits on either backend.

8.s5 Parity points (Go default + Spring reasoned; SAME contract)

Critical invariant: the two new 409 bodies, the effective-price capture, and the /storefront bytes are identical across backends. Deal reservations release only via transaction rollback (no cancel path in this module).

order-lifecycle — Order Lifecycle & Inventory (optional module)

Self-contained: this module stands alone on the base build, assumes no other module (not storefront), and nothing in the core §4/§5 depends on it. Its schema is additive and created by the module’s own migration files; the only edit to a base object is widening the enumerated orders.status CHECK (§8.o1), which keeps every base value.

Summary. The full lifecycle wrapped around the sacred checkout transaction, bundled into one module. Reservations are cart holds with a TTL: a hold is a guarded decrement of products.stock into a reserved bucket (it never bypasses the base oversell guard), and an expiry sweep returns lapsed holds to stock with SELECT … FOR UPDATE SKIP LOCKED — the canonical Postgres queue-drain. An order + payment state machine carries the order from pending → authorized → paid → fulfilled → refunded, each hop a guarded UPDATE … WHERE status = <expected> RETURNING (an illegal hop matches 0 rows → 409), with a mock provider that authorizes on checkout and captures on the capture step. An append-only stock ledger (stock_movements) records why stock moved on every change — sale, reservation, expiry, refund, restock — written inside the same transaction as the change; it augments the base products.stock guard (the guard stays the source of truth; the ledger is the audit). A transactional outbox writes an event in the same commit as each transition and a poller drains it (also with SKIP LOCKED) — reliable side-effects without dual-write. And refund is a compensating transaction: per-line, idempotent, atomic — a positive stock_movements restock + a refund payments row + the terminal refunded transition, all in one tx. Every stock change and every state change lives inside the one transaction the spotlight is built on — oversell and lost/duplicated side-effects stay structurally impossible.

Course scope note. Go is compiled (pgx v5.10.0 / Go 1.26) and the SQL is run against postgres:16; Spring/Kotlin is reasoned against Spring 6 / Boot 3.4 / JdbcTemplate + @Transactional (no JVM toolchain in the authoring environment). The module adds no frontend step (it is backend/data-shaped). It does not implement order cancellation — cancelled is retained as a legal orders.status value for base/ai-support compatibility (the cancel endpoint POST /orders/{id}/cancel lives in the separate ai-support module, §5.6), and this module never re-implements it.

8.o1 Tables (module-owned; additive CREATE TABLE IF NOT EXISTS)

Created by the module’s own numbered migration files, after the base 0001/0002 (and after any storefront files, which this module does not assume): Go 0005_order_lifecycle.up.sql / .down.sql then 0006_order_lifecycle_seed.up.sql / .down.sql; Spring V5__order_lifecycle.sql then V6__order_lifecycle_seed.sql. Each Go/Spring pair is a byte-for-byte copy. All money is BIGINT cents; timestamps are TIMESTAMPTZ.

The one non-additive statement is widening the base orders.status enum. The base declares it (§4.1) as CHECK (status IN ('pending','shipped','cancelled')) (auto-named orders_status_check), which would reject the new lifecycle states (23514). The module drops and re-adds the CHECK, keeping every base value so no existing row is invalidated:

-- Widen the base orders.status enum to admit the payment/fulfilment lifecycle (the module's one edit
-- to a base object). ADD COLUMN IF NOT EXISTS is a no-op on the real base (the column already exists);
-- it keeps the migration correct on a base variant that somehow lacks the column.
ALTER TABLE orders ADD COLUMN IF NOT EXISTS status TEXT NOT NULL DEFAULT 'pending';
ALTER TABLE orders DROP CONSTRAINT IF EXISTS orders_status_check;
ALTER TABLE orders ADD CONSTRAINT orders_status_check
  CHECK (status IN ('pending','authorized','paid','fulfilled','refunded','cancelled','shipped'));

-- Reservations: a cart hold with a TTL. A hold is a GUARDED decrement of products.stock into the
-- reserved bucket (this row); it never bypasses the base oversell guard.
CREATE TABLE IF NOT EXISTS reservations (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id  BIGINT      NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  customer_id BIGINT      NOT NULL REFERENCES customers(id),
  qty         INTEGER     NOT NULL CHECK (qty > 0),
  status      TEXT        NOT NULL DEFAULT 'held'
                CHECK (status IN ('held','released','committed')),
  expires_at  TIMESTAMPTZ NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Partial index: the expiry sweep only ever scans still-held rows.
CREATE INDEX IF NOT EXISTS reservations_sweep ON reservations (expires_at) WHERE status = 'held';

-- Payments: a mock provider's authorize -> capture -> refund records.
CREATE TABLE IF NOT EXISTS payments (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id   BIGINT      NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  kind       TEXT        NOT NULL CHECK (kind IN ('authorize','capture','refund')),
  amount     BIGINT      NOT NULL CHECK (amount >= 0),                          -- cents
  status     TEXT        NOT NULL CHECK (status IN ('authorized','captured','refunded','voided')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS payments_order ON payments (order_id);

-- Stock ledger: an append-only audit of EVERY change to products.stock. It AUGMENTS the base
-- products.stock >= 0 guard (records why stock moved); it never replaces it.
CREATE TABLE IF NOT EXISTS stock_movements (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id BIGINT      NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  delta      INTEGER     NOT NULL CHECK (delta <> 0),   -- signed: - = out of available, + = into available
  reason     TEXT        NOT NULL CHECK (reason IN ('sale','reserve','reserve_expire','refund','restock')),
  ref        TEXT,                                      -- provenance, e.g. 'order:4821' or 'reservation:12'
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS stock_movements_product ON stock_movements (product_id, created_at);

-- Transactional outbox: events written in the SAME commit as a state transition, drained later.
CREATE TABLE IF NOT EXISTS outbox (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  topic      TEXT        NOT NULL,   -- 'order_authorized' | 'order_paid' | 'order_fulfilled' | 'order_refunded'
  payload    JSONB       NOT NULL,
  sent_at    TIMESTAMPTZ,            -- NULL until the drain poller marks it sent
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Partial index: the drain poller only ever scans unsent rows.
CREATE INDEX IF NOT EXISTS outbox_unsent ON outbox (id) WHERE sent_at IS NULL;

The seed migration (0006 / V6) backfills the ledger with the seeded stock as one restock movement per product (guarded by ref = 'seed' so a re-run inserts nothing), so the invariant sum(stock_movements.delta) = products.stock holds from day one.

8.o2 Reservations & the SKIP LOCKED expiry sweep

A hold moves units out of available (products.stock) into the reserved bucket (a reservations row) with the base guard; a hold either commits to a sale or expires and is swept back. The reserved units leave products.stock at hold time and are recorded once in the ledger as reserve (-qty); committing writes no further stock movement (the units never re-enter available), and expiring writes reserve_expire (+qty).

• POST /reservations — place a hold. Body { "customerId": 1, "productId": 3, "quantity": 2, "ttlSeconds": 300 } (ttlSeconds optional, default 300). The hold runs the base guarded decrement UPDATE products SET stock = stock - $1 WHERE id = $2 AND stock >= $1, inserts the reservations row (expires_at = now() + ttl), and writes stock_movements('reserve', -qty) — all in one tx.

  { "reservationId": 12, "expiresAt": "2026-07-14T00:10:00Z" }

  Condition	Status	Body
  OK	200	{"reservationId":N,"expiresAt":"…Z"}
  Insufficient stock — or an unknown productId (the guarded UPDATE can’t tell them apart — the §5.3 guard semantics; unlike base checkout, the id here is client-supplied, so the case is live)	409	{"error":"out_of_stock"}
  Unknown customerId (FK 23503)	404	{"error":"not_found"}
  Empty/malformed body, quantity <= 0, or ttlSeconds < 0	422	{"error":"invalid_request"}
  Backing store unreachable (retryable)	503	{"error":"unavailable"}
  Unmapped server error	500	{"error":"internal"}

• POST /reservations/{id}/commit — convert a held reservation into a confirmed sale (reserved → sold). Guarded transition UPDATE reservations SET status='committed' WHERE id=$1 AND status='held' RETURNING …; it creates the order (authorized) + order_items + a payments('authorize') row + an outbox('order_authorized') event, and — because the units already left products.stock at hold time — does not decrement stock again and writes no new ledger row.

  Condition	Status	Body
  OK	200	{"orderId":N,"status":"authorized"}
  Unknown order id {id}, or a non-numeric {id}	404	{"error":"not_found"}
  Reservation not held (already committed / released / expired)	409	{"error":"illegal_transition"}
  Backing store unreachable (retryable)	503	{"error":"unavailable"}
  Unmapped server error	500	{"error":"internal"}

  The 0-row transition is classified by a cheap SELECT EXISTS(… WHERE id=$1) probe: the reservation exists but is not held → illegal_transition (409); it does not exist → not_found (404).

• The expiry sweep (a small poller SweepExpired, not an HTTP endpoint). It drains the held queue with the canonical pattern and, per row, returns the units to available and marks the hold released — all in one tx:

  SELECT id, product_id, qty FROM reservations
    WHERE status = 'held' AND expires_at < now()
    ORDER BY expires_at
    FOR UPDATE SKIP LOCKED
    LIMIT $1;
  -- then, per claimed row, in the SAME tx:
  UPDATE products SET stock = stock + $qty WHERE id = $product_id;         -- back into available
  UPDATE reservations SET status = 'released' WHERE id = $id;
  INSERT INTO stock_movements (product_id, delta, reason, ref)
    VALUES ($product_id, $qty, 'reserve_expire', 'reservation:'||$id);

  FOR UPDATE SKIP LOCKED lets many sweepers run at once without ever touching the same row: a concurrent sweeper skips the rows another already holds instead of blocking. Run it on a schedule (Go: a time.Ticker goroutine; Spring: @Scheduled(fixedDelay=…)).

8.o3 Order + payment state machine

Checkout still returns {orderId} unchanged (§5.3), but the module extends the one checkout transaction (exactly as storefront does) to also: write stock_movements('sale', -qty) per line and authorize the payment — the guarded transition UPDATE orders SET status='authorized' WHERE id=$1 AND status='pending' plus a payments('authorize') row and an outbox('order_authorized') event, all in the same commit. So a freshly-checked-out order reads back (GET /orders/{id}, §5.4) with status: "authorized" (not pending) when the module is on — an additive, compatible change to the existing status field.

The remaining hops are their own endpoints. Each is a guarded UPDATE … WHERE status = <expected> RETURNING; a 0-row result is classified by a SELECT EXISTS probe into illegal_transition (409, the order exists in the wrong state) vs not_found (404, no such order). Transitions are strict (single expected state), so a repeat is an illegal transition — this is the module’s canonical “illegal transition → 409” lesson.

                    checkout / reservation commit         POST /orders/{id}/capture
   pending ─────────── authorize ──────────▶ authorized ──────── capture ────────▶ paid
                                                                                     │
                                             POST /orders/{id}/fulfil               │ fulfil
                                                                                     ▼
                     POST /orders/{id}/refund (idempotent)                       fulfilled
        refunded ◀────────────────────────────────────── (from paid or fulfilled)

   (cancelled — a retained base/ai-support value; NOT driven by this module.)

• POST /orders/{id}/capture — capture the authorized payment (authorized → paid); writes a payments('capture','captured') row + an outbox('order_paid') event in the same tx.

  Condition	Status	Body
  OK	200	{"orderId":N,"status":"paid"}
  Unknown order id {id}, or a non-numeric {id}	404	{"error":"not_found"}
  Order not authorized (e.g. still pending, already paid)	409	{"error":"illegal_transition"}
  Backing store unreachable (retryable)	503	{"error":"unavailable"}
  Unmapped server error	500	{"error":"internal"}

• POST /orders/{id}/fulfil — ship the paid order (paid → fulfilled); writes an outbox('order_fulfilled') event in the same tx.

  Condition	Status	Body
  OK	200	{"orderId":N,"status":"fulfilled"}
  Unknown order id {id}, or a non-numeric {id}	404	{"error":"not_found"}
  Order not paid	409	{"error":"illegal_transition"}
  Backing store unreachable (retryable)	503	{"error":"unavailable"}
  Unmapped server error	500	{"error":"internal"}

• POST /orders/{id}/refund — the compensating transaction (paid | fulfilled → refunded), idempotent and atomic. Optional body { "lines": [ { "productId": 3, "quantity": 2 } ] }; omit to refund every line. In one tx it runs the guarded terminal transition UPDATE orders SET status='refunded' WHERE id=$1 AND status IN ('paid','fulfilled') RETURNING …, then per refunded line writes a positive stock_movements('refund', +qty) and restocks products.stock += qty, records one payments('refund','refunded') row for the refunded amount, and emits an outbox('order_refunded') event.

  { "orderId": 4821, "status": "refunded", "refunded": 598 }

  Condition	Status	Body
  OK — or an idempotent replay (order already refunded)	200	{"orderId":N,"status":"refunded","refunded":C}
  Unknown order id {id}, or a non-numeric {id}	404	{"error":"not_found"}
  Order not refundable (pending, authorized, or cancelled)	409	{"error":"not_refundable"}
  Malformed body, an unknown line, or a line quantity above what was purchased	422	{"error":"invalid_request"}
  Backing store unreachable (retryable)	503	{"error":"unavailable"}
  Unmapped server error	500	{"error":"internal"}

  Idempotency follows the base cancel pattern (§5.6): the safety mechanism is order state, not a stored key. A second refund finds the order already refunded, restocks nothing, and returns the same {orderId, status:"refunded", refunded} 200 (the amount recomputed from the payments('refund') rows). An Idempotency-Key header is accepted for symmetry with POST /checkout but is not the safety mechanism and no refund key is stored (the single orders.idempotency_key column holds only the checkout key, §4.2). Course modelling note: a partial refund still moves the order to the terminal refunded state (this course models refunded as “a refund has been issued”; a production system might add a partially_refunded state).

8.o4 Inventory ledger + transactional outbox

• The ledger reinforces the guard. products.stock stays the single source of truth the base guarded UPDATE protects; stock_movements is the why. Every stock change writes a ledger row in the same transaction as the guarded stock UPDATE — sale / reserve (-qty), reserve_expire / refund / restock (+qty) — so the audit can never diverge from a committed change. Restock is a guarded increment + a restock row; low-stock is a read from the ledger:

  -- restock (in one tx)
  UPDATE products SET stock = stock + $1 WHERE id = $2;
  INSERT INTO stock_movements (product_id, delta, reason, ref) VALUES ($2, $1, 'restock', $3);
  
  -- low stock + reconcile: the ledger sum must equal the live stock
  SELECT p.id, p.name, p.stock, COALESCE(SUM(m.delta), 0) AS ledger_sum
    FROM products p LEFT JOIN stock_movements m ON m.product_id = p.id
   GROUP BY p.id
  HAVING p.stock <= $1                                 -- $1 = the low-stock threshold
   ORDER BY p.stock;

• The outbox is written in the same commit as the transition. Each transition (authorize, capture, fulfil, refund) inserts an outbox row inside its own transaction, so the event and the state change commit or roll back together — no dual-write. A poller drains unsent rows (again with SKIP LOCKED) and marks them sent; the consumer is a stub (log/no-op) to keep the course at $0:

  SELECT id, topic, payload FROM outbox WHERE sent_at IS NULL
    ORDER BY id FOR UPDATE SKIP LOCKED LIMIT $1;
  -- then, per delivered row:
  UPDATE outbox SET sent_at = now() WHERE id = $id;

  Payload schema (fixed keys; integer + enum only, no free-text). Every event’s payload is a fixed-shape object built from the transition’s own values — never marketing copy, an email, or other free-text (per §8.o5’s “no free-text in payloads”). Each topic carries {"orderId":<id>,"status":"<newStatus>"} where status is the enumerated state the transition just reached — order_authorized → {"orderId":4821,"status":"authorized"}, order_paid → {"orderId":4821,"status":"paid"}, order_fulfilled → {"orderId":4821,"status":"fulfilled"} — and order_refunded adds the integer refunded amount: {"orderId":4821,"status":"refunded","refunded":598}. Both backends build the identical string with keys in this order (orderId, status, then refunded); because the column is JSONB, Postgres normalises the stored value regardless of input key order or whitespace, and because every field is an integer or an enumerated status, the drained bytes are byte-identical on Go and Spring — the parity target §8.o6 names for the ($1, $2::jsonb) insert.

  The lesson: sending after COMMIT loses the event on a crash between commit and send; sending before COMMIT sends an event for a transaction that then rolls back. The outbox — write the intent in the same commit, deliver later, mark sent — is the only shape that is neither lossy nor premature.

8.o5 Byte-parity, money, time (module-specific)

• Money is integer cents everywhere (amount, total, refunded, unit_price).
• Time — expiresAt reuses the base createdAt rule (§5.4/§7): Go t.UTC().Format(time.RFC3339) as a string field; Spring reads OffsetDateTime/Instant, truncatedTo(ChronoUnit.SECONDS), serialised UTC with Z. No sub-second digits on either backend.
• No free-text in payloads. Every field this module returns is an integer, an RFC3339 instant, or an enumerated status/error string (held, authorized, out_of_stock, illegal_transition, …) — none carries <, >, or &, so the Go HTML-escape trap the storefront module hit does not arise here. For strict byte-parity the module’s writers still emit no trailing newline (Jackson emits none), matching the storefront encoder discipline (json.Encoder with SetEscapeHTML(false), trailing \n trimmed).
• Empty arrays (the base non-nil-slice rule, §7). This module’s endpoints return only scalars (orderId, status, refunded, reservationId, …) — no array-valued response field — so the []-not-null rule does not arise on the wire here; the base GET /orders/{id} items (§5.4) still follows it.
• Error bodies — 400 does not arise (base convention: malformed input is 422, a bad path is 404, §5.5). 404 (unknown/non-numeric order or reservation id), 409 (illegal_transition, not_refundable, out_of_stock), 422 (invalid_request), 503 (unavailable), and 500 (internal) are byte-identical on both backends, application/json, no trailing newline.

8.o6 Parity points (Go default + Spring reasoned; SAME contract)

Critical invariant: the reservation guard, the guarded transitions, the ledger writes, the refund compensation, and the outbox drain are all inside the one transaction and byte-identical across backends. The stock ledger augments — never replaces — the base products.stock >= qty guard: the guard keeps oversell impossible; the ledger records why stock moved. Spring’s in-method catch-and-rethrow on every 0-row guarded statement is mandatory for status/refund parity (§7).

discovery — Search & Recommendations (optional module)

Self-contained: this module stands alone on the base build, assumes no other module (not storefront, not order-lifecycle), and nothing in the core §4/§5 depends on it. Its schema is additive — module-owned columns and indexes added to the base products table by the module’s own migration files, plus CREATE EXTENSION IF NOT EXISTS pg_trgm. No base table’s meaning is changed and no new datastore is introduced.

Summary. How a real store is browsed and how it cross-sells, entirely in SQL. Two capabilities: (1) Faceted search — a Postgres full-text index (a generated tsvector column over the product name + description) queried with websearch_to_tsquery, plus facets (price range, in-stock stock > 0, and a category facet with live counts), keyset pagination (the seek method, never OFFSET), and typo-tolerance via a pg_trgm word-similarity fallback that fires when full-text finds nothing. (2) Recommendations from real order data — “customers also bought” via a co-purchase self-join over the base order_items, “frequently bought together” for a cart, and “trending” via a windowed sales count over recent orders. The load-bearing beat is the relational payoff: recommendations exist for free because the orders already live in the same SQL database beside the catalog (adjacent to the transactional spotlight, never touching it). Every discovery query is read-only — the module never mutates the catalog or an order and never touches checkout, so the transactional core is untouched.

Base-contract note (schema the module supplies). The base products table is (id, name, unit_price, stock, category_id) (§4.1) — categorised via the base categories table, but with no description. So the module adds description as a module-owned, additive column (ALTER TABLE products ADD COLUMN IF NOT EXISTS …), backfills it, and builds the tsvector over name || ' ' || description; the category facet joins the base categories (no module-owned category column exists — hits carry the base slug, and the facet rows are {slug, name, count}). pg_trgm is the same extension the optional ai-recipe module uses (§4.4); the module enables it itself with CREATE EXTENSION IF NOT EXISTS pg_trgm so it stands alone whether or not ai-recipe is installed. The base seed (§4.3) creates no orders, so the module seeds a small historical order graph as its own demo data for the recommendation queries.

Course scope note. Go is compiled (pgx v5.10.0 / Go 1.26) and the SQL is run against postgres:16; Spring/Kotlin is reasoned against Spring 6 / Boot 3.4 / JdbcTemplate (no JVM toolchain in the authoring environment). One frontend is built — a Jetpack Compose search screen (reasoned, not compiled) that consumes the keyset cursor for infinite scroll. The module adds no cloud or paid dependency; it is local and free like the base.

8.d1 Schema (module-owned; additive ALTER … IF NOT EXISTS, generated tsvector, GIN + trigram)

Created by the module’s own numbered migration files, after the base 0001/0002 (and after any storefront / order-lifecycle files, which this module does not assume): Go 0007_discovery.up.sql / .down.sql (schema) then 0008_discovery_seed.up.sql / .down.sql (demo data); Spring V7__discovery.sql then V8__discovery_seed.sql. golang-migrate and Flyway both apply versions in order and tolerate gaps, so a learner who enables only discovery gets 0001,0002,0007,0008 applied cleanly. Each Go/Spring pair is a byte-for-byte copy of the SQL (the in-course “keep the two copies from drifting” rule). Flyway has no down files; the .down.sql files are golang-migrate only.

-- 0007_discovery.up.sql (Go) / V7__discovery.sql (Spring)
-- OPTIONAL MODULE: discovery. Additive, self-contained, idempotent. Stands alone on the BASE build.

-- Typo-tolerance leans on pg_trgm. IF NOT EXISTS makes this a no-op if ai-recipe already enabled it, so
-- the discovery module stands alone on the base without conflicting with anything else.
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Module-owned column on the base catalog (additive; NOT NULL with a default so existing rows fill in).
-- The category needs NO module column: the base products.category_id → categories join covers it (§4.1).
ALTER TABLE products ADD COLUMN IF NOT EXISTS description TEXT NOT NULL DEFAULT '';

-- Generated full-text vector over name + description. The 2-arg to_tsvector('english', ...) is IMMUTABLE
-- (a fixed regconfig), which is what a STORED generated column requires — the 1-arg form is only STABLE.
ALTER TABLE products ADD COLUMN IF NOT EXISTS search_tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('english', name || ' ' || description)) STORED;

CREATE INDEX IF NOT EXISTS products_search_tsv ON products USING GIN (search_tsv);         -- full-text
CREATE INDEX IF NOT EXISTS products_name_trgm  ON products USING GIN (name gin_trgm_ops);  -- typo-tolerance
CREATE INDEX IF NOT EXISTS products_category   ON products (category_id);                  -- category facet join (the base declares no FK index, §4.4)

Notes: (a) the tsvector is a generated STORED column, so it recomputes automatically when the seed backfills a description — the app never maintains it. (b) products_name_trgm may duplicate an index ai-recipe created on products.name (§4.4); IF NOT EXISTS on a distinct name is harmless, and the down migration drops only this module’s objects. (c) The down migration drops the three indexes and the two module-owned columns (description, search_tsv) — the base category_id column and categories table are untouched — but leaves pg_trgm enabled: another module (ai-recipe) may rely on it, and CREATE EXTENSION IF NOT EXISTS records no ownership.

The seed migration (0008 / V8) backfills description for the base three products, inserts seven more module-owned products (so facets and recommendations have something to rank), each resolving its base category_id by slug — the one category the base seed lacks (Stationery/stationery) is seeded here first, ON CONFLICT (slug) DO NOTHING, the identical row sales-insights also seeds so either module can run first. The seven products include Aurora Hoodie, Aurora Water Bottle, and Aurora Notebook, the three names sales-insights also seeds, so its ON CONFLICT (name) DO NOTHING rows silently defer to these when both modules are on (this grounds §8.i2’s coexistence guarantee in defined rows). It also adds three module-owned demo customers, and inserts a demo order graph — historical orders inserted directly (not through the checkout guard, since they are recommendation data and do not decrement stock), idempotent via the base orders.idempotency_key partial-unique index (§4.2, keys seed:disc:*). A few demo orders are dated 30 days back so they fall outside the trending window while still counting for all-time co-purchase — this makes the window filter observable.

The seed (verified against postgres:16; idempotent on re-run — a second apply inserts nothing and the order/item counts stay put):

-- 0008_discovery_seed.up.sql (Go) / V8__discovery_seed.sql (Spring). Idempotent on re-run.
-- (a) Backfill description for the base three products (name-matched; the STORED search_tsv recomputes
--     automatically; their category_id already comes from the base seed). Value-idempotent on re-run.
UPDATE products SET description = 'Ceramic coffee mug with the Aurora logo' WHERE name = 'Aurora Mug';
UPDATE products SET description = 'Soft cotton t-shirt in Aurora colours'   WHERE name = 'Aurora Tee';
UPDATE products SET description = 'Weatherproof vinyl sticker pack'         WHERE name = 'Aurora Sticker Pack';

-- (b) The one category the base seed lacks (§4.3 seeds drinkware/apparel/accessories). Identical row in
--     the sales-insights seed (§8.i2); ON CONFLICT (slug) lets whichever module runs first win.
INSERT INTO categories (name, slug, image_url) VALUES
  ('Stationery', 'stationery', 'https://upload.wikimedia.org/wikipedia/commons/thumb/3/37/Schenker_VIA14_Laptop_asv2021-01.jpg/500px-Schenker_VIA14_Laptop_asv2021-01.jpg')
ON CONFLICT (slug) DO NOTHING;

-- (c) Seven more module-owned products, category_id resolved BY SLUG (never a hardcoded id).
--     ON CONFLICT (name) DO NOTHING makes this idempotent AND lets sales-insights' same-named rows
--     (Aurora Hoodie / Water Bottle / Notebook, §8.i2) defer to these.
INSERT INTO products (name, unit_price, stock, description, category_id)
SELECT v.name, v.unit_price, v.stock, v.description, c.id
FROM (VALUES
  ('Aurora Hoodie',       5999, 30, 'Heavyweight hoodie with embroidered logo', 'apparel'),
  ('Aurora Water Bottle', 1899, 40, 'Insulated stainless-steel bottle',         'drinkware'),
  ('Aurora Notebook',      899, 60, 'Dot-grid notebook, 120 pages',             'stationery'),
  ('Aurora Tote Bag',     1299, 45, 'Canvas tote with Aurora print',            'accessories'),
  ('Aurora Cap',          1799, 35, 'Adjustable cap, embroidered logo',         'apparel'),
  ('Aurora Coaster Set',   999, 55, 'Set of four cork-backed coasters',         'drinkware'),
  ('Aurora Enamel Pin',    699, 80, 'Hard-enamel lapel pin',                    'accessories')
) AS v(name, unit_price, stock, description, slug)
JOIN categories c ON c.slug = v.slug
ON CONFLICT (name) DO NOTHING;

-- (d) Three module-owned demo customers so "customers also bought" spans real buyers.
INSERT INTO customers (email) VALUES
  ('disc1@aurora.test'),('disc2@aurora.test'),('disc3@aurora.test')
ON CONFLICT (email) DO NOTHING;

-- (e) A small historical order graph (keys seed:disc:*, distinct from sales-insights' seed:si:*),
--     inserted DIRECTLY (recommendation data must not decrement live stock). Orders ~30 days back
--     fall OUTSIDE a 7-day trending window while still counting for all-time co-purchase.
WITH demo(tag, cust, product, qty, days_ago) AS (
  VALUES
    ('disc-o1','disc1@aurora.test','Aurora Mug',1,2),          ('disc-o1','disc1@aurora.test','Aurora Tee',1,2),
    ('disc-o2','disc1@aurora.test','Aurora Mug',1,4),          ('disc-o2','disc1@aurora.test','Aurora Sticker Pack',2,4),
    ('disc-o3','disc2@aurora.test','Aurora Mug',1,1),          ('disc-o3','disc2@aurora.test','Aurora Tee',1,1),          ('disc-o3','disc2@aurora.test','Aurora Enamel Pin',1,1),
    ('disc-o4','disc2@aurora.test','Aurora Hoodie',1,3),       ('disc-o4','disc2@aurora.test','Aurora Cap',1,3),
    ('disc-o5','disc3@aurora.test','Aurora Water Bottle',1,5), ('disc-o5','disc3@aurora.test','Aurora Coaster Set',1,5),
    ('disc-o6','disc3@aurora.test','Aurora Mug',1,6),          ('disc-o6','disc3@aurora.test','Aurora Tee',1,6),
    ('disc-o7','disc1@aurora.test','Aurora Notebook',1,2),     ('disc-o7','disc1@aurora.test','Aurora Enamel Pin',1,2),
    ('disc-o8','disc2@aurora.test','Aurora Tote Bag',1,4),     ('disc-o8','disc2@aurora.test','Aurora Sticker Pack',1,4),
    ('disc-o9','disc1@aurora.test','Aurora Mug',1,30),         ('disc-o9','disc1@aurora.test','Aurora Tee',1,30),
    ('disc-o10','disc3@aurora.test','Aurora Hoodie',1,32),     ('disc-o10','disc3@aurora.test','Aurora Water Bottle',1,32)
),
ins_orders AS (
  INSERT INTO orders (customer_id, total, status, idempotency_key, created_at)
  SELECT c.id, 0, 'pending', 'seed:disc:' || d.tag, now() - make_interval(days => d.days_ago)
  FROM (SELECT DISTINCT tag, cust, days_ago FROM demo) d
  JOIN customers c ON c.email = d.cust
  ON CONFLICT (idempotency_key) WHERE idempotency_key IS NOT NULL DO NOTHING
  RETURNING id, idempotency_key
)
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT o.id, p.id, d.qty, p.unit_price
FROM demo d
JOIN products p   ON p.name = d.product
JOIN ins_orders o ON o.idempotency_key = 'seed:disc:' || d.tag
ON CONFLICT (order_id, product_id) DO NOTHING;

UPDATE orders o
SET total = (SELECT COALESCE(SUM(oi.quantity * oi.unit_price),0) FROM order_items oi WHERE oi.order_id = o.id)
WHERE o.idempotency_key LIKE 'seed:disc:%';

The down migration is DELETE FROM orders WHERE idempotency_key LIKE 'seed:disc:%' (line items cascade, §4.1); the shared products/customers — and the shared stationery category — are left in place because sales-insights may reference them by name/slug.

8.d2 GET /search — full-text + facets + keyset pagination (new endpoint)

GET /search?q=&minPrice=&maxPrice=&inStock=&category=&after=&limit=. Read-only. Money is integer cents; field names are camelCase on the wire.

• q (required, non-blank) — matched with websearch_to_tsquery('english', q) against search_tsv.
• minPrice / maxPrice — integer cents, inclusive band (defaults 0 / unbounded).
• inStock — true restricts to stock > 0; false/absent = no stock filter.
• category — exact category slug filter (the base categories.slug, §4.1); absent = all categories (facet counts are still returned).
• limit — 1..100, default 20.
• after — the keyset cursor (below); absent = first page.

Ranking. score = (ts_rank(search_tsv, query) * 1000000)::bigint — the relevance rank scaled to an integer so it is byte-identical across backends and safe to carry in a cursor (a raw float on the wire or in a cursor is a Go/Jackson byte-parity hazard). Results order by score DESC, id DESC.

Keyset pagination (the seek method, not OFFSET). The page’s ordering key is the pair (score, id); id is unique, so (score, id) is a total order. The next page uses a row-value comparison as the seek predicate — WHERE (score, id) < (afterScore, afterId) under ORDER BY score DESC, id DESC — which never skips or repeats a row even when many hits share the same score (the base seed’s names each contain “aurora” once, so most hits tie on score; the id tiebreak resolves them exactly).

after cursor — exact encoding. Opaque, base64url (RFC 4648 §5, no padding) of the ASCII string "<score>:<id>", where both are base-10 integers: the score and id of the last hit on the current page. Decoding base64url-decodes, splits on the first :, and parses two int64. Example: a page whose last hit is (score 75991, id 6) → the string 75991:6 → cursor NzU5OTE6Ng. A malformed cursor (bad base64url, missing :, non-integer parts) is a 422 invalid_request. Because the cursor is pure integers + base64url, Go and Spring emit identical cursor bytes. nextCursor is the cursor of the last hit when the page is full (len(hits) == limit), and null on the final page.

• 200 →

{
  "hits": [
    { "id": 10, "name": "Aurora Enamel Pin", "unitPrice": 699, "stock": 80, "category": "accessories", "score": 60793 }
  ],
  "facets": { "categories": [
    { "slug": "accessories", "name": "Accessories", "count": 3 },
    { "slug": "apparel", "name": "Apparel", "count": 3 },
    { "slug": "drinkware", "name": "Drinkware", "count": 3 },
    { "slug": "stationery", "name": "Stationery", "count": 1 }
  ] },
  "nextCursor": "NzU5OTE6Ng"
}

Each hit’s category is the base category slug (via the products.category_id → categories join, §4.1); each facet row is {slug, name, count} — the slug is what a client feeds back as the category filter, the name is for display. score is an opaque, backend-agnostic relevance integer (do not compare it across a full-text page and a trigram-fallback page — §8.d3 explains why a session is only ever one or the other). The facet counts are computed for the same q and the price/in-stock filters but ignoring the category filter, so the shopper always sees every category still reachable from the current query (the standard faceted-search semantic); the counts above are the verified all-ten-products result for q=aurora with no price/stock filter.

The full-text + facet + keyset read (identical SQL on both backends):

WITH q AS (SELECT websearch_to_tsquery('english', $1) AS query)
SELECT p.id, p.name, p.unit_price, p.stock, c.slug AS category,
       (ts_rank(p.search_tsv, q.query) * 1000000)::bigint AS score
FROM products p
JOIN categories c ON c.id = p.category_id
CROSS JOIN q
WHERE p.search_tsv @@ q.query
  AND p.unit_price >= $2 AND p.unit_price <= $3
  AND (NOT $4 OR p.stock > 0)                                                          -- inStock facet
  AND ($5 = '' OR c.slug = $5)                                                         -- category facet (slug)
  AND (NOT $6 OR ((ts_rank(p.search_tsv, q.query) * 1000000)::bigint, p.id) < ($7, $8)) -- keyset seek
ORDER BY score DESC, p.id DESC
LIMIT $9;                                                                              -- $2 min, $3 max, $6 hasCursor, $7 afterScore, $8 afterId, $9 limit

-- facet counts: same q + price/stock, category filter deliberately omitted; rows are {slug, name, count}
WITH q AS (SELECT websearch_to_tsquery('english', $1) AS query)
SELECT c.slug, c.name, count(*)
FROM products p
JOIN categories c ON c.id = p.category_id
CROSS JOIN q
WHERE p.search_tsv @@ q.query AND p.unit_price >= $2 AND p.unit_price <= $3 AND (NOT $4 OR p.stock > 0)
GROUP BY c.slug, c.name ORDER BY count(*) DESC, c.slug;

GET /search error bodies:

400 and 404 do not arise on GET /search (it takes no path id; malformed input is 422, per the base convention §5.5 that the module never returns 400).

8.d3 Typo-tolerance — a pg_trgm word-similarity fallback

websearch_to_tsquery matches lexemes, not misspellings: a shopper who types hoddie gets zero full-text hits. When the full-text query returns no rows on the first page (after absent), the server re-runs a trigram fallback so the misspelling still lands, then paginates the fallback with the same keyset machinery (score = (word_similarity(q, name) * 1000000)::bigint). The fallback fires only when full-text page 1 is empty, so a single paginated session is entirely full-text or entirely trigram — the two score scales never mix within one result set.

-- lower the threshold so the index-backed <% operator keeps plausible matches, then match on the name
SET pg_trgm.word_similarity_threshold = 0.3;
SELECT p.id, p.name, p.unit_price, p.stock, c.slug AS category,
       (word_similarity($1, p.name) * 1000000)::bigint AS score
FROM products p
JOIN categories c ON c.id = p.category_id
WHERE $1 <% p.name                                                                     -- word_similarity(q,name) >= threshold
  AND p.unit_price >= $2 AND p.unit_price <= $3
  AND (NOT $4 OR p.stock > 0)
  AND ($5 = '' OR c.slug = $5)
  AND (NOT $6 OR ((word_similarity($1, p.name) * 1000000)::bigint, p.id) < ($7, $8))
ORDER BY score DESC, p.id DESC
LIMIT $9;

<% (word-similarity) is backed by the products_name_trgm GIN index (gin_trgm_ops); on the tiny demo table the planner picks a seq scan, but with enable_seqscan=off the plan is a Bitmap Index Scan on products_name_trgm (Index Cond: (name %> 'hoddie')), confirming the index accelerates typo-tolerance at real catalog sizes. The fallback introduces no new endpoint, response shape, or error body — it is a routing decision inside GET /search.

8.d4 Recommendations from real order data (new endpoints; SQL-only)

Three read-only endpoints, all served straight from the base order_items/orders — no new datastore. Each recommendation row carries a weight: an integer whose meaning is the endpoint’s ranking signal.

• GET /products/{id}/related?limit= — “customers also bought”: products that appeared in the same orders as {id}, ranked by co-occurrence count (weight = co-purchase count). A co-purchase self-join over order_items:

  SELECT oi2.product_id, p.name, p.unit_price, p.stock, COUNT(*) AS weight
  FROM order_items oi1
  JOIN order_items oi2 ON oi2.order_id = oi1.order_id AND oi2.product_id <> oi1.product_id
  JOIN products    p   ON p.id = oi2.product_id
  WHERE oi1.product_id = $1
  GROUP BY oi2.product_id, p.name, p.unit_price, p.stock
  ORDER BY weight DESC, oi2.product_id
  LIMIT $2;

  { "productId": 1, "related": [ { "id": 2, "name": "Aurora Tee", "unitPrice": 2999, "stock": 12, "weight": 3 } ] }

  Condition	Status	Body
  OK (an empty related for a real product is a valid 200 [])	200	{"productId":N,"related":[…]}
  Unknown product {id}, or a non-numeric {id}	404	{"error":"not_found"}
  limit not in 1..100	422	{"error":"invalid_request"}
  Backing store unreachable / unmapped	503 / 500	{"error":"unavailable"} / {"error":"internal"}

  A resource-path endpoint names a product that must exist, so an unknown id is a 404 (a cheap SELECT EXISTS(SELECT 1 FROM products WHERE id=$1) distinguishes “no such product” from “real product, no co-purchases yet”) — mirroring the base GET /orders/{id} 404 (§5.4).

• GET /cart/related?ids=1,2&limit= — “frequently bought together” for a cart: products co-occurring in orders that contain any of the cart’s products, excluding the cart itself (weight = orders in common):

  SELECT oi2.product_id, p.name, p.unit_price, p.stock, COUNT(DISTINCT oi1.order_id) AS weight
  FROM order_items oi1
  JOIN order_items oi2 ON oi2.order_id = oi1.order_id
  JOIN products    p   ON p.id = oi2.product_id
  WHERE oi1.product_id = ANY($1) AND oi2.product_id <> ALL($1)
  GROUP BY oi2.product_id, p.name, p.unit_price, p.stock
  ORDER BY weight DESC, oi2.product_id
  LIMIT $2;

  { "ids": [1, 2], "related": [ { "id": 3, "name": "Aurora Sticker Pack", "unitPrice": 499, "stock": 200, "weight": 2 } ] }

  Condition	Status	Body
  OK (unknown ids contribute nothing; an empty result is a valid 200)	200	{"ids":[…],"related":[…]}
  Missing/empty ids, a non-integer in ids, or limit not in 1..100	422	{"error":"invalid_request"}
  Backing store unreachable / unmapped	503 / 500	{"error":"unavailable"} / {"error":"internal"}

• GET /trending?days=&limit= — a windowed sales count over recent orders (weight = units sold in the window; days 1..365, default 7). The window function RANK() OVER (ORDER BY SUM(quantity) DESC) gives a tie-aware rank for teaching; the wire response is the ordered list (rank = 1-based array position):

  SELECT oi.product_id, p.name, p.unit_price, p.stock, SUM(oi.quantity)::bigint AS weight
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products    p  ON p.id = oi.product_id
  WHERE o.created_at >= now() - make_interval(days => $1)
  GROUP BY oi.product_id, p.name, p.unit_price, p.stock
  ORDER BY weight DESC, oi.product_id
  LIMIT $2;

  { "trending": [ { "id": 1, "name": "Aurora Mug", "unitPrice": 1499, "stock": 50, "weight": 8 } ] }

  Condition	Status	Body
  OK	200	{"trending":[…]}
  days not in 1..365 or limit not in 1..100	422	{"error":"invalid_request"}
  Backing store unreachable / unmapped	503 / 500	{"error":"unavailable"} / {"error":"internal"}

GET /cart/related and GET /trending are browse-style queries with no single resource identity, so — like the base GET /storefront in storefront — they never 404: an empty result is a valid 200. Only the resource-path GET /products/{id}/related 404s on an unknown id.

Spotlight framing. These recommendations are the relational payoff — they exist for free because the orders live in the same SQL database as the catalog. They are strictly read-only and never touch the checkout transaction; the transactional core (§1, §5.3) is unchanged.

8.d5 Byte-parity, money, time (module-specific)

• Free-text JSON escaping (the storefront parity trap applies). Search hits carry the product name (free-text). Go’s default json.Marshal/Encoder escapes &, <, > to &/</> while Spring/Jackson emits them literally, so a product named Tees & Mugs <50% off> would diverge. Reuse the storefront encoder discipline (§8.s4): the Go writers use json.Encoder with SetEscapeHTML(false) and trim the encoder’s trailing \n (Jackson emits none). Verified against Go 1.26: the no-escape encoder emits {"…","name":"Tees & Mugs <50% off>",…} byte-for-byte with Jackson.
• Empty arrays (the base non-nil-slice rule, §7): hits, facets.categories, and every related/trending list MUST serialize as [] when empty — Go []T{} before the scan; Spring a non-null List.
• Money is integer cents everywhere (unitPrice, minPrice, maxPrice).
• The relevance score and every weight/count are integers. No float reaches the wire or the cursor — the cursor is base64url of two base-10 integers — so there is no float-formatting divergence between Go’s strconv and Jackson.
• Time — not emitted. No discovery payload carries a timestamp (search and recommendations return integers and enum-free free-text only), so the base createdAt RFC3339 rule (§5.4/§7) does not arise here; created_at is used only inside the trending WHERE window predicate.
• Error bodies — 400 does not arise (base convention: malformed input is 422, a bad path is 404, §5.5). 404 (unknown/non-numeric product id on /related), 422 (invalid_request), 503 (unavailable), and 500 (internal) are byte-identical on both backends, application/json, no trailing newline.

8.d6 Parity points (Go default + Spring reasoned; SAME contract)

Critical invariant: the search SQL, the integer score, the keyset seek, the base64url cursor bytes, the three recommendation queries, and every error body are identical across backends. All discovery queries are read-only and never touch the checkout transaction — the module is the relational payoff of keeping orders and catalog in one SQL database, not a change to the transactional core.

cache — Redis Read-Cache & Rate-Limiting (optional module)

Self-contained: this module stands alone on the base build, assumes no other module (not storefront, not order-lifecycle, not discovery), and nothing in the core §4/§5 depends on it. Its only new infrastructure is a tiny Redis container added to the Compose file (§8.c1); it adds no DB migration and no base-table edit. If REDIS_URL is unset the module is simply off — reads go straight to Postgres and the limiter allows everything — so the base build is never blocked by it.

Summary. The speed layer, taught with its one hard boundary. The module puts a read-through cache in front of the base GET /products catalog read (check Redis → on miss read Postgres → SET with a TTL) and a per-customer rate-limiter in front of the base POST /checkout (an atomic Redis counter, INCR + EXPIRE in one Lua script). It teaches cache invalidation head-on — bust the catalog key on a write that changes catalog data, and contrast TTL-only staleness with explicit invalidation — and it makes the never-cache boundary the spine of the module: stock and the checkout decision are never cached. The cached catalog carries an advisory stock number (like the storefront’s advisory display price, §8.s2); the authoritative stock lives only in the base guarded UPDATE … WHERE stock >= qty RETURNING, uncached, inside the one checkout transaction (§5.3). A Redis outage fails open — reads degrade to direct Postgres and the limiter allows the request — so checkout correctness never depends on the cache. The frame: make reads fast and shield the hot path, without ever letting a cache lie about stock in a way that matters.

Where Redis sits in this curriculum. Redis is the spotlight of the Ticker project (Streams as the change feed) and load-bearing in Concord (pub/sub fan-out); here it is a supporting cache and limiter beside the Postgres spotlight, not the star. The base course’s source of truth is unchanged — Redis only accelerates reads and throttles a hot endpoint. See the Redis track (/tech/redis) for the primitives (SET/GET/EX, INCR/EXPIRE, EVAL) this module composes.

Course scope note. Go is compiled (github.com/redis/go-redis/v9 v9.21.0 / Go 1.26) and run against a real redis:7-alpine container (Redis 7.4.9) via podman — the <Success> blocks paste real output (a hit/miss, a TTL expiry, an invalidation bust, the limiter blocking the Nth request, and a fail-open on Redis-down). Spring/Kotlin is reasoned against Spring Data Redis / Lettuce (StringRedisTemplate, DefaultRedisScript) — no JVM toolchain in the authoring environment. The module adds no frontend step (it is backend/infra-shaped) and no paid dependency; the Redis container is local and $0.

8.c1 Infrastructure (module-owned Compose service; no migration)

The only new infra is a Redis container appended to the base docker-compose.yml (§3.1). It is module-owned, additive, pinned, and disposable (a cache needs no volume — it is safe to lose):

services:
  # ... base "db" (postgres:16) service unchanged ...

  redis:                       # OPTIONAL MODULE: cache. Module-owned; additive; $0.
    image: redis:7-alpine      # current 7.x pin; matches the Redis track (/tech/redis)
    ports:
      - "6379:6379"
    # no volume: the cache is disposable — losing it only forces a cold re-read from Postgres

Datasource env (canonical, both backends). The client reads one URL from REDIS_URL (redis://localhost:6379). Go parses it with redis.ParseURL; Spring binds it to spring.data.redis.url (Spring Boot 3). If REDIS_URL is unset, the module is off: the read path calls Postgres directly and the limiter allows every request. This keeps the base build runnable with the module code present but no Redis container — the same “optional, standalone-on-base” discipline as the other §8 modules.

No migration files are added (Redis is external state, not a Postgres schema). If a variant ever needed SQL, it would continue the numbered sequence after discovery’s 0007/0008 (Go) / V7/V8 (Spring) — but this module needs none.

8.c2 Read-through cache in front of GET /products

The base GET /products (§5.1) becomes: check Redis → on miss read Postgres → SET with a TTL → serve. The response is byte-identical on a hit, a miss, and the base uncached path.

• Cache key scheme (versioned namespaces).

  Key	Holds	TTL	Notes
  catalog:products:v1	the exact serialized GET /products JSON bytes	60 s	the whole listing; v1 rotates the namespace if the payload shape ever changes, so no stale-shape read survives a deploy
  ratelimit:checkout:<customerId>	the fixed-window request counter	= window (60 s)	§8.c4; per-customer (or ratelimit:checkout:ip:<ip> when unauthenticated)

• Byte-parity (load-bearing). The cache stores the serialized bytes of the response, so a HIT replays exactly what a MISS produced. Both are produced by the base no-HTML-escape encoder discipline (§8.s4): json.Encoder with SetEscapeHTML(false) and the trailing \n trimmed on Go; the identical String Jackson produces on Spring, stored/returned verbatim by a StringRedisSerializer. A HIT is therefore byte-for-byte equal to a MISS and to the base uncached response — Content-Type: application/json, no trailing newline.

• Read-through algorithm (identical logic both backends):

  b := GET catalog:products:v1
  if hit:            return b                                   # 200, cached bytes
  if miss (Nil):     fresh := <base Postgres read> ; serialize  # the base encoder
                     SET catalog:products:v1 fresh EX 60
                     return fresh                               # 200
  if Redis error:    return <base Postgres read>                # FAIL OPEN (§8.c5) — 200, not 503

  The miss sentinel is redis.Nil (Go) / a null from opsForValue().get (Spring) — a miss, not an error. Only a genuine connection error takes the fail-open arm.

• Advisory stock (the boundary, stated up front). The base GET /products payload includes stock (§5.1), so the cached listing carries a stock number — but that number is advisory display data, exactly like the storefront’s advisory displayPrice (§8.s2). It is fine for rendering a browse list; it is never read to make a stock decision. The authoritative stock lives only in the uncached guarded UPDATE at checkout (§5.3, §8.c5). A stale cached stock number can never cause an oversell, because the decision is never made from the cache.

• One-line note (if storefront is also on): the same read-through pattern applies to GET /storefront (storefront:home:v1, advisory like the display price) — but the taught path caches the base GET /products so the module stands alone.

8.c3 Cache invalidation — the hard problem, taught head-on

Two ways to bound staleness, taught by contrast:

• TTL-only. With just EX 60, a catalog write (a price change, a product becoming unavailable) is invisible to readers for up to the TTL — a bounded but real stale-read window of ≤ 60 s.
• Explicit invalidation. On a committed catalog write that changes what the listing shows — a price change, or a product crossing the in-stock ⇄ out-of-stock boundary (a stock-visibility change, not every per-unit decrement) — the writer busts the key: DEL catalog:products:v1. The next read misses and repopulates from Postgres. This shrinks the stale window to the tiny gap between COMMIT and the DEL.

The taught policy is both: explicit bust for catalog-shape changes (bounds staleness to ~ms) plus a TTL as the backstop (bounds staleness for anything a bust missed, and self-heals if a bust is ever dropped because Redis was briefly down). Crucially, the per-unit stock decrement on every sale does not bust the catalog — that would defeat the cache and, more importantly, is unnecessary: the listing’s stock is advisory (§8.c2). Only a visibility change (crossing zero, price change, add/remove) busts.

Ordering rule: write to Postgres, commit, then DEL — never DEL before commit (a concurrent read would repopulate the old value between the DEL and the commit, re-poisoning the cache).

8.c4 Rate-limit POST /checkout (shield the hot path WITHOUT caching it)

The base POST /checkout (§5.3) is protected by an atomic fixed-window counter keyed per customer (or per IP when unauthenticated). The request contract is unchanged; the module only adds a pre-flight limit check and one new response code.

• The counter is atomic in one round trip — INCR the key, EXPIRE it only on the first hit of the window, compare against the limit — in a single Lua script so there is no read-modify-write race (the /tech/redis rate-limiter pattern):

  -- KEYS[1] = ratelimit:checkout:<customerId>   ARGV[1] = limit   ARGV[2] = window seconds
  local n = redis.call('INCR', KEYS[1])
  if n == 1 then redis.call('EXPIRE', KEYS[1], ARGV[2]) end
  if n > tonumber(ARGV[1]) then return 0 end     -- blocked
  return 1                                        -- allowed

  (A sliding-window or token-bucket variant is a mentioned refinement; the fixed window is the taught baseline. The EXPIRE is guarded by n == 1 so the window starts on the first request and is never extended by later hits.)

• Contract. Default limit 30 requests / 60 s per customer (the demo uses limit=3 so the block is observable). On the (limit+1)-th request within the window:

  Condition	Status	Body
  Within the limit — checkout proceeds to the base flow (§5.3)	200 / 409 / 422 / 404 / 500	as base
  Over the limit for this customer in the current window	429	{"error":"rate_limited"}
  Rate-limiter backing store (Postgres) unreachable	503	{"error":"unavailable"} (see §8.c5)

  429 is a NEW code — the base contract has no 429 (§5.5). It is byte-identical on both backends, Content-Type: application/json, no trailing newline: {"error":"rate_limited"}.

• Shield, not cache. The checkout body, the guarded UPDATE, and the idempotency key are never cached — the limiter only counts requests in front of the untouched transaction. An over-limit request is rejected before the transaction opens, so a blocked checkout writes nothing.

8.c5 The never-cache boundary + fail-open policy (the module’s spine)

What is NEVER cached — and why. The stock decision and the checkout read path are never cached. A stale stock read used as a decision would reintroduce exactly the oversell the whole course eliminates. So:

• The base guarded UPDATE products SET stock = stock - $1 WHERE id = $2 AND stock >= $1 RETURNING unit_price (§5.3) stays the sole source of truth for stock — uncached, inside the one transaction. The cache is never consulted to decide whether a sale can proceed.
• The cached catalog:products:v1 listing’s stock is advisory display (§8.c2), never a decision input.
• Price at purchase is captured by the transaction’s RETURNING, never from the cached listing (mirroring the base “price at purchase time” invariant, §4.1, and the storefront’s advisory-vs-authoritative price split, §8.s2).

Fail-open vs fail-closed (taught explicitly). A Redis/cache outage must not take down checkout:

• Reads fail open. A Redis error on GET /products (a real connection error, not a redis.Nil miss) degrades to a direct Postgres read and still returns 200. A cache outage is invisible to correctness — the answer is merely slower.
• The rate-limiter fails open. A Redis error on the limiter allows the checkout. The limiter is a shield (against abuse/cost), not a correctness mechanism: even with the limiter down, the Postgres guard still prevents oversell and the idempotency key still prevents double-orders (§5.3). Losing rate limiting for a brief Redis blip beats rejecting real checkouts.
• When 503 fires. 503 {"error":"unavailable"} (the retryable code the sibling §8 modules already define) is reserved for when the authoritative store — Postgres — is unreachable, not Redis. A Redis outage never produces a 503 here, precisely because both paths fail open. This is the honest, non-contradictory reading of “503 if the backing store is down”: the backing store that matters is Postgres.
• Contrast: fail-closed would be correct for a limiter guarding a security boundary (e.g. a login throttle — if you can’t count attempts, reject). This limiter guards availability and cost, so fail-open is the right trade. Naming the axis is the lesson.

8.c6 Byte-parity, error bodies, money, time

• 200 parity. A cache HIT is byte-identical to a MISS and to the base uncached GET /products — the cache stores serialized bytes and both backends use the no-HTML-escape, no-trailing-newline encoder discipline (§8.s4). (The base products payload carries free-text — product and category names plus image URLs, §5.1; caching the bytes means the free-text escaping trap can never diverge a hit from a miss.)
• New error body. 429 {"error":"rate_limited"} — a code the base does not use — byte-identical on both backends, application/json, no trailing newline.
• Reused error bodies. 503 {"error":"unavailable"} (Postgres unreachable only; §8.c5) and 500 {"error":"internal"} follow the base §5.5 frame. 400 does not arise (base convention: malformed input is 422, a bad path is 404).
• Money is integer cents everywhere (unchanged from base; the cache stores the base payload verbatim).
• Time — the cache stores TTLs (Redis EX, seconds); no timestamp reaches the wire from this module, so the base createdAt RFC3339 rule (§5.4/§7) does not arise here.

8.c7 Parity points (Go default + Spring reasoned; SAME contract)

Critical invariant: a cache HIT is byte-identical to a MISS and to the base response; the 429 body is byte-identical across backends; and stock and the checkout decision are never cached. The base guarded UPDATE stays the uncached, transactional source of truth (§5.3), and a Redis outage fails open on both the read path and the limiter — so the cache makes reads fast and shields the hot path without ever letting a cache lie about stock in a way that matters.

sales-insights — Commerce Analytics (optional module)

Self-contained: this module stands alone on the base build, assumes no other module (not storefront, not order-lifecycle, not discovery — it does not depend on discovery’s seeded orders), and nothing in the core §4/§5 depends on it. Its schema is additive — three read-only analytic VIEWs over the base tables plus a module-owned demo order history — created by the module’s own migration files. No base table’s meaning is changed and no new datastore is introduced.

Summary. Turn the orders that already live in Postgres into decision-grade insight — in SQL, for $0, with no API key — and then, optionally, let Gemini narrate it. Two layers:

(1) The SQL analytics core (fully runnable, no key). Windowed aggregations over orders/order_items: revenue over time with a running total (SUM(...) OVER (ORDER BY day)), average order value, top products by units and revenue, low-stock, and a conversion-ish repeat-customer rate. Plus two state-of-the-art techniques, both pure SQL — the module’s differentiator:

• RFM customer segmentation — Recency / Frequency / Monetary scored 1..5 with ntile(5) and mapped to segments (champions, at-risk, promising, hibernating, …). A classic, genuinely-used segmentation done entirely with window functions and quantiles — the mechanics are the lesson.
• Market-basket association rules — support / confidence / lift for co-purchased product pairs from order_items co-occurrence. lift above 1 is a real association (the pair is bought together more than each product’s popularity predicts); raw co-count is not, because it is dominated by popular items. Teaching why lift beats a raw “bought together” count is the point.

(2) The optional AI layer (the “ai” option). A Gemini constrained-JSON board report that summarises the computed metrics into prose (a headline, a summary, and recommendations), mirroring Vitals ai-insights (§ai-insights) and Helix’s constrained-JSON judge. It is optional: the module is fully valuable on the SQL analytics alone. Only the aggregate numbers cross the boundary to the model — never raw order rows.

Spotlight framing. This is the relational + AI payoff of having every order in one SQL database: the segmentation and the basket rules exist for free beside the catalog, adjacent to the transactional spotlight and never touching it. Every query in this module is strictly read-only — it never mutates an order, never touches checkout, and never reads stock to make a decision. The transactional core (§1, §5.3) is untouched.

Course scope note. Go is compiled (pgx v5.10.0 / Go 1.26) and the SQL is run against postgres:16 via podman — the <Success> blocks paste real output for the revenue rollup, the RFM ntile segmentation, and the market-basket lift computation on the seeded order graph. Spring/Kotlin is reasoned against Spring 6 / Boot 3.4 / JdbcTemplate (no JVM toolchain in the authoring environment). The optional Gemini board report uses the pinned google.golang.org/genai v1.62.0 SDK: the request/response shape is verified by compiling against the SDK (go build passes), but there is no GOOGLE_API_KEY in the authoring env, so any claim about the model’s live prose is marked needsWebCheck — the SQL metrics (real) are the substance; the AI prose is a summary layer, never fabricated. The module adds no frontend step (it is analytics/board-report-shaped, like order-lifecycle) and no paid dependency; it is local and free like the base.

8.i1 Analytics views (module-owned; three read-only VIEWs, no new tables)

Created by the module’s own numbered migration files, after the base 0001/0002 (and after any storefront / order-lifecycle / discovery files, which this module does not assume; cache adds no migration): Go 0009_sales_insights.up.sql / .down.sql (the views) then 0010_sales_insights_seed.up.sql / .down.sql (demo order history); Spring V9__sales_insights.sql then V10__sales_insights_seed.sql. golang-migrate and Flyway both apply versions in order and tolerate gaps, so a learner who enables only sales-insights gets 0001,0002,0009,0010 applied cleanly. Each Go/Spring pair is a byte-for-byte copy of the SQL (the in-course “keep the two copies from drifting” rule). Flyway has no down files; the .down.sql files are golang-migrate only.

The module prefers VIEWs over new tables — the analytics are a view of the base order data, not a second copy of it. The down migration is DROP VIEW; no base object is touched.

-- 0009_sales_insights.up.sql (Go) / V9__sales_insights.sql (Spring)
-- OPTIONAL MODULE: sales-insights. Additive, read-only, self-contained. Stands alone on the BASE build.
-- Three analytics VIEWs over the base orders/order_items/products/customers. No new tables.

-- (1) Revenue over time: a per-day rollup with a WINDOWED running total.
CREATE OR REPLACE VIEW sales_daily_revenue AS
SELECT
  date_trunc('day', o.created_at)::date                    AS bucket_day,
  count(DISTINCT o.id)                                     AS orders,
  sum(oi.quantity)::bigint                                 AS units,
  sum(oi.quantity * oi.unit_price)::bigint                 AS revenue_cents,
  sum(sum(oi.quantity * oi.unit_price))
      OVER (ORDER BY date_trunc('day', o.created_at)::date)::bigint
                                                           AS running_revenue_cents
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY date_trunc('day', o.created_at)::date;

-- (2) RFM customer segmentation: Recency / Frequency / Monetary scored 1..5 with ntile(5),
--     then mapped to segments. Deterministic tiebreaks (customer_id) make the output byte-stable.
CREATE OR REPLACE VIEW customer_rfm AS
WITH per_customer AS (
  SELECT
    o.customer_id,
    (current_date - max(o.created_at)::date) AS recency_days,   -- days since last order (lower = better)
    count(*)                                 AS frequency,
    sum(o.total)::bigint                     AS monetary_cents,
    max(o.created_at)                        AS last_order_at
  FROM orders o
  GROUP BY o.customer_id
),
scored AS (
  SELECT
    per_customer.*,
    ntile(5) OVER (ORDER BY recency_days DESC, customer_id)  AS r,  -- most recent -> 5
    ntile(5) OVER (ORDER BY frequency ASC, customer_id)      AS f,  -- most frequent -> 5
    ntile(5) OVER (ORDER BY monetary_cents ASC, customer_id) AS m   -- highest spend -> 5
  FROM per_customer
)
SELECT
  s.customer_id, c.email,
  s.recency_days, s.frequency, s.monetary_cents, s.last_order_at,
  s.r, s.f, s.m,
  CASE
    WHEN s.r >= 4 AND s.f >= 4 THEN 'champions'
    WHEN s.r <= 2 AND s.f >= 3 THEN 'at_risk'        -- was frequent, now stale
    WHEN s.f >= 4              THEN 'loyal'
    WHEN s.r >= 4 AND s.f <= 2 THEN 'promising'      -- recent, few orders
    WHEN s.r <= 2 AND s.f <= 2 THEN 'hibernating'
    ELSE 'needs_attention'
  END AS segment
FROM scored s
JOIN customers c ON c.id = s.customer_id;

-- (3) Market-basket association rules: support / confidence / lift for co-purchased product PAIRS.
--     lift = P(a,b) / (P(a)*P(b)) = co*N / (cnt_a*cnt_b); lift above 1 = a real association (beats popularity).
--     Ratios are scaled x1000 to stay EXACT integers on the wire (a raw float is a Go/Jackson byte hazard).
CREATE OR REPLACE VIEW market_basket AS
WITH order_products AS (          -- DISTINCT so a SKU bought twice in one order counts once
  SELECT DISTINCT oi.order_id, oi.product_id FROM order_items oi
),
total AS (SELECT count(DISTINCT order_id)::numeric AS n FROM order_products),
prod AS (                         -- per-product order count (marginal support numerator)
  SELECT product_id, count(*)::numeric AS cnt FROM order_products GROUP BY product_id
),
pair AS (                         -- unordered pairs (a < b): each pair once
  SELECT op1.product_id AS a, op2.product_id AS b, count(*)::numeric AS co
  FROM order_products op1
  JOIN order_products op2 ON op2.order_id = op1.order_id AND op2.product_id > op1.product_id
  GROUP BY op1.product_id, op2.product_id
)
SELECT
  pair.a AS product_a, pa.name AS name_a,
  pair.b AS product_b, pb.name AS name_b,
  pair.co::bigint                                                       AS co_count,
  round(pair.co / total.n * 1000)::bigint                              AS support_x1000,
  round(pair.co / prod_a.cnt * 1000)::bigint                           AS confidence_a_to_b_x1000,
  round(pair.co / prod_b.cnt * 1000)::bigint                           AS confidence_b_to_a_x1000,
  round(pair.co * total.n / (prod_a.cnt * prod_b.cnt) * 1000)::bigint  AS lift_x1000
FROM pair
CROSS JOIN total
JOIN prod prod_a ON prod_a.product_id = pair.a
JOIN prod prod_b ON prod_b.product_id = pair.b
JOIN products pa ON pa.id = pair.a
JOIN products pb ON pb.id = pair.b
ORDER BY lift_x1000 DESC, co_count DESC, product_a, product_b;