# Commerce

> A store with a catalog imported from CSV, stock, carts on anonymous sign-ins, promotion codes, checkout, invoices and shipping.

Source: https://bettersupabase.com/docs/presets/commerce

A visitor fills a cart before signing in, applies a code and checks out
once they sign in. The catalog prices the cart, promotions take their
discount, inventory reserves the stock, and the order gets a gapless order
number and, once paid, an invoice number. The people watching the order
hear when it is paid and when it ships. Revenue, average order value and
units sold chart from paid orders, and a large refund or a large discount
waits for an admin to approve it. Staff search the products from the admin
and keep saved views of the order grid.

## Tables [#tables]

The catalog, stock, carts, orders and promotions are module tables, so the
app adds none of its own. Orders are subjects of type `order`: their events
carry `subjectType: "order"` and the order id, so `watcherHandler` and
`notifications.subscribe` work on them like on any app row. Registering
`order` and `promotion` in `subjects` let approvals file requests on orders
and promotions, and the `search` entry on `product` indexes the catalog.

Three pieces of app SQL read the module tables, so they go in a schema file
that runs after the modules. Order lines carry no time, so units sold reads
them through a view that joins each line to its order's `paid_at`. The
`after_refund_create` hook runs in the transaction that records a refund
and refuses one above 50.00 unless the order's latest `refund` request was
approved. `before_promotion_activate` runs whenever a promotion ends up
active and refuses a percentage above 30 unless the promotion's latest
`activate` request was approved:

```sql title="supabase/schemas/commerce.sql"
create schema store;
grant usage on schema store to authenticated, service_role;
create view store.units_sold with (security_invoker = true) as
  select l.tenant_id, o.paid_at, l.quantity
  from better_supabase.order_lines l
  join better_supabase.orders o on o.id = l.order_id;
grant select on store.units_sold to authenticated, service_role;

create function public.after_refund_create(refund uuid)
returns void
language plpgsql
set search_path = ''
as $$
declare
  v_refund record;
begin
  select r.order_id, r.amount into v_refund
  from better_supabase.order_refunds r where r.id = refund;
  if v_refund.amount > 5000
    and not better_supabase.is_approved('order', v_refund.order_id::text, 'refund') then
    raise exception 'a refund above 50.00 needs an approved refund request'
      using errcode = '42501', hint = 'REFUND_APPROVAL_REQUIRED';
  end if;
end;
$$;
revoke execute on function public.after_refund_create(uuid) from public, anon, authenticated;

create function public.before_promotion_activate(promotion uuid)
returns void
language plpgsql
set search_path = ''
as $$
begin
  if exists (
    select 1 from better_supabase.promotions p
    where p.id = promotion and p.kind = 'percentage' and p.percent > 30
  ) and not better_supabase.is_approved('promotion', promotion::text, 'activate') then
    raise exception 'a discount above 30 percent needs an approved activate request'
      using errcode = '42501', hint = 'PROMOTION_APPROVAL_REQUIRED';
  end if;
end;
$$;
revoke execute on function public.before_promotion_activate(uuid) from public, anon, authenticated;
```

## Config [#config]

```ts title="src/metrics.ts"
import { defineMetric } from "better-supabase/blocks/metrics";
import { orderMetrics } from "better-supabase/blocks/orders";

export const metrics = [
  ...orderMetrics({ currency: "EUR" }),
  defineMetric({
    key: "units_sold",
    label: "Units sold",
    table: "store.units_sold",
    tenant: "tenant_id",
    measure: { sum: "quantity" },
    timeColumn: "paid_at",
  }),
] as const;
```

```ts title="better-supabase.config.ts"
import { defineConfig } from "better-supabase/config";

import { metrics } from "./src/metrics";

export default defineConfig({
  preset: "commerce",
  subjects: {
    order: {
      table: "better_supabase.orders",
      idType: "uuid",
      tenant: "tenant_id",
      title: "number",
    },
    promotion: {
      table: "better_supabase.promotions",
      idType: "uuid",
      tenant: "tenant_id",
      title: "name",
    },
    product: {
      table: "better_supabase.products",
      idType: "uuid",
      tenant: "tenant_id",
      title: "title",
      url: "/admin/products/{id}",
      search: { body: ["description"] },
    },
  },
  blocks: {
    imports: {},
    "number-sequences": {
      options: {
        entities: {
          order: { period: "none", format: "SO-{n:4}" },
          invoice: { period: "year", format: "INV-{yyyy}-{n:5}" },
        },
      },
    },
    search: {},
    "saved-views": {},
    metrics: { options: { metrics } },
    approvals: {
      options: {
        policies: {
          order: { refund: { steps: [{ approvers: { role: "admin" } }] } },
          promotion: {
            activate: { steps: [{ approvers: { role: "admin" } }] },
          },
        },
      },
    },
  },
});
```

An unpaid order has no `paid_at`, so it falls outside every bucket. The
`commerce` preset brings the SaaS blocks plus `catalog`, `inventory`,
`orders` and `promotions`, so orders reserve stock. The `order` and
`invoice` entities give the order and invoice numbers.

## The blocks [#the-blocks]

`orders` takes the payment and tax providers. The service transport merges
carts and records payment events; everything else runs as the caller, so
the policies apply:

```ts title="lib/store.ts"
import { fixedRates, stripePayments } from "better-supabase/blocks/commerce";
import { createOrders } from "better-supabase/blocks/orders";
import { createPromotions } from "better-supabase/blocks/promotions";

export const orders = createOrders({
  transport,
  service,
  payments: stripePayments({
    stripe: { secretKey: env.STRIPE_SECRET_KEY },
    webhookSecret: env.STRIPE_WEBHOOK_SECRET,
  }),
  tax: fixedRates({ rates: { NL: { standard: "0.21" } } }),
});
export const promotions = createPromotions({ transport });
```

## Importing the catalog [#importing-the-catalog]

An import reads the product CSV and writes each batch through the catalog
block, so a product gets the same checks as one made in the admin. The row
key is the SKU, so a rerun skips what already imported:

```ts title="lib/imports/products.ts"
import { defineImport } from "better-supabase/blocks/imports";
import { money } from "better-supabase/blocks/commerce";

export const products = defineImport({
  kind: "products",
  columns: productRow,
  key: (row) => row.sku,
  async insert(batch, { organizationId }) {
    for (const row of batch) {
      const product = await catalog
        .createProduct(organizationId, { title: row.title })
        .orThrow();
      await catalog.setProductStatus(product.id, "active").orThrow();
      const variant = await catalog
        .createVariant(product.id, { sku: row.sku })
        .orThrow();
      await catalog
        .setPrice(variant.id, { amount: money(BigInt(row.price), "EUR") })
        .orThrow();
    }
  },
});
```

## From cart to shipment [#from-cart-to-shipment]

### Shop anonymously [#shop-anonymously]

The storefront signs the visitor in with `supabase.auth.signInAnonymously()`,
then keeps their cart:

```ts
const cart = await orders
  .createCart(organizationId, { currency: "EUR" })
  .orThrow();
await orders.addToCart(cart.id, variantId, 2).orThrow();
await promotions.applyCode(cart.id, "WELCOME10").orThrow();
```

### Merge on sign-in [#merge-on-sign-in]

When the visitor signs in to an account, the auth callback route moves the
anonymous user's cart to the account. A cart the account already had in the
same currency takes the lines, adding up quantities, and the promotion
codes:

```ts title="app/auth/callback/route.ts"
await orders.mergeCarts(anonymousUserId, user.id).orThrow();
```

### Check out [#check-out]

`priceCart` shows the discount and any problem that blocks checkout, such
as a code that no longer applies. `checkout` places the order, reserves the
stock, redeems the promotions and creates the payment with an idempotency
key, so a retry returns the same order and payment. Once the payment
succeeds, the reservations turn into `sale` movements and the order gets
its invoice number:

```ts
const { order, payment } = await orders.checkout(cart.id).orThrow();
```

### Ship [#ship]

Staff create and ship a fulfillment:

```ts
const fulfillment = await orders.createFulfillment(order.id).orThrow();
await orders.shipFulfillment(fulfillment.id, { carrier: "PostNL" }).orThrow();
```

### Refund [#refund]

A refund up to 50.00 goes through at once. A larger one fails with
`REFUND_APPROVAL_REQUIRED` until an admin approves a `refund` request on the
order:

```ts
const refund = await orders.refund(order.id);
if (!refund.ok && refund.error.hint === "REFUND_APPROVAL_REQUIRED") {
  await approvals
    .request({
      organizationId,
      subjectType: "order",
      subjectId: order.id,
      action: "refund",
      reason,
    })
    .orThrow();
}
```

Once the request is approved, the same `orders.refund(order.id)` call
succeeds.

### Approve a large discount [#approve-a-large-discount]

A promotion above 30 percent can't go live on its own. Save it inactive,
file an `activate` request, and turn it on once an admin approves it:

```ts
const draft = await promotions
  .savePromotion(organizationId, {
    name: "Clearance",
    discount: { kind: "percentage", percent: "50" },
    active: false,
  })
  .orThrow();
await approvals
  .request({
    organizationId,
    subjectType: "promotion",
    subjectId: draft.id,
    action: "activate",
    reason,
  })
  .orThrow();
// After approvals.approve(requestId):
await promotions.setPromotionActive(draft.id, true).orThrow();
```

Saving it active, or activating it before the approval, fails with
`PROMOTION_APPROVAL_REQUIRED`. The hook also runs on every save that leaves a
promotion live, so an edit can't take an unapproved promotion above 30
percent.

## Metrics [#metrics]

Relay `order.placed` and `order.paid` to the metrics sink so the charts
include an order as soon as it is placed and paid, and run `metrics.job` on a schedule for late changes.
The metrics block's `sink` option builds the sink:

```ts title="app/api/outbox/route.ts"
const app = createBlocks(
  { transport: service },
  {
    metrics: metricsBlock
      .with({
        sink: {
          on: {
            ...ORDER_METRIC_EVENTS,
            "order.paid": [...ORDER_METRIC_EVENTS["order.paid"], "units_sold"],
          },
        },
      })
      .using((options) => createMetrics({ ...options, metrics })),
  },
);

export const GET = outbox.relayRoute({
  secret: process.env.CRON_SECRET,
  consumers: { ...app.sinks },
});
```

Stat cards read `kpi("revenue", range, { organizationId })` and charts read
`series`, as the caller.

## Admin search [#admin-search]

[Search](/docs/blocks/search) indexes each product's title and description
as it is saved. `search(q)` runs as the caller and only returns rows of the
caller's organizations, so it belongs in the admin: a shopper who isn't a
member gets no hits. The storefront lists products through the catalog.

```ts
const search = createSearch({ transport, service });

const hits = await search
  .search("linnen", { types: ["product"], limit: 10 })
  .orThrow();
// [{ subjectType: "product", subjectId, title: "Linen tee", url: "/admin/products/..." }]
```

The query matches words in the description and typos in the title, and
`useCommandSearch` puts it behind Cmd+K.

## Saved views of the order grid [#saved-views-of-the-order-grid]

Add `better_supabase` to `schemas` in the config so `gen` types the module
tables, then define the order grid as a [list query](/docs/platform/list)
and pass it to [saved views](/docs/blocks/saved-views), which checks each
view against it before saving:

```ts title="lib/orders-list.ts"
import { createSavedViews } from "better-supabase/blocks/saved-views";
import { defineListQuery } from "better-supabase/list";

export const orderList = defineListQuery(
  betterSupabase,
  "betterSupabaseOrders",
  {
    search: ["number", "email"],
    facets: { status: "status" },
    sorts: {
      newest: [{ placedAt: "desc" }, { id: "asc" }],
      oldest: [{ placedAt: "asc" }, { id: "asc" }],
    },
    defaultSort: "newest",
  },
);

export const savedViews = createSavedViews({
  transport,
  lists: { orders: orderList },
});

await savedViews
  .save({
    organizationId,
    list: "orders",
    name: "To ship",
    scope: "tenant",
    state: { query: { facets: { status: ["paid"] }, sort: "oldest" } },
  })
  .orThrow();
```

A view with a sort or facet the list doesn't define returns a `validation`
error and isn't saved.

## Event flow [#event-flow]

| What happens         | Call                            | Event                 | Watchers get    |
| -------------------- | ------------------------------- | --------------------- | --------------- |
| The order is paid    | `orders.checkout(cartId)`       | `order.paid`          | `order.paid`    |
| A code is redeemed   | `orders.checkout(cartId)`       | `promotion.redeemed`  |                 |
| The order ships      | `orders.shipFulfillment(id)`    | `fulfillment.shipped` | `order.shipped` |
| The catalog imported | `runImport(products, csv, ...)` | `import.completed`    |                 |
| A refund is approved | `approvals.approve(requestId)`  | `approval.approved`   |                 |

```ts title="app/api/cron/watchers/route.ts"
const watchers = watcherHandler(notifications, {
  types: {
    "order.paid": "order.paid",
    "fulfillment.shipped": "order.shipped",
  },
});
```

`promotion.redeemed` has no subject, so it goes to a consumer of its own,
such as a marketing sink, instead of to watchers.

## With ReUI [#with-reui]

* Product grid and product detail for the storefront, from the catalog's
  products, variants and prices.
* Shopping cart and coupon for the cart, from `priceCart` and
  `promotions.applyCode`.
* `checkout-2` for checkout, with the payment's `clientSecret`.
* Receipt for the order page, with the order and invoice numbers.
* The inventory blocks for stock levels, from `inventory`.
* Stat cards and charts for revenue, average order value and units sold,
  from `metrics.kpi` and `metrics.series`.
* A command menu for product search, from `useCommandSearch`, and a data
  grid with a views menu for orders, from `orderList` and `useSavedViews`.

Pro ecommerce blocks are licensed per user; copy them from
[reui.io](https://reui.io).