Zero-Lag Multi-Tenant Architecture: Lessons from Building Sobatoko for Thousands of Merchants

The Multi-Tenant SaaS Dilemma

When designing a B2B SaaS platform for retail and small businesses like Sobatoko, you face a core architectural dilemma:

Database-per-tenant: Maximum data isolation, but catastrophic cost and migration complexity when scaling to thousands of low-margin merchants.
Shared database with tenant ID columns: Extremely cost-efficient, but prone to catastrophic data leaks if a single developer forgets a WHERE tenant_id = ? clause.

Furthermore, merchants demand that online store storefronts load in under 100 milliseconds for their customers, while the backoffice admin updates stock instantly across multiple branches.

Here is how we architected Sobatoko’s multi-tenant engine using Next.js App Router, PostgreSQL Row-Level Security (RLS), and Edge Caching.

1. PostgreSQL Row-Level Security (RLS) as the Safety Net

Instead of trusting application-level ORM filters on every database query, we delegate isolation directly to the database engine via PostgreSQL RLS.

-- 1. Enable RLS on core tables
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- 2. Define Tenant Context Extraction Policy
CREATE POLICY tenant_isolation_policy ON products
  FOR ALL
  USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid);

In our server-side database connection layer, every request extracts the tenant identifier from the authenticated session or custom domain and sets the transaction-local variable:

// db/tenant-context.ts
import { sql } from '@/lib/db';

export async function withTenantContext<T>(
  tenantId: string,
  callback: () => Promise<T>
): Promise<T> {
  return await sql.begin(async (tx) => {
    // Set local configuration for this specific transaction
    await tx`SELECT set_config('app.current_tenant_id', ${tenantId}, true)`;
    return await callback();
  });
}

Even if an engineer writes SELECT * FROM products without a WHERE clause, PostgreSQL automatically filters out all data belonging to other tenants. Zero data leakage, zero manual boilerplate.

2. Dynamic Custom Domains on Next.js Edge

Sobatoko merchants get their own white-labeled storefront (e.g. store.merchantbrand.com or merchant.sobatoko.com).

Using Next.js Middleware with edge routing:

// middleware.ts
import { NextResponse } from 'next/server';
import type { NextRequest } from 'next/request';

export async function middleware(req: NextRequest) {
  const hostname = req.headers.get('host') || '';
  
  // Strip default platform domain to identify custom subdomains
  const currentHost = hostname.replace(`.${process.env.NEXT_PUBLIC_ROOT_DOMAIN}`, '');

  // Rewrite internal request to dynamic tenant route
  const url = req.nextUrl;
  url.pathname = `/storefront/${currentHost}${url.pathname}`;
  
  return NextResponse.rewrite(url);
}

Storefront pages are rendered using Next.js Incremental Static Regeneration (ISR) with tag-based on-demand revalidation:

Lighthouse Score: 98+
First Contentful Paint (FCP): < 400ms globally
Server Load: Cached entirely on edge CDN points of presence until a merchant updates an item in the catalog.

3. Real-Time Stock Decrements Without Race Conditions

When multiple cashiers or online shoppers purchase the final unit of stock simultaneously, traditional read -> decrement -> save workflows result in negative inventory.

We solve this using atomic conditional database decrements:

-- Atomic Stock Reduction Query
UPDATE products
SET stock = stock - 1,
    updated_at = NOW()
WHERE id = $productId
  AND tenant_id = $tenantId
  AND stock >= 1
RETURNING id, stock;

If the updated row count is 0, the application immediately throws an OutOfStockException, preventing over-selling without locking entire tables.

4. Key Takeaways for Building SaaS That Scales

Enforce isolation at the database layer (RLS): Do not rely solely on developer discipline in backend application code.
Leverage Edge Middleware for custom domains: Avoid spinning up separate server instances for every customer storefront.
Keep cloud bills predictable: By utilizing pooled serverless PostgreSQL and edge caching, we can serve thousands of store catalogs on predictable infrastructure costs.

Looking to Build a Scalable Web SaaS or Platform?

At renodotdev, we build web architectures designed for speed, security, and low operational friction.

Direct WhatsApp consultation: Chat with Reno (+62 812 1299 6550)
Direct Email: renodotdev@gmail.com