Lager Guru Docs

Appearance

Multi-Tenant Architecture Standards ​

ENFORCEABLE RULES FOR TENANT ISOLATION

This document defines strict, non-negotiable standards for multi-tenant implementation in Lager Guru. All developers must follow these rules without exception.

1. Database Schema Requirements ​

Rule 1.1: Every Table Must Have tenant_id ​

REQUIRED: Every business data table MUST include a tenant_id column.

sql
-- ✅ CORRECT
CREATE TABLE public.shipments (
  id uuid PRIMARY KEY,
  tenant_id uuid NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
  -- ... other columns
);

-- ❌ FORBIDDEN
CREATE TABLE public.shipments (
  id uuid PRIMARY KEY,
  -- Missing tenant_id
  -- ... other columns
);

Exceptions (must be explicitly approved):

  • tenants table (tenant definition itself)
  • tenant_users table (tenant-user mapping)
  • profiles table (global user profiles)
  • user_roles table (global roles)
  • System configuration tables (if truly global)

Enforcement:

  • Migration review must verify tenant_id presence
  • Automated schema checks in CI/CD
  • Database triggers can validate on insert/update

Rule 1.2: tenant_id Must Be NOT NULL ​

sql
-- ✅ CORRECT
tenant_id uuid NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE

-- ❌ FORBIDDEN
tenant_id uuid REFERENCES public.tenants(id) -- Nullable

Rationale: Nullable tenant_id creates ambiguity and potential data leakage.

Rule 1.3: Foreign Key Constraint Required ​

sql
-- ✅ CORRECT
tenant_id uuid NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE

-- ❌ FORBIDDEN
tenant_id uuid NOT NULL -- Missing foreign key

Rationale: Foreign key ensures referential integrity and proper cascade deletion.

Rule 1.4: Index on tenant_id ​

sql
-- ✅ REQUIRED
CREATE INDEX idx_<table>_tenant_id ON public.<table>(tenant_id);

Rationale: All queries filter by tenant_id, so index is essential for performance.

2. API Query Requirements ​

Rule 2.1: Every Query Must Filter by tenant_id ​

REQUIRED: All SELECT, UPDATE, DELETE queries MUST explicitly filter by tenant_id.

typescript
// ✅ CORRECT
const tenantId = await getCurrentUserTenantId();
const { data } = await supabase
  .from('shipments')
  .select('*')
  .eq('tenant_id', tenantId); // Explicit filter

// ❌ FORBIDDEN
const { data } = await supabase
  .from('shipments')
  .select('*'); // Missing tenant_id filter

Even with RLS: Always include explicit tenant_id filter for:

  • Code clarity
  • Performance (uses index)
  • Defense in depth

Rule 2.2: No Wildcard Queries ​

typescript
// ❌ FORBIDDEN
const { data } = await supabase
  .from('shipments')
  .select('*')
  .limit(100); // No tenant filter

// ✅ CORRECT
const { data } = await supabase
  .from('shipments')
  .select('*')
  .eq('tenant_id', tenantId)
  .limit(100);

Rule 2.3: INSERT Must Include tenant_id ​

typescript
// ✅ CORRECT
await supabase
  .from('shipments')
  .insert({
    tenant_id: tenantId, // Explicitly set
    status: 'pending',
    // ... other fields
  });

// ❌ FORBIDDEN
await supabase
  .from('shipments')
  .insert({
    // Missing tenant_id
    status: 'pending',
  });

Helper Function:

typescript
// lib/tenants.ts
export async function insertWithTenant<T>(
  table: string,
  data: Omit<T, 'tenant_id'> & { tenant_id?: string }
) {
  const tenantId = await getCurrentUserTenantId();
  if (!tenantId) throw new Error('No tenant context');
  
  return supabase.from(table).insert({
    ...data,
    tenant_id: tenantId,
  });
}

Rule 2.4: UPDATE Must Filter by tenant_id ​

typescript
// ✅ CORRECT
await supabase
  .from('shipments')
  .update({ status: 'completed' })
  .eq('id', shipmentId)
  .eq('tenant_id', tenantId); // Required filter

// ❌ FORBIDDEN
await supabase
  .from('shipments')
  .update({ status: 'completed' })
  .eq('id', shipmentId); // Missing tenant_id filter

Rule 2.5: DELETE Must Filter by tenant_id ​

typescript
// ✅ CORRECT
await supabase
  .from('shipments')
  .delete()
  .eq('id', shipmentId)
  .eq('tenant_id', tenantId); // Required filter

// ❌ FORBIDDEN
await supabase
  .from('shipments')
  .delete()
  .eq('id', shipmentId); // Missing tenant_id filter

3. RLS Policy Requirements ​

Rule 3.1: Every RLS Policy Must Use Tenant Check ​

REQUIRED: All RLS policies MUST verify tenant access using tenant_users table.

sql
-- ✅ CORRECT
CREATE POLICY shipments_tenant_isolation ON public.shipments
  FOR SELECT
  TO authenticated
  USING (
    tenant_id IN (
      SELECT tenant_id FROM public.tenant_users
      WHERE user_id = auth.uid()
    )
  );

-- ❌ FORBIDDEN
CREATE POLICY shipments_tenant_isolation ON public.shipments
  FOR SELECT
  TO authenticated
  USING (true); -- No tenant check

Rule 3.2: Use Helper Function for Performance ​

sql
-- ✅ PREFERRED (uses optimized helper)
CREATE POLICY shipments_tenant_isolation ON public.shipments
  FOR SELECT
  TO authenticated
  USING (
    tenant_id = public.get_user_tenant_id()
  );

-- ✅ ACCEPTABLE (explicit subquery)
CREATE POLICY shipments_tenant_isolation ON public.shipments
  FOR SELECT
  TO authenticated
  USING (
    tenant_id IN (
      SELECT tenant_id FROM public.tenant_users
      WHERE user_id = auth.uid()
    )
  );

Rule 3.3: INSERT Policy Must Validate tenant_id ​

sql
-- ✅ CORRECT
CREATE POLICY shipments_tenant_insert ON public.shipments
  FOR INSERT
  TO authenticated
  WITH CHECK (
    tenant_id IN (
      SELECT tenant_id FROM public.tenant_users
      WHERE user_id = auth.uid()
    )
  );

-- ❌ FORBIDDEN
CREATE POLICY shipments_tenant_insert ON public.shipments
  FOR INSERT
  TO authenticated
  WITH CHECK (true); -- No tenant validation

Rule 3.4: UPDATE Policy Must Check Both USING and WITH CHECK ​

sql
-- ✅ CORRECT
CREATE POLICY shipments_tenant_update ON public.shipments
  FOR UPDATE
  TO authenticated
  USING (
    tenant_id IN (
      SELECT tenant_id FROM public.tenant_users
      WHERE user_id = auth.uid()
    )
  )
  WITH CHECK (
    tenant_id IN (
      SELECT tenant_id FROM public.tenant_users
      WHERE user_id = auth.uid()
    )
  );

Rationale: USING checks existing row, WITH CHECK validates new values.

Rule 3.5: Service Role Policies Must Be Explicit ​

sql
-- ✅ CORRECT
CREATE POLICY shipments_service_role_all ON public.shipments
  FOR ALL
  TO service_role
  USING (true)
  WITH CHECK (true);

Note: Service role automatically bypasses RLS, but explicit policies document intent.

4. Data Isolation Requirements ​

Rule 4.1: No Cross-Tenant Data Exposure ​

FORBIDDEN:

  • Queries that return data from multiple tenants
  • Aggregations across tenants (unless super admin)
  • Joins that could leak tenant data
typescript
// ❌ FORBIDDEN
const { data } = await supabase
  .from('shipments')
  .select('*, tenant:tenants(name)'); // Could expose tenant names

// ✅ CORRECT
const tenantId = await getCurrentUserTenantId();
const { data } = await supabase
  .from('shipments')
  .select('*')
  .eq('tenant_id', tenantId);

Rule 4.2: No Public Aggregated Stats ​

typescript
// ❌ FORBIDDEN
// Public endpoint that aggregates across all tenants
app.get('/api/stats', async (req, res) => {
  const { data } = await supabase
    .from('shipments')
    .select('tenant_id, count(*)')
    .group('tenant_id'); // Exposes tenant data
});

// ✅ CORRECT
// Tenant-scoped stats
app.get('/api/stats', async (req, res) => {
  const tenantId = await getTenantIdFromRequest(req);
  const { data } = await supabase
    .from('shipments')
    .select('count(*)')
    .eq('tenant_id', tenantId); // Single tenant only
});

Rule 4.3: No Multi-Tenant Endpoints ​

typescript
// ❌ FORBIDDEN
// Endpoint that accepts tenant_id as parameter
app.post('/api/shipments', async (req, res) => {
  const { tenant_id, ...data } = req.body;
  await supabase.from('shipments').insert({ tenant_id, ...data });
});

// ✅ CORRECT
// Endpoint uses authenticated user's tenant
app.post('/api/shipments', async (req, res) => {
  const tenantId = await getTenantIdFromAuth(req);
  await supabase.from('shipments').insert({ tenant_id: tenantId, ...data });
});

Rule 4.4: No Cross-Tenant Joins ​

sql
-- ❌ FORBIDDEN
-- Join that could return data from multiple tenants
SELECT s.*, t.name as tenant_name
FROM shipments s
JOIN tenants t ON s.tenant_id = t.id;
-- Missing tenant filter

-- ✅ CORRECT
SELECT s.*, t.name as tenant_name
FROM shipments s
JOIN tenants t ON s.tenant_id = t.id
WHERE s.tenant_id = <current_user_tenant_id>;

5. Seeding Requirements ​

Rule 5.1: All Seeds Must Use Service Role ​

typescript
// ✅ CORRECT
import { createAdminClient } from './seedHelpers';

const adminClient = createAdminClient(); // Uses service role

await adminClient
  .from('shipments')
  .insert({ tenant_id: tenantId, ...data });

// ❌ FORBIDDEN
import { supabase } from '@/integrations/supabase/client';

await supabase // Regular client (subject to RLS)
  .from('shipments')
  .insert({ tenant_id: tenantId, ...data });

Rule 5.2: Seeds Must Explicitly Set tenant_id ​

typescript
// ✅ CORRECT
async function seedTenant(tenantId: string) {
  const adminClient = createAdminClient();
  
  await adminClient
    .from('shipments')
    .insert({
      tenant_id: tenantId, // Explicitly set
      status: 'pending',
    });
}

// ❌ FORBIDDEN
async function seedTenant(tenantId: string) {
  const adminClient = createAdminClient();
  
  await adminClient
    .from('shipments')
    .insert({
      // Missing tenant_id
      status: 'pending',
    });
}

Rule 5.3: Seeds Must Validate Tenant Exists ​

typescript
// ✅ CORRECT
async function seedTenant(tenantId: string) {
  const adminClient = createAdminClient();
  
  // Verify tenant exists
  const { data: tenant } = await adminClient
    .from('tenants')
    .select('id')
    .eq('id', tenantId)
    .single();
  
  if (!tenant) {
    throw new Error(`Tenant ${tenantId} does not exist`);
  }
  
  // Proceed with seeding
}

6. Script Requirements ​

Rule 6.1: Scripts Must Set Tenant Context Explicitly ​

typescript
// ✅ CORRECT
// scripts/migrateData.ts
async function main() {
  const tenantId = process.env.TENANT_ID;
  if (!tenantId) {
    throw new Error('TENANT_ID environment variable required');
  }
  
  const adminClient = createAdminClient();
  
  // All operations use explicit tenantId
  await adminClient
    .from('shipments')
    .select('*')
    .eq('tenant_id', tenantId);
}

// ❌ FORBIDDEN
async function main() {
  const adminClient = createAdminClient();
  
  // No tenant context
  await adminClient
    .from('shipments')
    .select('*'); // Missing tenant filter
}

Rule 6.2: Scripts Must Use Service Role ​

typescript
// ✅ CORRECT
import { createAdminClient } from './seedHelpers';

const adminClient = createAdminClient(); // Service role

// ❌ FORBIDDEN
import { supabase } from '@/integrations/supabase/client';

const { data } = await supabase // Regular client
  .from('shipments')
  .select('*');

Rule 6.3: Scripts Must Log Tenant Context ​

typescript
// ✅ CORRECT
async function main() {
  const tenantId = process.env.TENANT_ID;
  console.log(`Running script for tenant: ${tenantId}`);
  
  // ... operations
}

7. UI Requirements ​

Rule 7.1: tenant_id Is Invisible to Normal Users ​

typescript
// ✅ CORRECT
// Normal users never see tenant_id in UI
function ShipmentList() {
  const { data } = useShipments(); // tenant_id filtered automatically
  
  return (
    <div>
      {data.map(shipment => (
        <ShipmentCard key={shipment.id} shipment={shipment} />
        // No tenant_id displayed
      ))}
    </div>
  );
}

// ❌ FORBIDDEN
function ShipmentList() {
  const { data } = useShipments();
  
  return (
    <div>
      {data.map(shipment => (
        <div>
          <span>Tenant: {shipment.tenant_id}</span> {/* Exposed! */}
          <ShipmentCard shipment={shipment} />
        </div>
      ))}
    </div>
  );
}

Rule 7.2: Tenant Admins See "System Settings" ​

typescript
// ✅ CORRECT
function SettingsPage() {
  const { isTenantAdmin } = useTenant();
  
  if (!isTenantAdmin) {
    return <AccessDenied />;
  }
  
  return (
    <div>
      <h1>System Settings</h1>
      {/* Tenant-specific settings */}
    </div>
  );
}

Rule 7.3: Super Admin Sees "All Tenants" ​

typescript
// ✅ CORRECT
function TenantManagement() {
  const { isSuperAdmin } = useAuth();
  
  if (!isSuperAdmin) {
    return <AccessDenied />;
  }
  
  return (
    <div>
      <h1>All Tenants</h1>
      {/* List of all tenants */}
    </div>
  );
}

Rule 7.4: No Tenant Selection in UI (Unless Super Admin) ​

typescript
// ✅ CORRECT
// Normal users: tenant is determined from auth
const tenantId = await getCurrentUserTenantId();

// Super admin: can select tenant
function SuperAdminDashboard() {
  const { isSuperAdmin } = useAuth();
  const [selectedTenant, setSelectedTenant] = useState<string | null>(null);
  
  if (!isSuperAdmin) {
    return <AccessDenied />;
  }
  
  return (
    <TenantSelector
      value={selectedTenant}
      onChange={setSelectedTenant}
    />
  );
}

8. Security Requirements ​

Rule 8.1: No Cross-Tenant Joins ​

sql
-- ❌ FORBIDDEN
-- Join that could return data from multiple tenants
SELECT s1.*, s2.*
FROM shipments s1
JOIN shipments s2 ON s1.related_id = s2.id;
-- Missing tenant filters

-- ✅ CORRECT
SELECT s1.*, s2.*
FROM shipments s1
JOIN shipments s2 ON s1.related_id = s2.id
WHERE s1.tenant_id = <current_tenant>
  AND s2.tenant_id = <current_tenant>;

Rule 8.2: No Multi-Tenant Endpoints ​

typescript
// ❌ FORBIDDEN
// Endpoint that accepts tenant_id
app.get('/api/shipments/:tenantId', async (req, res) => {
  const { tenantId } = req.params;
  // User could request any tenant's data
});

// ✅ CORRECT
// Endpoint uses authenticated user's tenant
app.get('/api/shipments', async (req, res) => {
  const tenantId = await getTenantIdFromAuth(req);
  // Only user's tenant
});

Rule 8.3: No Global Mutations (Except Super Admin) ​

typescript
// ❌ FORBIDDEN
// Mutation that affects all tenants
async function deleteAllShipments() {
  await supabase
    .from('shipments')
    .delete()
    .neq('id', '00000000-0000-0000-0000-000000000000'); // Deletes all
}

// ✅ CORRECT
// Super admin only
async function deleteAllShipments() {
  const { isSuperAdmin } = await checkSuperAdmin();
  if (!isSuperAdmin) throw new Error('Unauthorized');
  
  const adminClient = createAdminClient();
  await adminClient.from('shipments').delete().neq('id', '00000000-0000-0000-0000-000000000000');
}

// ✅ CORRECT
// Tenant-scoped mutation
async function deleteTenantShipments(tenantId: string) {
  const adminClient = createAdminClient();
  await adminClient
    .from('shipments')
    .delete()
    .eq('tenant_id', tenantId); // Single tenant only
}

Rule 8.4: Validate Tenant Access Before Operations ​

typescript
// ✅ CORRECT
async function updateShipment(shipmentId: string, data: any) {
  const tenantId = await getCurrentUserTenantId();
  
  // Verify shipment belongs to tenant
  const { data: shipment } = await supabase
    .from('shipments')
    .select('tenant_id')
    .eq('id', shipmentId)
    .eq('tenant_id', tenantId)
    .single();
  
  if (!shipment) {
    throw new Error('Shipment not found or access denied');
  }
  
  // Proceed with update
  await supabase
    .from('shipments')
    .update(data)
    .eq('id', shipmentId)
    .eq('tenant_id', tenantId);
}

9. Code Review Checklist ​

Before merging any PR, verify:

  • [ ] All new tables have tenant_id uuid NOT NULL
  • [ ] All queries filter by tenant_id
  • [ ] All RLS policies check tenant access
  • [ ] No wildcard queries without tenant filter
  • [ ] No cross-tenant joins
  • [ ] Seeds use service role
  • [ ] Scripts set tenant context explicitly
  • [ ] UI doesn't expose tenant_id to normal users
  • [ ] No multi-tenant endpoints
  • [ ] No global mutations (except super admin)

10. Enforcement ​

Automated Checks ​

  1. Schema Validation: CI/CD checks for tenant_id in all tables
  2. Query Analysis: Static analysis to detect missing tenant_id filters
  3. RLS Policy Review: Automated policy validation
  4. Code Review: Mandatory review for all database/API changes

Manual Review ​

  1. Database Migrations: All migrations reviewed for tenant_id
  2. API Endpoints: All new endpoints reviewed for tenant isolation
  3. RLS Policies: All policies reviewed for correctness
  4. Seeding Scripts: All seeds reviewed for service role usage

Violations ​

Violations of these standards are blocking issues and must be fixed before merge.

11. Helper Functions ​

Get Current Tenant ID ​

typescript
// lib/tenants.ts
export async function getCurrentUserTenantId(): Promise<string | null> {
  const { data: { user } } = await supabase.auth.getUser();
  if (!user) return null;

  const { data } = await supabase
    .from('tenant_users')
    .select('tenant_id')
    .eq('user_id', user.id)
    .maybeSingle();

  return data?.tenant_id || null;
}

Insert with Tenant ​

typescript
export async function insertWithTenant<T extends { tenant_id?: string }>(
  table: string,
  data: Omit<T, 'tenant_id'>
) {
  const tenantId = await getCurrentUserTenantId();
  if (!tenantId) throw new Error('No tenant context');

  return supabase.from(table).insert({
    ...data,
    tenant_id: tenantId,
  } as T);
}

Query with Tenant ​

typescript
export async function queryWithTenant<T>(
  table: string,
  query: (builder: PostgrestQueryBuilder<T>) => PostgrestQueryBuilder<T>
) {
  const tenantId = await getCurrentUserTenantId();
  if (!tenantId) throw new Error('No tenant context');

  return query(supabase.from(table).eq('tenant_id', tenantId));
}

Summary ​

These standards are non-negotiable. Every developer must follow them without exception.

  • ✅ Every table has tenant_id
  • ✅ Every query filters by tenant_id
  • ✅ Every RLS policy checks tenant access
  • ✅ No cross-tenant data exposure
  • ✅ Seeds use service role
  • ✅ Scripts set tenant context
  • ✅ UI hides tenant_id from users
  • ✅ No multi-tenant endpoints
  • ✅ No global mutations (except super admin)

When in doubt, ask. When unsure, be conservative. When reviewing, be strict.

Released under Commercial License

Copyright © 2025 HashMatrix