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, a read-only cart preview, and a checkout that 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 products 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.
4. POST /checkout with a valid cart returns 200 {"orderId": N}, the order + items exist, and stock is decremented by exactly the purchased quantity.
5. POST /checkout for more than available stock returns 409 {"error":"out_of_stock"} and leaves stock unchanged (the whole transaction rolled back).
6. Re-sending the same POST /checkout with the same Idempotency-Key returns the original order, not a second one.
7. GET /orders/{id} returns the created order with its line items.
8. The concurrency test (20 parallel buyers, 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.

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 /checkout (+Idem-Key) ──┤
   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
                         products, customers, 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 preview, 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, 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.22+)
  docker-compose.yml          # postgres:16, POSTGRES_PASSWORD=dev, POSTGRES_DB=aurora
  db/
    migrations/
      0001_init.up.sql        # products, customers, orders, order_items + orders.idempotency_key + partial unique index
      0001_init.down.sql
      0002_seed.up.sql        # 3 products + 1 customer (the prerequisite FK row)
      0002_seed.down.sql
  internal/
    store/
      store.go                # type Store (wraps a pgxpool); Products; Order; Checkout
      errors.go               # var ErrOutOfStock; var ErrNotFound
    httpapi/
      router.go               # newRouter(store, logger) *http.ServeMux  — all routes
      products.go             # GET /products
      checkout.go             # POST /checkout (decode, 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: newRouter(&store.Store{Pool: pool}),      // 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 }

// Store has these methods (here, a concrete type over a pgxpool.Pool):
//   Products(ctx) ([]Product, error)
//   Order(ctx, id int64) (Order, error)                              // GET /orders/{id}
//   // Checkout is ONE transaction. idemKey may be ""; if set, a replay returns the original order.
//   Checkout(ctx, customerID int64, lines []Line, idemKey string) (int64, error)

Checkout returns ErrOutOfStock (→ 409) when any line can’t be satisfied; the handler maps it. A replay with a known idemKey returns the original orderID (no second order).

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             # products, customers, orders, order_items + orders.idempotency_key + partial unique index
      V2__seed.sql             # 3 products + 1 customer  (or a CommandLineRunner seeder)
  src/main/kotlin/.../
    AuroraApplication.kt       # @SpringBootApplication entrypoint (Spring composes the server + shutdown)
    web/
      ProductController.kt     # GET /products
      CheckoutController.kt    # POST /checkout (body + Idempotency-Key header → 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, lines, 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.

Parity note: the Spring CheckoutService.checkout(customerId, lines, idemKey) signature mirrors the Go Store.Checkout; the controller maps OutOfStockException→409 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 products (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        TEXT    NOT NULL,
  unit_price  BIGINT  NOT NULL CHECK (unit_price >= 0),   -- cents
  stock       INTEGER NOT NULL CHECK (stock >= 0)
);

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

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',
  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

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. The seed creates it and a small catalog:

-- 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;

INSERT INTO products (name, unit_price, stock) VALUES
  ('Aurora Mug', 1499, 50),
  ('Aurora Tee', 2999, 12),
  ('Aurora Sticker Pack', 499, 200);

The demo customer’s id (1 on a fresh DB) is the customerId the cart/checkout uses. 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.
• (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.

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

5.2 POST /cart/preview

A read-only total: it prices the lines against the current catalog and mutates nothing. A preview is allowed to be slightly stale — the checkout (§5.3) is where truth is fixed.

• Request body:

{ "lines": [ { "productId": 1, "quantity": 2 } ] }

• 200 → per-line and grand totals, all integer cents (lineTotal = unitPrice * quantity, total = sum(lineTotal)):

{ "total": 2998, "lines": [ { "productId": 1, "quantity": 2, "unitPrice": 1499, "lineTotal": 2998 } ] }

• 404 → a productId does not exist: { "error": "not_found" }.

5.3 POST /checkout

• Request headers: Idempotency-Key: <client-generated UUID> (optional but recommended; one key per logical attempt, reused across retries).
• Request body:

{ "customerId": 1, "lines": [ { "productId": 1, "quantity": 2 } ] }

• 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):

{ "error": "out_of_stock" }

• 422 → empty lines, quantity <= 0, or missing customerId:

{ "error": "invalid_request" }

• 404 → customerId or a productId does not exist: { "error": "not_found" }.

Idempotency semantics: if the Idempotency-Key matches an existing order, return that order’s id with 200 and make no DB change. The key is written inside the checkout transaction, so a crash can never leave a key without its order; a duplicate insert hits orders_idem_key and the handler returns the original.

5.4 GET /orders/{id}

• 200 → the order with its line items:

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

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

5.5 Error code table (shared)

5.6 Optional feature endpoints

• ai-recipe: POST /cart/from-recipe { "recipe": string } → { "items":[{"productId","name","quantity","confidence"}], "unmatched":[string] }. Returns a proposal the client confirms; never mutates the cart server-side.
• ai-support: POST /support { "question": string } → { "answer": string } (Gemini function calling over three read-only tools). Cancel/refund is not a callable tool; the agent returns { "action","orderId","summary","requiresConfirmation": true } and the real mutation lives behind the separate, idempotent, human-confirmed POST /orders/{id}/cancel below.
• 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)" }

  • 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" }.

  • Idempotency semantics: identical to checkout — 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.

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 — products, customers, orders, order_items. (common)
3. Seed the catalog AND a customer — the prerequisite FK row before any checkout. (common)
4. Scaffold the API + GET /products + a composed main — pool/store, router, trace-id middleware, graceful shutdown. (backend: go / spring)
5. Model the cart (read-only preview total) — observable preview, no mutation. (common; small endpoint or pure function the cart screen uses)
6. Watch oversell become impossible — by hand — two psql sessions race the conditional UPDATE on the last unit. (common)
7. ★ Checkout as one transaction — conditional UPDATE … RETURNING unit_price, insert order + items, persist the idempotency key in-tx. (backend: go / spring)
8. Build POST /checkout — decode body, read Idempotency-Key, call Checkout, map 200/409/422/404; 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 — replace hand-run SQL with ordered files. (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 checkout button — POST the cart with 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 + DB_USER/DB_NAME/Secret-Manager DB_PASSWORD (authenticated). (common, optional) 16+. Feature modules (off by default): ai-recipe, ai-support, observability. (feature steps)

Each backend-specific milestone (4, 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.

9. Free-to-complete ($0)

• Database: local postgres:16 via Docker Compose. docker compose down -v resets.
• Backends: Go toolchain (free) or JDK + Gradle (free). No paid services to reach “done”.
• AI features: a free Google AI Studio API key (free tier) — server-side only, read from the canonical env var GOOGLE_API_KEY (what the google.golang.org/genai and com.google.genai SDKs read). Don’t pin a volatile model id; use a current gemini-2.5-flash-class model and link the official model list.
• Deploy (optional): Cloud Run scales to zero (an idle service costs nothing); Cloud SQL db-f1-micro is the smallest tier — the only step that can incur cost, and it is clearly optional. Everything to reach the definition of done is local and free.

Local credential reconciliation (so a learner who edits it isn’t stuck): the Compose file sets POSTGRES_PASSWORD=dev for the default postgres superuser, and DATABASE_URL uses that same postgres:dev pair — they must match. In Cloud SQL the DSN instead carries DB_USER + a Secret-Manager password over the connector socket (never an unauthenticated DSN).