Blueprint: an online store

-- schema.sql : the four core tables of the store
CREATE TABLE customers (
  id    INTEGER PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  name  TEXT
);

CREATE TABLE products (
  id          INTEGER PRIMARY KEY,
  title       TEXT NOT NULL,
  price_cents INTEGER NOT NULL,
  stock       INTEGER NOT NULL DEFAULT 0,
  image_key   TEXT                      -- object key inside the R2 bucket
);

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  email       TEXT,
  status      TEXT NOT NULL DEFAULT 'pending',  -- pending | paid | failed
  total_cents INTEGER NOT NULL,
  created_at  TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
  id               INTEGER PRIMARY KEY,
  order_id         INTEGER NOT NULL REFERENCES orders(id),
  product_id       INTEGER NOT NULL REFERENCES products(id),
  qty              INTEGER NOT NULL,
  unit_price_cents INTEGER NOT NULL
);

CREATE INDEX idx_items_order ON order_items(order_id);

// public/store.js -- runs in the browser on the Pages storefront
const API = "https://store-api.example.workers.dev";

// A stable id for this browser's cart (used to find its Durable Object)
const cartId = localStorage.getItem("cartId") || crypto.randomUUID();
localStorage.setItem("cartId", cartId);

// 1) Browse products (images load straight from R2)
async function loadProducts() {
  const res = await fetch(`${API}/api/products`);
  const products = await res.json();
  for (const p of products) {
    // <img src="https://images.example.com/<image_key>"> served by R2
    renderCard(p);
  }
}

// 2) Add to cart
async function addToCart(productId) {
  await fetch(`${API}/api/cart/add`, {
    method: "POST",
    headers: { "content-type": "application/json" },
    body: JSON.stringify({ cartId, productId, qty: 1 }),
  });
}

// 3) Checkout -- token comes from the Turnstile widget on the page
async function checkout(email) {
  const token = window.turnstile.getResponse();   // proves you are human
  const res = await fetch(`${API}/api/checkout`, {
    method: "POST",
    headers: { "content-type": "application/json" },
    body: JSON.stringify({ cartId, token, email }),
  });
  const { orderId, status } = await res.json();
  alert(`Order ${orderId}: ${status}`);
}

// src/index.js -- the store Worker API (router)
export default {
  async fetch(request, env) {
    const url = new URL(request.url);

    // Browse catalog: serve from the KV cache, fall back to D1
    if (url.pathname === "/api/products") {
      let catalog = await env.CATALOG.get("catalog", "json");
      if (!catalog) {
        const { results } = await env.DB
          .prepare("SELECT id, title, price_cents, stock, image_key FROM products")
          .all();
        catalog = results;
        await env.CATALOG.put("catalog", JSON.stringify(results), { expirationTtl: 60 });
      }
      return Response.json(catalog);
    }

    // Add to cart: route to THIS shopper's Cart Durable Object
    if (url.pathname === "/api/cart/add" && request.method === "POST") {
      const { cartId, productId, qty } = await request.json();
      const stub = env.CART.get(env.CART.idFromName(cartId));
      return stub.fetch("https://do/add", {
        method: "POST",
        body: JSON.stringify({ productId, qty }),
      });
    }

    // Checkout
    if (url.pathname === "/api/checkout" && request.method === "POST") {
      const { cartId, token, email } = await request.json();

      // 1) Turnstile: prove the buyer is a human, not a bot
      if (!(await verifyTurnstile(token, env.TURNSTILE_SECRET)))
        return new Response("Failed challenge", { status: 403 });

      // 2) Reserve stock atomically inside the Cart Durable Object
      const cart = env.CART.get(env.CART.idFromName(cartId));
      const reserved = await (await cart.fetch("https://do/reserve", { method: "POST" })).json();
      if (!reserved.ok) return new Response("Out of stock", { status: 409 });

      // 3) Create a pending order in D1
      const order = await env.DB
        .prepare("INSERT INTO orders (email, status, total_cents) VALUES (?, 'pending', ?) RETURNING id")
        .bind(email, reserved.total_cents)
        .first();

      // 4) Charge the external payment provider
      const charge = await chargePayment(env.PAYMENT_KEY, order.id, reserved.total_cents);

      // 5) Confirm, or roll the stock back on failure
      const status = charge.paid ? "paid" : "failed";
      await env.DB.prepare("UPDATE orders SET status = ? WHERE id = ?")
        .bind(status, order.id).run();
      if (!charge.paid) await cart.fetch("https://do/release", { method: "POST" });

      return Response.json({ orderId: order.id, status });
    }

    return new Response("Not found", { status: 404 });
  },
};

async function verifyTurnstile(token, secret) {
  const r = await fetch("https://challenges.cloudflare.com/turnstile/v0/siteverify", {
    method: "POST",
    headers: { "content-type": "application/json" },
    body: JSON.stringify({ secret, response: token }),
  });
  return (await r.json()).success === true;
}

async function chargePayment(apiKey, orderId, amountCents) {
  // Swap this for your provider's SDK / REST call
  const r = await fetch("https://api.payments.example/v1/charges", {
    method: "POST",
    headers: { authorization: "Bearer " + apiKey, "content-type": "application/json" },
    body: JSON.stringify({ amount: amountCents, currency: "twd", reference: orderId }),
  });
  return r.json();
}

// src/cart.js -- one Durable Object instance per shopper cart.
// A single DO is single-threaded, so stock checks never race each other.
export class Cart {
  constructor(state, env) {
    this.state = state;   // durable, consistent storage for THIS cart
    this.env = env;
  }

  async fetch(request) {
    const url = new URL(request.url);
    let items = (await this.state.storage.get("items")) || [];

    if (url.pathname === "/add") {
      const { productId, qty } = await request.json();
      items = [...items, { productId, qty }];           // immutable update
      await this.state.storage.put("items", items);
      return Response.json({ items });
    }

    if (url.pathname === "/reserve") {
      // Decrement stock for every line, all-or-nothing.
      // 'WHERE stock >= ?' makes each decrement safe under load.
      let total = 0;
      for (const it of items) {
        const row = await this.env.DB
          .prepare("UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ? RETURNING price_cents")
          .bind(it.qty, it.productId, it.qty)
          .first();
        if (!row) return Response.json({ ok: false });  // sold out -> abort
        total += row.price_cents * it.qty;
      }
      return Response.json({ ok: true, total_cents: total });
    }

    if (url.pathname === "/release") {
      // Payment failed: give the reserved stock back.
      for (const it of items) {
        await this.env.DB
          .prepare("UPDATE products SET stock = stock + ? WHERE id = ?")
          .bind(it.qty, it.productId).run();
      }
      await this.state.storage.delete("items");
      return Response.json({ ok: true });
    }

    return new Response("DO route not found", { status: 404 });
  }
}