Wednesday, June 3, 2026

Supabase as the Single-Tenant Order Database for Digital Stores

Supabase as the Single-Tenant Order Database for Digital Stores

Introduction

Building a commerce backend that remains under full technical and legal control requires a single-tenant, open-source data layer. Supabase provides PostgreSQL with row-level security, a purpose-built realtime API, and a storage layer whose egress cost is low enough to price transparently. This article shows how to model orders, line items, refunds, and cancellations purely inside Supabase without outsourcing custody to Stripe. All endpoints run on Cloudflare Workers, the frontend on Cloudflare Pages or GitHub Pages, and incoming payments are validated with NOWPayments Webhooks. Deployment time is measured in minutes, not days.

Schema Design for Orders

Start by creating two tables:

```sql create table stores ( id uuid primary key default gen_random_uuid(), slug text unique not null, title text not null, stripe_connect_id text, created_at timestamptz default now() );

create table orders ( id uuid primary key default gen_random_uuid(), store_id uuid references stores(id) on delete cascade, total_quotation numeric(10,2) not null, currency text not null check (currency ~ '^[A-Z]{3}$'), status text not null check (status in ('pending','paid','fulfilled','refunded','cancelled')), payment_method text not null, -- nowpayments, manual, crypto, etc. external_payment_id text, customer_email text not null, customer_metadata jsonb, metadata jsonb, created_at timestamptz default now(), updated_at timestamptz default now() ); ```

Add an index on `(store_id, created_at desc)` so list queries remain sub-20 ms at 10 k rows. A partial index on `status = 'paid'` when fulfilling reduces scan cost further. Line items are stored in a separate table with a foreign key to orders, allowing per-item refunds without rebuilding the entire transaction.

Row-Level Security Policies

Enable RLS on both tables:

```sql alter table stores enable row_level_security;

create policy store_admin_policy on stores for all using (auth.uid() = owner_id);

alter table orders enable row_level_security;

create policy store_admin_policy on orders for all using (store_id = (select store_id from stores where owner_id = auth.uid())); ```

Any API route that needs store-scoped data simply calls the Supabase client with the signed-in users JWT; unauthenticated requests are rejected before they hit the query planner. This eliminates the need for application-level ownership checks.

Cloudflare Workers Endpoints

Workers are deployed under a single namespace (`/api/v1`). Routes are versioned and scoped:

``` POST /api/v1/stores/{store_id}/orders // create GET /api/v1/stores/{store_id}/orders // list POST /api/v1/orders/{id}/refund // initiate refund POST /api/v1/webhooks/nowpayments // NOWPayments async ```

Each Worker imports `@supabase/supabase-js` and connects to the same Supabase URL. Connection reuse is automatic inside the Workers runtime, so cold starts measure ~50 ms. Latency from U.S. East to Supabases Ohio region is 15–20 ms, which is indistinguishable from regional services.

For idempotency, POST bodies include an `Idempotency-Key` header and a `public.orders` trigger writes the key to an index. Duplicate keys return the existing order without mutation.

NOWPayments Integration

Incoming cryptocurrency payments are validated via NOWPayments IPN. The worker extracts `order_id` from the callback, fetches the order, recalculates the quote given prevailing exchange rates, and marks the order paid only if the net amount ≥ total_quotation. Exchange rate cache is refreshed every 60 seconds via NOWPayments REST; the worker keeps 24 hours of rates in memory to avoid repeated fetches.

Fee transparency: NOWPayments fee is 0.4% + a fixed miner fee; both are shown to the customer before payment and persisted in the order metadata. This matches Stripes published fee structure while keeping custody with the merchant.

Frontend Build

The storefront is a static site generated by Hugo or Next.js; it is deployed to Cloudflare Pages or GitHub Pages. The site fetches product SKUs from Supabase via the same `/api/v1/stores/{slug}/products` endpoint, which uses a materialized view to reduce joins. Checkout is a form POST that returns an order id, then redirects to a payment gateway URL constructed client-side. No third-party cart JavaScript is loaded on the storefront, keeping bundle size sub-100 kB and first-byte time <250 ms globally with Cloudflare CDN.

Cost and Observability

Supabase free tier allows 500 MB database, 2 GB egress, and 50 k monthly active auth users. Beyond that, Pro tier is $25/month prepaid and scales linearly. Workers cost $5 per million requests; the average store executes <5 k requests/day, so the bill is <$1. Cloudflare Pages and GitHub Pages are free. NOWPayments charges the 0.4 % fee only on settled volume; no monthly minimum.

Conclusion

Placing orders entirely inside Supabase removes the operational burden of managing Stripe webhooks, disputes, and PCI compliance while keeping the codebase under 1 k lines and deployable in a single repository. Cloudflare Workers provide millisecond latency, automatic retries,