Supabase Backend

The Supabase adapter runs the entire commerce layer — catalog, carts, quotes, orders, customers, discounts, and admin checks — on Supabase Postgres instead of the default Firestore. It lives in src/lib/commerce/server/supabase.ts and implements the full CommerceServices interface (src/lib/commerce/types.ts), so it is a behavioral drop-in for the Firebase adapter: identical read shapes and defaulting rules, the same Q-YYYYMMDD-NNNNXXXX / O-YYYYMMDD-NNNNXXXX quote and order numbers, the same denormalized per-customer copies, and the same lifecycle events on the commerce event bus.

How it fits together

The Supabase adapter is server-side only. It connects with the Supabase service-role key, which bypasses Row Level Security, so it must be reached exclusively from Next.js API routes / server code — never imported into a client component.

Firebase Auth remains the identity provider even when the data lives in Postgres. The browser never touches the service-role key. Instead it calls a single RPC route, POST /api/commerce/[service]/[method] (src/app/api/commerce/[service]/[method]/route.ts), which:

1. verifies the caller's Firebase ID token,
2. enforces per-method access control (public / user-scoped / admin), then
3. runs supabaseCommerce with the service-role connection.

Because Firebase Auth stays the identity provider, the FIREBASE_SERVICE_ACCOUNT* credentials are still required — the server needs them to verify ID tokens. See environment variables for the full list.

Setup

SUPABASE_URL=https://<project-ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=<service_role key>

NEXT_PUBLIC_COMMERCE_BACKEND=supabase   # browser: route commerce calls to the RPC layer
COMMERCE_BACKEND=supabase               # server: run the Supabase adapter

insert into admins (uid) values ('<firebase-auth-uid>')
on conflict (uid) do nothing;

Required environment variables

The client is created lazily and cached, with persistSession: false and autoRefreshToken: false. If SUPABASE_URL or SUPABASE_SERVICE_ROLE_KEY is missing, the first operation throws:

Supabase commerce adapter: SUPABASE_URL and SUPABASE_SERVICE_ROLE_KEY must be
set. This adapter is server-side only — never expose the service-role key to
the client.

Schema reference

The DDL in supabase/schema.sql mirrors the Firestore layout the Firebase adapter uses. Schemaless documents (product bodies, cart items, quote/order items and customer info, addresses, applied discounts) live in jsonb columns so unknown fields round-trip exactly like Firestore documents. Queried scalars (numbers, statuses, timestamps, customer ids) are promoted to real columns.

Catalog tables

products is keyed by the caller-supplied human id (e.g. AUD-001); the full schemaless product body lives in the data jsonb column, including the adapter-maintained productNameLower search field. featured_products holds home-page entries — each row is a full copy of a product body with its own generated id and a productId back-reference, exactly like the Firestore home-products collection.

create extension if not exists pgcrypto;

create table if not exists products (
  id text primary key,
  data jsonb not null default '{}'::jsonb
);

-- Prefix search: WHERE data->>'productNameLower' LIKE 'term%'.
-- text_pattern_ops makes the LIKE prefix scan use the index regardless of collation.
create index if not exists products_name_lower_idx
  on products ((data ->> 'productNameLower') text_pattern_ops);

create index if not exists products_brand_idx
  on products ((data ->> 'brand'));
create index if not exists products_categories_idx
  on products using gin ((data -> 'categories'));

create table if not exists featured_products (
  id uuid primary key default gen_random_uuid(),
  data jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

-- One cart per user, keyed by the auth uid. `items` is the JSON array of
-- cart line snapshots (line identity = id or id::variantId). clear() DELETES
-- the row (a missing row reads as "no cart", distinct from an empty cart).
create table if not exists carts (
  user_id text primary key,
  items jsonb not null default '[]'::jsonb,
  updated_at timestamptz not null default now()
);

Quotes and orders

Quotes and orders each keep a main record plus a denormalized per-customer copy keyed by the human-readable number. quotes.order_number is NULL until the quote is converted; createFromQuote claims it atomically to reject double conversion. Orders carry a computed total (line subtotals minus the clamped discount, rounded to cents).

create table if not exists quotes (
  id uuid primary key default gen_random_uuid(),
  quote_number text not null,
  customer_id text,
  status text not null default 'Pending',
  message text not null default '',
  order_number text,
  created timestamptz not null default now(),
  items jsonb not null default '[]'::jsonb,
  customer jsonb not null default '{}'::jsonb,
  discount jsonb
);
create index if not exists quotes_created_desc_idx on quotes (created desc);
create index if not exists quotes_customer_idx on quotes (customer_id);

create table if not exists customer_quotes (
  customer_id text not null,
  quote_number text not null,
  main_quote_id uuid,
  status text not null default 'Pending',
  message text not null default '',
  order_number text,
  created timestamptz not null default now(),
  items jsonb not null default '[]'::jsonb,
  customer jsonb not null default '{}'::jsonb,
  discount jsonb,
  primary key (customer_id, quote_number)
);

create table if not exists orders (
  id uuid primary key default gen_random_uuid(),
  order_number text not null,
  quote_id uuid,
  quote_number text,
  customer_id text,
  customer jsonb not null default '{}'::jsonb,
  items jsonb not null default '[]'::jsonb,
  total numeric(12, 2) not null default 0,
  status text not null default 'Processing',
  created timestamptz not null default now(),
  discount jsonb
);
create index if not exists orders_created_desc_idx on orders (created desc);
create index if not exists orders_customer_idx on orders (customer_id);

create table if not exists customer_orders (
  customer_id text not null,
  order_number text not null,
  quote_id uuid,
  quote_number text,
  customer jsonb not null default '{}'::jsonb,
  items jsonb not null default '[]'::jsonb,
  total numeric(12, 2) not null default 0,
  status text not null default 'Processing',
  created timestamptz not null default now(),
  discount jsonb,
  primary key (customer_id, order_number)
);

Customers, discounts, and admins

customers is a rigid four-field profile keyed by the auth uid — the write path whitelists exactly these fields, matching the Firestore converter. customer_addresses is a schemaless address book with no FK to customers (Firestore subcollections can exist without a parent doc). discounts is keyed by the trimmed UPPERCASE code; expires_at NULL means never expires. admins uses row existence as the admin flag.

create table if not exists customers (
  id text primary key,
  first_name text not null default '',
  last_name text not null default '',
  phone text not null default '',
  email text not null default ''
);

create table if not exists customer_addresses (
  id uuid primary key default gen_random_uuid(),
  customer_id text not null,
  data jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);
create index if not exists customer_addresses_customer_idx
  on customer_addresses (customer_id);

create table if not exists discounts (
  code text primary key,
  type text not null default 'percent',
  value numeric not null default 0,
  active boolean not null default false,
  expires_at timestamptz,
  created timestamptz not null default now()
);
create index if not exists discounts_created_desc_idx on discounts (created desc);

create table if not exists admins (
  uid text primary key,
  created_at timestamptz not null default now()
);

Row Level Security

RLS is enabled with no policies on every table. The adapter uses the service-role key (which bypasses RLS), so enabling RLS with no policies denies all access to the anon/authenticated PostgREST roles — nothing is reachable except through the server-side adapter. This mirrors how firestore.rules gates the Firebase adapter.

alter table products enable row level security;
alter table featured_products enable row level security;
alter table carts enable row level security;
alter table quotes enable row level security;
alter table customer_quotes enable row level security;
alter table orders enable row level security;
alter table customer_orders enable row level security;
alter table customers enable row level security;
alter table customer_addresses enable row level security;
alter table discounts enable row level security;
alter table admins enable row level security;

Seeding the catalog

The bundled seed script (scripts/seed.mjs) targets Firestore. To seed the Supabase catalog instead, insert the same product bodies into products, remembering the two invariants the adapter normally maintains on write:

• The whole product body (including its id) goes in the data jsonb column, keyed by the same human id (AUD-001, ...).
• data.productNameLower must be present (lower(productName)) or prefix search will silently miss the row.

The simplest route is to call the adapter itself from a one-off server script — e.g. invoke supabaseCommerce.products.create(...) per seed product and products.feature(...) for home-page entries. create derives productNameLower for you, and featured entries become copies with a productId back-reference and their own generated id, exactly like the Firestore home-products collection.

Behavior notes and limitations

The adapter is a faithful behavioral drop-in, but a few Firestore-parity quirks are worth knowing:

• Cart updates poll instead of push. carts.listen() fetches the cart immediately, then re-fetches every 5 seconds; realtime push is Firestore-only for now. The callback contract is otherwise identical (fires immediately with the current state; undefined for a missing cart). Expect up to ~5s of latency on cross-session cart changes and slightly more read traffic per open listener.
• Pagination cursors are JSON-serializable tokens (base64url-encoded keyset cursors), so they survive HTTP round-trips between the API route and the browser. They remain opaque — hand back exactly what products.list returned. Firestore parity quirk preserved: a full final page still returns a non-null cursor; the caller detects exhaustion when the next page comes back empty.
• Non-atomic tails preserved. updateStatus and createFromQuote update the main record first, then the per-customer copy — a missing copy throws after the main write committed, matching the Firebase adapter. The only transactional piece is the double-conversion guard in createFromQuote (a conditional UPDATE ... WHERE order_number IS NULL).
• Stock decrement is best-effort, exactly like Firebase: failures are logged and never fail the already-created order; unknown product ids, untracked stock, and missing variants are skipped silently.
• Events fire server-side. Lifecycle events (quote.submitted, order.placed, ...) are emitted in the server runtime that executes the mutation, so subscribers may safely use secrets — but client-side subscribers will not see them.
• Ordering nuances. products.list orders by id using the database collation, which can differ from Firestore's code-point ordering for ids mixing cases or non-ASCII characters (typical AUD-001-style ids sort identically). listForCustomer orders by quote/order number ascending, mirroring the Firestore subcollection's doc-id ordering.

Related