DEV Community

Cover image for How I Designed a Multi-Tenant ERP System That Isolates 100% of Customer Data
Ravindu Gajanayaka
Ravindu Gajanayaka

Posted on

How I Designed a Multi-Tenant ERP System That Isolates 100% of Customer Data

#webdev #postgressql #nextjs #opensource

How I Designed a Multi-Tenant ERP System That Isolates 100% of Customer Data

When you build a SaaS application where multiple businesses share the same database, one question keeps you up at night: "What if Company A accidentally sees Company B's data?"

I built Retail Smart ERP — an open-source POS and ERP system serving retail shops, restaurants, supermarkets, auto service centers, and dealerships — all from one codebase, one database. Here's how I made sure every tenant's data stays completely isolated, even when a developer makes a mistake.

The Problem

Imagine this scenario:

A developer writes a new API route to fetch customer data. They forget to add the tenant filter. Now every business on the platform can see every other business's customers.

In a traditional multi-tenant app, this is a real risk. Every single database query needs a WHERE tenant_id = ? clause. Miss one, and you have a data leak.

I needed something better. Something that protects data even when the application code has bugs.

The Solution: Three Layers of Defense

Layer 1: Application-Level Tenant Filtering

Every API route in the system requires authentication, and every authenticated session includes a tenantId. All database queries filter by this tenant:

const items = await db.query.items.findMany({
  where: eq(items.tenantId, session.user.tenantId)
})
Enter fullscreen mode Exit fullscreen mode

This is the standard approach most SaaS apps use. But it's fragile — it depends on every developer remembering to add the filter every time.

Layer 2: PostgreSQL Row-Level Security (RLS)

This is where it gets interesting. PostgreSQL has a built-in feature called Row-Level Security that enforces data isolation at the database level.

Here's how it works:

Step 1: Enable RLS on every tenant-scoped table:

ALTER TABLE items ENABLE ROW LEVEL SECURITY;
ALTER TABLE items FORCE ROW LEVEL SECURITY;
Enter fullscreen mode Exit fullscreen mode

Step 2: Create a policy that filters rows automatically:

CREATE POLICY tenant_isolation ON items
  USING (tenant_id = current_setting('app.tenant_id')::uuid);
Enter fullscreen mode Exit fullscreen mode

Step 3: Before every query, set the tenant context:

SET LOCAL app.tenant_id = 'abc-123-tenant-uuid';
Enter fullscreen mode Exit fullscreen mode

Now, even if a developer writes SELECT * FROM items with no WHERE clause, PostgreSQL itself will only return rows belonging to the current tenant. The database becomes the safety net.

I applied this to all 65+ tables in the system. The migration file is over 200 lines of SQL, but it was worth every line.

Layer 3: Database Role Enforcement

There's a catch with RLS: the PostgreSQL superuser (postgres) bypasses all RLS policies by default. In production, if your app connects as postgres, RLS does nothing.

The fix: create a restricted database role:

CREATE ROLE app_user NOLOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES TO app_user;
Enter fullscreen mode Exit fullscreen mode

Then, inside every transaction, switch to this role:

SET LOCAL ROLE app_user;
SET LOCAL app.tenant_id = 'abc-123-tenant-uuid';
-- Now RLS is enforced, even though we connected as postgres
Enter fullscreen mode Exit fullscreen mode

The app_user role has no login capability — it's purely for RLS enforcement. The application still connects as postgres for migrations and admin operations, but tenant-scoped queries run under the restricted role.

The Implementation

In practice, I wrapped all of this into helper functions that developers use instead of raw database access:

// Recommended: Auth + RLS in one call
const result = await withAuthTenant(async (session, db) => {
  // RLS automatically filters by tenant
  return db.query.items.findMany()
})

// For background jobs with a known tenantId
await withTenant(tenantId, async (db) => {
  return db.query.items.findMany()
})

// For admin operations (bypasses RLS)
await withoutTenant(async (db) => {
  return db.query.tenants.findMany()
})
Enter fullscreen mode Exit fullscreen mode

When you call withAuthTenant:

  1. It checks authentication (returns 401 if not logged in)
  2. Starts a database transaction
  3. Sets LOCAL ROLE app_user
  4. Sets LOCAL app.tenant_id from the session
  5. Runs your query (RLS is now active)
  6. Commits the transaction

A developer using this helper literally cannot access another tenant's data. The database won't allow it.

Subdomain Routing: Each Business Gets Its Own URL

Multi-tenancy isn't just about the database. Each business on Retail Smart ERP gets its own subdomain:

gajanayaka-auto.retailsmarterp.com  →  Auto service center
marios-pizza.retailsmarterp.com     →  Restaurant
city-mart.retailsmarterp.com        →  Supermarket
Enter fullscreen mode Exit fullscreen mode

The Next.js middleware handles routing:

  1. Extract subdomain from the Host header
  2. Look up the tenant by slug
  3. Rewrite the URL to /c/[slug]/... internally
  4. The layout validates that the logged-in user belongs to this tenant

Reserved subdomains (www, app, api, admin) are blocked from tenant registration.

Five Business Types, One Codebase

The trickiest part wasn't the multi-tenancy — it was supporting five completely different business types:

Business Type Unique Modules
Retail POS, inventory, barcodes
Restaurant Kitchen display, floor plan, table reservations, recipes
Supermarket Bulk pricing, shelf labels, loyalty programs
Auto Service Work orders, vehicle tracking, insurance estimates
Dealership Vehicle inventory, test drives, dealership sales

Each tenant has a businessType field. The UI shows/hides modules based on this:

  • A restaurant sees the Kitchen and Tables menu items
  • An auto service center sees Work Orders and Appointments
  • A retail shop sees neither

But the core modules — POS, customers, inventory, accounting, HR — are shared across all business types. This means a bug fix in the POS benefits all five business types simultaneously.

Real-Time Updates Across Terminals

A POS system needs real-time data. If a cashier at Terminal 1 sells the last unit of an item, Terminal 2 needs to know immediately.

I built a custom WebSocket server integrated with Next.js:

  1. API route creates/updates data → calls broadcastChange(tenantId, 'item', 'updated', itemId)
  2. WebSocket server sends the event to all connected clients for that tenant
  3. Client hooks (useRealtimeData) automatically refresh the displayed data

The WebSocket connections are also tenant-scoped — a client only receives events for their own tenant. This is another layer of data isolation.

Lessons Learned

1. RLS is worth the complexity

Setting up Row-Level Security across 65+ tables was tedious. But the peace of mind is invaluable. I never worry about data leaks from a missing WHERE clause.

2. Helper functions prevent mistakes

By providing withAuthTenant() as the standard way to query data, I made the secure path also the easiest path. Developers don't need to think about tenant isolation — it just happens.

3. Start with RLS from day one

I added RLS after the app already had 50+ tables. Retrofitting was painful. If I started over, I'd enable RLS on every table from the first migration.

4. Test with multiple tenants in development

I keep at least two test tenants in my local database. This catches cross-tenant bugs that you'd never find with a single tenant.

5. The superuser bypass is a trap

If you don't set up role enforcement, RLS gives you a false sense of security. Always use a non-superuser role for application queries.

The Numbers

  • 65+ database tables with RLS enabled
  • 5 business types from one codebase
  • 15+ user roles with granular permissions
  • Real-time WebSocket updates across all terminals
  • Subdomain routing for tenant isolation at the URL level

Try It Yourself

Retail Smart ERP is fully open source. You can:

The RLS migration, tenant context helpers, and WebSocket implementation are all in the repo. Feel free to use the patterns in your own projects.

Other Projects

I'm also building:

All open source, all looking for contributors.

Sponsor on GitHub
Buy Me A Coffee

Questions about multi-tenant architecture? Drop a comment — happy to go deeper on any of these topics.

Top comments (1)

Collapse
 
aaronre16397861 profile image
Aaron Reese •

Could you not just set up copies of the tables in different schemas and restrict accounts to a schema?
Yes you would have to duplicate all of views and stored procedures but that is easy to script out. This would also then allow you to update particular tenants for A/B testing or phased rollout of new features that require a schema change