Forem: Ravindu Gajanayaka

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

Ravindu Gajanayaka — Sun, 08 Mar 2026 19:18:40 +0000

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)
})

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;

Step 2: Create a policy that filters rows automatically:

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

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

SET LOCAL app.tenant_id = 'abc-123-tenant-uuid';

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;

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

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

When you call withAuthTenant:

It checks authentication (returns 401 if not logged in)
Starts a database transaction
Sets LOCAL ROLE app_user
Sets LOCAL app.tenant_id from the session
Runs your query (RLS is now active)
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

The Next.js middleware handles routing:

Extract subdomain from the Host header
Look up the tenant by slug
Rewrite the URL to /c/[slug]/... internally
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:

API route creates/updates data → calls broadcastChange(tenantId, 'item', 'updated', itemId)
WebSocket server sends the event to all connected clients for that tenant
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:

See the live demo: retailsmarterp.com
Read the code: github.com/ravindu2012/retail-smart-erp
Contribute: Issues labeled good first issue are waiting for you

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:

POS Prime — A modern POS replacement for ERPNext (Vue 3, Python)
QuickBooks Desktop Clone — Desktop accounting app (.NET 8, WPF)

All open source, all looking for contributors.

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

I Can't Code, But I Built 3 Open-Source Business Apps — Here's How

Ravindu Gajanayaka — Sat, 07 Mar 2026 07:15:55 +0000

I Can't Code, But I Built 3 Open-Source Business Apps — Here's How

I don't know Python. I can't write JavaScript from scratch. I've never formally learned any programming language.

But in the last year, I've built and shipped three open-source business applications that are live, functional, and growing. One of them is a full multi-tenant ERP system with 65+ database tables, real-time WebSocket updates, and AI-powered features.

This is how I did it.

My Background

I'm not a developer. I'm a guy from Sri Lanka who understands business operations — how a retail shop runs, how an auto service center manages work orders, how accounting should flow. I've worked with business software my whole career and always thought: "I could build something better than this."

The problem was always the same: I had the ideas but not the coding skills.

Then AI changed everything.

The Approach: AI as My Development Partner

I use AI tools — primarily Claude — as my coding partner. But let me be clear about what that means:

I don't just say "build me an app" and wait.

Here's what I actually do:

I design the system. I decide the database schema, the user flows, the business logic. I know what needs to be built because I understand the domain deeply.
I architect the solution. I choose the tech stack, plan the modules, decide how things connect. Multi-tenant architecture with row-level security? That was my decision based on real business needs.
I direct every feature. I work feature by feature, reviewing every piece of code, testing it, and iterating. If something doesn't work the way a real business needs it to, I know immediately.
AI writes the code. I describe what I need, review what it produces, and guide it to fix issues. Think of it like being an architect who doesn't lay bricks but designs the entire building.
I debug through conversation. When things break — and they do — I describe the problem and work through solutions. Over time, I've developed an intuition for common issues even without reading the code line by line.

The key insight: domain knowledge is more valuable than syntax knowledge. A developer who doesn't understand accounting will build a terrible accounting system, no matter how clean their code is.

What I Built

1. Retail Smart ERP

A multi-tenant SaaS Point of Sale and ERP system.

Tech: Next.js 16, React 19, PostgreSQL, WebSocket, Tailwind CSS

This is the big one. A complete business management system that supports five different business types:

Retail shops
Restaurants (with kitchen display and floor plans)
Supermarkets
Auto service centers (with work orders and vehicle tracking)
Vehicle dealerships

Features that I'm particularly proud of:

Multi-tenant with subdomain routing — each business gets company.retailsmarterp.com
Row-level security — tenant data isolation at the database level, not just application level
Real-time updates — WebSocket-powered, so every terminal sees changes instantly
AI-powered insights — smart warnings and anomaly detection
65+ database tables with proper double-entry accounting

Live: retailsmarterp.com
GitHub: github.com/ravindu2012/retail-smart-erp

2. POS Prime

A modern Point of Sale replacement for ERPNext.

Tech: Vue 3, TypeScript, Python, Frappe Framework, Tailwind CSS

ERPNext is a popular open-source ERP, but its built-in POS is slow and clunky. I built POS Prime as a complete replacement that:

Works on touch screens and barcode scanners
Has a self-checkout kiosk mode
Supports customer pole displays
Handles split payments and returns
Zero modifications to ERPNext — installs cleanly, uninstalls cleanly

The "zero modifications" part was a deliberate design decision. Too many ERPNext apps break upgrades because they add custom fields everywhere. POS Prime works entirely with standard ERPNext doctypes.

GitHub: github.com/ravindu2012/pos-prime

3. QuickBooks Desktop Clone

A full-featured desktop accounting application.

Tech: .NET 8, WPF, C#, Clean Architecture

This one targets small businesses that need proper accounting software but can't afford QuickBooks. Features include:

Double-entry accounting engine with 11 transaction posting types
65+ seeded Chart of Accounts
Customer and vendor management with aging reports
Invoice, bill, and payment processing
Banking and reconciliation
Full financial reporting (P&L, Balance Sheet, Trial Balance)

GitHub: github.com/ravindu2012/QuickBooksDesktop

What I've Learned

1. You don't need to know code to build software

You need to know what the software should do. The actual typing of code is becoming less important every day. Understanding user needs, business logic, data relationships, and system architecture — that's what matters.

2. AI is a tool, not a replacement for thinking

AI can write code, but it can't decide what to build. It can't tell you that auto service centers need to track core returns separately from regular parts. It can't tell you that restaurant POS needs a different flow than retail POS. Domain expertise is irreplaceable.

3. Start with what you know

All three of my projects come from domains I understand deeply. I didn't try to build a social media app or a game. I built business tools because that's where my knowledge lives.

4. Ship early, iterate often

My first version of Retail Smart ERP was embarrassingly basic. But it worked. Each week I added features, fixed issues, and improved the experience. Shipping beats perfection.

5. Open source amplifies everything

Making my projects open source was the best decision I made. It forced me to write better documentation, think about contributor experience, and build things that work for more than just me.

The Uncomfortable Truth

Some developers might read this and think it's "cheating" or "not real development." I understand that reaction.

But consider this: the end user doesn't care if a human typed every semicolon or if AI helped. They care that the POS system processes their sales correctly, that the accounting balances, and that their business data is secure.

I'm not claiming to be a senior developer. I'm a product builder who uses the best tools available. Five years ago, that might have meant hiring a team. Today, it means partnering with AI.

The barrier to building software has dropped dramatically. If you have deep knowledge in any field — healthcare, education, logistics, finance, anything — you can now turn that knowledge into real software.

Want to Contribute?

All three projects are open source and actively looking for contributors:

Retail Smart ERP — Next.js, React, PostgreSQL
POS Prime — Vue 3, Python, ERPNext
QuickBooks Desktop Clone — .NET 8, WPF, C#

Each repo has good first issue labels, contributing guides, and active discussions. Whether you're a seasoned developer or someone like me who's learning as they go — you're welcome.

If this project helps you or your business, consider supporting development:

Have questions? Drop a comment below or find me on GitHub.