Commerce

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

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

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:

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

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;

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

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:

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

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:

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

Shop anonymously

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

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

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:

app/auth/callback/route.ts

await orders.mergeCarts(anonymousUserId, user.id).orThrow();

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:

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

Ship

Staff create and ship a fulfillment:

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

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:

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

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:

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

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:

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

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.

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

Add better_supabase to schemas in the config so gen types the module tables, then define the order grid as a list query and pass it to saved views, which checks each view against it before saving:

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

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`

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

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.

Tables

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

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;

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" } }] },
          },
        },
      },
    },
  },
});

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:

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

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

Shop anonymously

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

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

app/auth/callback/route.ts

await orders.mergeCarts(anonymousUserId, user.id).orThrow();

Check out

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

Ship

Staff create and ship a fulfillment:

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

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:

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

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:

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

Metrics

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

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

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

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`

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

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.

Tables

Config

The blocks

Importing the catalog

From cart to shipment

Shop anonymously

Check out

Ship

Refund

Approve a large discount

Metrics

Admin search

Saved views of the order grid

Event flow

With ReUI

On this page

Commerce

Tables

Config

The blocks

Importing the catalog

From cart to shipment

Shop anonymously

Check out

Ship

Refund

Approve a large discount

Metrics

Admin search

Saved views of the order grid

Event flow

With ReUI

On this page

Commerce

TablesCopy Anchor Link

ConfigCopy Anchor Link

The blocksCopy Anchor Link

Importing the catalogCopy Anchor Link

From cart to shipmentCopy Anchor Link

Shop anonymouslyCopy Anchor Link

Merge on sign-inCopy Anchor Link

Check outCopy Anchor Link

ShipCopy Anchor Link

RefundCopy Anchor Link

Approve a large discountCopy Anchor Link

MetricsCopy Anchor Link

Admin searchCopy Anchor Link

Saved views of the order gridCopy Anchor Link

Event flowCopy Anchor Link

With ReUICopy Anchor Link

On this page

Commerce

TablesCopy Anchor Link

ConfigCopy Anchor Link

The blocksCopy Anchor Link

Importing the catalogCopy Anchor Link

From cart to shipmentCopy Anchor Link

Shop anonymouslyCopy Anchor Link

Merge on sign-inCopy Anchor Link

Check outCopy Anchor Link

ShipCopy Anchor Link

RefundCopy Anchor Link

Approve a large discountCopy Anchor Link

MetricsCopy Anchor Link

Admin searchCopy Anchor Link

Saved views of the order gridCopy Anchor Link

Event flowCopy Anchor Link

With ReUICopy Anchor Link

On this page

Tables

Config

The blocks

Importing the catalog

From cart to shipment

Shop anonymously

Merge on sign-in

Check out

Ship

Refund

Approve a large discount

Metrics

Admin search

Saved views of the order grid

Event flow

With ReUI

Tables

Config

The blocks

Importing the catalog

From cart to shipment

Shop anonymously

Merge on sign-in

Check out

Ship

Refund

Approve a large discount

Metrics

Admin search

Saved views of the order grid

Event flow

With ReUI