Aurora Commerce

# docker-compose.yml
services:
  db:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: dev
      POSTGRES_DB: aurora
    ports: ["5432:5432"]
    volumes: ["pgdata:/var/lib/postgresql/data"]
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 5s
      retries: 5
volumes: { pgdata: {} }

docker compose up -d
export DATABASE_URL="postgres://postgres:dev@localhost:5432/aurora?sslmode=disable"
# Primary check — Docker only, no host psql needed (runs psql INSIDE the container):
docker compose exec db psql -U postgres -d aurora -c "select version();"
# Optional variant, only if you have the host psql client (libpq):
# psql "$DATABASE_URL" -c "select version();"

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; 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 created first
);

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 API never serves image bytes
  position   INTEGER NOT NULL DEFAULT 0,                   -- gallery order (GET /products sorts 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; minted by the DB, core PostgreSQL (13+)
  customer_id BIGINT NULL REFERENCES customers(id),        -- NULL while anonymous; set when checkout claims the cart
  status      TEXT   NOT NULL DEFAULT 'active'
                CHECK (status IN ('active','converted','abandoned')),  -- 'abandoned' is reserved: no producer, like orders' 'shipped'
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()           -- touched by every cart write
);

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
  qty        INTEGER NOT NULL CHECK (qty > 0),
  PRIMARY KEY (cart_id, product_id)                        -- one line per product; POST /cart/items merges via ON CONFLICT
);

CREATE TABLE orders (
  id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id     BIGINT NOT NULL REFERENCES customers(id),
  total           BIGINT NOT NULL CHECK (total >= 0),
  status          TEXT NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending','shipped','cancelled')),
  idempotency_key TEXT,                                    -- safe-retry key; see the idempotency step
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Partial unique index: many NULLs allowed, but a set key is unique.
CREATE UNIQUE INDEX orders_idem_key ON orders (idempotency_key)
  WHERE idempotency_key IS NOT NULL;

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 at purchase
  PRIMARY KEY (order_id, product_id)
);

# Docker only (no host psql needed) — apply the init migration file directly to check it now:
docker compose exec -T db psql -U postgres -d aurora < db/migrations/0001_init.up.sql
# Optional variant, if you have the host psql client (libpq):
# psql "$DATABASE_URL" -f db/migrations/0001_init.up.sql

-- one customer first: orders.customer_id NOT NULL REFERENCES customers(id)
INSERT INTO customers (email) VALUES ('demo@aurora.test')
  ON CONFLICT (email) DO NOTHING;

-- categories BEFORE products: products.category_id is NOT NULL. 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 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 GET /products 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;

# Docker only (no host psql needed) — apply the seed migration file directly to check it now:
docker compose exec -T db psql -U postgres -d aurora < db/migrations/0002_seed.up.sql
# Optional variant, if you have the host psql client (libpq):
# psql "$DATABASE_URL" -f db/migrations/0002_seed.up.sql

go mod init github.com/you/aurora-api
go get github.com/jackc/pgx/v5

// cmd/api/main.go
package main

import (
	"bytes"
	"cmp"
	"context"
	"encoding/json"
	"errors"
	"log/slog"
	"net/http"
	"os"
	"os/signal"
	"syscall"
	"time"

	"github.com/jackc/pgx/v5/pgxpool"
	// The cart + checkout steps add "fmt", "github.com/jackc/pgx/v5", and ".../pgx/v5/pgconn" here —
	// see their "add these imports" notes.
)

type Category struct {
	ID       int64  `json:"id"`
	Slug     string `json:"slug"`
	Name     string `json:"name"`
	ImageURL string `json:"imageUrl"`
}

type Image struct {
	URL string `json:"url"`
}

type Product struct {
	ID        int64    `json:"id"`
	Name      string   `json:"name"`
	UnitPrice int64    `json:"unitPrice"` // cents; camelCase on the wire
	Stock     int      `json:"stock"`
	Category  Category `json:"category"`  // always present: products.category_id is NOT NULL
	Images    []Image  `json:"images"`    // ordered by position; [] when a product has no image rows
}

// Store is the contract the handlers consume; the cart and checkout land on it next.
type Store struct{ pool *pgxpool.Pool }

func (s *Store) Products(ctx context.Context) ([]Product, error) {
	rows, err := s.pool.Query(ctx, `
		SELECT p.id, p.name, p.unit_price, p.stock,
		       c.id, c.slug, c.name, COALESCE(c.image_url, '')
		FROM products p
		JOIN categories c ON c.id = p.category_id
		ORDER BY p.id`)
	if err != nil { return nil, err }
	defer rows.Close()
	out := []Product{}
	idx := map[int64]int{}
	for rows.Next() {
		p := Product{Images: []Image{}} // non-nil: no image rows serializes as [], never null
		if err := rows.Scan(&p.ID, &p.Name, &p.UnitPrice, &p.Stock,
			&p.Category.ID, &p.Category.Slug, &p.Category.Name, &p.Category.ImageURL); err != nil { return nil, err }
		idx[p.ID] = len(out)
		out = append(out, p)
	}
	if err := rows.Err(); err != nil { return nil, err }
	imgs, err := s.pool.Query(ctx,
		`SELECT product_id, url FROM product_images ORDER BY product_id, position`)
	if err != nil { return nil, err }
	defer imgs.Close()
	for imgs.Next() {
		var pid int64
		var u string
		if err := imgs.Scan(&pid, &u); err != nil { return nil, err }
		if i, ok := idx[pid]; ok { out[i].Images = append(out[i].Images, Image{URL: u}) }
	}
	return out, imgs.Err()
}

func newRouter(s *Store) *http.ServeMux {
	mux := http.NewServeMux()
	mux.HandleFunc("GET /healthz", func(w http.ResponseWriter, r *http.Request) { _, _ = w.Write([]byte("ok")) })
	mux.HandleFunc("GET /products", func(w http.ResponseWriter, r *http.Request) {
		ps, err := s.Products(r.Context())
		if err != nil { slog.Error("products", "err", err); writeJSON(w, 500, map[string]string{"error": "internal"}); return }
		writeJSON(w, 200, ps)
	})
	return mux // cart + checkout + orders routes register here in later steps
}

// writeJSON is the ONE writer every response goes through. It disables HTML escaping and trims the
// encoder's trailing newline so the bytes match Spring/Jackson exactly: the default json.Encoder would
// turn the & in a real image URL's query params (or a name like "Tees & Mugs") into \u0026 and
// append a newline — Jackson emits the literal byte and no trailing newline.
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"))
}

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()
	slog.SetDefault(slog.New(slog.NewJSONHandler(os.Stdout, nil)))

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

	// The observability step wraps this Handler with withTrace(...) — a bare newRouter here means
	// the trace id never reaches Checkout. See "Structured logging in Go".
	srv := &http.Server{Addr: ":" + cmp.Or(os.Getenv("PORT"), "8080"), Handler: newRouter(&Store{pool})}
	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)
}

export JDBC_DATABASE_URL="jdbc:postgresql://localhost:5432/aurora"
export DB_USER=postgres
export DB_PASSWORD=dev

// build.gradle.kts (key deps)
dependencies {
    implementation("org.springframework.boot:spring-boot-starter-web")
    implementation("org.springframework.boot:spring-boot-starter-jdbc")
    runtimeOnly("org.postgresql:postgresql")
    // Flyway 10+ (versions from the Boot BOM): Postgres support is a SEPARATE module —
    // flyway-core alone throws "Unsupported Database: PostgreSQL 16" and the app won't start.
    implementation("org.flywaydb:flyway-core")
    runtimeOnly("org.flywaydb:flyway-database-postgresql")
}

# JDBC URL shape (NOT the Go path's libpq DATABASE_URL):
spring.datasource.url=${JDBC_DATABASE_URL:jdbc:postgresql://localhost:5432/aurora}
spring.datasource.username=${DB_USER:postgres}
spring.datasource.password=${DB_PASSWORD:dev}
server.shutdown=graceful

// web/ProductController.kt
@RestController
class ProductController(private val jdbc: JdbcTemplate) {
    data class Category(val id: Long, val slug: String, val name: String, val imageUrl: String)
    data class Image(val url: String)
    data class Product(val id: Long, val name: String, val unitPrice: Long, val stock: Int,
                       val category: Category, val images: List<Image>)

    @GetMapping("/products")
    fun list(): List<Product> {
        // one read for all image rows, grouped per product (ordered by position)
        val images = jdbc.query(
            "SELECT product_id, url FROM product_images ORDER BY product_id, position",
        ) { rs, _ -> rs.getLong("product_id") to Image(rs.getString("url")) }
            .groupBy({ it.first }, { it.second })
        return jdbc.query(
            """SELECT p.id, p.name, p.unit_price, p.stock,
                      c.id AS cat_id, c.slug, c.name AS cat_name, COALESCE(c.image_url, '') AS cat_image
               FROM products p JOIN categories c ON c.id = p.category_id
               ORDER BY p.id""",
        ) { rs, _ ->
            Product(
                rs.getLong("id"), rs.getString("name"), rs.getLong("unit_price"), rs.getInt("stock"),
                Category(rs.getLong("cat_id"), rs.getString("slug"), rs.getString("cat_name"), rs.getString("cat_image")),
                images[rs.getLong("id")] ?: emptyList(),   // [] on the wire, never null
            )
        }
    }
}

// The cart representation: the 200 body of EVERY cart endpoint. The token is NOT here —
// it travels only in the X-Cart-Token headers, so the body stays uniform.
type CartItem struct {
	ProductID int64  `json:"productId"`
	Name      string `json:"name"`
	Qty       int    `json:"qty"`
	UnitPrice int64  `json:"unitPrice"`
	LineTotal int64  `json:"lineTotal"`
}
type Cart struct {
	Total     int64      `json:"total"`
	UpdatedAt string     `json:"updatedAt"` // RFC3339 UTC Z, no sub-second (the shared wire rule)
	Items     []CartItem `json:"items"`
}

var ErrCartNotFound = errors.New("cart not found")

// isBadUUID: a malformed X-Cart-Token can never match a cart — fold the uuid cast
// error (SQLSTATE 22P02) into "unknown token" instead of surfacing a 500.
func isBadUUID(err error) bool {
	var pgErr *pgconn.PgError
	return errors.As(err, &pgErr) && pgErr.Code == "22P02"
}

// AddCartItem adds a line; "" token -> create an anonymous cart first. Returns the cart + its token.
func (s *Store) AddCartItem(ctx context.Context, token string, productID int64, qty int) (Cart, string, error) {
	tx, err := s.pool.Begin(ctx)
	if err != nil { return Cart{}, "", err }
	defer tx.Rollback(ctx)
	var cartID int64
	if token == "" {
		// the token is minted by the column default gen_random_uuid() — never by app code
		if err := tx.QueryRow(ctx,
			`INSERT INTO carts DEFAULT VALUES RETURNING id, token::text`).Scan(&cartID, &token); err != nil {
			return Cart{}, "", err
		}
	} else if err := tx.QueryRow(ctx,
		`SELECT id FROM carts WHERE token = $1 AND status = 'active'`, token).Scan(&cartID); err != nil {
		if errors.Is(err, pgx.ErrNoRows) || isBadUUID(err) { return Cart{}, "", ErrCartNotFound }
		return Cart{}, "", err
	}
	// merge, not overwrite: a re-add of the same product sums quantities in ONE statement
	if _, err := tx.Exec(ctx,
		`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`,
		cartID, productID, qty); err != nil {
		return Cart{}, "", err // FK 23503 (unknown productId) -> the handler maps 404 not_found
	}
	if _, err := tx.Exec(ctx,
		`UPDATE carts SET updated_at = now() WHERE id = $1`, cartID); err != nil { return Cart{}, "", err }
	if err := tx.Commit(ctx); err != nil { return Cart{}, "", err }
	cart, err := s.CartByToken(ctx, token)
	return cart, token, err
}

// CartByToken is GET /cart: live advisory prices + server-computed integer-cent totals.
func (s *Store) CartByToken(ctx context.Context, token string) (Cart, error) {
	cart := Cart{Items: []CartItem{}} // non-nil: an empty cart serializes as {"items":[]}
	var updatedAt time.Time
	if err := s.pool.QueryRow(ctx,
		`SELECT updated_at FROM carts WHERE token = $1 AND status = 'active'`, token).Scan(&updatedAt); err != nil {
		if errors.Is(err, pgx.ErrNoRows) || isBadUUID(err) { return Cart{}, ErrCartNotFound }
		return Cart{}, err
	}
	cart.UpdatedAt = updatedAt.UTC().Format(time.RFC3339) // trailing Z, no sub-second
	rows, err := s.pool.Query(ctx, `
		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`, token)
	if err != nil { return Cart{}, err }
	defer rows.Close()
	for rows.Next() {
		var it CartItem
		if err := rows.Scan(&it.ProductID, &it.Name, &it.Qty, &it.UnitPrice, &it.LineTotal); err != nil {
			return Cart{}, err
		}
		cart.Total += it.LineTotal
		cart.Items = append(cart.Items, it)
	}
	return cart, rows.Err()
}

// in newRouter(s) — every cart response repeats the token in the X-Cart-Token response header:
mux.HandleFunc("POST /cart/items", func(w http.ResponseWriter, r *http.Request) {
	var req struct {
		ProductID int64 `json:"productId"`
		Qty       int   `json:"qty"`
	}
	if err := json.NewDecoder(http.MaxBytesReader(w, r.Body, 1<<20)).Decode(&req); err != nil ||
		req.ProductID == 0 || req.Qty < 1 {
		writeJSON(w, 422, map[string]string{"error": "invalid_request"}); return
	}
	// the header is OPTIONAL here (only here): absent -> the server creates an anonymous cart
	cart, token, err := s.AddCartItem(r.Context(), r.Header.Get("X-Cart-Token"), req.ProductID, req.Qty)
	if err != nil { writeCartErr(w, err); return }
	w.Header().Set("X-Cart-Token", token) // on creation, this is where the client learns its token
	writeJSON(w, 200, cart)
})
mux.HandleFunc("GET /cart", func(w http.ResponseWriter, r *http.Request) {
	token := r.Header.Get("X-Cart-Token") // required from here on: absent/unknown -> 404 cart_not_found
	cart, err := s.CartByToken(r.Context(), token)
	if err != nil { writeCartErr(w, err); return }
	w.Header().Set("X-Cart-Token", token)
	writeJSON(w, 200, cart)
})

// writeCartErr maps the shared cart error table (all four endpoints).
func writeCartErr(w http.ResponseWriter, err error) {
	var pgErr *pgconn.PgError
	switch {
	case errors.Is(err, ErrCartNotFound):
		writeJSON(w, 404, map[string]string{"error": "cart_not_found"})
	case errors.As(err, &pgErr) && pgErr.Code == "23503": // unknown productId (cart_items FK)
		writeJSON(w, 404, map[string]string{"error": "not_found"})
	default:
		writeJSON(w, 500, map[string]string{"error": "internal"})
	}
}

// The SQL for the two line-edit endpoints (wire them via the prompt):
//   PUT /cart/items/{productId} {qty}  — the idempotent ABSOLUTE SET (works for a new line too):
//     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}     — naturally idempotent (an absent line is a no-op 200):
//     DELETE FROM cart_items WHERE cart_id = $1 AND product_id = $2;
// Both also touch carts.updated_at in the same tx; a non-numeric {productId} is 404 not_found.

class CartNotFoundException(token: String?) : RuntimeException("cart $token not found")

data class CartItem(val productId: Long, val name: String, val qty: Int,
                    val unitPrice: Long, val lineTotal: Long)
data class Cart(val total: Long, val updatedAt: String, val items: List<CartItem>)

@Service
class CartService(private val jdbc: JdbcTemplate) {

    private fun resolveActiveCart(token: String): Long = try {
        jdbc.queryForObject(
            "SELECT id FROM carts WHERE token = ?::uuid AND status = 'active'", Long::class.java, token)!!
    } catch (e: EmptyResultDataAccessException) {
        throw CartNotFoundException(token)          // unknown or already-converted token
    } catch (e: DataAccessException) {
        throw CartNotFoundException(token)          // malformed token: the uuid cast (22P02) can't match any cart
    }

    @Transactional
    fun addItem(token: String?, productId: Long, qty: Int): Pair<Cart, String> {
        val (cartId, t) = if (token == null) {
            // the token is minted by the column default gen_random_uuid() — never by app code
            val row = jdbc.queryForMap("INSERT INTO carts DEFAULT VALUES RETURNING id, token::text")
            (row["id"] as Long) to (row["token"] as String)
        } else resolveActiveCart(token) to token
        // merge, not overwrite — identical SQL to the Go path
        jdbc.update(
            """INSERT INTO cart_items (cart_id, product_id, qty) VALUES (?, ?, ?)
               ON CONFLICT (cart_id, product_id) DO UPDATE SET qty = cart_items.qty + EXCLUDED.qty""",
            cartId, productId, qty)                 // FK 23503 -> the advice maps 404 not_found
        jdbc.update("UPDATE carts SET updated_at = now() WHERE id = ?", cartId)
        return cartByToken(t) to t
    }

    fun cartByToken(token: String): Cart {
        val updatedAt = try {
            jdbc.queryForObject(
                "SELECT updated_at FROM carts WHERE token = ?::uuid AND status = 'active'",
                java.time.OffsetDateTime::class.java, token)!!
        } catch (e: EmptyResultDataAccessException) { throw CartNotFoundException(token) }
          catch (e: DataAccessException)            { throw CartNotFoundException(token) }
        val items = jdbc.query(
            """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 = ?::uuid AND c.status = 'active'
               ORDER BY ci.product_id""",
            { rs, _ -> CartItem(rs.getLong("product_id"), rs.getString("name"), rs.getInt("qty"),
                                rs.getLong("unit_price"), rs.getLong("line_total")) }, token)
        return Cart(
            total = items.sumOf { it.lineTotal },   // server-computed integer cents
            updatedAt = updatedAt.toInstant().truncatedTo(java.time.temporal.ChronoUnit.SECONDS).toString(),
            items = items,                          // a non-null List: an empty cart is {"items":[]}
        )
    }
}

data class AddItemReq(val productId: Long?, val qty: Int?)

@RestController
class CartController(private val svc: CartService) {
    @PostMapping("/cart/items")
    fun add(
        @RequestBody req: AddItemReq,               // {productId, qty} — validate before touching the DB
        @RequestHeader(value = "X-Cart-Token", required = false) token: String?,
    ): ResponseEntity<Any> {
        val productId = req.productId ?: 0L
        val qty = req.qty ?: 0
        if (productId == 0L || qty < 1)
            return ResponseEntity.unprocessableEntity().body(mapOf("error" to "invalid_request"))
        val (cart, t) = svc.addItem(token, productId, qty)
        return ResponseEntity.ok().header("X-Cart-Token", t).body(cart) // the client learns its token here
    }

    @GetMapping("/cart")
    fun read(@RequestHeader(value = "X-Cart-Token", required = false) token: String?): ResponseEntity<Any> {
        if (token == null) return ResponseEntity.status(404).body(mapOf("error" to "cart_not_found"))
        return ResponseEntity.ok().header("X-Cart-Token", token).body(svc.cartByToken(token))
    }
    // PUT /cart/items/{productId} (absolute-set upsert) + DELETE /cart/items/{productId} via the prompt;
    // bind {productId} as String + toLongOrNull() ?: 404 (the non-numeric-id pin).
}

# Terminal 1 — Session A:
docker compose exec db psql -U postgres -d aurora
# Terminal 2 — Session B:
docker compose exec db psql -U postgres -d aurora
# (host-psql variant: psql "$DATABASE_URL" in each terminal)

UPDATE products SET stock = 1 WHERE id = 1;

BEGIN;
UPDATE products
  SET stock = stock - 1
  WHERE id = 1 AND stock >= 1;   -- A sees: UPDATE 1
-- ...now switch to Session B and run its UPDATE (it will block) ...
COMMIT;                          -- releases the row lock; B unblocks

BEGIN;
UPDATE products
  SET stock = stock - 1
  WHERE id = 1 AND stock >= 1;   -- BLOCKS on A's row lock, then sees: UPDATE 0 (the loser)
ROLLBACK;                        -- nothing to commit; stock stayed at 0
-- final: SELECT stock FROM products WHERE id = 1;  -> 0, sold exactly once

// A cart line as read INSIDE the checkout tx. Internal only — the client sends no lines (§5.3),
// so no json tags are needed.
type Line struct {
	ProductID int64
	Qty       int
}

// ErrOutOfStock: a line's qty exceeds available stock, so the guarded UPDATE matches zero rows.
// POST /checkout maps it to 409. ErrEmptyCart maps to 422 — nothing has been written when it fires.
var ErrOutOfStock = errors.New("out of stock")
var ErrEmptyCart = errors.New("empty cart")

// Checkout is ONE transaction: read the cart's lines, run the guarded UPDATE per line, claim the
// cart, insert order + items, persist the idempotency key. idemKey may be ""; a KNOWN key never
// reaches this method — the handler replays it first (§5.3 replay ordering).
func (s *Store) Checkout(ctx context.Context, customerID int64, cartToken, idemKey string) (int64, error) {
	tx, err := s.pool.Begin(ctx)
	if err != nil { return 0, err }
	defer tx.Rollback(ctx) // no-op after a successful Commit

	// resolve the ACTIVE cart — a converted or unknown token is 404 cart_not_found
	var cartID int64
	if err := tx.QueryRow(ctx,
		`SELECT id FROM carts WHERE token = $1 AND status = 'active'`, cartToken).Scan(&cartID); err != nil {
		if errors.Is(err, pgx.ErrNoRows) || isBadUUID(err) { return 0, ErrCartNotFound }
		return 0, err
	}
	// the server reads the cart's lines — the client sent none. One row per product
	// (the cart_items PK): duplicate lines cannot occur, so no aggregation step is needed.
	rows, err := tx.Query(ctx,
		`SELECT product_id, qty FROM cart_items WHERE cart_id = $1 ORDER BY product_id`, cartID)
	if err != nil { return 0, err }
	var lines []Line
	for rows.Next() {
		var l Line
		if err := rows.Scan(&l.ProductID, &l.Qty); err != nil { rows.Close(); return 0, err }
		lines = append(lines, l)
	}
	rows.Close()
	if err := rows.Err(); err != nil { return 0, err }
	if len(lines) == 0 { return 0, ErrEmptyCart } // 422; nothing has been written

	var total int64
	prices := make([]int64, len(lines))
	for i, l := range lines {
		// THE SPOTLIGHT, unchanged: guard + decrement + capture price in ONE statement
		err := tx.QueryRow(ctx,
			`UPDATE products SET stock = stock - $1
			   WHERE id = $2 AND stock >= $1
			 RETURNING unit_price`, l.Qty, l.ProductID).Scan(&prices[i])
		if errors.Is(err, pgx.ErrNoRows) { // 0 rows updated == insufficient stock
			return 0, fmt.Errorf("product %d: %w", l.ProductID, ErrOutOfStock)
		}
		if err != nil { return 0, err }
		total += prices[i] * int64(l.Qty)
	}

	// claim the cart: the anonymous cart becomes the customer's at the instant the stock moves
	claim, err := tx.Exec(ctx,
		`UPDATE carts SET customer_id = $1, status = 'converted', updated_at = now()
		 WHERE id = $2 AND status = 'active'`, customerID, cartID)
	if err != nil { return 0, err }
	if claim.RowsAffected() == 0 { // raced: a concurrent request converted it first
		return 0, ErrCartNotFound
	}

	var key any
	if idemKey != "" { key = idemKey } // NULL when empty so the partial unique index stays happy
	var orderID int64
	if err := tx.QueryRow(ctx,
		`INSERT INTO orders (customer_id, total, idempotency_key) VALUES ($1, $2, $3) RETURNING id`,
		customerID, total, key).Scan(&orderID); err != nil { return 0, err }

	for i, l := range lines {
		if _, err := tx.Exec(ctx,
			`INSERT INTO order_items (order_id, product_id, quantity, unit_price)
			   VALUES ($1, $2, $3, $4)`,
			orderID, l.ProductID, l.Qty, prices[i]); err != nil { return 0, err }
	}
	return orderID, tx.Commit(ctx)
}

// A cart line as read INSIDE the checkout tx. Internal only — the client sends no lines (§5.3).
data class Line(val productId: Long, val qty: Int)

class OutOfStockException(productId: Long) : RuntimeException("product $productId out of stock")
class EmptyCartException : RuntimeException("cart is empty")           // -> 422; nothing written yet
// CartNotFoundException comes from the cart step.

@Service
class CheckoutService(private val jdbc: JdbcTemplate) {

    // idemKey may be null; a KNOWN key never reaches this method — the controller replays it
    // first (§5.3 replay ordering).
    @Transactional
    fun checkout(customerId: Long, cartToken: String, idemKey: String?): Long {
        // resolve the ACTIVE cart — a converted/unknown token is 404 cart_not_found
        val cartId = try {
            jdbc.queryForObject(
                "SELECT id FROM carts WHERE token = ?::uuid AND status = 'active'",
                Long::class.java, cartToken,
            )!!
        } catch (e: EmptyResultDataAccessException) {
            throw CartNotFoundException(cartToken)
        } catch (e: DataAccessException) {
            throw CartNotFoundException(cartToken) // malformed token: the uuid cast can't match any cart
        }
        // the server reads the cart's lines — one row per product (the cart_items PK), so
        // duplicate lines cannot occur and no aggregation step is needed.
        val lines = jdbc.query(
            "SELECT product_id, qty FROM cart_items WHERE cart_id = ? ORDER BY product_id",
            { rs, _ -> Line(rs.getLong("product_id"), rs.getInt("qty")) }, cartId,
        )
        if (lines.isEmpty()) throw EmptyCartException()

        var total = 0L
        val prices = LongArray(lines.size)
        for ((i, l) in lines.withIndex()) {
            // THE SPOTLIGHT, unchanged: guard + decrement + capture price in ONE statement
            prices[i] = try {
                jdbc.queryForObject(
                    "UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ? RETURNING unit_price",
                    Long::class.java, l.qty, l.productId, l.qty,
                )!!
            } catch (e: EmptyResultDataAccessException) {
                throw OutOfStockException(l.productId) // 0 rows updated -> rolls the whole tx back
            }
            total += prices[i] * l.qty
        }
        // claim the cart: anonymous -> the customer's, at the instant the stock moves
        val claimed = jdbc.update(
            "UPDATE carts SET customer_id = ?, status = 'converted', updated_at = now() " +
                "WHERE id = ? AND status = 'active'", customerId, cartId,
        )
        if (claimed == 0) throw CartNotFoundException(cartToken) // raced: converted concurrently
        val orderId = jdbc.queryForObject(
            "INSERT INTO orders (customer_id, total, idempotency_key) VALUES (?, ?, ?) RETURNING id",
            Long::class.java, customerId, total, idemKey,
        )!!
        for ((i, l) in lines.withIndex()) {
            jdbc.update(
                "INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (?, ?, ?, ?)",
                orderId, l.productId, l.qty, prices[i],
            )
        }
        return orderId
    }
}

type checkoutReq struct {
	CustomerID int64 `json:"customerId"`
}

func (s *Store) orderIDByKey(ctx context.Context, key string) (int64, error) {
	var id int64
	err := s.pool.QueryRow(ctx, `SELECT id FROM orders WHERE idempotency_key = $1`, key).Scan(&id)
	return id, err
}

// in newRouter(s):
mux.HandleFunc("POST /checkout", func(w http.ResponseWriter, r *http.Request) {
	var req checkoutReq
	if err := json.NewDecoder(http.MaxBytesReader(w, r.Body, 1<<20)).Decode(&req); err != nil ||
		req.CustomerID == 0 {
		writeJSON(w, 422, map[string]string{"error": "invalid_request"}); return
	}
	idem := r.Header.Get("Idempotency-Key")

	// REPLAY FIRST (load-bearing): a successful checkout CONVERTED the cart, so a retry's token
	// no longer resolves. Look the key up BEFORE the cart, or every honest retry 404s.
	if idem != "" {
		if id, err := s.orderIDByKey(r.Context(), idem); err == nil {
			writeJSON(w, 200, map[string]int64{"orderId": id}); return
		}
	}
	token := r.Header.Get("X-Cart-Token")
	if token == "" { // required here (only POST /cart/items may omit it)
		writeJSON(w, 404, map[string]string{"error": "cart_not_found"}); return
	}

	orderID, err := s.Checkout(r.Context(), req.CustomerID, token, idem)
	switch {
	case err == nil:
		writeJSON(w, 200, map[string]int64{"orderId": orderID})
	case errors.Is(err, ErrOutOfStock):
		writeJSON(w, 409, map[string]string{"error": "out_of_stock"})
	case errors.Is(err, ErrCartNotFound):
		writeJSON(w, 404, map[string]string{"error": "cart_not_found"})
	case errors.Is(err, ErrEmptyCart):
		writeJSON(w, 422, map[string]string{"error": "invalid_request"})
	default:
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) && pgErr.Code == "23503" { // FK violation: unknown customerId
			writeJSON(w, 404, map[string]string{"error": "not_found"}); return
		}
		// race backstop: a concurrent duplicate hit orders_idem_key (23505); its tx rolled back
		// (stock decrement + cart claim released) — replay the winner's order. Gate on the
		// constraint NAME so no other unique index is ever misread as a replay.
		if idem != "" && errors.As(err, &pgErr) && pgErr.Code == "23505" && pgErr.ConstraintName == "orders_idem_key" {
			if id, e := s.orderIDByKey(r.Context(), idem); e == nil {
				writeJSON(w, 200, map[string]int64{"orderId": id}); return
			}
		}
		writeJSON(w, 500, map[string]string{"error": "internal"})
	}
})

data class CheckoutReq(val customerId: Long)

@RestController
class CheckoutController(private val svc: CheckoutService, private val jdbc: JdbcTemplate) {
    @PostMapping("/checkout")
    fun checkout(
        @RequestBody req: CheckoutReq,
        @RequestHeader(value = "X-Cart-Token", required = false) cartToken: String?,
        @RequestHeader(value = "Idempotency-Key", required = false) idemKey: String?,
    ): ResponseEntity<Map<String, Any>> {
        if (req.customerId == 0L)
            return ResponseEntity.unprocessableEntity().body(mapOf("error" to "invalid_request"))
        // REPLAY FIRST (load-bearing): a successful checkout CONVERTED the cart, so a retry's
        // token no longer resolves. Look the key up BEFORE the cart, or every honest retry 404s.
        if (idemKey != null) {
            jdbc.query("SELECT id FROM orders WHERE idempotency_key = ?",
                { rs, _ -> rs.getLong("id") }, idemKey)
                .firstOrNull()?.let { return ResponseEntity.ok(mapOf("orderId" to it)) }
        }
        if (cartToken == null) // required here (only POST /cart/items may omit it)
            return ResponseEntity.status(404).body(mapOf("error" to "cart_not_found"))
        return try {
            ResponseEntity.ok(mapOf("orderId" to svc.checkout(req.customerId, cartToken, idemKey)))
        } catch (e: DuplicateKeyException) {
            // race backstop: a concurrent duplicate hit orders_idem_key (23505); the @Transactional
            // rolled back (stock + cart claim released) — this lookup runs OUTSIDE that dead tx.
            val id = jdbc.queryForObject(
                "SELECT id FROM orders WHERE idempotency_key = ?", Long::class.java, idemKey)!!
            ResponseEntity.ok(mapOf("orderId" to id))
        }
    }
}

@RestControllerAdvice
class ApiExceptionHandler {
    @ExceptionHandler(OutOfStockException::class)
    fun outOfStock(e: OutOfStockException) =
        ResponseEntity.status(409).body(mapOf("error" to "out_of_stock"))
    @ExceptionHandler(CartNotFoundException::class)
    fun cartNotFound(e: CartNotFoundException) =
        ResponseEntity.status(404).body(mapOf("error" to "cart_not_found"))
    @ExceptionHandler(EmptyCartException::class)
    fun emptyCart(e: EmptyCartException) =
        ResponseEntity.unprocessableEntity().body(mapOf("error" to "invalid_request"))
    @ExceptionHandler(EmptyResultDataAccessException::class)
    fun notFound(e: EmptyResultDataAccessException) =
        ResponseEntity.status(404).body(mapOf("error" to "not_found"))
    // Unknown customerId (checkout) or unknown productId (cart writes) -> FK 23503 ->
    // DataIntegrityViolationException. Safe: the race-backstop DuplicateKeyException (a DIVE
    // subtype) is caught in the controller FIRST.
    @ExceptionHandler(org.springframework.dao.DataIntegrityViolationException::class)
    fun notFoundFk(e: org.springframework.dao.DataIntegrityViolationException) =
        ResponseEntity.status(404).body(mapOf("error" to "not_found"))
    // Catch-all: any unmapped error is 500 {"error":"internal"}, matching the Go frame — never
    // Spring's whitelabel {timestamp,status,error,path}. The specific handlers above still win.
    @ExceptionHandler(Exception::class)
    fun internal(e: Exception) =
        ResponseEntity.status(500).body(mapOf("error" to "internal"))
}

GET /orders/{id}
200: { "id":4821, "customerId":1, "total":5998, "status":"pending",
       "createdAt":"2026-01-01T00:00:00Z",
       "items":[ { "productId":2, "quantity":2, "unitPrice":2999 } ] }
404: { "error":"not_found" }   // no such order, OR a non-numeric {id} (e.g. /orders/abc)