SaaS Multi-Tenancy Architecture in 2026

┌────────────────────────────────────────────────────────┐
│     Model A: Database-Per-Tenant (High Infrastructure Overhead)       │
│  Tenant 1 ──► [RDS Instance #1] (₹6,500/mo)            │
│  Tenant 2 ──► [RDS Instance #2] (₹6,500/mo)            │
│  ...                                                   │
│  Tenant 50 ──► [RDS Instance #50] (₹6,500/mo)          │
│  TOTAL INFRASTRUCTURE BURN: ₹3,25,000 / month!         │
└────────────────────────────────────────────────────────┘

┌────────────────────────────────────────────────────────┐
│   Model B: KaamLabs Shared PostgreSQL + RLS Engine     │
│  All 50 Tenants ──► [Single High-Performance Cluster]  │
│  Tenant Isolation enforced by PostgreSQL Engine Kernel │
│  TOTAL INFRASTRUCTURE BURN: ₹12,000 / month (96% Cut!) │
└────────────────────────────────────────────────────────┘

[API Query: "SELECT * FROM orders;"]
                 │
                 ▼
┌────────────────────────────────────────────────────────┐
│             PostgreSQL Query Planner                   │
│  RLS Policy automatically rewrites query to:          │
│  "SELECT * FROM orders WHERE tenant_id = auth.uid()"   │
└────────────────┬───────────────────────────────────────┘
                 │
                 ▼
[Returns ONLY current tenant's data; 100% leak-proof!]

-- 1. Create Tenants/Organizations Table
CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  gstin TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 2. Create Memberships Mapping Users to Organizations
CREATE TABLE organization_members (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
  user_id UUID NOT NULL, -- references auth.users
  role TEXT CHECK (role IN ('owner', 'admin', 'member')) DEFAULT 'member',
  UNIQUE(organization_id, user_id)
);

-- 3. Core Business Data Table (Customer Invoices)
CREATE TABLE invoices (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  invoice_number TEXT NOT NULL,
  amount NUMERIC(12, 2) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 4. Enable Row Level Security
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

-- 5. Helper Function: Get Active User's Organizations
CREATE OR REPLACE FUNCTION get_user_organizations()
RETURNS SETOF UUID AS $
  SELECT organization_id FROM organization_members
  WHERE user_id = auth.uid();
$ LANGUAGE sql STABLE SECURITY DEFINER;

-- 6. Deploy Airtight RLS Policy
CREATE POLICY "Tenants can only access their own invoices"
ON invoices
FOR ALL
TO authenticated
USING (organization_id IN (SELECT get_user_organizations()))
WITH CHECK (organization_id IN (SELECT get_user_organizations()));

-- 7. High-Performance Indexing: Mandatory for sub-millisecond RLS execution
CREATE INDEX idx_invoices_org_composite 
ON invoices(organization_id, created_at DESC);